# Disaster Recovery Procedure

## When to Use This Guide

Use this procedure when:
- ✅ Database corruption occurred
- ✅ You restored from a backup
- ✅ Orders are missing from the database
- ✅ But orders exist in seller's Google Sheets

---

## The Problem: Order ID Conflicts

### Example Scenario:

```
Timeline:
──────────────────────────────────────
Monday 2 AM:
  ✅ Backup taken
  Database: Orders ID 1-100

Monday-Wednesday:
  📝 10 new orders created (ID 101-110)
  ✅ All synced to Google Sheets
  ❌ NOT in Monday's backup

Thursday:
  💥 DATABASE CORRUPTION
  🔄 Restore Monday's backup

After Restore:
  ✅ Database: Orders 1-100
  ❌ Missing: Orders 101-110
  ✅ Google Sheets: Has all 110 orders

Friday (Without Fix):
  📝 New order created
  ❗ Gets ID 101 (auto-increment)
  💥 CONFLICT! ID 101 already used

Friday (With Fix):
  ✓ Import missing orders 101-110 from Google Sheets
  ✓ Fix auto-increment to 200
  ✓ New order gets ID 200
  ✓ NO CONFLICTS!
```

---

## Solution: Smart Import Process

### Key Principles:

1. **Use `order_number` NOT `id`**
   - `order_number` (e.g., CK-SIT-000001) is unique and permanent
   - `id` can conflict after restore

2. **Import with ORIGINAL data**
   - Restore missing orders exactly as they were
   - Preserve customer info, totals, status

3. **Fix auto-increment BEFORE creating new orders**
   - Set MySQL auto-increment higher than all existing IDs
   - Prevents future conflicts

4. **Verify before import**
   - Dry-run mode to preview changes
   - Confirm what will be imported

---

## ⚠️ CRITICAL: Recovery Order Matters!

**YOU MUST FOLLOW THIS EXACT SEQUENCE:**

1. Put site in maintenance mode FIRST
2. Restore database
3. Import missing orders from Google Sheets
4. Fix sequences
5. ONLY THEN bring site back up

**Why?** If you bring site up before importing, new orders will reuse old order numbers and cause conflicts!

---

## Step-by-Step Recovery Procedure

### Step 1: Put Site in Maintenance Mode

```bash
# THIS MUST BE DONE FIRST!
php artisan down --message="System recovery in progress. We'll be back soon!"
```

**CRITICAL:** Site must stay DOWN until Step 5. Do NOT bring it up early!

---

### Step 2: Restore Database from Backup

```bash
# Restore database (site is still DOWN from Step 1)
mysql -u root -p multistore_manager < backup_20251120.sql

# Or if compressed:
gunzip < backup_20251120.sql.gz | mysql -u root -p multistore_manager
```

**Result:** Database is restored to backup state, but missing recent orders.

⚠️ **DO NOT run `php artisan up` yet!** Site must stay in maintenance mode.

---

### Step 3: Check What's Missing

Run dry-run import to see what orders are missing:

```bash
# Site is still DOWN from Step 1
php artisan orders:import-missing 4 --dry-run
```

Replace `4` with the actual seller's user ID.

⚠️ **Site must still be in maintenance mode at this step!**

**Example Output:**

```
=== Import Missing Orders from Google Sheets ===

User: Siti Seller (ID: 4)
Google Sheet ID: 1PgEJhP-8Z9xrjPXIGqjbpF1XfZH7tt95di5M2bORpqc

Found 110 orders in Google Sheets

Orders already in database: 100
Missing orders to import: 10

┌────────────────┬──────────────┬────────────┬─────────────────────┬────────────┐
│ Order Number   │ Customer     │ Total      │ Date                │ Status     │
├────────────────┼──────────────┼────────────┼─────────────────────┼────────────┤
│ CK-SIT-000101  │ Ahmad Ali    │ MYR 150.00 │ 2025-11-18 10:30:00 │ processing │
│ CK-SIT-000102  │ Siti Zainab  │ MYR 200.00 │ 2025-11-18 14:20:00 │ completed  │
│ CK-SIT-000103  │ John Tan     │ MYR 100.00 │ 2025-11-19 09:15:00 │ processing │
│ ...            │ ...          │ ...        │ ...                 │ ...        │
└────────────────┴──────────────┴────────────┴─────────────────────┴────────────┘

--dry-run mode: No changes made.
Run without --dry-run to import these orders.
```

