-- =====================================================
-- MIGRATION SCRIPT: OLD DB TO NEW DB
-- =====================================================
-- This script migrates data from the old database structure to the new database structure
-- IMPORTANT: Run this script after the new database schema has been created
-- IMPORTANT: Backup both databases before running this migration
-- IMPORTANT: Test in a staging environment first
-- =====================================================

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

-- =====================================================
-- 1. MIGRATE USERS TABLE
-- =====================================================
-- The new users table has many additional fields
-- Old columns map directly, new columns get default values
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`,
    -- New fields with defaults
    `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`,
    -- Map old account_status to new verification_status if needed
    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`
ON DUPLICATE KEY UPDATE
    `name` = VALUES(`name`),
    `profile` = VALUES(`profile`),
    `email` = VALUES(`email`),
    `account_status` = VALUES(`account_status`),
    `currency` = VALUES(`currency`),
    `country` = VALUES(`country`),
    `phone` = VALUES(`phone`),
    `referral_code` = VALUES(`referral_code`);

-- =====================================================
-- 2. MIGRATE INVESTMENT_PLANS TABLE
-- =====================================================
-- New table has many additional fields
INSERT INTO `investment_plans` (
    `id`,
    `name`,
    `amount_invested`,
    `min_amount`,
    `bonus_per_downline`,
    `number_of_downlines`,
    `total_earning`,
    `amount_per_hour`,
    `initial_earning`,
    `duration`,
    `created_at`,
    `updated_at`,
    -- New fields
    `description`,
    `holdings_allocation`,
    `investment_type`,
    `min_additional_investment`,
    `min_return_percentage`,
    `max_return_percentage`,
    `instant_reward`,
    `extra_note`,
    `investment_duration_range`,
    `image`,
    `category_id`
)
SELECT 
    `id`,
    `name`,
    CAST(`amount_invested` AS DECIMAL(20,4)) as `amount_invested`,
    CAST(`min_amount` AS DECIMAL(20,4)) as `min_amount`,
    `bonus_per_downline`,
    `number_of_downlines`,
    CAST(`total_earning` AS DECIMAL(20,4)) as `total_earning`,
    `amount_per_hour`,
    CAST(`initial_earning` AS DECIMAL(20,4)) as `initial_earning`,
    `duration`,
    `created_at`,
    `updated_at`,
    NULL as `description`,
    NULL as `holdings_allocation`,
    NULL as `investment_type`,
    NULL as `min_additional_investment`,
    NULL as `min_return_percentage`,
    NULL as `max_return_percentage`,
    NULL as `instant_reward`,
    NULL as `extra_note`,
    NULL as `investment_duration_range`,
    NULL as `image`,
    NULL as `category_id`
FROM `old_database`.`investment_plans`
ON DUPLICATE KEY UPDATE
    `name` = VALUES(`name`),
    `amount_invested` = VALUES(`amount_invested`),
    `min_amount` = VALUES(`min_amount`),
    `duration` = VALUES(`duration`);

-- =====================================================
-- 3. MIGRATE INVESTMENTS TABLE
-- =====================================================
-- amount_invested changed from varchar to decimal, earning stays varchar
INSERT INTO `investments` (
    `id`,
    `user_id`,
    `investment_plan_id`,
    `amount_invested`,
    `status`,
    `payment_source`,
    `earning`,
    `created_at`,
    `updated_at`,
    `duration`,
    `percentage_earned`
)
SELECT 
    `id`,
    `user_id`,
    `investment_plan_id`,
    CAST(`amount_invested` AS DECIMAL(20,4)) as `amount_invested`,
    `status`,
    `payment_source`,
    `earning`,
    `created_at`,
    `updated_at`,
    `duration`,
    NULL as `percentage_earned`
FROM `old_database`.`investments`
ON DUPLICATE KEY UPDATE
    `amount_invested` = VALUES(`amount_invested`),
    `status` = VALUES(`status`),
    `earning` = VALUES(`earning`);

