/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

CREATE DATABASE IF NOT EXISTS `wxpy` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;
USE `wxpy`;

CREATE TABLE IF NOT EXISTS `access_frequency` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `puid` char(8) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '访问者(当前登录人) puid',
  `puid_name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'puid 名，冗余字段，标准的在 user 表中',
  `spend_time` bigint(20) NOT NULL COMMENT '请求耗时（秒除10的9次方），要还原成秒就要乘10的9次方',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `type` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '接口代号，类名.方法名如： Bot.friends Bot.groups Bot.chats Group.update_group 等',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='接口访问频率记录';

CREATE TABLE IF NOT EXISTS `config` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `key` varchar(256) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '配置名',
  `value` varchar(256) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '配置的值',
  `remark` varchar(256) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '配置说明',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='配置';

CREATE TABLE IF NOT EXISTS `group_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `group_puid` char(8) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '群 puid',
  `user_puid` char(8) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户 puid',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `group_puid_user_puid` (`group_puid`,`user_puid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='群与用户关系表';

CREATE TABLE IF NOT EXISTS `group_user_left` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `group_user_id` bigint(20) NOT NULL COMMENT 'group_user 表的 id',
  `group_puid` char(8) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '群 puid',
  `user_puid` char(8) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户 puid',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='离群用户表(从group_user中删除的项目放这里)';

CREATE TABLE IF NOT EXISTS `message` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `msg_id` char(19) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'wxpy''s message id',
  `type` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '文本''Text'',位置''Map'',名片''Card'',提示''Note'',分享''Sharing'',图片''Picture'',语音''Recording'',文件''Attachment'',视频''Video'',好友请求''Friends'',系统''System''',
  `senderPuid` char(8) COLLATE utf8mb4_unicode_ci NOT NULL,
  `receiverPuid` char(8) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '接收者 puid 可能是人也可能是群',
  `receiverType` tinyint(2) NOT NULL COMMENT 'receiverPuid 是：1个人  2群',
  `is_at` tinyint(2) NOT NULL COMMENT '自己是否被 @: 0否 1是',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '服务端发送时间',
  `receive_time` datetime NOT NULL COMMENT '本地接收时间',
  `text` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '少于64字符的聊天内容，多的放到 message_big 中',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `message_big` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `msg_id` char(19) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'wxpy''s message id',
  `text` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '消息正文',
  `raw` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '完整的消息 raw',
  `img_url` varchar(512) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '消息图片 url',
  `file_name` varchar(512) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '文件名',
  `file_size` bigint(20) NOT NULL DEFAULT '0' COMMENT '文件大小，byte',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='大消息如图片、视频、很长的文字等';

CREATE TABLE IF NOT EXISTS `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `puid` char(8) COLLATE utf8mb4_unicode_ci NOT NULL,
  `nick_name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '昵称',
  `remark_name` varchar(256) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '备注名',
  `avatar` varchar(256) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '头像 url',
  `is_group` tinyint(1) NOT NULL DEFAULT '0' COMMENT '此对象是否是群 1是 0否',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `puid` (`puid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='基本聊天对象，包含所有的用户、群、好友等角色';

CREATE TABLE IF NOT EXISTS `user_all` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `is_group` tinyint(1) NOT NULL COMMENT '1群组 2非群，非 dict 成员',
  `puid` char(8) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '非 dict 成员',
  `avatar` varchar(256) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '自己上传头像 url，多个用英文逗号分隔',
  `uin` int(11) NOT NULL DEFAULT '-1',
  `user_name` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `nick_name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `attr_status` bigint(20) NOT NULL DEFAULT '-1',
  `py_initial` varchar(256) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `py_quan_pin` varchar(256) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `remark_py_initial` varchar(256) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `remark_py_quan_pin` varchar(256) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `display_name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `key_word` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `head_img_url` varchar(256) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'wxpy 内部头像',
  `contact_flag` int(11) NOT NULL DEFAULT '-1',
  `member_count` int(11) NOT NULL DEFAULT '-1',
  `remark_name` varchar(256) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `hide_input_bar_flag` int(11) NOT NULL DEFAULT '-1',
  `sex` int(11) NOT NULL DEFAULT '-1' COMMENT '0未知 1男 2女',
  `signature` varchar(256) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '签名，字符最多31个，但有enoji 时就不止，因为 emoji 用 xml 表达的',
  `verify_flag` int(11) NOT NULL DEFAULT '-1',
  `owner_uin` int(11) NOT NULL DEFAULT '-1',
  `star_friend` int(11) NOT NULL DEFAULT '-1',
  `app_account_flag` int(11) NOT NULL DEFAULT '-1',
  `statues` int(11) NOT NULL DEFAULT '-1',
  `province` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '英文的城市名会更长',
  `city` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `alias` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `sns_flag` int(11) NOT NULL DEFAULT '-1',
  `uni_friend` int(11) NOT NULL DEFAULT '-1',
  `chat_room_id` int(11) NOT NULL DEFAULT '-1',
  `encry_chat_room_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `is_owner` int(11) NOT NULL DEFAULT '-1',
  `member_status` int(11) NOT NULL DEFAULT '-1' COMMENT '只有非群才有',
  `is_admin` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '只有群才有',
  `head_img_update_flag` int(11) NOT NULL DEFAULT '-1' COMMENT '只有群才有',
  `contact_type` int(11) NOT NULL DEFAULT '-1' COMMENT '只有群才有',
  `chat_room_owner` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '只有群才有',
  PRIMARY KEY (`id`),
  UNIQUE KEY `puid_is_group` (`puid`,`is_group`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `user_big` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `puid` char(8) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '关联 user 中的 puid',
  `raw` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '存用户 raw 数据',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='存所有用户的 raw 数据';

CREATE TABLE IF NOT EXISTS `user_del` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `puid` char(8) COLLATE utf8mb4_unicode_ci NOT NULL,
  `nick_name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '昵称',
  `remark_name` varchar(256) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '备注名',
  `avatar` varchar(256) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '头像 url',
  `is_group` tinyint(1) NOT NULL DEFAULT '0' COMMENT '此对象是否是群 1是 0否',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='user 表已删除的放这里';

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;