# Inventory & Stores Unification Plan: Accounting System → Catalog System

## Executive Summary

The system currently has **2 separate inventory/warehouse systems** that operate independently:
1. **Accounting Inventory System** - Stores linked to Chart of Accounts for financial tracking
2. **Catalog Inventory System** - Physical warehouses for e-commerce/catalog operations

This document outlines the current state, field comparisons, and tasks required to unify these systems.

---

## 1. Current State Analysis

### 1.1 Existing Inventory/Warehouse Systems

| System | Location | Purpose | Multi-tenant |
|--------|----------|---------|--------------|
| **Accounting Stores** | `/Domains/Accounting/` | Financial tracking, cost valuation, approval workflows | Yes (company_id) |
| **Catalog Warehouses** | `/Domains/Catalog/Inventory/` | Physical stock, serial/batch tracking, reservations | No (catalog-wide) |

### 1.2 System Architecture Overview

```
┌─────────────────────────────────────────────────────────────────────┐
│                     ACCOUNTING SYSTEM                                │
├─────────────────────────────────────────────────────────────────────┤
│  MainInventory ──► ChartOfAccount (store_id) ──► AccountingTransaction│
│       │                    │                                         │
│       └───► ProductStore ──┴───► InventoryTransaction                │
│                  │                      │                            │
│                  └──────────────────────┴───► InventoryValuation     │
└─────────────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────────────┐
│                      CATALOG SYSTEM                                  │
├─────────────────────────────────────────────────────────────────────┤
│  Warehouse ──► Inventory ──► InventoryTransaction                    │
│      │             │                                                 │
│      │             ├──► InventoryBatch                               │
│      │             ├──► InventorySerial ──► InventoryReservation     │
│      │             └──► InventoryCost                                │
│      └─────────────────────────────────────────────────────────────►│
└─────────────────────────────────────────────────────────────────────┘
```

---

## 2. Core Model Comparison

### 2.1 Warehouse/Store Definition

| Aspect | Accounting: `MainInventory` | Catalog: `Warehouse` | Notes |
|--------|----------------------------|---------------------|-------|
| **Table** | `main_inventories` | `catalog_warehouses` | Different tables |
| **Model Path** | `Accounting\Entities\Models\MainInventory` | `Catalog\Inventory\Models\Warehouse` | - |
| **Storage Reference** | FK to `chart_of_accounts` (`store_id`) | Direct table | **Key Difference** |
| **Multi-tenant** | Yes (`company_id`) | No | **Gap in Catalog** |
| **Soft Deletes** | No | Yes | - |

#### Field Comparison: MainInventory vs Warehouse

| Field | MainInventory | Warehouse | Migration Notes |
|-------|---------------|-----------|-----------------|
| `id` | ✅ PK | ✅ PK | - |
| `company_id` | ✅ FK | ❌ Missing | **Gap**: Catalog lacks multi-tenant |
| `store_id` | ✅ FK to `chart_of_accounts` | ❌ N/A | Accounting links to CoA |
| `code` | ✅ Auto-generated (WH prefix) | ✅ Unique | Need mapping strategy |
| `title` | ✅ String | ❌ Missing | Catalog uses `name` |
| `name` | ❌ | ✅ String | Catalog field |
| `location` | ❌ | ✅ String | **Gap**: Accounting lacks location |
| `inventory_type` | ✅ Enum | ❌ | Catalog has separate InventoryType table |
| `for_product` | ✅ Boolean | ❌ | - |
| `for_material` | ✅ Boolean | ❌ | - |
| `for_equipment` | ✅ Boolean | ❌ | - |
| `project_id` | ✅ FK (optional) | ❌ | Project-specific warehouses |
| `status` | ✅ Boolean | ❌ | - |
| `is_active` | ❌ | ✅ Boolean | Similar purpose to `status` |
| `deleted_at` | ❌ | ✅ Soft delete | - |

### 2.2 Stock Level Tracking

| Aspect | Accounting: `ProductStore` | Catalog: `Inventory` | Notes |
|--------|---------------------------|---------------------|-------|
| **Table** | `product_stores` | `catalog_inventories` | - |
| **Primary Key** | Composite (product_id, store_id, unit_id) | variant_id + warehouse_id + type_id | Different structure |
| **Item Reference** | `product_id` (Products/Materials/Equipment) | `variant_id` (ProductVariant only) | **Key Difference** |
| **Multi-tenant** | Yes (`company_id`) | No | - |

