set names utf8;

alter table `book_comment` add `goods` int(6) DEFAULT NULL COMMENT '点赞数';
alter table `book_comment` add `channel` varchar(20) DEFAULT NULL COMMENT '数据来源';
alter table `book_comment` add `messages` int(6) DEFAULT NULL;

alter table `media` add `recommends` int(6) DEFAULT NULL COMMENT '推荐数';
alter table `media` add `collections` int(6) DEFAULT NULL COMMENT '收藏数';
alter table `media` add `totalScore` int(11) DEFAULT NULL COMMENT '总分';
alter table `media` add `totalScoreTimes` int(11) DEFAULT NULL COMMENT '评分人数';

alter table `user_book_annotation` add `channel` varchar(20) DEFAULT NULL;

alter table `user` add `attention` int(6) DEFAULT NULL COMMENT '关注';
alter table `user` add `fans` int(6) DEFAULT NULL COMMENT '粉丝';

alter table `user_extra` add `noteJurisdiction` int(6) DEFAULT NULL COMMENT '0：仅自己可见，1：好友可见，2：所有人可见';
alter table `user_extra` add `collectionJurisdiction` int(6) DEFAULT NULL COMMENT '0：仅自己可见，1：好友可见，2：所有人可见';

alter table user_book_annotation add type int(1) DEFAULT 1 COMMENT '1：PDF，2：TXT';

DROP TABLE IF EXISTS `book_leave_comment`;
CREATE TABLE `book_leave_comment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bookCommentId` int(11) NOT NULL,
  `parentId` int(11) DEFAULT NULL,
  `parentUserId` int(11) DEFAULT NULL,
  `idFormat` varchar(500) DEFAULT NULL,
  `content` varchar(2000) DEFAULT NULL,
  `userId` int(11) NOT NULL,
  `userName` varchar(100) DEFAULT NULL,
  `channel` varchar(20) DEFAULT NULL,
  `createTime` datetime NOT NULL,
  `status` int(1) DEFAULT NULL COMMENT '状态：0正常 1等待审核 2审核不通过',
  `messageStatus` int(1) DEFAULT NULL COMMENT '留言状态0：未查看，1：未查看',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `user_media_collection`;
CREATE TABLE `user_media_collection` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) DEFAULT NULL,
  `userName` varchar(100) DEFAULT NULL,
  `mediaId` int(11) DEFAULT NULL,
  `title` varchar(200) DEFAULT NULL,
  `createTime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user` (`userId`),
  KEY `idx_media` (`mediaId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `user_media_recommend`;
CREATE TABLE `user_media_recommend` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) DEFAULT NULL,
  `userName` varchar(100) DEFAULT NULL,
  `mediaId` int(11) DEFAULT NULL,
  `title` varchar(200) DEFAULT NULL,
  `createTime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user` (`userId`),
  KEY `idx_media` (`mediaId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `media_comment`;
CREATE TABLE `media_comment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mediaId` int(11) DEFAULT NULL,
  `title` varchar(200) DEFAULT NULL,
  `userId` int(11) DEFAULT NULL,
  `userName` varchar(100) DEFAULT NULL,
  `commentContent` varchar(2000) DEFAULT NULL,
  `submitTime` datetime DEFAULT NULL,
  `goods` int(5) DEFAULT NULL COMMENT '点赞',
  `channel` varchar(20) DEFAULT NULL,
  `status` int(1) DEFAULT NULL,
  `isTop` int(1) DEFAULT NULL,
  `topTime` datetime DEFAULT NULL,
  `messages` int(6) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `media_leave_comment`;
CREATE TABLE `media_leave_comment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mediaCommentId` int(11) NOT NULL,
  `parentId` int(11) DEFAULT NULL,
  `parentUserId` int(11) DEFAULT NULL,
  `idFormat` varchar(500) DEFAULT NULL,
  `content` varchar(2000) DEFAULT NULL,
  `userId` int(11) NOT NULL,
  `userName` varchar(100) DEFAULT NULL,
  `channel` varchar(20) DEFAULT NULL,
  `createTime` datetime NOT NULL,
  `status` int(1) DEFAULT NULL,
  `messageStatus` int(1) DEFAULT NULL COMMENT '留言状态0：未查看，1：未查看',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `user_attention`;
CREATE TABLE `user_attention` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) DEFAULT NULL,
  `attentionUserId` int(11) DEFAULT NULL COMMENT '被关注用户ID',
  `attentionUserName` varchar(100) DEFAULT NULL COMMENT '被关注用户名',
  `createTime` datetime DEFAULT NULL,
  `status` int(1) DEFAULT NULL COMMENT '状态 0：未查看，1：已经查看',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `user_book_comment_goods`;
