# Google Sheets Sync Investigation - Multi-Seller Issue

**Date:** 2025-11-20
**Issue:** Only 1 seller's orders syncing to their own Google Sheet, other 3 sellers not syncing
**Environment:** Live server (production)

---

## 🎯 Problem Statement

**What SHOULD happen:**
- Seller 1 creates order via `/checkout` → Syncs to Seller 1's own Google Sheet ✓
- Seller 2 creates order via `/checkout` → Syncs to Seller 2's own Google Sheet ✗
- Seller 3 creates order via `/checkout` → Syncs to Seller 3's own Google Sheet ✗
- Seller 4 creates order via `/checkout` → Syncs to Seller 4's own Google Sheet ✗

**Current Reality:**
- All 4 sellers have Google Sheets connected (headers appear, sheet IDs exist)
- Only 1 seller's orders sync successfully
- Other 3 sellers' orders create in database but DON'T sync to their sheets

---

## ✅ Step 1: Run Diagnostic Command on LIVE Server

```bash
# SSH to live server
cd /path/to/multistore

# Run diagnostic
php artisan googlesheets:diagnose
```

**What to look for:**
- Are all 4 sellers showing `Sync Enabled: ✓`?
- Do all 4 have `Has Sheet ID: ✓`?
- Do all 4 have `Access Token: ✓` and `Refresh Token: ✓`?
- **Are any tokens EXPIRED?** 🚨

**Expected Output:**
```
+----+-----------+--------------+--------------+--------------+
| ID | Name      | Sync Enabled | Has Sheet ID | Token Status |
+----+-----------+--------------+--------------+--------------+
| 4  | Seller 1  | ✓            | ✓            | ✓ Valid      | ✓ WORKING
| 5  | Seller 2  | ✓            | ✓            | ❌ EXPIRED   | ✗ NOT SYNCING
| 6  | Seller 3  | ✓            | ✓            | ❌ EXPIRED   | ✗ NOT SYNCING
| 7  | Seller 4  | ✓            | ✓            | ❌ EXPIRED   | ✗ NOT SYNCING
+----+-----------+--------------+--------------+--------------+
```

---

## ✅ Step 2: Check Logs for Sync Attempts

```bash
# Monitor logs in real-time
tail -f storage/logs/laravel.log | grep "Google Sheets"

# Or search recent logs
grep "Google Sheets" storage/logs/laravel-$(date +%Y-%m-%d).log | tail -50
```

**What to look for:**

### A) Successful Sync (Seller 1):
```
[2025-11-20 10:00:00] Google Sheets sync job queued {"order_id":233,"seller_id":4,"seller_name":"Seller 1"}
[2025-11-20 10:00:05] Order synced to Google Sheets {"order_id":233,"seller_id":4}
```

### B) Failed Sync (Sellers 2-4) - Possible Errors:

**Token Expired:**
```
[2025-11-20 10:00:00] Google Sheets sync job queued {"seller_id":5,"seller_name":"Seller 2"}
[2025-11-20 10:00:05] Failed to authenticate with Google. Please reconnect your account.
[2025-11-20 10:00:05] Google Sheets sync job failed {"user_id":5,"error":"Refresh token not found"}
```

**Rate Limit:**
```
[2025-11-20 10:00:00] Google Sheets rate limit hit {"seller_id":5,"status_code":429}
```

**Authentication Error:**
```
[2025-11-20 10:00:00] Google Sheets authentication error {"user_id":5,"error":"invalid_grant"}
```

---

## ✅ Step 3: Check Queue Status

```bash
# Check if queue worker is running
ps aux | grep "queue:work"

# Check queue table for failed jobs
php artisan tinker
>>> DB::table('failed_jobs')->where('payload', 'like', '%SyncOrderToGoogleSheets%')->count();
>>> DB::table('failed_jobs')->where('payload', 'like', '%SyncOrderToGoogleSheets%')->orderBy('failed_at', 'desc')->limit(5)->get();
```

**What to look for:**
- If queue worker is NOT running → Jobs queued but never executed
- If failed_jobs has many entries → Check error messages

---

## ✅ Step 4: Test Each Seller Manually

**For each seller (2, 3, 4):**

1. **Login as seller** (or impersonate)
2. **Go to:** `/settings/google-sheets`
3. **Check status:**
   - Does it show "Connected" with their Google email?
   - Is "Last synced" timestamp recent or old?
4. **Click:** "Manual Sync Now"
5. **Watch logs immediately:**
   ```bash
   tail -f storage/logs/laravel.log | grep "seller_id\":5"
   ```
6. **Check their Google Sheet** - Did orders appear?

---

## ✅ Step 5: Database Check

```bash
php artisan tinker

# Check sellers' Google Sheets configuration
>>> User::whereIn('id', [4,5,6,7])->get(['id','name','google_sheets_sync_enabled','google_sheet_id','google_email','google_token_expires_at']);

# Sample seller 2
>>> $seller = User::find(5);
>>> $seller->google_sheets_sync_enabled;  // Should be 1 (true)
>>> $seller->google_sheet_id;             // Should be a long string
>>> $seller->google_access_token ? 'Yes' : 'No';  // Should be 'Yes'
>>> $seller->google_refresh_token ? 'Yes' : 'No'; // Should be 'Yes'
>>> $seller->google_token_expires_at;     // Should be future date, not past!
```

