38 lines
1.7 KiB
SQL
38 lines
1.7 KiB
SQL
-- 兑换码功能数据库表
|
|
-- 执行方式: mysql -u root -p video_parser < database/redeem_codes.sql
|
|
|
|
-- 兑换码表
|
|
CREATE TABLE IF NOT EXISTS `redeem_codes` (
|
|
`id` INT PRIMARY KEY AUTO_INCREMENT,
|
|
`code` VARCHAR(32) NOT NULL UNIQUE COMMENT '兑换码',
|
|
`batch_id` VARCHAR(32) COMMENT '批次ID',
|
|
`target_group_id` INT NOT NULL COMMENT '目标用户组ID',
|
|
`duration_days` INT DEFAULT 30 COMMENT '有效期天数',
|
|
`is_used` TINYINT(1) DEFAULT 0 COMMENT '是否已使用',
|
|
`used_by` INT COMMENT '使用者用户ID',
|
|
`used_at` DATETIME COMMENT '使用时间',
|
|
`expires_at` DATETIME COMMENT '兑换码过期时间',
|
|
`remark` VARCHAR(255) COMMENT '备注',
|
|
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`target_group_id`) REFERENCES `user_groups`(`id`),
|
|
FOREIGN KEY (`used_by`) REFERENCES `users`(`id`),
|
|
INDEX `idx_code` (`code`),
|
|
INDEX `idx_batch_id` (`batch_id`),
|
|
INDEX `idx_is_used` (`is_used`),
|
|
INDEX `idx_expires_at` (`expires_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='兑换码表';
|
|
|
|
-- 用户组到期时间表
|
|
CREATE TABLE IF NOT EXISTS `user_group_expiry` (
|
|
`id` INT PRIMARY KEY AUTO_INCREMENT,
|
|
`user_id` INT NOT NULL UNIQUE COMMENT '用户ID',
|
|
`group_id` INT NOT NULL COMMENT '用户组ID',
|
|
`expires_at` DATETIME NOT NULL COMMENT '到期时间',
|
|
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`),
|
|
FOREIGN KEY (`group_id`) REFERENCES `user_groups`(`id`),
|
|
INDEX `idx_user_id` (`user_id`),
|
|
INDEX `idx_expires_at` (`expires_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户组到期时间表';
|