#### Field Comparison: ProductStore vs Inventory

| Field | ProductStore | Inventory | Migration Notes |
|-------|--------------|-----------|-----------------|
| `id` | ✅ PK | ✅ PK | - |
| `company_id` | ✅ FK | ❌ | **Gap** |
| `product_id` | ✅ FK | ❌ | References Products/Materials/Equipment |
| `variant_id` | ❌ | ✅ FK | References ProductVariant |
| `store_id` | ✅ FK to `chart_of_accounts` | ❌ | - |
| `warehouse_id` | ❌ | ✅ FK | - |
| `unit_id` | ✅ FK to `measurement_units` | ❌ | **Gap**: Catalog lacks unit tracking |
| `inventory_type_id` | ❌ | ✅ FK | Catalog has type classification |
| `amount` | ✅ Float | ❌ | - |
| `quantity` | ❌ | ✅ Decimal(3) | Similar purpose |
| `price` | ✅ Float | ❌ | Current unit price |
| `type` | ✅ Enum (product/material/equipment) | ❌ | - |
| `status` | ✅ (pending/approved) | ❌ | Approval workflow |
| `deleted_at` | ❌ | ✅ | - |

### 2.3 Inventory Transactions

| Aspect | Accounting: `InventoryTransaction` | Catalog: `InventoryTransaction` | Notes |
|--------|-----------------------------------|--------------------------------|-------|
| **Table** | `inventory_transactions` | `catalog_inventory_transactions` | Different tables |
| **Polymorphic Source** | ✅ `sourceable_type/id` | ✅ `reference_type/id` | Same pattern, different naming |

#### Field Comparison: InventoryTransaction

| Field | Accounting | Catalog | Migration Notes |
|-------|------------|---------|-----------------|
| `id` | ✅ PK | ✅ PK | - |
| `company_id` | ✅ FK | ❌ | **Gap** |
| `product_id` | ✅ FK | ❌ | - |
| `inventory_id` | ❌ | ✅ FK | Catalog references Inventory |
| `store_id` | ✅ FK | ❌ | - |
| `warehouse_id` | ❌ | ✅ FK | - |
| `unit_id` | ✅ FK | ❌ | **Gap** |
| `inventory_type_id` | ❌ | ✅ FK | - |
| `transaction_type` | ✅ Enum (in/out) | ✅ Enum (7 types) | Catalog more granular |
| `quantity` | ✅ Float | ✅ Decimal(3) | - |
| `unit_price` | ✅ Float | ❌ | - |
| `total_price` | ✅ Float | ❌ | - |
| `status` | ✅ (pending/approved) | ❌ | - |
| `type` | ✅ (product/material/equipment) | ❌ | - |
| `reference_number` | ✅ String | ❌ | - |
| `notes` | ✅ Text | ✅ Text | - |
| `sourceable_type/id` | ✅ Polymorphic | ❌ | - |
| `reference_type/id` | ❌ | ✅ Polymorphic | Same pattern |
| `deleted_at` | ❌ | ✅ | - |

**Transaction Type Values:**
- Accounting: `in`, `out`
- Catalog: `purchase`, `sale`, `adjustment`, `transfer`, `reservation`, `release`, `return`

### 2.4 Cost/Valuation Tracking

| Aspect | Accounting: `InventoryValuation` | Catalog: `InventoryCost` | Notes |
|--------|--------------------------------|-------------------------|-------|
| **Table** | `inventory_valuations` | `catalog_inventory_costs` | - |
| **Purpose** | FIFO-style batch tracking | Cost method per warehouse | Different approaches |

#### Field Comparison: Cost Tracking

