# Technical Analysis and Migration Plan: Sales Products & Catalog Products Unification

**Date:** January 2026  
**Project:** Building Management System  
**Tech Stack:** Laravel, MySQL, Eloquent ORM

---

## Executive Summary

This document provides a comprehensive analysis of merging two distinct product management systems:
- **Sales Products** (`sales_products` table) - Located in the Accounting domain
- **Catalog Products** (`catalog_products` table) - Located in the Catalog domain

The analysis includes gap analysis, architectural comparison, unification strategies, recommendations, and a detailed migration roadmap.

---

## Table of Contents

1. [Gap Analysis](#1-gap-analysis)
2. [Current Architecture Comparison](#2-current-architecture-comparison)
3. [Pros and Cons Analysis](#3-pros-and-cons-analysis)
4. [Unification Strategies](#4-unification-strategies)
5. [Golden Recommendation](#5-golden-recommendation)
6. [Migration Roadmap](#6-migration-roadmap)
7. [Risk Assessment](#7-risk-assessment)
8. [Appendix](#8-appendix)

---

## 1. Gap Analysis

### 1.1 Database Schema Comparison

#### Sales Products (`sales_products`)

**Core Fields:**
- `id` (bigint, PK)
- `company_id` (bigint, FK → companies)
- `user_id` (bigint, nullable, FK → users)
- `name` (varchar 255) - **Simple string**
- `description` (text, nullable)
- `code` (varchar 255, nullable) - Auto-generated: `ITM-{category_code}-{sequence}`
- `status` (tinyint, default 0) - **Boolean (0/1)**
- `section` (varchar 255, default 'accounting') - **Domain separation**
- `type` (varchar 255, default 'product')
- `image` (varchar 255, nullable) - **Single image path**
- `barcode` (varchar 255, nullable)
- `barcode_type` (varchar 255, nullable) - 'barcode' or 'qr'
- `barcode_path` (varchar 255, nullable)

**Accounting-Specific Fields:**
- `track_stock` (tinyint, default 0)
- `initial_stock_level` (double, default 0)
- `discount_amount` (decimal 15,4, nullable)
- `discount_type` (varchar 255, nullable)
- `tax_id` (bigint, nullable, FK → taxes)
- `is_taxable` (tinyint, default 0)
- `sale_tax` (varchar 255, nullable)
- `purchase_tax` (varchar 255, nullable)
- `purchase_tax_id` (bigint, nullable, FK → taxes)
- `purchase_account_id` (bigint, nullable, FK → chart_of_accounts)
- `cost_center_id` (bigint, nullable, FK → cost_centers)
- `min_qty` (int, default 1)
- `is_returnable` (tinyint, default 0)
- `valuation_record_id` (bigint, nullable)
- `category_id` (bigint, nullable, FK → categories) - **Simple category reference**
- `categories` (varchar 255, nullable) - **Legacy string field**
- `tags` (varchar 255, nullable)
- `notes` (text, nullable)

**Relationships:**
- `sales_product_prices` (hasMany) - **Separate pricing table**
- `product_price_histories` (hasMany)
- `sales_invoice_products` (hasMany)
- `purchase_products` (hasMany)
- `product_stores` (hasMany)
- `category` (belongsTo)
- `tax` (belongsTo)
- `purchaseAccount` (belongsTo)
- `costCenter` (belongsTo)
- Media Library (Spatie) - **Single collection: 'products_attachments'**

**Key Characteristics:**
- Company-scoped (global scope)
- Section-based filtering (accounting/procurement)
- Permission-based access control
- Auto-generated codes based on category hierarchy
- Price management via separate table (`sales_product_prices`)
- Supports multiple units per product via prices table
- Price history tracking
- Barcode/QR code generation

#### Catalog Products (`catalog_products`)

**Core Fields:**
- `id` (bigint, PK)
- `catalog_item_id` (bigint, FK → catalog_category_items) - **Hierarchical category**
- `parent_id` (bigint, nullable, FK → catalog_products) - **Product hierarchy**
- `canonical_name` (json) - **Translatable, multi-language**
- `description` (json, nullable) - **Translatable, multi-language**
- `brand` (varchar 255, nullable)
- `manufacturer` (varchar 255, nullable)
- `product_type` (enum: 'physical', 'digital', 'spare_part')
- `status` (enum: 'draft', 'active', 'discontinued', 'pending') - **Rich status enum**

**Accounting Integration Fields:**
- `revenue_account_id` (bigint, nullable, FK → chart_of_accounts)
- `expense_account_id` (bigint, nullable, FK → chart_of_accounts)
- `inventory_account_id` (bigint, nullable, FK → chart_of_accounts)
- `cogs_account_id` (bigint, nullable, FK → chart_of_accounts)

**Relationships:**
- `catalog_product_variants` (hasMany) - **Variant-based architecture**
- `catalog_product_aliases` (hasMany)
- `catalog_product_images` (hasMany) - **Multiple images**
- `catalog_sub_assemblies` (hasMany)
- `catalog_product_bundles` (hasMany)
- `catalog_client_product_prices` (hasMany)
- `catalog_supplier_product_prices` (hasMany)
- `catalog_product_price_audits` (hasMany)
- `catalogItem` (belongsTo) - Hierarchical category
- `parent` (belongsTo) - Product hierarchy
- `children` (hasMany) - Product hierarchy
- `revenueAccount`, `expenseAccount`, `inventoryAccount`, `cogsAccount` (belongsTo)

**Key Characteristics:**
- **Variant-based architecture** - Products have variants (SKU-level)
- **Multi-language support** (Spatie Translatable)
- **Hierarchical products** (parent-child relationships)
- **Rich categorization** (hierarchical catalog items)
- **Multiple images** per product/variant
- **Variant-level pricing** (`catalog_product_variant_prices`)
- **Variant-level inventory** (`catalog_inventories` linked to variants)
- **Variant attributes** (quality grade, origin, certification, etc.)
- **Variant materials** (BOM support)
- **Variant units** (multiple units per variant)
- **Price types** (different price types: retail, wholesale, etc.)
- **Price audits** (change tracking)
- **Client/Supplier specific pricing**

### 1.2 Field Mapping & Overlaps

#### Overlapping Fields (Semantic Equivalence)

| Sales Products | Catalog Products | Notes |
|---------------|------------------|-------|
| `name` | `canonical_name` | Sales: string, Catalog: JSON (translatable) |
| `description` | `description` | Sales: text, Catalog: JSON (translatable) |
| `status` | `status` | Sales: boolean, Catalog: enum |
| `barcode` | `variants.barcode` | Sales: product-level, Catalog: variant-level |
| `code` | N/A | Sales: auto-generated, Catalog: uses SKU in variants |
| `category_id` | `catalog_item_id` | Different category systems |
| `image` | `images` (hasMany) | Sales: single, Catalog: multiple |
| `purchase_account_id` | `expense_account_id` | Similar accounting concept |
| `tax_id` | N/A | Sales: direct tax reference |
| `company_id` | Inherited from BaseModel | Both company-scoped |

#### Unique to Sales Products

- `section` (accounting/procurement domain separation)
- `type` (product type classification)
- `track_stock` (stock tracking flag)
- `initial_stock_level` (starting inventory)
- `discount_amount` / `discount_type` (product-level discounts)
- `is_taxable` (tax flag)
- `sale_tax` / `purchase_tax` (tax strings)
- `purchase_tax_id` (tax reference)
- `min_qty` (minimum order quantity)
- `is_returnable` (return policy)
- `cost_center_id` (cost center assignment)
- `valuation_record_id` (inventory valuation)
- `tags` (simple string tags)
- `notes` (text notes)
- `user_id` (creator tracking)
- `barcode_type` / `barcode_path` (barcode generation)

#### Unique to Catalog Products

- `parent_id` (product hierarchy)
- `brand` / `manufacturer` (product metadata)
- `product_type` (enum: physical/digital/spare_part)
- `canonical_name` (translatable JSON)
- Variant system (SKU-level management)
- Variant attributes (quality grade, origin, certification)
- Variant materials (BOM)
- Variant units (multiple units per variant)
- Variant images (variant-specific images)
- Product aliases (alternative names)
- Sub-assemblies (component products)
- Product bundles (bundled products)
- Client-specific pricing
- Supplier-specific pricing
- Price audits
- Price types (different price categories)
- Hierarchical categories (`catalog_category_items`)

### 1.3 Business Logic Comparison

#### Sales Products Logic

**Code Generation:**
- Format: `ITM-{category_code}-{sequence}`
- Category-based sequential numbering
- Example: `ITM-0001-01-01-03`

**Pricing:**
- Separate `sales_product_prices` table
- Multiple prices per product (one per unit)
- Price segments (client segment-based pricing)
- Price history tracking
- Main price flag (`is_main_price`)

**Permissions:**
- Section-based permissions (`accounting-sale_products.*`, `procurement-sale_products.*`)
- Global scope filtering by section

**Media:**
- Single media collection (`products_attachments`)
- Barcode/QR code generation
- Image path stored in `image` field

#### Catalog Products Logic

**SKU Management:**
- Variant-based architecture
- Each variant has unique SKU
- Variants can have different attributes, prices, inventory

**Pricing:**
- Variant-level pricing (`catalog_product_variant_prices`)
- Price types (retail, wholesale, etc.)
- Client-specific pricing
- Supplier-specific pricing
- Price audits (change tracking)
- Currency support

**Inventory:**
- Variant-level inventory (`catalog_inventories`)
- Warehouse-level tracking
- Inventory types
- Batch tracking support
- Serial number tracking support

**Categorization:**
- Hierarchical catalog items
- Multi-level category structure
- Product can belong to one catalog item

**Product Hierarchy:**
- Parent-child product relationships
- Supports product families/variants at product level

**Internationalization:**
- Translatable fields (`canonical_name`, `description`)
- Multi-language support via Spatie Translatable

**Media:**
- Multiple images per product
- Variant-specific images
- Image hash deduplication

### 1.4 Related Tables Comparison

#### Sales Products Related Tables

| Table | Purpose | Relationship |
|-------|---------|-------------|
| `sales_product_prices` | Product pricing by unit | hasMany |
| `product_price_histories` | Price change tracking | hasMany |
| `sales_invoice_products` | Sales line items | hasMany |
| `purchase_products` | Purchase line items | hasMany |
| `product_stores` | Store assignments | hasMany |
| `categories` | Simple category | belongsTo |
| `taxes` | Tax information | belongsTo |
| `chart_of_accounts` | Purchase account | belongsTo |
| `cost_centers` | Cost center | belongsTo |
| `media` (Spatie) | Attachments | morphMany |

#### Catalog Products Related Tables

| Table | Purpose | Relationship |
|-------|---------|-------------|
| `catalog_product_variants` | Product variants (SKUs) | hasMany |
| `catalog_product_variant_prices` | Variant pricing | hasMany (through variants) |
| `catalog_product_variant_units` | Variant units | hasMany (through variants) |
| `catalog_product_variant_materials` | BOM materials | hasMany (through variants) |
| `catalog_product_images` | Product/variant images | hasMany |
| `catalog_product_aliases` | Alternative names | hasMany |
| `catalog_sub_assemblies` | Component products | hasMany |
| `catalog_product_bundles` | Bundled products | hasMany |
| `catalog_client_product_prices` | Client-specific pricing | hasMany |
| `catalog_supplier_product_prices` | Supplier-specific pricing | hasMany |
| `catalog_product_price_audits` | Price change audits | hasMany |
| `catalog_inventories` | Warehouse inventory | hasMany (through variants) |
| `catalog_inventory_transactions` | Inventory movements | hasMany (through variants) |
| `catalog_category_items` | Hierarchical categories | belongsTo |
| `chart_of_accounts` (4x) | Accounting accounts | belongsTo (4 relationships) |

---

## 2. Current Architecture Comparison

### 2.1 Domain Separation

**Sales Products:**
- Located in `App\Domains\Accounting\Sales\Models\Product`
- Tightly coupled with accounting domain
- Used in sales invoices, purchase orders
- Section-based domain separation (`accounting` vs `procurement`)

**Catalog Products:**
- Located in `App\Domains\Catalog\Products\Models\Product`
- Catalog domain focused on product information management
- Used for product cataloging, inventory management
- No section separation (single catalog domain)

### 2.2 Data Model Patterns

**Sales Products:**
- **Single Table Inheritance** pattern (implicit)
- Product-level attributes
- Simple one-to-many relationships
- Flat structure

**Catalog Products:**
- **Variant Pattern** (explicit)
- Product → Variants → Prices/Inventory/Units
- Rich hierarchical relationships
- Nested structure

### 2.3 Service Layer Comparison

**Sales Products:**
- `ProductServices` - Single service class
- Direct database operations
- Price management integrated
- Barcode generation integrated
- File import/export integrated

**Catalog Products:**
- `ProductService` - Main service
- `ProductBaseService` - Base CRUD operations
- `ProductRepository` - Repository pattern
- Event-driven (ProductCreated, ProductUpdated, etc.)
- More modular architecture

---

## 3. Pros and Cons Analysis

### 3.1 Current Separated Approach

#### Pros ✅

1. **Domain Isolation**
   - Clear separation of concerns
   - Accounting domain independent from catalog domain
   - Easier to maintain domain-specific logic

2. **Independent Evolution**
   - Each system can evolve independently
   - Changes to catalog don't affect accounting
   - Different teams can work independently

3. **Performance**
   - Smaller tables = faster queries
   - No complex joins for simple operations
   - Optimized indexes per domain

4. **Simplicity**
   - Sales products: Simple, straightforward
   - Catalog products: Rich but isolated
   - Easier to understand each system

5. **Flexibility**
   - Different data models for different needs
   - Sales: Simple product model
   - Catalog: Complex variant model

#### Cons ❌

1. **Data Duplication**
   - Product information duplicated
   - Same product may exist in both systems
   - Inconsistent data between systems

2. **Synchronization Issues**
   - No automatic sync between systems
   - Manual data entry required
   - Risk of data inconsistency

3. **Complexity**
   - Two systems to maintain
   - Two sets of APIs
   - Two sets of business logic
   - Two sets of tests

4. **User Confusion**
   - Users must understand two systems
   - Different UIs for similar concepts
   - Learning curve for new users

5. **Reporting Challenges**
   - Cross-domain reporting difficult
   - Need to join data from both systems
   - Inconsistent metrics

6. **Integration Overhead**
   - Need to integrate two systems
   - Complex data mapping
   - Potential for errors

### 3.2 Unified Approach

#### Pros ✅

1. **Single Source of Truth**
   - One product record
   - No data duplication
   - Consistent data across system

2. **Simplified Architecture**
   - One product model
   - One set of APIs
   - One set of business logic
   - Easier to maintain

3. **Better Reporting**
   - Unified reporting
   - Single query for product data
   - Consistent metrics

4. **Improved User Experience**
   - Single product interface
   - Consistent UI/UX
   - Easier to learn

5. **Easier Integration**
   - Single integration point
   - Simpler data mapping
   - Less error-prone

6. **Rich Product Model**
   - Can leverage variant system
   - Support for complex products
   - Better inventory management

#### Cons ❌

1. **Migration Complexity**
   - Complex migration process
   - Risk of data loss
   - Downtime required
   - Extensive testing needed

2. **Performance Concerns**
   - Larger table size
   - More complex queries
   - Potential performance degradation
   - Need for optimization

3. **Increased Complexity**
   - More fields in single table
   - More relationships
   - More complex business logic
   - Harder to understand

4. **Domain Coupling**
   - Accounting and catalog domains coupled
   - Changes affect both domains
   - Harder to maintain separation

5. **Backward Compatibility**
   - Need to maintain old APIs
   - Migration of existing integrations
   - Risk of breaking changes

---

## 4. Unification Strategies

### Strategy 1: Single Table Inheritance (STI) with Type Discriminator

**Concept:**
- Merge both tables into a single `products` table
- Add `product_type` enum field: `'sales'`, `'catalog'`, `'unified'`
- Include all fields from both tables (nullable where appropriate)
- Use type discriminator to filter by product type

**Schema Design:**
```sql
CREATE TABLE `products` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint unsigned NOT NULL,
  `product_type` enum('sales', 'catalog', 'unified') NOT NULL DEFAULT 'unified',
  
  -- Core fields (from both)
  `name` varchar(255) NOT NULL,
  `canonical_name` json NULL, -- For catalog products
  `description` text NULL,
  `code` varchar(255) NULL,
  `status` enum('draft', 'active', 'discontinued', 'pending', 'inactive') NOT NULL DEFAULT 'draft',
  
  -- Sales-specific fields (nullable)
  `section` varchar(255) NULL,
  `type` varchar(255) NULL,
  `track_stock` tinyint(1) DEFAULT 0,
  `initial_stock_level` double DEFAULT 0,
  `discount_amount` decimal(15,4) NULL,
  `discount_type` varchar(255) NULL,
  `is_taxable` tinyint(1) DEFAULT 0,
  `sale_tax` varchar(255) NULL,
  `purchase_tax` varchar(255) NULL,
  `min_qty` int DEFAULT 1,
  `is_returnable` tinyint(1) DEFAULT 0,
  `cost_center_id` bigint unsigned NULL,
  `valuation_record_id` bigint unsigned NULL,
  `tags` varchar(255) NULL,
  `notes` text NULL,
  `barcode_type` varchar(255) NULL,
  `barcode_path` varchar(255) NULL,
  
  -- Catalog-specific fields (nullable)
  `catalog_item_id` bigint unsigned NULL,
  `parent_id` bigint unsigned NULL,
  `brand` varchar(255) NULL,
  `manufacturer` varchar(255) NULL,
  `catalog_product_type` enum('physical', 'digital', 'spare_part') NULL,
  
  -- Common fields
  `barcode` varchar(255) NULL,
  `category_id` bigint unsigned NULL, -- Sales category
  `tax_id` bigint unsigned NULL,
  `purchase_tax_id` bigint unsigned NULL,
  `purchase_account_id` bigint unsigned NULL,
  `revenue_account_id` bigint unsigned NULL,
  `expense_account_id` bigint unsigned NULL,
  `inventory_account_id` bigint unsigned NULL,
  `cogs_account_id` bigint unsigned NULL,
  `user_id` bigint unsigned NULL,
  
  `deleted_at` timestamp NULL,
  `created_at` timestamp NULL,
  `updated_at` timestamp NULL,
  
  PRIMARY KEY (`id`),
  KEY `products_company_id_index` (`company_id`),
  KEY `products_product_type_index` (`product_type`),
  KEY `products_status_index` (`status`),
  -- ... other indexes
);
```

**Pros:**
- Simple migration path
- Single table to query
- Easy to add new product types
- Good performance for simple queries

**Cons:**
- Many nullable fields (wasteful)
- Complex validation logic
- Harder to enforce domain-specific constraints
- Large table size

**Implementation Complexity:** Medium  
**Migration Risk:** Medium  
**Performance Impact:** Medium

---

### Strategy 2: Class Table Inheritance (CTI) / Table Per Type

**Concept:**
- Base `products` table with common fields
- `sales_product_details` table for sales-specific fields
- `catalog_product_details` table for catalog-specific fields
- Foreign key relationships

**Schema Design:**
```sql
-- Base products table
CREATE TABLE `products` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint unsigned NOT NULL,
  `product_type` enum('sales', 'catalog', 'unified') NOT NULL,
  `name` varchar(255) NOT NULL,
  `canonical_name` json NULL,
  `description` text NULL,
  `code` varchar(255) NULL,
  `status` enum('draft', 'active', 'discontinued', 'pending', 'inactive') NOT NULL,
  `barcode` varchar(255) NULL,
  `category_id` bigint unsigned NULL,
  `catalog_item_id` bigint unsigned NULL,
  `tax_id` bigint unsigned NULL,
  `user_id` bigint unsigned NULL,
  `deleted_at` timestamp NULL,
  `created_at` timestamp NULL,
  `updated_at` timestamp NULL,
  PRIMARY KEY (`id`)
);

-- Sales-specific details
CREATE TABLE `sales_product_details` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `product_id` bigint unsigned NOT NULL,
  `section` varchar(255) NOT NULL DEFAULT 'accounting',
  `type` varchar(255) NOT NULL DEFAULT 'product',
  `track_stock` tinyint(1) DEFAULT 0,
  `initial_stock_level` double DEFAULT 0,
  `discount_amount` decimal(15,4) NULL,
  `discount_type` varchar(255) NULL,
  `is_taxable` tinyint(1) DEFAULT 0,
  `sale_tax` varchar(255) NULL,
  `purchase_tax` varchar(255) NULL,
  `purchase_tax_id` bigint unsigned NULL,
  `min_qty` int DEFAULT 1,
  `is_returnable` tinyint(1) DEFAULT 0,
  `cost_center_id` bigint unsigned NULL,
  `valuation_record_id` bigint unsigned NULL,
  `purchase_account_id` bigint unsigned NULL,
  `tags` varchar(255) NULL,
  `notes` text NULL,
  `barcode_type` varchar(255) NULL,
  `barcode_path` varchar(255) NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sales_product_details_product_id_unique` (`product_id`),
  FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
);

-- Catalog-specific details
CREATE TABLE `catalog_product_details` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `product_id` bigint unsigned NOT NULL,
  `parent_id` bigint unsigned NULL,
  `brand` varchar(255) NULL,
  `manufacturer` varchar(255) NULL,
  `catalog_product_type` enum('physical', 'digital', 'spare_part') NULL,
  `revenue_account_id` bigint unsigned NULL,
  `expense_account_id` bigint unsigned NULL,
  `inventory_account_id` bigint unsigned NULL,
  `cogs_account_id` bigint unsigned NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `catalog_product_details_product_id_unique` (`product_id`),
  FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`parent_id`) REFERENCES `products` (`id`) ON DELETE SET NULL
);
```

**Pros:**
- Clean separation of concerns
- No nullable fields waste
- Domain-specific constraints easier
- Smaller base table
- Better normalization

**Cons:**
- Requires joins for full product data
- More complex queries
- More tables to maintain
- Slightly more complex migration

**Implementation Complexity:** Medium-High  
**Migration Risk:** Medium  
**Performance Impact:** Low-Medium (with proper indexing)

---

### Strategy 3: Metadata/JSONB Approach

**Concept:**
- Single `products` table with core fields
- `metadata` JSONB column for domain-specific fields
- Use JSON indexes for querying
- Application-level type handling

**Schema Design:**
```sql
CREATE TABLE `products` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint unsigned NOT NULL,
  `product_type` enum('sales', 'catalog', 'unified') NOT NULL,
  
  -- Core fields
  `name` varchar(255) NOT NULL,
  `canonical_name` json NULL,
  `description` text NULL,
  `code` varchar(255) NULL,
  `status` enum('draft', 'active', 'discontinued', 'pending', 'inactive') NOT NULL,
  `barcode` varchar(255) NULL,
  
  -- Common relationships
  `category_id` bigint unsigned NULL,
  `catalog_item_id` bigint unsigned NULL,
  `tax_id` bigint unsigned NULL,
  `user_id` bigint unsigned NULL,
  
  -- Metadata JSONB column
  `metadata` json NULL,
  
  `deleted_at` timestamp NULL,
  `created_at` timestamp NULL,
  `updated_at` timestamp NULL,
  
  PRIMARY KEY (`id`),
  KEY `products_company_id_index` (`company_id`),
  KEY `products_product_type_index` (`product_type`),
  KEY `products_metadata_index` ((CAST(`metadata` AS CHAR(255) ARRAY))) -- JSON index
);

-- Example metadata structure:
-- {
--   "sales": {
--     "section": "accounting",
--     "track_stock": true,
--     "discount_amount": 10.00,
--     "cost_center_id": 5
--   },
--   "catalog": {
--     "brand": "Nike",
--     "manufacturer": "Nike Inc",
--     "catalog_product_type": "physical"
--   }
-- }
```

**Pros:**
- Very flexible schema
- Easy to add new fields
- No schema migrations for metadata changes
- Single table simplicity
- Good for evolving requirements

**Cons:**
- Harder to query JSON fields
- No foreign key constraints on metadata
- Validation complexity
- Performance concerns with JSON queries
- Less type safety

**Implementation Complexity:** High  
**Migration Risk:** Low  
**Performance Impact:** High (JSON queries slower)

---

### Strategy 4: Unified Variant-Based Architecture (Recommended)

**Concept:**
- Use catalog's variant-based architecture as foundation
- Extend variants to support sales product features
- Migrate sales products as catalog products with single variant
- Unified product model with optional variant complexity

**Schema Design:**
```sql
-- Unified products table (based on catalog_products)
CREATE TABLE `products` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint unsigned NOT NULL,
  `catalog_item_id` bigint unsigned NULL, -- Can be nullable for simple products
  `parent_id` bigint unsigned NULL,
  
  -- Core fields
  `canonical_name` json NOT NULL, -- Always use translatable
  `description` json NULL,
  `code` varchar(255) NULL, -- Auto-generated code
  `status` enum('draft', 'active', 'discontinued', 'pending', 'inactive') NOT NULL DEFAULT 'draft',
  
  -- Product metadata
  `brand` varchar(255) NULL,
  `manufacturer` varchar(255) NULL,
  `product_type` enum('physical', 'digital', 'spare_part', 'service') NOT NULL DEFAULT 'physical',
  
  -- Sales-specific flags (for simple products)
  `section` varchar(255) NULL, -- 'accounting', 'procurement'
  `is_simple_product` tinyint(1) DEFAULT 0, -- Flag for sales-style products
  
  -- Accounting accounts
  `revenue_account_id` bigint unsigned NULL,
  `expense_account_id` bigint unsigned NULL,
  `inventory_account_id` bigint unsigned NULL,
  `cogs_account_id` bigint unsigned NULL,
  `purchase_account_id` bigint unsigned NULL, -- Sales products use this
  
  -- Common fields
  `tax_id` bigint unsigned NULL,
  `purchase_tax_id` bigint unsigned NULL,
  `cost_center_id` bigint unsigned NULL,
  `user_id` bigint unsigned NULL,
  
  `deleted_at` timestamp NULL,
  `created_at` timestamp NULL,
  `updated_at` timestamp NULL,
  
  PRIMARY KEY (`id`),
  KEY `products_company_id_index` (`company_id`),
  KEY `products_status_index` (`status`),
  KEY `products_section_index` (`section`),
  KEY `products_is_simple_product_index` (`is_simple_product`)
);

-- Unified variants table (extends catalog_product_variants)
CREATE TABLE `product_variants` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `product_id` bigint unsigned NOT NULL,
  `variant_group_id` bigint unsigned NULL,
  
  -- Identification
  `sku` varchar(255) NULL, -- Can be nullable for simple products
  `barcode` varchar(255) NULL,
  
  -- Sales-specific fields (for simple products)
  `track_stock` tinyint(1) DEFAULT 0,
  `initial_stock_level` double DEFAULT 0,
  `min_qty` int DEFAULT 1,
  `is_returnable` tinyint(1) DEFAULT 0,
  `discount_amount` decimal(15,4) NULL,
  `discount_type` varchar(255) NULL,
  `is_taxable` tinyint(1) DEFAULT 0,
  `sale_tax` varchar(255) NULL,
  `purchase_tax` varchar(255) NULL,
  `barcode_type` varchar(255) NULL,
  `barcode_path` varchar(255) NULL,
  
  -- Catalog-specific fields
  `origin_country` varchar(255) NULL,
  `quality_grade` enum('oem', 'compatible', 'aftermarket', 'refurbished') NULL,
  `manufacturer_location` varchar(255) NULL,
  `certification` varchar(255) NULL,
  `warranty_terms` varchar(255) NULL,
  `is_preferred` tinyint(1) DEFAULT 0,
  
  `notes` text NULL,
  
  `deleted_at` timestamp NULL,
  `created_at` timestamp NULL,
  `updated_at` timestamp NULL,
  
  PRIMARY KEY (`id`),
  UNIQUE KEY `product_variants_sku_unique` (`sku`),
  KEY `product_variants_product_id_index` (`product_id`),
  FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
);

-- Unified pricing table
CREATE TABLE `product_variant_prices` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `variant_id` bigint unsigned NOT NULL,
  `unit_id` bigint unsigned NOT NULL,
  `price_type_id` bigint unsigned NULL, -- For catalog price types
  `company_id` bigint unsigned NOT NULL,
  
  `purchasing_price` decimal(15,4) NULL, -- Sales products
  `selling_price` decimal(15,4) NOT NULL,
  `min_selling_price` decimal(15,4) NULL,
  `price` decimal(15,2) NULL, -- Catalog products (generic)
  
  `currency` varchar(3) DEFAULT 'USD',
  `min_quantity` decimal(15,3) NULL,
  `is_main_price` tinyint(1) DEFAULT 0, -- Sales products
  `valid_from` date NULL,
  `valid_to` date NULL,
  
  `created_at` timestamp NULL,
  `updated_at` timestamp NULL,
  
  PRIMARY KEY (`id`),
  KEY `product_variant_prices_variant_id_index` (`variant_id`),
  KEY `product_variant_prices_unit_id_index` (`unit_id`),
  FOREIGN KEY (`variant_id`) REFERENCES `product_variants` (`id`) ON DELETE CASCADE
);
```

**Migration Approach:**
1. Sales products → Products with `is_simple_product = 1`
2. Each sales product → Single variant (SKU = product code)
3. Sales prices → Variant prices
4. Maintain backward compatibility via model scopes

**Pros:**
- Leverages existing variant architecture
- Supports both simple and complex products
- Single unified model
- Rich feature set available to all products
- Scalable architecture
- Can gradually migrate sales products to use variants

**Cons:**
- More complex initial migration
- Requires variant creation for simple products
- Need to handle backward compatibility
- More complex queries for simple products

**Implementation Complexity:** High  
**Migration Risk:** Medium-High  
**Performance Impact:** Low (with proper indexing)

---

## 5. Golden Recommendation

### Recommended Strategy: **Unified Variant-Based Architecture (Strategy 4)**

### Rationale

1. **Future-Proof Architecture**
   - Variant-based system is more flexible
   - Can handle both simple and complex products
   - Supports future requirements (multi-SKU products, bundles, etc.)

2. **Leverages Existing Investment**
   - Catalog system already has robust variant architecture
   - Reuse existing code and patterns
   - Less new code to write

3. **Gradual Migration Path**
   - Can migrate sales products incrementally
   - Maintain backward compatibility
   - Low-risk migration approach

4. **Unified Data Model**
   - Single source of truth
   - Consistent product representation
   - Better reporting and analytics

5. **Rich Feature Set**
   - All products can leverage variant features
   - Multi-language support
   - Advanced inventory management
   - Better pricing flexibility

### Implementation Approach

**Phase 1: Foundation (Weeks 1-2)**
- Create unified `products` and `product_variants` tables
- Create migration scripts
- Set up dual-write mechanism (write to both old and new tables)

**Phase 2: Data Migration (Weeks 3-4)**
- Migrate catalog products (minimal changes)
- Migrate sales products (create variants)
- Data validation and reconciliation

**Phase 3: API Unification (Weeks 5-6)**
- Create unified Product model
- Update controllers to use unified model
- Maintain backward compatibility via adapters

**Phase 4: Testing & Validation (Weeks 7-8)**
- Comprehensive testing
- Performance optimization
- User acceptance testing

**Phase 5: Cutover (Week 9)**
- Switch to unified system
- Monitor for issues
- Rollback plan ready

**Phase 6: Cleanup (Week 10)**
- Remove old tables (after verification period)
- Remove dual-write mechanism
- Documentation updates

### Key Design Decisions

1. **Simple Product Flag**
   - Use `is_simple_product` flag to identify sales-style products
   - Allows different UI/UX for simple vs complex products
   - Maintains backward compatibility

2. **Variant Requirement**
   - All products must have at least one variant
   - Simple products have single variant
   - Complex products can have multiple variants

3. **Code Generation**
   - Maintain sales product code generation logic
   - Use code as SKU for simple products
   - Support both code and SKU systems

4. **Pricing Unification**
   - Unified pricing table supports both systems
   - Sales: `purchasing_price`, `selling_price`, `min_selling_price`
   - Catalog: `price`, `price_type_id`, `currency`
   - Support both pricing models

5. **Status Unification**
   - Use catalog's enum status system
   - Map sales boolean status: `0` → `'inactive'`, `1` → `'active'`
   - Support all status values

---

## 6. Migration Roadmap

### Phase 1: Preparation & Planning (Week 1)

**Tasks:**
1. ✅ Create detailed migration plan
2. ✅ Set up staging environment
3. ✅ Backup production database
4. ✅ Create rollback procedures
5. ✅ Set up monitoring and logging
6. ✅ Document current system state

**Deliverables:**
- Migration plan document
- Database backup
- Rollback procedures
- Monitoring setup

---

### Phase 2: Schema Creation (Week 2)

**Tasks:**
1. Create unified `products` table migration
2. Create unified `product_variants` table migration
3. Create unified `product_variant_prices` table migration
4. Create data mapping tables (for migration tracking)
5. Create indexes and foreign keys
6. Test migrations on staging

**Migration Scripts:**
```php
// Migration: create_unified_products_table.php
Schema::create('products', function (Blueprint $table) {
    // ... unified schema
});

// Migration: create_unified_product_variants_table.php
Schema::create('product_variants', function (Blueprint $table) {
    // ... unified variant schema
});

// Migration: create_unified_product_variant_prices_table.php
Schema::create('product_variant_prices', function (Blueprint $table) {
    // ... unified pricing schema
});
```

**Deliverables:**
- Migration scripts
- Tested schema on staging
- Index optimization plan

---

### Phase 3: Dual-Write Mechanism (Week 3)

**Tasks:**
1. Create unified Product model
2. Implement dual-write service
3. Update create/update operations to write to both systems
4. Implement read-from-new-write-to-both pattern
5. Add feature flag for dual-write
6. Test dual-write mechanism

**Implementation:**
```php
class UnifiedProductService
{
    public function create(array $data)
    {
        DB::transaction(function () use ($data) {
            // Write to new unified system
            $product = UnifiedProduct::create($data);
            
            // Write to old system (if feature flag enabled)
            if (config('features.dual_write')) {
                $this->writeToOldSystem($product, $data);
            }
            
            return $product;
        });
    }
    
    private function writeToOldSystem($product, $data)
    {
        // Determine if sales or catalog product
        if ($data['is_simple_product'] ?? false) {
            SalesProduct::create($this->mapToSalesProduct($product, $data));
        } else {
            CatalogProduct::create($this->mapToCatalogProduct($product, $data));
        }
    }
}
```

**Deliverables:**
- Unified Product model
- Dual-write service
- Feature flag configuration
- Test results

---

### Phase 4: Data Migration - Catalog Products (Week 4)

**Tasks:**
1. Map catalog products to unified schema
2. Create migration script for catalog products
3. Migrate product data
4. Migrate variant data
5. Migrate pricing data
6. Validate migrated data
7. Reconcile discrepancies

**Migration Script:**
```php
class MigrateCatalogProductsCommand extends Command
{
    public function handle()
    {
        $catalogProducts = CatalogProduct::with(['variants', 'variants.prices'])->get();
        
        foreach ($catalogProducts as $catalogProduct) {
            DB::transaction(function () use ($catalogProduct) {
                // Create unified product
                $product = UnifiedProduct::create([
                    'company_id' => $catalogProduct->company_id,
                    'catalog_item_id' => $catalogProduct->catalog_item_id,
                    'parent_id' => $catalogProduct->parent_id,
                    'canonical_name' => $catalogProduct->canonical_name,
                    'description' => $catalogProduct->description,
                    'brand' => $catalogProduct->brand,
                    'manufacturer' => $catalogProduct->manufacturer,
                    'product_type' => $catalogProduct->product_type,
                    'status' => $this->mapStatus($catalogProduct->status),
                    'revenue_account_id' => $catalogProduct->revenue_account_id,
                    'expense_account_id' => $catalogProduct->expense_account_id,
                    'inventory_account_id' => $catalogProduct->inventory_account_id,
                    'cogs_account_id' => $catalogProduct->cogs_account_id,
                    'is_simple_product' => false,
                ]);
                
                // Migrate variants
                foreach ($catalogProduct->variants as $variant) {
                    $unifiedVariant = UnifiedProductVariant::create([
                        'product_id' => $product->id,
                        'sku' => $variant->sku,
                        'barcode' => $variant->barcode,
                        // ... other fields
                    ]);
                    
                    // Migrate variant prices
                    foreach ($variant->prices as $price) {
                        UnifiedProductVariantPrice::create([
                            'variant_id' => $unifiedVariant->id,
                            'price_type_id' => $price->price_type_id,
                            'price' => $price->price,
                            'currency' => $price->currency,
                            // ... other fields
                        ]);
                    }
                }
                
                // Record migration
                MigrationLog::create([
                    'source_table' => 'catalog_products',
                    'source_id' => $catalogProduct->id,
                    'target_table' => 'products',
                    'target_id' => $product->id,
                    'status' => 'completed',
                ]);
            });
        }
    }
}
```

**Deliverables:**
- Migration script
- Migrated catalog products
- Validation report
- Migration log

---

### Phase 5: Data Migration - Sales Products (Week 5)

**Tasks:**
1. Map sales products to unified schema
2. Create migration script for sales products
3. Migrate product data (create as simple products)
4. Create variants for each sales product
5. Migrate pricing data to variant prices
6. Migrate related data (images, attachments)
7. Validate migrated data
8. Reconcile discrepancies

**Migration Script:**
```php
class MigrateSalesProductsCommand extends Command
{
    public function handle()
    {
        $salesProducts = SalesProduct::with(['prices', 'media'])->get();
        
        foreach ($salesProducts as $salesProduct) {
            DB::transaction(function () use ($salesProduct) {
                // Create unified product (as simple product)
                $product = UnifiedProduct::create([
                    'company_id' => $salesProduct->company_id,
                    'user_id' => $salesProduct->user_id,
                    'canonical_name' => ['en' => $salesProduct->name], // Convert to translatable
                    'description' => $salesProduct->description ? ['en' => $salesProduct->description] : null,
                    'code' => $salesProduct->code,
                    'status' => $this->mapSalesStatus($salesProduct->status),
                    'section' => $salesProduct->section,
                    'is_simple_product' => true,
                    'category_id' => $salesProduct->category_id,
                    'tax_id' => $salesProduct->tax_id,
                    'purchase_tax_id' => $salesProduct->purchase_tax_id,
                    'purchase_account_id' => $salesProduct->purchase_account_id,
                    'cost_center_id' => $salesProduct->cost_center_id,
                ]);
                
                // Create single variant for sales product
                $variant = UnifiedProductVariant::create([
                    'product_id' => $product->id,
                    'sku' => $salesProduct->code, // Use code as SKU
                    'barcode' => $salesProduct->barcode,
                    'track_stock' => $salesProduct->track_stock,
                    'initial_stock_level' => $salesProduct->initial_stock_level,
                    'min_qty' => $salesProduct->min_qty,
                    'is_returnable' => $salesProduct->is_returnable,
                    'discount_amount' => $salesProduct->discount_amount,
                    'discount_type' => $salesProduct->discount_type,
                    'is_taxable' => $salesProduct->is_taxable,
                    'sale_tax' => $salesProduct->sale_tax,
                    'purchase_tax' => $salesProduct->purchase_tax,
                    'barcode_type' => $salesProduct->barcode_type,
                    'barcode_path' => $salesProduct->barcode_path,
                    'notes' => $salesProduct->notes,
                ]);
                
                // Migrate prices to variant prices
                foreach ($salesProduct->prices as $price) {
                    UnifiedProductVariantPrice::create([
                        'variant_id' => $variant->id,
                        'unit_id' => $price->unit_id,
                        'company_id' => $salesProduct->company_id,
                        'purchasing_price' => $price->purchasing_price,
                        'selling_price' => $price->selling_price,
                        'min_selling_price' => $price->min_selling_price,
                        'is_main_price' => $price->is_main_price,
                    ]);
                }
                
                // Migrate media
                foreach ($salesProduct->media as $media) {
                    // Copy media to unified product
                    // Implementation depends on media library
                }
                
                // Record migration
                MigrationLog::create([
                    'source_table' => 'sales_products',
                    'source_id' => $salesProduct->id,
                    'target_table' => 'products',
                    'target_id' => $product->id,
                    'status' => 'completed',
                ]);
            });
        }
    }
    
    private function mapSalesStatus($status)
    {
        return $status ? 'active' : 'inactive';
    }
}
```

**Deliverables:**
- Migration script
- Migrated sales products
- Validation report
- Migration log

---

### Phase 6: Relationship Migration (Week 6)

**Tasks:**
1. Migrate `sales_invoice_products` relationships
2. Migrate `purchase_products` relationships
3. Migrate `product_stores` relationships
4. Update foreign keys to point to unified products/variants
5. Validate relationships
6. Test relationship integrity

**Migration Script:**
```php
class MigrateProductRelationshipsCommand extends Command
{
    public function handle()
    {
        // Migrate sales_invoice_products
        $invoiceProducts = SalesInvoiceProduct::all();
        foreach ($invoiceProducts as $invoiceProduct) {
            $migration = MigrationLog::where('source_table', 'sales_products')
                ->where('source_id', $invoiceProduct->product_id)
                ->first();
            
            if ($migration) {
                // Find variant for this product
                $variant = UnifiedProductVariant::where('product_id', $migration->target_id)
                    ->where('sku', SalesProduct::find($invoiceProduct->product_id)->code)
                    ->first();
                
                if ($variant) {
                    $invoiceProduct->update([
                        'product_id' => $migration->target_id,
                        'variant_id' => $variant->id, // Add variant_id column
                    ]);
                }
            }
        }
        
        // Similar for purchase_products, product_stores, etc.
    }
}
```

**Deliverables:**
- Updated relationship tables
- Validation report
- Relationship integrity test results

---

### Phase 7: API Unification (Week 7)

**Tasks:**
1. Create unified ProductController
2. Update routes to use unified endpoints
3. Create adapter layer for backward compatibility
4. Update API resources
5. Update request validation
6. Maintain backward compatibility
7. Test API endpoints

**Implementation:**
```php
// Unified ProductController
class UnifiedProductController extends Controller
{
    public function index()
    {
        $products = UnifiedProductService::index();
        return UnifiedProductResource::collection($products);
    }
    
    public function store(UnifiedProductRequest $request)
    {
        $product = UnifiedProductService::create($request->validated());
        return new UnifiedProductResource($product);
    }
}

// Adapter for backward compatibility
class SalesProductAdapter
{
    public static function toUnified($salesProduct)
    {
        return UnifiedProduct::whereHas('variants', function ($q) use ($salesProduct) {
            $q->where('sku', $salesProduct->code);
        })->first();
    }
}

// Backward compatible controller
class SalesProductController extends Controller
{
    public function index()
    {
        // Use adapter to get unified products
        $products = UnifiedProduct::where('is_simple_product', true)
            ->where('section', request('section', 'accounting'))
            ->get();
        
        // Transform to sales product format
        return SalesProductResource::collection($products);
    }
}
```

**Deliverables:**
- Unified API endpoints
- Backward compatible adapters
- Updated API documentation
- Test results

---

### Phase 8: Testing & Validation (Week 8)

**Tasks:**
1. Unit tests for unified model
2. Integration tests for migration
3. API endpoint tests
4. Performance testing
5. Data integrity validation
6. User acceptance testing
7. Load testing
8. Security testing

**Test Scenarios:**
- Create simple product (sales-style)
- Create complex product (catalog-style)
- Update product
- Delete product
- Query products
- Filter by section
- Filter by product type
- Price calculations
- Inventory calculations
- Relationship queries

**Deliverables:**
- Test suite
- Test results report
- Performance benchmarks
- Security audit report

---

### Phase 9: Cutover (Week 9)

**Tasks:**
1. Final data validation
2. Switch feature flag to use unified system
3. Monitor system performance
4. Monitor error logs
5. User support
6. Rollback if needed

**Cutover Checklist:**
- [ ] All migrations completed successfully
- [ ] Data validation passed
- [ ] Performance tests passed
- [ ] API tests passed
- [ ] User acceptance testing completed
- [ ] Rollback plan ready
- [ ] Monitoring in place
- [ ] Support team briefed
- [ ] Documentation updated

**Deliverables:**
- Cutover completion report
- Monitoring dashboard
- Issue log

---

### Phase 10: Cleanup (Week 10)

**Tasks:**
1. Remove dual-write mechanism
2. Archive old tables (keep for reference)
3. Remove old models (after verification period)
4. Update documentation
5. Train team on new system
6. Performance optimization
7. Final validation

**Cleanup Tasks:**
- Remove `sales_products` table (after 30-day verification)
- Remove `catalog_products` table (after 30-day verification)
- Remove old controllers (after API deprecation period)
- Update all documentation
- Archive migration scripts

**Deliverables:**
- Cleanup completion report
- Updated documentation
- Team training materials

---

## 7. Risk Assessment

### High-Risk Areas

1. **Data Loss Risk**
   - **Mitigation:** Comprehensive backups, dual-write mechanism, rollback procedures
   - **Probability:** Low
   - **Impact:** Critical

2. **Performance Degradation**
   - **Mitigation:** Proper indexing, query optimization, performance testing
   - **Probability:** Medium
   - **Impact:** High

3. **Data Inconsistency**
   - **Mitigation:** Data validation scripts, reconciliation procedures
   - **Probability:** Medium
   - **Impact:** High

4. **API Breaking Changes**
   - **Mitigation:** Backward compatibility adapters, API versioning
   - **Probability:** Medium
   - **Impact:** Medium

5. **Migration Time**
   - **Mitigation:** Incremental migration, parallel systems during transition
   - **Probability:** Low
   - **Impact:** Medium

### Medium-Risk Areas

1. **User Confusion**
   - **Mitigation:** User training, documentation, gradual rollout
   - **Probability:** Medium
   - **Impact:** Medium

2. **Integration Issues**
   - **Mitigation:** Comprehensive testing, integration test suite
   - **Probability:** Low
   - **Impact:** Medium

3. **Code Complexity**
   - **Mitigation:** Code reviews, refactoring, documentation
   - **Probability:** Medium
   - **Impact:** Low

### Low-Risk Areas

1. **Schema Changes**
   - **Mitigation:** Well-planned migrations, testing
   - **Probability:** Low
   - **Impact:** Low

2. **Documentation Updates**
   - **Mitigation:** Documentation as part of migration
   - **Probability:** Low
   - **Impact:** Low

---

## 8. Appendix

### 8.1 Field Mapping Reference

#### Sales Products → Unified Products

| Sales Products Field | Unified Products Field | Transformation |
|---------------------|----------------------|----------------|
| `id` | `id` | Direct mapping |
| `company_id` | `company_id` | Direct mapping |
| `user_id` | `user_id` | Direct mapping |
| `name` | `canonical_name` | Convert to JSON: `{'en': name}` |
| `description` | `description` | Convert to JSON: `{'en': description}` |
| `code` | `code` | Direct mapping |
| `status` (0/1) | `status` | Map: `0` → `'inactive'`, `1` → `'active'` |
| `section` | `section` | Direct mapping |
| `type` | N/A | Store in variant or metadata |
| `category_id` | `category_id` | Direct mapping |
| `tax_id` | `tax_id` | Direct mapping |
| `purchase_tax_id` | `purchase_tax_id` | Direct mapping |
| `purchase_account_id` | `purchase_account_id` | Direct mapping |
| `cost_center_id` | `cost_center_id` | Direct mapping |
| `barcode` | `variants.barcode` | Move to variant |
| `track_stock` | `variants.track_stock` | Move to variant |
| `initial_stock_level` | `variants.initial_stock_level` | Move to variant |
| `discount_amount` | `variants.discount_amount` | Move to variant |
| `discount_type` | `variants.discount_type` | Move to variant |
| `is_taxable` | `variants.is_taxable` | Move to variant |
| `min_qty` | `variants.min_qty` | Move to variant |
| `is_returnable` | `variants.is_returnable` | Move to variant |
| `barcode_type` | `variants.barcode_type` | Move to variant |
| `barcode_path` | `variants.barcode_path` | Move to variant |
| `tags` | N/A | Store in metadata or separate table |
| `notes` | `variants.notes` | Move to variant |
| `image` | `images` | Migrate to media library |
| `prices` | `variants.prices` | Migrate to variant prices |

#### Catalog Products → Unified Products

| Catalog Products Field | Unified Products Field | Transformation |
|----------------------|----------------------|----------------|
| `id` | `id` | Direct mapping |
| `company_id` | `company_id` | Direct mapping (from BaseModel) |
| `catalog_item_id` | `catalog_item_id` | Direct mapping |
| `parent_id` | `parent_id` | Direct mapping |
| `canonical_name` | `canonical_name` | Direct mapping |
| `description` | `description` | Direct mapping |
| `brand` | `brand` | Direct mapping |
| `manufacturer` | `manufacturer` | Direct mapping |
| `product_type` | `product_type` | Direct mapping |
| `status` | `status` | Direct mapping |
| `revenue_account_id` | `revenue_account_id` | Direct mapping |
| `expense_account_id` | `expense_account_id` | Direct mapping |
| `inventory_account_id` | `inventory_account_id` | Direct mapping |
| `cogs_account_id` | `cogs_account_id` | Direct mapping |
| `variants` | `variants` | Direct mapping (with adjustments) |

### 8.2 Status Mapping

#### Sales Products Status → Unified Status

| Sales Status | Unified Status | Notes |
|-------------|---------------|-------|
| `0` (false) | `'inactive'` | Disabled product |
| `1` (true) | `'active'` | Active product |

#### Catalog Products Status → Unified Status

| Catalog Status | Unified Status | Notes |
|---------------|---------------|-------|
| `'draft'` | `'draft'` | Direct mapping |
| `'active'` | `'active'` | Direct mapping |
| `'discontinued'` | `'discontinued'` | Direct mapping |
| `'pending'` | `'pending'` | Direct mapping |

### 8.3 Code Generation Logic

**Sales Products:**
- Format: `ITM-{category_code}-{sequence}`
- Example: `ITM-0001-01-01-03`

**Unified Products:**
- Simple products: Maintain sales code generation
- Complex products: Use SKU from variants
- Code can be used as SKU for simple products

### 8.4 Pricing Model Mapping

#### Sales Product Prices → Variant Prices

| Sales Price Field | Variant Price Field | Notes |
|------------------|-------------------|-------|
| `product_id` | `variant_id` | Link to variant |
| `unit_id` | `unit_id` | Direct mapping |
| `purchasing_price` | `purchasing_price` | Direct mapping |
| `selling_price` | `selling_price` | Direct mapping |
| `min_selling_price` | `min_selling_price` | Direct mapping |
| `is_main_price` | `is_main_price` | Direct mapping |
| `company_id` | `company_id` | Direct mapping |

#### Catalog Variant Prices → Unified Variant Prices

| Catalog Price Field | Unified Price Field | Notes |
|-------------------|-------------------|-------|
| `variant_id` | `variant_id` | Direct mapping |
| `price_type_id` | `price_type_id` | Direct mapping |
| `price` | `price` | Direct mapping |
| `currency` | `currency` | Direct mapping |
| `min_quantity` | `min_quantity` | Direct mapping |
| `valid_from` | `valid_from` | Direct mapping |
| `valid_to` | `valid_to` | Direct mapping |

### 8.5 Related Tables Migration

#### Tables Requiring Updates

1. **`sales_invoice_products`**
   - Add `variant_id` column
   - Update foreign key to `products.id`
   - Migrate existing records

2. **`purchase_products`**
   - Add `variant_id` column
   - Update foreign key to `products.id`
   - Migrate existing records

3. **`product_stores`**
   - Update foreign key to `products.id`
   - Consider variant-level stores

4. **`product_price_histories`**
   - Update to use variant prices
   - Migrate existing records

5. **`catalog_inventories`**
   - Already uses variants
   - Update foreign key if needed

6. **`catalog_inventory_transactions`**
   - Already uses variants
   - Update foreign key if needed

### 8.6 Performance Considerations

**Indexes Required:**
- `products.company_id`
- `products.status`
- `products.section`
- `products.is_simple_product`
- `products.catalog_item_id`
- `product_variants.product_id`
- `product_variants.sku`
- `product_variants.barcode`
- `product_variant_prices.variant_id`
- `product_variant_prices.unit_id`

**Query Optimization:**
- Use eager loading for relationships
- Implement query scopes for common filters
- Cache frequently accessed data
- Use database views for complex queries

### 8.7 Rollback Procedures

**If Migration Fails:**
1. Stop dual-write mechanism
2. Switch back to old system
3. Restore from backup if needed
4. Investigate failure cause
5. Fix issues
6. Retry migration

**If Performance Degrades:**
1. Analyze slow queries
2. Add missing indexes
3. Optimize queries
4. Consider partitioning if needed
5. Scale infrastructure if needed

**If Data Inconsistency Detected:**
1. Run reconciliation scripts
2. Fix inconsistencies
3. Re-validate data
4. Continue migration

---

## Conclusion

The unification of Sales Products and Catalog Products into a single unified variant-based architecture is a complex but necessary evolution of the system. The recommended approach leverages the existing catalog variant architecture while maintaining backward compatibility and providing a clear migration path.

**Key Success Factors:**
1. Comprehensive planning and testing
2. Incremental migration approach
3. Backward compatibility during transition
4. Proper monitoring and rollback procedures
5. Team training and documentation

**Expected Benefits:**
- Single source of truth for products
- Unified product management
- Better reporting and analytics
- Richer feature set for all products
- Simplified architecture
- Better user experience

**Timeline:** 10 weeks  
**Risk Level:** Medium-High  
**Complexity:** High  
**ROI:** High (long-term)

---

**Document Version:** 1.0  
**Last Updated:** January 2026  
**Author:** Technical Analysis Team  
**Status:** Draft for Review