# Quick Fix Guide - Inventory ACID Compliance

## Problem Summary

Your inventory system has **ACID violations** and **normalization issues**:

1. **ACID Violations:** `Inventory.quantity` is manually maintained, not enforced. Transactions can fail partway, causing sync issues.
2. **Normalization:** `Inventory.quantity` is redundant computed data (violates 3NF). Should be calculated, not stored.

## Current Status Check

For **Summer Dress (Product ID 3)**:
- Base product: 120 quantity = 120 IN_STOCK items ✅ Currently matches
- Variant 1: 100 quantity = 100 IN_STOCK items ✅ Currently matches

**However**, there's no enforcement mechanism, so discrepancies can occur.

## Immediate Fix (Apply Now)

### Step 1: Backup Database
```bash
mysqldump -h localhost -u root -p'Kennedy123!' xyz_pos_db > backup_$(date +%Y%m%d_%H%M%S).sql
```

### Step 2: Fix Existing Discrepancies
```bash
cd /home/kennedy/Desktop/Project/xyz_pos
node server/scripts/fix-inventory-sync.js --apply
```

### Step 3: Apply Database Triggers (Enforces ACID)
```bash
mysql -h localhost -u root -p'Kennedy123!' xyz_pos_db < server/scripts/migrations/005_create_inventory_sync_triggers.sql
```

### Step 4: Verify Triggers Work
```sql
-- Connect to MySQL
mysql -h localhost -u root -p'Kennedy123!' xyz_pos_db

-- Check triggers exist
SHOW TRIGGERS LIKE 'trg_inventory%';

-- Test trigger (creates test item)
INSERT INTO inventory_items (uid, product_id, variant_id, status, source, created_at, updated_at)
VALUES ('TEST001', 3, NULL, 'IN_STOCK', 'GRN', NOW(), NOW());

-- Verify quantity increased
SELECT quantity FROM inventories WHERE product_id = 3 AND variant_id IS NULL;

-- Test status change (mark as SOLD)
UPDATE inventory_items SET status = 'SOLD' WHERE uid = 'TEST001';

-- Verify quantity decreased
SELECT quantity FROM inventories WHERE product_id = 3 AND variant_id IS NULL;

-- Cleanup
DELETE FROM inventory_items WHERE uid = 'TEST001';
```

## What the Fixes Do

### 1. Sync Fix Script
- Identifies all discrepancies
- Recalculates `Inventory.quantity` from actual `InventoryItem` counts
- Can run in dry-run mode to check first

### 2. Database Triggers
- **Automatic sync:** When `InventoryItem` is created/updated/deleted, `Inventory.quantity` auto-updates
- **Status changes:** IN_STOCK ↔ SOLD/RESERVED automatically updates quantity
- **ACID compliance:** Enforced at database level, not application level

### 3. Code Changes (Next Phase)
After triggers are working, we should remove manual quantity updates from code:
- `server/modules/procurement/services/grns.js`
- `server/modules/production/services/productionOrders.js`
- `server/modules/sales/services/index.js`

## Monitoring

After applying fixes, monitor for discrepancies:
```bash
# Check for any discrepancies (dry run)
node server/scripts/fix-inventory-sync.js

# Should show no discrepancies if triggers are working
```

## If Issues Occur

**Rollback 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;
```

**Restore backup:**
```bash
mysql -h localhost -u root -p'Kennedy123!' xyz_pos_db < backup_YYYYMMDD_HHMMSS.sql
```

## Long-term Solution

Consider migrating to **computed quantity** (calculate on-the-fly):
- Remove `quantity` column from `inventories` table
- Calculate: `COUNT(InventoryItems WHERE status='IN_STOCK')`
- Better normalization, always accurate
- Requires code refactoring

See `INVENTORY_ACID_FIX_IMPLEMENTATION.md` for details.

## Files Created

1. `server/scripts/fix-inventory-sync.js` - Sync fix utility
2. `server/scripts/migrations/005_create_inventory_sync_triggers.sql` - Database triggers
3. `INVENTORY_SYNC_ISSUES_ANALYSIS.md` - Detailed analysis
4. `INVENTORY_ACID_FIX_IMPLEMENTATION.md` - Full implementation guide
5. `QUICK_FIX_GUIDE.md` - This file

