-- ============================================================
-- 湛江市智慧人社运营运维项目
-- 综合修复见习人员管理模块问题
-- 修复内容：
--   1. 修复 sys_permission 按钮权限 status=NULL 问题（导出按钮+服务跟进按钮不显示的根本原因）
--   2. 修复服务跟进权限ID冲突问题
--   3. 确保导出权限和服务跟进权限已授权给admin角色
--   4. 确保 DICTIONARY/DICTIONARY_ITEM 字典数据存在（自定义标签不显示的原因）
-- 数据库：达梦数据库 (DM8) 兼容（不加反引号，使用 FROM DUAL 和 NEWID）
-- 创建日期：2026-06-05
-- ============================================================

-- ============================================================
-- 第一部分：修复权限 status 字段（核心问题）
-- 后端 SysPermissionController 构建 codeList 时要求 status='1'
-- 但 V20260603_6 插入的见习人员权限 status=NULL，导致权限码不返回
-- V20260604_9 只修复了重点关注人员，遗漏了见习人员
-- ============================================================

-- 1.1 修复见习人员所有按钮权限的 status 为 1
UPDATE sys_permission
SET status = 1
WHERE perms IN (
    'internship_personnel:add',
    'internship_personnel:edit',
    'internship_personnel:delete',
    'internship_personnel:deleteBatch',
    'internship_personnel:exportXls',
    'internship_personnel:importExcel',
    'internship_personnel:serviceFollow',
    'internship_personnel:messagePush',
    'internship_personnel:jobPush',
    'internship_personnel:customTag'
)
  AND (status IS NULL OR status != 1);

-- ============================================================
-- 第二部分：修复服务跟进权限ID冲突
-- V20260603_6 中角色授权ID 1780601000000327 与服务跟进权限ID冲突
-- ============================================================

-- 2.1 清理冲突的旧数据
DELETE FROM sys_role_permission WHERE permission_id = '1780601000000327';
DELETE FROM sys_permission WHERE id = '1780601000000327' AND perms = 'internship_personnel:serviceFollow';

-- 2.2 插入服务跟进权限（使用不冲突的ID，status=1）
INSERT INTO sys_permission (id, parent_id, name, url, component, is_route, component_name, redirect, menu_type, perms, perms_type, sort_no, always_show, icon, is_leaf, keep_alive, hidden, hide_tab, description, create_by, create_time, update_by, update_time, del_flag, rule_flag, status, internal_or_external)
SELECT '1780601000000340', '178060100000032', '服务跟进_见习人员管理', NULL, NULL, 0, '', NULL, 2, 'internship_personnel:serviceFollow', '1', NULL, 0, NULL, 1, 0, 0, 0, NULL, 'admin', '2026-06-05 10:00:00', NULL, NULL, 0, 0, 1, 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM sys_permission WHERE perms = 'internship_personnel:serviceFollow');

-- 2.3 授权服务跟进权限给admin角色
INSERT INTO sys_role_permission (id, role_id, permission_id, data_rule_ids, operate_date, operate_ip)
SELECT '1780601000000341', 'f6817f48af4fb3af11b9e8bf182f618b', '1780601000000340', NULL, '2026-06-05 10:00:00', '127.0.0.1'
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM sys_role_permission WHERE role_id = 'f6817f48af4fb3af11b9e8bf182f618b' AND permission_id = '1780601000000340');

-- 2.4 确保导出权限已授权给admin角色（权限ID 1780601000000325）
INSERT INTO sys_role_permission (id, role_id, permission_id, data_rule_ids, operate_date, operate_ip)
SELECT '1780601000000342', 'f6817f48af4fb3af11b9e8bf182f618b', '1780601000000325', NULL, '2026-06-05 10:00:00', '127.0.0.1'
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM sys_role_permission WHERE role_id = 'f6817f48af4fb3af11b9e8bf182f618b' AND permission_id = '1780601000000325');

-- ============================================================
-- 第三部分：确保 DICTIONARY/DICTIONARY_ITEM 字典数据存在
-- 自定义标签不显示的原因：V20260604_7 可能未成功执行（之前有CAST错误）
-- 前端 useDict 从 DICTIONARY_ITEM 表加载字典数据
-- ============================================================

-- 3.1 确保 DICTIONARY 表有见习人员标签字典
INSERT INTO DICTIONARY (DictionaryCode, Name, OrderNo, RecordStatus)
SELECT 'internship_major_tag', '见习人员大类标签', 4, 0 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY WHERE DictionaryCode = 'internship_major_tag');

