-- Migration Script: Change referred_by from user_id (INT) to refercode (VARCHAR)
-- Date: 2025-11-18
-- Description: Updates referred_by column to store refercode instead of user_id

-- Step 1: Create a temporary column to store refercodes
ALTER TABLE `users` ADD COLUMN `referred_by_temp` VARCHAR(8) DEFAULT NULL;

-- Step 2: Update existing referred_by values from user_id to refercode
-- This converts all existing user_id references to their corresponding refercodes
UPDATE `users` u1
INNER JOIN `users` u2 ON u1.`referred_by` = u2.`id`
SET u1.`referred_by_temp` = u2.`refercode`
WHERE u1.`referred_by` IS NOT NULL;

-- Step 3: Drop the old referred_by column
ALTER TABLE `users` DROP COLUMN `referred_by`;

-- Step 4: Rename the temporary column to referred_by
ALTER TABLE `users` CHANGE COLUMN `referred_by_temp` `referred_by` VARCHAR(8) DEFAULT NULL;

-- Step 5: Update the index (if needed, the index will be automatically updated)
-- Note: The existing index on referred_by will work with VARCHAR as well

-- Verification query (run this to check the migration):
-- SELECT id, email, refercode, referred_by FROM users WHERE referred_by IS NOT NULL;

