# WooCommerce Integration Restoration - Implementation Plan

**Parent Issue:** #7
**Status:** Ready for Implementation
**Estimated Time:** 58 hours (7-10 business days)
**Full Documentation:** See `WOOCOMMERCE_RESTORATION_PLAN.md` in repository

---

## Executive Summary

The WooCommerce integration has a solid foundation with OAuth authentication and API client implemented, but lacks the complete data synchronization features that exist for ShopRenter. This plan outlines the steps to restore full WooCommerce functionality by implementing missing Edge Functions for product, order, and customer synchronization.

---

## Current State

### ✅ What's Working
- OAuth 1.0a authentication flow (`oauth-woocommerce`)
- API client with HMAC-SHA256 signatures (`_shared/woocommerce-client.ts`)
- Frontend connection wizard (`WooCommerceConnect.tsx`)

### ❌ What's Missing
- Data synchronization Edge Functions (products, orders, customers)
- Database cache tables for WooCommerce data
- Scheduled background sync (pg_cron integration)
- Frontend data display components
- Webhook support for real-time updates

---

## Implementation Phases

### Phase 1: Edge Functions for Data Sync (18 hours) - HIGH PRIORITY

**Create 4 Edge Functions:**

1. **`woocommerce-products`** (4 hours)
   - Fetch products from WooCommerce API
   - Cache in `woocommerce_products_cache` table
   - Support pagination for large catalogs
   - Return product list to frontend

2. **`woocommerce-orders`** (4 hours)
   - Fetch orders with status filtering
   - Cache in `woocommerce_orders_cache` table
   - Support date range queries
   - Return order list to frontend

3. **`woocommerce-customers`** (4 hours)
   - Fetch customer data
   - Cache in `woocommerce_customers_cache` table
   - Support search and filtering
   - Return customer list to frontend

4. **`woocommerce-sync`** (6 hours)
   - Manual sync trigger endpoint
   - Sync all data types in parallel
   - Update `last_synced_at` timestamp
   - Return comprehensive sync summary

---

### Phase 2: Database Tables (3 hours) - HIGH PRIORITY

**Create Migration:** `20250130_woocommerce_cache_tables.sql`

**Tables to Create:**

1. **`woocommerce_products_cache`**
   - Product details (name, SKU, price, description)
   - Stock information
   - Categories and images (JSONB)
   - Last sync timestamp

2. **`woocommerce_orders_cache`**
   - Order details (number, status, total)
   - Customer information
   - Line items (JSONB)
   - Billing information
   - Last sync timestamp

3. **`woocommerce_customers_cache`**
   - Customer contact details
   - Billing information (JSONB)
   - Last sync timestamp

4. **Sync Tracking Columns**
   - Add to `stores` table: `last_sync_at`, `sync_status`, `sync_error`

---

### Phase 3: Scheduled Background Sync (8 hours) - MEDIUM PRIORITY

1. **`woocommerce-scheduled-sync` Edge Function** (6 hours)
   - Triggered by pg_cron every hour
   - Fetch all enabled WooCommerce stores
   - Sync each store's data
   - Log results to `sync_logs` table
   - Handle rate limiting

2. **Database Configuration** (2 hours)
   - Update `store_sync_config` for WooCommerce
   - Create pg_cron job
   - Configure environment variables

---

### Phase 4: Frontend Integration (8 hours) - MEDIUM PRIORITY

1. **Update IntegrationsContent.tsx** (2 hours)
   - Add "Sync Now" button
   - Display last sync time
   - Show sync status indicators

2. **Create Data Display Components** (6 hours)
   - `WooCommerceProducts.tsx` - Product list
   - `WooCommerceOrders.tsx` - Order list
   - `WooCommerceCustomers.tsx` - Customer list
   - Integrate with Dashboard and Analytics

---

### Phase 5: Webhook Support (10 hours) - LOW PRIORITY

1. **`webhook-woocommerce` Edge Function** (6 hours)
   - Receive webhooks from WooCommerce
   - Verify webhook signatures
   - Update cache in real-time
   - Support: order.created, order.updated, product.updated

2. **Auto-register Webhooks** (4 hours)
   - Register webhooks after OAuth
   - Store webhook IDs in `woocommerce_webhooks` table
   - Handle webhook lifecycle

