# Inventory Management System - Implementation Plan

## Overview

Add Purchasing, Stock In/Receiving, and Inventory Management modules to the existing Production service (port 8002). Store all data in Production database (omnia_production_db) with automatic stock deduction when orders are packed.

**User Decisions:**
- ✅ Extend Production service (not new microservice)
- ✅ Store in Production database (omnia_production_db)
- ✅ Auto-deduct stock when orders packed (real-time)
- ✅ Admin + Production Manager have full access

---

## Database Schema (omnia_production_db)

### 1. Suppliers Table
```php
Schema::create('suppliers', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->string('contact_person')->nullable();
    $table->string('email')->nullable();
    $table->string('phone')->nullable();
    $table->text('address')->nullable();
    $table->boolean('is_active')->default(true);
    $table->text('notes')->nullable();
    $table->timestamps();

    $table->index('is_active');
});
```

### 2. Purchase Orders Table
```php
Schema::create('purchase_orders', function (Blueprint $table) {
    $table->id();
    $table->string('po_number')->unique(); // Auto: PO-20250125-001
    $table->foreignId('supplier_id')->constrained()->onDelete('cascade');
    $table->enum('status', ['draft', 'confirmed', 'partial', 'received', 'cancelled'])->default('draft');
    $table->date('order_date');
    $table->date('expected_date')->nullable();
    $table->decimal('total_amount', 10, 2)->default(0);
    $table->text('notes')->nullable();
    $table->unsignedBigInteger('created_by'); // SSO user_id
    $table->string('created_by_name'); // SSO user name
    $table->unsignedBigInteger('received_by')->nullable(); // SSO user_id
    $table->string('received_by_name')->nullable();
    $table->timestamp('received_at')->nullable();
    $table->timestamps();

    $table->index(['supplier_id', 'status']);
    $table->index('order_date');
});
```

### 3. Purchase Order Items Table
```php
Schema::create('purchase_order_items', function (Blueprint $table) {
    $table->id();
    $table->foreignId('purchase_order_id')->constrained()->onDelete('cascade');
    $table->string('sku');
    $table->string('product_name');
    $table->integer('qty_ordered');
    $table->integer('qty_received')->default(0);
    $table->decimal('unit_cost', 10, 2);
    $table->decimal('total_cost', 10, 2); // qty_ordered * unit_cost
    $table->timestamps();

    $table->index('purchase_order_id');
    $table->index('sku');
});
```

### 4. Inventory Table
```php
Schema::create('inventory', function (Blueprint $table) {
    $table->id();
    $table->string('sku')->unique(); // Links to Marketing products.sku
    $table->string('product_name');
    $table->integer('quantity')->default(0); // Current stock level
    $table->string('location')->nullable(); // Warehouse location (Shelf A1, etc.)
    $table->integer('reorder_point')->default(10); // Alert threshold
    $table->decimal('average_cost', 10, 2)->nullable(); // Weighted average
    $table->timestamp('last_restocked_at')->nullable();
    $table->timestamps();

    $table->index('sku');
    $table->index(['quantity', 'reorder_point']); // For low stock alerts
});
```

### 5. Stock Movements Table
```php
Schema::create('stock_movements', function (Blueprint $table) {
    $table->id();
    $table->string('sku');
    $table->enum('type', ['in', 'out', 'adjustment', 'transfer']); // Movement type
    $table->integer('quantity'); // Positive for IN, negative for OUT
    $table->integer('balance_after'); // Running balance
    $table->string('reference_type')->nullable(); // 'po', 'order', 'adjustment'
    $table->unsignedBigInteger('reference_id')->nullable(); // PO ID, Order ID, etc.
    $table->string('reference_number')->nullable(); // PO-xxx, Order #xxx
    $table->text('notes')->nullable();
    $table->unsignedBigInteger('performed_by'); // SSO user_id
    $table->string('performed_by_name'); // SSO user name
    $table->timestamp('performed_at');
    $table->timestamps();

    $table->index('sku');
    $table->index(['reference_type', 'reference_id']);
    $table->index('performed_at');
});
```

