-- 联盟返佣系统：规范化数据模型
-- 替换原先手写的 affiliate_stats / affiliate_clicks 裸表，改用 ORM 管理
-- 注意：Prisma 字段名即为列名（camelCase），仅通过 @@map 映射表名为 snake_case

CREATE TABLE IF NOT EXISTS `affiliate_programs` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(191) NOT NULL,
  `slug` VARCHAR(191) NOT NULL,
  `provider` VARCHAR(191),
  `description` TEXT,
  `commissionRate` VARCHAR(191),
  `cookieDays` INT,
  `homepageUrl` VARCHAR(191),
  `logo` VARCHAR(191),
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  `sortOrder` INT NOT NULL DEFAULT 0,
  `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 INDEX `affiliate_programs_slug_key` (`slug`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `affiliate_links` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `programId` INT NOT NULL,
  `title` VARCHAR(191) NOT NULL,
  `description` TEXT,
  `url` TEXT NOT NULL,
  `thumbnail` VARCHAR(191),
  `toolId` INT,
  `skillId` VARCHAR(191),
  `tags` TEXT,
  `estimatedCommission` DOUBLE NOT NULL DEFAULT 0,
  `sortOrder` INT NOT NULL DEFAULT 0,
  `isFeatured` TINYINT(1) NOT NULL DEFAULT 0,
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  `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`),
  INDEX `affiliate_links_programId_idx` (`programId`),
  INDEX `affiliate_links_toolId_idx` (`toolId`),
  INDEX `affiliate_links_skillId_idx` (`skillId`),
  CONSTRAINT `affiliate_links_programId_fkey` FOREIGN KEY (`programId`) REFERENCES `affiliate_programs` (`id`) ON DELETE CASCADE,
  CONSTRAINT `affiliate_links_toolId_fkey` FOREIGN KEY (`toolId`) REFERENCES `tools` (`id`) ON DELETE SET NULL,
  CONSTRAINT `affiliate_links_skillId_fkey` FOREIGN KEY (`skillId`) REFERENCES `skills` (`id`) ON DELETE SET NULL
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `affiliate_clicks` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `linkId` INT NOT NULL,
  `userId` INT,
  `ip` VARCHAR(191),
  `userAgent` TEXT,
  `ref` VARCHAR(191),
  `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  INDEX `affiliate_clicks_linkId_idx` (`linkId`),
  CONSTRAINT `affiliate_clicks_linkId_fkey` FOREIGN KEY (`linkId`) REFERENCES `affiliate_links` (`id`) ON DELETE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 清理旧的手写裸表（被本迁移的规范化模型取代）
DROP TABLE IF EXISTS `affiliate_stats`;
DROP TABLE IF EXISTS `affiliate_clicks_old`;