# Products & Variants Unification Plan: Sales Accounting → Catalog System

## Executive Summary

The system currently has **4 separate product/item systems** that operate independently:
1. **Sales Accounting Products** (`sales_products`) - Products for sales/invoicing with pricing and stock tracking
2. **Construction Materials** (`materials`) - Materials for construction projects with supplier relationships
3. **Catalog Materials** (`catalog_materials`) - Materials used in Bill of Materials (BOM) for product variants
4. **Catalog Products** (`catalog_products`) - Modern product catalog with variants, hierarchical categories, and multi-language support

This document outlines the current state, field comparisons, and tasks required to unify these systems into a cohesive product and variant management system.

---

## 1. Current State Analysis

### 1.1 Existing Product/Material Systems

| System | Location | Purpose | Multi-tenant | Variants |
|--------|----------|---------|--------------|----------|
| **Sales Products** | `/Domains/Accounting/Sales/` | Sales/invoicing, pricing, stock | Yes (company_id) | No (product-level only) |
| **Construction Materials** | `/Domains/Construction/Material/` | Construction materials, suppliers | Yes (company_id) | No |
| **Catalog Materials** | `/Domains/Catalog/Products/` | BOM materials for variants | No | No |
| **Catalog Products** | `/Domains/Catalog/Products/` | E-commerce catalog, variants | No | Yes (variant-based) |

### 1.2 System Architecture Overview

```
┌─────────────────────────────────────────────────────────────────────┐
│                  SALES ACCOUNTING SYSTEM                             │
├─────────────────────────────────────────────────────────────────────┤
│  Product ──► ProductPrice ──► SalesInvoiceProduct                  │
│     │              │                                                  │
│     ├──► PurchaseProduct                                           │
│     ├──► ProductStore ──► InventoryTransaction                     │
│     └──► ProductPriceHistory                                        │
└─────────────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────────────┐
│                  CONSTRUCTION MATERIALS SYSTEM                      │
├─────────────────────────────────────────────────────────────────────┤
│  Material ──► ProductStore ──► InventoryTransaction                │
│     │              │                                                  │
│     ├──► PurchaseProduct                                           │
│     ├──► SalesInvoiceProduct                                      │
│     └──► Supplier (many-to-many)                                   │
└─────────────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────────────┐
│                      CATALOG SYSTEM                                 │
├─────────────────────────────────────────────────────────────────────┤
│  Product ──► ProductVariant ──► ProductVariantPrice                │
│     │              │                      │                          │
│     │              ├──► ProductVariantUnit                          │
│     │              ├──► ProductVariantMaterial ──► CatalogMaterial  │
│     │              ├──► Inventory (warehouse stock)                  │
│     │              └──► InventoryTransaction                        │
│     │                                                                 │
│     ├──► ProductImage                                               │
│     ├──► ProductAlias                                               │
│     └──► CategoryItem (hierarchical)                                │
└─────────────────────────────────────────────────────────────────────┘
```

---

## 2. Core Model Comparison

### 2.1 Product Definition

| Aspect | Sales: `Product` | Catalog: `Product` | Construction: `Material` | Catalog: `Material` | Notes |
|--------|------------------|-------------------|---------------------------|---------------------|-------|
| **Table** | `sales_products` | `catalog_products` | `materials` | `catalog_materials` | Different tables |
| **Model Path** | `Accounting\Sales\Models\Product` | `Catalog\Products\Models\Product` | `Construction\Material\Models\Material` | `Catalog\Products\Models\Material` | - |
| **Multi-tenant** | Yes (`company_id`) | No | Yes (`company_id`) | No | **Gap in Catalog** |
| **Soft Deletes** | Yes | Yes | Yes | Yes | - |
| **Variants** | No | Yes (ProductVariant) | No | No | **Key Difference** |

#### Field Comparison: Sales Product vs Catalog Product

