SET NAMES utf8mb4;
-- ----------------------------
-- Table structure for 用户认证
-- ----------------------------
DROP TABLE IF EXISTS `t_auth_user`;
CREATE TABLE `t_auth_user`  (
  `id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'id',
  `user_code` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '用户登录账号',
  `password` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '密码',
  `open_id` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'openid',
  `nike_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '昵称',
  `avatar_url` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '头像',
  `parent_user_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '父账号',
  `state` tinyint(1) NULL DEFAULT NULL COMMENT '0 无效，1有效',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '登录用户' ROW_FORMAT = Dynamic;
 -- 初始账号密码 admin/admin123!@#
INSERT INTO `db_operation`.`t_auth_user`(`id`, `user_code`, `password`, `open_id`, `nike_name`, `avatar_url`, `parent_user_id`, `state`, `create_time`) VALUES ('1305345329560653826', 'admin', '9b045c31ea6aff22f11de768a37fa0e5', NULL, NULL, 'http://pic.51yuansu.com/pic3/cover/01/69/80/595f67c2aff1e_610.jpg', NULL, 1, '2020-09-14 11:19:13');

-- ----------------------------
-- Table structure for 公众号列表
-- ----------------------------
DROP TABLE IF EXISTS `t_account`;
CREATE TABLE `t_account`  (
  `id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '主键',
  `authorizer_app_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '授权方appid',
  `authorizer_access_token` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '授权方accessToken',
  `authorizer_refresh_token` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '授权方刷新token',
  `expires_in` int(0) NULL DEFAULT NULL COMMENT 'token过期时间',
  `nick_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '昵称',
  `head_img` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '头像',
  `service_type_info` json NULL COMMENT '公众号类型',
  `verify_type_info` json NULL COMMENT '公众号认证类型',
  `user_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '原始 ID',
  `principal_name` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '主体名称',
  `alias` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '公众号所设置的微信号，可能为空',
  `business_info` json NULL COMMENT '用以了解功能的开通状况（0代表未开通，1代表已开通）',
  `qrcode_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '二维码图片的 URL',
  `state` tinyint(0) NULL DEFAULT NULL COMMENT '状态',
  `create_time` datetime(0) NULL DEFAULT NULL,
  `create_user_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '创建人',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '公众号' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for 公众号粉丝
-- ----------------------------
DROP TABLE IF EXISTS `t_account_fans`;
CREATE TABLE `t_account_fans`  (
  `id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'id',
  `account_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '公众号id',
  `open_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'openid',
  `nick_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'nickname',
  `sex` tinyint(1) NULL DEFAULT NULL COMMENT '用户的性别，值为1时是男性，值为2时是女性，值为0时是未知',
  `city` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '城市',
  `province` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '省份',
  `country` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '国家',
  `head_img_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'headimgurl',
  `union_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `subscribe` tinyint(1) NULL DEFAULT NULL COMMENT '1是关注',
  `subscribe_time` bigint(0) NULL DEFAULT NULL COMMENT '关注时间',
  `subscribe_scene` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '关注的渠道来源',
  `subscribe_scene_label` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '关注的渠道来源zh',
  `tag_id_list` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '标签id集合tagid_list',
  `last_inactive_time` bigint(0) NULL DEFAULT NULL COMMENT '最后互动时间',
  `group_id` int(0) NULL DEFAULT NULL COMMENT '分组',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '备注',
  `state` tinyint(1) NULL DEFAULT NULL COMMENT '状态码',
  `create_time` bigint(0) NULL DEFAULT NULL COMMENT '创建时间 秒数时间戳',
  `update_time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '公众号粉丝' ROW_FORMAT = Dynamic;



-- ----------------------------
-- Table structure for 公众号标签
-- ----------------------------
DROP TABLE IF EXISTS `t_account_tag`;
CREATE TABLE `t_account_tag`  (
  `id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'id',
  `wx_id` int(0) NULL DEFAULT NULL COMMENT '微信返回的id',
  `account_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '公众号id',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '标签名称',
  `fans_count` int(0) NULL DEFAULT NULL COMMENT '粉丝数量 微信字段count',
  `create_time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '公众号标签' ROW_FORMAT = Dynamic;


-- ----------------------------
-- Table structure for 公众号粉丝统计
-- ----------------------------
DROP TABLE IF EXISTS `t_account_stat`;
CREATE TABLE `t_account_stat`  (
  `id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'id',
  `account_id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '公众号id',
  `new_num` int(0) NULL DEFAULT 0 COMMENT '新增数量',
  `cancel_num` int(0) NULL DEFAULT 0 COMMENT '取关数量',
  `inactive_num` int(0) NULL DEFAULT 0 COMMENT '活跃数量',
  `total_fans_num` int(0) NULL DEFAULT 0 COMMENT '总粉丝数',
  `add_num` int(0) NULL DEFAULT 0 COMMENT '净增数量',
  `page_read_num` int(0) NULL DEFAULT 0 COMMENT '阅读数量',
  `new_rate` decimal(5, 2) NULL DEFAULT NULL COMMENT '新增比例',
  `cancel_rate` decimal(5, 2) NULL DEFAULT NULL COMMENT '取关比例',
  `inactive_rate` decimal(5, 2) NULL DEFAULT NULL COMMENT '活跃数量',
  `total_fans_rate` decimal(5, 2) NULL DEFAULT NULL COMMENT '总粉丝比例',
  `add_rate` decimal(5, 2) NULL DEFAULT NULL COMMENT '净增比例',
  `page_read_rate` decimal(5, 2) NULL DEFAULT NULL COMMENT '阅读比例',
  `seven_num` int(0) NULL DEFAULT NULL COMMENT '七天',
  `fifteen_num` int(0) NULL DEFAULT NULL COMMENT '15天',
  `stat_date` date NULL DEFAULT NULL COMMENT '统计日期',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建日期',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '公众号粉丝统计' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for 粉丝动作统计
-- ----------------------------
DROP TABLE IF EXISTS `t_fans_action_stat`;
CREATE TABLE `t_fans_action_stat`  (
  `id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'id',
  `account_id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '公众号id',
  `opend_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'openid',
  `action` int(0) NULL DEFAULT NULL COMMENT '粉丝动作 0-粉丝消息,1-新关注,2-关注,3-取关,4-扫描二维码,5-菜单点击',
  `create_time` bigint(0) NULL DEFAULT NULL COMMENT '创建时间 秒级',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '粉丝动作统计' ROW_FORMAT = Dynamic;


-- ----------------------------
-- Table structure for t_subscribe_scene
-- ----------------------------
DROP TABLE IF EXISTS `t_subscribe_scene`;
CREATE TABLE `t_subscribe_scene`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `key` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'key',
  `label` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'value',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '粉丝关注类型' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of 微信关注来源
-- ----------------------------
INSERT INTO `t_subscribe_scene` VALUES (1, 'ADD_SCENE_SEARCH', '公众号搜索');
INSERT INTO `t_subscribe_scene` VALUES (2, 'ADD_SCENE_ACCOUNT_MIGRATION', '公众号迁移');
INSERT INTO `t_subscribe_scene` VALUES (3, 'ADD_SCENE_PROFILE_CARD', '名片分享');
INSERT INTO `t_subscribe_scene` VALUES (4, 'ADD_SCENE_QR_CODE', '扫描二维码');
INSERT INTO `t_subscribe_scene` VALUES (5, 'ADD_SCENE_PROFILE_LINK', '图文页内名称点击');
INSERT INTO `t_subscribe_scene` VALUES (6, 'ADD_SCENE_PROFILE_ITEM', '图文页右上角菜单');
INSERT INTO `t_subscribe_scene` VALUES (7, 'ADD_SCENE_PAID', '支付后关注');
INSERT INTO `t_subscribe_scene` VALUES (8, 'ADD_SCENE_WECHAT_ADVERTISEMENT', '微信广告');
INSERT INTO `t_subscribe_scene` VALUES (9, 'ADD_SCENE_OTHERS', '其他');



-- ----------------------------
-- Table structure for t_article_stat 图文阅读统计
-- ----------------------------
DROP TABLE IF EXISTS `t_article_stat`;
CREATE TABLE `t_article_stat`  (
  `id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'id',
  `account_id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '公众号id',
  `stat_date` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '数据日期',
  `msg_id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '微信返回msg_id',
  `title` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '标题',
  `int_page_read_user` int(0) NULL DEFAULT NULL COMMENT '图文页（点击群发图文卡片进入的页面）的阅读人数',
  `int_page_read_count` int(0) NULL DEFAULT NULL COMMENT '图文页的阅读次数',
  `ori_page_read_user` int(0) NULL DEFAULT NULL COMMENT '原文页（点击图文页“阅读原文”进入的页面）的阅读人数，无原文页时此处数据为0',
  `ori_page_read_count` int(0) NULL DEFAULT NULL COMMENT '原文页的阅读次数',
  `share_user` int(0) NULL DEFAULT NULL COMMENT '分享的人数',
  `share_count` int(0) NULL DEFAULT NULL COMMENT '分享的次数',
  `add_to_fav_user` int(0) NULL DEFAULT NULL COMMENT '收藏的人数',
  `add_to_fav_count` int(0) NULL DEFAULT NULL COMMENT '收藏的次数',
  `create_time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '图文阅读统计' ROW_FORMAT = Dynamic;