<?php

/**
 *
 *
 * @author      Komma <info@komma.pro>
 * @copyright   (c) 2012-2016, Komma
 */

namespace App\KommaApp\Kms\Core\SearchIndex;

use Illuminate\Database\Query\Builder as QueryBuilder;
use Illuminate\Database\DatabaseManager;
use App\KommaApp\Categories\Models\Category;

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

    function __construct(DatabaseManager $db)
    {
        $this->db = $db;
    }

    public function getAllRootCategories(array $select = ['*'])
    {
        return $this->db->table('categories')
            ->where('lft', '=', '1')
            ->select($select)
            ->get();
    }

    public function getCategoryTranslations(Category $category, array $select = ['*'])
    {
        return $this->db->table('category_translations')
            ->join('categories', 'categories.id', '=', 'category_translations.category_id')
            ->where('categories.id', '=', $category->id)
            ->select($select)
            ->get();
    }

    public function getProductTranslationsInCategory(Category $category, array $select = ['*'])
    {
        return $this->db->table('product_translations')
            ->join('products_sites', 'products_sites.id', '=', 'product_translations.products_site_id')
            ->join('products', 'products.id', '=', 'products_sites.product_id')
            ->join('categories_products_sites', 'categories_products_sites.product_site_id', '=', 'products_sites.id')
            ->where('categories_products_sites.category_id', '=', $category->id)
            ->select($select)
            ->get();
    }

    public function getProductTranslationsWithProductId($productId, array $select = ['*'])
    {
        return $this->db->table('products')
            ->join('products_sites', 'products.id', '=', 'products_sites.product_id')
            ->join('product_translations', 'products_sites.id', '=', 'product_translations.products_site_id')
            ->where('products.id', '=', $productId)
            ->select($select)
            ->get();
    }

    public function getCategoryTranslationsHierarchically(Category $category, array $select = ['*'])
    {
        return $this->db->table('category_translations')
            ->join('categories', 'categories.id', '=', 'category_translations.category_id')
            ->where('categories.lft', '>=', $category->lft)
            ->where('categories.rgt', '<=', $category->rgt)
            ->where('categories.tree', '=', $category->tree)
            ->where('categories.site_id', '=', $category->site_id)
            ->select($select)
            ->get();
    }

    public function getActiveCategoryTranslationsHierarchically(Category $category, array $select = ['*'])
    {
        return $this->db->table('category_translations')
            ->join('categories', 'categories.id', '=', 'category_translations.category_id')
            ->join('routes', function($join){
                $join->on('category_translations.id', '=', 'routes.routeable_id')
                    ->where('routes.routeable_type', '=', 'KommaApp\Categories\Models\CategoryTranslation');
            })
            ->where('categories.lft', '>=', $category->lft)
            ->where('categories.rgt', '<=', $category->rgt)
            ->where('categories.tree', '=', $category->tree)
            ->where('categories.site_id', '=', $category->site_id)
            ->whereNotIn('categories.id', function($query){
                $query = $this->queryInactiveCategories($query);
            })
            ->groupBy('category_translations.id') // QnD: group the routes and images
            ->select($select)
            ->get();
    }

    public function getProductTranslationsInCategoryHierarchically(Category $category, array $select = ['*'])
    {
        return $this->db->table('product_translations')
            ->join('products_sites', 'products_sites.id', '=', 'product_translations.products_site_id')
            ->join('products', 'products.id', '=', 'products_sites.product_id')
            ->join('categories_products_sites', 'categories_products_sites.product_site_id', '=', 'products_sites.id')
            ->join('categories', 'categories.id', '=', 'categories_products_sites.category_id')
            ->where('categories.lft', '>=', $category->lft)
            ->where('categories.rgt', '<=', $category->rgt)
            ->where('categories.tree', '=', $category->tree)
            ->where('categories.site_id', '=', $category->site_id)
            ->select($select)
            ->get();
    }

    public function getProductTranslationsInActiveCategoriesHierarchically(Category $category, array $select = ['*'])
    {
        return $this->db->table('product_translations')
            ->join('products_sites', 'products_sites.id', '=', 'product_translations.products_site_id')
            ->join('products', 'products.id', '=', 'products_sites.product_id')
            ->join('categories_products_sites', 'categories_products_sites.product_site_id', '=', 'products_sites.id')
            ->join('categories', 'categories.id', '=', 'categories_products_sites.category_id')
            ->join('routes', function($join){
                $join->on('product_translations.id', '=', 'routes.routeable_id')
                    ->where('routes.routeable_type', '=', 'KommaApp\Products\Models\SiteProductTranslation');
            })
            ->where('products_sites.active', '1')
            ->where('categories.lft', '>=', $category->lft)
            ->where('categories.rgt', '<=', $category->rgt)
            ->where('categories.tree', '=', $category->tree)
            ->where('categories.site_id', '=', $category->site_id)
            ->whereNotIn('categories.id', function($query){
                $query = $this->queryInactiveCategories($query);
            })
            ->select($select)
            ->get();
    }

    protected function queryInactiveCategories(QueryBuilder $query)
    {
        return $query->from('categories as inactive_categories')
            ->join('categories as inactive_child_categories', function($join){
                $join->on('inactive_categories.tree', '=', 'inactive_child_categories.tree');
            })
            ->where('inactive_categories.active', '=', 0)
            ->whereRaw('inactive_categories.lft <= inactive_child_categories.lft')
            ->whereRaw('inactive_categories.rgt >= inactive_child_categories.rgt')
            ->select('inactive_categories.id as id');
    }

}