# Database Migration Guide: Old DB to New DB

This guide will help you migrate data from your old database structure to the new upgraded database structure.

## Prerequisites

1. **Backup both databases** before starting the migration
2. Ensure the new database schema has been created (run all migrations)
3. Have database access credentials ready
4. Test in a staging environment first

## Important Notes

- The old database name in the SQL script is referenced as `old_database`. **You must replace this with your actual old database name**.
- The new database is assumed to be the default/current database where you run the script.
- Some tables have significant structural changes - review them carefully.
- Foreign key constraints are disabled during migration.

## Step-by-Step Migration Process

### Step 1: Backup Databases

```bash
# Backup old database
mysqldump -u [username] -p old_database_name > old_db_backup.sql

# Backup new database (even if empty)
mysqldump -u [username] -p new_database_name > new_db_backup.sql
```

### Step 2: Update Database Names in SQL Script

Open `migration_old_to_new.sql` and replace:
- `old_database` with your actual old database name
- Ensure you're connected to the correct new database

### Step 3: Review Table Mappings

Key table changes:

1. **users**: Many new fields added (verification, payment methods, etc.)
2. **investment_plans**: New fields for description, holdings, return percentages
3. **investments**: `amount_invested` changed from varchar to decimal
4. **wallets**: Structure changed - `balance` and `withdrawable_balance` removed, `amount` changed to decimal
5. **fundings**: New fields for wallet address, chat, type
6. **withdrawals**: `amount` changed from varchar to decimal
7. **custom_plans**: **MAJOR CHANGE** - completely different structure. This table may need manual review.
8. **joint_accounts**: New fields for account_number, approval system
9. **depay_transactions**: `local_amount` changed from varchar to decimal

### Step 4: Run the Migration Script

```bash
# Connect to MySQL
mysql -u [username] -p new_database_name

# Run the migration script
source migration_old_to_new.sql;
```

Or from command line:
```bash
mysql -u [username] -p new_database_name < migration_old_to_new.sql
```

### Step 5: Verify Migration

Run these verification queries:

```sql
-- Check record counts
SELECT 'users' as table_name, COUNT(*) as count FROM users
UNION ALL
SELECT 'investments', COUNT(*) FROM investments
UNION ALL
SELECT 'wallets', COUNT(*) FROM wallets
UNION ALL
SELECT 'fundings', COUNT(*) FROM fundings
UNION ALL
SELECT 'withdrawals', COUNT(*) FROM withdrawals
UNION ALL
SELECT 'investment_plans', COUNT(*) FROM investment_plans
UNION ALL
SELECT 'referrals', COUNT(*) FROM referrals;

-- Verify user-investment relationships
SELECT u.id, u.email, u.name, COUNT(i.id) as investment_count 
FROM users u 
LEFT JOIN investments i ON u.id = i.user_id 
GROUP BY u.id, u.email, u.name 
ORDER BY investment_count DESC
LIMIT 10;

-- Check for any NULL values in critical fields
SELECT 
    (SELECT COUNT(*) FROM users WHERE email IS NULL) as users_missing_email,
    (SELECT COUNT(*) FROM investments WHERE user_id IS NULL) as investments_missing_user,
    (SELECT COUNT(*) FROM investments WHERE investment_plan_id IS NULL) as investments_missing_plan;

-- Verify wallet amounts are numeric
SELECT id, user_id, amount, real_amount, coin 
FROM wallets 
WHERE amount = 0 OR real_amount = 0 
LIMIT 10;

-- Check investment amounts
SELECT id, user_id, amount_invested, earning, status 
FROM investments 
WHERE amount_invested IS NULL OR amount_invested = 0
LIMIT 10;
```

## Handling Special Cases

### 1. Custom Plans Table

The `custom_plans` table has a completely different structure between old and new databases:
- **Old**: Meal plan related (amount, duration, num_meals_per_day, contains_meat, etc.)
- **New**: Investment plan related (investment_plan_id, amount_invested, min_amount, etc.)

