-- 建库
CREATE DATABASE "ShWasherAdmin" WITH OWNER = postgres ENCODING = 'UTF8';

-- ----------------------------
-- 1、组织机构表
-- ----------------------------
DROP TABLE IF EXISTS sys_org;
CREATE TABLE sys_org
(
    org_id       BIGSERIAL PRIMARY KEY,
    tenant_id    VARCHAR(20) DEFAULT '000000',
    parent_id    BIGINT DEFAULT 0,
    ancestors    VARCHAR(500) DEFAULT '',
    org_name     VARCHAR(30) DEFAULT '',
    org_category VARCHAR(100),
    order_num    INT DEFAULT 0,
    leader       BIGINT,
    phone        VARCHAR(11),
    email        VARCHAR(50),
    status       CHAR(1) DEFAULT '0',
    del_flag     CHAR(1) DEFAULT '0',
    create_org   BIGINT,
    create_by    BIGINT,
    create_time  TIMESTAMP,
    update_by    BIGINT,
    update_time  TIMESTAMP
);

COMMENT ON TABLE sys_org IS '组织机构表';
COMMENT ON COLUMN sys_org.org_id IS '组织机构id';
COMMENT ON COLUMN sys_org.tenant_id IS '租户编号';
COMMENT ON COLUMN sys_org.parent_id IS '父组织机构id';
COMMENT ON COLUMN sys_org.ancestors IS '祖级列表';
COMMENT ON COLUMN sys_org.org_name IS '组织机构名称';
COMMENT ON COLUMN sys_org.org_category IS '组织机构类别编码';
COMMENT ON COLUMN sys_org.order_num IS '显示顺序';
COMMENT ON COLUMN sys_org.leader IS '负责人';
COMMENT ON COLUMN sys_org.phone IS '联系电话';
COMMENT ON COLUMN sys_org.email IS '邮箱';
COMMENT ON COLUMN sys_org.status IS '组织机构状态（0正常 1停用）';
COMMENT ON COLUMN sys_org.del_flag IS '删除标志（0代表存在 2代表删除）';
COMMENT ON COLUMN sys_org.create_org IS '创建组织机构';
COMMENT ON COLUMN sys_org.create_by IS '创建者';
COMMENT ON COLUMN sys_org.create_time IS '创建时间';
COMMENT ON COLUMN sys_org.update_by IS '更新者';
COMMENT ON COLUMN sys_org.update_time IS '更新时间';

-- ----------------------------
-- 2、用户表
-- ----------------------------
DROP TABLE IF EXISTS sys_user;
CREATE TABLE sys_user
(
    user_id         BIGSERIAL PRIMARY KEY,
    tenant_id       VARCHAR(20) DEFAULT '000000',
    dept_id         BIGINT DEFAULT 0,
    user_name       VARCHAR(30) NOT NULL,
    nick_name       VARCHAR(30),
    user_type       CHAR(1) DEFAULT '0',
    email           VARCHAR(50),
    phonenumber     VARCHAR(11),
    sex             CHAR(1) DEFAULT '0',
    avatar          VARCHAR(100),
    password        VARCHAR(100) NOT NULL,
    status          CHAR(1) DEFAULT '0',
    del_flag        CHAR(1) DEFAULT '0',
    login_ip        VARCHAR(50),
    login_date      TIMESTAMP,
    create_org      BIGINT,
    create_by       BIGINT,
    create_time     TIMESTAMP,
    update_by       BIGINT,
    update_time     TIMESTAMP
);

COMMENT ON TABLE sys_user IS '用户表';
COMMENT ON COLUMN sys_user.user_id IS '用户ID';
COMMENT ON COLUMN sys_user.tenant_id IS '租户编号';
COMMENT ON COLUMN sys_user.dept_id IS '部门ID';
COMMENT ON COLUMN sys_user.user_name IS '用户账号';
COMMENT ON COLUMN sys_user.nick_name IS '用户昵称';
COMMENT ON COLUMN sys_user.user_type IS '用户类型（0系统用户 1注册用户）';
COMMENT ON COLUMN sys_user.email IS '用户邮箱';
COMMENT ON COLUMN sys_user.phonenumber IS '手机号码';
COMMENT ON COLUMN sys_user.sex IS '用户性别（0男 1女 2未知）';
COMMENT ON COLUMN sys_user.avatar IS '头像地址';
COMMENT ON COLUMN sys_user.password IS '密码';
COMMENT ON COLUMN sys_user.status IS '帐号状态（0正常 1停用）';
COMMENT ON COLUMN sys_user.del_flag IS '删除标志（0代表存在 2代表删除）';
COMMENT ON COLUMN sys_user.login_ip IS '最后登录IP';
COMMENT ON COLUMN sys_user.login_date IS '最后登录时间';
COMMENT ON COLUMN sys_user.create_org IS '创建组织机构';
COMMENT ON COLUMN sys_user.create_by IS '创建者';
COMMENT ON COLUMN sys_user.create_time IS '创建时间';
COMMENT ON COLUMN sys_user.update_by IS '更新者';
COMMENT ON COLUMN sys_user.update_time IS '更新时间';

