drop table if exists tab_user;
create table tab_user
(
    user_id     bigint auto_increment primary key not null comment '主键',
    password    text                              not null comment '密码',
    nickname    varchar(20) default '' comment '姓名',
    idcard      char(18)    default '' comment '身份证',
    phone       varchar(11) default '' comment '手机',
    email       varchar(99) default '' comment '邮箱',
    avatar      varchar(99) default '' comment '头像',
    status      tinyint     default 0 comment '状态',
    dept_id     bigint      default 0 comment '部门ID',
    post_id     bigint      default 0 comment '岗位ID',
    leader_id   bigint      default 0 comment '直属上级ID',
    version     bigint      default 0 comment '乐观锁',
    deleted     tinyint     default 0 comment '逻辑删除',
    leave_time  datetime comment '离职时间',
    create_time datetime    default CURRENT_TIMESTAMP comment '创建时间',
    update_time datetime    default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP comment '更新时间'
) comment '用户信息' auto_increment = 10000;

insert into tab_user (nickname, password)
values ('超级管理员',
        'HHzurzBfRCaHJcEbme67tVE4wqY2hR0fEw88ByWmzjmGJ7UYh5FQ2Z2/2DtpXSb3/cOaezl6e8qnh/e0lPEOWZay0A9HLPNgLgGPZAoDLbzjvrFwx8M+4rhkN2nKaBr7b/7mmWqYIDJqS9HT47dm1sLQRiELPHmaMftBvz/AtZM='),
       ('普通用户',
        'HHzurzBfRCaHJcEbme67tVE4wqY2hR0fEw88ByWmzjmGJ7UYh5FQ2Z2/2DtpXSb3/cOaezl6e8qnh/e0lPEOWZay0A9HLPNgLgGPZAoDLbzjvrFwx8M+4rhkN2nKaBr7b/7mmWqYIDJqS9HT47dm1sLQRiELPHmaMftBvz/AtZM=');

drop table if exists tab_role;
create table tab_role
(
    role_id     bigint auto_increment primary key not null comment '主键',
    role_name   varchar(30) unique                not null comment '角色名称',
    remark      varchar(255) default '' comment '备注',
    role_sort   integer(4)   default 0 comment '显示顺序',
    version     bigint       default 0 comment '乐观锁',
    deleted     tinyint      default 0 comment '逻辑删除',
    create_time datetime     default CURRENT_TIMESTAMP comment '创建时间',
    update_time datetime     default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP comment '更新时间'
) comment '角色信息' auto_increment = 100;

insert into tab_role (role_id, role_name)
values (100, '超级管理员'),
       (101, '普通用户');

drop table if exists tab_user_role;
create table tab_user_role
(
    user_id bigint not null comment '用户ID',
    role_id bigint not null comment '角色ID',
    primary key (user_id, role_id),
    key (role_id)
) comment '用户和角色关联表';

insert into tab_user_role (user_id, role_id)
values (10000, 100),
       (10001, 101);

drop table if exists tab_menu;
create table tab_menu
(
    menu_id     bigint auto_increment primary key not null comment '主键',
    menu_name   varchar(50) default ''            not null comment '菜单名称',
    permission  varchar(60) default ''            not null comment '菜单权限',
    menu_type   tinyint     default 1 comment '菜单类型',
    parent_id   bigint      default 0 comment '父菜单ID',
    version     bigint      default 0 comment '乐观锁',
    deleted     tinyint     default 0 comment '逻辑删除',
    create_time datetime    default CURRENT_TIMESTAMP comment '创建时间',
    update_time datetime    default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP comment '更新时间'
) comment '菜单信息';

insert into tab_menu (menu_id, menu_name, permission, parent_id, menu_type)
values (1, '系统管理', '', 0, 0),

       (2, '用户管理', 'user:query', 1, 1),
       (3, '用户新增', 'user:add', 2, 2),
       (4, '用户删除', 'user:delete', 2, 2),
       (5, '用户编辑', 'user:edit', 2, 2),
       (6, '重置密码', 'user:reset', 2, 2),

       (7, '角色管理', 'role:query', 1, 1),
       (8, '角色新增', 'role:add', 7, 2),
       (9, '角色删除', 'role:delete', 7, 2),
       (10, '角色编辑', 'role:edit', 7, 2),

       (11, '菜单管理', 'menu:query', 1, 1),
       (12, '菜单新增', 'menu:add', 11, 2),
       (13, '菜单删除', 'menu:delete', 11, 2),
       (14, '菜单编辑', 'menu:edit', 11, 2),

       (15, '部门管理', 'dept:query', 1, 1),
       (16, '部门新增', 'dept:add', 15, 2),
       (17, '部门删除', 'dept:delete', 15, 2),
       (18, '部门编辑', 'dept:edit', 15, 2),

       (19, '岗位管理', 'post:query', 1, 1),
       (20, '岗位新增', 'post:add', 19, 2),
       (21, '岗位删除', 'post:delete', 19, 2),
       (22, '岗位编辑', 'post:edit', 19, 2),

       (23, '审批管理', '', 0, 0),

       (24, '考勤管理', 'attend:query', 23, 1),
       (25, '考勤审批', 'attend:add', 24, 2),

       (26, '请假管理', 'absence:query', 23, 1),
       (27, '请假审批', 'absence:add', 26, 2),

       (28, '报销管理', 'expense:query', 23, 1),
       (29, '报销审批', 'expense:add', 28, 2),

       (30, '字典管理', 'dict:add', 1, 1),
       (31, '字典新增', 'dict:add', 30, 2),
       (32, '字典删除', 'dict:delete', 30, 2),
       (33, '字典编辑', 'dict:edit', 30, 2),

       (34, '通知管理', 'notice:query', 0, 1),
       (35, '通知新增', 'notice:add', 34, 2),
       (36, '通知删除', 'notice:delete', 34, 2),
       (37, '通知编辑', 'notice:edit', 34, 2);

