# Product Variants & Multi-Unit Management Technical Plan

## Overview
This document outlines the technical approach for implementing a flexible product variant system that supports complex attributes (Type, Size, Color, Packaging) and multi-unit handling (Purchase vs Sale units like 1 case = 24 pieces).

## 1. Problem Analysis

### Current Challenge
The existing product creation system needs to handle:
- **Pepsi Example**: Same product with different variants:
  - Type: Can, Pet Bottle, Glass Bottle
  - Size: 250ml, 400ml, 1 Liter
  - Packaging: Individual, Case (24pcs), Pack (6pcs)
- **Complex Variants**: Products with Color, Size, Type, Package Type combinations
- **Multi-Unit Pricing**: Different prices for different units (single vs case vs pack)
- **Inventory Tracking**: Track stock in base unit but sell in multiple units

## 2. Database Schema Design

### Core Tables Structure

```mermaid
erDiagram
    PRODUCTS ||--o{ PRODUCT_VARIANTS : has
    PRODUCTS ||--o{ PRODUCT_UNITS : has
    PRODUCT_VARIANTS ||--o{ VARIANT_ATTRIBUTES : has
    PRODUCT_VARIANTS ||--o{ VARIANT_PRICES : has
    PRODUCT_UNITS ||--o{ UNIT_CONVERSIONS : has

    PRODUCTS {
        int id PK
        string name
        string category
        string description
        boolean has_variants
        string base_unit
        datetime created_at
        datetime updated_at
    }

    PRODUCT_VARIANTS {
        int id PK
        int product_id FK
        string sku
        string barcode
        decimal base_price
        int stock_quantity
        boolean is_active
        datetime created_at
    }

    VARIANT_ATTRIBUTES {
        int id PK
        int variant_id FK
        string attribute_type
        string attribute_value
        string display_name
    }

    PRODUCT_UNITS {
        int id PK
        int product_id FK
        string unit_name
        string unit_code
        decimal conversion_factor
        boolean is_base_unit
        boolean is_purchase_unit
        boolean is_sale_unit
    }

    VARIANT_PRICES {
        int id PK
        int variant_id FK
        int unit_id FK
        decimal price
        decimal cost_price
        boolean is_default
    }
```

### Detailed Table Definitions

#### Products Table
```sql
CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    name VARCHAR(255) NOT NULL,
    category VARCHAR(100),
    description TEXT,
    has_variants BOOLEAN DEFAULT false,
    base_unit VARCHAR(50) DEFAULT 'pcs',
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
```

#### Product Variants Table
```sql
CREATE TABLE product_variants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    product_id UUID REFERENCES products(id),
    sku VARCHAR(100) UNIQUE NOT NULL,
    barcode VARCHAR(100),
    base_price DECIMAL(10,2) DEFAULT 0.00,
    stock_quantity INTEGER DEFAULT 0,
    min_stock_level INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
```

#### Variant Attributes Table
```sql
CREATE TABLE variant_attributes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    variant_id UUID REFERENCES product_variants(id),
    attribute_type VARCHAR(50) NOT NULL, -- 'type', 'size', 'color', 'package'
    attribute_value VARCHAR(100) NOT NULL,
    display_name VARCHAR(100),
    display_order INTEGER DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
```

#### Product Units Table
```sql
CREATE TABLE product_units (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    product_id UUID REFERENCES products(id),
    unit_name VARCHAR(50) NOT NULL, -- 'piece', 'case', 'pack', 'dozen'
    unit_code VARCHAR(20) NOT NULL, -- 'pcs', 'case', 'pack'
    conversion_factor DECIMAL(10,3) NOT NULL, -- 1 case = 24 pcs
    is_base_unit BOOLEAN DEFAULT false,
    is_purchase_unit BOOLEAN DEFAULT false,
    is_sale_unit BOOLEAN DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
```

#### Variant Prices Table
```sql
CREATE TABLE variant_prices (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    variant_id UUID REFERENCES product_variants(id),
    unit_id UUID REFERENCES product_units(id),
    price DECIMAL(10,2) NOT NULL,
    cost_price DECIMAL(10,2) DEFAULT 0.00,
    is_default BOOLEAN DEFAULT false,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
```

## 3. UI/UX Design for Product Creation

### Product Create Page Structure

#### Step 1: Basic Product Information
```
┌─────────────────────────────────────┐
│ Product Name: [Pepsi]               │
│ Category:     [Beverages] ▼       │
│ Description:  [Carbonated Drink]    │
│ Base Unit:    [Pieces] ▼           │
│ Has Variants: ☑ Yes ☐ No          │
└─────────────────────────────────────┘
```

#### Step 2: Variant Configuration (if Has Variants = Yes)
```
┌─────────────────────────────────────┐
│ Add Variant Attributes:             │
│                                     │
│ Attribute 1:                        │
│ Name: [Type]         Type: ▼       │
│ Values: [Can, Pet Bottle, Glass]    │
│                                     │
│ Attribute 2:                        │
│ Name: [Size]         Type: ▼       │
│ Values: [250ml, 400ml, 1Liter]      │
│                                     │
│ [+ Add Attribute]                   │
└─────────────────────────────────────┘
```

#### Step 3: Unit Configuration
```
┌─────────────────────────────────────┐
│ Base Unit: Pieces (1)               │
│                                     │
│ Additional Units:                   │
│ ┌─────────────────────────────────┐ │
│ │ Unit    │ Ratio │ Purchase │ Sale│ │
│ │ Case    │ 24    │    ☑     │  ☑  │ │
│ │ Pack    │ 6     │    ☐     │  ☑  │ │
│ │ Dozen   │ 12    │    ☐     │  ☐  │ │
│ └─────────────────────────────────┘ │
│ [+ Add Unit]                        │
└─────────────────────────────────────┘
```

