-- Apple经销商ERP系统对接项目 - 菜单管理和字典管理权限配置脚本
-- 创建时间: 2025-01-27
-- 版本: v1.0

-- 设置数据库和字符集
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- 使用数据库
USE apple_erp;

-- =========================================
-- 1. 插入菜单管理相关菜单和权限
-- =========================================

-- 菜单管理主菜单
INSERT INTO t_sys_menu (menu_id, parent_id, menu_name, menu_type, path, icon, sort, status, perms, create_by, create_time, update_by, update_time, del_flag) 
VALUES (100, 0, '菜单管理', '0', '/system/menu', 'menu', 4, '1', '', 'admin', NOW(), 'admin', NOW(), '0');

-- 菜单管理子菜单
INSERT INTO t_sys_menu (menu_id, parent_id, menu_name, menu_type, path, icon, sort, status, perms, create_by, create_time, update_by, update_time, del_flag) 
VALUES 
(1001, 100, '菜单查询', '1', '/system/menu/list', '', 1, '1', 'sys:menu:list', 'admin', NOW(), 'admin', NOW(), '0'),
(1002, 100, '菜单新增', '2', '', '', 2, '1', 'sys:menu:add', 'admin', NOW(), 'admin', NOW(), '0'),
(1003, 100, '菜单修改', '2', '', '', 3, '1', 'sys:menu:edit', 'admin', NOW(), 'admin', NOW(), '0'),
(1004, 100, '菜单删除', '2', '', '', 4, '1', 'sys:menu:remove', 'admin', NOW(), 'admin', NOW(), '0'),
(1005, 100, '菜单详情', '2', '', '', 5, '1', 'sys:menu:query', 'admin', NOW(), 'admin', NOW(), '0');

-- =========================================
-- 2. 插入字典管理相关菜单和权限
-- =========================================

-- 字典管理主菜单
INSERT INTO t_sys_menu (menu_id, parent_id, menu_name, menu_type, path, icon, sort, status, perms, create_by, create_time, update_by, update_time, del_flag) 
VALUES (200, 0, '字典管理', '0', '/system/dict', 'dict', 5, '1', '', 'admin', NOW(), 'admin', NOW(), '0');

-- 字典类型管理子菜单
INSERT INTO t_sys_menu (menu_id, parent_id, menu_name, menu_type, path, icon, sort, status, perms, create_by, create_time, update_by, update_time, del_flag) 
VALUES 
(2001, 200, '字典类型查询', '1', '/system/dict/type/list', '', 1, '1', 'sys:dict:list', 'admin', NOW(), 'admin', NOW(), '0'),
(2002, 200, '字典类型新增', '2', '', '', 2, '1', 'sys:dict:add', 'admin', NOW(), 'admin', NOW(), '0'),
(2003, 200, '字典类型修改', '2', '', '', 3, '1', 'sys:dict:edit', 'admin', NOW(), 'admin', NOW(), '0'),
(2004, 200, '字典类型删除', '2', '', '', 4, '1', 'sys:dict:remove', 'admin', NOW(), 'admin', NOW(), '0'),
(2005, 200, '字典类型详情', '2', '', '', 5, '1', 'sys:dict:query', 'admin', NOW(), 'admin', NOW(), '0');

-- 字典项管理子菜单
INSERT INTO t_sys_menu (menu_id, parent_id, menu_name, menu_type, path, icon, sort, status, perms, create_by, create_time, update_by, update_time, del_flag) 
VALUES 
(2006, 200, '字典项查询', '1', '/system/dict/item/list', '', 6, '1', 'sys:dict:list', 'admin', NOW(), 'admin', NOW(), '0'),
(2007, 200, '字典项新增', '2', '', '', 7, '1', 'sys:dict:add', 'admin', NOW(), 'admin', NOW(), '0'),
(2008, 200, '字典项修改', '2', '', '', 8, '1', 'sys:dict:edit', 'admin', NOW(), 'admin', NOW(), '0'),
(2009, 200, '字典项删除', '2', '', '', 9, '1', 'sys:dict:remove', 'admin', NOW(), 'admin', NOW(), '0'),
(2010, 200, '字典项详情', '2', '', '', 10, '1', 'sys:dict:query', 'admin', NOW(), 'admin', NOW(), '0');

-- =========================================
-- 3. 为ADMIN角色分配菜单管理权限
-- =========================================

-- 获取ADMIN角色ID
SET @admin_role_id = (SELECT role_id FROM t_sys_role WHERE role_code = 'ADMIN' AND del_flag = '0' LIMIT 1);

-- 为ADMIN角色分配菜单管理权限
INSERT INTO t_sys_role_menu (role_id, menu_id, create_by, create_time, update_by, update_time, del_flag) 
SELECT @admin_role_id, menu_id, 'admin', NOW(), 'admin', NOW(), '0'
FROM t_sys_menu 
WHERE menu_id IN (100, 1001, 1002, 1003, 1004, 1005)
AND del_flag = '0';

-- 为ADMIN角色分配字典管理权限
INSERT INTO t_sys_role_menu (role_id, menu_id, create_by, create_time, update_by, update_time, del_flag) 
SELECT @admin_role_id, menu_id, 'admin', NOW(), 'admin', NOW(), '0'
FROM t_sys_menu 
WHERE menu_id IN (200, 2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010)
AND del_flag = '0';

-- =========================================
-- 4. 插入基础字典数据
-- =========================================

