# Google Sheets Sync - Global Order ID Fix

**Date:** 2025-11-21
**Issue:** Duplicate detection using wrong column
**Fix:** Use `global_order_id` instead of `order_number`

---

## 🐛 The Problem

The Google Sheets sync was checking for duplicate orders using **Column A (order_number)**, which can have duplicates:

**Why order_number can be duplicate:**
- Different WooCommerce stores can have the same order number
- Store A: Order #1038
- Store B: Order #1038
- Both would be treated as duplicates ❌

**Example:**
```
Order 1: Store Dara, #10558 → Syncs to sheet
Order 2: Store Sara, #10558 → Wrongly treats as duplicate, updates instead of creating new row ❌
```

---

## ✅ The Solution

Use **Column B (global_order_id)** for duplicate detection instead:

**Why global_order_id is unique:**
- Generated per seller with prefix: `CK-{SELLER}`, `SP-{SELLER}`, `WH-`, etc.
- Always unique across all stores and sellers
- Example: `CK-SIT-000003`, `SP-SIT-000042`, `WH-000123`

**New behavior:**
```
Order 1: Store Dara, #10558, Global: MT00163353 → Syncs to sheet
Order 2: Store Sara, #10558, Global: MT00163354 → Creates new row ✓
```

---

## 📊 Google Sheet Column Structure

| Column | Field | Unique? | Used for Duplicate Check? |
|--------|-------|---------|---------------------------|
| **A** | order_number | ❌ No | ~~Before~~ |
| **B** | global_order_id | ✅ Yes | **Now ✓** |
| C | date_created | ❌ No | No |
| D | customer_name | ❌ No | No |
| E | phone | ❌ No | No |
| ... | ... | ... | ... |

---

## 🔧 What Was Changed

### File: `app/Services/GoogleSheetsService.php`

### Change 1: Updated findOrderRow() method

**Before:**
```php
protected function findOrderRow(User $user, $orderNumber)
{
    $range = 'Sheet1!A:A'; // Order number column ❌
    // ... checks column A (order_number)
    if ($row[0] === $orderNumber) {
        return $index + 1;
    }
}
```

**After:**
```php
protected function findOrderRow(User $user, $globalOrderId)
{
    $range = 'Sheet1!B:B'; // Global Order ID column ✅
    // ... checks column B (global_order_id)
    if ($row[0] === $globalOrderId) {
        return $index + 1;
    }
}
```

### Change 2: Updated syncOrder() to pass global_order_id

**Before:**
```php
$existingRow = $this->findOrderRow($user, $order->order_number);
```

**After:**
```php
$existingRow = $this->findOrderRow($user, $order->global_order_id);
```

---

## ❓ FAQ: About Timestamps

**Q: What if 2 orders are created at the exact same time?**

**A:** Timestamps include **microseconds**, making them unique:

```
Order 1: 2025-11-21 11:30:45.123456
Order 2: 2025-11-21 11:30:45.789012
                            ^^^^^^ <- microseconds differ
```

Even if created "simultaneously", the database assigns sequential timestamps with different microseconds. **However**, we don't use timestamps for duplicate detection because:
- ❌ Timestamps can theoretically collide
- ❌ Orders might be synced out of order
- ✅ `global_order_id` is guaranteed unique by database constraint

---

## 🧪 Testing

### Before Fix (Wrong Behavior):
1. Create order in Store A: #1038 → Syncs to sheet ✓
2. Create order in Store B: #1038 → **Updates** Store A's row ❌
3. Result: Store B's order overwrites Store A's order

### After Fix (Correct Behavior):
1. Create order in Store A: #1038, Global: MT00001 → Syncs to sheet ✓
2. Create order in Store B: #1038, Global: MT00002 → **Creates new row** ✓
3. Result: Both orders exist separately in sheet

---

## 🚀 Deployment

### Files to Upload:
```
app/Services/GoogleSheetsService.php
```

### Steps:
1. Upload the file to live server
2. No database changes needed
3. No cache clearing needed
4. Test by creating a new order

### Verification:
1. Create a test order
2. Check Google Sheet - it should appear
3. Update the same order (change customer name)
4. Check Google Sheet - it should **update** the existing row, not create duplicate
5. Create another order
6. Check Google Sheet - it should create a **new row**

---

## 🎯 Impact

### What This Fixes:
- ✅ Prevents duplicate orders from different stores from overwriting each other
- ✅ Correctly updates existing orders when they're modified
- ✅ Uses the proper unique identifier (global_order_id)

### What's Not Affected:
- ✅ Real-time sync still works
- ✅ Manual sync still works
- ✅ All other Google Sheets functionality unchanged
- ✅ No data loss (old data remains in sheets)

---

## 📝 Database Schema Reference

For reference, here's the uniqueness constraint:

```sql
-- orders table
global_order_id VARCHAR(255) UNIQUE NOT NULL
order_number VARCHAR(255) NOT NULL  -- Can be duplicate!
```

**Why we can't make order_number unique:**
- Multiple WooCommerce stores send orders with same numbers
- We preserve the original order number from external systems
- `global_order_id` provides the uniqueness we need

---

**Status:** ✅ Fixed and ready for deployment