drop table if exists tab_role_menu;
create table tab_role_menu
(
    role_id bigint not null comment '角色ID',
    menu_id bigint not null comment '菜单ID',
    primary key (role_id, menu_id),
    key (menu_id)
) comment = '角色和菜单关联表';

drop table if exists tab_dept;
create table tab_dept
(
    dept_id     bigint primary key     not null auto_increment comment '主键',
    dept_name   varchar(50) default '' not null comment '部门名称',
    parent_id   bigint      default 0 comment '上级部门ID',
    dept_sort   integer(4)  default 0 comment '显示顺序',
    version     bigint      default 0 comment '乐观锁',
    deleted     tinyint     default 0 comment '逻辑删除',
    create_time datetime    default CURRENT_TIMESTAMP comment '创建时间',
    update_time datetime    default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP comment '更新时间',
    key (parent_id)
) comment = '部门表' auto_increment = 100;

drop table if exists tab_post;
create table tab_post
(
    post_id     bigint primary key     not null auto_increment comment '主键',
    post_name   varchar(50) default '' not null comment '岗位名称',
    post_sort   integer(4)  default 0 comment '显示顺序',
    version     bigint      default 0 comment '乐观锁',
    deleted     tinyint     default 0 comment '逻辑删除',
    create_time datetime    default CURRENT_TIMESTAMP comment '创建时间',
    update_time datetime    default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP comment '更新时间'
) comment = '岗位表' auto_increment = 100;

drop table if exists tab_absence;
create table tab_absence
(
    absence_id  bigint primary key     not null auto_increment comment '主键',
    user_id     bigint      default 0 comment '用户ID',
    type        tinyint     default 0 comment '请假类型',
    remark      varchar(50) default '' not null comment '说明',
    status      tinyint     default 0 comment '状态',
    version     bigint      default 0 comment '乐观锁',
    deleted     tinyint     default 0 comment '逻辑删除',
    start_time  datetime               not null comment '开始时间',
    end_time    datetime               not null comment '结束时间',
    create_time datetime    default CURRENT_TIMESTAMP comment '创建时间',
    update_time datetime    default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP comment '更新时间',
    key (user_id)
) comment = '请假表';

drop table if exists tab_notice;
create table tab_notice
(
    id          bigint primary key       not null auto_increment comment '主键',
    title       varchar(50)   default '' not null comment '标题',
    content     varchar(1024) default '' not null comment '内容',
    version     bigint        default 0 comment '乐观锁',
    deleted     tinyint       default 0 comment '逻辑删除',
    create_time datetime      default CURRENT_TIMESTAMP comment '创建时间',
    update_time datetime      default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP comment '更新时间'
) comment = '通知表';

drop table if exists tab_attendance;
create table tab_attendance
(
    id        bigint primary key     not null auto_increment comment '主键',
    user_id   bigint      default 0 comment '用户ID',
    status    tinyint     default 0 comment '状态',
    type      tinyint     default 0 comment '异常类型',
    remark    varchar(50) default '' null comment '备注',
    snapshot  varchar(50) default '' null comment '拍照',
    lng       varchar(20) default '' null comment '经度',
    lat       varchar(20) default '' null comment '纬度',
    sign_date date comment '签到日期',
    sign_time time comment '签到时间',
    exit_time time comment '签退时间',
    unique key (user_id, sign_date),
    key (sign_date)
) comment = '考勤表';

drop table if exists tab_expense;
create table tab_expense
(
    id           bigint primary key        not null auto_increment comment '主键',
    user_id      bigint         default 0 comment '用户ID',
    amount       decimal(11, 2) default 0 comment '报销金额',
    status       tinyint        default 0 comment '申请状态',
    remark       varchar(50)    default '' not null comment '费用说明',
    annex        varchar(99)    default '' comment '附件',
    expense_time datetime       default CURRENT_TIMESTAMP comment '消费日期',
    create_time  datetime       default CURRENT_TIMESTAMP comment '创建时间',
    update_time  datetime       default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP comment '更新时间',
    key (user_id)
) comment = '报销表';

drop table if exists tab_dict;
create table tab_dict
(
    id          bigint primary key not null auto_increment comment '主键',
    name        varchar(50) unique default '' comment '名称',
    value       varchar(255)       default '' comment '值',
    create_time datetime           default CURRENT_TIMESTAMP comment '创建时间',
    update_time datetime           default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP comment '更新时间'
) comment = '字典表';

insert into tab_dict (name, value)
values ('打卡点', '120.58419;31.29834'),
       ('工作时间', '9:00;18:00');

drop table if exists tab_oper_log;
create table tab_oper_log
(
    oper_id        bigint primary key not null auto_increment comment '主键',
    request_method varchar(10)   default '' comment '请求方式',
    oper_url       varchar(255)  default '' comment '请求URL',
    class_name     varchar(100)  default '' comment '类名',
    method         varchar(100)  default '' comment '方法名称',
    oper_param     varchar(2000) default '' comment '请求参数',
    json_result    varchar(2000) default '' comment '返回参数',
    status         tinyint       default 0 comment '操作状态（1正常 0异常）',
    oper_name      varchar(50)   default '' comment '操作人员',
    error_msg      varchar(2000) default '' comment '错误消息',
    oper_time      datetime comment '操作时间',
    key idx_sys_oper_log_s (status),
    key idx_sys_oper_log_ot (oper_time)
) comment = '操作日志' auto_increment = 100;