<?php


namespace KommaApp\Shop\Products;

use Carbon\Carbon;
use KommaApp\Shop\Products;
use Shop;

class ProductRepository
{

    public function countProductsByCategoryId($categoryId)
    {
        return count(
            \DB::table('products')
                ->join('products_shops', 'products.id', '=', 'products_shops.product_id')
                ->join('product_translations', 'products_shops.id', '=', 'product_translations.products_shop_id')
                ->join('categories_products_shops', 'categories_products_shops.product_shop_id', '=', 'product_translations.products_shop_id')
                ->where('categories_products_shops.category_id', '=', $categoryId)
                ->where('products_shops.shop_id', '=', Shop::getId())
                ->where('products_shops.price', '>', 0)
                ->where('products_shops.active', '=', 1)
                ->select('*')
                ->orderBy('categories_products_shops.position')
                ->groupBy('products.id')
                ->get()
        );
    }

    public function getProductsByCategoryId($categoryId, $skip = null, $take = null)
    {
        $productQuery = \DB::table('products')
            ->join('products_shops', 'products.id', '=', 'products_shops.product_id')
            ->leftjoin('images', function ($join) {
                $join->on('products.id', '=', 'images.imageble_id')
                    ->where('images.imageble_type', '=', 'Komma\Kms\Products\Models\Product');
            })
            ->join('product_translations', 'products_shops.id', '=', 'product_translations.products_shop_id')
            ->join('categories_products_shops', 'categories_products_shops.product_shop_id', '=', 'product_translations.products_shop_id')
            ->join('languages', 'product_translations.language_id', '=', 'languages.id')
            ->join('routes', 'routes.routable_id', '=', 'product_translations.id')
            ->join('taxes', 'taxes.id', '=', 'products_shops.tax_id')
            /*->leftjoin('images as brandimages', function($join){
                $join->on('categories_products_shops.category_id', '=', 'brandimages.imageble_id')
                    ->where('brandimages.imageble_type', '=', 'Komma\Kms\Products\Images\Models\Category');
            })*/
            ->groupBy('products.id')
            //This makes the order like 1,2,3,NULL instead of NULL,1,2,3
            ->orderByRaw('-categories_products_shops.position DESC')
            ->where('categories_products_shops.category_id', '=', $categoryId)
            ->where('routes.routable_type', '=', 'Komma\Kms\Products\Models\ShopProductTranslation')
            ->where('languages.id', '=', Shop::getLanguageService()->getCurrentLanguageId())
            ->where('products_shops.shop_id', '=', Shop::getId())
            ->where('products_shops.price', '>', 0)
            ->where('products_shops.active', '=', 1)
            ->select(
                'products.id',
                'products.internal_article_number',
                'products.pick_number',
                'products.article_number',
                'products.bypass',
                'products.filter_class', 'products.pollen_filter',
                'products.fine_dust_filter',
                'products.size',
                'products.unit_type',
                'products.volume',
                'products.default_price',

                'products.supplier_name',
                'products.brand_name',
                'products.is_filter_set',
                'products.product_group',

                'images.small_image_url',

                //'brandimages.small_image_url as small_brand_image_url',
                //'brandimages.large_image_url as large_brand_image_url',

                'products_shops.price',

                'product_translations.language_id',
                'product_translations.name',
                'product_translations.description',
                'product_translations.model',
                'product_translations.notes',
                'product_translations.warning',

                'product_translations.special_1',
                'product_translations.special_2',
                'product_translations.composition',

                'categories_products_shops.category_id',

                'routes.route',
                'taxes.name AS tax_name',
                'taxes.rate AS tax_rate'
            );
        if (is_numeric($skip)) $productQuery->skip($skip);
        if (is_numeric($take)) $productQuery->take($take);

        $products = $productQuery->get();

        foreach ($products as $key => $product) {
            $products[$key]->discounts = $this->getProductDiscountsByCategoryId($categoryId);
            $products[$key]->discountBarDiscounts = $this->getDiscountBarDiscounts($products[$key]->category_id);
            $products[$key]->composition = json_decode($products[$key]->composition);
        }

        return $products;
    }

