# Division-Based Inventory Management System

## Overview

The KCG Inventory system implements a **Division-based inventory tracking** mechanism where materials are stored and managed on behalf of specific divisions (departments). This enhancement enables precise tracking of which division owns which materials in the warehouse, providing better accountability and cost allocation.

---

## Key Concepts

### What is a Division?

A **Division** (also referred to as a Department) represents an organizational unit within the company. In this system, divisions are stored in the `departments` table and are linked to inventory through the `division_id` field.

### Core Principle

> **Materials are stored in the warehouse on behalf of divisions.**

This means:
- When materials arrive via Supplier Delivery, they are tagged to a specific division
- When materials are issued from inventory, they are deducted from the division's stock
- Each division can track their own inventory holdings and costs

---

## System Components

### 1. Database Schema

#### Inventory Table
The `inventory` table includes a `division_id` column:
```
inventory
├── id
├── item_id
├── division_id          ← Links to departments table
├── quantity_available
├── unit_price
├── batch_number
├── production_date
├── expiry_date
└── ...
```

#### Division Deliveries Table
A separate `division_deliveries` table tracks cumulative delivery statistics per division:
```
division_deliveries
├── id
├── division_id
├── item_id
├── total_quantity_delivered
├── current_available_quantity
├── total_quantity_issued
├── average_unit_price
├── total_value
├── first_delivery_date
├── last_delivery_date
└── last_updated
```

#### Outgoing Items Table
The `outgoing_items` table also has `division_id` to track which division's stock is being used:
```
outgoing_items
├── id
├── outgoing_transaction_id
├── item_id
├── division_id          ← Division source for issued materials
├── quantity_requested
├── quantity_released
└── ...
```

---

## Workflow Flows

### Flow 1: Supplier Delivery (Incoming Materials)

**Location:** `warehouse/supplier-delivery/create`

**Process:**
1. User creates a new Supplier Delivery
2. User selects a **Division** from the dropdown (required field)
3. User adds items with quantities, prices, batch numbers, etc.
4. On save, the system:
   - Creates an `IncomingOperation` record with the selected `division`
   - When approved/completed, calls `processToInventory()`
   - The `updateInventory()` method:
     ```php
     $inventory = Inventory::firstOrNew([
         'item_id' => $item->item_id,
         'batch_number' => $item->batch_number,
         'division_id' => $divisionId,  // Division is part of the unique key
     ]);
     ```
   - Also records the delivery in `DivisionDelivery::recordDelivery()`

**Key Code (IncomingOperation.php:412-450):**
```php
protected function updateInventory($item)
{
    // Get division_id - handle both numeric ID and text division name
    $divisionId = null;
    if (is_numeric($this->division)) {
        $divisionId = (int)$this->division;
    } elseif (!empty($this->division)) {
        $division = ProjectDivision::where('division_name', $this->division)->first();
        $divisionId = $division ? $division->id : null;
    }

    $inventory = Inventory::firstOrNew([
        'item_id' => $item->item_id,
        'batch_number' => $item->batch_number,
        'division_id' => $divisionId,
    ]);

    $inventory->quantity_available += $item->quantity_accepted;
    // ... save inventory

    // Record delivery totals in division_deliveries table
    if ($divisionId) {
        DivisionDelivery::recordDelivery(
            $divisionId,
            $item->item_id,
            $item->quantity_accepted,
            $item->unit_price_aed ?: 0,
            $this->operation_date
        );
    }
}
```

---

### Flow 2: Material Issue (Outgoing Materials)

**Location:** `warehouse/material-issue/{id}` (decision-multi view)

**Process:**
1. Warehouse receives a Material Request from a project
2. User opens the Material Issue Decision page
3. **Three fulfillment options available:**

#### Option A: Inventory Dispatch
- User enables "Inventory Dispatch" section
- For each item, user selects:
  - **Division** (dropdown) - Which division's stock to use
  - **Dispatch Quantity** - How many units to issue
- System deducts from the selected division's inventory

**Form Fields (decision-multi.blade.php:382-404):**
```html
<select name="inventory_division[{{ $item->item_id }}]"
        class="form-control division-select"
        onchange="updateDivisionStock({{ $item->item_id }})">
    <option value="">Select Division</option>
    @foreach($divisions as $division)
        <option value="{{ $division->id }}">
            {{ $division->name }} ({{ $division->code }})
        </option>
    @endforeach
</select>

<input type="number" name="inventory[{{ $item->item_id }}]"
       class="form-control inventory-qty" />
```