INSERT INTO DICTIONARY (DictionaryCode, Name, OrderNo, RecordStatus)
SELECT 'internship_minor_tag', '见习人员小类标签', 5, 0 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY WHERE DictionaryCode = 'internship_minor_tag');

INSERT INTO DICTIONARY (DictionaryCode, Name, OrderNo, RecordStatus)
SELECT 'internship_custom_tag', '见习人员自定义标签', 6, 0 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY WHERE DictionaryCode = 'internship_custom_tag');

-- 3.2 确保 DICTIONARY_ITEM 表有见习人员大类标签（Value=1, Name='见习人员'）
INSERT INTO DICTIONARY_ITEM (DictionaryItemID, DictionaryCode, Code, Value, Name, OrderNo, RecordStatus, IsEditable)
SELECT NEWID(), 'internship_major_tag', '1', 1, '见习人员', 1, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY_ITEM WHERE DictionaryCode = 'internship_major_tag' AND Value = 1);

-- 3.3 确保 DICTIONARY_ITEM 表有见习人员小类标签
INSERT INTO DICTIONARY_ITEM (DictionaryItemID, DictionaryCode, Code, Value, Name, OrderNo, RecordStatus, IsEditable)
SELECT NEWID(), 'internship_minor_tag', '1', 1, '离校2年内未就业高校毕业生', 1, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY_ITEM WHERE DictionaryCode = 'internship_minor_tag' AND Value = 1);

INSERT INTO DICTIONARY_ITEM (DictionaryItemID, DictionaryCode, Code, Value, Name, OrderNo, RecordStatus, IsEditable)
SELECT NEWID(), 'internship_minor_tag', '2', 2, '16-24岁失业青年', 2, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY_ITEM WHERE DictionaryCode = 'internship_minor_tag' AND Value = 2);

-- 3.4 确保 DICTIONARY_ITEM 表有8条自定义标签
INSERT INTO DICTIONARY_ITEM (DictionaryItemID, DictionaryCode, Code, Value, Name, OrderNo, RecordStatus, IsEditable)
SELECT NEWID(), 'internship_custom_tag', '1', 1, '零就业家庭', 1, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY_ITEM WHERE DictionaryCode = 'internship_custom_tag' AND Value = 1);

INSERT INTO DICTIONARY_ITEM (DictionaryItemID, DictionaryCode, Code, Value, Name, OrderNo, RecordStatus, IsEditable)
SELECT NEWID(), 'internship_custom_tag', '2', 2, '低保家庭', 2, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY_ITEM WHERE DictionaryCode = 'internship_custom_tag' AND Value = 2);

INSERT INTO DICTIONARY_ITEM (DictionaryItemID, DictionaryCode, Code, Value, Name, OrderNo, RecordStatus, IsEditable)
SELECT NEWID(), 'internship_custom_tag', '3', 3, '残疾人员', 3, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY_ITEM WHERE DictionaryCode = 'internship_custom_tag' AND Value = 3);

INSERT INTO DICTIONARY_ITEM (DictionaryItemID, DictionaryCode, Code, Value, Name, OrderNo, RecordStatus, IsEditable)
SELECT NEWID(), 'internship_custom_tag', '4', 4, '长期失业', 4, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY_ITEM WHERE DictionaryCode = 'internship_custom_tag' AND Value = 4);

INSERT INTO DICTIONARY_ITEM (DictionaryItemID, DictionaryCode, Code, Value, Name, OrderNo, RecordStatus, IsEditable)
SELECT NEWID(), 'internship_custom_tag', '5', 5, '特困人员', 5, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY_ITEM WHERE DictionaryCode = 'internship_custom_tag' AND Value = 5);

INSERT INTO DICTIONARY_ITEM (DictionaryItemID, DictionaryCode, Code, Value, Name, OrderNo, RecordStatus, IsEditable)
SELECT NEWID(), 'internship_custom_tag', '6', 6, '刑满释放', 6, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY_ITEM WHERE DictionaryCode = 'internship_custom_tag' AND Value = 6);

INSERT INTO DICTIONARY_ITEM (DictionaryItemID, DictionaryCode, Code, Value, Name, OrderNo, RecordStatus, IsEditable)
SELECT NEWID(), 'internship_custom_tag', '7', 7, '社会救助', 7, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY_ITEM WHERE DictionaryCode = 'internship_custom_tag' AND Value = 7);

