CREATE TABLE `sys_dict` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `version` INT NOT NULL DEFAULT 0,
  `pid` INT NOT NULL  COMMENT '上级ID',
  `module_id` tinyint NULL COMMENT '模块',
  `dict_key` varchar(255) NULL COMMENT '字典标识',
  `value` varchar(255) NULL COMMENT '字典值',
  `description` varchar(255) NULL COMMENT '描述',
  `sort` int NOT NULL DEFAULT 0 COMMENT '排序',
  `status` int NOT NULL COMMENT '状态 0启用 1禁用',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `updated_by` bigint  NULL COMMENT '更新人',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE `uniq_dict_key` (`dict_key`, `pid`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '字典表';

CREATE TABLE `sys_organization` (
  `org_id` BIGINT NOT NULL COMMENT '组织ID',
  `pid` BIGINT DEFAULT 0 NOT NULL COMMENT '上级组织ID',
  `org_name` varchar(255) NULL COMMENT '组织名称',
  `org_code` varchar(128) NULL COMMENT '组织编码',
  `org_type` int NOT NULL COMMENT '组织类型',
  `description` varchar(255) NULL COMMENT '描述',
  `sort` int NOT NULL DEFAULT 0 COMMENT '排序',
  `status` int NOT NULL COMMENT '状态',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `updated_by` bigint  NULL COMMENT '更新人',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`org_id`),
  UNIQUE `uniq_org_code` (`org_code`, `pid`),
  UNIQUE `uniq_org_name` (`org_name`, `pid`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '组织表';

CREATE TABLE `sys_organization_manager` (
  `id` BIGINT NOT NULL COMMENT 'ID',
  `version` INT NOT NULL DEFAULT 0,
  `org_id` bigint NOT NULL COMMENT '组织ID',
  `user_id` bigint NOT NULL COMMENT '用户ID',
  `rank` int NOT NULL COMMENT '当前用户对组织管理的级别，1为最高级别',
  `status` int NOT NULL COMMENT '状态',
  `description` varchar(255) NULL,
  `created_by` bigint NOT NULL COMMENT '创建人',
  `updated_by` bigint  NULL COMMENT '更新人',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE `uniq_org_user` (`org_id`, `user_id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '组织负责人表';

CREATE TABLE `sys_permission` (
  `perm_id` BIGINT NOT NULL COMMENT '权限ID',
  `pid` BIGINT NOT NULL DEFAULT 0 COMMENT '上级权限ID',
  `perm_name` varchar(255) NOT NULL COMMENT '权限名称',
  `perm_key` varchar(255) NOT NULL COMMENT '权限标识',
  `url` varchar(255) NULL COMMENT '权限路径',
  `avatar_url` varchar(255) NULL COMMENT '权限图标',
  `description` varchar(255) NULL COMMENT '描述',
  `perm_type` int NOT NULL COMMENT '权限类型',
  `is_visible` int NOT NULL DEFAULT 0 COMMENT '是否可见',
  `version` INT NOT NULL DEFAULT 0,
  `sort` int NOT NULL DEFAULT 0 COMMENT '排序',
  `status` int NOT NULL COMMENT '状态',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `updated_by` bigint  NULL COMMENT '更新人',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`perm_id`),
  UNIQUE `uniq_pid_name` (`perm_name`, `pid`),
  UNIQUE `uniq_perm_key` (`perm_key`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '权限表';

CREATE TABLE `sys_re_role_permission` (
  `id` BIGINT NOT NULL COMMENT '关系ID',
  `role_id` BIGINT NOT NULL COMMENT '角色ID',
  `perm_id` BIGINT NOT NULL COMMENT '权限ID',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `updated_by` bigint  NULL COMMENT '更新人',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE `uniq_perm_role` (`role_id`, `perm_id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '用户组织关系表';

CREATE TABLE `sys_re_user_organization` (
  `id` BIGINT NOT NULL COMMENT '关系ID',
  `user_id` BIGINT NOT NULL COMMENT '用户ID',
  `org_id` BIGINT NOT NULL COMMENT '组织ID',
  `version` INT NOT NULL DEFAULT 0,
  `created_by` bigint NOT NULL COMMENT '创建人',
  `updated_by` bigint  NULL COMMENT '更新人',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE `uniq_user_org` (`user_id`, `org_id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '用户组织关系表';

CREATE TABLE `sys_re_user_role` (
  `id` BIGINT NOT NULL COMMENT '关系ID',
  `user_id` BIGINT NOT NULL COMMENT '用户ID',
  `role_id` BIGINT NOT NULL COMMENT '角色ID',
  `version` INT NOT NULL DEFAULT 0,
  `created_by` bigint NOT NULL COMMENT '创建人',
  `updated_by` bigint  NULL COMMENT '更新人',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE `uniq_user_role` (`user_id`, `role_id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '用户角色关联表';;

CREATE TABLE `sys_role` (
  `role_id` BIGINT NOT NULL COMMENT '角色ID',
  `pid` BIGINT NOT NULL COMMENT '上级角色ID',
  `role_name` varchar(255) NOT NULL COMMENT '角色名称',
  `role_key` varchar(255) NOT NULL COMMENT '角色表示',
  `description` varchar(255) NULL COMMENT '角色描述',
  `status` int NOT NULL COMMENT '状态',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `updated_by` bigint  NULL COMMENT '更新人',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`role_id`),
  UNIQUE `uniq_role_pid` (`role_name`, `pid`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '用户角色表';

CREATE TABLE `sys_user`  (
  `user_id` bigint NOT NULL COMMENT '用户ID',
  `pid` bigint NOT NULL COMMENT '上级ID',
  `username` varchar(255) NOT NULL COMMENT '用户名，全小写',
  `nickname` varchar(255) NOT NULL COMMENT '用户昵称',
  `email` varchar(255) NOT NULL COMMENT '邮箱',
  `phone` varchar(255) NULL COMMENT '手机号',
  `avatar_url` varchar(255) NULL COMMENT '图标',
  `user_type` tinyint NULL DEFAULT NULL COMMENT '用户类型从字典取',
  `status` tinyint DEFAULT 0 NOT NULL COMMENT '状态',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `updated_by` bigint  NULL COMMENT '更新人',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  UNIQUE `uniq_username` (`username`),
  UNIQUE `uniq_email` (`email`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '用户基础信息表';

CREATE TABLE `sys_user_auth`  (
  `user_id` bigint NOT NULL COMMENT '用户ID',
  `password_hash` varchar(255) NOT NULL COMMENT '用户密码',
  `password_modified` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '上一次修改时间',
  `password_expire` DATETIME NULL DEFAULT NULL COMMENT '过期时间',
  PRIMARY KEY (`user_id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '用户密码表';

CREATE TABLE `sys_user_auth_history`  (
  `id` bigint NOT NULL COMMENT '历史记录自增主键',
  `user_id` bigint NOT NULL COMMENT '用户id',
  `password_hash` varchar(255) NOT NULL COMMENT '历史密码值，注意需要限制密码更改次数',
  `modified_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '上次修改的时间',
  PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '历史密码表';

CREATE TABLE `sys_user_field_definition` (
  `field_id` BIGINT NOT NULL COMMENT '用户拓展字段ID',
  `version` INT NOT NULL DEFAULT 0,
  `field_name` varchar(255) NOT NULL COMMENT '拓展字段名称',
  `field_key` varchar(255) NOT NULL COMMENT '拓展字段标识',
  `field_type` tinyint NOT NULL COMMENT '拓展字段类型0文字1数字2日期3单选4多选5附件6富文本',
  `dict_module` int NULL COMMENT '字典集模块值',
  `is_required` tinyint NOT NULL DEFAULT 0 COMMENT '是否必填0否1是',
  `limit` int NULL COMMENT '值长度，最大2048',
  `description` varchar(255) NULL COMMENT '描述',
  `default_value` varchar(255) NULL COMMENT '默认值',
  `default_options` varchar(255) NULL COMMENT '选项值',
  `sort` int NOT NULL DEFAULT 0 COMMENT '排序',
  `status` int NOT NULL COMMENT '状态',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `updated_by` bigint  NULL COMMENT '更新人',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`field_id`),
  UNIQUE `uniq_field_name` (`field_name`),
  UNIQUE `uniq_field_key` (`field_key`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '用户拓展字段定义表';

CREATE TABLE `sys_user_field_value`  (
  `id` bigint NOT NULL COMMENT '用户拓展字段记录id',
  `user_id` bigint NOT NULL COMMENT '用户ID',
  `field_id` int NOT NULL COMMENT '字段ID',
  `value` varchar(4096) NULL COMMENT '用户拓展字段值',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `updated_by` bigint  NULL COMMENT '更新人',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE `uniq_user_field` (`user_id`, `field_id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '用户拓展字段记录表';

CREATE TABLE `sys_profile` (
  `id` BIGINT NOT NULL COMMENT '系统配置自增主键',
  `version` INT NOT NULL DEFAULT 0,
  `name` varchar(32) NOT NULL COMMENT '系统配置名称',
  `profile_key` varchar(255) NOT NULL COMMENT '系统配置记录Key',
  `description` varchar(255) NULL COMMENT '系统配置记录描述',
  `content` varchar(255) NULL COMMENT '系统配置记录值',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `updated_by` bigint  NULL COMMENT '更新人',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE `uniq_name` (`name`),
  UNIQUE `uniq_profile_key` (`profile_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '系统配置';

CREATE TABLE `sys_module` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '系统模块自增主键',
  `version` INT NOT NULL DEFAULT 0,
  `name` varchar(32) NOT NULL COMMENT '系统模块名称',
  `module_key` varchar(255) NOT NULL COMMENT '系统模块记录Key',
  `description` varchar(255) NULL COMMENT '系统模块记录描述',
  `sort` int NOT NULL DEFAULT 0 COMMENT '排序',
  `status` int NOT NULL COMMENT '状态',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `updated_by` bigint  NULL COMMENT '更新人',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE `uniq_module_key` (`module_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '系统模块';