| Field | Sales Product | Catalog Product | Migration Notes |
|-------|---------------|-----------------|-----------------|
| `id` | ✅ PK | ✅ PK | - |
| `company_id` | ✅ FK | ❌ Missing | **Gap**: Catalog lacks multi-tenant |
| `name` | ✅ String | ❌ | Catalog uses `canonical_name` (JSON) |
| `canonical_name` | ❌ | ✅ JSON (translatable) | **Gap**: Sales lacks translations |
| `description` | ✅ Text | ✅ JSON (translatable) | Different formats |
| `code` | ✅ String (auto-generated) | ❌ | Catalog uses SKU in variants |
| `status` | ✅ Boolean | ✅ Enum | Different types |
| `section` | ✅ Enum (accounting/procurement) | ❌ | Sales-specific |
| `type` | ✅ String | ❌ | Sales-specific |
| `track_stock` | ✅ Boolean | ❌ | Sales-specific |
| `initial_stock_level` | ✅ Decimal | ❌ | Sales-specific |
| `barcode` | ✅ String | ❌ | Catalog uses in variants |
| `category_id` | ✅ FK | ❌ | Sales uses simple category |
| `catalog_item_id` | ❌ | ✅ FK | Catalog uses hierarchical category |
| `parent_id` | ❌ | ✅ FK | Catalog supports product hierarchy |
| `brand` | ❌ | ✅ String | **Gap**: Sales lacks brand |
| `manufacturer` | ❌ | ✅ String | **Gap**: Sales lacks manufacturer |
| `product_type` | ❌ | ✅ Enum (physical/digital/spare_part) | Catalog-specific |
| `tax_id` | ✅ FK | ❌ | Sales-specific |
| `purchase_tax_id` | ✅ FK | ❌ | Sales-specific |
| `purchase_account_id` | ✅ FK | ❌ | Sales-specific |
| `revenue_account_id` | ❌ | ✅ FK | Catalog-specific |
| `expense_account_id` | ❌ | ✅ FK | Catalog-specific |
| `inventory_account_id` | ❌ | ✅ FK | Catalog-specific |
| `cogs_account_id` | ❌ | ✅ FK | Catalog-specific |
| `cost_center_id` | ✅ FK | ❌ | Sales-specific |
| `min_qty` | ✅ Integer | ❌ | Sales-specific |
| `is_returnable` | ✅ Boolean | ❌ | Sales-specific |
| `discount_amount` | ✅ Decimal | ❌ | Sales-specific |
| `discount_type` | ✅ String | ❌ | Sales-specific |
| `is_taxable` | ✅ Boolean | ❌ | Sales-specific |
| `tags` | ✅ String | ❌ | Sales-specific |
| `notes` | ✅ Text | ❌ | Sales-specific |
| `user_id` | ✅ FK | ❌ | Sales-specific |

#### Field Comparison: Construction Material vs Catalog Material

| Field | Construction Material | Catalog Material | Migration Notes |
|-------|----------------------|------------------|-----------------|
| `id` | ✅ PK | ✅ PK | - |
| `company_id` | ✅ FK | ❌ Missing | **Gap**: Catalog lacks multi-tenant |
| `name` | ✅ String | ✅ String | - |
| `code` | ✅ String (auto-generated MAT prefix) | ✅ String (unique) | Different generation |
| `description` | ✅ String | ✅ Text | - |
| `unit_id` | ✅ FK | ✅ FK | Different unit tables |
| `status` | ✅ Boolean | ❌ | Construction uses boolean |
| `is_active` | ❌ | ✅ Boolean | Catalog uses is_active |
| `length` | ✅ String | ❌ | Construction-specific |
| `width` | ✅ String | ❌ | Construction-specific |
| `height` | ✅ String | ❌ | Construction-specific |
| `manufacture_material` | ✅ String | ❌ | Construction-specific |
| `unit_price` | ✅ Decimal | ❌ | Construction-specific |
| `selling_price` | ✅ Decimal | ❌ | Construction-specific |
| `profit_margin_type` | ✅ String | ❌ | Construction-specific |
| `profit_margin` | ✅ Decimal | ❌ | Construction-specific |
| `final_cost` | ✅ Decimal | ❌ | Construction-specific |
| `current_stock` | ✅ Integer | ❌ | Construction-specific |
| `store_id` | ✅ FK | ❌ | Construction-specific |
| `material_category_id` | ✅ FK | ❌ | Construction-specific |
| `primary_supplier_id` | ✅ FK | ❌ | Construction-specific |
| `currency_code` | ✅ String | ❌ | Construction-specific |
| `exchange_rate` | ✅ Decimal | ❌ | Construction-specific |
| `tax_rate` | ✅ Decimal | ❌ | Construction-specific |
| `reorder_level` | ✅ Integer | ❌ | Construction-specific |
| `max_stock` | ✅ Integer | ❌ | Construction-specific |
| `lead_time` | ✅ Integer | ❌ | Construction-specific |
| `time_unit` | ✅ String | ❌ | Construction-specific |
| `weight` | ✅ Decimal | ❌ | Construction-specific |
| `expiration_date` | ✅ Date | ❌ | Construction-specific |
| `custom_fields` | ✅ JSON | ❌ | Construction-specific |
| `qr_code_value` | ✅ String | ❌ | Construction-specific |