INSERT INTO DICTIONARY_ITEM (DictionaryItemID, DictionaryCode, Code, Value, Name, OrderNo, RecordStatus, IsEditable)
SELECT NEWID(), 'internship_custom_tag', '8', 8, '优抚对象', 8, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY_ITEM WHERE DictionaryCode = 'internship_custom_tag' AND Value = 8);

-- 3.5 确保 JobSeekerCategory（求职人员类别）字典数据存在
INSERT INTO DICTIONARY (DictionaryCode, Name, OrderNo, RecordStatus)
SELECT 'JobSeekerCategory', '求职人员类别', 7, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY WHERE DictionaryCode = 'JobSeekerCategory');

INSERT INTO DICTIONARY_ITEM (DictionaryItemID, DictionaryCode, Code, Value, Name, OrderNo, RecordStatus, IsEditable)
SELECT NEWID(), 'JobSeekerCategory', '1', 1, '新成长失业青年', 1, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY_ITEM WHERE DictionaryCode = 'JobSeekerCategory' AND Value = 1);

INSERT INTO DICTIONARY_ITEM (DictionaryItemID, DictionaryCode, Code, Value, Name, OrderNo, RecordStatus, IsEditable)
SELECT NEWID(), 'JobSeekerCategory', '2', 2, '应届高校毕业生', 2, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY_ITEM WHERE DictionaryCode = 'JobSeekerCategory' AND Value = 2);

INSERT INTO DICTIONARY_ITEM (DictionaryItemID, DictionaryCode, Code, Value, Name, OrderNo, RecordStatus, IsEditable)
SELECT NEWID(), 'JobSeekerCategory', '3', 3, '就业转失业人员', 3, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY_ITEM WHERE DictionaryCode = 'JobSeekerCategory' AND Value = 3);

INSERT INTO DICTIONARY_ITEM (DictionaryItemID, DictionaryCode, Code, Value, Name, OrderNo, RecordStatus, IsEditable)
SELECT NEWID(), 'JobSeekerCategory', '4', 4, '其他失业人员', 4, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY_ITEM WHERE DictionaryCode = 'JobSeekerCategory' AND Value = 4);

INSERT INTO DICTIONARY_ITEM (DictionaryItemID, DictionaryCode, Code, Value, Name, OrderNo, RecordStatus, IsEditable)
SELECT NEWID(), 'JobSeekerCategory', '5', 5, '本市农村人员', 5, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY_ITEM WHERE DictionaryCode = 'JobSeekerCategory' AND Value = 5);

INSERT INTO DICTIONARY_ITEM (DictionaryItemID, DictionaryCode, Code, Value, Name, OrderNo, RecordStatus, IsEditable)
SELECT NEWID(), 'JobSeekerCategory', '6', 6, '外埠人员', 6, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY_ITEM WHERE DictionaryCode = 'JobSeekerCategory' AND Value = 6);

INSERT INTO DICTIONARY_ITEM (DictionaryItemID, DictionaryCode, Code, Value, Name, OrderNo, RecordStatus, IsEditable)
SELECT NEWID(), 'JobSeekerCategory', '7', 7, '在业人员', 7, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY_ITEM WHERE DictionaryCode = 'JobSeekerCategory' AND Value = 7);

INSERT INTO DICTIONARY_ITEM (DictionaryItemID, DictionaryCode, Code, Value, Name, OrderNo, RecordStatus, IsEditable)
SELECT NEWID(), 'JobSeekerCategory', '8', 8, '下岗职工', 8, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY_ITEM WHERE DictionaryCode = 'JobSeekerCategory' AND Value = 8);

INSERT INTO DICTIONARY_ITEM (DictionaryItemID, DictionaryCode, Code, Value, Name, OrderNo, RecordStatus, IsEditable)
SELECT NEWID(), 'JobSeekerCategory', '9', 9, '退休人员', 9, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY_ITEM WHERE DictionaryCode = 'JobSeekerCategory' AND Value = 9);

INSERT INTO DICTIONARY_ITEM (DictionaryItemID, DictionaryCode, Code, Value, Name, OrderNo, RecordStatus, IsEditable)
SELECT NEWID(), 'JobSeekerCategory', '10', 10, '在学人员', 10, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY_ITEM WHERE DictionaryCode = 'JobSeekerCategory' AND Value = 10);

-- 3.6 确保 JobSeekerStatus（求职状态）字典数据存在
INSERT INTO DICTIONARY (DictionaryCode, Name, OrderNo, RecordStatus)
SELECT 'JobSeekerStatus', '求职状态', 8, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY WHERE DictionaryCode = 'JobSeekerStatus');

