all-project/backend/sql/test_missing_fields_fix.sql

-- =====================================================
-- 奖学金类型表字段修复验证脚本
-- 创建时间: 2025-08-04
-- 说明: 验证申请条件、评定标准、备注字段是否正确添加和工作
-- =====================================================

-- 1. 验证字段是否存在
SELECT
    COLUMN_NAME as '字段名',
    DATA_TYPE as '数据类型',
    CHARACTER_MAXIMUM_LENGTH as '最大长度',
    IS_NULLABLE as '是否可空',
    COLUMN_DEFAULT as '默认值',
    COLUMN_COMMENT as '字段注释'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'jcbt_scholarship_type'
  AND COLUMN_NAME IN ('application_conditions', 'evaluation_criteria', 'remark')
ORDER BY ORDINAL_POSITION;

-- 2. 查看表结构（包含新增字段的完整表结构）
DESCRIBE jcbt_scholarship_type;

-- 3. 测试插入数据（包含新增字段）
INSERT INTO jcbt_scholarship_type (
    id, type_code, type_name, type_category, level_type, award_amount,
    description, application_guide, application_conditions, evaluation_criteria, remark,
    sort_order, status, is_system, compatibility_type,
    create_by, create_time, update_by, update_time, tenant_id, deleted
) VALUES (
    1001, 'TEST_001', '测试奖学金', 'ACADEMIC', 'FIRST_CLASS', 5000.00,
    '这是一个测试奖学金描述',
    '请按照以下步骤申请：\n1. 填写申请表\n2. 提交相关材料',
    '申请条件：\n1. 学习成绩优秀，GPA≥3.5\n2. 无违纪记录\n3. 积极参与社会实践',
    '评定标准：\n1. 学习成绩占60%\n2. 综合素质占30%\n3. 社会实践占10%',
    '备注：本奖学金每学期评选一次，优秀学生可连续获得',
    1, 1, 0, 'COMPATIBLE',
    1, NOW(), 1, NOW(), '000000', 0
);

-- 4. 验证数据插入是否成功
SELECT
    id, type_code, type_name,
    LEFT(application_conditions, 50) as '申请条件_前50字符',
    LEFT(evaluation_criteria, 50) as '评定标准_前50字符',
    LEFT(remark, 50) as '备注_前50字符'
FROM jcbt_scholarship_type
WHERE type_code = 'TEST_001';

-- 5. 测试更新操作
UPDATE jcbt_scholarship_type
SET
    application_conditions = '更新后的申请条件：\n1. GPA≥3.8\n2. 英语四级以上\n3. 无挂科记录',
    evaluation_criteria = '更新后的评定标准：\n1. 学习成绩70%\n2. 英语水平20%\n3. 综合表现10%',
    remark = '更新后的备注：此奖学金面向大二及以上学生',
    update_time = NOW()
WHERE type_code = 'TEST_001';

-- 6. 验证更新是否成功
SELECT
    id, type_code, type_name,
    application_conditions as '申请条件',
    evaluation_criteria as '评定标准',
    remark as '备注',
    update_time as '更新时间'
FROM jcbt_scholarship_type
WHERE type_code = 'TEST_001';

-- 7. 查询所有奖学金类型的这三个字段（查看现有数据）
SELECT
    id, type_code, type_name,
    CASE
        WHEN application_conditions IS NULL THEN '空值'
        WHEN application_conditions = '' THEN '空字符串'
        ELSE CONCAT('有值-', LEFT(application_conditions, 20), '...')
    END as '申请条件状态',
    CASE
        WHEN evaluation_criteria IS NULL THEN '空值'
        WHEN evaluation_criteria = '' THEN '空字符串'
        ELSE CONCAT('有值-', LEFT(evaluation_criteria, 20), '...')
    END as '评定标准状态',
    CASE
        WHEN remark IS NULL THEN '空值'
        WHEN remark = '' THEN '空字符串'
        ELSE CONCAT('有值-', LEFT(remark, 20), '...')
    END as '备注状态'
FROM jcbt_scholarship_type
WHERE deleted = 0
ORDER BY sort_order, create_time DESC;

-- 8. 清理测试数据
DELETE FROM jcbt_scholarship_type WHERE type_code = 'TEST_001';

-- 9. 验证清理结果
SELECT COUNT(*) as '清理后剩余测试数据数量'
FROM jcbt_scholarship_type
WHERE type_code = 'TEST_001';

-- =====================================================
-- 测试完成提示
-- =====================================================
SELECT
    '字段修复验证完成' as '状态',
    '如果以上查询都正常执行，说明字段修复成功' as '说明',
    '现在可以重启后端服务测试详情页面显示' as '下一步';