-------------------------------------------
--用户信息表
-------------------------------------------
DECLARE
  VC_STR           VARCHAR2(2000);
  VN_COUNT         NUMBER;
  BEGIN
    --查看是否存在NRE_BUSINESSTYPE
    SELECT COUNT(*) INTO VN_COUNT FROM USER_TABLES WHERE TABLE_NAME = 'CHAT_USER';
    --如果没有则新增加表 
    IF VN_COUNT < 1 THEN
    VC_STR :='
  CREATE TABLE CHAT_USER
  (
    ID               VARCHAR2(32) DEFAULT SYS_GUID() PRIMARY KEY,
    USER_CODE        VARCHAR2(32) CONSTRAINT UN_CHAT_USER_CODE UNIQUE,
    USER_NAME        VARCHAR2(50),
    USER_EMAIL       VARCHAR2(100),
    USER_PHONE       VARCHAR2(32),
    USER_IMAGE_PATH  VARCHAR2(200),
    USER_PASSWORD    VARCHAR2(50),
    USER_SEX	     VARCHAR2(1),
    USER_STATUS      VARCHAR2(2),
    USER_CREATE_DATE DATE DEFAULT SYSDATE   
  )';
    EXECUTE IMMEDIATE VC_STR;
    commit;
  END IF;
  END;
/
comment on column CHAT_USER.ID is '主键';
comment on column CHAT_USER.USER_CODE is '用户代码';
comment on column CHAT_USER.USER_NAME is '用户昵称';
comment on column CHAT_USER.USER_EMAIL is '用户邮箱';
comment on column CHAT_USER.USER_PHONE is '用户电话';
comment on column CHAT_USER.USER_IMAGE_PATH is '用户图片地址';
comment on column CHAT_USER.USER_PASSWORD is '用户密码';
comment on column CHAT_USER.USER_SEX is '用户性别 0:男,1:女';
comment on column CHAT_USER.USER_STATUS is '用户状态-1:注销,0:离线,1:在线';
comment on column CHAT_USER.USER_CREATE_DATE is '创建时间';
/

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', to_date('20-02-2022 19:01:50', 'dd-mm-yyyy hh24:mi:ss'));
/

-------------------------------------------
--用户信息发发送表
-------------------------------------------
DECLARE
  VC_STR           VARCHAR2(2000);
  VN_COUNT         NUMBER;
  BEGIN
    --查看是否存在NRE_BUSINESSTYPE
    SELECT COUNT(*) INTO VN_COUNT FROM USER_TABLES WHERE TABLE_NAME = 'CHAT_MSG';
    --如果没有则新增加表 
    IF VN_COUNT < 1 THEN
    VC_STR :='
  CREATE TABLE CHAT_MSG
  (
    ID                VARCHAR2(32) DEFAULT SYS_GUID() PRIMARY KEY,
    CHART_CMD         VARCHAR2(2),
    CHART_STATUS      VARCHAR2(2),
    CHART_FROM_ID     VARCHAR2(32),
    CHART_FROM_CODE   VARCHAR2(32),
    CHART_FROM_IP     VARCHAR2(50),
    CHART_ACCEPT_ID   VARCHAR2(32),
    CHART_ACCEPT_CODE VARCHAR2(32),
    CHART_ACCEPT_IP   VARCHAR2(50),
    CHART_GROUP_ID    VARCHAR2(32),
    CHART_GROUP_CODE  VARCHAR2(32),
    CHART_MSG         VARCHAR2(500),
    CHART_DATE        DATE DEFAULT SYSDATE   
  )';
    EXECUTE IMMEDIATE VC_STR;
    commit;
  END IF;
  END;
/
comment on column CHAT_MSG.ID is '主键';
comment on column CHAT_MSG.CHART_STATUS is '状态0未发送 1已发送 -1删除 ';
comment on column CHAT_MSG.CHART_CMD is '1.绑定上线 2.下线 3.单聊 4.群聊 5.获取用户信息 6获取群组用户信息';
comment on column CHAT_MSG.CHART_FROM_ID is '发送用户ID';
comment on column CHAT_MSG.CHART_FROM_CODE is '发送用户CODE';
comment on column CHAT_MSG.CHART_FROM_IP   is '发送用户IP';
comment on column CHAT_MSG.CHART_ACCEPT_ID is '接收用户ID';
comment on column CHAT_MSG.CHART_ACCEPT_CODE is '接收用户CODE';
comment on column CHAT_MSG.CHART_ACCEPT_IP is '接收用户IP';
comment on column CHAT_MSG.CHART_GROUP_ID is '群组ID';
comment on column CHAT_MSG.CHART_GROUP_CODE is '群组CODE';
comment on column CHAT_MSG.CHART_MSG is '发送信息';
comment on column CHAT_MSG.CHART_DATE is '创建时间';
/

------------------------------------------
--这里查询用户的历史聊天用户
------------------------------------------
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';
/