INSERT INTO DICTIONARY_ITEM (DictionaryItemID, DictionaryCode, Code, Value, Name, OrderNo, RecordStatus, IsEditable)
SELECT NEWID(), 'JobSeekerStatus', '1', 1, '离职-随时到岗', 1, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY_ITEM WHERE DictionaryCode = 'JobSeekerStatus' AND Value = 1);

INSERT INTO DICTIONARY_ITEM (DictionaryItemID, DictionaryCode, Code, Value, Name, OrderNo, RecordStatus, IsEditable)
SELECT NEWID(), 'JobSeekerStatus', '2', 2, '离职-随时到岗', 2, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY_ITEM WHERE DictionaryCode = 'JobSeekerStatus' AND Value = 2);

INSERT INTO DICTIONARY_ITEM (DictionaryItemID, DictionaryCode, Code, Value, Name, OrderNo, RecordStatus, IsEditable)
SELECT NEWID(), 'JobSeekerStatus', '3', 3, '在职-月内到岗', 3, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY_ITEM WHERE DictionaryCode = 'JobSeekerStatus' AND Value = 3);

INSERT INTO DICTIONARY_ITEM (DictionaryItemID, DictionaryCode, Code, Value, Name, OrderNo, RecordStatus, IsEditable)
SELECT NEWID(), 'JobSeekerStatus', '4', 4, '在职-考虑机会', 4, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY_ITEM WHERE DictionaryCode = 'JobSeekerStatus' AND Value = 4);

INSERT INTO DICTIONARY_ITEM (DictionaryItemID, DictionaryCode, Code, Value, Name, OrderNo, RecordStatus, IsEditable)
SELECT NEWID(), 'JobSeekerStatus', '5', 5, '在职-暂无意向', 5, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY_ITEM WHERE DictionaryCode = 'JobSeekerStatus' AND Value = 5);

INSERT INTO DICTIONARY_ITEM (DictionaryItemID, DictionaryCode, Code, Value, Name, OrderNo, RecordStatus, IsEditable)
SELECT NEWID(), 'JobSeekerStatus', '6', 6, '在校-即将毕业', 6, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM DICTIONARY_ITEM WHERE DictionaryCode = 'JobSeekerStatus' AND Value = 6);

-- ============================================================
-- 第四部分：清理见习人员在 sys_dict/sys_dict_item 中的旧数据
-- V20260604_3 插入了中文item_value的旧字典数据，需要清理
-- 前端 useDict 从 DICTIONARY_ITEM 表加载，不再使用 sys_dict_item
-- ============================================================

DELETE FROM sys_dict_item
WHERE dict_id IN (
    SELECT id FROM sys_dict
    WHERE dict_code IN ('internship_major_tag', 'internship_minor_tag', 'internship_custom_tag')
      AND del_flag = 0
);

DELETE FROM sys_dict
WHERE dict_code IN ('internship_major_tag', 'internship_minor_tag', 'internship_custom_tag')
  AND del_flag = 0;

-- ============================================================
-- 第五部分：验证修复结果
-- ============================================================

-- 5.1 验证权限status是否已修复
SELECT '权限status检查' AS 检查项, id, name, perms, status
FROM sys_permission
WHERE perms LIKE 'internship_personnel:%'
  AND menu_type = 2
ORDER BY perms;

-- 5.2 验证导出和服务跟进权限的角色授权
SELECT '角色授权检查' AS 检查项, p.perms, rp.role_id
FROM sys_permission p
LEFT JOIN sys_role_permission rp ON rp.permission_id = p.id AND rp.role_id = 'f6817f48af4fb3af11b9e8bf182f618b'
WHERE p.perms IN ('internship_personnel:exportXls', 'internship_personnel:serviceFollow');

-- 5.3 验证DICTIONARY字典数据
SELECT '字典主表检查' AS 检查项, DictionaryCode, Name
FROM DICTIONARY
WHERE DictionaryCode IN ('internship_major_tag', 'internship_minor_tag', 'internship_custom_tag', 'JobSeekerCategory', 'JobSeekerStatus');

-- 5.4 验证DICTIONARY_ITEM字典项数据
SELECT '字典项检查' AS 检查项, DictionaryCode, Value, Name, OrderNo
FROM DICTIONARY_ITEM
WHERE DictionaryCode IN ('internship_major_tag', 'internship_minor_tag', 'internship_custom_tag', 'JobSeekerCategory', 'JobSeekerStatus')
ORDER BY DictionaryCode, OrderNo;