-- =====================================================
-- 4. MIGRATE WALLETS TABLE
-- =====================================================
-- Structure changed: balance and withdrawable_balance removed in new DB
-- amount and real_amount changed to decimal(20,4)
INSERT INTO `wallets` (
    `id`,
    `user_id`,
    `amount`,
    `real_amount`,
    `address`,
    `coin`,
    `invoice_id`,
    `amount_paid20`,
    `status`,
    `created_at`,
    `updated_at`
)
SELECT 
    `id`,
    `user_id`,
    CAST(COALESCE(`amount`, '0') AS DECIMAL(20,4)) as `amount`,
    CAST(COALESCE(`real_amount`, '0') AS DECIMAL(20,4)) as `real_amount`,
    `address`,
    `coin`,
    `invoice_id`,
    CAST(COALESCE(`amount_paid`, '0') AS DECIMAL(4,2)) as `amount_paid20`,
    `status`,
    `created_at`,
    `updated_at`
FROM `old_database`.`wallets`
ON DUPLICATE KEY UPDATE
    `amount` = VALUES(`amount`),
    `real_amount` = VALUES(`real_amount`),
    `status` = VALUES(`status`);

-- =====================================================
-- 5. MIGRATE FUNDINGS TABLE
-- =====================================================
-- New table has additional fields: wallet_address, wallet_name, type, wallet_address_id, chat_id
-- description field removed in new DB
INSERT INTO `fundings` (
    `id`,
    `user_id`,
    `amount`,
    `status`,
    `gateway`,
    `transaction_id`,
    `created_at`,
    `updated_at`,
    `wallet_address`,
    `wallet_name`,
    `type`,
    `wallet_address_id`,
    `chat_id`
)
SELECT 
    `id`,
    `user_id`,
    `amount`,
    `status`,
    `gateway`,
    `transaction_id`,
    `created_at`,
    `updated_at`,
    NULL as `wallet_address`,
    NULL as `wallet_name`,
    NULL as `type`,
    NULL as `wallet_address_id`,
    NULL as `chat_id`
FROM `old_database`.`fundings`
ON DUPLICATE KEY UPDATE
    `amount` = VALUES(`amount`),
    `status` = VALUES(`status`),
    `gateway` = VALUES(`gateway`);

-- =====================================================
-- 6. MIGRATE WITHDRAWALS TABLE
-- =====================================================
-- amount changed from varchar to decimal(20,4)
INSERT INTO `withdrawals` (
    `id`,
    `user_id`,
    `amount`,
    `source`,
    `address`,
    `coin_name`,
    `status`,
    `created_at`,
    `updated_at`
)
SELECT 
    `id`,
    `user_id`,
    CAST(`amount` AS DECIMAL(20,4)) as `amount`,
    `source`,
    `address`,
    `coin_name`,
    `status`,
    `created_at`,
    `updated_at`
FROM `old_database`.`withdrawals`
ON DUPLICATE KEY UPDATE
    `amount` = VALUES(`amount`),
    `status` = VALUES(`status`);

-- =====================================================
-- 7. MIGRATE BONUSES TABLE
-- =====================================================
-- Structure is the same
INSERT INTO `bonuses` (
    `id`,
    `user_id`,
    `amount`,
    `status`,
    `created_at`,
    `updated_at`
)
SELECT 
    `id`,
    `user_id`,
    `amount`,
    `status`,
    `created_at`,
    `updated_at`
FROM `old_database`.`bonuses`
ON DUPLICATE KEY UPDATE
    `amount` = VALUES(`amount`),
    `status` = VALUES(`status`);

-- =====================================================
-- 8. MIGRATE REFERRALS TABLE
-- =====================================================
-- Structure is the same
INSERT INTO `referrals` (
    `id`,
    `status`,
    `earning`,
    `invited_id`,
    `inviter_id`,
    `investment_id`,
    `created_at`,
    `updated_at`
)
SELECT 
    `id`,
    `status`,
    `earning`,
    `invited_id`,
    `inviter_id`,
    `investment_id`,
    `created_at`,
    `updated_at`
