# Google Sheets Integration - Change History

This document tracks all updates and fixes made to the Google Sheets integration feature.

## 2025-11-21 - Column Rearrangement & Notes Field

### Changes

**Column Order Updated:**
- Global Order ID moved from Column B to Column A (now first column)
- Order Number moved from Column A to Column B (now second column)
- All other columns remain in same order
- Added new Column T for customer notes

**Why this change?**
Global Order ID is the unique identifier across all stores, so it makes more sense as the first column. Order numbers can be duplicated across different stores, but Global Order IDs are always unique system-wide.

### New Feature: Customer Notes

Orders now include a "Notes" column (Column T) that shows customer comments from the checkout form.

**What's included:**
- Customer notes entered during checkout
- Notes from sales page orders
- Internal notes added manually
- Empty if customer didn't enter notes

**Files Modified:**
- `app/Services/GoogleSheetsService.php` - Updated column order and added notes field
- `resources/views/orders/show.blade.php` - Added customer notes display section

### Migration for Existing Users

**For new sellers:**
- New sheets created with correct column order automatically
- 20 columns (A-T) from the start

**For existing sellers:**
- Old sheets still have old column order
- Recommended: Disconnect and reconnect to get new sheet with correct columns
- Alternative: Manually rearrange columns A & B and add Notes as Column T

---

## 2025-11-21 - Fixed Duplicate Detection

### Problem

The system was using `order_number` to check for duplicates. This caused issues because:
- Order numbers can be the same across different stores
- Example: Store A order #5 and Store B order #5 would conflict
- Updates would overwrite wrong orders

### Solution

Changed duplicate detection to use `global_order_id` instead:
- Global Order IDs are unique across the entire system
- Format: `OMS00000001`, `OMS00000002`, etc.
- Each order has only one global ID, ever
- No conflicts between stores

### Technical Details

**Changed in `findOrderRow()` method:**
```php
// Before
$range = 'Sheet1!B:B'; // Order number column

// After
$range = 'Sheet1!A:A'; // Global Order ID column
```

The method now searches Column A for the Global Order ID to find existing rows.

---

## 2025-11-20 - Multi-Seller Isolation Fix

### Problem

Initial implementation didn't properly isolate sellers' data. There was a risk of:
- Orders syncing to wrong seller's sheet
- Cross-contamination of data
- Privacy concerns

### Solution

Implemented proper seller isolation in `OrderObserver.php`:

**For manual/checkout orders:**
- Uses `created_by` field to identify the seller
- Syncs to that specific seller's sheet only

**For WooCommerce/webhook orders:**
- Uses store relationship to find the seller
- Only syncs to the store owner's sheet

**Result:**
- Each seller's orders only sync to their own sheet
- Complete data privacy
- No cross-contamination

### Verification

Each seller's data is stored separately:
- Separate Google account per seller
- Separate OAuth tokens (encrypted)
- Separate Sheet ID
- Separate sync settings

---

## 2025-11-20 - Queue Worker Setup Documentation

### Added

Created comprehensive documentation for setting up queue workers:

**Production (Supervisor):**
- Auto-start on server boot
- Auto-restart on crash
- Process monitoring
- Log management

**Development:**
- Simple background process
- Manual start/stop
- Good for testing

**Performance Impact:**
- Idle: ~80MB RAM, <1% CPU
- Active: ~150MB RAM, 5-20% CPU during sync
- Negligible impact on server

**Key File:** `docs/QUEUE_WORKER_SETUP.md`

---

## 2025-11-19 - Initial Implementation

### Features Implemented

1. **OAuth Integration**
   - Google Cloud Console setup
   - OAuth 2.0 authentication flow
   - Token storage (encrypted)
   - Automatic token refresh

2. **Sheet Creation**
   - Automatic spreadsheet creation on first connect
   - Pre-formatted header row (blue background, white text, bold)
   - Frozen header row
   - 20 column structure

3. **Auto-Sync**
   - Background syncing via Laravel queues
   - Triggered on order create/update
   - Rate limiting (1.2s delay between requests)
   - Automatic retry on failure

4. **Manual Sync**
   - "Sync Last 10 Orders" button
   - Useful for initial setup or testing
   - Batch processing with delays

5. **Settings UI**
   - Connect/disconnect Google account
   - View connection status
   - Manual sync controls
   - Open sheet directly in Google

6. **Data Synced**
   - Order identification (Global ID, Order Number)
   - Customer details (name, phone, email)
   - Address information (full breakdown)
   - Product details (names, SKUs, quantities)
   - Order totals and currency
   - Payment method
   - Order status
   - Tracking information
   - Store name

### Database Schema

Added to `users` table:
- `google_sheet_id` - Spreadsheet ID
- `google_access_token` - OAuth access token (encrypted)
- `google_refresh_token` - OAuth refresh token (encrypted)
- `google_token_expires_at` - Token expiration timestamp
- `google_sheets_sync_enabled` - Enable/disable toggle
- `google_sheets_last_sync` - Last successful sync time
- `google_email` - Connected Gmail address

### Rate Limiting

Implemented to comply with Google Sheets API limits:
- 60 requests per minute per user
- 1.2 second delay between sync requests
- Automatic retry with exponential backoff (2^attempt seconds)
- Maximum 3 retry attempts

### Error Handling

Comprehensive error handling:
- Token expiration detection and auto-refresh
- API error logging
- Failed job tracking
- User-friendly error messages

---

## Known Limitations

1. **Google API Rate Limits**
   - 60 requests/minute per user
   - May slow down during bulk syncs

2. **Batch Sync Size**
   - Recommended maximum: 50 orders at a time
   - Larger batches may hit rate limits

3. **Real-time Sync Delay**
   - 3-5 second delay due to queue system
   - Not instantaneous (by design)

4. **Sheet Complexity**
   - Very large sheets (10,000+ rows) may slow down
   - Google Sheets has 10 million cell limit per sheet

---

## Upgrade Notes

### When Updating From Old Version

1. **Pull latest code:**
   ```bash
   git pull origin main
   ```

2. **Install dependencies:**
   ```bash
   composer install
   ```

3. **Run migrations:**
   ```bash
   php artisan migrate
   ```

4. **Clear caches:**
   ```bash
   php artisan config:clear
   php artisan cache:clear
   php artisan view:clear
   ```

5. **Restart queue workers:**
   ```bash
   # With Supervisor
   sudo supervisorctl restart laravel-worker:*

   # Without Supervisor
   killall php
   php artisan queue:work database --sleep=3 --tries=3 &
   ```

6. **For existing sellers with active sheets:**
   - Option A: Keep using old sheet (may have old column order)
   - Option B: Disconnect and reconnect for fresh sheet with new structure

---

## Future Enhancements (Planned)

- Import orders from Google Sheets (disaster recovery)
- Export to CSV directly from settings
- Custom column selection
- Multiple sheet support per seller
- Scheduled batch syncs
- Sync status dashboard

---

## Support

For issues or questions:
- Check [Troubleshooting Guide](troubleshooting.md)
- Review [Features Documentation](features.md)
- Check Laravel logs: `storage/logs/laravel.log`

---

**Last updated:** 2025-11-21
