# Task 09 — Stock Balance Engine

## Goal
Build the `stock_balances` table and `StockBalanceService` — the heart of inventory.
Every stock movement must go through this service to maintain accurate real-time stock levels.

---

## Migration: `stock_balances`

```php
Schema::create('stock_balances', function (Blueprint $table) {
    $table->id();
    $table->unsignedBigInteger('item_id');
    $table->unsignedBigInteger('warehouse_id');
    $table->decimal('quantity', 18, 2)->default(0);
    $table->decimal('average_cost', 18, 6)->default(0);
    $table->decimal('total_value', 18, 2)->default(0);
    $table->timestamp('last_movement_at')->nullable();
    $table->timestamps();

    $table->unique(['item_id', 'warehouse_id']);
    $table->foreign('item_id')->references('id')->on('items');
    $table->foreign('warehouse_id')->references('id')->on('warehouses');
    $table->index('item_id');
    $table->index('warehouse_id');
});
```

---

## Model: `app/Models/Inventory/StockBalance.php`

```php
namespace App\Models\Inventory;

class StockBalance extends Model
{
    protected $table = 'stock_balances';

    protected $fillable = [
        'item_id', 'warehouse_id', 'quantity', 'average_cost', 'total_value', 'last_movement_at',
    ];

    protected $casts = [
        'quantity'       => 'decimal:2',
        'average_cost'   => 'decimal:6',
        'total_value'    => 'decimal:2',
        'last_movement_at' => 'datetime',
    ];

    public function item(): BelongsTo { return $this->belongsTo(Item::class); }
    public function warehouse(): BelongsTo { return $this->belongsTo(Warehouse::class); }

    public function scopeForItem($query, int $itemId) { return $query->where('item_id', $itemId); }
    public function scopeForWarehouse($query, int $warehouseId) { return $query->where('warehouse_id', $warehouseId); }
    public function scopeLowStock($query) {
        return $query->join('items', 'items.id', '=', 'stock_balances.item_id')
            ->where('items.type', 'stock')
            ->where('items.reorder_level', '>', 0)
            ->whereColumn('stock_balances.quantity', '<=', 'items.reorder_level');
    }
}
```

---

## Service: `app/Services/Inventory/Stock/StockBalanceService.php`

This is the MOST CRITICAL service in inventory. Must be transactional.

