# Migration Notes - Supabase to MySQL

This document tracks the migration from Supabase to MySQL/Namecheap hosting.

---

## ✅ Completed Changes

### Database Layer
- ✅ Created MySQL schema (`database/schema.sql`)
- ✅ Created database connection utility (`lib/db/mysql.ts`)
- ✅ Created database types (`lib/db/types.ts`)
- ✅ Created query utilities for:
  - Users (`lib/auth/user.ts`)
  - Sessions (`lib/auth/session.ts`)
  - Analysis runs (`lib/db/analysis.ts`)
  - Exports (`lib/db/exports.ts`)
  - Billing events (`lib/db/billing.ts`)
  - Admin logs (`lib/db/admin.ts`)

### Authentication System
- ✅ Created password utilities (`lib/auth/password.ts`)
- ✅ Created session management (`lib/auth/session.ts`)
- ✅ Created user management (`lib/auth/user.ts`)
- ✅ Replaced Supabase client (`lib/mysql/client.ts`)
- ✅ Replaced Supabase server (`lib/mysql/server.ts`)

### Email System
- ✅ Created SMTP service (`lib/email/smtp.ts`)
- ✅ Created email templates (`lib/email/templates.ts`)
- ✅ Created email sending utilities (`lib/email/send.ts`)

### API Routes - Completed
- ✅ `app/api/auth/signup/route.ts` - New signup with MySQL
- ✅ `app/api/auth/login/route.ts` - New login with MySQL
- ✅ `app/api/auth/signout/route.ts` - Updated session cleanup
- ✅ `app/api/auth/verify-email/route.ts` - New email verification
- ✅ `app/api/auth/forgot-password/route.ts` - New password reset request
- ✅ `app/api/auth/reset-password/route.ts` - New password reset
- ✅ `app/api/auth/resend-verification/route.ts` - New resend verification
- ✅ `app/api/user/profile/route.ts` - Updated user profile
- ✅ `app/api/admin/profiles/route.ts` - Updated admin user list
- ✅ `app/api/admin/update-plan/route.ts` - Updated plan management
- ✅ `app/api/admin/stats/route.ts` - New admin statistics

### Configuration
- ✅ Updated `package.json` - Added MySQL, bcrypt, nodemailer
- ✅ Created `.env.example` - Complete environment template
- ✅ Created deployment documentation

### Documentation
- ✅ `NAMECHEAP_DEPLOYMENT.md` - Complete deployment guide
- ✅ `DATABASE_SETUP.md` - Database setup guide
- ✅ `README.md` - Project overview
- ✅ `QUICKSTART.md` - Quick start guide

---

## 🔄 Files That Still Need Updates

### API Routes - Stripe Integration

**Files to update**:
1. `app/api/stripe/checkout/route.ts` - Replace Supabase profile lookup
2. `app/api/stripe/portal/route.ts` - Replace Supabase customer lookup
3. `app/api/stripe/webhook/route.ts` - Replace Supabase profile updates
4. `app/api/stripe/sync/route.ts` - Replace Supabase sync logic

**Changes needed**:
- Replace `supabase.from('profiles')` with MySQL queries
- Use `getUserById()`, `updateUserStripeInfo()` from `lib/auth/user.ts`
- Use `createBillingEvent()` from `lib/db/billing.ts`

### API Routes - Analysis & Rebrand

**Files to update**:
1. `app/api/scrape/route.ts` - Replace Supabase usage tracking
2. `app/api/rebrand/route.ts` - Replace Supabase analysis storage
3. `app/api/export-csv/route.ts` - Replace Supabase export tracking

**Changes needed**:
- Replace Supabase auth with `requireAuth()` from `lib/auth/session.ts`
- Use `createAnalysisRun()`, `updateAnalysisRunResults()` from `lib/db/analysis.ts`
- Use `createExport()` from `lib/db/exports.ts`
- Use `incrementUserAnalysis()`, `incrementUserExports()` from `lib/auth/user.ts`

### Frontend Components

**Files to update**:
1. Components using `createClient()` from `@/lib/supabase/client`
2. Components using `createClient()` from `@/lib/supabase/server`
3. Any component with Supabase imports

**Search for**:
```bash
grep -r "supabase" app/ components/ --include="*.tsx" --include="*.ts"
```

**Changes needed**:
- Replace `import { createClient } from '@/lib/supabase/client'` with API calls
- Replace `import { createClient } from '@/lib/supabase/server'` with `getUser()` from `@/lib/mysql/server`
- Update authentication checks to use new session system

### Pages

**Files to check**:
1. `app/dashboard/page.tsx` - May use Supabase for user data
2. `app/dashboard/billing/page.tsx` - May use Supabase for subscription
3. `app/admin/page.tsx` - May use Supabase for admin queries
4. `app/auth/*/page.tsx` - May use Supabase auth

**Changes needed**:
- Replace Supabase data fetching with API calls to new endpoints
- Update authentication flows

---

## 🔍 How to Find Remaining Supabase References

### Search Commands