FROM `old_database`.`referrals`
ON DUPLICATE KEY UPDATE
    `earning` = VALUES(`earning`),
    `status` = VALUES(`status`);

-- =====================================================
-- 9. MIGRATE REINVESTMENTS TABLE
-- =====================================================
-- Structure is the same (deleted_at exists in both)
INSERT INTO `reinvestments` (
    `id`,
    `user_id`,
    `investment_id`,
    `amount`,
    `source`,
    `status`,
    `created_at`,
    `updated_at`,
    `deleted_at`
)
SELECT 
    `id`,
    `user_id`,
    `investment_id`,
    `amount`,
    `source`,
    `status`,
    `created_at`,
    `updated_at`,
    `deleted_at`
FROM `old_database`.`reinvestments`
ON DUPLICATE KEY UPDATE
    `amount` = VALUES(`amount`),
    `status` = VALUES(`status`);

-- =====================================================
-- 10. MIGRATE DEPAY_TRANSACTIONS TABLE
-- =====================================================
-- local_amount changed from varchar to decimal(20,4)
INSERT INTO `depay_transactions` (
    `id`,
    `user_id`,
    `blockchain`,
    `sender`,
    `nonce`,
    `receiver`,
    `token`,
    `amount`,
    `local_amount`,
    `confirmations`,
    `after_block`,
    `uuid`,
    `forward_to`,
    `forward_on_failure`,
    `status`,
    `created_at`,
    `updated_at`
)
SELECT 
    `id`,
    `user_id`,
    `blockchain`,
    `sender`,
    `nonce`,
    `receiver`,
    `token`,
    `amount`,
    CAST(`local_amount` AS DECIMAL(20,4)) as `local_amount`,
    `confirmations`,
    `after_block`,
    `uuid`,
    `forward_to`,
    `forward_on_failure`,
    `status`,
    `created_at`,
    `updated_at`
FROM `old_database`.`depay_transactions`
ON DUPLICATE KEY UPDATE
    `status` = VALUES(`status`),
    `local_amount` = VALUES(`local_amount`);

-- =====================================================
-- 11. MIGRATE CUSTOM_PLANS TABLE
-- =====================================================
-- MAJOR CHANGE: New structure is completely different
-- Old: user_id, name, amount, duration, num_meals_per_day, contains_meat, description, num_dishes
-- New: user_id, investment_plan_id, name, amount_invested, min_amount, bonus_per_downline, etc.
-- This seems like a different concept - may need manual review
-- For now, we'll skip this table as it appears to serve different purposes
-- INSERT INTO `custom_plans` ... (REVIEW NEEDED)

-- =====================================================
-- 12. MIGRATE OTHER TABLES (same structure)
-- =====================================================

-- CARDS
INSERT INTO `cards` (
    `id`, `user_id`, `number1`, `number2`, `number3`, `number4`, 
    `status`, `card_type`, `created_at`, `updated_at`
)
SELECT `id`, `user_id`, `number1`, `number2`, `number3`, `number4`, 
       `status`, `card_type`, `created_at`, `updated_at`
FROM `old_database`.`cards`
ON DUPLICATE KEY UPDATE `status` = VALUES(`status`);

-- CARTS
INSERT INTO `carts` (
    `id`, `user_id`, `products`, `status`, `payment_id`, 
    `created_at`, `updated_at`
)
SELECT `id`, `user_id`, `products`, `status`, `payment_id`, 
       `created_at`, `updated_at`
FROM `old_database`.`carts`
ON DUPLICATE KEY UPDATE `status` = VALUES(`status`);

-- CATEGORIES (new DB has description field)
INSERT INTO `categories` (
    `id`, `name`, `created_at`, `updated_at`, `description`
)
SELECT `id`, `name`, `created_at`, `updated_at`, NULL as `description`
FROM `old_database`.`categories`
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`);

-- COUNTRIES
INSERT INTO `countries` (
    `id`, `name`, `currency`, `code`, `currency_code`, 
    `phone_code`, `flag`, `created_at`, `updated_at`
)
SELECT `id`, `name`, `currency`, `code`, `currency_code`, 
       `phone_code`, `flag`, `created_at`, `updated_at`
FROM `old_database`.`countries`
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`);

