DROP TABLE IF EXISTS `IM_APP`;

CREATE TABLE `IM_APP` (
  `APPID` int(11) NOT NULL AUTO_INCREMENT,
  `APPNAME` varchar(100)  DEFAULT NULL,
  `IOS_CERTFILE` longblob,
  `IOS_CERTPASS` varchar(100)  DEFAULT NULL,
  `MANAGERID` varchar(200)  DEFAULT NULL,
  PRIMARY KEY (`APPID`)
)  AUTO_INCREMENT=2 ;

INSERT INTO `IM_APP` VALUES (0,'消息服务',NULL,'123','0');



DROP TABLE IF EXISTS `IM_FRIEND`;

CREATE TABLE `IM_FRIEND` (
  `USERID` int(11) NOT NULL,
  `FRIENDID` int(11) NOT NULL,
  `IS_IMPORTANT` varchar(10)  DEFAULT NULL,
  `IS_TOP` varchar(10)  DEFAULT NULL,
  `IS_BOTHER` varchar(10)  DEFAULT NULL,
  `NOTE` varchar(10)  DEFAULT NULL,
  UNIQUE KEY `IM_FRIEND_INDEX` (`USERID`,`FRIENDID`)
)  ;


DROP TABLE IF EXISTS `IM_GROUP`;

CREATE TABLE `IM_GROUP` (
  `GROUPID` int(11) NOT NULL AUTO_INCREMENT,
  `CREATE_USERID` int(11) NOT NULL,
  `CREATE_TIME` mediumtext  NOT NULL,
  `GROUPNAME` varchar(100)  DEFAULT NULL,
  `AVATAR` varchar(1000)  DEFAULT NULL,
  `APPID` int(11) DEFAULT NULL,
  PRIMARY KEY (`GROUPID`),
  UNIQUE KEY `IM_GROUP_INDEX` (`GROUPID`)
)  AUTO_INCREMENT=964 ;


DROP TABLE IF EXISTS `IM_GROUP_USER`;

CREATE TABLE `IM_GROUP_USER` (
  `GROUPID` int(11) NOT NULL,
  `USERID` int(11) NOT NULL,
  `MANA` int(11) DEFAULT NULL,
  `NOTE` varchar(200)  DEFAULT NULL,
  UNIQUE KEY `IM_GROUP_USER_INDEX` (`GROUPID`,`USERID`)
)  ;


DROP TABLE IF EXISTS `IM_GROUP_USER_NOTE`;

CREATE TABLE `IM_GROUP_USER_NOTE` (
  `GROUPID` int(11) NOT NULL DEFAULT '0',
  `USERID1` int(11) NOT NULL DEFAULT '0',
  `USERID2` int(11) NOT NULL DEFAULT '0',
  `NOTE` varchar(200)  DEFAULT NULL,
  PRIMARY KEY (`GROUPID`,`USERID1`,`USERID2`)
)  ;


DROP TABLE IF EXISTS `IM_INBOX`;

CREATE TABLE `IM_INBOX` (
  `USERID` int(11) DEFAULT NULL,
  `FROMUSERID` int(11) DEFAULT NULL,
  `ACK` int(11) DEFAULT NULL,
  `READED` int(11) DEFAULT NULL,
  `MSGTIME` bigint(20) DEFAULT NULL,
  `BODY` longblob,
  `CONTENTTYPE` int(11) DEFAULT NULL,
  `TOUSERID` int(11) DEFAULT NULL,
  `ACKCLIENTID` varchar(200)  DEFAULT NULL,
  `SERVERTIME` bigint(20) DEFAULT NULL,
  `MSGORDER` int(11) DEFAULT NULL,
  `MSGID` varchar(100)  DEFAULT NULL,
  `ACKTIME` bigint(20) DEFAULT NULL,
  `READEDTIME` bigint(20) DEFAULT NULL,
  `POSTTYPE` int(11) DEFAULT NULL,
  `APPID` int(11) DEFAULT NULL,
  `PMSGID` varchar(100)  DEFAULT NULL,
  `STRUCT` longblob,
  `VERSION` int(11) DEFAULT NULL,
  `PUSERID` int(11) DEFAULT NULL,
  `BODYTEXT` longtext 
)  ;

