-- -----------------------------------------
-- 用户信息表
-- -----------------------------------------
CREATE TABLE IF NOT EXISTS CHAT_USER (
	ID               VARCHAR(32) DEFAULT (replace(uuid(),'-','')) PRIMARY KEY comment '主键',
    USER_CODE        VARCHAR(32)  UNIQUE comment '用户代码',
    USER_NAME        VARCHAR(50) comment '用户昵称',
    USER_EMAIL       VARCHAR(100) comment '用户邮箱',
    USER_PHONE       VARCHAR(32) comment '用户电话',
    USER_IMAGE_PATH  VARCHAR(200) comment '用户图片地址',
    USER_PASSWORD    VARCHAR(50) comment '用户密码',
    USER_SEX	     VARCHAR(1) comment '用户性别 0:男,1:女',
    USER_STATUS      VARCHAR(2) comment '用户状态-1:注销,0:离线,1:在线',
    USER_CREATE_DATE TIMESTAMP DEFAULT (SYSDATE()) comment '创建时间'
);
##GOGO;

insert into CHAT_USER (ID, USER_CODE, USER_NAME, USER_EMAIL, USER_PHONE, USER_IMAGE_PATH, USER_PASSWORD, USER_SEX, USER_STATUS, USER_CREATE_DATE)
values ('AAB872D0612C44E5B33AC7BEA6C9D1BF', 'admin', '管理员', null, null, null, '6c5de1b510e8bdd0bc40eff99dcd03f8', null, '0',SYSDATE());
##GOGO;

-- -----------------------------------------
-- 用户信息发发送表
-- -----------------------------------------
CREATE TABLE IF NOT EXISTS CHAT_MSG (
	ID                VARCHAR(32) DEFAULT (replace(uuid(),'-','')) PRIMARY KEY comment '主键',
    CHART_CMD         VARCHAR(2) comment '状态0未发送 1已发送 -1删除',
    CHART_STATUS      VARCHAR(2) comment '1.绑定上线 2.下线 3.单聊 4.群聊 5.获取用户信息 6获取群组用户信息',
    CHART_FROM_ID     VARCHAR(32) comment '发送用户ID',
    CHART_FROM_CODE   VARCHAR(32) comment '发送用户CODE',
    CHART_FROM_IP     VARCHAR(50) comment '发送用户IP',
    CHART_ACCEPT_ID   VARCHAR(32) comment '接收用户ID',
    CHART_ACCEPT_CODE VARCHAR(32) comment '接收用户CODE',
    CHART_ACCEPT_IP   VARCHAR(50) comment '接收用户IP',
    CHART_GROUP_ID    VARCHAR(32) comment '群组ID',
    CHART_GROUP_CODE  VARCHAR(32) comment '群组CODE',
    CHART_MSG         VARCHAR(500) comment '发送信息',
    CHART_DATE        TIMESTAMP DEFAULT (SYSDATE())  comment '创建时间' 
);
##GOGO;
-- ----------------------------------------
-- 这里查询用户的历史聊天用户
-- ----------------------------------------
CREATE OR REPLACE VIEW V_OLD_CHAT_USER
AS
SELECT DISTINCT FU.ID        FU_ID,
                FU.USER_CODE FU_USER_CODE,
                FU.USER_NAME FU_USER_NAME,
                AU.ID        AU_ID,
                AU.USER_CODE AU_USER_CODE,
                AU.USER_NAME AU_USER_NAME
  FROM CHAT_MSG CM
  LEFT JOIN CHAT_USER FU
    ON CM.CHART_FROM_ID = FU.ID
  LEFT JOIN CHAT_USER AU
    ON CM.CHART_ACCEPT_ID = AU.ID
 WHERE CM.CHART_CMD = '3';
##GOGO;


-- -----------------------------------------
-- 群组表
-- -----------------------------------------
CREATE TABLE IF NOT EXISTS CHAT_GROUP (
	 ID               VARCHAR(32) DEFAULT (replace(uuid(),'-','')) PRIMARY KEY comment '主键',
    GROUP_CODE       VARCHAR(32)  UNIQUE comment '组代码',
    GROUP_NAME       VARCHAR(50) comment '组名称',
    GROUP_PURPOSE    VARCHAR(100) comment '组备注',
    GROUP_USER_ID    VARCHAR(32) comment '创建用户ID',
    GROUP_USER_CODE  VARCHAR(32) comment '创建用户代码',
    GROUP_STATUS     VARCHAR(2) comment '状态0正常 -1删除',
    GROUP_IMAGE_PATH VARCHAR(200) comment '群组头像',
    GROUP_CREATE_DATE TIMESTAMP DEFAULT (SYSDATE())  comment '创建时间'
);
##GOGO;

DELETE FROM CHAT_GROUP c where c.id in('1AE8F5F0A13542B1B278B5313A2B77E6','AF373FF634D64BC0A4EA25BA105F558E','5E07C44C6D8443E099B6DD69AD2533A8');
##GOGO;

insert into CHAT_GROUP (ID, GROUP_CODE, GROUP_NAME, GROUP_PURPOSE, GROUP_USER_ID, GROUP_USER_CODE, GROUP_STATUS)
values ('1AE8F5F0A13542B1B278B5313A2B77E6', 'g001', '划水', null, null, null, '0');
##GOGO;

insert into CHAT_GROUP (ID, GROUP_CODE, GROUP_NAME, GROUP_PURPOSE, GROUP_USER_ID, GROUP_USER_CODE, GROUP_STATUS)
values ('AF373FF634D64BC0A4EA25BA105F558E', 'g002', '闲聊', null, null, null, '0');
##GOGO;

insert into CHAT_GROUP (ID, GROUP_CODE, GROUP_NAME, GROUP_PURPOSE, GROUP_USER_ID, GROUP_USER_CODE, GROUP_STATUS)
values ('5E07C44C6D8443E099B6DD69AD2533A8', 'g003', '讨乱', null, null, null, '0');
##GOGO;


-- -----------------------------------------
-- 信息查询内容视图
-- -----------------------------------------
CREATE OR REPLACE VIEW V_CHAT_MSG
AS
SELECT CM.*,CG.ID GROUP_ID,CG.GROUP_CODE,CG.GROUP_NAME,CU_FROM.USER_NAME CHART_FROM_USER_NAME,CU_ACCEPT.USER_NAME CHART_ACCEPT_USER_NAME FROM CHAT_MSG CM
LEFT JOIN CHAT_USER CU_FROM ON CM.CHART_FROM_ID=CU_FROM.ID
LEFT JOIN CHAT_USER CU_ACCEPT ON CM.CHART_ACCEPT_ID=CU_ACCEPT.ID
LEFT JOIN CHAT_GROUP CG ON CM.CHART_GROUP_ID=CG.ID OR CM.CHART_GROUP_CODE=CG.GROUP_CODE
ORDER BY CM.CHART_DATE DESC;
##GOGO;

-- ----------------------------------
-- 用户绑定的IP
-- ----------------------------------
CREATE TABLE IF NOT EXISTS CHAT_USER_BIND (
	 ID               VARCHAR(32) DEFAULT (replace(uuid(),'-','')) PRIMARY KEY comment '主键',
     USER_CODE        VARCHAR(32) comment '用户代码',
     USER_IP          VARCHAR(50) comment '绑定服务端IP',
     USER_TYPE        VARCHAR(2) comment '创建时间',
     CREATE_DATE TIMESTAMP DEFAULT (SYSDATE()) comment '0注册成功，1登录成功，2绑定成功'
);
##GOGO;