| Field | InventoryValuation | InventoryCost | Migration Notes |
|-------|-------------------|---------------|-----------------|
| `id` | ✅ PK | ✅ PK | - |
| `company_id` | ✅ FK | ❌ | **Gap** |
| `product_id` | ✅ FK | ❌ | - |
| `inventory_id` / `variant_id` | ❌ | ✅ FK | - |
| `store_id` | ✅ FK | ❌ | - |
| `warehouse_id` | ❌ | ✅ FK | - |
| `unit_id` | ✅ FK | ❌ | **Gap** |
| `quantity` | ✅ Float | ❌ | - |
| `remaining_quantity` | ✅ Float | ❌ | For FIFO depletion |
| `unit_cost` | ✅ Float | ❌ | - |
| `cost` | ❌ | ✅ Decimal(4) | - |
| `total_cost` | ✅ Float | ❌ | - |
| `cost_method` | ❌ | ✅ Enum (fifo/lifo/average) | - |
| `transaction_type` | ✅ (in/out/adjustment) | ❌ | - |
| `transaction_date` | ✅ Date | ❌ | - |
| `effective_from` | ❌ | ✅ Date | - |
| `is_consumed` | ✅ Boolean | ❌ | For FIFO tracking |
| `batch_number` | ✅ Unique | ❌ | Auto-generated (INV prefix) |
| `reference_type/id` | ✅ Polymorphic | ❌ | - |

---

## 3. Feature Comparison Matrix

| Feature | Accounting System | Catalog System | Unified Target |
|---------|------------------|----------------|----------------|
| **Multi-tenant (company_id)** | ✅ Yes | ❌ No | ✅ Required |
| **Soft Deletes** | ❌ No | ✅ Yes | ✅ Recommended |
| **Approval Workflow** | ✅ Yes (pending/approved) | ❌ No | ✅ Keep |
| **Chart of Accounts Link** | ✅ Yes (financial tracking) | ❌ No | ✅ Keep |
| **Physical Location** | ❌ No | ✅ Yes (location field) | ✅ Add |
| **Serial Number Tracking** | ❌ No | ✅ Yes | ✅ Add |
| **Batch/Lot Tracking** | ✅ Partial (valuation batches) | ✅ Yes (with dates) | ✅ Enhance |
| **Reservation System** | ❌ No | ✅ Yes | ✅ Add |
| **Inventory Types** | ❌ No (type field only) | ✅ Yes (affects_availability) | ✅ Add |
| **Unit of Measure** | ✅ Yes | ❌ No | ✅ Keep |
| **Multiple Item Types** | ✅ Yes (product/material/equipment) | ❌ No (variants only) | ✅ Keep |
| **Cost Valuation (FIFO/LIFO/Avg)** | ✅ Yes (FIFO implemented) | ✅ Yes (schema only) | ✅ Merge |
| **Project Assignment** | ✅ Yes (project_id) | ❌ No | ✅ Keep |

---

## 4. Foreign Key Dependencies

### 4.1 Tables Referencing Accounting Stores (`chart_of_accounts.id` as store)

| Table | Column | Domain | Impact |
|-------|--------|--------|--------|
| `main_inventories` | `store_id` | Accounting | Core warehouse definition |
| `product_stores` | `store_id` | Accounting | Stock levels |
| `inventory_transactions` | `store_id` | Accounting | Movement history |
| `inventory_valuations` | `store_id` | Accounting | Cost tracking |
| `store_first_terms` | `store_id` | Accounting | Initial inventory |
| `store_first_term_products` | `store_id` | Accounting | Initial inventory items |
| `manual_store_transactions` | `store_id` | Accounting | Manual adjustments |

### 4.2 Tables Referencing Catalog Warehouses (`catalog_warehouses.id`)

| Table | Column | Domain | Impact |
|-------|--------|--------|--------|
| `catalog_inventories` | `warehouse_id` | Catalog | Stock levels |
| `catalog_inventory_transactions` | `warehouse_id` | Catalog | Movement history |
| `catalog_inventory_serials` | `warehouse_id` | Catalog | Serial locations |
| `catalog_inventory_costs` | `warehouse_id` | Catalog | Cost by warehouse |

---

## 5. Key Files Summary

### 5.1 Accounting Inventory Models

| File | Purpose |
|------|---------|
| [MainInventory.php](app/Domains/Accounting/Entities/Models/MainInventory.php) | Warehouse definition linked to ChartOfAccount |
| [ProductStore.php](app/Domains/Accounting/Entities/Models/ProductStore.php) | Current stock levels with approval workflow |
| [InventoryTransaction.php](app/Domains/Accounting/Entities/Models/InventoryTransaction.php) | Movement tracking |
| [InventoryValuation.php](app/Domains/Accounting/Inventory/Models/InventoryValuation.php) | Cost/valuation tracking |
| [StoreFirstTerm.php](app/Domains/Accounting/Inventory/Models/StoreFirstTerm.php) | Initial inventory entry |
| [StoreProductTransfer.php](app/Domains/Accounting/Inventory/Models/StoreProductTransfer.php) | Inter-store transfers |
| [StoreSettlement.php](app/Domains/Accounting/Inventory/Models/StoreSettlement.php) | Settlement operations |

