complaint-system/sql/init_dict_data.sql

-- ============================================
-- 字典数据初始化脚本（幂等性版本）
-- ============================================

-- 1. 检查并插入 dict_type
DECLARE
    v_count INT;
BEGIN
    SELECT COUNT(*) INTO v_count FROM sys_dict_type WHERE dict_type = 'complaint_category';
    IF v_count = 0 THEN
        INSERT INTO sys_dict_type (dict_id, dict_name, dict_type, status, create_by, create_time, remark)
        VALUES (100, '投诉分类', 'complaint_category', '0', 'admin', sysdate, '教育投诉分类');
        DBMS_OUTPUT.PUT_LINE('✓ 已创建字典类型：complaint_category');
    ELSE
        DBMS_OUTPUT.PUT_LINE('⚠️ 字典类型 complaint_category 已存在');
    END IF;
    
    SELECT COUNT(*) INTO v_count FROM sys_dict_type WHERE dict_type = 'school_type';
    IF v_count = 0 THEN
        INSERT INTO sys_dict_type (dict_id, dict_name, dict_type, status, create_by, create_time, remark)
        VALUES (101, '学校类别', 'school_type', '0', 'admin', sysdate, '学校类别（映射东软 DWLX）');
        DBMS_OUTPUT.PUT_LINE('✓ 已创建字典类型：school_type');
    ELSE
        DBMS_OUTPUT.PUT_LINE('⚠️ 字典类型 school_type 已存在');
    END IF;
END;
/

-- 2. 检查并插入 dict_data（投诉分类）
DECLARE
    v_count INT;
BEGIN
    -- 违规收费
    SELECT COUNT(*) INTO v_count FROM sys_dict_data WHERE dict_type = 'complaint_category' AND dict_value = '违规收费';
    IF v_count = 0 THEN
        INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, is_default, status, create_by, create_time, remark)
        VALUES (500, 1, '违规收费', '违规收费', 'complaint_category', 'N', '0', 'admin', sysdate, '');
    END IF;
    
    -- 食品安全
    SELECT COUNT(*) INTO v_count FROM sys_dict_data WHERE dict_type = 'complaint_category' AND dict_value = '食品安全';
    IF v_count = 0 THEN
        INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, is_default, status, create_by, create_time, remark)
        VALUES (501, 2, '食品安全', '食品安全', 'complaint_category', 'N', '0', 'admin', sysdate, '');
    END IF;
    
    -- 补课违规
    SELECT COUNT(*) INTO v_count FROM sys_dict_data WHERE dict_type = 'complaint_category' AND dict_value = '补课违规';
    IF v_count = 0 THEN
        INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, is_default, status, create_by, create_time, remark)
        VALUES (502, 3, '补课违规', '补课违规', 'complaint_category', 'N', '0', 'admin', sysdate, '');
    END IF;
    
    -- 师德师风
    SELECT COUNT(*) INTO v_count FROM sys_dict_data WHERE dict_type = 'complaint_category' AND dict_value = '师德师风';
    IF v_count = 0 THEN
        INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, is_default, status, create_by, create_time, remark)
        VALUES (503, 4, '师德师风', '师德师风', 'complaint_category', 'N', '0', 'admin', sysdate, '');
    END IF;
    
    -- 招生违规
    SELECT COUNT(*) INTO v_count FROM sys_dict_data WHERE dict_type = 'complaint_category' AND dict_value = '招生违规';
    IF v_count = 0 THEN
        INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, is_default, status, create_by, create_time, remark)
        VALUES (504, 5, '招生违规', '招生违规', 'complaint_category', 'N', '0', 'admin', sysdate, '');
    END IF;
    
    -- 其他
    SELECT COUNT(*) INTO v_count FROM sys_dict_data WHERE dict_type = 'complaint_category' AND dict_value = '其他';
    IF v_count = 0 THEN
        INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, is_default, status, create_by, create_time, remark)
        VALUES (505, 6, '其他', '其他', 'complaint_category', 'N', '0', 'admin', sysdate, '');
    END IF;
    
    DBMS_OUTPUT.PUT_LINE('✓ 投诉分类字典数据已就绪');
END;
/

-- 3. 检查并插入 dict_data（学校类别）
DECLARE
    v_count INT;
BEGIN
    -- 幼儿园
    SELECT COUNT(*) INTO v_count FROM sys_dict_data WHERE dict_type = 'school_type' AND dict_value = '幼儿园';
    IF v_count = 0 THEN
        INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, is_default, status, create_by, create_time, remark)
        VALUES (510, 1, '幼儿园', '幼儿园', 'school_type', 'N', '0', 'admin', sysdate, 'DWLX=11');
    END IF;
    
    -- 小学
    SELECT COUNT(*) INTO v_count FROM sys_dict_data WHERE dict_type = 'school_type' AND dict_value = '小学';
    IF v_count = 0 THEN
        INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, is_default, status, create_by, create_time, remark)
        VALUES (511, 2, '小学', '小学', 'school_type', 'N', '0', 'admin', sysdate, 'DWLX=21');
    END IF;
    
    -- 初中
    SELECT COUNT(*) INTO v_count FROM sys_dict_data WHERE dict_type = 'school_type' AND dict_value = '初中';
    IF v_count = 0 THEN
        INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, is_default, status, create_by, create_time, remark)
        VALUES (512, 3, '初中', '初中', 'school_type', 'N', '0', 'admin', sysdate, 'DWLX=31');
    END IF;
    
    -- 高中
    SELECT COUNT(*) INTO v_count FROM sys_dict_data WHERE dict_type = 'school_type' AND dict_value = '高中';
    IF v_count = 0 THEN
        INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, is_default, status, create_by, create_time, remark)
        VALUES (513, 4, '高中', '高中', 'school_type', 'N', '0', 'admin', sysdate, 'DWLX=34');
    END IF;
    
    -- 中职
    SELECT COUNT(*) INTO v_count FROM sys_dict_data WHERE dict_type = 'school_type' AND dict_value = '中职';
    IF v_count = 0 THEN
        INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, is_default, status, create_by, create_time, remark)
        VALUES (514, 5, '中职', '中职', 'school_type', 'N', '0', 'admin', sysdate, 'DWLX=36');
    END IF;
    
    DBMS_OUTPUT.PUT_LINE('✓ 学校类别字典数据已就绪');
END;
/

COMMIT;

-- 4. 最终验证
SELECT '==== 字典数据统计 ====' as message;
SELECT dict_type, COUNT(*) as count FROM sys_dict_data 
WHERE dict_type IN ('complaint_category', 'school_type')
GROUP BY dict_type;