all-project/backend/script/sql/20-fix-student-fields-mapping.sql

-- 学生信息字段映射修复脚本
-- 确保所有字段都正确存在并具有正确的属性

USE hzdyjs_xg;

-- 检查并添加缺失的字段
-- 1. 宿舍信息字段
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS dormitory_building VARCHAR(50) COMMENT '宿舍楼栋';
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS dormitory_room VARCHAR(20) COMMENT '宿舍房间号';

-- 2. 健康信息字段
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS allergies TEXT COMMENT '过敏史';
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS medical_history TEXT COMMENT '病史';
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS health_status VARCHAR(20) DEFAULT '良好' COMMENT '健康状况';
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS blood_type VARCHAR(10) COMMENT '血型';

-- 3. 银行信息字段
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS bank_branch VARCHAR(100) COMMENT '开户支行';
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS card_holder_name VARCHAR(50) COMMENT '持卡人姓名';
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS bank_card_no VARCHAR(30) COMMENT '银行卡号';
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS bank_name VARCHAR(50) COMMENT '开户银行';
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS bank_city VARCHAR(50) COMMENT '开户城市';
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS bank_province VARCHAR(50) COMMENT '开户省份';
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS card_type VARCHAR(20) DEFAULT '储蓄卡' COMMENT '卡片类型';
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS card_status VARCHAR(20) DEFAULT '正常' COMMENT '卡片状态';
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS bank_card_remark TEXT COMMENT '银行卡备注';

-- 4. 紧急联系人信息字段
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS emergency_contact_name VARCHAR(50) COMMENT '紧急联系人姓名';
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS emergency_contact_phone VARCHAR(20) COMMENT '紧急联系人电话';
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS emergency_contact_relation VARCHAR(20) COMMENT '紧急联系人关系';

-- 5. 家庭信息字段
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS father_name VARCHAR(50) COMMENT '父亲姓名';
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS father_phone VARCHAR(20) COMMENT '父亲电话';
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS father_work_unit VARCHAR(100) COMMENT '父亲工作单位';
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS mother_name VARCHAR(50) COMMENT '母亲姓名';
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS mother_phone VARCHAR(20) COMMENT '母亲电话';
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS mother_work_unit VARCHAR(100) COMMENT '母亲工作单位';
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS family_income VARCHAR(20) COMMENT '家庭年收入';
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS family_members INT COMMENT '家庭成员数量';
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS family_economic_status VARCHAR(20) COMMENT '家庭经济状况';

-- 6. 监护人信息字段
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS guardian_name VARCHAR(50) COMMENT '监护人姓名';
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS guardian_phone VARCHAR(20) COMMENT '监护人电话';
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS guardian_relation VARCHAR(20) COMMENT '监护人关系';

-- 7. 体征信息字段
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS height DECIMAL(5,2) COMMENT '身高(cm)';
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS weight DECIMAL(5,2) COMMENT '体重(kg)';

-- 8. 特殊情况字段
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS is_poor_student TINYINT(1) DEFAULT 0 COMMENT '是否贫困生：0-否，1-是';
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS is_minority TINYINT(1) DEFAULT 0 COMMENT '是否少数民族：0-否，1-是';
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS is_disabled TINYINT(1) DEFAULT 0 COMMENT '是否残疾学生：0-否，1-是';
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS is_orphan TINYINT(1) DEFAULT 0 COMMENT '是否孤儿：0-否，1-是';
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS is_left_behind_child TINYINT(1) DEFAULT 0 COMMENT '是否留守儿童：0-否，1-是';
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS is_single_child TINYINT(1) DEFAULT 0 COMMENT '是否独生子女：0-否，1-是';

-- 9. 扩展信息字段
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS specialties TEXT COMMENT '特长爱好';
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS awards TEXT COMMENT '获奖情况';
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS violations TEXT COMMENT '违纪记录';
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS social_activities TEXT COMMENT '社会活动';

-- 10. 其他扩展字段
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS disability_type VARCHAR(50) COMMENT '残疾类型';
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS disability_level VARCHAR(20) COMMENT '残疾等级';
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS info_status TINYINT(1) DEFAULT 1 COMMENT '信息状态：1-正常，2-待审核，3-审核中，4-已驳回，5-已锁定';
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS approval_status TINYINT(1) DEFAULT 1 COMMENT '审批状态：1-待审批，2-审批中，3-已通过，4-已驳回';
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS audit_time DATETIME COMMENT '审核时间';
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS audit_by BIGINT(20) COMMENT '审核人员ID';
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS audit_remark TEXT COMMENT '审核备注';

-- 验证字段是否正确添加
SELECT
    COLUMN_NAME as '字段名',
    DATA_TYPE as '数据类型',
    IS_NULLABLE as '可空',
    COLUMN_DEFAULT as '默认值',
    COLUMN_COMMENT as '注释'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'hzdyjs_xg'
AND TABLE_NAME = 'xg_student_info'
AND COLUMN_NAME IN (
    'dormitory_building', 'dormitory_room', 'allergies', 'medical_history',
    'bank_branch', 'card_holder_name', 'emergency_contact_name',
    'father_name', 'mother_name', 'family_income', 'family_members',
    'height', 'weight', 'specialties', 'awards', 'violations', 'social_activities'
)
ORDER BY COLUMN_NAME;

-- 输出执行结果
SELECT '字段映射修复脚本执行完成' as '执行结果';
SELECT CONCAT('执行时间: ', NOW()) as '时间戳';