# 🚀 First Capital Database Migration Guide

## 📋 **Prerequisites**
- Access to phpMyAdmin
- Old database: `firstcap_firstcapital_old` 
- New database: `firstcap_firstcapital`
- Both databases on the same server

## 🔧 **Step-by-Step Migration Process**

### **Step 1: Backup Your Current Database**
1. **Select the NEW database** (`firstcap_firstcapital`)
2. **Go to Export tab**
3. **Choose "Custom" export method**
4. **Select all tables**
5. **Click Go** to download backup

### **Step 2: Verify Database Access**
1. **Check you can see both databases:**
   - `firstcap_firstcapital_old` (source)
   - `firstcap_firstcapital` (target)

2. **Verify you have access to both databases**

### **Step 3: Run the Migration Script**
1. **Select the NEW database** (`firstcap_firstcapital`)
2. **Go to SQL tab**
3. **Copy the entire migration script** from `migration_script.sql`
4. **Paste it into the SQL text area**
5. **Click Go** to execute

## ⚠️ **Important Notes**

### **What the Script Does:**
✅ **Creates mapping tables** to track old→new relationships  
✅ **Migrates users** preserving IDs when possible  
✅ **Creates bank accounts** from old embedded data  
✅ **Migrates cards, transactions, investments, loans**  
✅ **Logs everything** for audit trail  
✅ **Shows results** at the end  

### **Safety Features:**
- **Won't overwrite** existing data
- **Uses mapping tables** to maintain relationships
- **Preserves passwords** from old system
- **Creates audit trail** in `etl_log` table

## 🔍 **After Migration - Verification Steps**

### **1. Check Migration Log**
```sql
SELECT * FROM etl_log ORDER BY created_at;
```

### **2. Verify User Counts**
```sql
SELECT 
  'Old Database' as source,
  COUNT(*) as user_count
FROM firstcap_firstcapital_old.users
UNION ALL
SELECT 
  'New Database' as source,
  COUNT(*) as user_count
FROM users;
```

### **3. Check Account Mappings**
```sql
SELECT 
  mu.old_user_id,
  mu.new_user_id,
  u.email,
  u.name
FROM map_users mu
JOIN users u ON u.id = mu.new_user_id
LIMIT 10;
```

### **4. Test Login**
- Try logging in with old user credentials
- Passwords should work (they're preserved)

## 🆘 **Troubleshooting**

### **Common Issues:**

#### **"Table doesn't exist" errors:**
- The script checks for table existence
- Some tables might not exist in old database
- Script will skip missing tables safely

#### **"Access denied" errors:**
- Ensure you have access to both databases
- Check user permissions

#### **"Duplicate entry" errors:**
- Script uses `INSERT IGNORE` to handle duplicates
- Check mapping tables for conflicts

### **If Migration Fails:**
1. **Check the error message**
2. **Look at the `etl_log` table** to see what completed
3. **Restore from backup** if needed
4. **Run the script again** - it's safe to rerun

## 📊 **Expected Results**

After successful migration, you should see:

- **Users migrated** with preserved passwords
- **Bank accounts created** for each user
- **Cards imported** (if they existed)
- **Transactions mapped** from old system
- **Investment accounts created** (if they existed)
- **Loans imported** (if they existed)

## 🔄 **Rollback Plan**

If you need to undo the migration:

1. **Restore from backup** (Step 1)
2. **Drop mapping tables:**
   ```sql
   DROP TABLE IF EXISTS map_users;
   DROP TABLE IF EXISTS map_accounts;
   DROP TABLE IF EXISTS etl_log;
   ```

## 📞 **Need Help?**

- Check the `etl_log` table for detailed migration status
- Verify both databases are accessible
- Ensure you have proper permissions
- The script is designed to be safe and reversible

## 🎯 **Next Steps After Migration**

1. **Test the new system** with migrated users
2. **Verify all data** appears correctly
3. **Update any hardcoded references** to old database
4. **Test login functionality** with old credentials
5. **Monitor for any data inconsistencies**

---

**Remember:** This migration preserves your old data and creates a clean mapping between old and new systems. You can always rollback if needed!
