# Task 10 — Stock Valuation & Reorder Reports

## Goal
Build stock valuation report and reorder (low stock) alert dashboard widget.
Average Cost method was implemented in Task 09 — this task builds the reporting layer.

---

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

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

class StockValuationService
{
    /**
     * Stock valuation report — total inventory value by item/warehouse.
     */
    public function getValuationReport(array $filters = []): Collection
    {
        $query = StockBalance::query()
            ->join('items', 'items.id', '=', 'stock_balances.item_id')
            ->join('warehouses', 'warehouses.id', '=', 'stock_balances.warehouse_id')
            ->leftJoin('item_categories', 'item_categories.id', '=', 'items.category_id')
            ->select([
                'stock_balances.item_id',
                'stock_balances.warehouse_id',
                DB::raw("JSON_UNQUOTE(JSON_EXTRACT(items.name, '$.en')) as item_name_en"),
                DB::raw("JSON_UNQUOTE(JSON_EXTRACT(items.name, '$.ar')) as item_name_ar"),
                'items.code as item_code',
                'items.type as item_type',
                DB::raw("JSON_UNQUOTE(JSON_EXTRACT(warehouses.name, '$.en')) as warehouse_name"),
                DB::raw("JSON_UNQUOTE(JSON_EXTRACT(item_categories.name, '$.en')) as category_name"),
                'stock_balances.quantity',
                'stock_balances.average_cost',
                'stock_balances.total_value',
                'items.reorder_level',
                'items.sale_price',
                DB::raw('stock_balances.quantity * items.sale_price as potential_revenue'),
            ])
            ->where('items.type', 'stock')
            ->where('stock_balances.quantity', '>', 0);

        // Apply filters
        if (!empty($filters['warehouse_id'])) {
            $query->where('stock_balances.warehouse_id', $filters['warehouse_id']);
        }
        if (!empty($filters['category_id'])) {
            $query->where('items.category_id', $filters['category_id']);
        }
        if (!empty($filters['branch_id'])) {
            $query->where('warehouses.branch_id', $filters['branch_id']);
        }

        return $query->orderBy('item_code')->get();
    }

    /**
     * Summary totals for the valuation report.
     */
    public function getValuationTotals(array $filters = []): array
    {
        $report = $this->getValuationReport($filters);
        return [
            'total_items'     => $report->count(),
            'total_value'     => $report->sum('total_value'),
            'total_potential' => $report->sum('potential_revenue'),
        ];
    }

    /**
     * Low stock items for dashboard widget.
     */
    public function getLowStockReport(array $filters = []): Collection
    {
        $query = StockBalance::query()
            ->join('items', 'items.id', '=', 'stock_balances.item_id')
            ->join('warehouses', 'warehouses.id', '=', 'stock_balances.warehouse_id')
            ->select([
                'items.code', 'items.name', 'items.reorder_level', 'items.reorder_qty',
                'warehouses.name as warehouse_name',
                'stock_balances.quantity as current_qty',
                'stock_balances.average_cost',
                DB::raw('items.reorder_level - stock_balances.quantity as shortage'),
            ])
            ->where('items.type', 'stock')
            ->where('items.reorder_level', '>', 0)
            ->whereColumn('stock_balances.quantity', '<=', 'items.reorder_level')
            ->orderByRaw('shortage DESC');

        if (!empty($filters['branch_id'])) {
            $query->where('warehouses.branch_id', $filters['branch_id']);
        }

        return $query->get();
    }

    /**
     * Stock movement history for an item.
     */
    public function getItemMovementHistory(int $itemId, array $filters = []): LengthAwarePaginator
    {
        return StockMovement::where('item_id', $itemId)
            ->with(['warehouse', 'item'])
            ->when(!empty($filters['warehouse_id']), fn($q) => $q->where('warehouse_id', $filters['warehouse_id']))
            ->when(!empty($filters['date_from']), fn($q) => $q->whereDate('movement_date', '>=', $filters['date_from']))
            ->when(!empty($filters['date_to']), fn($q) => $q->whereDate('movement_date', '<=', $filters['date_to']))
            ->orderByDesc('movement_date')
            ->paginate(20);
    }
}
```

---

## Controller: `app/Http/Controllers/Admin/Inventory/StockValuationController.php`

```php
public function valuation(StockReportFilterRequest $request): View
{
    $filters = $request->validated();
    $report  = $this->valuationService->getValuationReport($filters);
    $totals  = $this->valuationService->getValuationTotals($filters);
    $options = $this->getFilterOptions();
    return view('dashboard.admin.inventory.reports.valuation', compact('report', 'totals', 'options', 'filters'));
}

public function lowStock(Request $request): View
{
    $filters  = $request->only(['branch_id']);
    $items    = $this->valuationService->getLowStockReport($filters);
    return view('dashboard.admin.inventory.reports.low-stock', compact('items', 'filters'));
}
```

---

## Views

### Valuation Report: `inventory/reports/valuation.blade.php`
- Filter bar: warehouse, category, branch
- Table: item code, name, warehouse, qty, avg cost, total value, potential revenue
- Footer totals row
- Export to Excel button (wired to `maatwebsite/laravel-excel` when installed)

### Low Stock Report: `inventory/reports/low-stock.blade.php`
- Table: item code, name, warehouse, current qty, reorder level, shortage, suggested reorder qty
- Color coding: red if qty=0, orange if below reorder
- "Create PO" quick action per row

---

## Dashboard Widget

Add to the main dashboard (DashboardAnalyticsService):
```php
public function getLowStockCount(): int
{
    return StockBalance::join('items', 'items.id', '=', 'stock_balances.item_id')
        ->where('items.type', 'stock')
        ->where('items.reorder_level', '>', 0)
        ->whereColumn('stock_balances.quantity', '<=', 'items.reorder_level')
        ->count();
}

public function getTotalInventoryValue(): float
{
    return (float) StockBalance::join('items', 'items.id', '=', 'stock_balances.item_id')
        ->where('items.type', 'stock')
        ->sum('stock_balances.total_value');
}
```

---

## Permissions
```
inventory.reports.valuation.view
inventory.reports.low_stock.view
```