-------------------------------------------
--群组表
-------------------------------------------
DECLARE
  VC_STR           VARCHAR2(2000);
  VN_COUNT         NUMBER;
  BEGIN
    --查看是否存在NRE_BUSINESSTYPE
    SELECT COUNT(*) INTO VN_COUNT FROM USER_TABLES WHERE TABLE_NAME = 'CHAT_GROUP';
    --如果没有则新增加表 
    IF VN_COUNT < 1 THEN
    VC_STR :='
  CREATE TABLE CHAT_GROUP
  (
    ID               VARCHAR2(32) DEFAULT SYS_GUID() PRIMARY KEY,
    GROUP_CODE       VARCHAR2(32) CONSTRAINT UN_GROUP_CODE UNIQUE,
    GROUP_NAME       VARCHAR2(50),
    GROUP_PURPOSE    VARCHAR2(100),
    GROUP_USER_ID    VARCHAR2(32),
    GROUP_USER_CODE  VARCHAR2(32),
    GROUP_STATUS     VARCHAR2(2),
    GROUP_IMAGE_PATH VARCHAR2(200),
    GROUP_CREATE_DATE DATE DEFAULT SYSDATE   
  )';
    EXECUTE IMMEDIATE VC_STR;
    commit;
  END IF;
  END;
/
comment on column CHAT_GROUP.ID is '主键';
comment on column CHAT_GROUP.GROUP_CODE is '组代码';
comment on column CHAT_GROUP.GROUP_NAME is '组名称';
comment on column CHAT_GROUP.GROUP_PURPOSE is '组备注';
comment on column CHAT_GROUP.GROUP_USER_ID is '创建用户ID';
comment on column CHAT_GROUP.GROUP_USER_CODE is '创建用户代码';
comment on column CHAT_GROUP.GROUP_STATUS is '状态0正常 -1删除';
comment on column CHAT_GROUP.GROUP_IMAGE_PATH is '群组头像';
comment on column CHAT_GROUP.GROUP_CREATE_DATE is '创建时间';
/

truncate table chat_group;
/

insert into chat_group (ID, GROUP_CODE, GROUP_NAME, GROUP_PURPOSE, GROUP_USER_ID, GROUP_USER_CODE, GROUP_STATUS, GROUP_CREATE_DATE)
values ('1AE8F5F0A13542B1B278B5313A2B77E6', 'g001', '划水', null, null, null, '0', to_date('18-05-2020 18:50:08', 'dd-mm-yyyy hh24:mi:ss'));
/

insert into chat_group (ID, GROUP_CODE, GROUP_NAME, GROUP_PURPOSE, GROUP_USER_ID, GROUP_USER_CODE, GROUP_STATUS, GROUP_CREATE_DATE)
values ('AF373FF634D64BC0A4EA25BA105F558E', 'g002', '闲聊', null, null, null, '0', to_date('18-05-2020 18:50:08', 'dd-mm-yyyy hh24:mi:ss'));
/

insert into chat_group (ID, GROUP_CODE, GROUP_NAME, GROUP_PURPOSE, GROUP_USER_ID, GROUP_USER_CODE, GROUP_STATUS, GROUP_CREATE_DATE)
values ('5E07C44C6D8443E099B6DD69AD2533A8', 'g003', '讨乱', null, null, null, '0', to_date('18-05-2020 21:36:00', 'dd-mm-yyyy hh24:mi:ss'));
/
commit;
/

-------------------------------------------
--信息查询内容视图
-------------------------------------------
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;
/

------------------------------------
--用户绑定的IP
------------------------------------
DECLARE
  VC_STR           VARCHAR2(2000);
  VN_COUNT         NUMBER;
  BEGIN
    --查看是否存在NRE_BUSINESSTYPE
    SELECT COUNT(*) INTO VN_COUNT FROM USER_TABLES WHERE TABLE_NAME = 'CHAT_USER_BIND';
    --如果没有则新增加表 
    IF VN_COUNT < 1 THEN
    VC_STR :='
  CREATE TABLE CHAT_USER_BIND
  (
    ID               VARCHAR2(32) DEFAULT SYS_GUID() PRIMARY KEY,
    USER_CODE        VARCHAR2(32),
    USER_IP          VARCHAR2(50),
    USER_TYPE        VARCHAR2(2),
    CREATE_DATE DATE DEFAULT SYSDATE,
  )';
    EXECUTE IMMEDIATE VC_STR;
    commit;
  END IF;
  END;
/
comment on column CHAT_USER_BIND.ID is '主键';
comment on column CHAT_USER_BIND.USER_CODE is '用户代码';
comment on column CHAT_USER_BIND.USER_IP is '绑定服务端IP';
comment on column CHAT_USER_BIND.CREATE_DATE is '创建时间';
comment on column CHAT_USER_BIND.USER_TYPE is '0注册成功，1登录成功，2绑定成功';
/