CREATE UNIQUE INDEX IM_INBOX_INDEX  ON IM_INBOX(USERID, MSGID)
;
CREATE INDEX IM_INBOX_INX2 ON IM_INBOX(MSGID)
;
CREATE INDEX IM_INBOX_INX3 ON IM_INBOX(USERID, SERVERTIME)
;
CREATE INDEX IM_INBOX_INX4 ON IM_INBOX(ACKTIME)
;
CREATE INDEX IM_INBOX_INX5 ON IM_INBOX(SERVERTIME)
;

DROP TABLE IF EXISTS `IM_MANAGER`;

CREATE TABLE `IM_MANAGER` (
  `MANAGERID` varchar(200)  NOT NULL,
  `MANAGERNAME` varchar(200)  DEFAULT NULL,
  `PASSWORD` varchar(200)  DEFAULT NULL,
  `EMAIL` varchar(200)  DEFAULT NULL,
  `PHONE` varchar(200)  DEFAULT NULL,
  PRIMARY KEY (`MANAGERID`)
)  ;

INSERT INTO `IM_MANAGER` VALUES ('0','消息服务管理员','E10ADC3949BA59ABBE56E057F20F883E',NULL,NULL);


DROP TABLE IF EXISTS `IM_OUTBOX`;

CREATE TABLE `IM_OUTBOX` (
  `USERID` int(11) DEFAULT NULL,
  `TOUSERID` int(11) DEFAULT NULL,
  `ACK` int(11) DEFAULT NULL,
  `MSGTIME` bigint(20) DEFAULT NULL,
  `BODY` longblob,
  `CONTENTTYPE` int(11) DEFAULT NULL,
  `FROMCLIENTID` varchar(200)  DEFAULT NULL,
  `SERVERTIME` bigint(20) DEFAULT NULL,
  `MSGORDER` int(11) DEFAULT NULL,
  `MSGID` varchar(100)  DEFAULT NULL,
  `POSTTYPE` int(11) DEFAULT NULL,
  `APPID` int(11) DEFAULT NULL,
  `PMSGID` varchar(100)  DEFAULT NULL,
  `STRUCT` longblob,
  `VERSION` int(11) DEFAULT NULL,
  `PUSERID` int(11) DEFAULT NULL,
  `BODYTEXT` longtext 
)  ;


CREATE UNIQUE INDEX IM_OUTBOX_INDEX ON IM_OUTBOX(MSGID)
;
CREATE INDEX IM_OUTBOX_INDEX2 ON IM_OUTBOX(USERID, SERVERTIME)
;
CREATE INDEX IM_OUTBOX_INX3 ON IM_OUTBOX(SERVERTIME)
;


DROP TABLE IF EXISTS `IM_PUBLIC`;

CREATE TABLE `IM_PUBLIC` (
  `USERID` int(11) NOT NULL,
  `URL` varchar(1000)  DEFAULT NULL,
  PRIMARY KEY (`USERID`)
)  ;

DROP TABLE IF EXISTS `IM_PUBLIC_USER`;

CREATE TABLE `IM_PUBLIC_USER` (
  `PUBLICID` int(11) NOT NULL,
  `USERID` int(11) NOT NULL,
  `MANA` int(11) DEFAULT NULL,
  UNIQUE KEY `IM_PUBLIC_USER_INDEX` (`PUBLICID`,`USERID`)
)  ;


DROP TABLE IF EXISTS `IM_SERVER`;

CREATE TABLE `IM_SERVER` (
  `SERVERID` varchar(100)  NOT NULL,
  `HOST` varchar(20)  NOT NULL,
  `PORT` varchar(20)  NOT NULL,
  `TYPE` varchar(20)  DEFAULT NULL,
  UNIQUE KEY `IM_SERVER_INDEX` (`SERVERID`)
)  ;



INSERT INTO `IM_SERVER` VALUES ('server-1','10.80.0.176','8000','TCP');


DROP TABLE IF EXISTS `IM_SESSION`;

