-- ----------------------------
-- 日期:2022-06-07
-- 提交人:青杰
-- 勋章相关功能的表结构
-- ----------------------------

CREATE TABLE `member_medal`
(
    `id`                     bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
    `medal_name`             varchar(100)        NOT NULL COMMENT '勋章名称',
    `medal_icon`             varchar(200)        NOT NULL COMMENT '勋章图标',
    `medal_video`            varchar(200) DEFAULT NULL COMMENT '勋章视频',
    `medal_detail`           text                NOT NULL COMMENT '勋章详细',
    `medal_type`             char(2)             NOT NULL COMMENT '勋章类型：\r\n00-勋章；\r\n01-装备；',
    `medal_rarity`           varchar(10)  DEFAULT NULL COMMENT '勋章稀有度：\r\n00-消费级；\r\n01-工业级；\r\n02-军规级；\r\n03-受限；\r\n04-保密；\r\n05-隐秘；\r\n06-违禁；',
    `condition_logic`        varchar(10)  DEFAULT NULL COMMENT '颁发条件逻辑：\r\nAND-同时满足；\r\nOR-任意满足；',
    `sort`                   bigint(20)          NOT NULL COMMENT '排序',
    `status`                 char(2)             NOT NULL COMMENT '发布状态：\r\n00-草稿；\r\n01-待审核；\r\n02-已驳回；\r\n03-待发布；\r\n04-定时发布；\r\n05-已发布；\r\n06-已下线；',
    `timed_public_date`      datetime     DEFAULT NULL COMMENT '定时发布时间',
    `public_date`            datetime     DEFAULT NULL COMMENT '发布时间',
    `create_member_id`       bigint(20) unsigned NOT NULL COMMENT '创建用户ID',
    `create_member_nickname` longtext COMMENT '创建用户昵称',
    `create_member_avatar`   varchar(255) DEFAULT NULL COMMENT '创建用户头像',
    `create_date`            datetime            NOT NULL COMMENT '创建时间',
    PRIMARY KEY (`id`),
    KEY `idx_type_sort` (`sort`, `medal_type`) USING BTREE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='勋章表';

CREATE TABLE `member_medal_audit`
(
    `id`                     bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
    `medal_id`               bigint(20)          NOT NULL COMMENT '勋章ID',
    `audit_status`           char(2)             NOT NULL COMMENT '审核状态：\r\n00-待审核；\r\n01-审核通过；\r\n02-审核不通过；',
    `reject_reason`          varchar(200)        DEFAULT NULL COMMENT '审核不通过原因',
    `submit_member_id`       bigint(20) unsigned NOT NULL COMMENT '提审用户ID',
    `submit_member_nickname` longtext COMMENT '送审用户昵称',
    `submit_member_avatar`   varchar(255)        DEFAULT NULL COMMENT '送审用户头像',
    `submit_date`            datetime            NOT NULL COMMENT '送审时间',
    `audit_member_id`        bigint(20) unsigned DEFAULT NULL COMMENT '审核用户ID',
    `audit_member_nickname`  longtext COMMENT '审核用户昵称',
    `audit_member_avatar`    varchar(255)        DEFAULT NULL COMMENT '审核用户头像',
    `audit_date`             datetime            DEFAULT NULL COMMENT '审核时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_medal_id` (`medal_id`) COMMENT '勋章唯一键'
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='勋章审核表';

CREATE TABLE `member_medal_award_condition`
(
    `id`              bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
    `medal_id`        bigint(20) unsigned NOT NULL COMMENT '勋章ID',
    `award_condition` char(2)             NOT NULL COMMENT '颁发条件：\r\n00-登录次数；\r\n01-在线时长；\r\n02-账号等级；\r\n03-浏览数量；',
    `condition_value` varchar(200)        NOT NULL COMMENT '条件值',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_medal_condition` (`medal_id`, `award_condition`) COMMENT '勋章条件唯一键'
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='勋章颁发条件表';

CREATE TABLE `member_medal_award_record`
(
    `id`              bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
    `member_id`       bigint(20) unsigned NOT NULL COMMENT '用户ID',
    `member_nickname` longtext COMMENT '用户昵称',
    `member_avatar`   varchar(255)        DEFAULT NULL COMMENT '用户头像',
    `medal_id`        bigint(20) unsigned NOT NULL COMMENT '勋章ID',
    `award_date`      datetime            NOT NULL COMMENT '颁发日期',
    `award_type`      char(2)             NOT NULL COMMENT '颁发方式：\r\n00-手动颁发；\r\n01-自动颁发；',
    `award_member_id` bigint(20) unsigned DEFAULT NULL COMMENT '颁发用户ID',
    `award_reason`    varchar(200)        DEFAULT NULL COMMENT '颁发原因',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_member_medal` (`member_id`, `medal_id`) COMMENT '用户勋章唯一键'
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='勋章颁发记录表';

CREATE TABLE `member_action_statistics`
(
    `memberId`   bigint UNSIGNED NOT NULL COMMENT '用户ID',
    `loginTimes` bigint UNSIGNED NOT NULL COMMENT '登录次数',
    `onlineTime` bigint UNSIGNED NOT NULL COMMENT '在线时长',
    `viewCount`  bigint UNSIGNED NOT NULL COMMENT '浏览数量',
    PRIMARY KEY (`memberId`),
    UNIQUE INDEX `uk_member_id` (`memberId`) COMMENT '用户ID唯一键'
) COMMENT = '用户行为统计表';

-- ----------------------------
-- 日期:2022-06-15
-- 提交人:青杰
-- 邀请码功能
-- 增加首次激活时间、邀请码支持永久有效
-- ----------------------------
ALTER TABLE member_invitation
    ADD activeTime DATETIME NULL COMMENT '首次激活时间';
ALTER TABLE member_invitation
    MODIFY COLUMN invitationTime DATE NULL COMMENT '邀请码截止有效时间';

CREATE TABLE `member_action_log`
(
    `id`            bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
    `memberId`      bigint(20) unsigned NOT NULL COMMENT '用户ID',
    `actionType`    char(2)             NOT NULL COMMENT '行为类型：\r\n00-密码登录；\r\n01-短信登录；\r\n02-微信登录；\r\n03-免验证登录',
    `actionDate`    datetime            NOT NULL COMMENT '行为时间',
    `actionAddress` varchar(100) DEFAULT NULL COMMENT '行为地址',
    `ipAddress`     varchar(50)  DEFAULT NULL COMMENT 'IP地址',
    `deviceId`      varchar(100) DEFAULT NULL COMMENT '设备标识符',
    PRIMARY KEY (`id`),
    KEY `idx_member_id` (`memberId`) USING BTREE,
    KEY `idx_action_date` (`actionDate`) USING BTREE
) ENGINE = InnoDB
  AUTO_INCREMENT = 12
  DEFAULT CHARSET = utf8mb4 COMMENT ='用户行为日志表';

-- ----------------------------
-- 日期:2022-06-24
-- 提交人:青杰
-- 邀请码功能
-- 邀请码生成位置
-- ----------------------------
ALTER TABLE member_setting
    ADD invitationCodeIndex BIGINT UNSIGNED NULL COMMENT '邀请码当前生成位置';

-- ----------------------------
-- 日期:2022-06-27
-- 提交人:青杰
-- 邀请记录的被邀请人唯一键
-- ----------------------------
ALTER TABLE member_invitationlog
    MODIFY COLUMN inviteeID BIGINT NOT NULL COMMENT '被邀请人ID';
CREATE INDEX uk_invitation USING BTREE ON member_invitationlog (inviteeID);


#####日期:2022-06-28
#####提交人:蔡松林
#####sql 集结打卡添加字段
#####sql语句
ALTER TABLE MEMBER_CLOCKIN_MISSION add column mediaInfoType tinyint(2) default  null COMMENT '素材类型' after mediaInfo;


#####日期:2022-07-05
#####提交人:蔡松林
#####sql 配置表添加字段
#####sql语句
alter table member_setting add column expiretime  int(10)    DEFAULT null COMMENT '登录超时时间';
alter table member_setting add column expiretimeType tinyint(2) DEFAULT '1' COMMENT '登录超时时间单位 1：天   2：小时  3：分钟 ';

#####日期:2022-07-06
#####提交人:罗凡
#####sql 会员表增加是否私密账号标记
#####sql语句
alter table member_member add column privacyFlag  tinyint(2) DEFAULT '0' COMMENT '是否是私密账号 0：非私密 1：私密';



#####日期:2022-07-11
#####提交人:蔡松林
#####sql 会员表增加拉黑记录表
#####sql语句
CREATE TABLE `member_black` (
  `memberId` bigint(20) NOT NULL,
  `expiretime` bigint(20) NOT NULL COMMENT '禁用时长',
  `reason` varchar(500) DEFAULT NULL,
  `addTime` datetime DEFAULT NULL,
  `addUser` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`memberId`) USING BTREE,
  KEY `idx_expiretime` (`expiretime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

#####日期:2022-07-12
#####提交人:罗凡
#####sql 会员表增加用户剩余积分记录表
#####sql语句
DROP TABLE IF EXISTS `member_integral_available`;
CREATE TABLE `member_integral_available`  (
  `id` bigint(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `member_id` int(11) NOT NULL COMMENT '用户ID',
  `integral` double NOT NULL DEFAULT 0 COMMENT '剩余可用积分数',
  `logType` int(11) NULL DEFAULT NULL COMMENT '积分类型 1:注册;2:签到;3:评论;4:分享;5:上传视频图片;6:设置头像;7:积分兑换;8:摇一摇中奖;9:参与摇一摇;10:小游戏中奖;11:邀请注册;12:其他;13:兑吧；14:学习考试积分',
  `addtime` datetime(0) NULL DEFAULT NULL COMMENT '加积分的时间',
  `expireTime` datetime(0) NULL DEFAULT NULL COMMENT '积分过期时间',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_expiretime` (`expireTime`),
  KEY `idx_member_id` (`member_id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '会员剩余可用积分表' ROW_FORMAT = Compact;

#####日期:2022-07-12
#####提交人:刘豪
#####sql 用户可用成长值记录表
#####sql语句
CREATE TABLE `member_available_growth_value` (
    `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
    `memberId` int(11) NOT NULL COMMENT '用户ID',
    `growthValue` double NOT NULL DEFAULT '0' COMMENT '剩余可用成长值',
    `logType` int(11) DEFAULT NULL COMMENT '成长值类型：1.注册2.签到3.评论4.分享5.设置头像6.商城7.活动8.乐园9.摇一摇10.小游戏中奖11.邀请注册12.其他13.兑吧14.学习考试积分15.阅读16.课程学习17.课程评价18.课程分享19.模拟考试20.随机练习21.挑战答题22.视频观看23.活动奖励24.每日登录25.生日当月送积分26.升级发放积分礼包27.成功邀请用户，添加积分28.接受邀请，添加积分',
    `addTime` bigint(20) NOT NULL COMMENT '成长值获取时间',
    `expireTime` bigint(20) DEFAULT NULL COMMENT '成长值过期时间',
    PRIMARY KEY (`id`) USING BTREE,
    KEY `idx_expiretime` (`expireTime`),
    KEY `idx_memberId` (`memberId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会员剩余可用成长值表';

-- ----------------------------
-- 日期:2022-07-16
-- 提交人:青杰
-- 邀请码生成的起始位置
-- ----------------------------
UPDATE member_setting
SET invitationCodeIndex = 66666666;

#####日期:2022-08-08
#####提交人:李洲
#####sql 会员信息审核表修改字段类型大小
#####sql语句
alter table member_audit modify column id bigint(20) not null DEFAULT null comment '';
alter table member_audit modify column member_id bigint(20) not null DEFAULT null comment '会员主表id';
#####sql ai日志表修改字段类型大小
#####sql语句
alter table aitasklog modify column uid bigint(20) DEFAULT null comment '用户ID';
alter table aitasklog modify column auditId bigint(20) DEFAULT null comment '审核ID';

#####日期:2022-08-11
#####提交人:李洲
#####sql 邀请记录表修改字段类型大小
#####sql语句
alter table member_invitationlog modify column id bigint(20) not null DEFAULT null comment '';
alter table member_invitationlog modify column inviterID bigint(20) not null DEFAULT null comment '邀请人ID';
alter table member_invitationlog modify column inviteeID bigint(20) not null DEFAULT null comment '被邀请人ID';


#####日期:2022-08-15
#####提交人:李洲
#####sql 会员表修改字段字符集
#####sql语句
ALTER TABLE member_member MODIFY COLUMN nickName longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '用户昵称';
ALTER TABLE member_member MODIFY COLUMN intro VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '用户简介';