/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50730
 Source Host           : localhost:3306
 Source Schema         : wecloud_im

 Target Server Type    : MySQL
 Target Server Version : 50730
 File Encoding         : 65001

 Date: 20/12/2021 10:28:36
*/

SET NAMES utf8mb4;
SET
    FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for im_application
-- ----------------------------
DROP TABLE IF EXISTS `im_application`;
CREATE TABLE `im_application`
(
    `id`                             bigint(20) NOT NULL COMMENT '应用appid',
    `create_time`                    timestamp  NULL                   DEFAULT NULL COMMENT '创建时间',
    `update_time`                    timestamp  NULL                   DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
    `app_key`                        varchar(200) CHARACTER SET latin1 DEFAULT NULL COMMENT 'key',
    `app_secret`                     varchar(500) CHARACTER SET latin1 DEFAULT NULL COMMENT '密钥',
    `app_name`                       varchar(200)                      DEFAULT NULL COMMENT 'app名称',
    `ios_push_channel`               int(255)                          DEFAULT NULL COMMENT 'ios推送通道,友盟:1;firebase:2; apns原生:3',
    `android_push_channel`           int(255)                          DEFAULT NULL COMMENT '安卓推送通道,友盟:1;firebase:2; 信鸽3',
    `umeng_key`                      varchar(500)                      DEFAULT NULL COMMENT '友盟推送key',
    `umeng_secret`                   varchar(500)                      DEFAULT NULL COMMENT '友盟推送密钥',
    `firebase_secret`                varchar(500)                      DEFAULT NULL COMMENT 'firebase推送密钥',
    `repeat_session_status`          int(1)                            DEFAULT '0' COMMENT '是否允许创建重复会话 0不允许 1允许',
    `contrast_extended_field_status` int(1)                            DEFAULT '0' COMMENT '创建会话时对比扩展字段  0不  1是',
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE KEY `app_key` (`app_key`) USING HASH
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='第三方应用表';

-- ----------------------------
-- Table structure for im_client
-- ----------------------------
DROP TABLE IF EXISTS `im_client`;
CREATE TABLE `im_client`
(
    `id`                bigint(20) NOT NULL COMMENT '客户端id',
    `create_time`       timestamp  NULL DEFAULT NULL COMMENT '创建时间',
    `last_offline_time` timestamp  NULL DEFAULT NULL COMMENT '最后离线时间',
    `update_time`       timestamp  NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
    `fk_appid`          bigint(20)      DEFAULT NULL COMMENT '应用appid',
    `attributes`        json            DEFAULT NULL COMMENT '可选	自定义属性，供开发者扩展使用',
    `client_id`         varchar(200)    DEFAULT NULL COMMENT '客户方提供的唯一id',
    `valid`             int(1)          DEFAULT NULL COMMENT '设备不想收到推送提醒',
    `device_type`       int(1)          DEFAULT NULL COMMENT '设备类型1:ios; 2:android',
    `device_token`      varchar(300)    DEFAULT NULL COMMENT '设备推送token',
    `head_portrait`     varchar(1000)   DEFAULT NULL COMMENT '头像',
    `nickname`          varchar(30)     DEFAULT NULL COMMENT '主昵称',
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE KEY ```fk_appid``, ``client_id``` (`fk_appid`, `client_id`) USING BTREE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='终端表';

-- ----------------------------
-- Table structure for im_client_blacklist
-- ----------------------------
DROP TABLE IF EXISTS `im_client_blacklist`;
CREATE TABLE `im_client_blacklist`
(
    `id`                      bigint(20) NOT NULL COMMENT '客户端id',
    `create_time`             timestamp  NULL DEFAULT NULL COMMENT '创建时间',
    `update_time`             timestamp  NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
    `fk_appid`                bigint(20)      DEFAULT NULL COMMENT '应用appid',
    `fk_client_id_prevent`    bigint(20)      DEFAULT NULL COMMENT '拉黑者',
    `fk_client_id_be_prevent` bigint(20)      DEFAULT NULL COMMENT '被拉黑',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='黑名单';

-- ----------------------------
-- Table structure for im_conversation
-- ----------------------------
DROP TABLE IF EXISTS `im_conversation`;
CREATE TABLE `im_conversation`
(
    `id`           bigint(20) NOT NULL COMMENT '会话id',
    `create_time`  timestamp  NULL DEFAULT NULL COMMENT '创建时间',
    `update_time`  timestamp  NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
    `last_message` timestamp  NULL DEFAULT NULL COMMENT '对话中最后一条消息的发送或接收时间',
    `fk_appid`     bigint(20) NOT NULL COMMENT '应用appid',
    `creator`      bigint(20) NOT NULL COMMENT '创建者客户端id',
    `name`         varchar(255)    DEFAULT NULL COMMENT '可选	 对话的名字，可为群组命名。',
    `attributes`   json            DEFAULT NULL COMMENT '可选	自定义属性，供开发者扩展使用。',
    `system`       tinyint(1)      DEFAULT NULL COMMENT '可选	对话类型标志，是否是系统对话，后面会说明。',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='会话表';

-- ----------------------------
-- Table structure for im_conversation_members
-- ----------------------------
DROP TABLE IF EXISTS `im_conversation_members`;
CREATE TABLE `im_conversation_members`
(
    `id`                       bigint(20) NOT NULL COMMENT '唯一id',
    `create_time`              timestamp  NULL DEFAULT NULL COMMENT '加入时间',
    `update_time`              timestamp  NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
    `fk_appid`                 bigint(20) NOT NULL COMMENT '应用appid',
    `fk_conversation_id`       bigint(20) NOT NULL COMMENT '会话表id',
    `fk_client_id`             bigint(20) NOT NULL COMMENT '客户端id',
    `display_status`           int(2)          DEFAULT '1' COMMENT '单向删除(隐藏)会话, 0不显示, 1显示',
    `allow_system_push_status` int(2)          DEFAULT '1' COMMENT '是否允许系统推送, 0不推送, 1推送',
    `attributes`               json            DEFAULT NULL COMMENT '可选	自定义属性，供开发者扩展使用',
    `client_remark_name`       varchar(255)    DEFAULT NULL COMMENT '会话中client的备注名',
    PRIMARY KEY (`id`) USING BTREE,
    KEY `fk_conversation_id` (`fk_conversation_id`) USING HASH
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='会话成员表';

-- ----------------------------
-- Table structure for im_inbox
-- ----------------------------
DROP TABLE IF EXISTS `im_inbox`;
CREATE TABLE `im_inbox`
(
    `id`                  bigint(20) NOT NULL COMMENT '收件id',
    `create_time`         timestamp  NULL DEFAULT NULL COMMENT '创建时间',
    `update_time`         timestamp  NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
    `read_time`           timestamp  NULL DEFAULT NULL COMMENT '读取时间',
    `receiver_time`       timestamp  NULL DEFAULT NULL COMMENT '接收时间',
    `fk_appid`            bigint(20) NOT NULL COMMENT '应用appid',
    `receiver`            bigint(20) NOT NULL COMMENT '接收客户端id',
    `fk_msg_id`           bigint(20) NOT NULL COMMENT '消息id',
    `read_msg_status`     int(1)          DEFAULT NULL COMMENT '0未读; 1已读',
    `receiver_msg_status` int(1)          DEFAULT NULL COMMENT '0未接收; 1已接收',
    `fk_conversation_id`  bigint(20) NOT NULL COMMENT '会话id',
    PRIMARY KEY (`id`) USING BTREE,
    KEY `getMyOfflineMsg` (`receiver`) USING HASH
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='消息收件箱表';

-- ----------------------------
-- Table structure for im_ios_apns
-- ----------------------------
DROP TABLE IF EXISTS `im_ios_apns`;
CREATE TABLE `im_ios_apns`
(
    `id`              bigint(20) NOT NULL COMMENT 'id',
    `fk_app_id`       bigint(20) NOT NULL COMMENT '应用appid',
    `apns_file_value` text CHARACTER SET latin1 COMMENT 'Base64(apns.p12)',
    `env`             int(255)     DEFAULT NULL COMMENT '环境,正式1,测试0',
    `bundle_id`       varchar(500) DEFAULT NULL COMMENT 'bundle_id',
    `pwd`             varchar(500) DEFAULT NULL COMMENT '证书密码',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='apns配置表';

-- ----------------------------
-- Table structure for im_message
-- ----------------------------
DROP TABLE IF EXISTS `im_message`;
CREATE TABLE `im_message`
(
    `id`                 bigint(20) NOT NULL COMMENT '消息id',
    `fk_conversation_id` bigint(20)      DEFAULT NULL COMMENT '会话id',
    `fk_appid`           bigint(20) NOT NULL COMMENT '应用appid',
    `create_time`        timestamp  NULL DEFAULT NULL COMMENT '创建时间',
    `withdraw_time`      timestamp  NULL DEFAULT NULL COMMENT '撤回时间',
    `update_date`        timestamp  NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
    `sender`             bigint(20)      DEFAULT NULL COMMENT '发送者客户端id',
    `content`            json            DEFAULT NULL COMMENT '内容',
    `withdraw`           tinyint(1)      DEFAULT '0' COMMENT '0未撤回; 1已撤回',
    `event`              tinyint(1)      DEFAULT '0' COMMENT '0非事件; 1为事件',
    `system`             tinyint(1)      DEFAULT '0' COMMENT '0非系统通知; 1为系统通知',
    `at`                 text COMMENT 'at他人,传入客户端id数组',
    `send_status`        int(2)          DEFAULT NULL COMMENT '发送状态\n1AVIMMessageStatusSending（发送中）\n2AVIMMessageStatusSent（发送成功）\n3AVIMMessageStatusFailed（失败）',
    PRIMARY KEY (`id`) USING BTREE,
    KEY ```fk_conversation_id``` (`fk_conversation_id`) USING HASH
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='消息存储表';

-- ----------------------------
-- Table structure for im_rtc_channel
-- ----------------------------
DROP TABLE IF EXISTS `im_rtc_channel`;
CREATE TABLE `im_rtc_channel`
(
    `id`              bigint(20) NOT NULL COMMENT '会话id',
    `create_time`     timestamp  NULL DEFAULT NULL COMMENT '创建时间',
    `update_time`     timestamp  NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
    `fk_appid`        bigint(20) NOT NULL COMMENT '外键 应用appid',
    `fk_conversation` bigint(20)      DEFAULT NULL COMMENT '外键 会话id 可为空',
    `name`            varchar(255)    DEFAULT NULL COMMENT '可选	 对话的名字，可为群组命名。',
    `attributes`      json            DEFAULT NULL COMMENT '可选	自定义属性，供开发者扩展使用。',
    `channel_status`  int(1)          DEFAULT NULL COMMENT '频道状态, 1打开 , 0关闭',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='Rtc频道表';

-- ----------------------------
-- Table structure for im_rtc_log
-- ----------------------------
DROP TABLE IF EXISTS `im_rtc_log`;
CREATE TABLE `im_rtc_log`
(
    `id`              bigint(20) NOT NULL COMMENT 'rtc记录id',
    `fk_appid`        bigint(20) NOT NULL COMMENT '应用appid',
    `fk_channel_id`   bigint(20)      DEFAULT NULL COMMENT '频道id',
    `create_time`     timestamp  NULL DEFAULT NULL COMMENT '创建时间',
    `update_date`     timestamp  NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
    `fk_conversation` bigint(20)      DEFAULT NULL COMMENT '外键 会话id 可为空',
    `client_id`       bigint(20)      DEFAULT NULL COMMENT '事件主体客户端id',
    `event_type`      int(1)          DEFAULT NULL COMMENT '事件类型 1成为房主, 2加入频道,3退出频道,4拒绝邀请 ,5上传sdp, 6上传candidate',
    `remark`          text COMMENT '备注',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='RTC日志记录表';

SET
    FOREIGN_KEY_CHECKS = 1;

alter table im_application
    add COLUMN `online_status_subscribe_url` varchar(512) DEFAULT '' COMMENT '在线状态订阅地址';
alter table im_application
    add COLUMN `full_message_subscribe_url` varchar(512) DEFAULT '' COMMENT '全量消息订阅地址';
alter table `im_conversation_members`
    add column `is_do_not_disturb` tinyint(1) unsigned default 0 comment '免打扰开关 0-关闭 1开启';
alter table `im_conversation_members`
    add column `is_top` tinyint(1) unsigned default 0 comment '置顶开关 0-关闭 1开启';

alter table `im_conversation`
    add column `is_forbid_add_friend` tinyint(1) unsigned default 0 comment '是否禁止群里互加好友0否1是';
alter table `im_conversation`
    add column `is_forbid_send_red_packets` tinyint(1) unsigned default 0 comment '是否禁止发红包0否1是';
alter table `im_conversation`
    add column `is_forbid_send_pic` tinyint(1) unsigned default 0 comment '是否禁止发图片0否1是';
alter table `im_conversation`
    add column `is_forbid_send_link` tinyint(1) unsigned default 0 comment '是否禁止发链接0否1是';
