# Inventory Valuation — How It Works

This explains the two separate systems that together describe "how much stock is held and what it's worth."

---

## The Two Tables

### 1. `inventories_product_quantities` — Stock Levels

What Cydekick uses to answer "how many do we have?"

| Column | Purpose |
|--------|---------|
| `product_id` | FK to `products_products` |
| `location_id` | FK to `inventories_locations` (NOT warehouse_id) |
| `quantity` | On-hand quantity |
| `reserved_quantity` | Held for open orders |
| `company_id` | Company that owns the stock |
| `incoming_at` | Timestamp of last stock change |

**Available quantity** shown in the UI = `quantity - reserved_quantity`.

This is what the `ProductQuantityObserver` watches and what `SyncProductToChannels` pushes to Shopify.

---

### 2. `inventory_valuations` — Financial Valuation

What Cydekick uses to answer "what is that stock worth?"

| Column | Purpose |
|--------|---------|
| `product_id` | FK to `products_products` |
| `location_id` | FK to `inventories_locations` |
| `company_id` | Company |
| `currency_id` | FK to `currencies` (GBP = id 143) |
| `creator_id` | User who created the row |
| `quantity` | Running quantity (mirrors product_quantities) |
| `average_cost` | Weighted average cost per unit |
| `total_value` | `quantity × average_cost` |

This is what the **Dashboard → Total Inventory Value** widget reads (`InventoryValueWidget`).

The **Stock / Valuation tab** per-product (Inventory → Products → [product] → Stock / Valuation) shows `average_cost` as "Avg Cost" and `total_value` as "Total Value".

---

## How Valuations Are Normally Updated

**File:** `plugins/webkul/inventory-valuation/src/Observers/MoveLineObserver.php`

Fires when a `MoveLine` (stock move line) transitions to `state = DONE`.

### Incoming stock (receipt from supplier)
Recalculates a **weighted average cost**:
```
newAvg = ((existingQty × existingAvg) + (incomingQty × unitCost)) / totalQty
```
Cost priority in `resolveCost()`:
1. `move_line.unit_cost` if set explicitly
2. Purchase order line `price_unit` (after discount)
3. `products_products.cost` (standard cost fallback)

### Outgoing stock (delivery to customer, scrap)
Reduces `quantity` by the shipped qty. `average_cost` does NOT change on outgoing — it holds the historical average.

### Transfers (internal location → internal location)
Runs processOutgoing on source then processIncoming on destination, so value moves with the stock.

### Audit trail
Every change also creates a row in `inventory_valuation_lines` with `quantity_change`, `unit_cost`, `value_change`, `qty_after`, `avg_cost_after`, `value_after`. This is the ledger shown in the Valuation resource list.

---

## Warehouse → Location Mapping

The dashboard widget filters by `location.type = INTERNAL` and `location.is_scrap = false`. The location to use for a warehouse is its `lot_stock_location_id`:

```
inventories_warehouses.lot_stock_location_id → inventories_locations.id
```

**Known locations:**

| Warehouse | company_id | location_id |
|-----------|-----------|-------------|
| Allmakes4x4 (Warehouse) | 2 | 34 |
| Somerset4x4 (Warehouse) | 2 | 48 |
| Tool365 (Warehouse) | 3 | 56 |
| Warehouse A | 2 | 62 |

> **Warning:** Multiple warehouses share `company_id = 2`. Never query by `company_id` alone to find Somerset4x4 — query by name: `WHERE name LIKE 'Somerset4x4%'`.

---

## When Direct DB Writes Are Needed (bypassing stock moves)

Stock imports, hacked-data recovery, and bulk corrections write directly to `inventories_product_quantities` to avoid triggering the `ProductQuantityObserver` chain (~500 `SyncProductToChannels` jobs dispatched simultaneously).

