-- =====================================================
-- Database Merge Script for Taekwondo Databases
-- Merges 'taekwondo_db_live' and 'taekwondo_05072025' into 'final_taekwondo_db'
-- =====================================================

-- Set SQL mode and disable foreign key checks
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";
SET FOREIGN_KEY_CHECKS = 0;

-- =====================================================
-- STEP 1: Create final database
-- =====================================================
DROP DATABASE IF EXISTS `final_taekwondo_db`;
CREATE DATABASE `final_taekwondo_db` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `final_taekwondo_db`;

-- =====================================================
-- STEP 2: Copy all tables from both databases
-- =====================================================

-- Copy all tables from taekwondo_db_live
-- Note: You'll need to replace these with actual CREATE TABLE statements
-- from your databases. The following is a template:

-- Example for each table in taekwondo_db_live:
-- CREATE TABLE `table_name` LIKE `taekwondo_db_live`.`table_name`;
-- INSERT INTO `table_name` SELECT * FROM `taekwondo_db_live`.`table_name`;

-- Copy all tables from taekwondo_05072025 (except users which will be merged)
-- CREATE TABLE `table_name` LIKE `taekwondo_05072025`.`table_name`;
-- INSERT INTO `table_name` SELECT * FROM `taekwondo_05072025`.`table_name`;

-- =====================================================
-- STEP 3: Special handling for portal_users + users merge
-- =====================================================

-- Create portal_users table with combined structure
-- (This assumes portal_users exists in taekwondo_db_live)
CREATE TABLE `portal_users` LIKE `taekwondo_db_live`.`portal_users`;

-- Add any missing columns from users table if needed
-- ALTER TABLE `portal_users` ADD COLUMN `column_name` VARCHAR(255) NULL;

-- Insert data from portal_users
INSERT INTO `portal_users` SELECT * FROM `taekwondo_db_live`.`portal_users`;

-- Insert data from users table (mapping columns as needed)
-- INSERT INTO `portal_users` (id, name, email, ...) 
-- SELECT id, name, email, ... FROM `taekwondo_05072025`.`users`;

-- Copy users table as well (don't drop it)
CREATE TABLE `users` LIKE `taekwondo_05072025`.`users`;
INSERT INTO `users` SELECT * FROM `taekwondo_05072025`.`users`;

-- =====================================================
-- STEP 4: Migrate 'initial' column from user_profiles to roles
-- =====================================================

-- Check if user_profiles has 'initial' column and roles table exists
-- Add 'initial' column to roles if it doesn't exist
ALTER TABLE `roles` ADD COLUMN IF NOT EXISTS `initial` VARCHAR(10) NULL;

-- Copy 'initial' data from user_profiles to roles
UPDATE `roles` r 
INNER JOIN `user_profiles` up ON r.id = up.role_id 
SET r.initial = up.initial 
WHERE up.initial IS NOT NULL;

-- =====================================================
-- STEP 5: Post-merge operations
-- =====================================================

-- i) UPDATE portal_users SET current_role_id = NULLIF(profiles, '');
UPDATE `portal_users` SET current_role_id = NULLIF(profiles, '');

-- ii) ALTER TABLE portal_users CHANGE name
ALTER TABLE `portal_users` CHANGE `name` `name` VARCHAR(191) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NULL DEFAULT NULL;

-- iii) RENAME TABLE payment_gateway TO payment_gateways
RENAME TABLE `payment_gateway` TO `payment_gateways`;

-- =====================================================
-- STEP 6: Create user_roles table and populate from profiles
-- =====================================================

-- Create user_roles table if it doesn't exist
CREATE TABLE IF NOT EXISTS `user_roles` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `user_id` bigint unsigned NOT NULL,
    `role_id` int NOT NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `user_roles_user_id_index` (`user_id`),
    KEY `user_roles_role_id_index` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Clear existing user_roles if any
DELETE FROM `user_roles`;

-- =====================================================
-- STEP 7: Populate user_roles from portal_users.profiles
-- =====================================================

-- This requires a stored procedure to handle comma-separated values
DELIMITER $$

CREATE PROCEDURE PopulateUserRoles()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE user_id_var BIGINT;
    DECLARE profiles_var TEXT;
    DECLARE role_id_var INT;
    DECLARE profiles_cursor CURSOR FOR 
        SELECT id, profiles FROM portal_users 
        WHERE profiles IS NOT NULL AND profiles != '';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN profiles_cursor;
    
    read_loop: LOOP
        FETCH profiles_cursor INTO user_id_var, profiles_var;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- Split comma-separated values and insert
        SET profiles_var = CONCAT(profiles_var, ',');
        
        WHILE LOCATE(',', profiles_var) > 0 DO
            SET role_id_var = CAST(TRIM(SUBSTRING(profiles_var, 1, LOCATE(',', profiles_var) - 1)) AS UNSIGNED);
            SET profiles_var = SUBSTRING(profiles_var, LOCATE(',', profiles_var) + 1);
            
            IF role_id_var > 0 THEN
                INSERT INTO user_roles (user_id, role_id, created_at, updated_at)
                VALUES (user_id_var, role_id_var, NOW(), NOW());
            END IF;
        END WHILE;
        
    END LOOP;
    
    CLOSE profiles_cursor;
END$$

DELIMITER ;

-- Execute the procedure
CALL PopulateUserRoles();

-- Drop the procedure
DROP PROCEDURE PopulateUserRoles;

-- =====================================================
-- FINALIZATION
-- =====================================================

-- Re-enable foreign key checks
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;

-- Display completion message
SELECT 'Database merge completed successfully!' as Message;

-- Show table count in final database
SELECT COUNT(*) as Total_Tables FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'final_taekwondo_db';

-- Show portal_users count
SELECT COUNT(*) as Portal_Users_Count FROM portal_users;

-- Show user_roles count
SELECT COUNT(*) as User_Roles_Count FROM user_roles;