    public function getProductsByRoutableId($routableId)
    {
        $product = \DB::table('products')
            ->join('products_shops', 'products.id', '=', 'products_shops.product_id')
            ->join('product_translations', 'products_shops.id', '=', 'product_translations.products_shop_id')
            ->join('categories_products_shops', 'categories_products_shops.product_shop_id', '=', 'product_translations.products_shop_id')
            ->join('languages', 'product_translations.language_id', '=', 'languages.id')
            ->join('routes', 'routes.routable_id', '=', 'product_translations.id')
            ->join('taxes', 'taxes.id', '=', 'products_shops.tax_id')
            ->leftjoin('images', function ($join) {
                $join->on('categories_products_shops.category_id', '=', 'images.imageble_id')
                    ->where('images.imageble_type', '=', 'Komma\Kms\Products\Models\Product');
            })
            ->where('routes.routable_id', '=', $routableId)
            ->where('routes.routable_type', '=', 'Komma\Kms\Products\Models\ShopProductTranslation')
            ->where('languages.id', '=', Shop::getLanguageService()->getCurrentLanguageId())
            ->where('products_shops.shop_id', '=', Shop::getId())
            ->where('products_shops.price', '>', 0)
            ->where('products_shops.active', '=', 1)
            ->orderBy('categories_products_shops.position')
            ->select(
                'products.id',
                'products.internal_article_number',
                'products.pick_number',
                'products.article_number',
                'products.bypass',
                'products.filter_class',
                'products.pollen_filter',
                'products.fine_dust_filter',
                'products.size',
                'products.unit_type',
                'products.volume',
                'products.default_price',

                'products.supplier_name',
                'products.brand_name',
                'products.is_filter_set',
                'products.product_group',

                'products_shops.price',

                'product_translations.language_id',
                'product_translations.name',
                'product_translations.description',
                'product_translations.model',
                'product_translations.notes',
                'product_translations.warning',

                'product_translations.meta_title',
                'product_translations.meta_description',

                'product_translations.special_1',
                'product_translations.special_2',
                'product_translations.composition',

                'categories_products_shops.category_id',

                'images.large_image_url as large_brand_image_url',
                'images.small_image_url as small_brand_image_url',

                'routes.route',
                'taxes.name AS tax_name',
                'taxes.rate AS tax_rate'
            )
            ->first();
        if (!$product) return false;
        $entity = new ProductEntity((array)$product);
        $entity->images = $this->getProductImagesByProductId($product->id);
        $entity->discounts = $this->getProductDiscountsByCategoryId($product->category_id);
        $entity->discountBarDiscounts = $this->getDiscountBarDiscounts($product->category_id);

        $entity->composition = json_decode($entity->composition);
        return $entity;

    }

