-- 角色备注、菜单表、角色-菜单关联；示例「账号管理」下菜单树；ADMIN 绑定全部菜单

ALTER TABLE sys_role
    ADD COLUMN remark VARCHAR(512) NULL COMMENT '备注' AFTER role_name;

CREATE TABLE IF NOT EXISTS sys_menu (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    uuid VARCHAR(64) NOT NULL,
    version BIGINT NOT NULL DEFAULT 1,
    create_id BIGINT NULL,
    create_time DATETIME NULL,
    update_id BIGINT NULL,
    update_time DATETIME NULL,
    del_flag INT NOT NULL DEFAULT 0,
    pid BIGINT NOT NULL DEFAULT 0 COMMENT '父级 id，板块根为 0',
    menu_type TINYINT NOT NULL COMMENT '1板块 2页面 3权限',
    menu_name VARCHAR(128) NOT NULL,
    perm_code VARCHAR(128) NULL COMMENT '权限编码（扩展用）',
    sort_order INT NOT NULL DEFAULT 0,
    route_path VARCHAR(255) NULL COMMENT '前端路由，页面级可填',
    UNIQUE KEY uk_sys_menu_uuid (uuid),
    KEY idx_menu_pid (pid)
);

CREATE TABLE IF NOT EXISTS sys_role_menu (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    uuid VARCHAR(64) NOT NULL,
    version BIGINT NOT NULL DEFAULT 1,
    create_id BIGINT NULL,
    create_time DATETIME NULL,
    update_id BIGINT NULL,
    update_time DATETIME NULL,
    del_flag INT NOT NULL DEFAULT 0,
    role_id BIGINT NOT NULL,
    menu_id BIGINT NOT NULL,
    UNIQUE KEY uk_sys_role_menu_uuid (uuid),
    KEY idx_rm_role (role_id),
    KEY idx_rm_menu (menu_id)
);

-- ========== 示例：账号管理（板块）→ 平台管理员 / 角色管理（页面）→ 操作权限（叶子）==========
INSERT INTO sys_menu (uuid, version, pid, menu_type, menu_name, perm_code, sort_order, route_path, del_flag)
VALUES (REPLACE(UUID(), '-', ''), 1, 0, 1, '账号管理', NULL, 100, NULL, 0);
SET @menu_acct = LAST_INSERT_ID();

INSERT INTO sys_menu (uuid, version, pid, menu_type, menu_name, perm_code, sort_order, route_path, del_flag)
VALUES (REPLACE(UUID(), '-', ''), 1, @menu_acct, 2, '平台管理员', 'sys:users:page', 10, '/users', 0);
SET @menu_users = LAST_INSERT_ID();

INSERT INTO sys_menu (uuid, version, pid, menu_type, menu_name, perm_code, sort_order, route_path, del_flag) VALUES
(REPLACE(UUID(), '-', ''), 1, @menu_users, 3, '查看详情', 'sys:users:detail', 10, NULL, 0),
(REPLACE(UUID(), '-', ''), 1, @menu_users, 3, '新增用户', 'sys:users:create', 20, NULL, 0),
(REPLACE(UUID(), '-', ''), 1, @menu_users, 3, '编辑用户', 'sys:users:update', 30, NULL, 0),
(REPLACE(UUID(), '-', ''), 1, @menu_users, 3, '删除用户', 'sys:users:delete', 40, NULL, 0),
(REPLACE(UUID(), '-', ''), 1, @menu_users, 3, '启停账号', 'sys:users:status', 50, NULL, 0),
(REPLACE(UUID(), '-', ''), 1, @menu_users, 3, '重置密码', 'sys:users:password', 60, NULL, 0);

INSERT INTO sys_menu (uuid, version, pid, menu_type, menu_name, perm_code, sort_order, route_path, del_flag)
VALUES (REPLACE(UUID(), '-', ''), 1, @menu_acct, 2, '角色管理', 'sys:roles:page', 20, '/roles', 0);
SET @menu_roles = LAST_INSERT_ID();

INSERT INTO sys_menu (uuid, version, pid, menu_type, menu_name, perm_code, sort_order, route_path, del_flag) VALUES
(REPLACE(UUID(), '-', ''), 1, @menu_roles, 3, '查看详情', 'sys:roles:detail', 10, NULL, 0),
(REPLACE(UUID(), '-', ''), 1, @menu_roles, 3, '新增角色', 'sys:roles:create', 20, NULL, 0),
(REPLACE(UUID(), '-', ''), 1, @menu_roles, 3, '编辑角色', 'sys:roles:update', 30, NULL, 0),
(REPLACE(UUID(), '-', ''), 1, @menu_roles, 3, '删除角色', 'sys:roles:delete', 40, NULL, 0);

-- 系统管理员角色绑定当前库中全部菜单（含上列示例）
INSERT INTO sys_role_menu (uuid, version, role_id, menu_id, del_flag)
SELECT REPLACE(UUID(), '-', ''), 1, r.id, m.id, 0
FROM sys_role r
JOIN sys_menu m ON m.del_flag = 0
WHERE r.role_code = 'ADMIN' AND r.del_flag = 0;