**Review this carefully!**
- ✅ Verify these are the missing orders
- ✅ Check dates match the gap period
- ✅ Confirm customer names and totals look correct

---

### Step 4: Import Missing Orders

If the dry-run looks correct, import for real:

```bash
# Site is still DOWN
php artisan orders:import-missing 4
```

⚠️ **If you see CONFLICTS warning**, read it carefully! It means new orders were created after restore.

You'll be prompted to confirm:

```
Do you want to import these 10 orders? (yes/no) [yes]:
> yes
```

**Example Output:**

```
✓ Imported: CK-SIT-000101
✓ Imported: CK-SIT-000102
✓ Imported: CK-SIT-000103
...

=== Import Complete ===
✓ Successfully imported: 10 orders
```

**Result:** Missing orders are now back in the database!

---

### Step 5: Fix Sequences (CRITICAL!)

This prevents future order number conflicts:

```bash
# Site is still DOWN
php artisan orders:import-missing 4 --fix-auto-increment
```

**Example Output:**

```
=== Fixing Order Sequences ===
✓ Updated sequence for CK-SIT: 113
✓ Updated sequence for SP-SIT: 45
✓ Orders table auto-increment set to: 210

✓ All sequences updated. New orders will not conflict with imported ones.
```

**What this does:**
- Updates order_sequences table for each prefix (CK-, SP-, WH-)
- Sets sequences higher than any order in Google Sheets
- Next new order will use the correct next number (no conflicts!)

**Result:** ✓ Sequences are fixed. Ready to bring site back up!

---

### Step 6: Verify Recovery

Quick verification before bringing site back up:

**Check order count:**

```bash
php artisan tinker
>>> Order::count();
// Should match Google Sheets row count
```

**Check specific missing order:**

```bash
php artisan tinker
>>> Order::where('order_number', 'CK-SIT-000101')->first();
// Should show the order details
```

**Check auto-increment:**

```bash
mysql -u root -p
mysql> SELECT AUTO_INCREMENT FROM information_schema.TABLES
       WHERE TABLE_SCHEMA = 'multistore_manager'
       AND TABLE_NAME = 'orders';
// Should show 210 or higher
```

---

### Step 7: Bring Site Back Up 🎉

Everything is ready! Now bring the site back online:

```bash
php artisan up
```

**Result:** ✓ Site is back online! Recovery complete!

---

### Step 8: Test New Order Creation

Create a test order to verify everything works:

1. Go to checkout/sales page
2. Create a new order
3. Verify order number is correct and unique:

```bash
php artisan tinker
>>> Order::latest()->first();
// order_number should be CK-XXX-000114 (continuing from where Google Sheets left off)
// NOT reusing old numbers
```

**Result:** ✓ New orders work perfectly! No conflicts!

---

## Important Notes

### ⚠️ Data Limitations

**What's Preserved from Google Sheets:**
- ✅ Order number
- ✅ Customer name, phone, email
- ✅ Address details
- ✅ Products, SKU, quantities
- ✅ Order total
- ✅ Status
- ✅ Tracking number
- ✅ Payment method

**What's NOT in Google Sheets (will be lost):**
- ❌ Individual product prices
- ❌ Product variations/options
- ❌ Shipping method details
- ❌ Order notes
- ❌ Payment transaction IDs
- ❌ Internal metadata

**Why:** Google Sheets only stores summary data. Detailed product info is not synced.

**Solution:** Keep better backups! Daily backups prevent losing detailed data.

---

### 🔍 Understanding Order Numbers vs IDs

**Order ID (Primary Key):**
```
id: 101, 102, 103...
- Auto-increment integer
- Can conflict after restore
- Used internally in database
```

**Order Number (Business Identifier):**
```
order_number: CK-SIT-000101, CK-SIT-000102...
- Unique string
- NEVER changes
- Visible to customers
- Used for tracking
```

