-- COMPLETE FIX: Remove all UNIQUE constraints from referralCode
-- Run these commands one by one

-- Step 1: Check what indexes/constraints exist on referralCode
SHOW INDEX FROM `referrals` WHERE Column_name = 'referralCode';

-- Step 2: Check the table structure
SHOW CREATE TABLE `referrals`;

-- Step 3: Remove UNIQUE from column definition (this is the main fix)
ALTER TABLE `referrals` MODIFY COLUMN `referralCode` VARCHAR(50) NOT NULL;

-- Step 4: Check all constraints on referralCode
SELECT 
    CONSTRAINT_NAME,
    CONSTRAINT_TYPE,
    COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'referrals'
AND COLUMN_NAME = 'referralCode';

-- Step 5: If any unique constraint exists, drop it by name
-- (Replace 'CONSTRAINT_NAME' with actual name from Step 4)
-- ALTER TABLE `referrals` DROP INDEX `CONSTRAINT_NAME`;

-- Step 6: Ensure non-unique index exists (will create if doesn't exist, or do nothing if exists)
-- First check if it exists
SELECT COUNT(*) as index_exists
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'referrals'
AND INDEX_NAME = 'idx_referral_code'
AND NON_UNIQUE = 1;

-- If index doesn't exist or is unique, create/recreate it:
CREATE INDEX IF NOT EXISTS `idx_referral_code` ON `referrals`(`referralCode`);

-- Note: MySQL doesn't support IF NOT EXISTS for CREATE INDEX in older versions
-- If you get an error, the index might already exist as non-unique, which is fine!

-- Step 7: Final verification
SHOW CREATE TABLE `referrals`;
SHOW INDEX FROM `referrals` WHERE Column_name = 'referralCode';