    public function getProductsByIds(array $productIds)
    {
        //If the id array is empty, we are not going to execute the query
        if (empty($productIds)) return;

        $products = \DB::table('products')
            ->join('products_shops', 'products.id', '=', 'products_shops.product_id')
            ->join('product_translations', 'products_shops.id', '=', 'product_translations.products_shop_id')
            ->leftjoin('images', function ($join) {
                $join->on('products.id', '=', 'images.imageble_id')
                    ->where('images.imageble_type', '=', 'Komma\Kms\Products\Models\Product');
            })
            ->join('categories_products_shops', 'categories_products_shops.product_shop_id', '=', 'product_translations.products_shop_id')
            ->join('languages', 'product_translations.language_id', '=', 'languages.id')
            ->join('routes', 'routes.routable_id', '=', 'product_translations.id')
            ->join('taxes', 'taxes.id', '=', 'products_shops.tax_id')
            ->whereIn('products.id', $productIds)
            ->where('routes.routable_type', '=', 'Komma\Kms\Products\Models\ShopProductTranslation')
            ->where('languages.id', '=', Shop::getLanguageService()->getCurrentLanguageId())
            ->where('products_shops.shop_id', '=', Shop::getId())
            ->where('products_shops.price', '>', 0)
            ->where('products_shops.active', '=', 1)
            //This will put the fields in the order of the $ids array, these are sorted by ranking
            ->orderByRaw("FIELD(products.id," . implode(',', $productIds) . ")")
            ->groupBy('products.id')
            ->select(
                'products.id',
                'products.internal_article_number',
                'products.pick_number',
                'products.article_number',
                'products.bypass',
                'products.filter_class', 'products.pollen_filter',
                'products.fine_dust_filter',
                'products.size',
                'products.unit_type',
                'products.volume',
                'products.default_price',

                'products.supplier_name',
                'products.brand_name',
                'products.is_filter_set',
                'products.product_group',

                'products_shops.price',

                'product_translations.language_id',
                'product_translations.name',
                'product_translations.description',
                'product_translations.model',
                'product_translations.notes',
                'product_translations.warning',

                'product_translations.meta_title',
                'product_translations.meta_description',

                'product_translations.special_1',
                'product_translations.special_2',
                'product_translations.composition',

                'categories_products_shops.category_id',

                'images.small_image_url',

                'routes.route',
                'taxes.name AS tax_name',
                'taxes.rate AS tax_rate'
            )
            ->get();

        foreach ($products as $key => $product) {
            $products[$key]->discounts = $this->getProductDiscountsByCategoryId($product->category_id);
            $products[$key]->discountBarDiscounts = $this->getDiscountBarDiscounts($products[$key]->category_id);
            $products[$key]->composition = json_decode($products[$key]->composition);
        }

        return $products;
    }

    public function getProductsByIdsCompact($productIds, $limit = -1)
    {
        //If the id array is empty, we are not going to execute the query
        if (empty($productIds)) return;
        $products = \DB::table('products')
            ->join('products_shops', 'products.id', '=', 'products_shops.product_id')
            ->join('product_translations', 'products_shops.id', '=', 'product_translations.products_shop_id')
            ->join('categories_products_shops', 'categories_products_shops.product_shop_id', '=', 'product_translations.products_shop_id')
            ->join('languages', 'product_translations.language_id', '=', 'languages.id')
            ->join('routes', 'routes.routable_id', '=', 'product_translations.id')
            ->whereIn('products.id', $productIds)
            ->where('routes.routable_type', '=', 'Komma\Kms\Products\Models\ShopProductTranslation')
            ->where('languages.id', '=', Shop::getLanguageService()->getCurrentLanguageId())
            ->where('products_shops.shop_id', '=', Shop::getId())
            ->where('products_shops.active', '=', 1)
            //This will put the fields in the order of the $ids array, these are sorted by ranking
            ->orderByRaw("FIELD(products.id," . implode(',', $productIds) . ")")
            ->groupBy('products.id')
            ->select(
                'products.id',
                'product_translations.name',
//                'product_translations.description',
                'products.article_number',
                'products.brand_name',
                'products.bypass',
                'products.filter_class', 'products.pollen_filter',
                'products.fine_dust_filter',
                'routes.route'
            )
            ->take($limit)
            ->get();
        return $products;
    }

