<?php

namespace App\Models;

use App\Models\BaseModel;
use Illuminate\Database\Eloquent\SoftDeletes;

class Item extends BaseModel
{
    use SoftDeletes;

    protected $fillable = [
        'company_id','parent_id','category_id','code','name','description','barcode',
        'type','uom_id','default_warehouse_id',
        'inventory_account_id','cogs_account_id','sales_account_id','purchase_account_id',
        'reorder_level','opening_stock','valuation_method','tracking_type',
        'is_active','is_sold','is_purchased',
    ];

    protected $casts = [
        'is_active'    => 'boolean',
        'is_sold'      => 'boolean',
        'is_purchased' => 'boolean',
        'reorder_level'=> 'decimal:4',
        'opening_stock'=> 'decimal:4',
    ];

    // ── Relationships ─────────────────────────────────────────────────────────
    public function company()          { return $this->belongsTo(Company::class); }
    public function parent()           { return $this->belongsTo(Item::class,'parent_id'); }
    public function children()         { return $this->hasMany(Item::class,'parent_id'); }
    public function category()         { return $this->belongsTo(ItemCategory::class); }
    public function uom()              { return $this->belongsTo(Uom::class); }
    public function defaultWarehouse() { return $this->belongsTo(Warehouse::class, 'default_warehouse_id'); }
    public function inventoryAccount() { return $this->belongsTo(Account::class, 'inventory_account_id'); }
    public function cogsAccount()      { return $this->belongsTo(Account::class, 'cogs_account_id'); }
    public function salesAccount()     { return $this->belongsTo(Account::class, 'sales_account_id'); }
    public function purchaseAccount()  { return $this->belongsTo(Account::class, 'purchase_account_id'); }

    public function priceLevels()
    {
        return $this->hasMany(ItemPriceLevel::class);
    }

    public function serialNumbers()
    {
        return $this->hasMany(ItemSerialNumber::class);
    }

    public function lotNumbers()
    {
        return $this->hasMany(ItemLotNumber::class);
    }

    public function stockMovements()
    {
        return $this->hasMany(StockMovement::class);
    }

    // ── Scopes ────────────────────────────────────────────────────────────────
    public function scopeActive($q)     { return $q->where('is_active', true); }
    public function scopeSold($q)       { return $q->where('is_sold', true); }
    public function scopePurchased($q)  { return $q->where('is_purchased', true); }
    public function scopeInventory($q)  { return $q->where('type', 'inventory'); }

    // ── Helpers ───────────────────────────────────────────────────────────────
    public function isInventoryType(): bool
    {
        return $this->type === 'inventory';
    }

    public function hasSerialTracking(): bool
    {
        return in_array($this->tracking_type, ['serial', 'both']);
    }

    public function hasLotTracking(): bool
    {
        return in_array($this->tracking_type, ['lot', 'both']);
    }

    /**
     * Get price for a given price level as of a given date.
     * Returns null if no price configured for that level.
     */
    public function getPriceForLevel(int $priceLevelId, string $date = null): ?float
    {
        $date  = $date ?? now()->toDateString();
        $price = ItemPriceLevel::withoutGlobalScopes()
            ->where('company_id', $this->company_id)
            ->where('item_id', $this->id)
            ->where('price_level_id', $priceLevelId)
            ->where(fn($q) => $q->whereNull('effective_date')->orWhere('effective_date', '<=', $date))
            ->orderByDesc('effective_date')
            ->value('unit_price');

        return $price !== null ? (float) $price : null;
    }

    /**
     * Get current weighted average cost from stock movements.
     */
    public function getCurrentAvgCost(int $warehouseId = null): float
    {
        $query = StockMovement::withoutGlobalScopes()
            ->where('company_id', $this->company_id)
            ->where('item_id', $this->id);

        if ($warehouseId) {
            $query->where('warehouse_id', $warehouseId);
        }

        $latest = $query->orderByDesc('movement_date')->orderByDesc('id')->first();
        return $latest ? (float) $latest->running_avg_cost : 0.0;
    }

    /**
     * Get current stock quantity.
     * If warehouseId given: returns running_qty from latest movement in that warehouse.
     * If no warehouseId: sums the latest running_qty per warehouse (total stock across all warehouses).
     */
    public function getCurrentStock(int $warehouseId = null): float
    {
        if ($warehouseId) {
            // Get latest movement for this item in this warehouse
            $latest = StockMovement::withoutGlobalScopes()
                ->where('company_id', $this->company_id)
                ->where('item_id', $this->id)
                ->where('warehouse_id', $warehouseId)
                ->orderByDesc('movement_date')
                ->orderByDesc('id')
                ->first();
            return $latest ? (float) $latest->running_qty : 0.0;
        }

        // Total across all warehouses: get the latest movement per warehouse,
        // then sum their running_qty values
        $latestPerWarehouse = StockMovement::withoutGlobalScopes()
            ->selectRaw('warehouse_id, MAX(id) as max_id')
            ->where('company_id', $this->company_id)
            ->where('item_id', $this->id)
            ->groupBy('warehouse_id')
            ->pluck('max_id');

        if ($latestPerWarehouse->isEmpty()) return 0.0;

        $total = StockMovement::withoutGlobalScopes()
            ->whereIn('id', $latestPerWarehouse)
            ->sum('running_qty');

        return (float) $total;
    }
}