**When you do this, you MUST also write to `inventory_valuations` manually**, otherwise:
- Dashboard "Total Inventory Value" stays at £0
- "Avg Cost" column shows "—" in the Stock/Valuation tab
- "Total Value" per product shows £0

The `stock:import-take` Artisan command handles both writes.

### Manual fix (tinker one-liner)

If a product's valuation is wrong after a direct import:

```php
$product = \Webkul\Product\Models\Product::where('sku', 'THE_SKU')->firstOrFail();
$locationId = 48; // Somerset4x4 lot_stock_location_id
$qty = \Illuminate\Support\Facades\DB::table('inventories_product_quantities')
    ->where('product_id', $product->id)->where('location_id', $locationId)
    ->value('quantity');

\Illuminate\Support\Facades\DB::table('inventory_valuations')->updateOrInsert(
    ['product_id' => $product->id, 'location_id' => $locationId],
    [
        'company_id'   => 2,
        'currency_id'  => 143, // GBP
        'creator_id'   => 1,
        'quantity'     => $qty,
        'average_cost' => $product->cost,
        'total_value'  => round($qty * $product->cost, 4),
        'updated_at'   => now(),
        'created_at'   => now(),
    ]
);
echo "Done: qty={$qty} cost={$product->cost} value=" . round($qty * $product->cost, 2) . PHP_EOL;
```

### Bulk recalculate Somerset4x4 valuation from product costs

```php
$locationId = 48;
DB::table('inventories_product_quantities as pq')
    ->join('products_products as p', 'p.id', '=', 'pq.product_id')
    ->where('pq.location_id', $locationId)
    ->where('pq.quantity', '>', 0)
    ->where('p.cost', '>', 0)
    ->select('pq.product_id', 'pq.quantity', 'p.cost', 'p.company_id')
    ->orderBy('pq.product_id')
    ->chunk(200, function ($rows) use ($locationId) {
        foreach ($rows as $r) {
            DB::table('inventory_valuations')->updateOrInsert(
                ['product_id' => $r->product_id, 'location_id' => $locationId],
                [
                    'company_id'   => $r->company_id,
                    'currency_id'  => 143,
                    'creator_id'   => 1,
                    'quantity'     => $r->quantity,
                    'average_cost' => $r->cost,
                    'total_value'  => round($r->quantity * $r->cost, 4),
                    'updated_at'   => now(),
                    'created_at'   => now(),
                ]
            );
        }
    });
```

---

## Key Files

| File | Purpose |
|------|---------|
| `plugins/webkul/inventory-valuation/src/Observers/MoveLineObserver.php` | Recalculates valuation on every completed stock move |
| `plugins/webkul/inventory-valuation/src/Models/Valuation.php` | `inventory_valuations` model |
| `plugins/webkul/inventory-valuation/src/Models/ValuationLine.php` | `inventory_valuation_lines` audit ledger model |
| `plugins/webkul/inventory-valuation/src/Filament/Widgets/InventoryValueWidget.php` | Dashboard "Total Inventory Value" widget |
| `plugins/webkul/inventory-valuation/src/Filament/Clusters/ValuationCluster/Resources/ValuationResource.php` | Valuation list/detail UI |
| `app/Console/Commands/ImportStockTake.php` | Bulk import that writes both tables |

---

## Gotchas

**`average_cost` does not update on outgoing stock.** If you sell 10 units bought at £5 and 10 at £6, the average (£5.50) stays even after selling everything. It only resets if the quantity goes to zero and new stock comes in at a different cost.

**No `inventory_valuation_lines` rows for direct imports.** The audit trail is empty for anything written via tinker or the import command. The valuation total will be correct but there's no history of how it got there.

**"Avg Cost" stays "—" until the first real stock move.** Even if you seed `inventory_valuations` correctly, the per-product Stock/Valuation tab shows "—" for Avg Cost if it reads from the valuation lines ledger rather than the summary row. The dashboard total (which reads the summary row directly) will still be correct.