### 6. Stock Locations Table (Optional - Phase 2)
```php
Schema::create('stock_locations', function (Blueprint $table) {
    $table->id();
    $table->string('name'); // "Warehouse A", "Shelf B-12"
    $table->string('code')->unique(); // "WH-A", "SH-B12"
    $table->boolean('is_active')->default(true);
    $table->timestamps();
});
```

---

## Controllers

### 1. SupplierController
**File:** `app/Http/Controllers/SupplierController.php`

```php
class SupplierController extends Controller
{
    public function index()        // List all suppliers
    public function create()       // Show create form
    public function store()        // Save new supplier
    public function edit($id)      // Show edit form
    public function update($id)    // Update supplier
    public function destroy($id)   // Delete supplier (soft delete recommended)
}
```

### 2. PurchaseOrderController
**File:** `app/Http/Controllers/PurchaseOrderController.php`

```php
class PurchaseOrderController extends Controller
{
    public function index()           // List all POs with filters
    public function create()          // Show PO creation form
    public function store()           // Save new PO (generate PO number)
    public function show($id)         // View PO details
    public function edit($id)         // Edit draft PO
    public function update($id)       // Update PO
    public function confirm($id)      // Confirm PO (draft → confirmed)
    public function cancel($id)       // Cancel PO
    public function receive($id)      // Show receiving interface
    public function processReceiving() // Process received items
}
```

### 3. InventoryController
**File:** `app/Http/Controllers/InventoryController.php`

```php
class InventoryController extends Controller
{
    public function index()              // Stock levels dashboard
    public function show($sku)           // View single item details
    public function movements()          // Stock movement history
    public function lowStock()           // Low stock alerts
    public function adjust()             // Manual adjustment form
    public function processAdjustment()  // Save adjustment + movement
    public function syncFromMarketing()  // Sync SKUs from Marketing products table
}
```

### 4. Update OrderController
**File:** `app/Http/Controllers/OrderController.php`

**Add new method:**
```php
private function deductStock($orderId, $user)
{
    // 1. Get order from Marketing DB
    $order = DB::connection('marketing')->table('orders')->find($orderId);

    // 2. Parse line_items JSON
    $lineItems = json_decode($order->line_items, true);

    // 3. For each item:
    foreach ($lineItems as $item) {
        $sku = $item['sku'];
        $qty = $item['quantity'];

        // 4. Check if product is bundle (query Marketing products table)
        $product = DB::connection('marketing')->table('products')
            ->where('sku', $sku)->first();

        if ($product && $product->product_type === 'bundle') {
            // 5. Deduct bundle components
            $bundleItems = json_decode($product->bundle_items, true);
            foreach ($bundleItems as $component) {
                $this->deductInventory($component['sku'], $component['quantity'] * $qty, $orderId, $user);
            }
        } else {
            // 6. Deduct single item
            $this->deductInventory($sku, $qty, $orderId, $user);
        }
    }
}

private function deductInventory($sku, $qty, $orderId, $user)
{
    // 1. Get current inventory
    $inventory = DB::table('inventory')->where('sku', $sku)->first();

    if (!$inventory) {
        // Create inventory record if not exists
        DB::table('inventory')->insert([
            'sku' => $sku,
            'product_name' => 'Unknown Product',
            'quantity' => 0,
            'created_at' => now(),
            'updated_at' => now()
        ]);
        $inventory = DB::table('inventory')->where('sku', $sku)->first();
    }

    // 2. Check sufficient stock
    $newQty = $inventory->quantity - $qty;
    if ($newQty < 0) {
        // Log warning but allow (can have negative stock)
        \Log::warning("Insufficient stock for SKU: {$sku}. Current: {$inventory->quantity}, Required: {$qty}");
    }

    // 3. Update inventory
    DB::table('inventory')
        ->where('sku', $sku)
        ->update([
            'quantity' => $newQty,
            'updated_at' => now()
        ]);

    // 4. Record movement
    DB::table('stock_movements')->insert([
        'sku' => $sku,
        'type' => 'out',
        'quantity' => -$qty,
        'balance_after' => $newQty,
        'reference_type' => 'order',
        'reference_id' => $orderId,
        'reference_number' => "Order #{$orderId}",
        'notes' => "Stock out for order packing",
        'performed_by' => $user['user_id'],
        'performed_by_name' => $user['name'],
        'performed_at' => now(),
        'created_at' => now(),
        'updated_at' => now()
    ]);
}
```