**Action Required**: Review this table manually. If old custom_plans are meal-related, they may not map to the new investment-related structure. You may need to:
- Keep old data in a separate backup
- Create new custom_plans based on actual requirements
- Or map to a different table if applicable

### 2. Data Type Conversions

Several fields were converted from VARCHAR to DECIMAL:
- `investments.amount_invested`: varchar → decimal(20,4)
- `withdrawals.amount`: varchar → decimal(20,4)
- `wallets.amount`: varchar → decimal(20,4)
- `depay_transactions.local_amount`: varchar → decimal(20,4)

The migration script handles these conversions, but verify that all values converted correctly.

### 3. New Required Fields

Many tables now have required fields that didn't exist in the old database. The migration script sets default values, but you may need to:
- Review and update verification statuses for users
- Set appropriate wallet addresses for fundings
- Configure approval settings for joint accounts

### 4. Foreign Key Relationships

After migration, verify foreign key relationships:

```sql
-- Check for orphaned investments
SELECT i.id, i.user_id, i.investment_plan_id 
FROM investments i 
LEFT JOIN users u ON i.user_id = u.id 
WHERE u.id IS NULL;

-- Check for orphaned referrals
SELECT r.id, r.inviter_id, r.invited_id 
FROM referrals r 
LEFT JOIN users u1 ON r.inviter_id = u1.id 
LEFT JOIN users u2 ON r.invited_id = u2.id 
WHERE u1.id IS NULL OR u2.id IS NULL;
```

## Rollback Plan

If you need to rollback:

1. Restore the new database from backup
2. Identify what went wrong
3. Fix the migration script
4. Restore and re-run

## Post-Migration Tasks

1. **Update Application Code**: Ensure your application code works with the new database structure
2. **Test User Flows**: Test critical user flows:
   - User registration and login
   - Investment creation and tracking
   - Wallet operations (funding, withdrawal)
   - Referral system
   - Joint accounts
3. **Verify Calculations**: Double-check financial calculations (investments, earnings, balances)
4. **Update Indexes**: Ensure all necessary indexes are created for performance
5. **Enable Foreign Keys**: The script disables foreign keys during migration - ensure they're re-enabled

## Troubleshooting

### Issue: Duplicate Key Errors

If you encounter duplicate key errors, you may have already run the migration partially. Options:
- Clear the new database tables and re-run
- Modify the script to use `INSERT IGNORE` instead of `INSERT ... ON DUPLICATE KEY UPDATE`

### Issue: Data Type Conversion Errors

If you see conversion errors (especially VARCHAR to DECIMAL):
- Check the source data for invalid values
- Clean the data in the old database first
- Use CASE statements to handle edge cases

### Issue: Missing Foreign Key References

If references are missing:
- Check if the referenced records exist
- Create placeholder records if needed
- Or set NULL if the field allows it

## Support

If you encounter issues:
1. Check the error message carefully
2. Verify your database names are correct
3. Ensure both databases are accessible
4. Review the data in the old database for anomalies

## Tables Not Migrated

The following tables exist only in the new database and won't have data migrated:
- `applications`
- `billings`
- `cache` / `cache_locks`
- `connections`
- `copiers`
- `coin_rate_changes`
- `forex_markets`
- `funding_chats` / `funding_messages`
- `gas_fee_payments`
- `investor_profiles` / `investor_subscriptions`
- `jobs` / `job_applications` / `job_postings`
- `live_chats` / `live_chat_messages`
- `p2p_*` tables (all P2P related)
- `payment_proofs`
- `sessions`
- `stock_markets`
- `traders` / `trades` / `tradings` / `trading_plans`
- `update_messages`
- `visitors`
- `withdrawal_requirements` / `withdrawal_verifications`

These are new features in the upgraded system and will be populated through normal application usage.

