-- Automated script to drop ALL unique indexes on referralCode
-- This finds and drops all unique indexes automatically

-- Step 1: Generate DROP statements for all unique indexes
SELECT CONCAT('ALTER TABLE `referrals` DROP INDEX `', INDEX_NAME, '`;') AS drop_statement
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'referrals'
AND COLUMN_NAME = 'referralCode'
AND NON_UNIQUE = 0  -- Only unique indexes
AND INDEX_NAME != 'PRIMARY';  -- Don't drop primary key

-- Step 2: Copy the output from Step 1 and run those DROP statements
-- OR use this dynamic approach:

SET @drops = (
  SELECT GROUP_CONCAT(CONCAT('ALTER TABLE `referrals` DROP INDEX `', INDEX_NAME, '`') SEPARATOR '; ')
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'referrals'
  AND COLUMN_NAME = 'referralCode'
  AND NON_UNIQUE = 0
  AND INDEX_NAME != 'PRIMARY'
);

-- Execute the drops (if @drops is not null)
SET @sql = IF(@drops IS NOT NULL, @drops, 'SELECT "No unique indexes found" AS message');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Step 3: Verify only non-unique index remains
SHOW INDEX FROM `referrals` WHERE Column_name = 'referralCode';