-- Create affiliates table if it doesn't exist
CREATE TABLE IF NOT EXISTS `affiliates` (
  `id` varchar(36) NOT NULL,
  `userId` varchar(36) NOT NULL,
  `website` varchar(255) DEFAULT NULL,
  `promotionMethods` text,
  `notes` text,
  `status` enum('PENDING','APPROVED','REJECTED') NOT NULL DEFAULT 'PENDING',
  `referralCode` varchar(100) NOT NULL,
  `appliedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `commissionRate` decimal(5,2) NOT NULL DEFAULT 10.00,
  `totalEarnings` decimal(10,2) NOT NULL DEFAULT 0.00,
  `pendingPayout` decimal(10,2) NOT NULL DEFAULT 0.00,
  `totalPaid` decimal(10,2) NOT NULL DEFAULT 0.00,
  `createdAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  UNIQUE KEY `referralCode` (`referralCode`),
  KEY `userId` (`userId`),
  CONSTRAINT `affiliates_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create affiliate_referrals table for tracking referrals
CREATE TABLE IF NOT EXISTS `affiliate_referrals` (
  `id` varchar(36) NOT NULL,
  `affiliateId` varchar(36) NOT NULL,
  `referredUserId` varchar(36) DEFAULT NULL,
  `referralCode` varchar(100) NOT NULL,
  `status` enum('PENDING','COMPLETED','EXPIRED') NOT NULL DEFAULT 'PENDING',
  `earnings` decimal(10,2) DEFAULT 0.00,
  `createdAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  KEY `affiliateId` (`affiliateId`),
  KEY `referredUserId` (`referredUserId`),
  CONSTRAINT `affiliate_referrals_ibfk_1` FOREIGN KEY (`affiliateId`) REFERENCES `affiliates` (`id`) ON DELETE CASCADE,
  CONSTRAINT `affiliate_referrals_ibfk_2` FOREIGN KEY (`referredUserId`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;