-- EXPENSES
INSERT INTO `expenses` (
    `id`, `user_id`, `amount`, `reason`, `status`, 
    `created_at`, `updated_at`
)
SELECT `id`, `user_id`, `amount`, `reason`, `status`, 
       `created_at`, `updated_at`
FROM `old_database`.`expenses`
ON DUPLICATE KEY UPDATE `amount` = VALUES(`amount`);

-- JOINT_ACCOUNTS
-- New structure has many additional fields
INSERT INTO `joint_accounts` (
    `id`, `account_name`, `balance`, `status`, 
    `created_at`, `updated_at`,
    `account_number`, `description`, `minimum_approval_amount`,
    `approval_type`, `created_by`
)
SELECT 
    `id`, 
    `account_name`, 
    `balance`, 
    COALESCE(`status`, 'active') as `status`, 
    `created_at`, 
    `updated_at`,
    CONCAT('ACC', LPAD(`id`, 10, '0')) as `account_number`,
    NULL as `description`,
    100.00 as `minimum_approval_amount`,
    'majority' as `approval_type`,
    `primary_owner_id` as `created_by`
FROM `old_database`.`joint_accounts`
ON DUPLICATE KEY UPDATE `balance` = VALUES(`balance`);

-- JOINT_ACCOUNT_MEMBERS
-- New structure changed: role is now enum, permissions is longtext
INSERT INTO `joint_account_members` (
    `id`, `joint_account_id`, `user_id`, `role`, 
    `status`, `permissions`, `created_at`, `updated_at`,
    `joined_at`, `invited_by`
)
SELECT 
    `id`, 
    `joint_account_id`, 
    `user_id`, 
    CASE 
        WHEN `role` = 'owner' THEN 'owner'
        WHEN `role` = 'admin' THEN 'admin'
        WHEN `role` = 'viewer' THEN 'viewer'
        ELSE 'member'
    END as `role`,
    CASE 
        WHEN `status` = 'active' THEN 'active'
        WHEN `status` = 'suspended' THEN 'suspended'
        WHEN `status` = 'removed' THEN 'removed'
        ELSE 'pending'
    END as `status`,
    CAST(`permissions` AS CHAR) as `permissions`,
    `created_at`, 
    `updated_at`,
    `created_at` as `joined_at`,
    NULL as `invited_by`
FROM `old_database`.`joint_account_members`
ON DUPLICATE KEY UPDATE `role` = VALUES(`role`), `status` = VALUES(`status`);

-- JOINT_ACCOUNT_TRANSACTIONS
-- Major changes: user_id -> initiated_by, type -> transaction_type, added approval fields
INSERT INTO `joint_account_transactions` (
    `id`, `joint_account_id`, `transaction_type`, `amount`, 
    `description`, `reference`, `status`, `created_at`, `updated_at`,
    `initiated_by`, `approval_data`, `metadata`, `approved_at`, `completed_at`
)
SELECT 
    `id`, 
    `joint_account_id`, 
    `type` as `transaction_type`,
    `amount`, 
    `description`, 
    `reference`, 
    CASE 
        WHEN `status` = 'completed' THEN 'completed'
        WHEN `status` = 'pending' THEN 'pending'
        WHEN `status` = 'approved' THEN 'approved'
        WHEN `status` = 'rejected' THEN 'rejected'
        ELSE 'pending'
    END as `status`,
    `created_at`, 
    `updated_at`,
    `user_id` as `initiated_by`,
    NULL as `approval_data`,
    NULL as `metadata`,
    CASE WHEN `status` = 'completed' THEN `updated_at` ELSE NULL END as `approved_at`,
    CASE WHEN `status` = 'completed' THEN `updated_at` ELSE NULL END as `completed_at`
FROM `old_database`.`joint_account_transactions`
ON DUPLICATE KEY UPDATE `amount` = VALUES(`amount`), `status` = VALUES(`status`);