-- 插入基础字典类型
INSERT INTO t_sys_dict_type (dict_type, dict_name, status, remark, create_by, create_time, update_by, update_time, del_flag) 
VALUES 
('sys_user_sex', '用户性别', 1, '用户性别列表', 'admin', NOW(), 'admin', NOW(), '0'),
('sys_show_hide', '菜单状态', 1, '菜单状态列表', 'admin', NOW(), 'admin', NOW(), '0'),
('sys_normal_disable', '系统状态', 1, '系统状态列表', 'admin', NOW(), 'admin', NOW(), '0'),
('sys_menu_type', '菜单类型', 1, '菜单类型列表', 'admin', NOW(), 'admin', NOW(), '0'),
('sys_yes_no', '是否', 1, '是否列表', 'admin', NOW(), 'admin', NOW(), '0');

-- 插入基础字典项
-- 用户性别
INSERT INTO t_sys_dict_item (dict_type_id, dict_label, dict_value, sort, remark, create_by, create_time, update_by, update_time, del_flag) 
SELECT dt.dict_type_id, '男', '0', 1, '性别男', 'admin', NOW(), 'admin', NOW(), '0'
FROM t_sys_dict_type dt WHERE dt.dict_type = 'sys_user_sex' AND dt.del_flag = '0';

INSERT INTO t_sys_dict_item (dict_type_id, dict_label, dict_value, sort, remark, create_by, create_time, update_by, update_time, del_flag) 
SELECT dt.dict_type_id, '女', '1', 2, '性别女', 'admin', NOW(), 'admin', NOW(), '0'
FROM t_sys_dict_type dt WHERE dt.dict_type = 'sys_user_sex' AND dt.del_flag = '0';

-- 菜单状态
INSERT INTO t_sys_dict_item (dict_type_id, dict_label, dict_value, sort, remark, create_by, create_time, update_by, update_time, del_flag) 
SELECT dt.dict_type_id, '显示', '1', 1, '显示菜单', 'admin', NOW(), 'admin', NOW(), '0'
FROM t_sys_dict_type dt WHERE dt.dict_type = 'sys_show_hide' AND dt.del_flag = '0';

INSERT INTO t_sys_dict_item (dict_type_id, dict_label, dict_value, sort, remark, create_by, create_time, update_by, update_time, del_flag) 
SELECT dt.dict_type_id, '隐藏', '0', 2, '隐藏菜单', 'admin', NOW(), 'admin', NOW(), '0'
FROM t_sys_dict_type dt WHERE dt.dict_type = 'sys_show_hide' AND dt.del_flag = '0';

-- 系统状态
INSERT INTO t_sys_dict_item (dict_type_id, dict_label, dict_value, sort, remark, create_by, create_time, update_by, update_time, del_flag) 
SELECT dt.dict_type_id, '正常', '1', 1, '正常状态', 'admin', NOW(), 'admin', NOW(), '0'
FROM t_sys_dict_type dt WHERE dt.dict_type = 'sys_normal_disable' AND dt.del_flag = '0';

INSERT INTO t_sys_dict_item (dict_type_id, dict_label, dict_value, sort, remark, create_by, create_time, update_by, update_time, del_flag) 
SELECT dt.dict_type_id, '停用', '0', 2, '停用状态', 'admin', NOW(), 'admin', NOW(), '0'
FROM t_sys_dict_type dt WHERE dt.dict_type = 'sys_normal_disable' AND dt.del_flag = '0';

-- 菜单类型
INSERT INTO t_sys_dict_item (dict_type_id, dict_label, dict_value, sort, remark, create_by, create_time, update_by, update_time, del_flag) 
SELECT dt.dict_type_id, '目录', '0', 1, '目录', 'admin', NOW(), 'admin', NOW(), '0'
FROM t_sys_dict_type dt WHERE dt.dict_type = 'sys_menu_type' AND dt.del_flag = '0';

INSERT INTO t_sys_dict_item (dict_type_id, dict_label, dict_value, sort, remark, create_by, create_time, update_by, update_time, del_flag) 
SELECT dt.dict_type_id, '菜单', '1', 2, '菜单', 'admin', NOW(), 'admin', NOW(), '0'
FROM t_sys_dict_type dt WHERE dt.dict_type = 'sys_menu_type' AND dt.del_flag = '0';

INSERT INTO t_sys_dict_item (dict_type_id, dict_label, dict_value, sort, remark, create_by, create_time, update_by, update_time, del_flag) 
SELECT dt.dict_type_id, '按钮', '2', 3, '按钮', 'admin', NOW(), 'admin', NOW(), '0'
FROM t_sys_dict_type dt WHERE dt.dict_type = 'sys_menu_type' AND dt.del_flag = '0';

-- 是否
INSERT INTO t_sys_dict_item (dict_type_id, dict_label, dict_value, sort, remark, create_by, create_time, update_by, update_time, del_flag) 
SELECT dt.dict_type_id, '是', 'Y', 1, '是', 'admin', NOW(), 'admin', NOW(), '0'
FROM t_sys_dict_type dt WHERE dt.dict_type = 'sys_yes_no' AND dt.del_flag = '0';

INSERT INTO t_sys_dict_item (dict_type_id, dict_label, dict_value, sort, remark, create_by, create_time, update_by, update_time, del_flag) 
SELECT dt.dict_type_id, '否', 'N', 2, '否', 'admin', NOW(), 'admin', NOW(), '0'
FROM t_sys_dict_type dt WHERE dt.dict_type = 'sys_yes_no' AND dt.del_flag = '0';

-- 重置外键检查
SET FOREIGN_KEY_CHECKS = 1;

-- 输出完成信息
SELECT '菜单管理和字典管理权限配置完成！' AS message;