**Update markAsPacked() method:**
```php
public function markAsPacked(Request $request, $id)
{
    // ... existing code ...

    // BEFORE updating order status, deduct stock
    try {
        $this->deductStock($id, $user);
    } catch (\Exception $e) {
        \Log::error("Stock deduction failed: " . $e->getMessage());
        return response()->json([
            'success' => false,
            'message' => 'Failed to deduct stock: ' . $e->getMessage()
        ], 500);
    }

    // ... continue with existing pack logic ...
}
```

---

## Routes

**File:** `routes/web.php`

```php
Route::middleware('omnia.sso')->group(function () {
    // Existing routes
    Route::get('/dashboard', [OrderController::class, 'index'])->name('dashboard');
    Route::get('/performance', [OrderController::class, 'performance'])->name('performance');
    // ... existing order routes ...

    // === SUPPLIERS ===
    Route::resource('suppliers', SupplierController::class);

    // === PURCHASE ORDERS ===
    Route::prefix('purchase-orders')->name('purchase-orders.')->group(function () {
        Route::get('/', [PurchaseOrderController::class, 'index'])->name('index');
        Route::get('/create', [PurchaseOrderController::class, 'create'])->name('create');
        Route::post('/', [PurchaseOrderController::class, 'store'])->name('store');
        Route::get('/{id}', [PurchaseOrderController::class, 'show'])->name('show');
        Route::get('/{id}/edit', [PurchaseOrderController::class, 'edit'])->name('edit');
        Route::put('/{id}', [PurchaseOrderController::class, 'update'])->name('update');
        Route::post('/{id}/confirm', [PurchaseOrderController::class, 'confirm'])->name('confirm');
        Route::post('/{id}/cancel', [PurchaseOrderController::class, 'cancel'])->name('cancel');
        Route::get('/{id}/receive', [PurchaseOrderController::class, 'receive'])->name('receive');
        Route::post('/{id}/receive', [PurchaseOrderController::class, 'processReceiving'])->name('process-receive');
    });

    // === INVENTORY ===
    Route::prefix('inventory')->name('inventory.')->group(function () {
        Route::get('/', [InventoryController::class, 'index'])->name('index');
        Route::get('/movements', [InventoryController::class, 'movements'])->name('movements');
        Route::get('/low-stock', [InventoryController::class, 'lowStock'])->name('low-stock');
        Route::get('/adjust', [InventoryController::class, 'adjust'])->name('adjust');
        Route::post('/adjust', [InventoryController::class, 'processAdjustment'])->name('process-adjust');
        Route::post('/sync', [InventoryController::class, 'syncFromMarketing'])->name('sync');
        Route::get('/{sku}', [InventoryController::class, 'show'])->name('show');
    });
});
```

---

## Navigation Update

**File:** `resources/views/layouts/app.blade.php`

Update navigation to include new modules:

```blade
<nav class="bg-white shadow">
    <div class="max-w-7xl mx-auto px-4 sm:px-6 lg:px-8">
        <div class="flex justify-between h-16">
            <div class="flex">
                <!-- Navigation Links -->
                <div class="hidden space-x-8 sm:flex">
                    <a href="{{ route('dashboard') }}"
                       class="inline-flex items-center px-1 pt-1 border-b-2 {{ request()->routeIs('dashboard') ? 'border-indigo-500' : 'border-transparent' }}">
                        Dashboard
                    </a>

                    <a href="{{ route('performance') }}"
                       class="inline-flex items-center px-1 pt-1 border-b-2 {{ request()->routeIs('performance*') ? 'border-indigo-500' : 'border-transparent' }}">
                        Performance
                    </a>

                    <a href="{{ route('purchase-orders.index') }}"
                       class="inline-flex items-center px-1 pt-1 border-b-2 {{ request()->routeIs('purchase-orders*') ? 'border-indigo-500' : 'border-transparent' }}">
                        Purchasing
                    </a>

                    <a href="{{ route('inventory.index') }}"
                       class="inline-flex items-center px-1 pt-1 border-b-2 {{ request()->routeIs('inventory*') ? 'border-indigo-500' : 'border-transparent' }}">
                        Inventory
                    </a>

                    <a href="{{ route('suppliers.index') }}"
                       class="inline-flex items-center px-1 pt-1 border-b-2 {{ request()->routeIs('suppliers*') ? 'border-indigo-500' : 'border-transparent' }}">
                        Suppliers
                    </a>
                </div>
            </div>

            <!-- User Dropdown (existing) -->
        </div>
    </div>
</nav>
```