#### Step 4: Variant Matrix & Pricing
```
┌─────────────────────────────────────────────────────────────┐
│ Variant Matrix:                                             │
│                                                             │
│ Type: Can, Size: 250ml → SKU: PEP-CAN-250    [Auto-generate]│
│ Type: Can, Size: 400ml → SKU: PEP-CAN-400    [Auto-generate]│
│ Type: Pet, Size: 1L    → SKU: PEP-PET-1L     [Auto-generate]│
│                                                             │
│ Pricing per Variant:                                        │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ Variant        │ Unit  │ Price  │ Cost  │ Stock      │ │
│ │ PEP-CAN-250    │ Piece │ $0.50  │ $0.30 │ [100]      │ │
│ │ PEP-CAN-250    │ Case  │ $10.00 │ $7.20 │ [4]        │ │
│ │ PEP-PET-1L     │ Piece │ $1.20  │ $0.80 │ [50]       │ │
│ │ PEP-PET-1L     │ Case  │ $25.00 │ $19.20│ [2]        │ │
│ └─────────────────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────┘
```

## 4. Business Logic Implementation

### Variant Generation Logic
```javascript
// Generate all possible combinations
function generateVariants(attributes) {
    const combinations = [];
    
    // Recursive function to create all combinations
    function createCombinations(current, remainingAttrs) {
        if (remainingAttrs.length === 0) {
            combinations.push({...current});
            return;
        }
        
        const [attr, ...rest] = remainingAttrs;
        for (const value of attr.values) {
            createCombinations({...current, [attr.name]: value}, rest);
        }
    }
    
    createCombinations({}, attributes);
    return combinations;
}
```

### SKU Generation Strategy
```javascript
// Auto-generate SKU based on product and attributes
function generateSKU(productName, attributes) {
    const prefix = productName.substring(0, 3).toUpperCase();
    const attrCodes = Object.entries(attributes)
        .map(([key, value]) => {
            const keyCode = key.substring(0, 3).toUpperCase();
            const valCode = value.substring(0, 3).toUpperCase();
            return `${keyCode}-${valCode}`;
        })
        .join('-');
    
    return `${prefix}-${attrCodes}`;
}
```

### Unit Conversion Logic
```javascript
// Convert between units
class UnitConverter {
    constructor(units) {
        this.units = units; // Array of {unit, conversion_factor}
    }
    
    convert(quantity, fromUnit, toUnit) {
        const from = this.units.find(u => u.unit === fromUnit);
        const to = this.units.find(u => u.unit === toUnit);
        
        if (!from || !to) return null;
        
        // Convert to base unit first, then to target unit
        const baseQuantity = quantity * from.conversion_factor;
        return baseQuantity / to.conversion_factor;
    }
}
```

## 5. API Endpoints

### Product Management APIs
```
POST   /api/products                    - Create product
GET    /api/products/:id               - Get product details
PUT    /api/products/:id               - Update product
DELETE /api/products/:id               - Delete product

POST   /api/products/:id/variants     - Create variants
GET    /api/products/:id/variants      - List all variants
PUT    /api/variants/:id               - Update variant
DELETE /api/variants/:id               - Delete variant

POST   /api/products/:id/units        - Add unit conversion
GET    /api/products/:id/units         - List unit conversions
PUT    /api/units/:id                  - Update unit
DELETE /api/units/:id                  - Delete unit

POST   /api/variants/:id/prices       - Set variant prices
GET    /api/variants/:id/prices        - Get variant prices
```

## 6. Pepsi Example Solution

### Scenario: Creating Pepsi with Complex Variants

1. **Product Setup**:
   - Name: "Pepsi"
   - Category: "Beverages"
   - Base Unit: "Pieces"
   - Has Variants: Yes

2. **Variant Attributes**:
   - Type: [Can, Pet Bottle, Glass Bottle]
   - Size: [250ml, 400ml, 1Liter]
   - Package: [Single, Pack, Case]

3. **Unit Configuration**:
   - Base: 1 Piece
   - Pack: 6 Pieces
   - Case: 24 Pieces

4. **Generated Variants** (27 total combinations):
   - Pepsi-Can-250ml-Single
   - Pepsi-Can-250ml-Pack
   - Pepsi-Can-250ml-Case
   - Pepsi-PetBottle-1Liter-Single
   - ... (all combinations)

5. **Pricing Example**:
   - Pepsi-Can-250ml:
     - Single: $0.50
     - Pack: $2.80 (6 × $0.466)
     - Case: $10.80 (24 × $0.45)

## 7. Implementation Benefits

### For Business Users:
- **Flexible Product Management**: Handle any product with any combination of attributes
- **Accurate Pricing**: Set different prices for different units and variants
- **Inventory Control**: Track stock at variant level with unit conversions
- **Scalability**: Easy to add new attributes or units without code changes

### For Developers:
- **Normalized Database**: Clean schema that prevents data redundancy
- **Extensible Design**: Easy to add new attribute types or pricing rules
- **Performance**: Efficient queries with proper indexing
- **Maintainability**: Clear separation of concerns between products, variants, and pricing

## 8. Next Steps

1. **Database Migration**: Create the new tables and migrate existing data
2. **Backend API Development**: Implement the CRUD operations for all entities
3. **Frontend Implementation**: Build the multi-step product creation form
4. **Testing**: Comprehensive testing with various product scenarios
5. **Training**: Document the new workflow for business users

This plan provides a robust foundation for handling complex product variants while maintaining simplicity for basic products without variants.