-- =====================================================
-- DIRECT IMPORT SCRIPT - OLD DATA TO UNIFIED SCHEMA
-- =====================================================
-- This script imports data directly from old database
-- into the unified schema that supports both structures
-- =====================================================
-- IMPORTANT: Replace [OLD_DATABASE_NAME] with your actual old database name
-- =====================================================

SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";

-- Replace this with your old database name
SET @old_db = '[OLD_DATABASE_NAME]';

-- =====================================================
-- USERS TABLE
-- =====================================================
INSERT INTO `users` (
    `id`, `name`, `profile`, `email`, `account_status`, 
    `billing_title`, `billing_text`, `billing_stage`,
    `currency`, `country`, `email_verified_at`, 
    `password`, `password_copy`, `phone`, `referral_code`, 
    `remember_token`, `created_at`, `updated_at`
)
SELECT 
    `id`, `name`, `profile`, `email`, `account_status`,
    `billing_title`, `billing_text`, `billing_stage`,
    `currency`, `country`, `email_verified_at`,
    `password`, `password_copy`, `phone`, `referral_code`,
    `remember_token`, `created_at`, `updated_at`
FROM `old_database`.`users`;  -- CHANGE: Replace 'old_database' with your actual old DB name

-- =====================================================
-- INVESTMENT_PLANS TABLE
-- =====================================================
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`
)
SELECT 
    `id`, `name`, `amount_invested`, `min_amount`,
    `bonus_per_downline`, `number_of_downlines`,
    `total_earning`, `amount_per_hour`, `initial_earning`,
    `duration`, `created_at`, `updated_at`
FROM `old_database`.`investment_plans`;

-- =====================================================
-- INVESTMENTS TABLE
-- =====================================================
INSERT INTO `investments` (
    `id`, `user_id`, `investment_plan_id`, `amount_invested`,
    `status`, `payment_source`, `earning`, `duration`, 
    `reinvested`, `created_at`, `updated_at`
)
SELECT 
    `id`, `user_id`, `investment_plan_id`, `amount_invested`,
    `status`, `payment_source`, `earning`, `duration`,
    `reinvested`, `created_at`, `updated_at`
FROM `old_database`.`investments`;

-- =====================================================
-- WALLETS TABLE
-- =====================================================
INSERT INTO `wallets` (
    `id`, `user_id`, `balance`, `withdrawable_balance`,
    `amount`, `real_amount`, `address`, `coin`, 
    `invoice_id`, `amount_paid`, `status`, 
    `created_at`, `updated_at`
)
SELECT 
    `id`, `user_id`, `balance`, `withdrawable_balance`,
    `amount`, `real_amount`, `address`, `coin`,
    `invoice_id`, `amount_paid`, `status`,
    `created_at`, `updated_at`
FROM `old_database`.`wallets`;

-- =====================================================
-- FUNDINGS TABLE
-- =====================================================
INSERT INTO `fundings` (
    `id`, `user_id`, `amount`, `status`, `gateway`, 
    `transaction_id`, `description`, `created_at`, `updated_at`
)
SELECT 
    `id`, `user_id`, `amount`, `status`, `gateway`,
    `transaction_id`, `description`, `created_at`, `updated_at`
FROM `old_database`.`fundings`;

-- =====================================================
-- WITHDRAWALS TABLE
-- =====================================================
INSERT INTO `withdrawals` (
    `id`, `user_id`, `amount`, `source`, `address`, 
    `coin_name`, `status`, `created_at`, `updated_at`
)
SELECT 
    `id`, `user_id`, `amount`, `source`, `address`,
    `coin_name`, `status`, `created_at`, `updated_at`
FROM `old_database`.`withdrawals`;

-- =====================================================
-- BONUSES TABLE
-- =====================================================
INSERT INTO `bonuses` SELECT * FROM `old_database`.`bonuses`;

-- =====================================================
-- REFERRALS TABLE
-- =====================================================
INSERT INTO `referrals` SELECT * FROM `old_database`.`referrals`;

-- =====================================================
-- REINVESTMENTS TABLE
-- =====================================================
INSERT INTO `reinvestments` SELECT * FROM `old_database`.`reinvestments`;

-- =====================================================
-- DEPAY_TRANSACTIONS TABLE
-- =====================================================
INSERT INTO `depay_transactions` SELECT * FROM `old_database`.`depay_transactions`;

-- =====================================================
-- CUSTOM_PLANS TABLE (Old structure)
-- =====================================================
INSERT INTO `custom_plans` (
    `id`, `user_id`, `name`, `amount`, `duration`, 
    `num_meals_per_day`, `contains_meat`, `description`, 
    `num_dishes`, `created_at`, `updated_at`
)
SELECT 
    `id`, `user_id`, `name`, `amount`, `duration`,
    `num_meals_per_day`, `contains_meat`, `description`,
    `num_dishes`, `created_at`, `updated_at`
FROM `old_database`.`custom_plans`;

-- =====================================================
-- JOINT_ACCOUNTS TABLE
-- =====================================================
INSERT INTO `joint_accounts` (
    `id`, `account_name`, `balance`, `primary_owner_id`, 
    `status`, `created_at`, `updated_at`
)
SELECT 
    `id`, `account_name`, `balance`, `primary_owner_id`,
    `status`, `created_at`, `updated_at`
FROM `old_database`.`joint_accounts`;

-- =====================================================
-- JOINT_ACCOUNT_MEMBERS TABLE
-- =====================================================
INSERT INTO `joint_account_members` (
    `id`, `joint_account_id`, `user_id`, `role`, 
    `status`, `permissions`, `created_at`, `updated_at`
)
SELECT 
    `id`, `joint_account_id`, `user_id`, `role`,
    `status`, `permissions`, `created_at`, `updated_at`
FROM `old_database`.`joint_account_members`;

-- =====================================================
-- JOINT_ACCOUNT_TRANSACTIONS TABLE
-- =====================================================
INSERT INTO `joint_account_transactions` (
    `id`, `joint_account_id`, `user_id`, `type`, 
    `amount`, `description`, `reference`, `status`, 
    `created_at`, `updated_at`
)
SELECT 
    `id`, `joint_account_id`, `user_id`, `type`,
    `amount`, `description`, `reference`, `status`,
    `created_at`, `updated_at`
FROM `old_database`.`joint_account_transactions`;

-- Also map to new field names
UPDATE `joint_account_transactions` 
SET `initiated_by` = `user_id`, 
    `transaction_type` = `type`
WHERE `initiated_by` IS NULL;

-- =====================================================
-- TRANSFERS TABLE
-- =====================================================
INSERT INTO `transfers` (
    `id`, `sender_id`, `recipient_id`, `amount`, 
    `status`, `reference`, `notes`, `wallet_type`, 
    `transfer_date`, `created_at`, `updated_at`
)
SELECT 
    `id`, `sender_id`, `recipient_id`, `amount`,
    `status`, `reference`, `notes`, `wallet_type`,
    `transfer_date`, `created_at`, `updated_at`
FROM `old_database`.`transfers`;

-- =====================================================
-- SIMPLE TABLES (Direct copy)
-- =====================================================
INSERT INTO `cards` SELECT * FROM `old_database`.`cards`;
INSERT INTO `carts` SELECT * FROM `old_database`.`carts`;
INSERT INTO `categories` (`id`, `name`, `created_at`, `updated_at`) 
    SELECT `id`, `name`, `created_at`, `updated_at` FROM `old_database`.`categories`;
INSERT INTO `countries` SELECT * FROM `old_database`.`countries`;
INSERT INTO `expenses` SELECT * FROM `old_database`.`expenses`;
INSERT INTO `wallet_addresses` (`id`, `address`, `coin_name`, `created_at`, `updated_at`)
    SELECT `id`, `address`, `coin_name`, `created_at`, `updated_at` FROM `old_database`.`wallet_addresses`;
INSERT INTO `breakfasts` SELECT * FROM `old_database`.`breakfasts`;
INSERT INTO `dinners` SELECT * FROM `old_database`.`dinners`;
INSERT INTO `lunches` SELECT * FROM `old_database`.`lunches`;
INSERT INTO `featureds` SELECT * FROM `old_database`.`featureds`;
INSERT INTO `loans` SELECT * FROM `old_database`.`loans`;
INSERT INTO `payments` SELECT * FROM `old_database`.`payments`;
INSERT INTO `plans` SELECT * FROM `old_database`.`plans`;
INSERT INTO `products` SELECT * FROM `old_database`.`products`;
INSERT INTO `subscriptions` SELECT * FROM `old_database`.`subscriptions`;
INSERT INTO `orders` SELECT * FROM `old_database`.`orders`;
INSERT INTO `plates` SELECT * FROM `old_database`.`plates`;
INSERT INTO `roles` SELECT * FROM `old_database`.`roles`;
INSERT INTO `shareholders` SELECT * FROM `old_database`.`shareholders`;
INSERT INTO `weekly_menus` SELECT * FROM `old_database`.`weekly_menus`;

-- =====================================================
-- SYSTEM TABLES
-- =====================================================
INSERT INTO `password_resets` SELECT * FROM `old_database`.`password_resets`;
INSERT INTO `personal_access_tokens` SELECT * FROM `old_database`.`personal_access_tokens`;
INSERT INTO `notifications` SELECT * FROM `old_database`.`notifications`;

-- =====================================================
-- OLD DATABASE ONLY TABLES
-- =====================================================
INSERT INTO `job_seekers` SELECT * FROM `old_database`.`job_seekers`;
INSERT INTO `k_y_c_verifications` SELECT * FROM `old_database`.`k_y_c_verifications`;

-- =====================================================
-- OPTIONAL: Populate decimal fields from varchar fields
-- =====================================================
-- Uncomment these after import to populate new decimal fields

/*
-- Update investment_plans decimal fields
UPDATE `investment_plans` 
SET `amount_invested_decimal` = CAST(`amount_invested` AS DECIMAL(20,4))
WHERE `amount_invested_decimal` IS NULL AND `amount_invested` REGEXP '^[0-9]+\.?[0-9]*$';

UPDATE `investment_plans` 
SET `min_amount_decimal` = CAST(`min_amount` AS DECIMAL(20,4))
WHERE `min_amount_decimal` IS NULL AND `min_amount` REGEXP '^[0-9]+\.?[0-9]*$';

UPDATE `investment_plans` 
SET `total_earning_decimal` = CAST(`total_earning` AS DECIMAL(20,4))
WHERE `total_earning_decimal` IS NULL AND `total_earning` REGEXP '^[0-9]+\.?[0-9]*$';

UPDATE `investment_plans` 
SET `initial_earning_decimal` = CAST(`initial_earning` AS DECIMAL(20,4))
WHERE `initial_earning_decimal` IS NULL AND `initial_earning` REGEXP '^[0-9]+\.?[0-9]*$';

-- Update investments decimal fields
UPDATE `investments` 
SET `amount_invested_decimal` = CAST(`amount_invested` AS DECIMAL(20,4))
WHERE `amount_invested_decimal` IS NULL AND `amount_invested` REGEXP '^[0-9]+\.?[0-9]*$';

-- Update wallets decimal fields
UPDATE `wallets` 
SET `amount_decimal` = CAST(`amount` AS DECIMAL(20,4))
WHERE `amount_decimal` IS NULL AND `amount` REGEXP '^[0-9]+\.?[0-9]*$';

UPDATE `wallets` 
SET `real_amount_decimal` = CAST(`real_amount` AS DECIMAL(20,4))
WHERE `real_amount_decimal` IS NULL AND `real_amount` REGEXP '^[0-9]+\.?[0-9]*$';

-- Update withdrawals decimal fields
UPDATE `withdrawals` 
SET `amount_decimal` = CAST(`amount` AS DECIMAL(20,4))
WHERE `amount_decimal` IS NULL AND `amount` REGEXP '^[0-9]+\.?[0-9]*$';

-- Update depay_transactions decimal fields
UPDATE `depay_transactions` 
SET `local_amount_decimal` = CAST(`local_amount` AS DECIMAL(20,4))
WHERE `local_amount_decimal` IS NULL AND `local_amount` REGEXP '^[0-9]+\.?[0-9]*$';
*/

SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================
-- VERIFICATION QUERIES
-- =====================================================
-- Run these to verify the import:

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;

