all-project/backend/script/sql/fix-enrollment-college-fields.sql

-- 修复招生报名信息表中的学院相关字段
-- 版本: 1.0.0
-- 创建日期: 2025-07-25
-- 描述: 检查并添加学院名称和学院代码字段

-- 检查college_name字段是否存在，如果不存在则添加
SET @check_college_name = (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'zsjy_enrollment_info'
    AND COLUMN_NAME = 'college_name'
    AND TABLE_SCHEMA = DATABASE()
);

SET @alter_college_name = IF(@check_college_name = 0,
    'ALTER TABLE zsjy_enrollment_info ADD COLUMN college_name varchar(100) COMMENT \'学院名称\'',
    'SELECT \'college_name字段已存在，无需添加\' AS message');

PREPARE stmt FROM @alter_college_name;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 检查college_id字段是否存在，如果不存在则添加
SET @check_college_id = (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'zsjy_enrollment_info'
    AND COLUMN_NAME = 'college_id'
    AND TABLE_SCHEMA = DATABASE()
);

SET @alter_college_id = IF(@check_college_id = 0,
    'ALTER TABLE zsjy_enrollment_info ADD COLUMN college_id varchar(20) COMMENT \'学院代码\'',
    'SELECT \'college_id字段已存在，无需添加\' AS message');

PREPARE stmt FROM @alter_college_id;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 检查数据字典是否存在
SET @dict_exists = (
    SELECT COUNT(*)
    FROM sys_dict_type
    WHERE dict_type = 'zsjy_college'
);

-- 如果不存在则添加字典类型
SET @insert_dict_type = IF(@dict_exists = 0,
    'INSERT INTO sys_dict_type(dict_name, dict_type, status, create_by, create_time, update_by, update_time, remark) VALUES (\'学院列表\', \'zsjy_college\', \'0\', \'1\', NOW(), \'1\', NOW(), \'招生系统学院列表\')',
    'SELECT \'zsjy_college字典类型已存在，无需添加\' AS message');

PREPARE stmt FROM @insert_dict_type;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 检查字典数据是否存在
SET @dict_data_exists = (
    SELECT COUNT(*)
    FROM sys_dict_data
    WHERE dict_type = 'zsjy_college'
);

-- 如果不存在则添加示例字典数据
SET @insert_dict_data = IF(@dict_data_exists = 0,
    'INSERT INTO sys_dict_data(dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark)
    VALUES
    (1, \'机电工程学院\', \'JDGC\', \'zsjy_college\', null, \'default\', \'Y\', \'0\', \'1\', NOW(), \'1\', NOW(), null),
    (2, \'信息工程学院\', \'XXGC\', \'zsjy_college\', null, \'default\', \'N\', \'0\', \'1\', NOW(), \'1\', NOW(), null),
    (3, \'艺术设计学院\', \'YSSJ\', \'zsjy_college\', null, \'default\', \'N\', \'0\', \'1\', NOW(), \'1\', NOW(), null),
    (4, \'经济管理学院\', \'JJGL\', \'zsjy_college\', null, \'default\', \'N\', \'0\', \'1\', NOW(), \'1\', NOW(), null),
    (5, \'医药健康学院\', \'YYJK\', \'zsjy_college\', null, \'default\', \'N\', \'0\', \'1\', NOW(), \'1\', NOW(), null)',
    'SELECT \'zsjy_college字典数据已存在，无需添加\' AS message');

PREPARE stmt FROM @insert_dict_data;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SELECT '招生报名信息表字段修复完成!' AS result;