#### Option B: Direct Delivery
- Supplier delivers directly to project site
- User must still select a **Division** for tracking purposes
- This creates records for cost allocation to the division

**Form Fields (decision-multi.blade.php:539-550):**
```html
<select name="delivery_division[{{ $item->item_id }}]"
        class="form-control division-select">
    <option value="">Select Division</option>
    @foreach($divisions as $division)
        <option value="{{ $division->id }}">
            {{ $division->name }} ({{ $division->code }})
        </option>
    @endforeach
</select>
```

#### Option C: FOC Transfer
- Free of Charge items from FOC inventory
- No division selection needed (FOC items are tracked separately)

---

### Flow 3: Processing Multi-Source Issue

**Controller:** `MaterialIssueController::processMultiSource()`

**Validation Rules:**
```php
$validationRules = [
    'inventory_division' => 'nullable|array',
    'inventory_division.*' => 'nullable|exists:departments,id',
    'delivery_division' => 'nullable|array',
    'delivery_division.*' => 'nullable|exists:departments,id',
];

// Division is required for direct delivery items
if ($enableDelivery && !empty($request->delivery)) {
    foreach ($request->delivery as $itemId => $qty) {
        if ($qty > 0) {
            $validationRules["delivery_division.{$itemId}"] = 'required|exists:departments,id';
        }
    }
}
```

**Inventory Processing (lines 635-693):**
```php
private function processInventoryPortion(Request $request, $originalTransaction, $inventoryItems, $inventoryDivisions)
{
    foreach ($inventoryItems as $itemId => $quantity) {
        if ($quantity > 0) {
            $divisionId = $inventoryDivisions[$itemId] ?? null;

            // Get average price from the specific division
            $averagePrice = Inventory::where('item_id', $itemId)
                ->when($divisionId, function($query, $divisionId) {
                    return $query->where('division_id', $divisionId);
                })
                ->where('quantity_available', '>', 0)
                ->avg('unit_price') ?: 0;

            OutgoingItem::create([
                'outgoing_transaction_id' => $inventoryTransaction->id,
                'item_id' => $itemId,
                'division_id' => $divisionId,  // Division tracked on outgoing item
                'quantity_requested' => $quantity,
                'quantity_released' => $quantity,
                'unit_price' => $averagePrice,
            ]);
        }
    }
    // ... dispatch processing
}
```

---

## Model Relationships

### Inventory Model
```php
class Inventory extends Model
{
    protected $fillable = [
        'item_id',
        'division_id',
        // ...
    ];

    public function division(): BelongsTo
    {
        return $this->belongsTo(Department::class, 'division_id');
    }
}
```

### OutgoingItem Model
```php
class OutgoingItem extends Model
{
    protected $fillable = [
        'outgoing_transaction_id',
        'item_id',
        'division_id',
        // ...
    ];

    public function division(): BelongsTo
    {
        return $this->belongsTo(Department::class, 'division_id');
    }
}
```

### DivisionDelivery Model
```php
class DivisionDelivery extends Model
{
    // Records cumulative delivery data per division/item combination

    public static function recordDelivery($divisionId, $itemId, $quantity, $unitPrice, $deliveryDate)
    {
        $record = self::firstOrNew([
            'division_id' => $divisionId,
            'item_id' => $itemId
        ]);

        $record->total_quantity_delivered += $quantity;
        $record->current_available_quantity += $quantity;
        $record->total_value += ($quantity * $unitPrice);
        $record->average_unit_price = $record->total_value / $record->total_quantity_delivered;
        $record->last_delivery_date = $deliveryDate;
        $record->save();
    }

    public static function recordIssue($divisionId, $itemId, $issuedQuantity)
    {
        $record = self::where('division_id', $divisionId)
            ->where('item_id', $itemId)
            ->first();

        if ($record) {
            $record->current_available_quantity -= $issuedQuantity;
            $record->total_quantity_issued += $issuedQuantity;
            $record->save();
        }
    }
}
```

---

## UI Components

### Supplier Delivery Form
- **Division Dropdown** (required)
  - Uses Select2 for searchable dropdown
  - Shows: `{division_name} ({division_code})`
  - Submits: `division_id` as the value

