# Unified Database Setup Instructions

This guide will help you set up a database that supports both your old and new data structures, allowing you to import old data directly without complex transformations.

## What You Get

1. **`unified_database_schema.sql`** - Complete database schema that supports both old and new structures
2. **`import_old_data.sql`** - Simple script to import data directly from old database

## Quick Start (3 Steps)

### Step 1: Create the Unified Database Schema

```bash
# Connect to MySQL
mysql -u root -p

# Create a new database
CREATE DATABASE unified_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE unified_database;

# Run the unified schema script
source unified_database_schema.sql;
```

Or from command line:
```bash
mysql -u root -p -e "CREATE DATABASE unified_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
mysql -u root -p unified_database < unified_database_schema.sql
```

### Step 2: Import Your Old Data

Edit `import_old_data.sql` and replace `old_database` with your actual old database name, then run:

```bash
mysql -u root -p unified_database < import_old_data.sql
```

Or manually:
```sql
USE unified_database;
source import_old_data.sql;
```

### Step 3: Verify Import

Run the verification queries at the end of `import_old_data.sql` to ensure data was imported correctly.

## What Makes This Schema Special?

### ✅ Backward Compatibility
- All old database fields are preserved
- Old data types (VARCHAR) are maintained
- You can import old data without any transformations

### ✅ Forward Compatibility  
- All new database fields are included
- New features are ready to use
- Application can use new structure immediately

### ✅ Dual Support
For fields that changed data types (e.g., VARCHAR → DECIMAL), both versions exist:
- **Original field** (VARCHAR) - stores old data as-is
- **New field** (with `_decimal` suffix) - for new features

Examples:
- `wallets.amount` (VARCHAR) + `wallets.amount_decimal` (DECIMAL)
- `investments.amount_invested` (VARCHAR) + `investments.amount_invested_decimal` (DECIMAL)
- `investment_plans.amount_invested` (VARCHAR) + `investment_plans.amount_invested_decimal` (DECIMAL)

## Key Features

### Combined Fields

**Users Table:**
- ✅ Old fields: `billing_title`, `billing_text`, `billing_stage`, `account_status`
- ✅ New fields: `verification_status`, `payment_methods`, `two_fa_status`, etc.
- ✅ All fields included and nullable where appropriate

**Wallets Table:**
- ✅ Old fields: `balance`, `withdrawable_balance`, `amount` (VARCHAR), `real_amount` (VARCHAR)
- ✅ New fields: `amount_decimal`, `real_amount_decimal`, `amount_paid20`
- ✅ All fields included for compatibility

**Investment Plans:**
- ✅ Old structure preserved (VARCHAR fields)
- ✅ New structure added (DECIMAL fields with `_decimal` suffix)
- ✅ New fields: `description`, `holdings_allocation`, `investment_type`, etc.

**Custom Plans:**
- ✅ Old structure (meal plans) preserved
- ✅ New structure (investment plans) added
- ✅ Both can coexist in the same table

## Optional: Populate Decimal Fields

After importing, you can optionally populate the new decimal fields from varchar fields:

1. Uncomment the UPDATE statements at the end of `import_old_data.sql`
2. Run them to convert VARCHAR values to DECIMAL where valid

This is optional - your application can continue using VARCHAR fields or migrate gradually to DECIMAL fields.

## Application Usage

Your application code can now:
- Read old data from original VARCHAR fields
- Write new data to DECIMAL fields
- Gradually migrate to using DECIMAL fields
- Support both old and new data simultaneously

## Database Structure Summary

### Tables with Dual Field Support:
- `investment_plans` - VARCHAR + DECIMAL versions
- `investments` - VARCHAR + DECIMAL versions  
- `wallets` - Old balance fields + new decimal fields
- `withdrawals` - VARCHAR + DECIMAL versions
- `depay_transactions` - VARCHAR + DECIMAL versions

### Tables with Combined Structures:
- `users` - Old billing fields + new verification fields
- `custom_plans` - Old meal plan structure + new investment plan structure
- `joint_accounts` - Old structure + new approval system
- `joint_account_members` - Old VARCHAR permissions + new JSON permissions
- `joint_account_transactions` - Old field names + new field names

### Tables Unchanged:
- `bonuses`, `referrals`, `reinvestments`, `cards`, `carts`, `categories`, `countries`, `expenses`, `products`, `plans`, `subscriptions`, `orders`, `plates`, `roles`, `shareholders`, etc.

## Troubleshooting

### Issue: Duplicate Key Errors
- Data already exists? Use `INSERT IGNORE` or `REPLACE INTO` instead
- Check if you've run the import before

### Issue: Foreign Key Errors
- Foreign keys are disabled during import (SET FOREIGN_KEY_CHECKS = 0)
- Re-enable them after import if needed

### Issue: Data Type Mismatches
- The unified schema accepts both old and new data types
- If specific errors occur, check the field definitions in `unified_database_schema.sql`

## Next Steps

1. ✅ Import your old data successfully
2. ✅ Verify all records are imported
3. ✅ Test your application with the unified schema
4. ✅ Gradually migrate application code to use new fields/structures
5. ✅ Eventually deprecate old fields (optional, after migration is complete)

## Support

If you encounter any issues:
1. Check that database names are correct in the import script
2. Verify both databases are accessible
3. Ensure the unified schema was created successfully
4. Review error messages carefully

---

**Remember**: This unified schema is designed to give you maximum flexibility - you can import old data immediately and use new features without any data transformation!