CREATE TABLE `user_book_comment_goods` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) DEFAULT NULL,
  `userName` varchar(100) DEFAULT NULL,
  `bookCommentId` int(11) DEFAULT NULL,
  `channel` varchar(20) DEFAULT NULL,
  `createTime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `user_media_comment_goods`;
CREATE TABLE `user_media_comment_goods` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) DEFAULT NULL,
  `userName` varchar(100) DEFAULT NULL,
  `mediaCommentId` int(11) DEFAULT NULL,
  `channel` varchar(20) DEFAULT NULL,
  `createTime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `user_news_feed`;
CREATE TABLE `user_news_feed` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `objectId` int(11) NOT NULL,
  `userId` int(11) NOT NULL,
  `type` int(1) NOT NULL COMMENT '1：书籍收藏，2：书籍推荐，5：笔记，3：音视频收藏，4：音视频推荐',
  `createTime` datetime NOT NULL,
  `channel` varchar(20) NOT NULL,
  `goods` int(6) NOT NULL DEFAULT '0',
  `messages` int(6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `user_news_feed_comment`;
CREATE TABLE `user_news_feed_comment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userNewsFeedId` int(11) NOT NULL,
  `parentId` int(11) DEFAULT NULL,
  `parentUserId` int(11) DEFAULT NULL,
  `idFormat` varchar(500) DEFAULT NULL,
  `content` varchar(2000) DEFAULT NULL,
  `userId` int(11) NOT NULL,
  `userName` varchar(100) DEFAULT NULL,
  `channel` varchar(20) DEFAULT NULL,
  `createTime` datetime NOT NULL,
  `status` int(1) DEFAULT NULL COMMENT '留言状态0：未查看，1：未查看',
  `messageStatus` int(1) DEFAULT NULL COMMENT '留言状态0：未查看，1：未查看',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `user_news_feed_goods`;
CREATE TABLE `user_news_feed_goods` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `userName` varchar(100) NOT NULL,
  `userNewsFeedId` int(11) NOT NULL,
  `channel` varchar(20) NOT NULL,
  `createTime` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

alter table activity add column isPush int(1) comment '0：不推送，1:推送';
alter table activity add column pushStatus int(1) comment '0：推送失败，1:推送成功';
alter table activity add column updateTime datetime comment '更新时间';

alter table activity_statistics add column updateTime datetime comment '更新时间';

alter table book add column updateTime datetime comment '更新时间';
alter table media add column updateTime datetime comment '更新时间';

alter table news add column sourceStatus int(1) comment '0:本地创建，1:上级推送';
alter table news add column sourceId int(1) comment '下发新闻ID';
alter table news add column isPush int(1) comment '0：不推送，1:推送';
alter table news add column pushStatus int(1) comment '0：推送失败，1:推送成功';

alter table user add column ssoId varchar(32) comment '单点登陆用户唯一标识';

alter table user_borrow_book add column updateTime datetime comment '更新时间';
alter table book_sort_zt add(bookCount int(11) default 0);
alter table book_sort_specialty add(bookCount int(11) default 0);

DROP TABLE IF EXISTS `user_read_records_time`;
CREATE TABLE `user_read_records_time` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `version` int(5) DEFAULT NULL,
  `userId` int(11) DEFAULT NULL,
  `bookId` int(11) DEFAULT NULL,
  `type` int(1) DEFAULT NULL COMMENT '类型（1：PDF，2：TXT）',
  `nums` int(11) DEFAULT NULL COMMENT 'type为1时，值为pdf资源页码\n            type为2时，值为txt资源字数',
  `currentNum` int(11) DEFAULT NULL COMMENT 'type为1时，值为pdf资源页码\n            type为2时，值为txt资源字数',
  `startTime` datetime DEFAULT NULL COMMENT '开始阅读时间',
  `endTime` datetime DEFAULT NULL COMMENT '结束阅读时间',
  `duration` int(20) DEFAULT NULL COMMENT '时长（单位秒）',
  `sessionIdAnduuId` varchar(68) COLLATE utf8_bin DEFAULT NULL COMMENT 'web阅读时当前会话ID，判断是否是本次阅读',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='用户阅读图书时长记录';

alter table school add column locationCode varchar(6) comment '区域编号';
alter table user add column locationCode varchar(6) comment '区域编号';
delete from sys_function where levelCode='1004';

DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `roleName` varchar(50) DEFAULT NULL,
  `dataScope` int(1) DEFAULT NULL COMMENT '-1：所有，0：班级，1：学校，2：区县，3：市，4：省',
  `funcIds` varchar(200) DEFAULT NULL,
  `status` int(1) DEFAULT '1' COMMENT '0：禁用，1：启用',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
alter table user add column roleId int(11) comment '角色ID';

INSERT INTO sys_function (id,name, link, levelCode, lanFunc, netFunc, status) VALUES (58,'地区排行榜', '/admin/statistics/readRankingStatistics.action', '8008', '1', '2', '1');
INSERT INTO `role` VALUES ('1', '省管理员', '4', '9, 10, 11, 13, 14, 15, 16, 17, 19, 20, 21, 23, 28, 29, 55, 34, 35, 36, 47, 37, 38, 39, 40, 41, 56, 42, 43, 44, 45, 53, 54, 46, 52', '1','58');
INSERT INTO `role` VALUES ('2', '市管理员', '3', '9, 10, 11, 13, 14, 15, 16, 17, 19, 20, 21, 59, 23, 28, 29, 55, 34, 35, 36, 47, 37, 38, 39, 40, 41, 56, 42, 43, 44, 45, 53, 54, 46, 52', '1','58');
INSERT INTO `role` VALUES ('3', '区管理员', '2', '9, 10, 11, 13, 14, 15, 16, 17, 19, 20, 21, 59, 23, 28, 29, 55, 34, 35, 36, 47, 37, 38, 39, 40, 41, 56, 42, 43, 44, 45, 53, 54, 46, 52', '1','58');
INSERT INTO `role` VALUES ('4', '校管理员', '1', '9, 10, 11, 13, 14, 15, 16, 17, 18, 19, 20, 21, 59, 23, 28, 29, 55, 34, 35, 36, 47, 37, 38, 39, 40, 41, 56, 42, 43, 44, 45, 53, 54, 46, 52', '1','58');
INSERT INTO `role` VALUES ('5', '班级管理员', '0', '16, 21, 34, 35, 36, 47, 37, 38, 39, 40, 41, 56, 42, 43, 44, 45, 53, 54, 46, 52', '1');

CREATE TABLE `operation_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `loginName` varchar(50) NOT NULL,
  `functionName` varchar(50) NOT NULL COMMENT '功能名称',
  `operationType` varchar(20) NOT NULL COMMENT '操作类型',
  `operationData` text,
  `createTime` datetime NOT NULL COMMENT '操作时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `user_history_password` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `password` varchar(32) NOT NULL,
  `createTime` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `password_sensitive_words` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `words` varchar(20) NOT NULL,
  `isCaseSensitive` int(1) NOT NULL COMMENT '0：否，1：是',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


alter table user_read_records add(channel varchar(200),sessionIdAnduuId varchar(200));
alter table `user_book_recommend` add `channel` varchar(20) DEFAULT NULL COMMENT '数据来源';
alter table `user_borrow_book` add `channel` varchar(20) DEFAULT NULL COMMENT '数据来源';
alter table `user_book_collection` add `channel` varchar(20) DEFAULT NULL COMMENT '数据来源';