37 lines
1.7 KiB
SQL
37 lines
1.7 KiB
SQL
-- 创建陪伴员等级表
|
|
CREATE TABLE IF NOT EXISTS `teacher_level` (
|
|
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
|
`teacher_id` bigint(20) NOT NULL COMMENT '陪伴员ID',
|
|
`current_level` int(11) NOT NULL DEFAULT '1' COMMENT '当前等级(1-5)',
|
|
`level_name` varchar(50) DEFAULT NULL COMMENT '等级名称',
|
|
`upgrade_time` datetime DEFAULT NULL COMMENT '升级时间',
|
|
`next_level` int(11) DEFAULT NULL COMMENT '下一等级',
|
|
`can_upgrade` tinyint(1) DEFAULT '0' COMMENT '是否可以升级',
|
|
`exam_score` decimal(5,2) DEFAULT NULL COMMENT '考核分数',
|
|
`exam_passed` tinyint(1) DEFAULT '0' COMMENT '是否通过考核',
|
|
`video_count` int(11) DEFAULT '0' COMMENT '已上传视频数',
|
|
`required_video_count` int(11) DEFAULT '0' COMMENT '要求视频数',
|
|
`rating_score` decimal(3,2) DEFAULT NULL COMMENT '评分',
|
|
`service_count` int(11) DEFAULT '0' COMMENT '服务次数',
|
|
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
`tenant_id` bigint(20) DEFAULT '1' COMMENT '租户ID',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_teacher_id` (`teacher_id`),
|
|
KEY `idx_tenant_id` (`tenant_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='陪伴员等级表';
|
|
|
|
-- 为现有陪伴员初始化等级数据
|
|
INSERT INTO `teacher_level` (`teacher_id`, `current_level`, `level_name`, `can_upgrade`, `exam_passed`, `tenant_id`)
|
|
SELECT
|
|
id,
|
|
1 AS current_level,
|
|
'初级陪伴员' AS level_name,
|
|
0 AS can_upgrade,
|
|
1 AS exam_passed,
|
|
1 AS tenant_id
|
|
FROM `user`
|
|
WHERE user_type = 'teacher'
|
|
AND id NOT IN (SELECT teacher_id FROM `teacher_level`)
|
|
ON DUPLICATE KEY UPDATE update_time = CURRENT_TIMESTAMP;
|