# BOM System Redesign - Normalization & ACID Compliance

## Current Issues Identified

### 1. Data Repetition (Denormalization)
- **`boms.category`**: Duplicates data from `products` → `product_categories` → `categories`
- **`boms.type`**: Duplicates `products.name` 
- **`bom_items.use_dimensions`**: Can be derived from `products.track_by_dimensions`
- **`bom_items.item_type`**: May be redundant if derivable from product categorization

### 2. ACID Violations
- No database transactions in `createBOM()` - if item creation fails, BOM is left orphaned
- No transactions in `updateBOM()` - partial updates possible
- No transactions in `deleteBOM()` - cascade deletes not wrapped

### 3. Normalization Issues
- **1NF Violation**: Storing derived/computed data (`category`, `type`, `use_dimensions`)
- **3NF Violation**: BOM depends on Product, but storing Product attributes directly

## Proposed Normalized Schema

### `boms` Table (Simplified)
```sql
CREATE TABLE boms (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  fg_product_id BIGINT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  FOREIGN KEY (fg_product_id) REFERENCES products(id) ON DELETE CASCADE ON UPDATE CASCADE,
  UNIQUE KEY unique_bom_per_product (fg_product_id),
  INDEX idx_fg_product (fg_product_id)
);
```

**Removed Fields:**
- `category` - Derive from `products.id` → `product_categories.category_id` → `categories.name`
- `type` - Derive from `products.name`

### `bom_items` Table (Normalized)
```sql
CREATE TABLE bom_items (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  bom_id BIGINT NOT NULL,
  rm_product_id BIGINT NOT NULL,
  item_type ENUM('FABRIC', 'BUTTONS', 'ZIPPER', 'LINING', 'ELASTIC') NULL,
  
  -- Quantity-based requirements (for special items RM)
  quantity_per_unit DECIMAL(12, 3) NULL,
  
  -- Dimension-based requirements (for dimension-based RM)
  required_length DECIMAL(12, 3) NULL,
  required_width DECIMAL(12, 3) NULL,
  dimension_unit ENUM('inch', 'cm', 'm') NULL,
  
  FOREIGN KEY (bom_id) REFERENCES boms(id) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (rm_product_id) REFERENCES products(id) ON DELETE RESTRICT ON UPDATE CASCADE,
  
  INDEX idx_bom (bom_id),
  INDEX idx_rm_product (rm_product_id),
  INDEX idx_item_type (item_type),
  
  -- Constraint: Either quantity-based OR dimension-based, not both
  CONSTRAINT chk_bom_item_type CHECK (
    (quantity_per_unit IS NOT NULL AND required_length IS NULL AND required_width IS NULL AND dimension_unit IS NULL) OR
    (quantity_per_unit IS NULL AND required_length IS NOT NULL AND required_width IS NOT NULL AND dimension_unit IS NOT NULL)
  )
);
```

**Removed Fields:**
- `use_dimensions` - Derive from `products.track_by_dimensions`

**Logic:**
- If `required_length` + `required_width` + `dimension_unit` are set → dimension-based
- If `quantity_per_unit` is set → quantity-based
- Enforced by CHECK constraint

## Implementation Plan

### Phase 1: Database Migration
1. Remove `category` and `type` from `boms` table
2. Remove `use_dimensions` from `bom_items` table
3. Add CHECK constraint to `bom_items` for data integrity
4. Add unique constraint on `boms.fg_product_id` (one BOM per product)

### Phase 2: Model Updates
1. Update `BOM` model - remove `category` and `type` fields
2. Update `BOMItem` model - remove `use_dimensions` field
3. Add virtual/computed getters for derived fields:
   - `BOM.getCategory()` - joins to get category name
   - `BOM.getType()` - returns `product.name`
   - `BOMItem.getUseDimensions()` - returns `product.track_by_dimensions`

### Phase 3: Service Layer (ACID Compliance)
1. Wrap all BOM operations in transactions:
   ```javascript
   const transaction = await sequelize.transaction();
   try {
     // Create BOM
     // Create BOM items
     await transaction.commit();
   } catch (error) {
     await transaction.rollback();
     throw error;
   }
   ```
2. Add proper error handling and rollback on failures
3. Add validation before transactions to avoid unnecessary DB calls

### Phase 4: API Layer
1. Update response transformations to include derived fields:
   - Include `category` and `type` in responses (derived from Product)
   - Include `use_dimensions` in BOM item responses (derived from RM product)
2. Update validation to ensure proper constraints

### Phase 5: Frontend Updates
1. Remove `category` and `type` from form submission
2. Display `category` and `type` from product details (read-only)
3. Derive `use_dimensions` from selected RM product instead of user input

## Benefits

### Normalization
- ✅ No data duplication - single source of truth
- ✅ Easier updates - change product category, all BOMs reflect it
- ✅ Reduced storage - smaller tables
- ✅ Better data integrity - cannot have inconsistent category/type

### ACID Compliance
- ✅ Atomic operations - all-or-nothing BOM creation
- ✅ Consistency - database constraints ensure valid state
- ✅ Isolation - transactions prevent concurrent modification issues
- ✅ Durability - committed changes are permanent

### Maintainability
- ✅ Cleaner schema - fewer redundant fields
- ✅ Easier to understand - clear relationships
- ✅ Better performance - indexes on proper foreign keys
- ✅ Future-proof - easier to extend

## Migration Strategy

1. **Create new migration script** to:
   - Drop `category` and `type` from `boms`
   - Drop `use_dimensions` from `bom_items`
   - Add CHECK constraint
   - Add unique constraint on `fg_product_id`

2. **Update code in stages:**
   - Models first
   - Services with transactions
   - API responses
   - Frontend last

3. **Test thoroughly:**
   - BOM creation with transaction rollback
   - BOM update with partial failures
   - BOM deletion with cascade
   - Derived field calculations

## Notes

- `item_type` kept for now - may be useful for categorization even if derivable
- Consider making `item_type` derived from product metadata in future
- All derived fields are computed at query time (no caching yet)