### 5.2 Catalog Inventory Models

| File | Purpose |
|------|---------|
| [Warehouse.php](app/Domains/Catalog/Inventory/Models/Warehouse.php) | Physical warehouse |
| [Inventory.php](app/Domains/Catalog/Inventory/Models/Inventory.php) | Stock by variant/warehouse/type |
| [InventoryTransaction.php](app/Domains/Catalog/Inventory/Models/InventoryTransaction.php) | Movement tracking |
| [InventoryType.php](app/Domains/Catalog/Inventory/Models/InventoryType.php) | Stock classification |
| [InventoryBatch.php](app/Domains/Catalog/Inventory/Models/InventoryBatch.php) | Batch/lot tracking |
| [InventorySerial.php](app/Domains/Catalog/Inventory/Models/InventorySerial.php) | Serial number tracking |
| [InventoryReservation.php](app/Domains/Catalog/Inventory/Models/InventoryReservation.php) | Order reservations |
| [InventoryCost.php](app/Domains/Catalog/Inventory/Models/InventoryCost.php) | Cost by warehouse |

---

## 6. Unification Strategy Options

### Option A: Enhance Catalog System (Recommended)

**Approach:** Migrate Accounting stores to enhanced Catalog Warehouse system.

**Pros:**
- Modern architecture with soft deletes
- Better batch/serial tracking
- Reservation system
- Cleaner separation of concerns

**Cons:**
- Need to add multi-tenant support
- Need to add ChartOfAccount integration
- More migration work

### Option B: Enhance Accounting System

**Approach:** Add Catalog features to Accounting inventory.

**Pros:**
- Already has financial integration
- Multi-tenant ready
- Less migration of existing data

**Cons:**
- Tightly coupled to ChartOfAccount
- Lacks modern features (soft deletes, reservations)
- Harder to extend

### Option C: Unified Bridge Layer

**Approach:** Create a bridge/sync layer between both systems.

**Pros:**
- Keep both systems operational
- Gradual migration possible

**Cons:**
- Added complexity
- Sync issues possible
- Maintenance overhead

---

## 7. Tasks for Unification (Recommended: Option A)

### Phase 1: Schema Enhancement (Catalog System)

- [ ] **Task 1.1**: Add `company_id` FK to `catalog_warehouses` for multi-tenant support
- [ ] **Task 1.2**: Add `chart_account_id` FK to `catalog_warehouses` for financial integration
- [ ] **Task 1.3**: Add `title` JSON field to `catalog_warehouses` for translations
- [ ] **Task 1.4**: Add `for_product`, `for_material`, `for_equipment` boolean flags
- [ ] **Task 1.5**: Add `project_id` FK for project-specific warehouses
- [ ] **Task 1.6**: Add company scope trait to all Catalog Inventory models
- [ ] **Task 1.7**: Add `unit_id` FK to `catalog_inventories` and related tables
- [ ] **Task 1.8**: Add `status` (pending/approved) to `catalog_inventories`
- [ ] **Task 1.9**: Add `type` field (product/material/equipment) to `catalog_inventories`
- [ ] **Task 1.10**: Add price tracking fields to `catalog_inventories`

### Phase 2: Data Migration

- [ ] **Task 2.1**: Create warehouse mapping table (`warehouse_migration_map`)
- [ ] **Task 2.2**: Migrate `main_inventories` data to `catalog_warehouses`
- [ ] **Task 2.3**: Migrate `product_stores` data to `catalog_inventories`
- [ ] **Task 2.4**: Migrate `inventory_transactions` to `catalog_inventory_transactions`
- [ ] **Task 2.5**: Migrate `inventory_valuations` to enhanced `catalog_inventory_costs`
- [ ] **Task 2.6**: Create ChartOfAccount entries for each migrated warehouse

### Phase 3: Foreign Key Migration