```php
namespace App\Services\Inventory\Stock;

use App\Models\Inventory\StockBalance;
use App\Models\Inventory\StockMovement;
use App\Models\Inventory\Item;
use Illuminate\Support\Facades\DB;

class StockBalanceService
{
    /**
     * Apply a StockMovement to the balance.
     * Must be called inside a DB transaction from the caller.
     */
    public function applyMovement(StockMovement $movement): StockBalance
    {
        $balance = StockBalance::firstOrCreate(
            ['item_id' => $movement->item_id, 'warehouse_id' => $movement->warehouse_id],
            ['quantity' => 0, 'average_cost' => 0, 'total_value' => 0]
        );

        return match ($movement->movement_type) {
            'in', 'adjustment_positive' => $this->applyStockIn($balance, $movement),
            'out', 'adjustment_negative' => $this->applyStockOut($balance, $movement),
            'transfer_out' => $this->applyStockOut($balance, $movement),
            'transfer_in'  => $this->applyStockIn($balance, $movement),
            default => throw new \InvalidArgumentException("Unknown movement type: {$movement->movement_type}")
        };
    }

    private function applyStockIn(StockBalance $balance, StockMovement $movement): StockBalance
    {
        $inQty   = (float) $movement->quantity;
        $inCost  = (float) $movement->unit_cost;
        $oldQty  = (float) $balance->quantity;
        $oldCost = (float) $balance->average_cost;

        // Weighted average cost calculation
        if (($oldQty + $inQty) > 0) {
            $newAvgCost = (($oldQty * $oldCost) + ($inQty * $inCost)) / ($oldQty + $inQty);
        } else {
            $newAvgCost = $inCost;
        }

        $newQty   = $oldQty + $inQty;
        $newValue = round($newQty * $newAvgCost, 2);

        // Store average_cost on movement for audit trail
        $movement->update([
            'average_cost_before' => $oldCost,
            'average_cost_after'  => $newAvgCost,
        ]);

        $balance->update([
            'quantity'         => $newQty,
            'average_cost'     => $newAvgCost,
            'total_value'      => $newValue,
            'last_movement_at' => now(),
        ]);

        // Update item's average_cost field
        $movement->item->update(['average_cost' => $newAvgCost]);

        return $balance->fresh();
    }

    private function applyStockOut(StockBalance $balance, StockMovement $movement): StockBalance
    {
        $outQty = (float) $movement->quantity;
        $item   = $movement->item;

        // Negative stock check
        if (!$item->allow_negative_stock && (float)$balance->quantity < $outQty) {
            throw new \Exception(
                "Insufficient stock for item [{$item->code}]. Available: {$balance->quantity}, Requested: {$outQty}"
            );
        }

        $currentCost = (float) $balance->average_cost;
        $newQty      = (float) $balance->quantity - $outQty;
        $newValue    = round($newQty * $currentCost, 2);
        $cogsAmount  = round($outQty * $currentCost, 2);

        $movement->update([
            'average_cost_before' => $currentCost,
            'average_cost_after'  => $currentCost, // cost doesn't change on out
            'total_cost'          => $cogsAmount,  // used for COGS journal entry
        ]);

        $balance->update([
            'quantity'         => max(0, $newQty),
            'total_value'      => max(0, $newValue),
            'last_movement_at' => now(),
        ]);

        return $balance->fresh();
    }

    /**
     * Get current stock for an item across all warehouses.
     */
    public function getTotalStock(int $itemId): float
    {
        return (float) StockBalance::where('item_id', $itemId)->sum('quantity');
    }

    /**
     * Get stock for an item in a specific warehouse.
     */
    public function getWarehouseStock(int $itemId, int $warehouseId): float
    {
        return (float) StockBalance::where('item_id', $itemId)
            ->where('warehouse_id', $warehouseId)
            ->value('quantity') ?? 0;
    }

    /**
     * Check if sufficient stock is available.
     */
    public function hasStock(int $itemId, int $warehouseId, float $requiredQty): bool
    {
        $available = $this->getWarehouseStock($itemId, $warehouseId);
        $item = Item::find($itemId);
        if ($item?->allow_negative_stock) return true;
        return $available >= $requiredQty;
    }

    /**
     * Get all items below reorder level.
     */
    public function getLowStockItems(): Collection
    {
        return StockBalance::lowStock()
            ->select('stock_balances.*', 'items.reorder_level', 'items.reorder_qty', 'items.code as item_code')
            ->with(['item.category', 'warehouse'])
            ->get();
    }

    /**
     * Rebuild all stock balances from movement history.
     * Use only for data repair — never call in normal flow.
     */
    public function rebuildBalances(): void
    {
        DB::transaction(function () {
            StockBalance::truncate();
            StockMovement::where('posting_status', 'posted')
                ->orderBy('movement_date')
                ->orderBy('id')
                ->chunk(500, function ($movements) {
                    foreach ($movements as $movement) {
                        $this->applyMovement($movement);
                    }
                });
        });
    }
}
```

---

## Update `stock_movements` Table

Add columns to existing `stock_movements` table:

```php
Schema::table('stock_movements', function (Blueprint $table) {
    $table->enum('movement_type', [
        'in', 'out', 'adjustment', 'adjustment_positive', 'adjustment_negative',
        'transfer_in', 'transfer_out'
    ])->change();
    $table->enum('posting_status', ['pending', 'posted'])->default('posted')->after('reference_id');
    $table->unsignedBigInteger('journal_entry_id')->nullable()->after('posting_status');
    $table->decimal('average_cost_before', 18, 6)->default(0)->after('journal_entry_id');
    $table->decimal('average_cost_after', 18, 6)->default(0)->after('average_cost_before');

    $table->foreign('journal_entry_id')->references('id')->on('journal_entries')->nullOnDelete();
    $table->index('posting_status');
});
```

---

## Alert Integration

After every stock-out movement, check reorder:

```php
// In StockBalanceService::applyStockOut() — after balance update
if ($newQty <= (float) $movement->item->reorder_level && $movement->item->reorder_level > 0) {
    $this->triggerReorderAlert($movement->item, $balance);
}

private function triggerReorderAlert(Item $item, StockBalance $balance): void
{
    // Create notification using existing NotificationService
    app(NotificationService::class)->create([
        'type'    => 'low_stock',
        'title'   => "Low Stock: {$item->code}",
        'message' => "Item {$item->getTranslation('name', 'en')} has {$balance->quantity} units (reorder level: {$item->reorder_level})",
        'data'    => ['item_id' => $item->id, 'current_qty' => $balance->quantity],
    ]);
}
```

---

## Test This Task

1. Create stock balance manually via receipt posting (Task 08 must be done)
2. Post a receipt of 100 units at cost 10.00 → balance should show qty=100, avg_cost=10.00
3. Post another receipt of 50 units at cost 12.00 → balance should show:
   - qty = 150
   - avg_cost = (100×10 + 50×12) / 150 = 10.6667
4. Try to take out 200 units from an item with allow_negative_stock=false → expect exception
5. Enable allow_negative_stock=true on item → take out 200 → should succeed with negative balance