<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Nicolaslopezj\Searchable\SearchableTrait;
use Illuminate\Support\Facades\DB;

/**
 * App\Models\Category
 *
 * @property int $id
 * @property string $name
 * @property string|null $slug
 * @property int|null $parent_id
 * @property string|null $image
 * @property \Carbon\Carbon|null $created_at
 * @property \Carbon\Carbon|null $updated_at
 * @property-read \App\Models\Category|null $parent
 * @property-read \Illuminate\Database\Eloquent\Collection|\App\Models\Product[] $products
 * @property-read \Illuminate\Database\Eloquent\Collection|\App\Models\Category[] $subs
 * @method static \Illuminate\Database\Eloquent\Builder|\App\Models\Category whereCreatedAt($value)
 * @method static \Illuminate\Database\Eloquent\Builder|\App\Models\Category whereId($value)
 * @method static \Illuminate\Database\Eloquent\Builder|\App\Models\Category whereImage($value)
 * @method static \Illuminate\Database\Eloquent\Builder|\App\Models\Category whereName($value)
 * @method static \Illuminate\Database\Eloquent\Builder|\App\Models\Category whereParentId($value)
 * @method static \Illuminate\Database\Eloquent\Builder|\App\Models\Category whereSlug($value)
 * @method static \Illuminate\Database\Eloquent\Builder|\App\Models\Category whereUpdatedAt($value)
 * @mixin \Eloquent
 */
class Category extends Model
{
    use SearchableTrait;

    protected $guarded = [];

    protected $searchable = [
        'columns' => [
            'categories.name' => 10
        ]
    ];

    public function getRouteKeyName()
    {
        return 'slug';
    }

    public function subs()
    {
        return $this->hasMany('App\Models\Category', 'parent_id');
    }

    public function parent()
    {
        return $this->belongsTo('App\Models\Category', 'parent_id');
    }

    public function products()
    {
        return $this->hasMany(Product::class, 'category_id');
    }

    public function companies()
    {
        $products_companies = Product::published()->where('category_id', $this->id)->distinct()->pluck('company_id')->all();
        $companies = Company::published()->whereIn('id', $products_companies)->get();
        return $companies;
    }

    public function brands()
    {
        $products_brands = Product::where('category_id', $this->id)->distinct()->pluck('brand_id')->all();
        $brands = Brand::whereIn('id', $products_brands)->get();
        return $brands;
    }

    public function attributes()
    {
        return $this->hasMany(Attribute::class);
    }

    public function scopeLevel($query, $level)
    {
        return $query->where('level', $level);
    }

    public static function childs($parent ,$level = null)
    {
        $query = "
            SELECT * FROM (SELECT * FROM categories ORDER BY parent_id, id) AS products_sorted, (SELECT @pv := ". $parent .") AS initialisation
            WHERE find_in_set(parent_id, @pv)
            AND length(@pv := concat(@pv, ',', id))
        " . (($level)?" AND level like '". $level ."'":"");
        $results = DB::select(DB::raw($query));
        
        $categories = Category::hydrate($results);
        return $categories;
    }
}
