<?php

namespace App;

use DB;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Input;
use Illuminate\Pagination\LengthAwarePaginator;
use Illuminate\Database\Eloquent\Model;

class Category extends Model
{
    //
    protected $primaryKey = 'cat_id';

    public function group() {
        return $this->belongsTo("App\CategoryGroup", 'cg_id', 'cg_id');
    }

    public function products() {
        return $this->hasMany("App\Product", 'cat_id', 'cat_id');
    }

    public function translation($lang) {
        return $this->hasOne("App\CategoryTranslation", 'cat_id', 'cat_id')->where('ct_lang', '=', $lang)->first();
    }

    public function ctranslation() {
        return $this->hasOne("App\CategoryTranslation", 'cat_id', 'cat_id')->where('ct_lang', '=', app()->getLocale());
    }

    public function translations() {
        return $this->hasMany("App\CategoryTranslation", 'cat_id', 'cat_id');
    }

    public function attributes()
    {
        return $this->belongsToMany('App\Attribute', 'attribute_category', 'cat_id', 'atr_id');
    }

    public function route($lang) {
        return $this->hasOne("App\Route", 'object_id', 'cat_id')->where('object', '=', 'Category')->where('lang', '=', $lang)->first();
    }

    // Admin methods


    public static function getALlCategoriesCT(){

        try{

            $locale = app()->getLocale();

            $data = collect(DB::select('SELECT ct.`cat_name` as name, c.`cat_id` as id
                                            FROM `categories` c
                                            INNER JOIN `category_translations` ct ON ct.`cat_id` = c.`cat_id` AND ct.`ct_lang` = :lang
                                            ORDER BY ct.`cat_name`', [':lang' => $locale]
            ))->pluck('name','id');



            return $data;

        } catch (\Illuminate\Database\QueryException $e) {
            // something went wrong with the transaction, rollback
            DB::rollback();
            handle_error($e);
        } catch (\Exception $e) {
            // something went wrong elsewhere
            DB::rollback();
            handle_error($e);
        }
    }



    public static function getALlCategories(){

        try{

            $locale = app()->getLocale();

            $data = collect(DB::select('SELECT DISTINCT cgt.`cg_name` , g.`cg_img` , c.`cat_id` , ct.`cat_name` , \'c\', c.`cat_bold`,  COUNT(p.`prod_id`) as `numOfProducts`
                                            FROM `categories` c
                                            INNER JOIN `category_groups` g ON c.`cg_id` = g.`cg_id` 
                                            INNER JOIN `category_translations` ct ON ct.`cat_id` = c.`cat_id` AND ct.`ct_lang` = :lang
                                            INNER JOIN `category_group_translations` cgt ON g.`cg_id` = cgt.`cg_id` AND cgt.`cgt_lang` = :lang
                                            LEFT JOIN `products` p ON c.`cat_id` = p.`cat_id`
                                            GROUP BY c.`cat_id`
                                            ORDER BY g.`cg_ord` , g.`cg_id` , c.`cg_order`', [':lang' => $locale]
            ));


            return $data;

        } catch (\Illuminate\Database\QueryException $e) {
            // something went wrong with the transaction, rollback
            DB::rollback();
            handle_error($e);
        } catch (\Exception $e) {
            // something went wrong elsewhere
            DB::rollback();
            handle_error($e);
        }
    }

    // Methods for frontend