### 2.2 Variant System (Catalog Only)

| Aspect | Catalog: `ProductVariant` | Notes |
|--------|---------------------------|-------|
| **Table** | `catalog_product_variants` | Only Catalog has variants |
| **Model Path** | `Catalog\Products\Models\ProductVariant` | - |
| **Multi-tenant** | No | **Gap** |
| **Soft Deletes** | Yes | - |

#### Field Comparison: ProductVariant

| Field | ProductVariant | Notes |
|-------|----------------|-------|
| `id` | ✅ PK | - |
| `product_id` | ✅ FK to `catalog_products` | Links to parent product |
| `variant_group_id` | ✅ FK (nullable) | Variant grouping |
| `sku` | ✅ String (unique) | Stock Keeping Unit |
| `barcode` | ✅ String (nullable) | Barcode for variant |
| `origin_country` | ✅ String (nullable) | Origin information |
| `quality_grade` | ✅ Enum (oem/compatible/aftermarket/refurbished) | Quality classification |
| `manufacturer_location` | ✅ String (nullable) | Manufacturing location |
| `certification` | ✅ String (nullable) | Certifications |
| `warranty_terms` | ✅ String (nullable) | Warranty information |
| `is_preferred` | ✅ Boolean | Preferred variant flag |
| `notes` | ✅ Text (nullable) | Additional notes |

**Key Relationships:**
- `ProductVariantPrice` - Pricing by price type, unit, currency
- `ProductVariantUnit` - Multiple units per variant
- `ProductVariantMaterial` - Bill of Materials (BOM)
- `Inventory` - Warehouse stock levels
- `InventoryTransaction` - Stock movements
- `InventoryBatch` - Batch/lot tracking
- `InventorySerial` - Serial number tracking
- `ProductImage` - Variant images

### 2.3 Pricing Comparison

| Aspect | Sales: `ProductPrice` | Catalog: `ProductVariantPrice` | Notes |
|--------|----------------------|-------------------------------|-------|
| **Table** | `sales_product_prices` | `catalog_product_variant_prices` | Different tables |
| **Reference** | `product_id` | `variant_id` | **Key Difference** |
| **Multi-tenant** | Yes (`company_id`) | No | **Gap** |
| **Unit Support** | Yes (`unit_id`) | Yes (`unit_id`) | - |

#### Field Comparison: Pricing

| Field | Sales ProductPrice | Catalog VariantPrice | Migration Notes |
|-------|-------------------|----------------------|-----------------|
| `id` | ✅ PK | ✅ PK | - |
| `company_id` | ✅ FK | ❌ | **Gap** |
| `product_id` | ✅ FK | ❌ | Sales references product |
| `variant_id` | ❌ | ✅ FK | Catalog references variant |
| `unit_id` | ✅ FK | ✅ FK | Both support units |
| `purchasing_price` | ✅ Decimal | ❌ | Sales-specific |
| `selling_price` | ✅ Decimal | ❌ | Sales-specific |
| `min_selling_price` | ✅ Decimal | ❌ | Sales-specific |
| `is_main_price` | ✅ Boolean | ✅ Boolean | Both have main price flag |
| `price_type_id` | ❌ | ✅ FK | Catalog has price types |
| `price` | ❌ | ✅ Decimal | Catalog generic price |
| `currency` | ❌ | ✅ String | Catalog multi-currency |
| `min_quantity` | ❌ | ✅ Integer | Catalog quantity breaks |
| `valid_from` | ❌ | ✅ Date | Catalog time-based pricing |
| `valid_to` | ❌ | ✅ Date | Catalog time-based pricing |