---

## Testing Strategy (8 hours)

### Unit Tests
- [ ] OAuth signature generation
- [ ] API client error handling
- [ ] Rate limiting logic
- [ ] Product sync with pagination
- [ ] Order filtering by status
- [ ] Customer data mapping

### Integration Tests
- [ ] End-to-end OAuth flow
- [ ] Complete sync cycle
- [ ] Scheduled sync execution
- [ ] Webhook processing
- [ ] Frontend data display

### Manual Testing
- [ ] Connect test WooCommerce store
- [ ] Trigger manual sync
- [ ] Verify cached data
- [ ] Test with 100+ products
- [ ] Test edge cases (0 products, rate limits)
- [ ] Verify disconnect/reconnect flow

---

## Deployment Checklist

### Pre-Deployment
- [ ] Create staging environment
- [ ] Deploy to staging
- [ ] Run full test suite
- [ ] Monitor Edge Function logs
- [ ] Verify pg_cron configuration

### Deployment
- [ ] Run database migration
- [ ] Deploy Edge Functions
- [ ] Set environment variables
- [ ] Configure database settings
- [ ] Deploy frontend changes

### Post-Deployment
- [ ] Monitor logs for errors
- [ ] Check sync_logs table
- [ ] Verify pg_cron execution
- [ ] Test with real stores
- [ ] Set up failure alerts

---

## Success Criteria

**Functional:**
- ✅ Users can connect WooCommerce stores
- ✅ Products synced and cached
- ✅ Orders synced with filtering
- ✅ Customers synced
- ✅ Manual sync works
- ✅ Scheduled sync runs hourly
- ✅ Frontend displays data
- ✅ Webhooks update in real-time

**Non-Functional:**
- ⚡ Sync completes in < 30 seconds for 1000 products
- 🔒 Credentials encrypted
- 📊 Sync success rate > 95%
- 🚫 Rate limiting prevents bans
- 📝 Comprehensive logging

---

## Risk Assessment

### Technical Risks

1. **Rate Limiting** (Medium)
   - Mitigation: Exponential backoff, aggressive caching

2. **Large Store Performance** (Medium)
   - Mitigation: Batch processing, background jobs

3. **OAuth Token Expiration** (Low)
   - Mitigation: Test before sync, handle 401 errors

4. **Webhook Reliability** (Low)
   - Mitigation: Idempotent handling, signature verification

---

## Rollback Plan

If issues occur:
1. Disable scheduled sync: `SELECT cron.unschedule('woocommerce-sync-hourly');`
2. Hide manual sync button in frontend
3. Rollback database migration: `supabase migration down`
4. Rollback Edge Functions to previous version

---

## Documentation Requirements

### Developer Docs
- [ ] API documentation for Edge Functions
- [ ] Database schema documentation
- [ ] Webhook setup guide
- [ ] Troubleshooting guide

### User Docs
- [ ] Connection setup guide
- [ ] Manual sync instructions
- [ ] Sync status interpretation
- [ ] FAQ

---

## Acceptance Criteria

Implementation complete when:

1. ✅ All Edge Functions deployed
2. ✅ Database migrations applied
3. ✅ Manual sync works
4. ✅ Scheduled sync runs automatically
5. ✅ Sync logs show execution history
6. ✅ Frontend displays data
7. ✅ All tests pass
8. ✅ Documentation complete
9. ✅ 3+ real stores tested
10. ✅ Code reviewed and approved

---

## References

- **Existing Implementation:** ShopRenter integration (`supabase/functions/shoprenter-*`)
- **Full Plan:** `WOOCOMMERCE_RESTORATION_PLAN.md` in repository
- **WooCommerce API:** https://woocommerce.github.io/woocommerce-rest-api-docs/
- **OAuth 1.0a:** https://oauth.net/core/1.0a/
- **Supabase Functions:** https://supabase.com/docs/guides/functions
- **pg_cron:** https://github.com/citusdata/pg_cron

---

## Next Steps

1. Review and approve this plan
2. Create subtasks for each phase
3. Assign ownership
4. Set up staging environment
5. Begin Phase 1 implementation

---

**Created:** 2025-01-30
**Parent Issue:** #7
**Full Documentation:** WOOCOMMERCE_RESTORATION_PLAN.md