---

## 🐛 Common Issues & Solutions

### Issue 1: Expired Google Tokens

**Symptoms:**
- Seller connected successfully before
- Sheet has headers but no new orders
- Logs show: "Refresh token not found" or "invalid_grant"

**Solution:**
```bash
# Seller needs to RECONNECT their Google account
# As the seller:
1. Go to /settings/google-sheets
2. Click "Disconnect Google Account"
3. Click "Connect Google Account" again
4. Authorize with same Gmail account
```

---

### Issue 2: Queue Worker Not Running

**Symptoms:**
- Jobs queued but never execute
- Google Sheets never update
- Logs show "sync job queued" but no "Order synced" messages

**Solution:**
```bash
# Check if queue worker is running
supervisorctl status  # If using Supervisor
# OR
systemctl status horizon  # If using Laravel Horizon

# Restart queue worker
php artisan queue:restart
# OR
supervisorctl restart all
```

---

### Issue 3: Rate Limiting (Less Likely)

**Symptoms:**
- First seller syncs fine
- Subsequent sellers fail
- Logs show: "rate limit hit" or "429 error"

**Solution:**
- Rate limiting is already implemented with backoff
- Check if too many orders created too quickly
- Wait a few minutes and try again

---

### Issue 4: Wrong Sheet ID

**Symptoms:**
- Seller connected but orders going to wrong sheet
- Or getting "spreadsheet not found" errors

**Solution:**
```bash
php artisan tinker

# Check seller's sheet ID
>>> $seller = User::find(5);
>>> $seller->google_sheet_id;

# Verify it matches the URL of their Google Sheet
# Sheet URL format: https://docs.google.com/spreadsheets/d/{SHEET_ID}/edit
```

---

## 📊 Expected Behavior Flow

When Seller 2 creates an order:

```
1. Order created in database
   ✓ order_id: 234
   ✓ created_by: 5 (Seller 2's ID)
   ✓ order_number: CK-ALI-000001

2. OrderObserver->created() triggered
   ✓ Calls syncToGoogleSheets()
   ✓ Finds seller ID 5 from created_by
   ✓ Checks: seller->google_sheets_sync_enabled = true
   ✓ Checks: seller->google_sheet_id exists
   ✓ Dispatches: SyncOrderToGoogleSheets::dispatch($seller, $order)

3. Job queued
   ✓ Log: "Google Sheets sync job queued" {seller_id: 5}

4. Queue worker picks up job
   ✓ SyncOrderToGoogleSheets->handle()
   ✓ Authenticates with seller's Google tokens
   ✓ Calls GoogleSheetsService->syncOrder()

5. Sync to Seller 2's Google Sheet
   ✓ Appends order row to Seller 2's sheet
   ✓ Log: "Order synced to Google Sheets" {order_id: 234, seller_id: 5}
```

**If any step fails, check logs for that specific step!**

---

## 🔧 Quick Fix Commands

### Reconnect All Sellers' Google Sheets

**Don't run this! Manual reconnect is safer.**

But if you need to force disconnect all sellers (for testing):

```bash
php artisan tinker

# Disconnect all sellers (they'll need to reconnect)
>>> User::where('role', 'seller')->update([
    'google_sheets_sync_enabled' => false,
    'google_access_token' => null,
    'google_refresh_token' => null,
    'google_token_expires_at' => null
]);
```

### Check Last Sync Times

```bash
php artisan tinker

# Check when each seller last synced
>>> User::where('role', 'seller')
    ->where('google_sheets_sync_enabled', true)
    ->get(['id', 'name', 'google_sheets_last_sync'])
    ->map(function($u) {
        return [
            'id' => $u->id,
            'name' => $u->name,
            'last_sync' => $u->google_sheets_last_sync ? $u->google_sheets_last_sync->diffForHumans() : 'Never'
        ];
    });
```

---

## 📝 Report Template

After running diagnostics, please provide:

```
## Seller 1 (WORKING):
- ID: 4
- Name: Seller 1
- Google Email: seller1@gmail.com
- Sync Enabled: Yes
- Sheet ID: 1PgE...
- Token Status: Valid
- Last Sync: 5 minutes ago
- Test Order Created: Yes
- Synced to Sheet: Yes ✓

## Seller 2 (NOT WORKING):
- ID: 5
- Name: Seller 2
- Google Email: seller2@gmail.com
- Sync Enabled: Yes/No?
- Sheet ID: Present/Missing?
- Token Status: Valid/Expired?
- Last Sync: ?
- Test Order Created: Yes
- Synced to Sheet: No ✗
- Error in Logs: [paste error here]

## Seller 3 (NOT WORKING):
[Same format]

## Seller 4 (NOT WORKING):
[Same format]
```

---

## 🎯 Most Likely Root Cause

Based on the pattern (1 working, 3 not working), the most likely causes are:

1. **Expired Tokens** (80% probability)
   - Sellers 2-4 connected at same time, tokens expired together
   - Seller 1 reconnected more recently

2. **Queue Worker Issue** (15% probability)
   - Jobs dispatched but not executed
   - Or executed but failing silently

3. **Wrong Sheet IDs** (5% probability)
   - Sellers connected but sheet IDs incorrect in database

---

**Next Action:** Run diagnostics on LIVE server and report back with the results!

