# Sub-Location System Implementation Guide

**Status**: COMPLETE ✅  
**Date**: 2026-01-18  
**Version**: 1.0

## Overview

The sub-location system adds granular location tracking (suite, room, area, zone, bay, aisle) within main locations throughout the ERP system.

## Architecture

### Database Structure

**Main Table**: `sub_locations`
- `id` - Primary key
- `parent_location_id` - References locations(id)
- `code` - Unique within parent location
- `name` - Display name (e.g., "Suite 101", "Room A")
- `type` - ENUM: suite, room, area, zone, bay, aisle
- `description`, `square_footage`, `status`, `display_order`

**Tables with sub_location_id support** (nullable, backward compatible):

**Phase 2 - High Priority (Inventory)**:
- `inventory` - Track stock at sub-location level
- `stock_movements` - Movement history with sub-location
- `lot_serial_numbers` - Precise lot/serial location tracking
- `goods_receipts` - Receiving into specific sub-location

**Phase 3 - Operations**:
- `operations_equipment` - Equipment physical placement
- `operations_assignments` - Staff work area assignments
- `equipment_lines` - Production line location

**Phase 4 - Remaining**:
- `inventory_transactions` - Transaction history
- `job_positions` - Position assignment location

**Data Access Control**:
- `role_data_scopes` - Permission scoping to sub-locations

## Usage in Forms

### Using the Cascading Sub-Location Selector

The reusable component handles all the complexity of cascading dropdowns:

```php
<?php
// Include the component
include BASE_PATH . '/views/components/sub_location_select.php';

// Render with defaults (location required, sub-location optional)
renderSubLocationSelect(
    'location_id',           // Location field name
    'sub_location_id',       // Sub-location field name
    $selectedLocationId,     // Currently selected location (or null)
    $selectedSubLocationId   // Currently selected sub-location (or null)
);

// With custom options
renderSubLocationSelect(
    'location_id',
    'sub_location_id',
    $selectedLocationId,
    $selectedSubLocationId,
    [
        'locationLabel' => 'Warehouse',
        'subLocationLabel' => 'Area',
        'locationRequired' => true,
        'subLocationRequired' => false,  // Optional by default
        'allowNone' => true,              // Allow "None" option
        'locationColClass' => 'col-md-6',
        'subLocationColClass' => 'col-md-6'
    ]
);
?>
```

### Form Example - Inventory Create

```php
<!-- Before: Just location -->
<div class="mb-3">
    <label>Location</label>
    <select name="location_id" class="form-select" required>
        <option value="">Select...</option>
        <?php foreach ($locations as $loc): ?>
            <option value="<?= $loc['id'] ?>"><?= e($loc['name']) ?></option>
        <?php endforeach; ?>
    </select>
</div>

<!-- After: Location + Sub-Location (cascading) -->
<?php
include BASE_PATH . '/views/components/sub_location_select.php';
renderSubLocationSelect('location_id', 'sub_location_id', null, null);
?>
```

## Usage in Controllers

### Storing Data

```php
public function store() {
    // ... validation ...
    
    $data = [
        'product_id' => (int)$_POST['product_id'],
        'location_id' => (int)$_POST['location_id'],
        'sub_location_id' => !empty($_POST['sub_location_id']) ? (int)$_POST['sub_location_id'] : null,
        'quantity' => (float)$_POST['quantity']
    ];
    
    $id = $this->inventoryModel->create($data);
    // ...
}
```

### Querying Data

```php
// Simple query
$sql = "SELECT * FROM inventory WHERE product_id = ?";
$params = [$productId];

// With optional sub-location filter
if ($subLocationId) {
    $sql .= " AND sub_location_id = ?";
    $params[] = $subLocationId;
}

// Apply data scope filtering (automatically handles sub-locations)
$sql = applyDataScopeFilter($sql, '', 'location_id', 'employee_id', 'sub_location_id');

$results = $this->db->fetchAll($sql, $params);
```

