-- Drop existing foreign key constraints if they exist
ALTER TABLE `wallets` DROP FOREIGN KEY IF EXISTS `wallets_authorId_fkey`;

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

-- Add walletType column with default 'AUTHOR'
ALTER TABLE `wallets` ADD COLUMN `walletType` ENUM('AUTHOR', 'AFFILIATE', 'PUBLISHER') NOT NULL DEFAULT 'AUTHOR' AFTER `userId`;

-- 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 and walletType
ALTER TABLE `wallets` ADD UNIQUE INDEX `wallets_userId_walletType_unique` (`userId`, `walletType`);

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

-- Add walletId column to affiliates table if not exists
ALTER TABLE `affiliates` ADD COLUMN IF NOT EXISTS `walletId` VARCHAR(191) NULL;

-- Create wallets for existing affiliates
INSERT IGNORE INTO `wallets` (`id`, `userId`, `walletType`, `balance`, `currency`, `isActive`, `createdAt`, `updatedAt`)
SELECT 
  UUID(),
  a.userId,
  'AFFILIATE',
  a.totalEarnings,
  'KES',
  TRUE,
  NOW(),
  NOW()
FROM `affiliates` a
LEFT JOIN `wallets` w ON a.userId = w.userId AND w.walletType = 'AFFILIATE'
WHERE w.id IS NULL;

-- Update affiliate records with walletId
UPDATE `affiliates` a
JOIN `wallets` w ON a.userId = w.userId AND w.walletType = 'AFFILIATE'
SET a.walletId = w.id
WHERE a.walletId IS NULL;

-- Add foreign key for affiliate wallet
ALTER TABLE `affiliates` ADD CONSTRAINT `affiliates_walletId_fkey`
  FOREIGN KEY (`walletId`) REFERENCES `wallets`(`id`) ON DELETE SET NULL;