-- Migration: Fix referralCode UNIQUE constraint in referrals table
-- Issue: referralCode has UNIQUE constraint which prevents multiple people from using the same referral code
-- Solution: Remove UNIQUE constraint from referralCode in referrals table
-- Note: referralCode should remain UNIQUE in users table (each user has unique code), 
--       but NOT in referrals table (same code can be used by multiple people)

-- Find the unique index/constraint name for referralCode
SET @index_name = (
  SELECT INDEX_NAME 
  FROM INFORMATION_SCHEMA.STATISTICS 
  WHERE TABLE_SCHEMA = DATABASE() 
  AND TABLE_NAME = 'referrals' 
  AND COLUMN_NAME = 'referralCode'
  AND NON_UNIQUE = 0  -- NON_UNIQUE = 0 means it's a unique index
  LIMIT 1
);

-- Drop the unique index if found
SET @sql = IF(@index_name IS NOT NULL,
  CONCAT('ALTER TABLE `referrals` DROP INDEX `', @index_name, '`'),
  'SELECT "No unique index found on referralCode" AS message'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Check if non-unique index exists
SET @non_unique_exists = (
  SELECT COUNT(*) 
  FROM INFORMATION_SCHEMA.STATISTICS 
  WHERE TABLE_SCHEMA = DATABASE() 
  AND TABLE_NAME = 'referrals' 
  AND INDEX_NAME = 'idx_referral_code'
  AND NON_UNIQUE = 1  -- NON_UNIQUE = 1 means it's a regular (non-unique) index
);

-- Create non-unique index if it doesn't exist
SET @sql = IF(@non_unique_exists = 0,
  'CREATE INDEX `idx_referral_code` ON `referrals`(`referralCode`)',
  'SELECT "Non-unique index idx_referral_code already exists" AS message'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Verification: Show current indexes on referrals table
SELECT 
  INDEX_NAME,
  NON_UNIQUE,
  COLUMN_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'referrals'
AND COLUMN_NAME = 'referralCode';

-- Summary
SELECT 'Migration completed: referralCode UNIQUE constraint removed' AS status;