-- ----------------------------
-- 操行分审核表
-- ----------------------------
DROP TABLE IF EXISTS `xg_conduct_score_audit`;
CREATE TABLE `xg_conduct_score_audit` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `tenant_id` varchar(20) DEFAULT '000000' COMMENT '租户ID',
  `student_id` bigint(20) NOT NULL COMMENT '学生ID',
  `academic_year` int(11) NOT NULL COMMENT '学年',
  `semester` tinyint(1) NOT NULL COMMENT '学期：1-第一学期，2-第二学期',
  `month` tinyint(2) NOT NULL COMMENT '月份',
  `class_id` bigint(20) NOT NULL COMMENT '班级ID',
  `old_score` decimal(5,2) DEFAULT NULL COMMENT '原分数',
  `old_remark` varchar(500) DEFAULT NULL COMMENT '原备注',
  `new_score` decimal(5,2) NOT NULL COMMENT '新分数',
  `new_remark` varchar(500) DEFAULT NULL COMMENT '新备注',
  `apply_user_id` bigint(20) NOT NULL COMMENT '申请人ID',
  `apply_user_name` varchar(50) NOT NULL COMMENT '申请人姓名',
  `apply_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '申请时间',
  `apply_reason` varchar(200) DEFAULT NULL COMMENT '申请理由',
  `audit_status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '审核状态：0-待审核，1-审核通过，2-审核驳回',
  `audit_user_id` bigint(20) DEFAULT NULL COMMENT '审核人ID',
  `audit_user_name` varchar(50) DEFAULT NULL COMMENT '审核人姓名',
  `audit_time` datetime DEFAULT NULL COMMENT '审核时间',
  `audit_remark` varchar(200) DEFAULT NULL COMMENT '审核备注',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `deleted` tinyint(1) DEFAULT '0' COMMENT '删除标志',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_student_period` (`student_id`,`academic_year`,`semester`,`month`,`deleted`),
  KEY `idx_class_period` (`class_id`,`academic_year`,`semester`,`month`),
  KEY `idx_audit_status` (`audit_status`,`apply_time`),
  KEY `idx_apply_user` (`apply_user_id`,`apply_time`),
  KEY `idx_audit_user` (`audit_user_id`,`audit_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='操行分审核表';

-- ----------------------------
-- 为审核表添加必要的索引和约束
-- ----------------------------
ALTER TABLE `xg_conduct_score_audit`
ADD CONSTRAINT `fk_audit_student` FOREIGN KEY (`student_id`) REFERENCES `xg_student_manager` (`student_id`) ON DELETE CASCADE,
ADD CONSTRAINT `fk_audit_class` FOREIGN KEY (`class_id`) REFERENCES `xg_class_msg` (`class_id`) ON DELETE CASCADE,
ADD CONSTRAINT `fk_audit_apply_user` FOREIGN KEY (`apply_user_id`) REFERENCES `sys_user` (`user_id`),
ADD CONSTRAINT `fk_audit_user` FOREIGN KEY (`audit_user_id`) REFERENCES `sys_user` (`user_id`);

-- ----------------------------
-- 初始化审核表数据（可选）
-- ----------------------------
-- 将现有操行分数据标记为已审核状态（如果需要）
-- INSERT INTO xg_conduct_score_audit (
--   tenant_id, student_id, academic_year, semester, month, class_id,
--   old_score, old_remark, new_score, new_remark,
--   apply_user_id, apply_user_name, apply_time, apply_reason,
--   audit_status, audit_user_id, audit_user_name, audit_time, audit_remark,
--   create_by, create_time, update_by, update_time, deleted
-- )
-- SELECT
--   s.tenant_id, s.student_id, s.academic_year, s.semester, s.month, s.class_id,
--   NULL as old_score, NULL as old_remark, s.score as new_score, s.remark as new_remark,
--   s.class_teacher_id as apply_user_id, '系统导入' as apply_user_name, s.create_time as apply_time, '系统初始化' as apply_reason,
--   1 as audit_status, s.class_teacher_id as audit_user_id, '系统导入' as audit_user_name, s.create_time as audit_time, '系统初始化审核' as audit_remark,
--   s.create_by, s.create_time, s.update_by, s.update_time, s.deleted
-- FROM xg_conduct_score s
-- WHERE s.deleted = 0;