-- =====================================================
-- 项目管理 - 数据表 + 菜单权限配置 SQL
-- 在管理后台新增"项目管理"独立主菜单
--
-- 菜单结构：
--   项目管理(2130, M-type独立目录) ← 与"招聘会管理"平级
--     └ 项目列表(2131, C-type页面)
--         ├ 项目查询(2132, F-type按钮)
--         ├ 项目新增(2133, F-type按钮)
--         ├ 项目修改(2134, F-type按钮)
--         └ 项目删除(2135, F-type按钮)
--
-- 使用固定ID 2130-2135，避免与已有数据冲突
-- =====================================================

-- =====================================================
-- Step 1: 创建项目表
-- =====================================================
CREATE SEQUENCE IF NOT EXISTS shz.cms_project_id_seq
    START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

CREATE TABLE IF NOT EXISTS shz.cms_project (
    project_id          BIGINT NOT NULL DEFAULT nextval('shz.cms_project_id_seq'::regclass) PRIMARY KEY,
    title               VARCHAR(255) NOT NULL,
    description         TEXT,
    required_personnel  TEXT,
    cooperation_mode    VARCHAR(100),
    industry            VARCHAR(100),
    region              VARCHAR(100),
    contact_name        VARCHAR(100),
    contact_phone       VARCHAR(50),
    contact_email       VARCHAR(100),
    status              CHAR(1) DEFAULT '0',
    create_by           VARCHAR(64),
    create_time         TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_by           VARCHAR(64),
    update_time         TIMESTAMP,
    del_flag            CHAR(1) DEFAULT '0',
    remark              VARCHAR(500)
);

COMMENT ON TABLE shz.cms_project IS '项目管理表';
COMMENT ON COLUMN shz.cms_project.project_id IS '项目ID';
COMMENT ON COLUMN shz.cms_project.title IS '项目名称';
COMMENT ON COLUMN shz.cms_project.description IS '项目描述';
COMMENT ON COLUMN shz.cms_project.required_personnel IS '项目需求人员';
COMMENT ON COLUMN shz.cms_project.cooperation_mode IS '合作方式';
COMMENT ON COLUMN shz.cms_project.industry IS '行业';
COMMENT ON COLUMN shz.cms_project.region IS '地区';
COMMENT ON COLUMN shz.cms_project.contact_name IS '联系人';
COMMENT ON COLUMN shz.cms_project.contact_phone IS '联系电话';
COMMENT ON COLUMN shz.cms_project.contact_email IS '联系邮箱';
COMMENT ON COLUMN shz.cms_project.status IS '状态：0=草稿, 1=发布';

CREATE INDEX IF NOT EXISTS idx_cms_project_title ON shz.cms_project(title);
CREATE INDEX IF NOT EXISTS idx_cms_project_industry ON shz.cms_project(industry);
CREATE INDEX IF NOT EXISTS idx_cms_project_region ON shz.cms_project(region);
CREATE INDEX IF NOT EXISTS idx_cms_project_cooperation_mode ON shz.cms_project(cooperation_mode);
CREATE INDEX IF NOT EXISTS idx_cms_project_del_flag ON shz.cms_project(del_flag);

-- =====================================================
-- Step 2: 创建项目评论表
-- =====================================================
CREATE SEQUENCE IF NOT EXISTS shz.cms_project_comment_id_seq
    START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

CREATE TABLE IF NOT EXISTS shz.cms_project_comment (
    comment_id      BIGINT NOT NULL DEFAULT nextval('shz.cms_project_comment_id_seq'::regclass) PRIMARY KEY,
    project_id      BIGINT NOT NULL,
    user_id         BIGINT,
    user_name       VARCHAR(100),
    content         TEXT NOT NULL,
    create_by       VARCHAR(64),
    create_time     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    del_flag        CHAR(1) DEFAULT '0'
);

COMMENT ON TABLE shz.cms_project_comment IS '项目评论表';
COMMENT ON COLUMN shz.cms_project_comment.comment_id IS '评论ID';
COMMENT ON COLUMN shz.cms_project_comment.project_id IS '关联项目ID';
COMMENT ON COLUMN shz.cms_project_comment.user_id IS '用户ID';
COMMENT ON COLUMN shz.cms_project_comment.user_name IS '用户名';
COMMENT ON COLUMN shz.cms_project_comment.content IS '评论内容';

CREATE INDEX IF NOT EXISTS idx_cms_project_comment_project_id ON shz.cms_project_comment(project_id);

-- =====================================================
-- Step 3: 新增菜单权限
-- 结构：项目管理(2130, M-type目录) → 项目列表(2131, C-type) → 按钮(2132-2135, F-type)
-- =====================================================

