# Linked Product Stock System - Complete Guide

## Overview

The Linked Product Stock System allows you to create product bundles/cases/packages that automatically sync their stock with a base product. This is perfect for scenarios like:

- **Case of 32 individual units** → Adjusting case stock automatically updates individual stock (32:1 ratio)
- **Box of 24 packets** → Stock changes sync between box and packet
- **Carton of 50 items** → Any quantity change affects both products equally

## Key Features

✅ **Shared Inventory**: Both products share the exact same underlying stock
✅ **Bidirectional Sync**: Changes to either product automatically update the other
✅ **Flexible Ratios**: Each linked product can have a different multiplier (32, 24, 50, etc.)
✅ **User-Friendly UI**: Simple dropdown to link products when adding/editing
✅ **Real-time Preview**: See the relationship before saving
✅ **Auto-Tracking**: Stock movements are recorded for both products

## How It Works

### Example: Case of 32 Coca Colas

**Products:**
- Product 1: Coca Cola 250ml (Individual)
- Product 3: Case | 32 X Coca Cola 250ml (Bundle)

**Relationship:**
- Product 3 is linked to Product 1 with ratio 32:1
- When Product 1 has 160 units in stock, Product 3 shows 5 cases (160 ÷ 32 = 5)

**Stock Changes:**
| Action | Product 1 | Product 3 | Result |
|--------|-----------|-----------|--------|
| Add 32 to P1 | +32 | Auto: +1 case | Both updated |
| Add 1 case to P3 | Auto: +32 | +1 | Both updated |
| Remove 16 from P1 | -16 | Auto: -0.5 case | Both updated |

**Movement Tracking:**
- Both products have their own movement records
- Each movement shows the quantity change in that product's unit
- Example: If you add 64 units to P1, P3 movement shows +2 cases, P1 shows +64 units

## Using the Product Form

### To Create a Linked Product:

1. **Navigate to**: POS → Products → Add New Product
2. **Fill in basic details**: Name, Price, Cost, etc.
3. **Scroll to "Affect Another Product's Stock"** section
4. **Select the base product** in "Link to Product" dropdown
5. **Enter the quantity ratio** (e.g., 32 for a case of 32)
6. **Optional**: Add a description (e.g., "Case contains 32 individual units")
7. **Preview appears** automatically showing the relationship
8. **Save the product**

### To Edit a Linked Product:

1. **Navigate to**: POS → Products → Edit Product
2. **Scroll to "Affect Another Product's Stock"** section
3. **Modify** any of:
   - Base product (dropdown)
   - Quantity ratio (number)
   - Description (text)
4. **Preview updates** automatically
5. **Save changes**

### To Remove a Link:

1. **Edit the product**
2. **In "Link to Product" dropdown**, select "-- No Link --"
3. **Save the product**
4. The relationship is deleted, products are now independent

## Stock Management Interface

### Stock Maintenance Page (`/stock`)

The stock maintenance page shows:
- **Product ID**: Unique identifier
- **Product Name**: Full product name
- **Linked To**: Shows the relationship (e.g., "32x Coca Cola 250ml")
  - Blank if not linked
- **On-hand**: Current stock in that product's unit
- **Adjust Button**: Adjust stock for this product
- **History Button**: View all movements

### When You Adjust Stock

**For a linked product:**
- Input the quantity in that product's unit
- Example: For a case, enter "5" to add 5 cases
- The system automatically:
  - Multiplies by the ratio (5 × 32 = 160 units)
  - Updates the base product's stock to 160
  - Creates movements for both products
  - Shows correct quantity in each product's display

**Example Adjustment:**
```
Adjusting Product 3 (Case):
  Enter: Type=Add, Quantity=2, Note="Purchased new stock"
  
System does:
  1. Converts to base units: 2 cases × 32 = 64 units
  2. Updates Product 1 stock by +64 units
  3. Creates movement for Product 1: +64 units
  4. Creates movement for Product 3: +2 cases
  5. Both products refresh with correct quantities
```

## Database Schema

### `product_relationships` Table