---

## 3. Feature Comparison Matrix

| Feature | Sales Products | Construction Materials | Catalog Materials | Catalog Products | Unified Target |
|---------|----------------|----------------------|------------------|------------------|----------------|
| **Multi-tenant (company_id)** | ✅ Yes | ✅ Yes | ❌ No | ❌ No | ✅ Required |
| **Soft Deletes** | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Keep |
| **Variants** | ❌ No | ❌ No | ❌ No | ✅ Yes | ✅ Required |
| **Multi-language** | ❌ No | ❌ No | ❌ No | ✅ Yes | ✅ Add |
| **Hierarchical Categories** | ❌ No (simple FK) | ❌ No (simple FK) | ❌ No | ✅ Yes | ✅ Add |
| **Product Hierarchy** | ❌ No | ❌ No | ❌ No | ✅ Yes | ✅ Add |
| **Pricing by Unit** | ✅ Yes | ❌ No | ❌ No | ✅ Yes | ✅ Keep |
| **Price Types** | ❌ No | ❌ No | ❌ No | ✅ Yes | ✅ Add |
| **Multi-currency Pricing** | ❌ No | ❌ No | ❌ No | ✅ Yes | ✅ Add |
| **Time-based Pricing** | ❌ No | ❌ No | ❌ No | ✅ Yes | ✅ Add |
| **Price History** | ✅ Yes | ❌ No | ❌ No | ✅ Yes (audits) | ✅ Merge |
| **Client-specific Pricing** | ✅ Yes | ❌ No | ❌ No | ✅ Yes | ✅ Keep |
| **Supplier-specific Pricing** | ❌ No | ✅ Yes (many-to-many) | ❌ No | ✅ Yes | ✅ Keep |
| **BOM Support** | ❌ No | ❌ No | ❌ No | ✅ Yes (variant materials) | ✅ Add |
| **Multiple Units** | ✅ Yes (via prices) | ❌ No | ❌ No | ✅ Yes (variant units) | ✅ Keep |
| **Stock Tracking** | ✅ Yes | ✅ Yes | ❌ No | ✅ Yes (via variants) | ✅ Keep |
| **Batch/Lot Tracking** | ❌ No | ❌ No | ❌ No | ✅ Yes | ✅ Add |
| **Serial Tracking** | ❌ No | ❌ No | ❌ No | ✅ Yes | ✅ Add |
| **Accounting Integration** | ✅ Partial (purchase account) | ❌ No | ❌ No | ✅ Yes (4 accounts) | ✅ Enhance |
| **Cost Center** | ✅ Yes | ❌ No | ❌ No | ❌ No | ✅ Keep |
| **Tax Management** | ✅ Yes | ✅ Partial | ❌ No | ❌ No | ✅ Keep |
| **Section-based Access** | ✅ Yes | ❌ No | ❌ No | ❌ No | ✅ Keep |
| **Auto-generated Codes** | ✅ Yes (ITM prefix) | ✅ Yes (MAT prefix) | ❌ No | ❌ No | ✅ Keep |
| **Media Library** | ✅ Yes | ✅ Yes | ❌ No | ✅ Yes | ✅ Keep |
| **Image Management** | ✅ Single collection | ✅ Single collection | ❌ No | ✅ Multiple images | ✅ Enhance |

---

## 4. Foreign Key Dependencies

### 4.1 Tables Referencing Sales Products (`sales_products.id`)

| Table | Column | Domain | Impact |
|-------|--------|--------|--------|
| `sales_product_prices` | `product_id` | Accounting | Pricing |
| `product_price_histories` | `product_id` | Accounting | Price history |
| `sales_invoice_products` | `product_id` | Accounting | Sales line items |
| `purchase_products` | `product_id` | Accounting | Purchase line items |
| `product_stores` | `product_id` | Accounting | Stock levels |
| `client_products` | `product_id` | Accounting | Client-specific products |
| `client_prices_logs` | `product_id` | Accounting | Price change logs |