    public function getByProductId($productId)
    {

        $product = \DB::table('products')
            ->join('products_shops', 'products.id', '=', 'products_shops.product_id')
            ->join('product_translations', 'products_shops.id', '=', 'product_translations.products_shop_id')
            ->join('categories_products_shops', 'categories_products_shops.product_shop_id', '=', 'product_translations.products_shop_id')
            ->join('languages', 'product_translations.language_id', '=', 'languages.id')
            ->join('routes', 'routes.routable_id', '=', 'product_translations.id')
            ->join('taxes', 'taxes.id', '=', 'products_shops.tax_id')
            ->where('products.id', '=', $productId)
            ->where('routes.routable_type', '=', 'Komma\Kms\Products\Models\ShopProductTranslation')
            ->where('languages.id', '=', Shop::getLanguageService()->getCurrentLanguageId())
            ->where('products_shops.shop_id', '=', Shop::getId())
            ->where('products_shops.price', '>', 0)
            ->where('products_shops.active', '=', 1)
            ->orderBy('categories_products_shops.position')
            ->select(
                'products.id',
                'products.internal_article_number',
                'products.pick_number',
                'products.article_number',
                'products.bypass',
                'products.filter_class', 'products.pollen_filter',
                'products.fine_dust_filter',
                'products.size',
                'products.unit_type',
                'products.volume',
                'products.default_price',

                'products.supplier_name',
                'products.brand_name',
                'products.is_filter_set',
                'products.product_group',

                'products_shops.price',

                'product_translations.language_id',
                'product_translations.name',
                'product_translations.description',
                'product_translations.model',
                'product_translations.notes',
                'product_translations.warning',

                'product_translations.meta_title',
                'product_translations.meta_description',

                'product_translations.special_1',
                'product_translations.special_2',
                'product_translations.composition',

                'categories_products_shops.category_id',
                'categories_products_shops.category_id',
                \DB::raw('GROUP_CONCAT(categories_products_shops.category_id) as category_ids'),

                'routes.route',
                'taxes.name AS tax_name',
                'taxes.rate AS tax_rate'
            )
            ->first();


        //Check if the product doesn't exist, return false;
        if (!$product) return false;

        $entity = new ProductEntity((array)$product);
        $entity->images = $this->getProductImagesByProductId($product->id);


        $discounts = [];
        foreach (explode(',', $product->category_ids) as $categoryId) {
            $discounts = array_merge($this->getProductDiscountsByCategoryId($product->category_id), $discounts);
        }
        $entity->discounts = $this->cleanDiscounts($discounts);
        $entity->discountBarDiscounts = $this->getDiscountBarDiscounts($product->category_id);


//        $entity->discounts = $this->getProductDiscountsByCategoryId($product->category_id);
        $entity->setComposition(json_decode($entity->composition));

        return $entity;
    }