```sql
CREATE TABLE product_relationships (
  id                  BIGINT UNSIGNED PRIMARY KEY
  product_id          BIGINT UNSIGNED (the bundle/case product)
  linked_product_id   BIGINT UNSIGNED (the base product)
  quantity            DECIMAL(12,2) (multiplier: 32, 24, 50, etc.)
  description         TEXT (optional note)
  is_active           BOOLEAN (default: true)
  created_at          TIMESTAMP
  updated_at          TIMESTAMP
  
  UNIQUE(product_id)  -- Each product links to at most one base product
  FK product_id -> products(id) ON DELETE CASCADE
  FK linked_product_id -> products(id) ON DELETE CASCADE
)
```

### Stock Tables (Shared)

**`stock_items`**: One record per base product with current quantity
```sql
stock_items:
  product_id=1, current_qty=160  -- Base product stock
  (No separate record for Product 3; it shares Product 1's qty)
```

**`stock_movements`**: Separate movement records for each product
```sql
Movements for above scenario:
  id=1, product_id=1, qty=64, type='in'  -- Base product movement
  id=2, product_id=3, qty=2,  type='in'  -- Bundle product movement
```

## API Endpoints

### View Stock
```
GET /api/stock/{product_id}
Response: { stock: [...] }
- If product is linked: returns stock divided by ratio
- If product is base: returns actual stock quantity
```

### Adjust Stock
```
POST /api/stock/{product_id}/adjust
Body: {
  "quantity": 32,         // In that product's unit
  "type": "in|out|adjust",
  "note": "Optional note"
}
Response: { ok: true, stock: {...}, movements: [...] }
- Automatically handles ratio conversion
- Creates movements for both products if linked
```

### View Movement History
```
GET /api/stock/{product_id}/movements
Response: { movements: [...] }
- Shows all movements for this specific product
- Quantities shown in that product's unit
- Example: For a case, movements show +2, -1, etc. (not +64, -32)
```

## Service Class: StockSyncService

Located at: `app/Services/StockSyncService.php`

### Methods

#### `adjustStock(Product $product, float $quantity, string $type, array $data)`

Handles all stock adjustments with automatic linked product handling.

