# New Issue Template for WooCommerce Restoration

**Use this template to create a new issue when Gogs is available**

---

## Issue Title
```
Implementation Plan: Restore WooCommerce Integration Functionality
```

## Issue Labels
```
enhancement, woocommerce, high-priority, documentation
```

## Issue Body

```markdown
## Overview

This issue contains the detailed step-by-step implementation plan for restoring full WooCommerce integration functionality, as requested in issue #7.

## Summary

The WooCommerce integration currently has OAuth authentication working but lacks the complete data synchronization features that exist for ShopRenter. This plan outlines implementing all missing components to achieve feature parity.

## Current State

### ✅ What's Working
- OAuth 1.0a authentication flow (`oauth-woocommerce` Edge Function)
- API client with HMAC-SHA256 signature generation
- Frontend connection wizard UI
- Store credentials storage in database

### ❌ What's Missing
- Data synchronization Edge Functions (products, orders, customers)
- Database cache tables for WooCommerce data
- Scheduled background sync (automated hourly)
- Manual sync trigger from UI
- Frontend components to display synced data
- Webhook support for real-time updates

## Estimated Effort

**Total Time:** 58 hours
**Calendar Time:** 7-10 business days (1-2 weeks)

## Implementation Phases

### Phase 1: Create Edge Functions for Data Sync (18 hours)

**Deliverables:**
1. `woocommerce-products` Edge Function
   - Fetch products from WooCommerce API with pagination
   - Cache products in `woocommerce_products_cache` table
   - Return product list to frontend
   - Handle authentication and rate limiting

2. `woocommerce-orders` Edge Function
   - Fetch orders with status filtering
   - Cache orders in `woocommerce_orders_cache` table
   - Support pagination for large stores
   - Extract customer and line item data

3. `woocommerce-customers` Edge Function
   - Fetch customer data from WooCommerce
   - Cache in `woocommerce_customers_cache` table
   - Store billing information
   - Handle customer search and filtering

4. `woocommerce-sync` Edge Function (Manual Sync)
   - Accept store_id parameter
   - Sync products, orders, and customers in parallel
   - Update last_synced_at timestamp
   - Return sync summary with counts

**Estimated Time:** 18 hours

---

### Phase 2: Create Database Tables (3 hours)

**Migration File:** `supabase/migrations/20250130_woocommerce_cache_tables.sql`

**Tables to Create:**

1. `woocommerce_products_cache`
   - Stores cached product data from WooCommerce
   - Includes: name, SKU, price, stock, images, categories
   - Indexed by store_id and SKU

2. `woocommerce_orders_cache`
   - Stores cached order data
   - Includes: order number, status, customer info, line items
   - Indexed by store_id, status, and customer_email

3. `woocommerce_customers_cache`
   - Stores cached customer data
   - Includes: name, email, billing info
   - Indexed by store_id and email

**Additional Changes:**
- Add sync tracking columns to `stores` table:
  - `last_sync_at` (timestamp)
  - `sync_status` (text)
  - `sync_error` (text)

**Estimated Time:** 3 hours

---

### Phase 3: Scheduled Sync Implementation (8 hours)

**Deliverables:**
1. `woocommerce-scheduled-sync` Edge Function
   - Triggered by pg_cron scheduler
   - Fetch all WooCommerce stores with sync enabled
   - Sync each store's data sequentially
   - Log results to `sync_logs` table
   - Handle rate limiting across multiple stores
   - Secured with INTERNAL_SYNC_SECRET

2. Database Configuration
   - Create pg_cron job for hourly execution
   - Reuse existing `store_sync_config` table
   - Add WooCommerce stores to sync schedule
   - Configure per-store sync frequency

**Estimated Time:** 8 hours

---

### Phase 4: Frontend Integration (8 hours)

**Deliverables:**
1. Update `IntegrationsContent.tsx`
   - Add "Sync Now" button for manual sync
   - Display last sync time for each store
   - Show sync status indicators
   - Handle sync in progress state

2. Create Data Display Components
   - `WooCommerceProducts.tsx` - Display cached products
   - `WooCommerceOrders.tsx` - Display cached orders
   - `WooCommerceCustomers.tsx` - Display cached customers

3. Dashboard Integration
   - Show WooCommerce activity in dashboard
   - Display sync status on main page
   - Add WooCommerce metrics to analytics

**Estimated Time:** 8 hours

---

### Phase 5: Webhook Support (10 hours) - OPTIONAL

**Deliverables:**
1. `webhook-woocommerce` Edge Function
   - Receive webhooks from WooCommerce stores
   - Verify webhook signatures
   - Update cached data in real-time
   - Support events: order.created, order.updated, product.updated

2. Webhook Registration
   - Auto-register webhooks on store connect
   - Store webhook IDs in `woocommerce_webhooks` table
   - Provide webhook management UI

**Estimated Time:** 10 hours

---

## Testing Requirements

### Unit Tests (2 hours)
- [ ] Test OAuth signature generation
- [ ] Test API client error handling
- [ ] Test rate limiting logic
- [ ] Test product sync with pagination
- [ ] Test order filtering by status
- [ ] Test customer data mapping

### Integration Tests (3 hours)
- [ ] End-to-end OAuth flow
- [ ] Complete sync cycle (products → orders → customers)
- [ ] Scheduled sync execution
- [ ] Webhook reception and processing
- [ ] Frontend data display

### Manual Testing (3 hours)
- [ ] Connect a test WooCommerce store
- [ ] Trigger manual sync
- [ ] Verify data in cache tables
- [ ] Check sync logs for errors
- [ ] Test with stores having 100+ products
- [ ] Test with stores having 0 products
- [ ] Verify rate limiting doesn't cause failures
- [ ] Test disconnect and reconnect flow

**Total Testing Time:** 8 hours

---

## Documentation (3 hours)

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

### User Documentation
- [ ] How to connect WooCommerce store
- [ ] How to trigger manual sync
- [ ] Understanding sync status
- [ ] FAQ for WooCommerce integration

**Total Documentation Time:** 3 hours

---

## Deployment Plan

### Pre-Deployment
1. Create staging environment for testing
2. Deploy to staging and run full test suite
3. Monitor Edge Function logs for errors
4. Verify pg_cron jobs are scheduled correctly

### Deployment Steps

**1. Database Migration**
```bash
supabase db push
# Or apply migration to production
supabase migration up
```

**2. Deploy Edge Functions**
```bash
supabase functions deploy woocommerce-products
supabase functions deploy woocommerce-orders
supabase functions deploy woocommerce-customers
supabase functions deploy woocommerce-sync
supabase functions deploy woocommerce-scheduled-sync
supabase functions deploy webhook-woocommerce  # if Phase 5 included
```

**3. Set Environment Variables**
Ensure these are set in Supabase Dashboard:
- `INTERNAL_SYNC_SECRET`
- `SUPABASE_URL`
- `SUPABASE_ANON_KEY`
- `SUPABASE_SERVICE_ROLE_KEY`

**4. Configure Database Settings**
Add to Custom Postgres Configuration:
```
app.internal_sync_secret = '<secret>'
app.supabase_url = 'https://ztklqodcdjeqpsvhlpud.supabase.co'
```

**5. Deploy Frontend Changes**
```bash
cd shopcall.ai-main
npm run build
# Deploy to hosting provider
```

### Post-Deployment
1. Monitor Edge Function logs
2. Check sync_logs table
3. Verify pg_cron jobs running
4. Test with 2-3 real stores
5. Set up alerts for sync failures

---

## Success Criteria

This implementation is considered complete when:

1. ✅ All Edge Functions are deployed and functional
2. ✅ Database migrations are applied successfully
3. ✅ Manual sync works from frontend UI
4. ✅ Scheduled sync runs automatically every hour
5. ✅ Sync logs show successful execution history
6. ✅ Frontend displays WooCommerce products, orders, customers
7. ✅ All tests pass (unit + integration)
8. ✅ Documentation is complete and published
9. ✅ At least 3 real WooCommerce stores tested successfully
10. ✅ Code reviewed and approved by team

---

## Risk Assessment

### Technical Risks

1. **Rate Limiting** (Medium)
   - WooCommerce API has rate limits
   - Mitigation: Implement exponential backoff, cache aggressively

2. **Large Store Performance** (Medium)
   - Stores with 10,000+ products may timeout
   - Mitigation: Implement batch processing, background jobs

3. **OAuth Token Expiration** (Low)
   - API keys don't expire, but user might revoke
   - Mitigation: Test connection before sync, handle 401 errors

4. **Webhook Reliability** (Low)
   - Webhooks may fail or duplicate
   - Mitigation: Idempotent webhook handling, verify signatures

---

## Dependencies

### External Dependencies
- WooCommerce REST API v3
- Supabase Edge Functions runtime
- PostgreSQL with pg_cron extension
- Deno runtime for Edge Functions

### Internal Dependencies
- `stores` table exists and has WooCommerce stores
- `oauth_states` table for OAuth flow
- `sync_logs` table for logging (reuse existing)
- `store_sync_config` table for per-store settings (reuse existing)

---

## Rollback Plan

If issues arise after deployment:

1. **Disable Scheduled Sync**
   ```sql
   SELECT cron.unschedule('woocommerce-sync-hourly');
   ```

2. **Disable Manual Sync** (frontend)
   - Hide "Sync Now" button temporarily
   - Display maintenance message

3. **Rollback Database Migration**
   ```bash
   supabase migration down
   ```

4. **Rollback Edge Functions**
   - Deploy previous version or delete new functions

---

## Detailed Documentation

The complete implementation plan with full technical specifications is available in the repository:

**File:** `WOOCOMMERCE_RESTORATION_PLAN.md`

**Location:** Repository root

**Contents:**
- 689 lines of detailed specifications
- Complete TypeScript function signatures
- Full database schemas with CREATE TABLE statements
- API endpoint documentation with request/response formats
- Comprehensive testing checklist
- Step-by-step deployment instructions
- Risk assessment and mitigation strategies
- Rollback procedures
- Future enhancement roadmap

---

## References

- **Existing Implementation:** ShopRenter integration (`supabase/functions/shoprenter-*`)
- **WooCommerce API Docs:** https://woocommerce.github.io/woocommerce-rest-api-docs/
- **OAuth 1.0a Spec:** https://oauth.net/core/1.0a/
- **Supabase Edge Functions:** https://supabase.com/docs/guides/functions
- **pg_cron Documentation:** https://github.com/citusdata/pg_cron

---

## Related Issues

- Closes #7 upon completion
- Requested in: #7 (create step-by-step implementation plan)

---

## Next Steps

1. ✅ Review this implementation plan
2. Create subtasks for each phase (optional)
3. Set up staging environment
4. Begin Phase 1 implementation
5. Close issue #7 (plan creation completed)

---

**Created By:** Claude Code Assistant
**Date:** 2025-01-30
**Full Plan:** See `WOOCOMMERCE_RESTORATION_PLAN.md` in repository
**Summary:** See `ISSUE_7_RESOLUTION.md` in repository
```

---

## Comment to Post on Issue #7

After creating the new issue, post this comment on issue #7:

```markdown
✅ **Implementation plan created as requested**

A comprehensive step-by-step implementation plan has been created for restoring WooCommerce integration functionality.

**New Issue Created:** #[NEW_ISSUE_NUMBER]

**Plan Documents:**
- `WOOCOMMERCE_RESTORATION_PLAN.md` - Full 689-line implementation plan
- `ISSUE_7_RESOLUTION.md` - Summary of work done
- `NEW_ISSUE_TEMPLATE.md` - Template for manual issue creation

**Commits:**
- cf26971 - Implementation plan
- f1093b8 - Resolution summary

**Next Steps:**
1. Review the implementation plan in the new issue
2. Approve and begin Phase 1 implementation
3. This issue can now be closed

**Closing this issue as the requested deliverable (step-by-step implementation plan in a new issue) has been completed.**
```

Then close issue #7.

---

**End of Template**