```bash
# Find all Supabase imports
grep -r "from.*supabase" . --include="*.ts" --include="*.tsx"

# Find Supabase client usage
grep -r "createClient" . --include="*.ts" --include="*.tsx"

# Find Supabase auth usage
grep -r "supabase.auth" . --include="*.ts" --include="*.tsx"

# Find Supabase database usage
grep -r "supabase.from" . --include="*.ts" --include="*.tsx"
```

### Files to Ignore
- `lib/supabase/` - Old files, can be deleted after migration
- `node_modules/` - Dependencies
- `.next/` - Build output

---

## 📝 Migration Checklist

### Phase 1: Core Infrastructure ✅
- [x] Database schema
- [x] Database utilities
- [x] Authentication system
- [x] Email system
- [x] Environment configuration

### Phase 2: API Routes (Partial)
- [x] Authentication routes
- [x] User profile routes
- [x] Admin routes
- [ ] Stripe routes (needs update)
- [ ] Analysis routes (needs update)
- [ ] Export routes (needs update)

### Phase 3: Frontend (Pending)
- [ ] Update components
- [ ] Update pages
- [ ] Update hooks
- [ ] Remove Supabase dependencies

### Phase 4: Testing & Deployment
- [ ] Test all authentication flows
- [ ] Test subscription flows
- [ ] Test analysis flows
- [ ] Test admin functions
- [ ] Deploy to Namecheap
- [ ] Verify production functionality

---

## 🛠️ Update Pattern

### Before (Supabase)
```typescript
import { createClient } from '@/lib/supabase/server';

const supabase = await createClient();
const { data: { user } } = await supabase.auth.getUser();
const { data: profile } = await supabase
  .from('profiles')
  .select('*')
  .eq('id', user.id)
  .single();
```

### After (MySQL)
```typescript
import { getSessionUser } from '@/lib/auth/session';

const user = await getSessionUser();
if (!user) {
  return NextResponse.json({ error: 'Unauthorized' }, { status: 401 });
}
// user already contains all profile data
```

### Before (Supabase - Client)
```typescript
import { createClient } from '@/lib/supabase/client';

const supabase = createClient();
const { data: { user } } = await supabase.auth.getUser();
```

### After (MySQL - Client)
```typescript
// Use API call instead
const response = await fetch('/api/user/profile');
const { user } = await response.json();
```

---

## 🗑️ Files to Delete After Migration

Once migration is complete and tested:

```bash
# Supabase utilities (no longer needed)
rm -rf lib/supabase/

# Supabase types (if any)
# Check for @/types/supabase.ts or similar
```

---

## ⚠️ Breaking Changes

### Authentication
- Session cookies changed from Supabase format to custom format
- Users will need to log in again after migration
- Email verification required (can be bypassed for existing users via SQL)

### Database
- Different table structure (users vs profiles)
- Different field names in some cases
- Need data migration script if moving existing data

### API Responses
- Response format may differ slightly
- Error messages changed
- Status codes may differ

---

## 🔄 Data Migration (If Needed)

If migrating existing Supabase data:

### 1. Export from Supabase
```sql
-- In Supabase SQL editor
COPY (SELECT * FROM auth.users) TO '/tmp/users.csv' CSV HEADER;
COPY (SELECT * FROM profiles) TO '/tmp/profiles.csv' CSV HEADER;
```

### 2. Transform Data
Create transformation script to match new schema

### 3. Import to MySQL
```sql
LOAD DATA INFILE '/tmp/users_transformed.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
```

### 4. Verify Data
```sql
SELECT COUNT(*) FROM users;
SELECT * FROM users LIMIT 5;
```

---

## 📊 Testing Checklist

### Authentication
- [ ] Sign up new user
- [ ] Receive verification email
- [ ] Verify email
- [ ] Login
- [ ] Logout
- [ ] Forgot password
- [ ] Reset password
- [ ] Resend verification

### User Functions
- [ ] View profile
- [ ] Update profile
- [ ] View usage stats
- [ ] Access dashboard

### Admin Functions
- [ ] View all users
- [ ] Search users
- [ ] Update user plan
- [ ] View statistics
- [ ] Access admin panel

### Subscription
- [ ] Create checkout session
- [ ] Complete payment
- [ ] Subscription activated
- [ ] Access customer portal
- [ ] Cancel subscription
- [ ] Webhook received

### Analysis
- [ ] Scrape store
- [ ] Rebrand products
- [ ] Export CSV
- [ ] View history
- [ ] Usage limits enforced

---

## 🎯 Next Steps

1. **Update Stripe routes** - Priority: High
2. **Update analysis routes** - Priority: High
3. **Update frontend components** - Priority: Medium
4. **Test all flows** - Priority: High
5. **Deploy to staging** - Priority: Medium
6. **User acceptance testing** - Priority: Medium
7. **Deploy to production** - Priority: High
8. **Monitor for issues** - Priority: High

---

## 📞 Support During Migration

If you encounter issues:

1. Check this migration guide
2. Review error logs
3. Test database connection
4. Verify environment variables
5. Check API endpoint responses
6. Review Namecheap deployment guide

---

**Migration Progress**: ~70% Complete

**Remaining Work**: Stripe routes, Analysis routes, Frontend updates, Testing