TradeMate Dev
4ee0e546e3
- 加入缺失的 @testing-library/dom 依赖（RTL v16 必需 peer），修复 screen 导入 - 修正测试文件错误的相对导入路径（header 改具名导入；prompts/courses 指向 ../page） - 修正联盟/打赏迁移 SQL：Prisma 列名为 camelCase（仅 @@map 映射表名），原 snake_case 列名导致查询 500，已重建表 - npm run typecheck 现已通过（EXIT=0）
64 lines
2.7 KiB
SQL
64 lines
2.7 KiB
SQL
-- 联盟返佣系统：规范化数据模型
|
|
-- 替换原先手写的 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`;
|