-- TRANSFERS
-- New structure has additional fields
INSERT INTO `transfers` (
    `id`, `sender_id`, `recipient_id`, `amount`, `status`, 
    `reference`, `notes`, `wallet_type`, `transfer_date`, 
    `created_at`, `updated_at`,
    `user_id`, `destination`, `description`, `admin_notes`, `processed_at`
)
SELECT 
    `id`, 
    `sender_id`, 
    `recipient_id`, 
    `amount`, 
    `status`, 
    `reference`, 
    `notes`, 
    `wallet_type`, 
    `transfer_date`, 
    `created_at`, 
    `updated_at`,
    `sender_id` as `user_id`,
    NULL as `destination`,
    NULL as `description`,
    NULL as `admin_notes`,
    CASE WHEN `status` != 'pending' THEN `updated_at` ELSE NULL END as `processed_at`
FROM `old_database`.`transfers`
ON DUPLICATE KEY UPDATE `amount` = VALUES(`amount`), `status` = VALUES(`status`);

-- PRODUCTS (same structure)
INSERT INTO `products` (
    `id`, `name`, `category_id`, `unit_price`, `qty`, `timer`, 
    `description`, `short_description`, `product_image`, `unit_profit`, 
    `ingredients`, `nutritional_value`, `cost_per_price`, `meal_type`, 
    `created_at`, `updated_at`
)
SELECT `id`, `name`, `category_id`, `unit_price`, `qty`, `timer`, 
       `description`, `short_description`, `product_image`, `unit_profit`, 
       `ingredients`, `nutritional_value`, `cost_per_price`, `meal_type`, 
       `created_at`, `updated_at`
FROM `old_database`.`products`
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`);

-- PLANS (same structure)
INSERT INTO `plans` (
    `id`, `name`, `amount`, `delivery_fee`, `min_amount`, `max_amount`, 
    `duration`, `num_meals_per_day`, `created_at`, `updated_at`
)
SELECT `id`, `name`, `amount`, `delivery_fee`, `min_amount`, `max_amount`, 
       `duration`, `num_meals_per_day`, `created_at`, `updated_at`
FROM `old_database`.`plans`
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`);

-- SUBSCRIPTIONS (same structure)
INSERT INTO `subscriptions` (
    `id`, `user_id`, `plan_id`, `custom_plan_id`, `amount`, `balance`, 
    `status`, `served_days`, `created_at`, `updated_at`
)
SELECT `id`, `user_id`, `plan_id`, `custom_plan_id`, `amount`, `balance`, 
       `status`, `served_days`, `created_at`, `updated_at`
FROM `old_database`.`subscriptions`
ON DUPLICATE KEY UPDATE `amount` = VALUES(`amount`), `status` = VALUES(`status`);

-- ORDERS (same structure)
INSERT INTO `orders` (
    `id`, `user_id`, `cart_id`, `custom_plan_id`, `status`, 
    `created_at`, `updated_at`
)
SELECT `id`, `user_id`, `cart_id`, `custom_plan_id`, `status`, 
       `created_at`, `updated_at`
FROM `old_database`.`orders`
ON DUPLICATE KEY UPDATE `status` = VALUES(`status`);

-- PLATES (same structure)
INSERT INTO `plates` (
    `id`, `user_id`, `content`, `subscription_id`, `amount`, `address`, 
    `other_details`, `phone`, `status`, `source`, `order_id`, 
    `created_at`, `updated_at`
)
SELECT `id`, `user_id`, `content`, `subscription_id`, `amount`, `address`, 
       `other_details`, `phone`, `status`, `source`, `order_id`, 
       `created_at`, `updated_at`
FROM `old_database`.`plates`
ON DUPLICATE KEY UPDATE `status` = VALUES(`status`);