### 4.2 Tables Referencing Construction Materials (`materials.id`)

| Table | Column | Domain | Impact |
|-------|--------|--------|--------|
| `product_stores` | `product_id` | Accounting | Stock levels (type='material') |
| `purchase_products` | `product_id` | Accounting | Purchase line items |
| `sales_invoice_products` | `product_id` | Accounting | Sales line items |
| `store_first_term_products` | `product_id` | Accounting | Initial inventory |
| `supplier_materials` | `material_id` | Construction | Supplier relationships |
| `material_supplier` | `material_id` | Construction | Supplier pivot |
| `materials_received` | `material_id` | Construction | Receipt tracking |
| `materials_consumed` | `material_id` | Construction | Consumption tracking |
| `item_phase_materials` | `material_id` | Construction | Project materials |
| `project_clause_materials` | `material_id` | Construction | Project clauses |
| `project_template_item_materials` | `material_id` | Construction | Template materials |
| `tangible_items` | `material_id` | Construction | Tangible items |

### 4.3 Tables Referencing Catalog Products (`catalog_products.id`)

| Table | Column | Domain | Impact |
|-------|--------|--------|--------|
| `catalog_product_variants` | `product_id` | Catalog | Variants |
| `catalog_product_aliases` | `product_id` | Catalog | Alternative names |
| `catalog_product_images` | `product_id` | Catalog | Product images |
| `catalog_sub_assemblies` | `product_id` | Catalog | Component products |
| `catalog_product_bundles` | `product_id` | Catalog | Bundled products |
| `catalog_client_product_prices` | `product_id` | Catalog | Client pricing |
| `catalog_supplier_product_prices` | `product_id` | Catalog | Supplier pricing |
| `catalog_product_price_audits` | `product_id` | Catalog | Price audits |

### 4.4 Tables Referencing Catalog Product Variants (`catalog_product_variants.id`)

| Table | Column | Domain | Impact |
|-------|--------|--------|--------|
| `catalog_product_variant_prices` | `variant_id` | Catalog | Variant pricing |
| `catalog_product_variant_units` | `variant_id` | Catalog | Variant units |
| `catalog_product_variant_materials` | `variant_id` | Catalog | BOM materials |
| `catalog_variant_attribute_options` | `variant_id` | Catalog | Variant attributes |
| `catalog_product_images` | `variant_id` | Catalog | Variant images |
| `catalog_inventories` | `variant_id` | Catalog | Warehouse stock |
| `catalog_inventory_transactions` | `variant_id` | Catalog | Stock movements |
| `catalog_inventory_batches` | `variant_id` | Catalog | Batch tracking |
| `catalog_inventory_serials` | `variant_id` | Catalog | Serial tracking |
| `catalog_inventory_costs` | `variant_id` | Catalog | Cost tracking |
| `catalog_cost_rollups` | `variant_id` | Catalog | Cost rollup |

### 4.5 Tables Referencing Catalog Materials (`catalog_materials.id`)

| Table | Column | Domain | Impact |
|-------|--------|--------|--------|
| `catalog_product_variant_materials` | `material_id` | Catalog | BOM materials |

---

## 5. Key Files Summary

### 5.1 Sales Products Models

| File | Purpose |
|------|---------|
| [Product.php](app/Domains/Accounting/Sales/Models/Product.php) | Main sales product model |
| [ProductPrice.php](app/Domains/Accounting/Sales/Models/ProductPrice.php) | Product pricing by unit |
| [ProductPriceHistory.php](app/Domains/Accounting/Sales/Models/ProductPriceHistory.php) | Price change tracking |
| [SalesInvoiceProduct.php](app/Domains/Accounting/Sales/Models/SalesInvoiceProduct.php) | Sales line items |

### 5.2 Construction Materials Models

| File | Purpose |
|------|---------|
| [Material.php](app/Domains/Construction/Material/Models/Material.php) | Construction material model |
| [MaterialServices.php](app/Domains/Construction/Material/Services/MaterialServices.php) | Material business logic |

### 5.3 Catalog Products Models