    public static function getCategoriesForMegaMenu(){

        try{

            /*SELECT DISTINCT cgt.`cg_name` , g.`cg_img`, rg.`slug` as cg_slug , c.`cat_id` , ct.`cat_name` , c.`cat_bold`, r.`slug`
                                            FROM `categories` c
                                            INNER JOIN `category_groups` g ON c.`cg_id` = g.`cg_id`
                                            INNER JOIN `category_translations` ct ON ct.`cat_id` = c.`cat_id` AND ct.`ct_lang` = :lang
                                            INNER JOIN `category_group_translations` cgt ON g.`cg_id` = cgt.`cg_id` AND cgt.`cgt_lang` = :lang
                                            LEFT JOIN `routes` r ON r.`object` = 'Category' AND c.`cat_id` = r.`object_id` AND r.`lang`  = :lang
                                            LEFT JOIN `routes` rg ON rg.`object` = 'CategoryGroup' AND g.`cg_id` = rg.`object_id` AND r.`lang`  = :lang
                                            INNER JOIN `products` p ON c.`cat_id` = p.`cat_id`
                                            WHERE  p.`prod_status`  = 1
                                            ORDER BY g.`cg_ord` , g.`cg_id` , c.`cg_order`*/

            $locale = app()->getLocale();

            $data = collect(DB::select('SELECT DISTINCT cgt.`cg_name` , g.`cg_img`, c.`cat_id` , ct.`cat_name` , c.`cat_bold`, r.`slug`  
                                            FROM `categories` c 
                                            INNER JOIN `category_groups` g ON c.`cg_id` = g.`cg_id`  
                                            INNER JOIN `category_translations` ct ON ct.`cat_id` = c.`cat_id` AND ct.`ct_lang` = :lang 
                                            INNER JOIN `category_group_translations` cgt ON g.`cg_id` = cgt.`cg_id` AND cgt.`cgt_lang` = :lang 
                                            LEFT JOIN `routes` r ON r.`object` = \'Category\' AND c.`cat_id` = r.`object_id` AND r.`lang`  = :lang 
                                            INNER JOIN `products` p ON c.`cat_id` = p.`cat_id`
                                            WHERE  p.`prod_status`  = 1 
                                            ORDER BY g.`cg_ord` , g.`cg_id` , c.`cg_order`', [':lang' => $locale]
            ));

            return $data;

        } catch (\Illuminate\Database\QueryException $e) {
            // something went wrong with the transaction, rollback
            DB::rollback();
            handle_error($e);
        } catch (\Exception $e) {
            // something went wrong elsewhere
            DB::rollback();
            handle_error($e);
        }
    }


    public static function getFeatCategoriesBrandsForMenu(){

        try{

            $locale = app()->getLocale();

            /*$data = collect(DB::select('SELECT DISTINCT c.`cat_id` , ct.`cat_name`, ct.`cat_slug` , b.`brand_name`, b.`brand_slug` , r.`slug`
											FROM `categories` c
											INNER JOIN `category_translations` ct ON ct.`cat_id` = c.`cat_id` AND ct.`ct_lang` = :lang
											INNER JOIN `categoriesBrandsActive` cba ON c.`cat_id` = cba.`cat_id` 
											INNER JOIN `brands` b ON cba.`brand_id` = b.`brand_id` 
                                            INNER JOIN `routes` r ON r.`object` = \'Category\' AND r.`object_id` = c.`cat_id` AND r.`lang`  = :lang
											WHERE c.`cat_feat`
											ORDER BY c.`cg_order`, c.`cat_feat_order`, c.`cat_id` ', [':lang' => $locale]*/
            $data = collect(DB::select('SELECT DISTINCT c.`cat_id` , ct.`cat_name`,  r.`slug`
											FROM `categories` c
											INNER JOIN `category_translations` ct ON ct.`cat_id` = c.`cat_id` AND ct.`ct_lang` = :lang
											INNER JOIN `categoriesBrandsActive` cba ON c.`cat_id` = cba.`cat_id` 
                                            INNER JOIN `routes` r ON r.`object` = \'Category\' AND r.`object_id` = c.`cat_id` AND r.`lang`  = :lang
											WHERE c.`cat_feat`
											ORDER BY c.`cg_order`, c.`cat_feat_order`, c.`cat_id` ', [':lang' => $locale]
            ));

            return $data;

        } catch (\Illuminate\Database\QueryException $e) {
            // something went wrong with the transaction, rollback
            DB::rollback();
            handle_error($e);
        } catch (\Exception $e) {
            // something went wrong elsewhere
            DB::rollback();
            handle_error($e);
        }
    }


    public function getCategoryProducts(){

    try{

        $basesql = config('queries.defaultProductsBaseSql');
        $productsPerPage = config('queries.productsPerPage');

        $sql = $basesql.' WHERE p.prod_status = 1';
        $sql  .= ' AND p.`cat_id` = :catid ORDER BY p.`prod_price` ASC ';
        $pars = [':catid' => $this->cat_id, ':lang' => app()->getLocale()];

        //echo $sql; exit;

        //$products = collect(DB::select($sql,$pars))->paginate($productsPerPage);
        $products = DB::select($sql,$pars);


        $products = $this->arrayPaginator($products);

        return $products;

    } catch (\Illuminate\Database\QueryException $e) {
        handle_error($e);
    } catch (\Exception $e) {
        handle_error($e);
    }

}


    public function getCategoryBrandProducts($brandid){

        try{

            $basesql = config('queries.defaultProductsBaseSql');
            $productsPerPage = config('queries.productsPerPage');
            $sql = $basesql.' WHERE p.prod_status = 1';
            $sql  = $sql.' AND p.`cat_id` = :catid AND p.`brand_id` = :brandid ORDER BY p.`prod_price` ASC ';
            $pars = [':catid' => $this->cat_id, ':brandid' => $brandid];
            //$products = collect(DB::select($sql,$pars))->paginate($productsPerPage);
            $products = DB::select($sql,$pars);


            $products = $this->arrayPaginator($products);

            return $products;

        } catch (\Illuminate\Database\QueryException $e) {
            handle_error($e);
        } catch (\Exception $e) {
            handle_error($e);
        }

    }


