-- Drop existing foreign key constraints
ALTER TABLE `wallets` DROP FOREIGN KEY `wallets_authorId_fkey`;

-- Add new userId column
ALTER TABLE `wallets` ADD COLUMN `userId` VARCHAR(191) NULL AFTER `id`;

-- Migrate data from authorId to userId
UPDATE `wallets` w
JOIN `authors` a ON w.authorId = a.id
SET w.userId = a.userId;

-- Make userId NOT NULL after migration
ALTER TABLE `wallets` MODIFY `userId` VARCHAR(191) NOT NULL;

-- Add foreign key constraint for userId
ALTER TABLE `wallets` ADD CONSTRAINT `wallets_userId_fkey` 
  FOREIGN KEY (`userId`) REFERENCES `users`(`id`) ON DELETE CASCADE;

-- Add unique constraint on userId
ALTER TABLE `wallets` ADD UNIQUE INDEX `wallets_userId_unique` (`userId`);

-- Drop the old authorId column
ALTER TABLE `wallets` DROP COLUMN `authorId`;