# Inventory ACID Compliance & Normalization Fix

## Issues Identified

### 1. ACID Violations
- `Inventory.quantity` is manually maintained, not enforced
- No database-level constraint ensuring `quantity = COUNT(InventoryItems WHERE status='IN_STOCK')`
- Transactions can fail partway, leaving systems out of sync
- No triggers to auto-maintain consistency

### 2. Normalization Violations  
- `Inventory.quantity` violates 3NF - it's redundant computed data
- Should be calculated, not stored
- Creates data duplication and sync issues

### 3. Current Status (Summer Dress - Product ID 3)
- **Base Product (variant_id = NULL):**
  - Inventory.quantity: 120
  - InventoryItems IN_STOCK: 120 ✅ **MATCHES**
  - Total InventoryItems: 120
  
- **Variant 1 (variant_id = 1):**
  - Inventory.quantity: 100
  - InventoryItems IN_STOCK: 100 ✅ **MATCHES**
  - Total InventoryItems: 100

**Note:** Currently data appears consistent, but the system lacks enforcement mechanisms.

## Solution Implementation

### Phase 1: Immediate Fix (Triggers)

**File:** `server/scripts/migrations/005_create_inventory_sync_triggers.sql`

**What it does:**
- Creates database triggers to auto-maintain `Inventory.quantity`
- Trigger fires on INSERT/UPDATE/DELETE of InventoryItems
- Automatically increments/decrements quantity based on status changes
- Ensures ACID compliance at database level

**To apply:**
```bash
mysql -h localhost -u root -p xyz_pos_db < server/scripts/migrations/005_create_inventory_sync_triggers.sql
```

### Phase 2: Sync Fix Script

**File:** `server/scripts/fix-inventory-sync.js`

**What it does:**
- Identifies all discrepancies between Inventory.quantity and InventoryItem counts
- Recalculates quantity from actual IN_STOCK items
- Can run in dry-run mode (--dry-run) or apply fixes (--apply)

**Usage:**
```bash
# Dry run (check only)
node server/scripts/fix-inventory-sync.js

# Fix all discrepancies
node server/scripts/fix-inventory-sync.js --apply

# Fix specific product
node server/scripts/fix-inventory-sync.js --product=3 --apply

# Fix specific product/variant
node server/scripts/fix-inventory-sync.js --product=3 --variant=1 --apply
```

### Phase 3: Code Changes (Remove Manual Quantity Updates)

**Files to modify:**
1. `server/modules/procurement/services/grns.js`
2. `server/modules/production/services/productionOrders.js`
3. `server/modules/sales/services/index.js`
4. `server/modules/inventory/services/index.js`

**Changes:**
- Remove all manual `inventory.increment('quantity')` calls
- Remove all manual `inventory.quantity = ...` updates
- Let triggers handle quantity automatically
- Keep InventoryMovement records for audit trail

### Phase 4: Long-term Solution (Computed Quantity)

**Option A: Database VIEW**
```sql
CREATE VIEW inventory_with_computed_quantity AS
SELECT 
  i.id,
  i.product_id,
  i.variant_id,
  i.reorder_level,
  i.created_at,
  i.updated_at,
  COALESCE(COUNT(ii.id), 0) as quantity
FROM inventories i
LEFT JOIN inventory_items ii ON 
  i.product_id = ii.product_id AND
  (i.variant_id = ii.variant_id OR (i.variant_id IS NULL AND ii.variant_id IS NULL)) AND
  ii.status = 'IN_STOCK'
GROUP BY i.id, i.product_id, i.variant_id, i.reorder_level, i.created_at, i.updated_at;
```

**Option B: Remove Quantity Column**
- Calculate on-the-fly: `COUNT(InventoryItems WHERE status='IN_STOCK')`
- Add index: `(product_id, variant_id, status)` for performance
- Requires code refactoring

## Testing Checklist

- [ ] Run sync fix script (dry-run) to identify discrepancies
- [ ] Apply triggers to database
- [ ] Test GRN creation - verify quantity auto-updates
- [ ] Test Production - verify quantity auto-updates  
- [ ] Test Sales (marking items SOLD) - verify quantity auto-decrements
- [ ] Test Returns - verify quantity auto-increments
- [ ] Test concurrent operations - verify ACID compliance
- [ ] Run sync fix script again to verify no new discrepancies

## Migration Steps

1. **Backup database:**
   ```bash
   mysqldump -u root -p xyz_pos_db > backup_before_inventory_fix.sql
   ```

2. **Fix existing data:**
   ```bash
   node server/scripts/fix-inventory-sync.js --apply
   ```

3. **Apply triggers:**
   ```bash
   mysql -h localhost -u root -p xyz_pos_db < server/scripts/migrations/005_create_inventory_sync_triggers.sql
   ```

4. **Verify triggers work:**
   ```sql
   -- Create test item
   INSERT INTO inventory_items (uid, product_id, variant_id, status, source)
   VALUES ('TEST001', 3, NULL, 'IN_STOCK', 'GRN');
   
   -- Check quantity increased
   SELECT quantity FROM inventories WHERE product_id = 3 AND variant_id IS NULL;
   
   -- Update status to SOLD
   UPDATE inventory_items SET status = 'SOLD' WHERE uid = 'TEST001';
   
   -- Check quantity decreased
   SELECT quantity FROM inventories WHERE product_id = 3 AND variant_id IS NULL;
   
   -- Cleanup
   DELETE FROM inventory_items WHERE uid = 'TEST001';
   ```

5. **Update code** (Phase 3) to remove manual quantity updates

6. **Monitor** for any discrepancies over next few days

## Rollback Plan

If issues occur:

1. **Drop triggers:**
   ```sql
   DROP TRIGGER IF EXISTS trg_inventory_item_insert;
   DROP TRIGGER IF EXISTS trg_inventory_item_update;
   DROP TRIGGER IF EXISTS trg_inventory_item_delete;
   ```

2. **Restore backup:**
   ```bash
   mysql -u root -p xyz_pos_db < backup_before_inventory_fix.sql
   ```

## Expected Results

After implementation:
- ✅ Inventory.quantity automatically stays in sync with InventoryItems
- ✅ ACID compliance enforced at database level
- ✅ No manual quantity updates required
- ✅ Reduced risk of data discrepancies
- ✅ Still maintains audit trail via InventoryMovements

## Future Improvements

1. Consider removing `quantity` column entirely (compute on-the-fly)
2. Add CHECK constraints (MySQL 8.0.16+):
   ```sql
   ALTER TABLE inventories ADD CONSTRAINT chk_quantity_sync
   CHECK (quantity = (SELECT COUNT(*) FROM inventory_items 
                     WHERE inventory_items.product_id = inventories.product_id
                     AND inventory_items.variant_id = inventories.variant_id
                     AND inventory_items.status = 'IN_STOCK'));
   ```
3. Add monitoring/alerting for discrepancies
4. Consider event sourcing for inventory changes