- [ ] **Task 3.1**: Update all tables referencing `main_inventories.store_id` to use new warehouse_id
- [ ] **Task 3.2**: Update `store_first_terms` to reference `catalog_warehouses`
- [ ] **Task 3.3**: Update `store_first_term_products` to reference `catalog_warehouses`
- [ ] **Task 3.4**: Update `manual_store_transactions` to reference `catalog_warehouses`

**Tables Requiring FK Updates:**
1. `store_first_terms`
2. `store_first_term_products`
3. `manual_store_transactions`
4. Any custom tables referencing store_id

### Phase 4: Service Layer Updates

- [ ] **Task 4.1**: Create unified `InventoryService` with all operations
- [ ] **Task 4.2**: Add ChartOfAccount integration to Catalog WarehouseService
- [ ] **Task 4.3**: Migrate `ProductStore::incrementOrCreate` logic to unified service
- [ ] **Task 4.4**: Migrate `ProductStore::decreaseOrCreate` logic to unified service
- [ ] **Task 4.5**: Integrate `InventoryValuationService` with Catalog system
- [ ] **Task 4.6**: Update all controllers using Accounting inventory to use unified service
- [ ] **Task 4.7**: Add approval workflow to Catalog inventory operations

### Phase 5: API Route Consolidation

- [ ] **Task 5.1**: Deprecate Accounting Inventory API routes
- [ ] **Task 5.2**: Add new unified inventory API endpoints
- [ ] **Task 5.3**: Create backward-compatible route aliases
- [ ] **Task 5.4**: Update API documentation

### Phase 6: Financial Integration

- [ ] **Task 6.1**: Ensure warehouse creation triggers ChartOfAccount creation
- [ ] **Task 6.2**: Link inventory transactions to AccountingTransaction
- [ ] **Task 6.3**: Maintain cost tracking for financial reports
- [ ] **Task 6.4**: Update all financial reports to use new structure

### Phase 7: Cleanup

- [ ] **Task 7.1**: Remove `MainInventory` model
- [ ] **Task 7.2**: Remove `ProductStore` model
- [ ] **Task 7.3**: Remove Accounting `InventoryTransaction` model
- [ ] **Task 7.4**: Remove `InventoryValuation` model (merge with InventoryCost)
- [ ] **Task 7.5**: Drop deprecated tables after data verification
- [ ] **Task 7.6**: Remove unused services and controllers
- [ ] **Task 7.7**: Clean up routes

---

## 8. Risk Assessment

| Risk | Impact | Likelihood | Mitigation |
|------|--------|------------|------------|
| Data loss during migration | High | Medium | Create full backups, use transactions, verify row counts |
| Breaking financial reports | High | High | Maintain ChartOfAccount links, thorough testing |
| Approval workflow disruption | High | Medium | Keep status field, migrate pending items |
| Foreign key constraint violations | High | Medium | Proper migration order, disable checks temporarily |
| API breaking changes | Medium | High | Deprecation period, backward-compatible routes |
| Performance degradation | Medium | Low | Optimize queries, add indexes |
| Multi-tenant data leaks | Critical | Low | Test company scoping thoroughly |

---

## 9. Rollback Plan

1. Keep backup of all original tables for 30+ days
2. Maintain old models as deprecated (not deleted) during transition
3. Use feature flags to switch between old/new systems
4. Document all migration scripts for reversal
5. Create automated tests to verify data integrity

---

## 10. Estimated Scope

| Category | Count |
|----------|-------|
| Tables with schema changes | 8+ |
| Tables with FK changes | 6+ |
| Models to update | 12+ |
| Models to remove | 6+ |
| Services to update | 8+ |
| New migrations needed | 10-15 |
| API routes affected | 30+ |

---

## 11. Decision Points Requiring Clarification

1. **Should Catalog Warehouses be multi-tenant (per company) or shared across companies?**
   - Current: No company_id
   - Recommendation: Add company_id for consistency

2. **Should we maintain separate InventoryValuation or merge with InventoryCost?**
   - Current: Different approaches
   - Recommendation: Merge, keeping FIFO capabilities

3. **How to handle existing pending transactions during migration?**
   - Option A: Migrate as-is
   - Option B: Force approval before migration

4. **Should serial/batch tracking be optional or mandatory?**
   - Recommendation: Optional, configurable per warehouse

---

*Document Created: 2026-02-08*
*Status: Planning Phase*
*Related Document: [UNIT_UNIFICATION_PLAN.md](UNIT_UNIFICATION_PLAN.md)*