-- Step 3.1: 创建主菜单目录（M=目录, parent_id=0 表示顶级菜单）
INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, query, route_name, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
SELECT 2130, '项目管理', 0,
       5, 'project', '', '', '', 1, 0, 'M', '0', '0',
       '', 'project', 'admin', CURRENT_TIMESTAMP, '', null, '项目管理目录'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 2130);

-- Step 3.2: 创建菜单页面（C=菜单页面）
INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, query, route_name, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
SELECT 2131, '项目列表', 2130,
       1, 'project-mgmt', 'Management/ProjectMgmt', '', 'ProjectMgmt', 1, 0, 'C', '0', '0',
       'cms:project:list', '#', 'admin', CURRENT_TIMESTAMP, '', null, '项目列表菜单'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 2131);

-- Step 3.3: 新增按钮权限（F=按钮, parent_id=2131 归属到项目列表下）
-- 项目查询
INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, query, route_name, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
SELECT 2132, '项目查询', 2131, 1, '', '', '', '', 1, 0, 'F', '0', '0',
       'cms:project:query', '#', 'admin', CURRENT_TIMESTAMP, '', null, ''
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 2132);

-- 项目新增
INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, query, route_name, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
SELECT 2133, '项目新增', 2131, 2, '', '', '', '', 1, 0, 'F', '0', '0',
       'cms:project:add', '#', 'admin', CURRENT_TIMESTAMP, '', null, ''
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 2133);

-- 项目修改
INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, query, route_name, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
SELECT 2134, '项目修改', 2131, 3, '', '', '', '', 1, 0, 'F', '0', '0',
       'cms:project:edit', '#', 'admin', CURRENT_TIMESTAMP, '', null, ''
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 2134);

-- 项目删除
INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, query, route_name, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
SELECT 2135, '项目删除', 2131, 4, '', '', '', '', 1, 0, 'F', '0', '0',
       'cms:project:remove', '#', 'admin', CURRENT_TIMESTAMP, '', null, ''
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 2135);

-- =====================================================
-- Step 3.4: 将新菜单分配给角色
-- 管理员角色（role_key='admin'）通配符 *:*:* 自动拥有全部权限，无需分配
-- =====================================================
INSERT INTO sys_role_menu (role_id, menu_id)
SELECT r.role_id, 2130
FROM sys_role r
WHERE r.role_key != 'admin'
  AND NOT EXISTS (SELECT 1 FROM sys_role_menu rm WHERE rm.role_id = r.role_id AND rm.menu_id = 2130);

INSERT INTO sys_role_menu (role_id, menu_id)
SELECT r.role_id, 2131
FROM sys_role r
WHERE r.role_key != 'admin'
  AND NOT EXISTS (SELECT 1 FROM sys_role_menu rm WHERE rm.role_id = r.role_id AND rm.menu_id = 2131);

INSERT INTO sys_role_menu (role_id, menu_id)
SELECT r.role_id, 2132
FROM sys_role r
WHERE r.role_key != 'admin'
  AND NOT EXISTS (SELECT 1 FROM sys_role_menu rm WHERE rm.role_id = r.role_id AND rm.menu_id = 2132);

INSERT INTO sys_role_menu (role_id, menu_id)
SELECT r.role_id, 2133
FROM sys_role r
WHERE r.role_key != 'admin'
  AND NOT EXISTS (SELECT 1 FROM sys_role_menu rm WHERE rm.role_id = r.role_id AND rm.menu_id = 2133);

INSERT INTO sys_role_menu (role_id, menu_id)
SELECT r.role_id, 2134
FROM sys_role r
WHERE r.role_key != 'admin'
  AND NOT EXISTS (SELECT 1 FROM sys_role_menu rm WHERE rm.role_id = r.role_id AND rm.menu_id = 2134);

INSERT INTO sys_role_menu (role_id, menu_id)
SELECT r.role_id, 2135
FROM sys_role r
WHERE r.role_key != 'admin'
  AND NOT EXISTS (SELECT 1 FROM sys_role_menu rm WHERE rm.role_id = r.role_id AND rm.menu_id = 2135);

-- =====================================================
-- 验证 SQL（可选执行）
-- =====================================================
-- 检查表是否创建成功
-- SELECT * FROM shz.cms_project LIMIT 1;
-- SELECT * FROM shz.cms_project_comment LIMIT 1;

-- 检查菜单是否创建成功
-- SELECT * FROM sys_menu WHERE menu_id BETWEEN 2130 AND 2135;

-- 检查角色菜单关联
-- SELECT r.role_name, m.menu_name FROM sys_role_menu rm
-- JOIN sys_role r ON r.role_id = rm.role_id
-- JOIN sys_menu m ON m.menu_id = rm.menu_id
-- WHERE rm.menu_id BETWEEN 2130 AND 2135;