<?php

namespace KommaApp\Shop\Categories;

use Carbon\Carbon;
use Illuminate\Database\DatabaseManager;
use Illuminate\Support\Facades\Cache;
use KommaApp\Shop\Categories\Models\Category;
use KommaApp\Shop\Tree\Tree;

class CategoryRepository
{
    /**
     * @var DatabaseManager
     */
    protected $db;

    /**
     * @var Tree
     */
    private $tree;

    /**
     * @param Tree $tree
     */
    public function __construct(DatabaseManager $db, Tree $tree)
    {
        $this->db = $db;
        $this->tree = $tree;
    }

    public function getOnHomepage()
    {
        // Bind Languages and Routes
        return \DB::table('category_translations')
            ->join('categories', 'categories.id', '=', 'category_translations.category_id')
            ->leftjoin('images', function ($join) {
                $join->on('categories.id', '=', 'images.imageble_id')
                    ->where('images.imageble_type', '=', 'Komma\Kms\Categories\Models\Category');
            })
            ->join('routes', 'category_translations.id', '=', 'routes.routable_id')
            ->groupBy('categories.id')
            ->where('routes.routable_type', '=', 'Komma\Kms\Categories\Models\CategoryTranslation')
            ->where('category_translations.language_id', '=', \Shop::getLanguageService()->getCurrentLanguageId())
            ->where('categories.shop_id', '=', \Shop::getId())
            ->where('categories.show_on_home', '=', 1)
            ->where('categories.active', '=', 1)
            ->orderBy('categories.lft', 'asc')
            ->select(
                'categories.id',
                'categories.logo',
                'categories.active',

                'images.small_image_url',
                'images.medium_image_url',
                'images.large_image_url',

                'category_translations.name',
                'category_translations.category_id as id',

                'routes.route',
                'routes.routable_id'
            )
            ->take(8)
            ->get();

    }

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

