-- =====================================================
-- MIGRATION SCRIPT: OLD DB TO NEW DB (TEMPLATE)
-- =====================================================
-- INSTRUCTIONS:
-- 1. Replace [OLD_DATABASE_NAME] with your old database name
-- 2. Connect to your NEW database
-- 3. Run this script
-- =====================================================

-- SET YOUR DATABASE NAMES HERE:
SET @old_db = '[OLD_DATABASE_NAME]';  -- Replace with actual old database name
SET @new_db = DATABASE();  -- Current database (new database)

-- Verify database names (uncomment to check)
-- SELECT @old_db as old_database, @new_db as new_database;

SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

-- =====================================================
-- HELPER FUNCTION: Build table reference
-- =====================================================
-- Note: MySQL doesn't support variables in table names directly
-- So you'll need to replace [OLD_DATABASE_NAME] manually
-- OR use this approach with prepared statements (see end of file)

-- For now, use direct replacement:
-- Replace all instances of: `old_database`.`table_name`
-- With: `[YOUR_OLD_DB_NAME]`.`table_name`

-- =====================================================
-- 1. MIGRATE USERS TABLE
-- =====================================================
INSERT INTO `users` (
    `id`, `name`, `profile`, `email`, `account_status`, `currency`, `country`,
    `email_verified_at`, `password`, `password_copy`, `phone`, `referral_code`,
    `remember_token`, `created_at`, `updated_at`,
    `verification_status`, `payment_methods`, `payment_details`,
    `token_verification_amount`, `token_verification_status`, `gas_fee_amount`,
    `gas_fee_status`, `verification_token_type`, `gas_fee_token_type`,
    `address`, `city`, `state`, `zip`, `error_type`, `error_message`,
    `email_verification_status`, `sms_verification_status`, `two_fa_status`,
    `two_fa_verification`, `special_client`, `pending_balance`, `balance_status`
)
SELECT 
    `id`, `name`, `profile`, `email`,
    COALESCE(`account_status`, 'Active') as `account_status`,
    `currency`, `country`, `email_verified_at`, `password`, `password_copy`,
    `phone`, `referral_code`, `remember_token`, `created_at`, `updated_at`,
    NULL as `verification_status`, NULL as `payment_methods`, NULL as `payment_details`,
    0.00000000 as `token_verification_amount`, 0 as `token_verification_status`,
    0.00000000 as `gas_fee_amount`, 0 as `gas_fee_status`,
    'USDT' as `verification_token_type`, 'TRX' as `gas_fee_token_type`,
    '' as `address`, NULL as `city`, '' as `state`, NULL as `zip`,
    NULL as `error_type`, NULL as `error_message`,
    CASE WHEN `email_verified_at` IS NOT NULL THEN 'Verified' ELSE 'Unverified' END as `email_verification_status`,
    'Unverified' as `sms_verification_status`, 'Unverified' as `two_fa_status`,
    'Unverified' as `two_fa_verification`, 0 as `special_client`,
    0.00 as `pending_balance`, 'active' as `balance_status`
FROM `old_database`.`users`  -- CHANGE: Replace 'old_database' with your actual old DB name
ON DUPLICATE KEY UPDATE
    `name` = VALUES(`name`),
    `email` = VALUES(`email`),
    `account_status` = VALUES(`account_status`),
    `currency` = VALUES(`currency`),
    `country` = VALUES(`country`),
    `phone` = VALUES(`phone`);

-- =====================================================
-- CONTINUE WITH OTHER TABLES...
-- =====================================================
-- (Use the same pattern - replace 'old_database' with actual database name)
-- See migration_old_to_new.sql for complete script

SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================
-- ALTERNATIVE: Using Prepared Statements (Dynamic SQL)
-- =====================================================
-- If you want to use variables dynamically, use this approach:

/*
DELIMITER $$

CREATE PROCEDURE migrate_table(IN old_db_name VARCHAR(255), IN table_name VARCHAR(255))
BEGIN
    SET @sql = CONCAT('INSERT INTO ', table_name, ' SELECT * FROM ', old_db_name, '.', table_name);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$

DELIMITER ;

-- Usage:
-- CALL migrate_table('old_database_name', 'users');
-- CALL migrate_table('old_database_name', 'investments');
-- etc.
*/