### Material Issue Form
- **Per-Item Division Selection**
  - Inventory Dispatch: `inventory_division[item_id]`
  - Direct Delivery: `delivery_division[item_id]`
- Dynamic availability updates based on division selection

---

## Reports and Tracking

### Division Deliveries Report
- Shows all deliveries grouped by division
- Tracks total quantities delivered per item per division
- Calculates average unit prices
- Shows current available quantities

### Available Queries

**Get Division Summary:**
```php
DivisionDelivery::getDivisionSummary($divisionId);
```

**Get Item Summary Across Divisions:**
```php
DivisionDelivery::getItemSummary($itemId);
```

**Get Division Stock for Specific Item:**
```php
Inventory::where('division_id', $divisionId)
    ->where('item_id', $itemId)
    ->where('quantity_available', '>', 0)
    ->sum('quantity_available');
```

---

## Summary

The Division-based system provides:

1. **Accountability**: Each division knows exactly what materials they have in the warehouse
2. **Cost Tracking**: Costs can be allocated to specific divisions
3. **Flexible Issuing**: When issuing materials, warehouse can choose which division's stock to use
4. **Audit Trail**: Complete history of deliveries and issues per division
5. **Reporting**: Division-level reports for inventory valuation and consumption

This system ensures that materials in the warehouse are properly attributed to the divisions that procured them, enabling accurate cost center accounting and inventory management.

---

## Updated Modules

### Inventory Dashboard (`warehouse/inventory`)

The Inventory Dashboard has been enhanced to support division-based filtering and visualization:

#### Features Added:
1. **Division Filter** - Filter inventory by division in the Filters Panel
2. **Division Column** - Added Division column to the inventory DataTable
3. **Division Distribution Chart** - New doughnut chart showing inventory distribution across divisions
4. **Search Enhancement** - Search now includes division name/code

#### Filter Panel:
```html
<select id="filter-division" class="form-control">
    <option value="">All Divisions</option>
    @foreach($divisions as $division)
        <option value="{{ $division->id }}">{{ $division->name }}</option>
    @endforeach
</select>
```

#### DataTable Columns:
| Column | Description |
|--------|-------------|
| Item | Item name and code |
| Category | Item category |
| **Division** | Division that owns this inventory (NEW) |
| Current Stock | Available quantity |
| Min Level | Minimum stock level |
| Reserved | Reserved quantity |
| Value | Stock value |
| Status | In Stock / Low Stock / Out of Stock |
| Last Movement | Last movement date |
| Actions | View movements |

#### Controller Changes (`InventoryController.php`):
- Added `division` relationship to queries
- Added division filter support
- Added `getDivisionChartData()` method for division distribution chart
- Search now includes division name/code

---

### Low Stock Alerts (`warehouse/inventory/low-stock`)

The Low Stock Alerts page has been enhanced to support division-based filtering:

#### Features Added:
1. **Division Filter** - Filter low stock items by division in the Filters Panel
2. **Division Column** - Added Division column to the low stock DataTable
3. **Search Enhancement** - Search now includes division name/code

#### Filter Panel:
| Filter | Description |
|--------|-------------|
| Priority Level | Critical, High, Medium, Low |
| Category | Item category |
| **Division** | Filter by division (NEW) |
| Days Remaining | Time until stockout |
| Action Required | Urgent/Standard reorder |

#### DataTable Columns:
| Column | Description |
|--------|-------------|
| Priority | Critical/High/Medium/Low badge |
| Item | Item name and code |
| Category | Item category |
| **Division** | Division that owns this inventory (NEW) |
| Current Stock | Available quantity |
| Min Level | Minimum stock level |
| Shortage | Quantity shortage |
| Days Left | Estimated days until stockout |
| Value at Risk | Stock value |
| Last Updated | Last update timestamp |
| Actions | Adjust stock, View history |

#### Controller Changes (`getLowStockDataForTable`):
- Added `division` relationship to queries
- Added division filter support
- Added category and priority filter implementation
- Search now includes division name/code
- Data mapping includes `division` and `division_id`

---

### Stock Adjustments (`warehouse/inventory/adjustments`)

The Stock Adjustments page has been enhanced to support division-based filtering and tracking:

#### Features Added:
1. **Division Filter** - Filter adjustments by division in the Filters Panel
2. **Division Column** - Added Division column to the adjustments DataTable
3. **Search Enhancement** - Search now includes division name/code
4. **Type Filter** - Added adjustment type filter (increase/decrease/set)