        return \DB::table('category_translations')
            ->join('categories', 'categories.id', '=', 'category_translations.category_id')
            ->leftjoin('images', function ($join) {
                $join->on('categories.id', '=', 'images.imageble_id')
                    ->where('images.imageble_type', '=', 'Komma\Kms\Categories\Models\Category');
            })
            ->join('routes', 'category_translations.id', '=', 'routes.routable_id')
            ->groupBy('categories.id')
            ->where('routes.routable_type', '=', 'Komma\Kms\Categories\Models\CategoryTranslation')
            ->where('category_translations.language_id', '=', \Shop::getLanguageService()->getCurrentLanguageId())
            ->where('categories.shop_id', '=', \Shop::getId())
            ->where('categories.active', '=', 1)
            ->whereIn('categories.id', $ids)
//            ->orderBy('categories.lft', 'asc')
            ->groupBy('categories.id')
            ->select(
                'categories.id',
                'categories.logo',
                'categories.active',

                'images.small_image_url',
                'images.medium_image_url',
                'images.large_image_url',

                'category_translations.name',
                'category_translations.category_id as id',
                'category_translations.id as category_translation_id',
                'category_translations.description',

                'routes.route',
                'routes.routable_id'
            )
            //This will put the fields in the order of the $ids array, these are sorted by ranking
            ->orderByRaw("FIELD(categories.id,".implode(',',$ids).")")
            ->get();
    }

    /**
     * This function will get a compact version of the categories
     * The limit will limit the amount of lines -1 will return all
     *
     * @param array $ids
     * @param int   $limit
     * @return mixed
     *
     */
    public function getCategoriesByIdsCompact(array $ids,$limit=-1)
    {
        //If the id array is empty, we are not going to execute the query
        if(empty($ids)) return false;

        return \DB::table('category_translations')
            ->join('categories', 'categories.id', '=', 'category_translations.category_id')
            ->join('routes', 'category_translations.id', '=', 'routes.routable_id')
            ->groupBy('categories.id')
            ->where('routes.routable_type', '=', 'Komma\Kms\Categories\Models\CategoryTranslation')
            ->where('category_translations.language_id', '=', \Shop::getLanguageService()->getCurrentLanguageId())
            ->where('categories.shop_id', '=', \Shop::getId())
            ->where('categories.active', '=', 1)
            ->whereIn('categories.id', $ids)
            ->orderBy('categories.lft', 'asc')
            ->groupBy('categories.id')
            ->select(
                'categories.id',
                'category_translations.name',
                'routes.route'
            )
            //This will put the fields in the order of the $ids array, these are sorted by ranking
            ->orderByRaw("FIELD(categories.id,".implode(',',$ids).")")
            ->take($limit)
            ->get();
    }

    public function getRootCategoryByShopId($shopId)
    {
        $cacheKey = 'rootCategoryByShopId|'.$shopId;
        if (!Cache::has($cacheKey)) {
            $root = \DB::table('categories')
                ->select('categories.logo', 'categories.lft', 'categories.rgt', 'categories.active')
                ->where('categories.shop_id', '=', $shopId)
                ->where('categories.lft', '=', '1')
                ->first();
            Cache::put($cacheKey, $root, Carbon::now()->addMinutes(1440)); //24 hours
        } else {
            $root = Cache::get($cacheKey);
        }

        return $root;
    }

    public function getInactiveCategoriesByShopId($shopId)
    {
        $cacheKey = 'inactiveCategoriesByShopId|' . $shopId;
        if (!Cache::has($cacheKey)) {
            $inactive_items = \DB::table('categories')
                ->select('categories.id', 'categories.lft', 'categories.rgt')
                ->where('categories.shop_id', '=', $shopId)
                ->where('categories.active', '=', 0)
                ->get();
            Cache::put($cacheKey, $inactive_items, Carbon::now()->addMinutes(1440));
        } else {
            $inactive_items = Cache::get($cacheKey);
        }

        return $inactive_items;
    }

    public function getTreeNodes()
    {
        $shopId = \Shop::getId();
        $languageId = \Shop::getLanguageService()->getCurrentLanguageId();

        $root = $this->getRootCategoryByShopId($shopId);

        //Select the inactive items, we are going to use these as filter in the next query
        $inactive_items = $this->getInactiveCategoriesByShopId($shopId);

        $cacheKey = 'activeCategoriesWithTranslationsAndImagesAndRoutesForShopWithLanguageId|'.$shopId.'|'.$languageId;
        if (!Cache::has($cacheKey)) {
            // Select categories with translations, images, routes
            $categories = \DB::table('category_translations')
                ->join('categories', 'categories.id', '=', 'category_translations.category_id')
                ->leftjoin('images', function ($join) {
                    $join->on('categories.id', '=', 'images.imageble_id')
                        ->where('images.imageble_type', '=', 'Komma\Kms\Categories\Models\Category');
                })
                ->join('routes', 'category_translations.id', '=', 'routes.routable_id')
                ->groupBy('categories.id');

            //loop trough inactive items and exclude them from the real query
            foreach ($inactive_items as $inactive_item) {
                //if the diffrence between left and right is -1
                //            $categories = $categories->whereNotBetween('categories.lft',array($inactive_item->lft, $inactive_item->rgt));
                //            $categories = $categories->whereNotBetween('categories.rgt',array($inactive_item->lft, $inactive_item->rgt));

                $categories = $categories->whereNotBetween('categories.lft',
                    array($inactive_item->lft, $inactive_item->rgt));

                if (($inactive_item->lft - $inactive_item->rgt) == -1) {
                    //This is the last item om the tree, use lft en rgt

                } else {
                    //This is not the last item use of the three use lft and rgt-1 as boundries
                    //                $categories = $categories->whereNotBetween('categories.rgt',array($inactive_item->lft, $inactive_item->rgt-1));
                }
            }

            $categories->where('routes.routable_type', '=', 'Komma\Kms\Categories\Models\CategoryTranslation')
                ->where('category_translations.language_id', '=', $languageId)
                ->where('categories.shop_id', '=', $shopId)
                ->orderBy('categories.lft', 'asc')
                ->select(
                    'categories.lft',
                    'categories.rgt',
                    'categories.active',

                    'images.small_image_url',
                    'images.large_image_url',
                    'images.medium_image_url',

                    'category_translations.name',
                    'category_translations.category_id as id',
                    'category_translations.id as category_translation_id',
                    'category_translations.description',
                    'category_translations.description2',
                    'category_translations.description3',

                    'category_translations.meta_description',
                    'category_translations.meta_title',

                    'routes.route',
                    'routes.routable_id'
                );

            $categories = $categories->get();
            Cache::put($cacheKey, $categories, Carbon::now()->addMinutes(1440)); //24 hours
        } else {
            $categories = Cache::get($cacheKey);
        }

        $tree = $this->tree->make($root, $categories);
        //$abcTree = $this->tree->alphabetize($tree);
        return $tree;
    }

    public function search($searchWords)
    {
        $currentLanguageId = \Shop::getLanguageService()->getCurrentLanguageId();
        $currentShopId = \Shop::getId();

        $words = [];
        foreach ($searchWords as $word) {
            $words[] = '*' . $word . '*';
        }
        $searchQuery = implode(' ', $words);

        $result = \DB::table('categories')
            ->join('category_translations', function ($join) use ($currentLanguageId) {
                $join->on('categories.id', '=', 'category_translations.category_id')
                    ->where('category_translations.language_id', '=', $currentLanguageId);
            })
            ->leftjoin('images', function ($join) {
                $join->on('categories.id', '=', 'images.imageble_id')
                    ->where('images.imageble_type', '=', 'Komma\Kms\Categories\Models\Category');
            })
            ->join('routes', function ($join) {
                $join->on('category_translations.id', '=', 'routes.routable_id')
                    ->where('routes.routable_type', '=', 'Komma\Kms\Categories\Models\CategoryTranslation');
            })
            ->where('categories.shop_id', '=', $currentShopId)
            ->where('categories.active', '=', 1)
            ->whereRaw('MATCH(category_translations.name, category_translations.description) AGAINST(? IN BOOLEAN MODE)', [$searchQuery])
            ->select(
                'images.small_image_url',
                'images.large_image_url',
                'images.medium_image_url',

                'category_translations.name',
                'category_translations.category_id as id',
                'category_translations.id as category_translation_id',
                'category_translations.description',

                'routes.route'
            );

        return $result->get();
    }

    /**
     * @param array $select
     * @param int   $shopId
     * @param int   $languageId
     */
    public function getAllActiveCategories($select = '*', $shopId = null, $languageId = null)
    {
        $query = $this->db->table('categories')
            ->select($select)
            ->join('category_translations', 'categories.id', '=', 'category_translations.category_id')
            ->whereNotIn('categories.id', function ($query) {
                $this->queryInactiveCategories($query);
            });

        if ($shopId) $query = $query->where('categories.shop_id', $shopId);
        if ($languageId) $query = $query->where('categories.language_id', $languageId);

        $categories = $query->get();

        return $categories;
    }

    public function getInactiveCategories($select = '*', $shopId = null)
    {
        $query = $this->queryInactiveCategories($this->db->table('categories'));

        if ($shopId) $query = $query->where('categories.shop_id', $shopId)->where('active', '=', 1);

        $query->select($select);

        return $query->get();
    }

    /**
     * @param array    $select
     * @param Category $category
     */
    public function getCategoryAndChildren($select = '*', Category $category)
    {
        $query = $this->db->table('categories')
            ->select($select)
            ->join('category_translations', 'categories.id', '=', 'category_translations.category_id')
            ->whereNotIn('categories.id', function ($query) {
                $this->queryInactiveCategories($query);
            });

        if ($category->shop_id) $query = $query->where('categories.shop_id', $category->shop_id);
        if ($category->shop_id) $query = $query->where('categories.shop_id', $category->shop_id);

        return $query->get();
    }
}