orion/docs/implementation/inventory-admin-migration.md

# Admin Inventory Management Migration Plan

## Overview

**Objective:** Add inventory management capabilities to the admin "Store Operations" section, allowing administrators to view and manage store inventory on their behalf.

**Status:** Phase 1 Complete

---

## Current State Analysis

### What Exists

The inventory system is **fully implemented at the store API level** with comprehensive functionality:

| Component | Status | Location |
|-----------|--------|----------|
| Database Model | ✅ Complete | `models/database/inventory.py` |
| Pydantic Schemas | ✅ Complete | `models/schema/inventory.py` |
| Service Layer | ✅ Complete | `app/services/inventory_service.py` |
| Store API | ✅ Complete | `app/api/v1/store/inventory.py` |
| Exceptions | ✅ Complete | `app/exceptions/inventory.py` |
| Unit Tests | ✅ Complete | `tests/unit/services/test_inventory_service.py` |
| Integration Tests | ✅ Complete | `tests/integration/api/v1/store/test_inventory.py` |
| Store UI | 🔲 Placeholder | `app/templates/store/inventory.html` |
| Admin API | 🔲 Not Started | - |
| Admin UI | 🔲 Not Started | - |
| Audit Trail | 🔲 Not Started | Logs only, no dedicated table |

### Storage/Location Architecture

The inventory system tracks stock at the **storage location level**:

```
Product A
├── WAREHOUSE_MAIN: 100 units (10 reserved)
├── WAREHOUSE_WEST: 50 units (0 reserved)
└── STORE_FRONT: 25 units (5 reserved)

Total: 175 units | Reserved: 15 | Available: 160
```

**Key design decisions:**
- One product can have inventory across multiple locations
- Unique constraint: `(product_id, location)` - one entry per product/location
- Locations are text strings, normalized to UPPERCASE
- Available quantity = `quantity - reserved_quantity`

### Existing Operations

| Operation | Description | Service Method |
|-----------|-------------|----------------|
| **Set** | Replace exact quantity at location | `set_inventory()` |
| **Adjust** | Add/remove quantity (positive/negative) | `adjust_inventory()` |
| **Reserve** | Mark items for pending order | `reserve_inventory()` |
| **Release** | Cancel reservation | `release_reservation()` |
| **Fulfill** | Complete order (reduces both qty & reserved) | `fulfill_reservation()` |
| **Get Product** | Summary across all locations | `get_product_inventory()` |
| **Get Store** | List with filters | `get_store_inventory()` |
| **Update** | Partial field update | `update_inventory()` |
| **Delete** | Remove inventory entry | `delete_inventory()` |

### Store API Endpoints

All endpoints at `/api/v1/store/inventory/*`:

| Method | Endpoint | Operation |
|--------|----------|-----------|
| POST | `/inventory/set` | Set exact quantity |
| POST | `/inventory/adjust` | Add/remove quantity |
| POST | `/inventory/reserve` | Reserve for order |
| POST | `/inventory/release` | Cancel reservation |
| POST | `/inventory/fulfill` | Complete order |
| GET | `/inventory/product/{id}` | Product summary |
| GET | `/inventory` | List with filters |
| PUT | `/inventory/{id}` | Update entry |
| DELETE | `/inventory/{id}` | Delete entry |

---

## Gap Analysis

### What's Missing for Admin Store Operations

1. **Admin API Endpoints** - ✅ Implemented in Phase 1
2. **Admin UI Page** - No inventory management interface in admin panel
3. **Store Selector** - Admin needs to select which store to manage
4. **Cross-Store View** - ✅ Implemented in Phase 1
5. **Audit Trail** - Only application logs, no queryable audit history
6. **Bulk Operations** - No bulk adjust/import capabilities
7. **Low Stock Alerts** - Basic filter exists, no alert configuration

### Digital Products - Infinite Inventory ✅

**Implementation Complete**

Digital products now have unlimited inventory by default:

```python
# In Product model (models/database/product.py)
UNLIMITED_INVENTORY = 999999

@property
def has_unlimited_inventory(self) -> bool:
    """Digital products have unlimited inventory."""
    return self.is_digital

@property
def available_inventory(self) -> int:
    """Calculate available inventory (total - reserved).

    Digital products return unlimited inventory.
    """
    if self.has_unlimited_inventory:
        return self.UNLIMITED_INVENTORY
    return sum(inv.available_quantity for inv in self.inventory_entries)
```