-- ----------------------------
-- 3、角色表
-- ----------------------------
DROP TABLE IF EXISTS sys_role;
CREATE TABLE sys_role
(
    role_id         BIGSERIAL PRIMARY KEY,
    tenant_id       VARCHAR(20) DEFAULT '000000',
    role_name       VARCHAR(30) NOT NULL,
    role_key        VARCHAR(100) NOT NULL,
    role_sort       INT DEFAULT 0,
    data_scope      CHAR(1) DEFAULT '1',
    menu_check_strictly BOOLEAN DEFAULT TRUE,
    status          CHAR(1) DEFAULT '0',
    del_flag        CHAR(1) DEFAULT '0',
    create_org      BIGINT,
    create_by       BIGINT,
    create_time     TIMESTAMP,
    update_by       BIGINT,
    update_time     TIMESTAMP
);

COMMENT ON TABLE sys_role IS '角色表';
COMMENT ON COLUMN sys_role.role_id IS '角色ID';
COMMENT ON COLUMN sys_role.tenant_id IS '租户编号';
COMMENT ON COLUMN sys_role.role_name IS '角色名称';
COMMENT ON COLUMN sys_role.role_key IS '角色权限字符串';
COMMENT ON COLUMN sys_role.role_sort IS '显示顺序';
COMMENT ON COLUMN sys_role.data_scope IS '数据范围（1：全部数据权限 2：自定数据权限 3：本部门数据权限 4：本部门及以下数据权限）';
COMMENT ON COLUMN sys_role.menu_check_strictly IS '菜单树选择项是否关联显示（ 0：父子不互相关联显示 1：父子互相关联显示）';
COMMENT ON COLUMN sys_role.status IS '角色状态（0正常 1停用）';
COMMENT ON COLUMN sys_role.del_flag IS '删除标志（0代表存在 2代表删除）';
COMMENT ON COLUMN sys_role.create_org IS '创建组织机构';
COMMENT ON COLUMN sys_role.create_by IS '创建者';
COMMENT ON COLUMN sys_role.create_time IS '创建时间';
COMMENT ON COLUMN sys_role.update_by IS '更新者';
COMMENT ON COLUMN sys_role.update_time IS '更新时间';

-- ----------------------------
-- 4、菜单表
-- ----------------------------
DROP TABLE IF EXISTS sys_menu;
CREATE TABLE sys_menu
(
    menu_id         BIGSERIAL PRIMARY KEY,
    tenant_id       VARCHAR(20) DEFAULT '000000',
    menu_name       VARCHAR(50) NOT NULL,
    parent_id       BIGINT DEFAULT 0,
    order_num       INT DEFAULT 0,
    path            VARCHAR(200),
    component       VARCHAR(255),
    query_params    VARCHAR(255),
    is_frame        CHAR(1) DEFAULT '1',
    is_cache        CHAR(1) DEFAULT '0',
    menu_type       CHAR(1),
    visible         CHAR(1) DEFAULT '0',
    status          CHAR(1) DEFAULT '0',
    perms           VARCHAR(100),
    icon            VARCHAR(100),
    create_org      BIGINT,
    create_by       BIGINT,
    create_time     TIMESTAMP,
    update_by       BIGINT,
    update_time     TIMESTAMP
);

COMMENT ON TABLE sys_menu IS '菜单表';
COMMENT ON COLUMN sys_menu.menu_id IS '菜单ID';
COMMENT ON COLUMN sys_menu.tenant_id IS '租户编号';
COMMENT ON COLUMN sys_menu.menu_name IS '菜单名称';
COMMENT ON COLUMN sys_menu.parent_id IS '父菜单ID';
COMMENT ON COLUMN sys_menu.order_num IS '显示顺序';
COMMENT ON COLUMN sys_menu.path IS '路由地址';
COMMENT ON COLUMN sys_menu.component IS '组件路径';
COMMENT ON COLUMN sys_menu.query_params IS '路由参数';
COMMENT ON COLUMN sys_menu.is_frame IS '是否为外链（0是 1否）';
COMMENT ON COLUMN sys_menu.is_cache IS '是否缓存（0缓存 1不缓存）';
COMMENT ON COLUMN sys_menu.menu_type IS '菜单类型（M目录 C菜单 F按钮）';
COMMENT ON COLUMN sys_menu.visible IS '菜单状态（0显示 1隐藏）';
COMMENT ON COLUMN sys_menu.status IS '菜单状态（0正常 1停用）';
COMMENT ON COLUMN sys_menu.perms IS '权限标识';
COMMENT ON COLUMN sys_menu.icon IS '菜单图标';
COMMENT ON COLUMN sys_menu.create_org IS '创建组织机构';
COMMENT ON COLUMN sys_menu.create_by IS '创建者';
COMMENT ON COLUMN sys_menu.create_time IS '创建时间';
COMMENT ON COLUMN sys_menu.update_by IS '更新者';
COMMENT ON COLUMN sys_menu.update_time IS '更新时间';

