CREATE TABLE sys_user
(
    id             INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '用户的id',
    account        VARCHAR(200)    NOT NULL COMMENT '用户帐号',
    password       VARCHAR(64)     NOT NULL COMMENT '用户密码',
    email          VARCHAR(64)     NOT NULL COMMENT '用户邮箱',
    iphone         VARCHAR(64) NULL DEFAULT NULL COMMENT '用户手机号',
    lastloginip    VARCHAR(64) NULL DEFAULT NULL COMMENT '登陆IP',
    isactive       INT             NOT NULL DEFAULT '0' COMMENT '是否激活',
    IsDeleted      INT             NOT NULL DEFAULT '0' COMMENT '假删除',
    lastloginTime  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '最后一次登陆时间',
    createtime     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '用户创建时间',
    updatetime     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
    expirationtime DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '用户的到期时间',
    INDEX          account_index (account)
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci;


CREATE TABLE sys_role
(
    id          INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '角色的id',
    role_name   VARCHAR(50)     NOT NULL COMMENT '角色的名称',
    description VARCHAR(200)    NOT NULL COMMENT '角色的描述',
    user_id     INT             NOT NULL COMMENT '用户id',
    INDEX       user_index (user_id),
    FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci;

CREATE TABLE sys_permission
(
    id              INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '权限的id',
    permission_name VARCHAR(50)     NOT NULL COMMENT '权限的名称',
    description     VARCHAR(200)    NOT NULL COMMENT '权限的描述',
    role_id         INT             NOT NULL COMMENT '角色id',
    INDEX           role_index (role_id),
    FOREIGN KEY (role_id) REFERENCES role (id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci;



INSERT INTO role (role_name, description, user_id)
VALUES ('admin', '管理员', 1),
       ('editor', '编辑员', 2),
       ('viewer', '观察员', 3),
       ('operator', '操作员', 4),
       ('guest', '游客', 5);


INSERT INTO permission (permission_name, description, role_id)
VALUES ('read', '读取权限', 1),
       ('write', '写入权限', 2),
       ('delete', '删除权限', 3),
       ('execute', '执行权限', 4),
       ('view', '查看权限', 5);