**Why Order Number is Better:**
- ✅ Stable identifier (doesn't change)
- ✅ Can't conflict (unique constraint)
- ✅ Matches what customers see
- ✅ Survives database restore

---

## Multiple Sellers Recovery

If multiple sellers need recovery:

```bash
# Get all sellers with Google Sheets enabled
php artisan tinker
>>> $sellers = User::where('google_sheets_sync_enabled', true)->get();
>>> foreach($sellers as $seller) {
...   echo "User ID: {$seller->id} - {$seller->name}\n";
... }

# Import for each seller
php artisan orders:import-missing 4 --fix-auto-increment
php artisan orders:import-missing 7 --fix-auto-increment
php artisan orders:import-missing 9 --fix-auto-increment
```

---

## Advanced: Batch Script

Create recovery script for all sellers:

**File:** `recover-all-sellers.sh`

```bash
#!/bin/bash

echo "=== Disaster Recovery: Import All Sellers ==="

# Get seller IDs (adjust based on your setup)
SELLER_IDS="4 7 9 12"

for SELLER_ID in $SELLER_IDS; do
    echo ""
    echo "Processing Seller ID: $SELLER_ID"
    echo "────────────────────────────────────"

    # Dry run first
    php artisan orders:import-missing $SELLER_ID --dry-run

    # Ask for confirmation
    read -p "Import orders for this seller? (y/n) " -n 1 -r
    echo

    if [[ $REPLY =~ ^[Yy]$ ]]; then
        # Import orders
        php artisan orders:import-missing $SELLER_ID --fix-auto-increment
        echo "✓ Completed for Seller ID: $SELLER_ID"
    else
        echo "✗ Skipped Seller ID: $SELLER_ID"
    fi
done

echo ""
echo "=== Recovery Complete ==="
```

Usage:
```bash
chmod +x recover-all-sellers.sh
./recover-all-sellers.sh
```

---

## Troubleshooting

### Issue: "No orders found in Google Sheets"

**Cause:** Seller hasn't connected Google Sheets or sheet is empty.

**Solution:**
```bash
php artisan tinker
>>> $user = User::find(4);
>>> $user->google_sheets_sync_enabled; // Should be true
>>> $user->google_sheet_id; // Should have a value
```

---

### Issue: "Duplicate entry for 'order_number'"

**Cause:** Order already exists in database.

**Solution:** This is expected! The import skips duplicates automatically. Only missing orders are imported.

---

### Issue: "Auto-increment not high enough"

**Symptom:** New orders still conflict with imported orders.

**Solution:**
```bash
# Manually set auto-increment higher
mysql -u root -p

mysql> ALTER TABLE orders AUTO_INCREMENT = 500;
```

Choose a number higher than all existing order IDs.

---

### Issue: "Customer data incomplete in Google Sheets"

**Cause:** Google Sheets only has summary data.

**Solution:**
- Accept that some details will be lost
- Improve backup frequency (hourly instead of daily)
- Consider database replication for critical data

---

## Prevention: Better Backup Strategy

To minimize data loss in future:

### 1. **More Frequent Backups**

Change from daily to **every 6 hours**:

```bash
# Crontab
0 */6 * * * /usr/local/bin/backup-database.sh
```

**Data Loss Risk:** Max 6 hours instead of 24 hours

---

### 2. **Enable Binary Logs**

Already covered in `BACKUP_AND_DISASTER_RECOVERY.md`

Allows point-in-time recovery to the exact minute before corruption.

---

### 3. **Test Restores Monthly**

```bash
# Monthly drill (last Sunday)
0 3 * * 0 [ $(date +\%e) -ge 22 ] && /usr/local/bin/test-restore.sh
```

**test-restore.sh:**
```bash
#!/bin/bash
# Restore to test database
gunzip < /backups/mysql/latest.sql.gz | mysql -u root -p test_multistore

# Verify
mysql -u root -p test_multistore -e "SELECT COUNT(*) FROM orders;"

echo "✓ Test restore successful"
```

---

## Recovery Time Estimate

| Step | Time | Complexity |
|------|------|------------|
| Restore database backup | 5-10 min | Easy |
| Dry-run import check | 1 min | Easy |
| Import missing orders | 5-15 min | Easy |
| Fix auto-increment | 1 min | Easy |
| Verify recovery | 5 min | Easy |
| **Total** | **20-35 min** | **Easy** |

---

## Checklist

After disaster recovery, verify:

- [ ] Database restored from backup
- [ ] Missing orders imported from Google Sheets
- [ ] Auto-increment fixed (set higher than max ID)
- [ ] New test order created successfully
- [ ] Order count matches Google Sheets
- [ ] No duplicate orders
- [ ] Application back online
- [ ] Sellers notified (if needed)
- [ ] Backup system verified working
- [ ] Incident documented

---

## Contact for Help

If recovery fails or you need assistance:

1. Check logs: `storage/logs/laravel.log`
2. Run in verbose mode: `php artisan orders:import-missing 4 --dry-run -vvv`
3. Review Google Sheets data manually
4. Contact system administrator

---

**Last Updated:** 2025-11-20