CREATE TABLE `IM_SESSION` (
  `CLIENTID` varchar(200)  NOT NULL DEFAULT '',
  `USERID` int(11) DEFAULT NULL,
  `DEVICETYPE` varchar(100)  DEFAULT NULL,
  `DEVICEVERSION` varchar(100)  DEFAULT NULL,
  `DEVICECOMPANY` varchar(100)  DEFAULT NULL,
  `DEVICEMODEL` varchar(100)  DEFAULT NULL,
  `APPVERSION` varchar(100)  DEFAULT NULL,
  `SESSIONTIME` bigint(20) NOT NULL DEFAULT '0',
  `CLIENTIP` varchar(50)  DEFAULT NULL,
  `CLIENTPORT` int(11) DEFAULT NULL,
  `UNITID` varchar(50)  DEFAULT NULL,
  `SERVERID` varchar(50)  DEFAULT NULL,
  `CLIENTHTTPIP` varchar(50)  DEFAULT NULL,
  `CLIENTHTTPPORT` int(11) DEFAULT NULL,
  `CONNTYPE` varchar(20)  DEFAULT NULL,
  `DERVICEID` varchar(200)  DEFAULT NULL,
  `APPID` int(11) DEFAULT NULL,
  `USERTYPE` int(11) DEFAULT NULL,
  `IOSTOKEN` varchar(200)  DEFAULT NULL,
  PRIMARY KEY (`CLIENTID`,`SESSIONTIME`),
  KEY `IM_SESSION_INX2` (`APPID`,`DERVICEID`),
  KEY `IM_SESSION_INX1` (`DERVICEID`),
  KEY `IM_SESSION_INX3` (`IOSTOKEN`)
)  ;


DROP TABLE IF EXISTS `IM_USER`;

CREATE TABLE `IM_USER` (
  `USERID` int(11) NOT NULL AUTO_INCREMENT,
  `PASSWORD` varchar(100)  DEFAULT NULL,
  `USERNAME` varchar(50)  DEFAULT NULL,
  `NICKNAME` varchar(50)  DEFAULT NULL,
  `SEX` varchar(50)  DEFAULT NULL,
  `AVATAR` varchar(1000)  DEFAULT NULL,
  `PHONE` varchar(50)  DEFAULT NULL,
  `MOBILEPHONE` varchar(50)  DEFAULT NULL,
  `EMAIL` varchar(50)  DEFAULT NULL,
  `APPID` int(11) DEFAULT NULL,
  `USERTYPE` int(11) DEFAULT NULL,
  `CREATETIME` varchar(20)  DEFAULT NULL,
  `URL` varchar(1000)  DEFAULT NULL,
  `SIGN` varchar(200)  DEFAULT NULL,
  PRIMARY KEY (`USERID`),
  UNIQUE KEY `IM_USER_INDEX` (`USERID`)
)  AUTO_INCREMENT=10000 ;


alter table IM_USER add PUBLICTYPE int;

DROP TABLE IF EXISTS `IM_CONFIG`
;
create table IM_CONFIG(
CONFIG_KEY VARCHAR(100),
CONFIG_VALUE LONGTEXT,
PRIMARY KEY (CONFIG_KEY)
);

alter table IM_SESSION ADD DEVICECLASS VARCHAR(100);

create index IM_USER_INX2 ON IM_USER(APPID,USERNAME);

create index IM_USER_INX3 ON IM_USER(APPID,NICKNAME);

ALTER TABLE IM_INBOX ADD TOCLIENTID VARCHAR(200);

ALTER TABLE IM_INBOX ADD FROMCLIENTID VARCHAR(200);

ALTER TABLE IM_GROUP ADD STATUS INT;

ALTER TABLE IM_SERVER ADD EXP VARCHAR(200);

ALTER TABLE IM_SESSION ADD OFFLINETIME BIGINT;

-- create index IM_INBOX_INX4 ON IM_INBOX(ACKTIME);

ALTER TABLE IM_GROUP ADD GROUPTYPE INT;


alter table IM_APP ADD APPSTATUS int;

ALTER TABLE IM_APP ADD HW_APP_ID VARCHAR(100);

ALTER TABLE IM_APP ADD HW_APP_SECRET VARCHAR(100);

ALTER TABLE IM_APP ADD HW_APP_PKGNAME VARCHAR(100);

DROP TABLE IF EXISTS IM_APP_SUB
;
CREATE TABLE IM_APP_SUB  ( 
    APPID           int(11) ,
    SUB_APPID           varchar(100) NULL,
    SUB_APPNAME         varchar(100) NULL,
    IOS_CERTFILE    longblob NULL,
    IOS_CERTPASS    varchar(100) NULL,
    HW_APP_ID       varchar(100) NULL,
    HW_APP_SECRET   varchar(100) NULL,
    HW_APP_PKGNAME  varchar(100) NULL
)
;
create unique index IM_APP_SUB_INX ON IM_APP_SUB(APPID,SUB_APPID)
;
INSERT INTO IM_APP_SUB(APPID,SUB_APPID,SUB_APPNAME,IOS_CERTFILE,IOS_CERTPASS,HW_APP_ID,HW_APP_SECRET,HW_APP_PKGNAME)  select APPID,'defaultSubAppId',APPNAME,IOS_CERTFILE,IOS_CERTPASS,HW_APP_ID,HW_APP_SECRET,HW_APP_PKGNAME from IM_APP
;