**Behavior:**
- Physical products: Sum of inventory entries (0 if no entries)
- Digital products: Returns `999999` (unlimited) regardless of entries
- Orders for digital products never fail due to "insufficient inventory"

**Tests:** `tests/unit/models/database/test_product.py::TestProductInventoryProperties`

**Documentation:** [Inventory Management Guide](../guides/inventory-management.md)

---

## Migration Plan

### Phase 1: Admin API Endpoints

**Goal:** Expose inventory management to admin users with store selection

#### 1.1 New File: `app/api/v1/admin/inventory.py`

Admin endpoints that mirror store functionality with store_id as parameter:

| Method | Endpoint | Description |
|--------|----------|-------------|
| GET | `/admin/inventory` | List all inventory (cross-store) |
| GET | `/admin/inventory/stores/{store_id}` | Store-specific inventory |
| GET | `/admin/inventory/products/{product_id}` | Product inventory summary |
| POST | `/admin/inventory/set` | Set inventory (requires store_id) |
| POST | `/admin/inventory/adjust` | Adjust inventory |
| PUT | `/admin/inventory/{id}` | Update inventory entry |
| DELETE | `/admin/inventory/{id}` | Delete inventory entry |
| GET | `/admin/inventory/low-stock` | Low stock report |

#### 1.2 Schema Extensions

Add admin-specific request schemas in `models/schema/inventory.py`:

```python
class AdminInventoryCreate(InventoryCreate):
    """Admin version - requires explicit store_id."""
    store_id: int = Field(..., description="Target store ID")

class AdminInventoryAdjust(InventoryAdjust):
    """Admin version - requires explicit store_id."""
    store_id: int = Field(..., description="Target store ID")

class AdminInventoryListResponse(BaseModel):
    """Cross-store inventory list."""
    inventories: list[InventoryResponse]
    total: int
    skip: int
    limit: int
    store_filter: int | None = None
```

#### 1.3 Service Layer Reuse

The existing `InventoryService` already accepts `store_id` as a parameter - **no service changes needed**. Admin endpoints simply pass the store_id from the request instead of from the JWT token.

### Phase 2: Admin UI

**Goal:** Create admin inventory management page

#### 2.1 New Files

| File | Description |
|------|-------------|
| `app/templates/admin/inventory.html` | Main inventory page |
| `static/admin/js/inventory.js` | Alpine.js controller |

#### 2.2 UI Features

1. **Store Selector Dropdown** - Filter by store (or show all)
2. **Inventory Table** - Product, Location, Quantity, Reserved, Available
3. **Search/Filter** - By product name, location, low stock
4. **Adjust Modal** - Quick add/remove with reason
5. **Pagination** - Handle large inventories
6. **Export** - CSV download (future)

#### 2.3 Page Layout

```
┌─────────────────────────────────────────────────────────┐
│ Inventory Management                    [Store: All ▼] │
├─────────────────────────────────────────────────────────┤
│ [Search...] [Location ▼] [Low Stock Only ☐]            │
├─────────────────────────────────────────────────────────┤
│ Product      │ Location    │ Qty │ Reserved │ Available │
│──────────────┼─────────────┼─────┼──────────┼───────────│
│ Widget A     │ WAREHOUSE_A │ 100 │ 10       │ 90   [⚙️] │
│ Widget A     │ WAREHOUSE_B │ 50  │ 0        │ 50   [⚙️] │
│ Gadget B     │ WAREHOUSE_A │ 25  │ 5        │ 20   [⚙️] │
├─────────────────────────────────────────────────────────┤
│ Showing 1-20 of 150                    [< 1 2 3 ... >]  │
└─────────────────────────────────────────────────────────┘
```

#### 2.4 Sidebar Integration

Add to `app/templates/admin/partials/sidebar.html`:

```html
<!-- Under Store Operations section -->
<li class="relative px-6 py-3">
    <a href="/admin/inventory" ...>
        <span class="inline-flex items-center">
            <!-- inventory icon -->
            <span class="ml-4">Inventory</span>
        </span>
    </a>
</li>
```