    // Filters methods

    public function getCategoryBrandsData(){


        try{

            $data = collect(DB::select('SELECT b.`brand_id` , b.`brand_name` 
                                              FROM `categoriesBrandsActive` cb FORCE INDEX (`catBrandUnique`)
                                              INNER JOIN `brands` b FORCE INDEX (`PRIMARY`) ON cb.`brand_id` = b.`brand_id` 
                                              WHERE cb.`cat_id` = :catid',
                [':catid' => $this->cat_id]
            ));

            return $data;

        } catch (\Illuminate\Database\QueryException $e) {
            echo handle_error($e);
        } catch (\Exception $e) {
            echo handle_error($e);
        }

        try{


            $stmt = $this->registry->db->prepare("SELECT b.`brand_id` , b.`brand_name` 
												  FROM `categoriesBrandsActive` cb FORCE INDEX (`catBrandUnique`)
												  INNER JOIN `brands` b FORCE INDEX (`PRIMARY`) ON cb.`brand_id` = b.`brand_id` 
												  WHERE cb.`cat_id` = :catid");
            $id = $this->id;
            $stmt->bindParam(':catid', $id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt->fetchALL(PDO::FETCH_ASSOC);



        }
        catch (PDOException $e){
            handleError($e->getMessage());
        }
        catch (Exception $e){
            handleError($e->getMessage());
        }

    }


    public function getCategoryAttributesData(){


        try{

            $data = collect(DB::select('SELECT DISTINCT at.atr_displ_name AS atrname, a.atr_id AS atrid, av.atr_val_id AS valid, 
                                              avt.atr_value AS label
                                              FROM attribute_category ca
                                              FORCE INDEX ( `cat_id` ) 
                                              INNER JOIN attributes a
                                              FORCE INDEX ( `PRIMARY` ) ON ca.atr_id = a.atr_id
                                              INNER JOIN `attribute_translations` at ON a.atr_id = at.atr_id AND at.lang = :lang
                                              INNER JOIN attribute_values av
                                              FORCE INDEX ( `atr_id` ) ON ca.atr_id = av.atr_id
                                              INNER JOIN  `attribute_value_translations` avt ON av.atr_val_id = avt.atr_val_id AND avt.lang = :lang
                                              INNER JOIN product_attribute_values pav
                                              FORCE INDEX ( `atr_val_id` ) ON av.atr_val_id = pav.atr_val_id
                                              AND ca.atr_id = pav.atr_id
                                              INNER JOIN products p
                                              FORCE INDEX ( `PRIMARY` ) ON pav.prod_id = p.prod_id AND p.`cat_id` = ca.`cat_id`
                                              WHERE p.prod_status  = 1
                                              AND ca.cat_id = :catid
                                              ORDER BY ca.cat_atr_ord, av.atr_id, CAST( avt.atr_value AS UNSIGNED INT ) ASC , avt.atr_value ASC ',
                [':catid' => $this->cat_id, ':lang' => app()->getLocale()]
            ));

            return $data;



        } catch (\Illuminate\Database\QueryException $e) {
            echo handle_error($e);
        } catch (\Exception $e) {
            echo handle_error($e);
        }

    }


    public function getCategoryMinMaxPrice(){

        try{

            $data = collect(DB::select('SELECT MIN( `prod_price` ) AS `pmin` , MAX( `prod_price` ) AS `pmax` 
                                              FROM `products` 
                                              FORCE INDEX ( `catActivePrice` ) 
                                              WHERE `prod_status` = 1
                                              AND `cat_id` = :catid',
                                              [':catid' => $this->cat_id]
                                        ))->first();

            return $data;

        } catch (\Illuminate\Database\QueryException $e) {
            echo handle_error($e);
        } catch (\Exception $e) {
            echo handle_error($e);
        }

    }





    // Helper method


    public function arrayPaginator($array)
    {
        $request = request();
        $page = Input::get('page', 1);
        $perPage = config('queries.productsPerPage');
        $offset = ($page * $perPage) - $perPage;

        return new LengthAwarePaginator(array_slice($array, $offset, $perPage, true), count($array), $perPage, $page,
            ['path' => $this->cat_slug, 'query' => $request->query()]);
    }



}