alter table IM_APP_SUB add  XM_APP_ID       varchar(100) NULL
;
alter table IM_APP_SUB add  XM_APP_KEY      varchar(100) NULL
;
alter table IM_APP_SUB add  XM_APP_SECRET   varchar(100) NULL
;
alter table IM_APP_SUB add  XM_APP_PKGNAME varchar(100) NULL
;
ALTER TABLE IM_GROUP ADD NO_SPEAK INT DEFAULT 0
;
ALTER TABLE IM_GROUP_USER ADD NO_SPEAK INT DEFAULT 0
;
-- ALTER TABLE IM_USER ADD ALLOW_STRANGER INT DEFAULT 1
-- ;
alter table IM_GROUP ADD GROUP_KEY VARCHAR(100)
;
create index IM_GROUP_INX2 ON IM_GROUP(GROUP_KEY)
;
alter table IM_USER ADD DISABLE_STRANGERS INT DEFAULT 0
;
alter table IM_GROUP ADD GROUP_DESC varchar(200)
;
alter table IM_APP add APP_KEY VARCHAR(100)
;
alter table IM_APP add APP_SECRET VARCHAR(100)
;

create unique index IM_APP_INX2 ON IM_APP(APP_KEY)
;
update IM_APP set APP_KEY = MD5(UUID()) where APP_KEY is null
;
update IM_APP set APP_SECRET = MD5(UUID()) where APP_SECRET is null
;
alter table IM_GROUP_USER add DISTURB INT DEFAULT 0
;

alter table IM_APP_SUB add  GOOGLE_APP_KEY  varchar(100) NULL
;
alter table IM_APP_SUB add  GOOGLE_APP_PKGNAME  varchar(100) NULL
;
-- alter table IM_CONFIG ADD PRIMARY KEY (CONFIG_KEY)
-- ;
-- alter table IM_CONFIG drop index IM_CONFIG_INX
-- ;


alter table IM_FRIEND ADD PRIMARY KEY (USERID,FRIENDID)
;
alter table IM_FRIEND drop index IM_FRIEND_INDEX
;

alter table IM_GROUP drop index IM_GROUP_INDEX
;

alter table IM_GROUP_USER ADD PRIMARY KEY (GROUPID,USERID)
;
alter table IM_GROUP_USER drop index IM_GROUP_USER_INDEX
;

alter table IM_PUBLIC_USER ADD PRIMARY KEY (PUBLICID,USERID)
;
alter table IM_PUBLIC_USER drop index IM_PUBLIC_USER_INDEX
;

alter table IM_SERVER ADD PRIMARY KEY (SERVERID)
;
alter table IM_SERVER drop index IM_SERVER_INDEX
;

alter table IM_INBOX ADD PRIMARY KEY (USERID,MSGID)
;
alter table IM_INBOX drop index IM_INBOX_INDEX
;

alter table IM_OUTBOX ADD PRIMARY KEY (MSGID)
;
alter table IM_OUTBOX drop index IM_OUTBOX_INDEX
;
alter table IM_APP drop APPSTATUS
;

alter table IM_APP_SUB drop  GOOGLE_APP_KEY  
;
-- alter table IM_APP_SUB drop  GOOGLE_APP_PKGNAME  
-- ;

alter table IM_APP_SUB add  GOOGLE_DATABASE_URL      varchar(200) NULL
;
alter table IM_APP_SUB add  GOOGLE_CRET_FILE   longblob NULL
;

alter table IM_APP_SUB add  GOOGLE_APP_KEY  varchar(200)
;

-- 20191121
create table IM_VERSION (
	F_MODULE VARCHAR(20) PRIMARY KEY,
	F_VERSION VARCHAR(20) 
)
;
INSERT INTO IM_VERSION(F_MODULE,F_VERSION) VALUES('session-server','20191121')
;


alter table IM_FRIEND MODIFY NOTE VARCHAR(50)
;
update  IM_VERSION set F_VERSION = '20200203' where F_MODULE = 'session-server'
;
ALTER TABLE IM_OUTBOX  ADD COLUMN TOCLIENTID varchar(200) NULL
;
INSERT INTO IM_VERSION(F_MODULE,F_VERSION) VALUES('storage-server','20200211')
;