| File | Purpose |
|------|---------|
| [Product.php](app/Domains/Catalog/Products/Models/Product.php) | Catalog product model |
| [ProductVariant.php](app/Domains/Catalog/Products/Models/ProductVariant.php) | Product variant (SKU) |
| [ProductVariantPrice.php](app/Domains/Catalog/Products/Models/ProductVariantPrice.php) | Variant pricing |
| [ProductVariantUnit.php](app/Domains/Catalog/Products/Models/ProductVariantUnit.php) | Variant units |
| [ProductVariantMaterial.php](app/Domains/Catalog/Products/Models/ProductVariantMaterial.php) | BOM materials |
| [ProductImage.php](app/Domains/Catalog/Products/Models/ProductImage.php) | Product/variant images |
| [ProductAlias.php](app/Domains/Catalog/Products/Models/ProductAlias.php) | Alternative names |
| [Material.php](app/Domains/Catalog/Products/Models/Material.php) | Catalog material (BOM) |

---

## 6. Unification Strategy Options

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

**Approach:** Migrate Sales products and Construction materials to enhanced Catalog Product/Variant system.

**Pros:**
- Modern architecture with variants
- Multi-language support
- Hierarchical categories
- Better pricing model (price types, multi-currency, time-based)
- BOM support
- Batch/serial tracking ready
- Cleaner separation of concerns

**Cons:**
- Need to add multi-tenant support
- Need to add section-based access control
- More migration work
- Need to handle simple products without variants

### Option B: Enhance Sales System

**Approach:** Add Catalog features to Sales products.

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

**Cons:**
- No variant support (would need to add)
- No multi-language support
- No hierarchical categories
- Limited pricing model
- Harder to extend

### Option C: Unified Bridge Layer

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

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

**Cons:**
- Added complexity
- Sync issues possible
- Maintenance overhead
- Data inconsistency risks

---

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

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

- [ ] **Task 1.1**: Add `company_id` FK to `catalog_products` for multi-tenant support
- [ ] **Task 1.2**: Add `company_id` FK to `catalog_product_variants` for multi-tenant support
- [ ] **Task 1.3**: Add `company_id` FK to `catalog_materials` for multi-tenant support
- [ ] **Task 1.4**: Add `section` enum field to `catalog_products` (accounting/procurement)
- [ ] **Task 1.5**: Add `code` field to `catalog_products` for auto-generated codes
- [ ] **Task 1.6**: Add `is_simple_product` boolean flag to `catalog_products` (for sales-style products)
- [ ] **Task 1.7**: Add `category_id` FK to `catalog_products` for simple category reference
- [ ] **Task 1.8**: Add `tax_id` and `purchase_tax_id` FKs to `catalog_products`
- [ ] **Task 1.9**: Add `cost_center_id` FK to `catalog_products`
- [ ] **Task 1.10**: Add `user_id` FK to `catalog_products`
- [ ] **Task 1.11**: Add `track_stock`, `initial_stock_level`, `min_qty`, `is_returnable` fields to variants
- [ ] **Task 1.12**: Add `discount_amount`, `discount_type` fields to variants
- [ ] **Task 1.13**: Add `is_taxable`, `sale_tax`, `purchase_tax` fields to variants
- [ ] **Task 1.14**: Add `barcode_type`, `barcode_path` fields to variants
- [ ] **Task 1.15**: Add `tags`, `notes` fields to variants
- [ ] **Task 1.16**: Add `company_id` FK to `catalog_product_variant_prices`
- [ ] **Task 1.17**: Add `purchasing_price`, `selling_price`, `min_selling_price` to variant prices
- [ ] **Task 1.18**: Add company scope trait to all Catalog Product models
- [ ] **Task 1.19**: Add section-based access control scope to Catalog Products

### Phase 2: Material System Unification

- [ ] **Task 2.1**: Create unified Material model (merge Construction and Catalog materials)
- [ ] **Task 2.2**: Add `company_id` FK to unified materials
- [ ] **Task 2.3**: Migrate Construction Material fields to unified model
- [ ] **Task 2.4**: Create material mapping table (`material_migration_map`)
- [ ] **Task 2.5**: Migrate `materials` data to unified materials table
- [ ] **Task 2.6**: Migrate `catalog_materials` data to unified materials table
- [ ] **Task 2.7**: Update `catalog_product_variant_materials` to reference unified materials
- [ ] **Task 2.8**: Update all Construction Material references to unified materials