-- WALLET_ADDRESSES
-- New structure has many additional fields
INSERT INTO `wallet_addresses` (
    `id`, `address`, `coin_name`, `created_at`, `updated_at`,
    `display_area`, `image`, `wallet_type`, `terms`, `percentage`,
    `orders`, `payment_description`, `max`, `min`, `quantity`, `trader_name`
)
SELECT 
    `id`, 
    `address`, 
    `coin_name`, 
    `created_at`, 
    `updated_at`,
    NULL as `display_area`,
    NULL as `image`,
    NULL as `wallet_type`,
    NULL as `terms`,
    NULL as `percentage`,
    NULL as `orders`,
    NULL as `payment_description`,
    NULL as `max`,
    NULL as `min`,
    NULL as `quantity`,
    NULL as `trader_name`
FROM `old_database`.`wallet_addresses`
ON DUPLICATE KEY UPDATE `address` = VALUES(`address`);

-- ROLES (same structure)
INSERT INTO `roles` (
    `id`, `user_id`, `role`, `password`, `created_at`, `updated_at`
)
SELECT `id`, `user_id`, `role`, `password`, `created_at`, `updated_at`
FROM `old_database`.`roles`
ON DUPLICATE KEY UPDATE `role` = VALUES(`role`);

-- SHAREHOLDERS (same structure)
INSERT INTO `shareholders` (
    `id`, `user_id`, `type`, `status`, `created_at`, `updated_at`
)
SELECT `id`, `user_id`, `type`, `status`, `created_at`, `updated_at`
FROM `old_database`.`shareholders`
ON DUPLICATE KEY UPDATE `status` = VALUES(`status`);

-- OTHER SIMPLE TABLES
INSERT INTO `breakfasts` SELECT * FROM `old_database`.`breakfasts` 
ON DUPLICATE KEY UPDATE `status` = VALUES(`status`);

INSERT INTO `dinners` SELECT * FROM `old_database`.`dinners` 
ON DUPLICATE KEY UPDATE `status` = VALUES(`status`);

INSERT INTO `lunches` SELECT * FROM `old_database`.`lunches` 
ON DUPLICATE KEY UPDATE `status` = VALUES(`status`);

INSERT INTO `featureds` SELECT * FROM `old_database`.`featureds`
ON DUPLICATE KEY UPDATE `product_id` = VALUES(`product_id`);

INSERT INTO `loans` SELECT * FROM `old_database`.`loans`
ON DUPLICATE KEY UPDATE `amount` = VALUES(`amount`);

INSERT INTO `payments` SELECT * FROM `old_database`.`payments`
ON DUPLICATE KEY UPDATE `status` = VALUES(`status`);

INSERT INTO `weekly_menus` SELECT * FROM `old_database`.`weekly_menus`
ON DUPLICATE KEY UPDATE `date` = VALUES(`date`);

-- SYSTEM TABLES (if needed)
INSERT INTO `password_resets` SELECT * FROM `old_database`.`password_resets`
ON DUPLICATE KEY UPDATE `token` = VALUES(`token`);

INSERT INTO `personal_access_tokens` SELECT * FROM `old_database`.`personal_access_tokens`
ON DUPLICATE KEY UPDATE `token` = VALUES(`token`);

INSERT INTO `notifications` SELECT * FROM `old_database`.`notifications`
ON DUPLICATE KEY UPDATE `read_at` = VALUES(`read_at`);

-- =====================================================
-- MIGRATION COMPLETE
-- =====================================================

SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================
-- POST-MIGRATION VERIFICATION QUERIES
-- =====================================================
-- Run these queries to verify the migration:

-- SELECT COUNT(*) as total_users FROM users;
-- SELECT COUNT(*) as total_investments FROM investments;
-- SELECT COUNT(*) as total_wallets FROM wallets;
-- SELECT COUNT(*) as total_fundings FROM fundings;
-- SELECT COUNT(*) as total_withdrawals FROM withdrawals;

-- Verify user-investment relationships:
-- SELECT u.id, u.email, 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 
-- ORDER BY investment_count DESC;

-- Verify wallet balances:
-- SELECT SUM(CAST(amount AS DECIMAL(20,4))) as total_wallet_amount FROM wallets;
