-- First, let's drop the foreign key constraints temporarily
ALTER TABLE `_userfollows` DROP FOREIGN KEY IF EXISTS `_userfollows_ibfk_1`;
ALTER TABLE `_userfollows` DROP FOREIGN KEY IF EXISTS `_userfollows_ibfk_2`;

-- Drop the problematic unique constraint
ALTER TABLE `_userfollows` DROP INDEX IF EXISTS `_userfollows_AB_unique`;

-- Recreate the unique constraint with a different name
ALTER TABLE `_userfollows` ADD UNIQUE INDEX `_userfollows_unique` (`A`, `B`);

-- Re-add the foreign key constraints
ALTER TABLE `_userfollows` ADD CONSTRAINT `_userfollows_ibfk_1` FOREIGN KEY (`A`) REFERENCES `User`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `_userfollows` ADD CONSTRAINT `_userfollows_ibfk_2` FOREIGN KEY (`B`) REFERENCES `User`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- Now modify the User table to add the PENDING role
ALTER TABLE `User` 
MODIFY COLUMN `role` ENUM('PENDING', 'READER', 'AUTHOR', 'ADMIN', 'PUBLISHER', 'EDITOR', 'REVIEWER', 'AFFILIATE') NOT NULL DEFAULT 'PENDING';