-- ----------------------------
-- 5、岗位表
-- ----------------------------
DROP TABLE IF EXISTS sys_post;
CREATE TABLE sys_post
(
    post_id     BIGSERIAL PRIMARY KEY,
    tenant_id   VARCHAR(20) DEFAULT '000000',
    post_code   VARCHAR(64) NOT NULL,
    post_name   VARCHAR(50) NOT NULL,
    post_sort   INT DEFAULT 0,
    status      CHAR(1) DEFAULT '0',
    create_org  BIGINT,
    create_by   BIGINT,
    create_time TIMESTAMP,
    update_by   BIGINT,
    update_time TIMESTAMP
);

COMMENT ON TABLE sys_post IS '岗位表';
COMMENT ON COLUMN sys_post.post_id IS '岗位ID';
COMMENT ON COLUMN sys_post.tenant_id IS '租户编号';
COMMENT ON COLUMN sys_post.post_code IS '岗位编码';
COMMENT ON COLUMN sys_post.post_name IS '岗位名称';
COMMENT ON COLUMN sys_post.post_sort IS '显示顺序';
COMMENT ON COLUMN sys_post.status IS '状态（0正常 1停用）';
COMMENT ON COLUMN sys_post.create_org IS '创建组织机构';
COMMENT ON COLUMN sys_post.create_by IS '创建者';
COMMENT ON COLUMN sys_post.create_time IS '创建时间';
COMMENT ON COLUMN sys_post.update_by IS '更新者';
COMMENT ON COLUMN sys_post.update_time IS '更新时间';

-- ----------------------------
-- 6、用户与岗位关联表
-- ----------------------------
DROP TABLE IF EXISTS sys_user_post;
CREATE TABLE sys_user_post
(
    user_id BIGINT NOT NULL,
    post_id BIGINT NOT NULL,
    PRIMARY KEY (user_id, post_id)
);

COMMENT ON TABLE sys_user_post IS '用户与岗位关联表';
COMMENT ON COLUMN sys_user_post.user_id IS '用户ID';
COMMENT ON COLUMN sys_user_post.post_id IS '岗位ID';

-- ----------------------------
-- 7、用户与角色关联表
-- ----------------------------
DROP TABLE IF EXISTS sys_user_role;
CREATE TABLE sys_user_role
(
    user_id BIGINT NOT NULL,
    role_id BIGINT NOT NULL,
    PRIMARY KEY (user_id, role_id)
);

COMMENT ON TABLE sys_user_role IS '用户与角色关联表';
COMMENT ON COLUMN sys_user_role.user_id IS '用户ID';
COMMENT ON COLUMN sys_user_role.role_id IS '角色ID';

-- ----------------------------
-- 8、角色与菜单关联表
-- ----------------------------
DROP TABLE IF EXISTS sys_role_menu;
CREATE TABLE sys_role_menu
(
    role_id BIGINT NOT NULL,
    menu_id BIGINT NOT NULL,
    PRIMARY KEY (role_id, menu_id)
);

COMMENT ON TABLE sys_role_menu IS '角色与菜单关联表';
COMMENT ON COLUMN sys_role_menu.role_id IS '角色ID';
COMMENT ON COLUMN sys_role_menu.menu_id IS '菜单ID';

-- ----------------------------
-- 初始化数据
-- ----------------------------

-- ----------------------------
-- 初始化-组织机构表数据
-- ----------------------------
INSERT INTO sys_org (org_id, tenant_id, parent_id, ancestors, org_name, org_category, order_num, leader, phone, email, status, del_flag, create_org, create_by, create_time, update_by, update_time)
VALUES (100, '000000', 0, '0', '科技有限公司', NULL, 0, 1, '15888888888', 'vber@iwbnet.com', '0', '0', 100, 1, CURRENT_TIMESTAMP, NULL, NULL),
       (101, '000000', 100, '0,100', '001公司', NULL, 1, 3, '15888888888', 'xxx@qq.com', '0', '0', 100, 1, CURRENT_TIMESTAMP, NULL, NULL),
       (102, '000000', 100, '0,100', '002公司', NULL, 2, 4, '15888888888', 'xxx@qq.com', '0', '0', 100, 1, CURRENT_TIMESTAMP, NULL, NULL);


-- ----------------------------
-- 初始化-用户与岗位关联表数据
-- ----------------------------
INSERT INTO sys_user_post (user_id, post_id)
VALUES (1, 1),
       (2, 1);