### Phase 3: Audit Trail (Optional Enhancement)

**Goal:** Track inventory changes with queryable history

#### 3.1 Database Migration

```sql
CREATE TABLE inventory_audit_log (
    id SERIAL PRIMARY KEY,
    inventory_id INTEGER REFERENCES inventory(id) ON DELETE SET NULL,
    product_id INTEGER NOT NULL,
    store_id INTEGER NOT NULL,
    location VARCHAR(255) NOT NULL,

    -- Change details
    operation VARCHAR(50) NOT NULL,  -- 'set', 'adjust', 'reserve', 'release', 'fulfill'
    quantity_before INTEGER,
    quantity_after INTEGER,
    reserved_before INTEGER,
    reserved_after INTEGER,
    adjustment_amount INTEGER,

    -- Context
    reason VARCHAR(500),
    performed_by INTEGER REFERENCES users(id),
    performed_by_type VARCHAR(20) NOT NULL,  -- 'store', 'admin', 'system'

    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_audit_store ON inventory_audit_log(store_id);
CREATE INDEX idx_audit_product ON inventory_audit_log(product_id);
CREATE INDEX idx_audit_created ON inventory_audit_log(created_at);
```

#### 3.2 Service Enhancement

Add audit logging to `InventoryService` methods:

```python
def _log_audit(
    self,
    db: Session,
    inventory: Inventory,
    operation: str,
    qty_before: int,
    qty_after: int,
    reserved_before: int,
    reserved_after: int,
    user_id: int | None,
    user_type: str,
    reason: str | None = None
) -> None:
    """Record inventory change in audit log."""
    audit = InventoryAuditLog(
        inventory_id=inventory.id,
        product_id=inventory.product_id,
        store_id=inventory.store_id,
        location=inventory.location,
        operation=operation,
        quantity_before=qty_before,
        quantity_after=qty_after,
        reserved_before=reserved_before,
        reserved_after=reserved_after,
        adjustment_amount=qty_after - qty_before,
        reason=reason,
        performed_by=user_id,
        performed_by_type=user_type,
    )
    db.add(audit)
```

---

## Implementation Checklist

### Phase 1: Admin API ✅
- [x] Create `app/api/v1/admin/inventory.py`
- [x] Add admin inventory schemas to `models/schema/inventory.py`
- [x] Register routes in `app/api/v1/admin/__init__.py`
- [x] Write integration tests `tests/integration/api/v1/admin/test_inventory.py`

### Phase 2: Admin UI
- [ ] Create `app/templates/admin/inventory.html`
- [ ] Create `static/admin/js/inventory.js`
- [ ] Add route in `app/routes/admin.py`
- [ ] Add sidebar menu item
- [ ] Update `static/admin/js/init-alpine.js` for page mapping

### Phase 3: Audit Trail (Optional)
- [ ] Create Alembic migration for `inventory_audit_log` table
- [ ] Create `models/database/inventory_audit_log.py`
- [ ] Update `InventoryService` with audit logging
- [ ] Add audit history endpoint
- [ ] Add audit history UI component

---

## Testing Strategy

### Unit Tests
- Admin schema validation tests
- Audit log creation tests (if implemented)

### Integration Tests
- Admin inventory endpoints with authentication
- Store isolation verification (admin can access any store)
- Audit trail creation on operations

### Manual Testing
- Verify store selector works correctly
- Test adjust modal workflow
- Confirm pagination with large datasets

---

## Rollback Plan

Each phase is independent:

1. **Phase 1 Rollback:** Remove admin inventory routes from `__init__.py`
2. **Phase 2 Rollback:** Remove sidebar link, delete template/JS files
3. **Phase 3 Rollback:** Run down migration to drop audit table

---

## Dependencies

- Existing `InventoryService` (no changes required)
- Admin authentication (`get_current_admin_api`)
- Store model for store selector dropdown

---

## Related Documentation

- [Store Operations Expansion Plan](../development/migration/store-operations-expansion.md)
- [Admin Integration Guide](../backend/admin-integration-guide.md)
- [Architecture Patterns](../architecture/architecture-patterns.md)