    /**
     * @param $searchWords
     * @return mixed
     */
    public function search($searchWords)
    {
        // Give first keywords in string more relevance
        $words = [];
        foreach ($searchWords as $word) {
            $words[] = '*' . $word . '*';
        }
        $searchQuery = implode(' ', $words);

        $result = \DB::table('products')
            ->join('products_shops', 'products.id', '=', 'products_shops.product_id')
            ->leftjoin('images', function ($join) {
                $join->on('products.id', '=', 'images.imageble_id')
                    ->where('images.imageble_type', '=', 'Komma\Kms\Products\Models\Product');
            })
            ->join('product_translations', 'products_shops.id', '=', 'product_translations.products_shop_id')
            ->join('categories_products_shops', 'categories_products_shops.product_shop_id', '=', 'product_translations.products_shop_id')
            ->join('languages', 'product_translations.language_id', '=', 'languages.id')
            ->join('routes', 'routes.routable_id', '=', 'product_translations.id')
            ->join('taxes', 'taxes.id', '=', 'products_shops.tax_id')
            ->leftjoin('images as brandimages', function ($join) {
                $join->on('categories_products_shops.category_id', '=', 'brandimages.imageble_id')
                    ->where('brandimages.imageble_type', '=', 'Komma\Kms\Categories\Models\Category');
            })
            ->groupBy('products.id')
            ->orderBy('categories_products_shops.position')
            ->where('routes.routable_type', '=', 'Komma\Kms\Products\Models\ShopProductTranslation')
            ->where('languages.id', '=', Shop::getLanguageService()->getCurrentLanguageId())
            ->where('products_shops.shop_id', '=', Shop::getId())
            ->where('products_shops.price', '>', 0)
            ->where('products_shops.active', '=', 1)
            ->where(function ($query) use ($searchWords, $searchQuery) {
                $query->whereRaw('MATCH(product_translations.name, product_translations.description, product_translations.model) AGAINST(? IN BOOLEAN MODE) ', [$searchQuery]);
                foreach ($searchWords as $word) {
                    $query->orWhere(function ($q) use ($word) {
                        $q->orWhere('products.article_number', 'LIKE', '%' . $word . '%');
                        $q->orWhere('products.filter_class', 'LIKE', '%' . $word . '%');
                    });
                }
            })
            ->select(
                'products.id',
                'products.internal_article_number',
                'products.pick_number',
                'products.article_number',
                'products.bypass',
                'products.filter_class', 'products.pollen_filter',
                'products.fine_dust_filter',
                'products.size',
                'products.unit_type',
                'products.volume',
                'products.default_price',

                'products.supplier_name',
                'products.brand_name',
                'products.is_filter_set',
                'products.product_group',

                'images.small_image_url',

                'products_shops.price',

                'product_translations.language_id',
                'product_translations.name',
                'product_translations.description',
                'product_translations.model',
                'product_translations.notes',
                'product_translations.warning',

                'product_translations.meta_title',
                'product_translations.meta_description',

                'product_translations.special_1',
                'product_translations.special_2',
                'product_translations.composition',

                'categories_products_shops.category_id',

                'brandimages.small_image_url as small_brand_image_url',
                'brandimages.large_image_url as large_brand_image_url',

                'routes.route',
                'taxes.name AS tax_name',
                'taxes.rate AS tax_rate',
                \DB::raw('(MATCH(product_translations.name, product_translations.description, product_translations.model) AGAINST("' . $searchQuery . '" IN BOOLEAN MODE) * 1) AS relevancy', [$searchQuery])
            )
            ->orderBy('relevancy', 'DESC')
            ->take(20);

        $products = $result->get();

        foreach ($products as $key => $product) {
            $products[$key]->discounts = $this->getProductDiscountsByCategoryId($products[$key]->category_id);
            $products[$key]->discountBarDiscounts = $this->getDiscountBarDiscounts($products[$key]->category_id);

            $products[$key]->composition = json_decode($products[$key]->composition);
        }


        return $products;
    }

    public function getProductImagesByProductId($productId)
    {
        return \DB::table('images')
            ->where('imageble_type', 'Komma\\Kms\\Products\\Models\\Product')
            ->where('imageble_id', $productId)
            ->get();
    }

    public function getCategoryIds($productId)
    {
        return \DB::table('categories_products_shops')
            ->join('products_shops', 'products_shops.id', '=', 'categories_products_shops.product_shop_id')
            ->where('products_shops.shop_id', '=', Shop::getId())
            ->where('products_shops.product_id', '=', $productId)
            ->select('categories_products_shops.category_id')
            ->lists('category_id');
    }

    public function getProductDiscounts($productId, $quantity)
    {
        $categoryIds = $this->getCategoryIds($productId);
        $discounts = [];
        foreach ($categoryIds as $categoryId) {
            $discounts[] = $this->getProductDiscountsByCategoryId($categoryId, $quantity);
        }
        return $discounts;
    }