### Displaying Sub-Location Info

```php
// In queries, join to get sub-location details
$sql = "SELECT i.*, 
               l.name as location_name,
               sl.name as sub_location_name,
               sl.type as sub_location_type
        FROM inventory i
        LEFT JOIN locations l ON i.location_id = l.id
        LEFT JOIN sub_locations sl ON i.sub_location_id = sl.id
        WHERE i.product_id = ?";
```

## Usage in Views

### Displaying Sub-Location

```php
<!-- In table rows -->
<td>
    <?= e($item['location_name']) ?>
    <?php if ($item['sub_location_name']): ?>
        <br>
        <small class="text-muted">
            <i class="bi bi-geo-alt"></i>
            <?= e($item['sub_location_name']) ?>
            (<?= e(ucfirst($item['sub_location_type'])) ?>)
        </small>
    <?php endif; ?>
</td>

<!-- In detail pages -->
<div class="mb-3">
    <label class="text-muted">Location</label>
    <div>
        <?= e($record['location_name']) ?>
        <?php if ($record['sub_location_name']): ?>
            <span class="badge badge-outline-info ms-2">
                <i class="bi bi-geo-alt"></i>
                <?= e($record['sub_location_name']) ?>
            </span>
        <?php endif; ?>
    </div>
</div>
```

## Data Access Control

### Scoping by Sub-Location

Users can be restricted to specific sub-locations within a location:

```php
// In role_data_scopes table:
// scope_type: 'location'
// location_id: 3
// sub_location_id: 5  (restricts to specific sub-location)

// OR

// scope_type: 'location'
// location_id: 3
// sub_location_id: NULL  (access to all sub-locations in location 3)
```

The `applyDataScopeFilter()` function automatically handles this:

```php
// This query will be automatically filtered
$sql = "SELECT * FROM inventory WHERE product_id = ?";
$sql = applyDataScopeFilter($sql);
// Result: includes sub_location_id filtering if user has sub-location restrictions
```

## API Endpoints

### Get Sub-Locations by Parent Location

```javascript
// Used by cascading dropdown
fetch('/api/sub-locations/by-location?location_id=3')
    .then(response => response.json())
    .then(data => {
        // data.sub_locations = [...]
    });
```

### Browse/Search Sub-Locations

```javascript
// Search across all sub-locations
fetch('/api/sub-locations?search=suite&location_id=3')
    .then(response => response.json())
    .then(data => {
        // data.sub_locations = [...]
    });
```

### Quick Add Sub-Location

```javascript
// Add sub-location via AJAX
fetch('/sub-locations/quick-add', {
    method: 'POST',
    body: new FormData(form)
})
.then(response => response.json())
.then(data => {
    if (data.success) {
        // data.sub_location = { id, code, name, type }
    }
});
```

## Settings Management

Sub-location types can be managed at:
**Settings > Dropdowns > Operations > Sub-Location Types**

Default types:
- Suite
- Room
- Area
- Zone
- Bay
- Aisle

## Migration Strategy

### Backward Compatibility

All `sub_location_id` columns are:
- **Nullable** (NULL = location-level, no sub-location specified)
- **Backward compatible** (existing records work without changes)
- **Optional** in forms (defaults to "None")

### Phased Rollout

1. **Phase 1**: Infrastructure (COMPLETE)
   - Sub-locations management UI
   - Cascading selector component
   - Settings integration

2. **Phase 2**: High-Priority Tables (COMPLETE)
   - Inventory, stock movements, lot/serial, goods receipts
   - Update forms to include sub-location selector

3. **Phase 3**: Operations Tables (COMPLETE)
   - Equipment, assignments, production lines

4. **Phase 4**: Remaining Tables (COMPLETE)
   - Transactions, positions

5. **Phase 5**: Gradual Form Updates (IN PROGRESS)
   - Update create/edit forms as needed
   - No rush - backward compatible

## Example: Complete Implementation

### 1. Update Model (if needed)