**Parameters:**
- `$product`: The product being adjusted
- `$quantity`: Change amount (in that product's unit)
- `$type`: 'in', 'out', or 'adjust'
- `$data`: Optional array with 'note', 'created_by', 'reference_type', 'reference_id'

**Returns:**
```php
[
  'success' => true,
  'message' => 'Stock adjusted successfully',
  'old_qty' => 128.00,
  'new_qty' => 160.00,
  'movements' => [
    Movement object for base product,
    Movement object for bundle (if linked)
  ]
]
```

**Example Usage:**
```php
$service = app(\App\Services\StockSyncService::class);

// Add 5 cases (Product 3)
$result = $service->adjustStock(
  $product3,
  5,
  'in',
  ['note' => 'Received shipment', 'created_by' => auth()->id()]
);

// System automatically:
// - Multiplies 5 × 32 = 160 units
// - Updates Product 1 stock by +160
// - Creates two movements (one for each product)
```

#### `getEffectiveStock(Product $product)`

Gets the correct stock quantity for display.

```php
$stock = $service->getEffectiveStock($product3);
// Returns 5.00 (160 ÷ 32) even though base stock is 160
```

#### `getLinkedProducts(Product $product)`

Gets all products linked to a base product.

```php
$linked = $service->getLinkedProducts($product1);
// Returns: [Product 3, potentially other bundles...]
```

## Testing

### Test File: `test_linked_stock_clean.php`

Run to verify all scenarios:
```bash
php test_linked_stock_clean.php
```

**Output should show:**
```
✅ TEST 1: Add 64 units to Product 1 PASSED
✅ TEST 2: Set Product 3 to 5 cases PASSED
✅ TEST 3: Remove 32 units from Product 1 PASSED
✅ TEST 4: Remove 1 case from Product 3 PASSED
```

## Common Scenarios

### Scenario 1: Receive Shipment of Cases

**Situation**: You receive 10 cases of Coca Cola

**Actions:**
1. Go to Stock Maintenance (`/stock`)
2. Find "Case | 32 X Coca Cola 250ml"
3. Click Adjust
4. Type: "Add"
5. Quantity: "10"
6. Note: "Received shipment from Supplier ABC"
7. Click Save

**Result:**
- Product 3 stock increases by 10 cases
- Product 1 stock increases by 320 units (10 × 32)
- Both products show correct quantities
- Movement history updated for both

### Scenario 2: Sell Individual Units

**Situation**: Customer buys 50 individual Coca Colas (via POS)

**System automatically:**
1. Records sale for Product 1
2. Deducts 50 units from Product 1 stock
3. Automatically deducts 1.5625 cases from Product 3 display (50 ÷ 32)
4. Creates movements for both products
5. No manual action needed!

### Scenario 3: Stock Take & Correction

**Situation**: Physical inventory shows 200 units (Product 1) but system shows 160

**Actions:**
1. Go to Stock Maintenance
2. Find "Coca Cola 250ml"
3. Click Adjust
4. Type: "Set Quantity"
5. Quantity: "200"
6. Note: "Stock take correction"
7. Click Save

**Result:**
- Product 1 set to exactly 200 units
- Product 3 automatically shows 6.25 cases (200 ÷ 32)
- Movement records the adjustment

### Scenario 4: Create New Product Bundle

**Situation**: You want to sell Coca Cola in a new package: "Pack of 24"

**Actions:**
1. POS → Products → Add New Product
2. Name: "Coca Cola 24-Pack"
3. Fill in Price, Cost, etc.
4. Scroll to "Affect Another Product's Stock"
5. Link to Product: "Coca Cola 250ml" (Product 1)
6. Quantity Ratio: "24"
7. Description: "Pack contains 24 individual units"
8. Preview shows: "This product = 24x Coca Cola 250ml..."
9. Click Save

**Result:**
- New product created
- Automatically linked to Product 1
- Stock shared with individual Coca Cola
- When you add 10 packs, system adds 240 individual units

## Best Practices

✅ **DO:**
- Use the UI to create/manage links (never edit database directly)
- Set meaningful descriptions ("Case of 32", "Carton of 50")
- Use consistent names (e.g., "Coca Cola 250ml" + "Case | 32 X Coca Cola 250ml")
- Test adjustment before going live
- Keep stock adjustment notes for audit trail

❌ **DON'T:**
- Create circular links (A→B and B→A)
- Link a product to itself
- Manually edit `stock_items` table for linked products
- Adjust stock in legacy system if using new stock system (causes confusion)

## Troubleshooting

### Problem: Product isn't showing in "Link to Product" dropdown

**Solution:**
- The product must already exist in the system
- You cannot link a product to itself (filtered out)
- Refresh the page or clear browser cache

### Problem: Adjustment saved but quantities don't match expected ratio

**Solution:**
- Check the quantity ratio in product edit form
- Verify the relationship exists: `SELECT * FROM product_relationships`
- Clear cache: `php artisan optimize:clear`
- Test with `php artisan tinker`

### Problem: Stock movements show different quantities for linked products

**This is correct!** Example:
- Adjust Product 3 by +2 cases
- Product 3 movement: qty = 2
- Product 1 movement: qty = 64
- This is expected - each records in its own unit

## Performance Notes

- Linked product adjustments create 2 movement records instead of 1
- Stock queries use aggregation, so no N+1 problem
- Index on `product_relationships.linked_product_id` for fast lookup
- Cache is cleared automatically on product updates

## Future Enhancements

Possible improvements:
- [ ] Support multiple linked products (Product A = 2x Product B + 3x Product C)
- [ ] Automatic stock allocation (sell cases prefer individual units if available)
- [ ] Conversion wizard (bulk convert individual units to cases)
- [ ] Stock level warnings per linked product
- [ ] Barcode support for bundles

---

**Last Updated**: December 8, 2025
**System Version**: Stock Management v2.0