<?php

namespace App\Models\Stock;

use App\Scopes\CompanyScope;
use Illuminate\Support\Facades\DB;
use App\Models\Stock\StockinHistory;
use Illuminate\Database\Eloquent\Model;

class ProductTracker extends Model
{
    protected static function booted()
    {
        static::addGlobalScope(new CompanyScope);
    }

    protected $table = 'products';

    protected $appends = ['receiveQty', 'transferedQty', 'averageQty'];

    /**
     * @var $from
     */
    private $from;

    /**
     * @var $from
     */
    private $to;

    public function __construct()
    {
        $this->from = date('Y-m-d');
        if (!empty($from = \request()->get('from'))) {
            $this->from = $from;
        }
        $this->to = \request()->get('to');
    }

    /**
     * @return BelongsTo
     */
    public function unit()
    {
        return $this->belongsTo(Unit::class, 'id')
                    ->select('units.id', 'units.name')
                    ->withTrashed();
    }

        /**
     * @return BelongsTo
     */
    public function category()
    {
        return $this->belongsTo(ProductCategory::class, 'category_id')
                    ->select('product_categories.id', 'product_categories.name')
                    ->withTrashed();
    }

   /**
     * @return BelongsTo
     */
    public function expiry()
    {
        return $this->belongsTo(StockinHistory::class, "id", 'product_id')
                    ->select('product_id', 'expiration_date')
                    ->withTrashed();
    }
    
    public function getReceiveQtyAttribute()
    {
        $result = DB::table('stockin_histories')
                      ->selectRaw('COALESCE(SUM(stockin_histories.quantity), 0) AS total')
                      ->join('stock_receives', 'stockin_histories.stockin_id', '=', 'stock_receives.id')
                      ->where('stockin_histories.product_id', $this->id)
                      ->where('stock_receives.company_id', $this->company_id)
                      ->whereNull('stockin_histories.deleted_at');
        if (empty($this->to)) {
            $result->where('stock_receives.date_received', '=', $this->from);
        } else {
            $result->where('stock_receives.date_received', '>=', $this->from)
                   ->where('stock_receives.date_received', '<=', $this->to);
        }
        return $result->first()->total;
    }

    public function getTransferedQtyAttribute()
    {
        $result = DB::table('sale_items')
                      ->selectRaw('COALESCE(SUM(sale_items.quantity), 0) AS total')
                      ->join('sales', 'sale_items.sale_id', '=', 'sales.id')
                      ->where('sale_items.item_id', $this->id)
                      ->whereNull('sale_items.deleted_at');
        if (empty($this->to)) {
            $result->where('sales.committed_date', '=', $this->from);
        } else {
            $result->where('sales.committed_date', '>=', $this->from)
                   ->where('sales.committed_date', '<=', $this->to);
        }
        return $result->first()->total;
    }

    public function getAverageQtyAttribute()
    {
        $result = DB::table('sales')
                        ->selectRaw(DB::raw('COALESCE(SUM(sale_items.quantity), 0) / COUNT(DISTINCT DATE_FORMAT(sales.committed_date, "%Y-%m")) AS average_quantity'))
                        ->selectRaw(DB::raw('COALESCE(SUM(sale_items.quantity), 0) AS total_quantities'))
                        ->selectRaw(DB::raw('COALESCE(COUNT(sales.id), 1) AS transactions_nbers'))
                        ->selectRaw(DB::raw('COUNT(DISTINCT DATE_FORMAT(sales.committed_date, "%Y-%m")) AS months'))
                        ->join('sale_items', 'sales.id', '=', 'sale_items.sale_id')
                        ->where('sale_items.item_id', $this->id)
                        ->whereNull('sale_items.deleted_at');
        if (empty($this->to)) {
            $result->where('sales.committed_date', '=', $this->from);
        } else {
            $result->where('sales.committed_date', '>=', $this->from)
                   ->where('sales.committed_date', '<=', $this->to);
        }
        return $result->first();
    }

}