Most models work as-is since they use dynamic INSERT/UPDATE. Only update if you need specific sub-location queries:

```php
class InventoryModel extends BaseModel {
    public function getByLocationAndSubLocation($locationId, $subLocationId = null) {
        $sql = "SELECT * FROM inventory WHERE location_id = ?";
        $params = [$locationId];
        
        if ($subLocationId) {
            $sql .= " AND sub_location_id = ?";
            $params[] = $subLocationId;
        }
        
        return $this->db->fetchAll($sql, $params);
    }
}
```

### 2. Update Controller

```php
public function create() {
    // ... load data ...
    $this->layout('inventory/create', [
        'pageTitle' => 'Add Inventory',
        'selectedLocationId' => null,
        'selectedSubLocationId' => null
    ]);
}

public function store() {
    $data = [
        'product_id' => (int)$_POST['product_id'],
        'location_id' => (int)$_POST['location_id'],
        'sub_location_id' => !empty($_POST['sub_location_id']) ? (int)$_POST['sub_location_id'] : null,
        'quantity' => (float)$_POST['quantity']
    ];
    
    $this->inventoryModel->create($data);
    redirect(base_url('inventory'));
}
```

### 3. Update View

```php
<!-- In views/inventory/create.php -->
<form method="POST" action="<?= base_url('inventory/store') ?>">
    <?= csrf_field() ?>
    
    <!-- Product selection -->
    <div class="mb-3">
        <label>Product</label>
        <select name="product_id" class="form-select" required>
            <!-- ... -->
        </select>
    </div>
    
    <!-- Replace single location dropdown with cascading selector -->
    <?php
    include BASE_PATH . '/views/components/sub_location_select.php';
    renderSubLocationSelect('location_id', 'sub_location_id', null, null);
    ?>
    
    <!-- Rest of form -->
    <div class="mb-3">
        <label>Quantity</label>
        <input type="number" name="quantity" class="form-control" required>
    </div>
    
    <button type="submit" class="btn btn-primary">Save</button>
</form>
```

## Testing Checklist

### Basic Functionality
- [ ] Create sub-location for a location
- [ ] Edit sub-location
- [ ] Delete sub-location (check if it has data)
- [ ] View sub-locations list with filters

### Cascading Dropdown
- [ ] Select location → sub-locations load
- [ ] Change location → sub-locations update
- [ ] Select "None" → saves NULL for sub_location_id
- [ ] Form submission includes correct values

### Data Operations
- [ ] Create record with sub-location
- [ ] Create record without sub-location (NULL)
- [ ] View record shows sub-location if present
- [ ] Edit record can change sub-location
- [ ] Query filtering by sub-location works

### Permissions
- [ ] Data scope filtering respects sub-location restrictions
- [ ] Admin sees all sub-locations
- [ ] Restricted user sees only assigned sub-locations

## Troubleshooting

### Sub-Locations Not Loading
- Check API endpoint: `/api/sub-locations/by-location?location_id=X`
- Verify sub-locations exist for that location
- Check browser console for JavaScript errors

### Foreign Key Errors
- Ensure sub_location_id exists in sub_locations table
- Check that parent location_id matches the sub-location's parent

### Permission Issues
- Verify user has `inventory.view` permission
- Check role_data_scopes for sub-location restrictions

## Future Enhancements

Possible future additions:
- [ ] Sub-location capacity tracking
- [ ] Sub-location utilization reports
- [ ] Visual floor plan mapping
- [ ] Barcode/QR code generation for sub-locations
- [ ] Sub-location hierarchy (sub-sub-locations)

## Summary

The sub-location system is **fully backward compatible** and **incrementally adoptable**:

✅ **Database**: All tables updated  
✅ **API**: Endpoints ready  
✅ **Component**: Reusable selector available  
✅ **Settings**: Types configurable  
✅ **Permissions**: Data scope filtering updated  

**Next Steps**: Gradually update forms to use the sub-location selector component as needed. No rush - existing functionality continues to work.