<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\DB;
use Spatie\Activitylog\Traits\LogsActivity;
use Spatie\Activitylog\LogOptions;

class Location extends Model
{
    use HasFactory, SoftDeletes;
    use LogsActivity;

    public function getActivitylogOptions(): LogOptions
    {
        return LogOptions::defaults();
   }

    protected $table = 'locations';
    protected $primaryKey = 'id';

    protected $fillable = [
        'code',
        'name',
        'address',
        'city',
        'phone',
        'longitude',
        'latitude',
        'md_id',
        'display_name',
        'is_consignment',
        'postal_code'
    ];

    public function scopeFilter(Builder $query, array $filters)
    {
        $query->when($filters['search'] ?? false, function ($query, $search) {
            return $query
                ->where('name', 'iLIKE', '%' . $search . '%')
                ->orWhere('code', 'LIKE', '%' . $search . '%');
        });
    }

    public function md()
    {
        return $this->belongsTo(Employee::class);
    }

    public function storeCategory()
    {
        return $this->hasMany(StoreCapacity::class, "location_id", "id")
            ->selectRaw("
                store_category.id,
                store_category.name,
                store_capacity.max::int,
                COALESCE(b.qty, 0)::int as qty,
                COALESCE(b.sku, 0)::int as sku,
                (CASE WHEN COALESCE(b.qty, 0) = 0 OR store_capacity.max = 0 THEN 0 ELSE (qty / max * 100) END) as percentage
            ")
            ->leftJoin("store_category", "store_capacity.store_category_id", "store_category.id")
            ->leftJoin(DB::raw("(SELECT
                c.store_category_id,
                SUM(stocks.quantity) as qty,
                count(distinct item_reference.number) as sku
            FROM (SELECT item_dimension.no, store_category_map.store_category_id FROM store_category_map
                LEFT JOIN item_dimension
                    ON (store_category_map.category1 = item_dimension.category1 OR store_category_map.category1 is null)
                    AND (store_category_map.category2 = item_dimension.category2 OR store_category_map.category2 is null)
                    AND (store_category_map.category3 = item_dimension.category3 OR store_category_map.category3 is null)
                    AND (store_category_map.category4 = item_dimension.category4 OR store_category_map.category4 is null)
                GROUP BY item_dimension.no, store_category_map.store_category_id
            ) c
            LEFT JOIN items
                ON items.number = c.no
             LEFT JOIN item_reference
                ON items.id = item_reference.item_id
            LEFT JOIN stocks
                ON stocks.item_id = item_reference.item_id AND stocks.item_variant_id = item_reference.item_variant_id
            WHERE stocks.location_id = $this->id and stocks.quantity > 0
            GROUP BY c.store_category_id
            ) b"), "store_capacity.store_category_id", "b.store_category_id");
    }
}