    public function getProductDiscountsByCategoryId($categoryId, $quantity = null, $whereField = null, $whereValue = null, $condition = '=')
    {
        $category = \DB::table('categories')->where('id', '=', $categoryId)->first();

        $discounts = [];
        if ($category) {
            $discountQuery = \DB::table('discount_products')
                ->join('discount_products_translations', 'discount_products_translations.discount_product_id', '=', 'discount_products.id')
                ->where('language_id', \Shop::getLanguageService()->getCurrentLanguageId())
                ->join('discount_products_categories', 'discount_products_categories.discount_id', '=', 'discount_products.id')
                ->join('categories', function ($join) use ($category) {
                    $join->on('categories.id', '=', 'discount_products_categories.category_id')
                        ->where('categories.lft', '<=', $category->lft)
                        ->where('categories.rgt', '>=', $category->rgt)
                        ->where('categories.shop_id', '=', $category->shop_id);
                })
                ->where(function ($query) {
                    $query->where('date_min', '<=', Carbon::now())
                        ->orWhereNull('date_min');
                })
                ->where(function ($query) {
                    $query->where('date_max', '>=', Carbon::now())
                        ->orWhereNull('date_max');
                })
                ->where('discount_products.active', '=', '1')
                ->groupBy('discount_products.id')
                ->select(
                    'discount_products.id',
                    'discount_products.name',
                    'discount_products.priority',
                    'discount_products.is_final',
                    'discount_products.in_carrousel',
                    'discount_products.is_cumulative',
                    'discount_products.quantity_min',
                    'discount_products.quantity_max',
                    'discount_products.date_min',
                    'discount_products.date_max',
                    'discount_products.coupon_batch_id',
                    'discount_products.custom_coupon_code', // should be hashed in ajax
                    'discount_products.discount_percentage',
                    'discount_products.discount_fraction',
                    'discount_products.discount_absolute',
                    'discount_products.discount_no_shipping_costs',
                    'discount_products_translations.description',
                    'discount_products_translations.next_discount_description'
                );

            if (is_numeric($quantity)) {
                $discountQuery->where(function ($q) use ($quantity) {
                    $q->where('quantity_min', '<=', $quantity)
                        ->orWhereNull('quantity_min');
                });
                $discountQuery->where(function ($q) use ($quantity) {
                    $q->where('quantity_max', '>=', $quantity)
                        ->orWhereNull('quantity_max');
                });
            }

            if ($whereField && $whereValue) {
                $discountQuery = $discountQuery->where($whereField, '=', $whereValue);
                $discountQuery = $discountQuery->orderBy('quantity_min');

            } else {
                $discountQuery = $discountQuery->orderBy('discount_products.priority', 'asc');
            }


            $discounts = $discountQuery->get();

        }

        foreach ($discounts as $key => $discount) {
            $discount->default_next_discount_description = \Lang::get('checkout/cart.default_next_discount_tip');
            if ($discounts[$key]->custom_coupon_code)
                $discounts[$key]->custom_coupon_code = sha1($discounts[$key]->custom_coupon_code);
        }

        return $discounts;

    }

    public function getProductRouteByInternalArticleNumber($internal_article_number, $lang_iso)
    {
        $result = \DB::table('products')
            ->select('products.id', 'routes.route', 'languages.id as lid')
            ->leftjoin('products_shops', 'products.id', '=', 'products_shops.product_id')
            ->leftjoin('product_translations', 'products_shops.id', '=', 'product_translations.products_shop_id')
            ->leftjoin('languages', 'product_translations.language_id', '=', 'languages.id')
            ->leftjoin('routes', 'routes.routable_id', '=', 'product_translations.id')
            ->where('routes.routable_type', '=', 'Komma\Kms\Products\Models\ShopProductTranslation')
            ->where('products_shops.active', '=', 1)
            ->where('internal_article_number', '=', $internal_article_number)
            ->where('languages.iso_2', '=', $lang_iso);

        if ($result->count() > 0) return $result->first();

        return false;
    }

    public function cleanDiscounts($discounts)
    {
        $ids = [];
        foreach ($discounts as $key => $discount) {
            if (in_array($discount->id, $ids)) unset($discounts[$key]);
            $ids[] = $discount->id;
        }
        return $discounts;

    }

    public function getDiscountBarDiscounts($cat_id)
    {
        $discounts = [];
        foreach ($this->getProductDiscountsByCategoryId($cat_id, null, 'in_carrousel', '1') as $discount) {

            if ($discount->discount_percentage) {
                $discount->click_text = $discount->discount_percentage . '%';
            } elseif ($discount->discount_fraction) {
                $discount->click_text = '+' . $discount->discount_fraction . ' ' . \Lang::get('angular.cart.free');
                $discount->next_discount_description = str_replace(':quantity', $discount->quantity_min, str_replace(':free', $discount->discount_fraction, $discount->next_discount_description));
            } else {
                $discount->click_text = $discount->name;
            }
            $discounts[] = $discount;

        }
        return $discounts;


    }

}