---

## Views Structure

```
resources/views/
├── layouts/
│   └── app.blade.php (updated with new navigation)
├── orders/
│   └── index.blade.php (existing)
├── performance/
│   ├── index.blade.php (existing)
│   └── print.blade.php (existing)
├── suppliers/
│   ├── index.blade.php (list suppliers)
│   ├── create.blade.php (create form)
│   └── edit.blade.php (edit form)
├── purchase-orders/
│   ├── index.blade.php (list POs with filters)
│   ├── create.blade.php (create PO form)
│   ├── show.blade.php (view PO details)
│   ├── edit.blade.php (edit draft PO)
│   └── receive.blade.php (receiving interface with scanner)
└── inventory/
    ├── index.blade.php (stock levels dashboard)
    ├── movements.blade.php (movement history)
    ├── low-stock.blade.php (alerts)
    ├── adjust.blade.php (adjustment form)
    └── show.blade.php (single SKU details)
```

---

## Key Features by Module

### Module 1: Suppliers Management
- Create/edit/delete suppliers
- Contact information
- Active/inactive status
- Simple CRUD interface

### Module 2: Purchase Orders
- Create PO with multiple items
- Auto-generate PO number (PO-YYYYMMDD-###)
- PO statuses: draft → confirmed → partial/received
- Receiving interface:
  - Scan or manually enter SKU
  - Enter quantity received
  - Update inventory + create stock movement
  - Track partial receipts

### Module 3: Inventory Management
- Real-time stock levels per SKU
- Low stock alerts (quantity < reorder_point)
- Stock movement history with filters
- Manual adjustments (damaged, found, lost)
- Sync SKUs from Marketing products table
- Search and filter by SKU, product name

### Module 4: Stock Out Integration
- Auto-deduct when order packed
- Handle bundles (deduct components)
- Create stock movement records
- Warning if stock goes negative (but allow)
- Show stock status in order packing interface

---

## Implementation Steps

### Phase 1: Database Setup (Day 1)
1. Create 5 migrations (suppliers, purchase_orders, purchase_order_items, inventory, stock_movements)
2. Run migrations: `php artisan migrate`
3. Create seeders for testing (sample suppliers, inventory items)

### Phase 2: Suppliers Module (Day 1-2)
1. Create SupplierController with CRUD methods
2. Create supplier views (index, create, edit)
3. Test supplier management

### Phase 3: Purchase Orders Module (Day 2-3)
1. Create PurchaseOrderController
2. Implement PO creation with items
3. Build receiving interface with scanner
4. Test PO workflow: create → confirm → receive

### Phase 4: Inventory Module (Day 3-4)
1. Create InventoryController
2. Build stock levels dashboard
3. Implement movement history
4. Create adjustment interface
5. Add low stock alerts

### Phase 5: Stock Out Integration (Day 4-5)
1. Update OrderController.markAsPacked()
2. Implement deductStock() method
3. Handle bundle products
4. Test with real orders
5. Add stock warnings in UI

### Phase 6: Testing & Refinement (Day 5)
1. Test complete flow: PO → Receive → Pack Order → Stock Deduction
2. Test bundle handling
3. Test negative stock scenarios
4. Add validation and error messages
5. Create documentation

---

## Access Control

**Roles with Full Access:**
- Admin (all operations)
- Production Manager (all operations)

**Production Staff:**
- Read-only inventory view
- Cannot create POs
- Cannot adjust stock
- Cannot receive stock

**Implementation:**
```php
// In views
@php
    $userRoles = $user['roles'] ?? [];
    $canManage = in_array('admin', $userRoles) || in_array('production-manager', $userRoles);
@endphp

@if($canManage)
    <!-- Show create/edit buttons -->
@endif

// In controllers
$userRoles = $request->input('omnia_user')['roles'] ?? [];
if (!in_array('admin', $userRoles) && !in_array('production-manager', $userRoles)) {
    abort(403, 'Unauthorized action');
}
```

---

## Integration with Marketing Database

### Reading Products (for SKU sync)
```php
$products = DB::connection('marketing')
    ->table('products')
    ->where('is_active', true)
    ->get();

// Sync to inventory table
foreach ($products as $product) {
    DB::table('inventory')->updateOrInsert(
        ['sku' => $product->sku],
        [
            'product_name' => $product->name,
            'quantity' => $inventory->quantity ?? 0,
            'updated_at' => now()
        ]
    );
}
```

### Reading Orders (for stock deduction)
```php
$order = DB::connection('marketing')->table('orders')->find($id);
$lineItems = json_decode($order->line_items, true);

// Each line item has: sku, name, quantity, total
foreach ($lineItems as $item) {
    $sku = $item['sku'];
    $qty = $item['quantity'];
    // Deduct from inventory
}
```

---

## Error Handling

### Insufficient Stock
```php
if ($inventory->quantity < $requiredQty) {
    // Option 1: Block packing
    throw new \Exception("Insufficient stock for SKU: {$sku}. Available: {$inventory->quantity}, Required: {$requiredQty}");

    // Option 2: Allow with warning (RECOMMENDED)
    \Log::warning("Packing order with insufficient stock - SKU: {$sku}");
    // Continue with negative stock
}
```

### Missing Inventory Record
```php
if (!$inventory) {
    // Auto-create with zero quantity
    DB::table('inventory')->insert([
        'sku' => $sku,
        'product_name' => $productName,
        'quantity' => 0,
        'created_at' => now()
    ]);
}
```

---

## Testing Checklist

- [ ] Create supplier
- [ ] Create purchase order with 3 items
- [ ] Confirm PO
- [ ] Receive full quantity → Stock increases
- [ ] Pack order → Stock decreases automatically
- [ ] Pack bundle product → Component stocks decrease
- [ ] Manual stock adjustment (add/remove)
- [ ] View stock movement history
- [ ] Low stock alerts appear when below reorder point
- [ ] Sync SKUs from Marketing products
- [ ] Negative stock handling (pack with insufficient stock)
- [ ] Role-based access (Staff can't edit, Manager can)

---

## Files to Create/Modify

### New Files (Controllers)
- `app/Http/Controllers/SupplierController.php`
- `app/Http/Controllers/PurchaseOrderController.php`
- `app/Http/Controllers/InventoryController.php`

### New Files (Migrations)
- `database/migrations/YYYY_MM_DD_create_suppliers_table.php`
- `database/migrations/YYYY_MM_DD_create_purchase_orders_table.php`
- `database/migrations/YYYY_MM_DD_create_purchase_order_items_table.php`
- `database/migrations/YYYY_MM_DD_create_inventory_table.php`
- `database/migrations/YYYY_MM_DD_create_stock_movements_table.php`

### New Files (Views) - 13 files total
- Suppliers: index, create, edit (3 files)
- Purchase Orders: index, create, show, edit, receive (5 files)
- Inventory: index, movements, low-stock, adjust, show (5 files)

### Modified Files
- `routes/web.php` - Add new routes
- `resources/views/layouts/app.blade.php` - Add navigation links
- `app/Http/Controllers/OrderController.php` - Add stock deduction logic

---

## Future Enhancements (Out of Scope)

- Stock transfer between locations
- Barcode label printing
- Stock expiry tracking
- Inventory reports (Excel export)
- Stock reservation (hold stock for orders)
- Multi-warehouse support
- Supplier performance metrics