### Phase 3: Product Data Migration

- [ ] **Task 3.1**: Create product mapping table (`product_migration_map`)
- [ ] **Task 3.2**: Migrate `sales_products` data to `catalog_products`
  - Create simple products with `is_simple_product=true`
  - Create single variant per product (for simple products)
  - Map fields appropriately
- [ ] **Task 3.3**: Migrate `sales_product_prices` to `catalog_product_variant_prices`
- [ ] **Task 3.4**: Migrate `product_price_histories` to `catalog_product_price_audits`
- [ ] **Task 3.5**: Create variant for each migrated sales product
- [ ] **Task 3.6**: Migrate product codes (ITM prefix) to variant SKUs

### Phase 4: Foreign Key Migration

- [ ] **Task 4.1**: Update `sales_invoice_products` to reference `catalog_products` and `catalog_product_variants`
- [ ] **Task 4.2**: Update `purchase_products` to reference `catalog_products` and `catalog_product_variants`
- [ ] **Task 4.3**: Update `product_stores` to reference `catalog_products` and `catalog_product_variants`
- [ ] **Task 4.4**: Update `store_first_term_products` to reference unified products/variants
- [ ] **Task 4.5**: Update `client_products` to reference `catalog_products`
- [ ] **Task 4.6**: Update `client_prices_logs` to reference `catalog_products`
- [ ] **Task 4.7**: Update all Construction Material references to unified materials

**Tables Requiring FK Updates:**
1. `sales_invoice_products`
2. `purchase_products`
3. `product_stores`
4. `store_first_term_products`
5. `client_products`
6. `client_prices_logs`
7. `materials_received`
8. `materials_consumed`
9. `item_phase_materials`
10. `project_clause_materials`
11. `project_template_item_materials`
12. `tangible_items`
13. `supplier_materials`
14. `material_supplier`

### Phase 5: Service Layer Updates

- [ ] **Task 5.1**: Create unified `ProductService` with all operations
- [ ] **Task 5.2**: Create unified `ProductVariantService` for variant operations
- [ ] **Task 5.3**: Create unified `MaterialService` for material operations
- [ ] **Task 5.4**: Migrate `ProductPrice` logic to `ProductVariantPrice` service
- [ ] **Task 5.5**: Add section-based access control to services
- [ ] **Task 5.6**: Add company scoping to all services
- [ ] **Task 5.7**: Update all controllers using Sales products to use unified service
- [ ] **Task 5.8**: Update all controllers using Construction materials to use unified service
- [ ] **Task 5.9**: Add code generation logic for simple products
- [ ] **Task 5.10**: Add variant creation for simple products

### Phase 6: API Route Consolidation

- [ ] **Task 6.1**: Deprecate Sales Products API routes
- [ ] **Task 6.2**: Deprecate Construction Materials API routes
- [ ] **Task 6.3**: Add new unified product/variant API endpoints
- [ ] **Task 6.4**: Create backward-compatible route aliases
- [ ] **Task 6.5**: Update API documentation

### Phase 7: Financial Integration

- [ ] **Task 7.1**: Ensure product creation triggers ChartOfAccount creation (if needed)
- [ ] **Task 7.2**: Link product/variant operations to AccountingTransaction
- [ ] **Task 7.3**: Maintain cost tracking for financial reports
- [ ] **Task 7.4**: Update all financial reports to use new structure
- [ ] **Task 7.5**: Ensure tax calculations work with variants

### Phase 8: Cleanup

