-- 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;