-- 面试邀约补充字段与状态字典迁移脚本。 -- 适用于已创建 hr_interview_invitation 表的环境;执行前请先备份数据库。 -- status:0=待面试官确认,1=已确认,2=已取消,3=已完成。 ALTER TABLE hr_interview_invitation MODIFY COLUMN status char(1) NOT NULL DEFAULT '0' COMMENT '0待面试官确认 1已确认 2已取消 3已完成', MODIFY COLUMN remark varchar(500) DEFAULT NULL COMMENT '邀约备注或批量状态修改说明'; -- 面试结论不再使用独立表;一条邀约只对应一份结论,直接保存在 hr_interview_invitation。 ALTER TABLE hr_interview_invitation ADD COLUMN conclusion_result char(1) DEFAULT NULL COMMENT '面试结论:0通过 1待定 2不通过' AFTER status, ADD COLUMN conclusion_evaluation text COMMENT '面试评价' AFTER conclusion_result, ADD COLUMN conclusion_suggestion text COMMENT '后续处理建议' AFTER conclusion_evaluation, ADD COLUMN conclusion_by varchar(64) DEFAULT NULL COMMENT '结论录入人' AFTER conclusion_suggestion, ADD COLUMN conclusion_time datetime DEFAULT NULL COMMENT '结论录入时间' AFTER conclusion_by; -- 先迁移历史结论数据,确保删除旧表前不丢失数据。 UPDATE hr_interview_invitation i INNER JOIN hr_interview_conclusion c ON c.invitation_id = i.invitation_id SET i.conclusion_result = c.result, i.conclusion_evaluation = c.evaluation, i.conclusion_suggestion = c.suggestion, i.conclusion_by = c.create_by, i.conclusion_time = c.create_time; -- Offer 改为关联面试邀约;其面试结论通过 invitation_id 从邀约表读取。 ALTER TABLE hr_offer ADD COLUMN invitation_id bigint DEFAULT NULL COMMENT '面试邀约ID,结论直接保存在邀约表' AFTER resume_id; UPDATE hr_offer o INNER JOIN hr_interview_conclusion c ON c.conclusion_id = o.conclusion_id SET o.invitation_id = c.invitation_id; ALTER TABLE hr_offer DROP INDEX idx_hr_offer_conclusion, DROP COLUMN conclusion_id, MODIFY COLUMN invitation_id bigint NOT NULL COMMENT '面试邀约ID,结论直接保存在邀约表', ADD KEY idx_hr_offer_invitation (invitation_id); -- 仅在以上迁移全部成功后执行:删除已废弃的独立面试结论表。 DROP TABLE hr_interview_conclusion; -- 本期通知仅使用站内信和系统待办;不接入短信、企业微信。 CREATE TABLE IF NOT EXISTS hr_station_message ( message_id bigint NOT NULL AUTO_INCREMENT COMMENT '站内消息ID', user_id bigint NOT NULL COMMENT '接收人sys_user.user_id', message_type varchar(40) NOT NULL COMMENT '消息类型,例如interview_confirm', business_id bigint NOT NULL COMMENT '关联业务主键,例如面试邀约ID', title varchar(200) NOT NULL COMMENT '消息标题', content text COMMENT '消息正文', status char(1) NOT NULL DEFAULT '0' COMMENT '0未读 1已读', create_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', read_time datetime DEFAULT NULL COMMENT '阅读时间', PRIMARY KEY (message_id), UNIQUE KEY uk_hr_station_message_business_user (user_id,message_type,business_id), KEY idx_hr_station_message_user_status (user_id,status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='HR站内消息';