- [ ] **Task 8.1**: Remove `Accounting\Sales\Models\Product` model
- [ ] **Task 8.2**: Remove `Construction\Material\Models\Material` model
- [ ] **Task 8.3**: Remove `ProductPrice` model (merge with VariantPrice)
- [ ] **Task 8.4**: Remove `ProductPriceHistory` model (merge with PriceAudit)
- [ ] **Task 8.5**: Drop deprecated tables after data verification
- [ ] **Task 8.6**: Remove unused services and controllers
- [ ] **Task 8.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 sales invoices | High | High | Maintain product/variant references, thorough testing |
| Breaking purchase orders | High | High | Maintain product/variant references, thorough testing |
| Breaking financial reports | High | High | Maintain accounting account links, thorough testing |
| Pricing calculation errors | High | Medium | Comprehensive price migration testing |
| Stock level discrepancies | High | Medium | Verify stock levels before/after migration |
| 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 |
| Section-based access issues | Medium | Medium | Test permission scopes thoroughly |
| Variant creation for simple products | Medium | Medium | Automated variant creation logic |

---

## 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
6. Maintain mapping tables for reverse lookups
7. Create rollback scripts for each migration phase

---

## 10. Estimated Scope

| Category | Count |
|----------|-------|
| Tables with schema changes | 10+ |
| Tables with FK changes | 15+ |
| Models to update | 20+ |
| Models to remove | 5+ |
| Services to update | 10+ |
| New migrations needed | 20-25 |
| API routes affected | 50+ |

---

## 11. Decision Points Requiring Clarification

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

2. **How to handle simple products (Sales products) that don't need variants?**
   - Option A: Create single variant automatically
   - Option B: Allow products without variants
   - Recommendation: Option A (create single variant)

3. **Should Construction Materials become Catalog Products with variants, or remain separate?**
   - Option A: Migrate to Catalog Products
   - Option B: Keep as separate Materials but unified
   - Recommendation: Option B (keep as Materials, unified table)

4. **How to handle pricing migration?**
   - Sales: Product-level pricing by unit
   - Catalog: Variant-level pricing by price type
   - Recommendation: Create variant prices from product prices, use default price type

5. **Should section-based access control be maintained?**
   - Current: Sales products have section (accounting/procurement)
   - Recommendation: Yes, add to Catalog Products

6. **How to handle product codes?**
   - Sales: ITM-{category}-{sequence}
   - Catalog: Uses SKU in variants
   - Recommendation: Use product code as variant SKU for simple products

7. **Should we maintain separate Material system or merge into Products?**
   - Current: Separate Materials table
   - Recommendation: Keep separate but unified (Materials used in BOM)

---

## 12. Migration Mapping Examples

### 12.1 Sales Product → Catalog Product + Variant

**Sales Product:**
```php
{
  id: 1,
  company_id: 1,
  name: "Widget A",
  code: "ITM-0001-01",
  status: true,
  track_stock: true,
  initial_stock_level: 100,
  section: "accounting"
}
```

**Migrated to:**
```php
// Catalog Product
{
  id: 1001,
  company_id: 1,
  canonical_name: {"en": "Widget A"},
  code: "ITM-0001-01",
  status: "active",
  section: "accounting",
  is_simple_product: true
}

// Catalog Product Variant
{
  id: 2001,
  product_id: 1001,
  company_id: 1,
  sku: "ITM-0001-01",
  track_stock: true,
  initial_stock_level: 100
}
```

### 12.2 Sales ProductPrice → Catalog VariantPrice

**Sales ProductPrice:**
```php
{
  id: 1,
  company_id: 1,
  product_id: 1,
  unit_id: 5,
  purchasing_price: 10.00,
  selling_price: 15.00,
  min_selling_price: 12.00,
  is_main_price: true
}
```

**Migrated to:**
```php
// Catalog VariantPrice
{
  id: 3001,
  company_id: 1,
  variant_id: 2001,
  unit_id: 5,
  price_type_id: 1, // Default price type
  purchasing_price: 10.00,
  selling_price: 15.00,
  min_selling_price: 12.00,
  is_main_price: true
}
```

### 12.3 Construction Material → Unified Material

**Construction Material:**
```php
{
  id: 1,
  company_id: 1,
  name: "Steel Beam",
  code: "MAT-0001",
  unit_id: 3,
  unit_price: 50.00,
  status: true
}
```

**Migrated to:**
```php
// Unified Material
{
  id: 5001,
  company_id: 1,
  name: "Steel Beam",
  code: "MAT-0001",
  unit_id: 3,
  unit_price: 50.00,
  is_active: true,
  material_type: "construction" // Flag for construction materials
}
```

---

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