#### Filter Panel:
| Filter | Description |
|--------|-------------|
| Date Range | Start and end date |
| Adjustment Type | Increase, Decrease, Set Value |
| **Division** | Filter by division (NEW) |
| Location | Warehouse location |
| User | User who made adjustment |

#### DataTable Columns:
| Column | Description |
|--------|-------------|
| Date & Time | When adjustment was made |
| Item | Item name and code |
| **Division** | Division affected by adjustment (NEW) |
| Type | Increase/Decrease/Set badge |
| Old Qty | Quantity before adjustment |
| Adjustment | Amount adjusted (+/-/→) |
| New Qty | Quantity after adjustment |
| Reason | Reason for adjustment |
| User | User who made adjustment |
| Location | Warehouse location |
| Actions | View details, Delete |

#### Controller Changes (`getAdjustmentsDataForTable`):
- Added `division` relationship to StockMovement query
- Added division filter support
- Added type filter support (increase/decrease/set)
- Search now includes division name/code
- Data mapping includes `division` and `division_id`

#### Modal Enhancement:
The "New Adjustment" modal already includes a Division dropdown (using ProjectDivision model) which allows users to specify the division when creating stock adjustments.

---

### Physical Count (`warehouse/inventory/physical-count`)

The Physical Count page has been enhanced to support division-based filtering and tracking:

#### Features Added:
1. **Division Filter** - Filter items by division in the Filters Panel
2. **Division Column** - Added Division column to the Physical Count DataTable
3. **Multi-Division Display** - Items with inventory across multiple divisions show count with tooltip

#### Filter Panel:
| Filter | Description |
|--------|-------------|
| Category | Item category |
| **Division** | Filter by division (NEW) |
| Count Status | Pending, Counted, Matched, Discrepancy |
| Item Search | Search by item name or code |
| Variance Range | Min/Max variance filter |

#### DataTable Columns:
| Column | Description |
|--------|-------------|
| Item | Item name and code |
| Category | Item category |
| **Division** | Division(s) that have this item in inventory (NEW) |
| Current Stock | System quantity available |
| Physical Count | Input field for actual count |
| Variance | Difference between physical and system count |
| Status | Pending/Counted/Matched/Discrepancy |
| Last Updated | When count was last updated |
| Actions | Save, View History, Recount |

#### Division Display Logic:
- **Single Division**: Shows division name with business icon
- **Multiple Divisions**: Shows "X divisions" with tooltip listing all divisions
- **No Division**: Shows "No Division" in muted text

#### Controller Changes (`physicalCount`):
- Added `inventories.division` relationship to Item query
- Added `category` relationship to Item query
- Enables division information to be displayed per item

---

### Inventory Valuation (`warehouse/inventory/valuation`)

The Inventory Valuation page has been enhanced to support division-based valuation analysis:

#### Features Added:
1. **Division Filter** - Filter valuation data by division in the Filters Panel
2. **Division Column** - Added Division column to the Valuation DataTable
3. **Division Breakdown Card** - New card showing valuation summary by division
4. **Division Data in Export** - Division information included in valuation exports

#### Filter Panel:
| Filter | Description |
|--------|-------------|
| Category | Item category |
| **Division** | Filter by division (NEW) |
| Value Range | High/Medium/Low/Zero value items |
| Item Search | Search by item name or code |
| Cost Method | FIFO/LIFO/Average/Standard |

#### Breakdown Cards:
The page now displays two breakdown cards side-by-side:
- **Valuation by Category** - Shows top 6 categories by total value
- **Valuation by Division** - Shows top 6 divisions by total value (NEW)

#### DataTable Columns:
| Column | Description |
|--------|-------------|
| Item | Item name and code |
| Category | Item category |
| **Division** | Division that owns this inventory (NEW) |
| Stock Quantity | Current stock with unit |
| Unit Price | Price per unit (AED) |
| Total Value | Stock quantity × Unit price |
| Cost Method | FIFO/LIFO/Average/Standard |
| Last Updated | Last update timestamp |
| Actions | View details |

#### Controller Changes (`valuation`):
- Added `division` relationship to Inventory query
- Added `division` and `division_id` to valuation data mapping
- Added `divisionBreakdown` collection for division summary card
- Division breakdown shows total value, item count, and average value per division

---

### Inventory Aging Report (`warehouse/inventory/aging-report`)

The Inventory Aging Report page has been enhanced to support division-based aging analysis:

#### Features Added:
1. **Division Filter** - Filter aging inventory by division in the Filters Panel
2. **Division Column** - Added Division column to the Aging Report DataTable
3. **Division Breakdown Card** - New card showing aging breakdown by division (alongside Age Distribution card)
4. **Division Data Attribute** - Each row includes `data-division` attribute for JavaScript filtering

#### Filter Panel:
| Filter | Description |
|--------|-------------|
| Aging Period | 30/60/90/180 days or 1 year |
| Category | Item category |
| **Division** | Filter by division (NEW) |
| Age Group | Fresh/Moderate/Slow Moving/Dead Stock |
| Item Search | Search by item name or code |

#### Breakdown Cards:
The page now displays two breakdown cards side-by-side:
- **Age Distribution Analysis** - Shows item counts and values by age group (Fresh, Moderate, Slow, Dead)
- **Aging by Division** - Shows top 6 divisions with total value, item count, and average age days (NEW)

#### DataTable Columns:
| Column | Description |
|--------|-------------|
| Item | Item name and code |
| Category | Item category |
| **Division** | Division that owns this inventory (NEW) |
| Current Stock | Available quantity |
| Age (Days) | Days since last transaction |
| Last Transaction | Date of last movement |
| Value | Stock value (quantity × unit price) |
| Age Category | Fresh/Moderate/Slow Moving/Dead Stock badge |
| Actions | View details, Adjust stock, View history |

#### Repository Changes (`InventoryRepository.php`):
- Added `division` relationship to `getAgingInventory()` query:
```php
return $this->model->where('updated_at', '<=', $date)
                  ->where('quantity_available', '>', 0)
                  ->with(['item', 'item.category', 'division'])
                  ->get();
```

#### Controller Changes (`agingReport`):
- Transforms raw inventory data to include division information
- Calculates `division` name and `division_id` for each inventory record
- Added `divisionBreakdown` collection with:
  - `division`: Division name
  - `total_value`: Sum of aging inventory value for division
  - `item_count`: Number of aging items for division
  - `avg_age_days`: Average age in days for division's inventory

#### JavaScript Enhancements:
- Updated DataTable column indices for Division column (shifted from column 3 to 4 for age sorting)
- Added division filter to `applyAgingFilters()` function
- Uses named filter function (`agingFilter`) for proper cleanup
- Division filter uses `data-division` attribute on table rows

---

### Site Return Report (`warehouse/reports/returns`)

The Site Return Report page has been enhanced to support division-based return tracking and analysis:

#### Features Added:
1. **Division Filter** - Filter returns by division in the Filters Panel (already existed)
2. **Division Column** - Division column in the returns DataTable (already existed)
3. **Division Breakdown Card** - New card showing returns breakdown by division (NEW)

#### Filter Panel:
| Filter | Description |
|--------|-------------|
| Search | Item code, description |
| Project | Filter by project |
| **Division** | Filter by division |
| Category | Filter by category |
| Quality Status | Pending/Passed/Failed/Conditional |
| Date Range | Date from/to |

#### Division Breakdown Card:
The page now displays a "Returns by Division" card showing top 6 divisions with:
- Total return value (AED)
- Division name
- Number of returns and total quantity returned

#### DataTable Columns:
| Column | Description |
|--------|-------------|
| Reference Number | Return reference |
| Reference Type | Material Request/Direct Delivery/Material Transfer |
| Project | Project name |
| **Division** | Division associated with the return |
| Item Code | Item code |
| Description | Item description |
| Category | Item category |
| Quantity | Quantity returned |
| Unit Price | Price per unit |
| Total Price | Total return value |
| Quality Status | Pending/Passed/Failed/Conditional |
| Return Date | Date of return |
| Actions | View details |

#### Controller Changes (`ReportController::returns`):
- Added `divisionBreakdown` collection with:
  - `division`: Division name
  - `total_value`: Sum of return values for division
  - `total_quantity`: Sum of quantities returned for division
  - `item_count`: Number of return items for division
- Division breakdown is passed to view for display
- Existing division filter support via `division_id` parameter

#### View Changes (`returns.blade.php`):
- Added "Returns by Division" breakdown card between Statistics Cards and Filters Panel
- Added dirham symbol CSS styling for currency display in breakdown card
