1_Create.WDCHAT.Table.sql

-- DROP SEQUENCE public.wd_admin_user_id_seq;

CREATE SEQUENCE public.wd_admin_user_id_seq
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 7;

-- DROP SEQUENCE public.wd_authen_input_temp_id_seq;

CREATE SEQUENCE public.wd_authen_input_temp_id_seq
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 124;

-- DROP SEQUENCE public.wd_authen_output_temp_id_seq;

CREATE SEQUENCE public.wd_authen_output_temp_id_seq
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 134;

-- DROP SEQUENCE public.wd_authen_type_id_seq;

CREATE SEQUENCE public.wd_authen_type_id_seq
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1;

-- DROP SEQUENCE public.wd_block_user_id_seq;

CREATE SEQUENCE public.wd_block_user_id_seq
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 82;

-- DROP SEQUENCE public.wd_chat_message_id_seq;

CREATE SEQUENCE public.wd_chat_message_id_seq
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 17411;

-- DROP SEQUENCE public.wd_group_id_seq;

CREATE SEQUENCE public.wd_group_id_seq
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 314;

-- DROP SEQUENCE public.wd_menu_id_seq;

CREATE SEQUENCE public.wd_menu_id_seq
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 6;

-- DROP SEQUENCE public.wd_message_user_rel_id_seq;

CREATE SEQUENCE public.wd_message_user_rel_id_seq
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 29117;

-- DROP SEQUENCE public.wd_sys_config_id_seq;

CREATE SEQUENCE public.wd_sys_config_id_seq
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 50;

-- DROP SEQUENCE public.wd_token_id_seq;

CREATE SEQUENCE public.wd_token_id_seq
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1;

-- DROP SEQUENCE public.wd_topic_id_seq;

CREATE SEQUENCE public.wd_topic_id_seq
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1;

-- DROP SEQUENCE public.wd_user_additional_id_seq;

CREATE SEQUENCE public.wd_user_additional_id_seq
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 176;

-- DROP SEQUENCE public.wd_user_app_rel_id_seq;

CREATE SEQUENCE public.wd_user_app_rel_id_seq
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 278;

-- DROP SEQUENCE public.wd_user_authen_temp_id_seq;

CREATE SEQUENCE public.wd_user_authen_temp_id_seq
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 49;

-- DROP SEQUENCE public.wd_user_id_seq;

CREATE SEQUENCE public.wd_user_id_seq
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 281;

-- DROP SEQUENCE public.wd_user_role_id_seq;

CREATE SEQUENCE public.wd_user_role_id_seq
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 118;

-- DROP SEQUENCE public.wd_version_id_seq;

CREATE SEQUENCE public.wd_version_id_seq
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1;

-- DROP SEQUENCE public.wd_personal_contract_seq;

CREATE SEQUENCE public.wd_personal_contract_seq
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1;

CREATE TABLE public.wd_admin_user (
	username varchar(50) NULL,
	password varchar(50) NULL,
	actived bool NULL,
	id int4 NOT NULL DEFAULT nextval('wd_admin_user_id_seq'::regclass),
	CONSTRAINT wd_admin_user_pkey PRIMARY KEY (id),
	CONSTRAINT wd_admin_user_uni01 UNIQUE (username)
)
WITH (
	OIDS=FALSE
) ;

CREATE TABLE public.wd_authen_type (
	id int4 NOT NULL DEFAULT nextval('wd_authen_type_id_seq'::regclass),
	"name" varchar NULL,
	value varchar NULL,
	active bool NULL,
	CONSTRAINT wd_authen_type_pkey PRIMARY KEY (id),
	CONSTRAINT wd_authen_type_uni01 UNIQUE ("name")
)
WITH (
	OIDS=FALSE
) ;

CREATE TABLE public.wd_user_authen_publish (
	id int4 NOT NULL,
	"name" varchar(500) NULL,
	remark varchar(500) NULL,
	last_update_date timestamp NULL,
	last_publish_date timestamp NULL,
	authen_type_id int4 NULL,
	wsdl_location varchar(500) NULL,
	service_endpoint varchar(500) NULL,
	port_type_name varchar(100) NULL,
	operation_name varchar(100) NULL,
	CONSTRAINT wd_user_authen_publish_pkey PRIMARY KEY (id),
	CONSTRAINT wd_user_authen_publish_wd_authen_type_fk FOREIGN KEY (authen_type_id) REFERENCES public.wd_authen_type(id)
)
WITH (
	OIDS=FALSE
) ;

CREATE TABLE public.wd_user_authen_temp (
	id int4 NOT NULL DEFAULT nextval('wd_user_authen_temp_id_seq'::regclass),
	"name" varchar(500) NULL,
	remark varchar(500) NULL,
	last_update_date timestamp NULL,
	last_publish_date timestamp NULL,
	authen_type_id int4 NULL,
	wsdl_location varchar(500) NULL,
	service_endpoint varchar(500) NULL,
	port_type_name varchar(100) NULL,
	operation_name varchar(100) NULL,
	CONSTRAINT wd_user_authen_temp_pkey PRIMARY KEY (id),
	CONSTRAINT wd_user_authen_temp_uni01 UNIQUE ("name"),
	CONSTRAINT wd_user_authen_temp_wd_authen_type_fk FOREIGN KEY (authen_type_id) REFERENCES public.wd_authen_type(id)
)
WITH (
	OIDS=FALSE
) ;

CREATE TABLE public.wd_authen_input_publish (
	id int4 NOT NULL,
	"index" int4 NULL,
	field_name varchar(100) NULL,
	default_value varchar(100) NULL,
	value_from varchar(100) NULL,
	xpath varchar(500) NULL,
	user_authen_id int4 NOT NULL,
	CONSTRAINT wd_authen_input_publish_pkey PRIMARY KEY (id),
	CONSTRAINT wd_authen_input_publish_wd_user_authen_publish_fk FOREIGN KEY (user_authen_id) REFERENCES public.wd_user_authen_publish(id)
)
WITH (
	OIDS=FALSE
) ;

CREATE TABLE public.wd_authen_input_temp (
	id int4 NOT NULL DEFAULT nextval('wd_authen_input_temp_id_seq'::regclass),
	"index" int4 NULL,
	field_name varchar(100) NULL,
	default_value varchar(100) NULL,
	value_from varchar(100) NULL,
	xpath varchar(500) NULL,
	user_authen_id int4 NOT NULL,
	CONSTRAINT wd_authen_input_temp_pkey PRIMARY KEY (id),
	CONSTRAINT wd_authen_input_temp_wd_user_authen_temp_fk FOREIGN KEY (user_authen_id) REFERENCES public.wd_user_authen_temp(id)
)
WITH (
	OIDS=FALSE
) ;

CREATE TABLE public.wd_authen_output_publish (
  id integer NOT NULL,
  field_name character varying(100),
  field_type character varying(50),
  success_type character varying(20),
  success_value character varying(100),
  user_authen_id integer NOT NULL,
  xpath character varying(500),
  json_sample character varying(3000),
  CONSTRAINT id_pk PRIMARY KEY (id),
  CONSTRAINT wd_authen_output_publish_wd_user_authen_publish_fk FOREIGN KEY (user_authen_id)
      REFERENCES public.wd_user_authen_publish (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
	OIDS=FALSE
) ;

CREATE TABLE public.wd_authen_output_temp (
	id integer NOT NULL DEFAULT nextval('wd_authen_output_temp_id_seq'::regclass),
  field_name character varying(100),
  field_type character varying(50),
  success_type character varying(20),
  success_value character varying(100),
  user_authen_id integer NOT NULL,
  xpath character varying(500),
  json_sample character varying(3000),
  CONSTRAINT wd_authen_output_temp_pkey PRIMARY KEY (id),
  CONSTRAINT wd_authen_output_temp_wd_user_authen_temp_fk FOREIGN KEY (user_authen_id)
      REFERENCES public.wd_user_authen_temp (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
	OIDS=FALSE
) ;

CREATE TABLE public.wd_user (
	"userId" varchar(40) NOT NULL,
	password varchar(30) NULL,
	"displayName" varchar(80) NULL,
	email varchar(80) NULL,
	"contactNumber" varchar(100) NULL,
	"roleName" varchar(10) NULL,
	"createdDate" timestamp NULL DEFAULT now(),
	"updatedDate" timestamp NULL DEFAULT now(),
	id int4 NOT NULL DEFAULT nextval('wd_user_id_seq'::regclass),
	"imageLinkUrl" varchar NULL,
	"userKey" varchar(200) NULL,
	"emailVerified" bool NULL,
	"enableEncryption" bool NULL,
	timezone varchar(100) NULL,
	"prefContactAPI" int4 NULL,
	deactivate bool NULL,
	"pricingPackage" int4 NULL,
	"lastSeenAtTime" int8 NULL,
	status varchar NULL,
	"statusMessage" varchar(200) NULL,
	"imageLink" varchar(300) NULL,
	"lastLoggedInAtTime" int8 NULL,
	"lastMessageAtTime" int8 NULL,
	connected bool NULL DEFAULT false,
	CONSTRAINT wd_user_pkey PRIMARY KEY (id),
	CONSTRAINT wd_user_unique01 UNIQUE ("userId")
)
WITH (
	OIDS=FALSE
) ;
CREATE UNIQUE INDEX wd_user_id_key ON public.wd_user (id DESC) ;
CREATE INDEX wd_user_idx_userid ON public.wd_user ("userId" DESC) ;

CREATE TABLE public.wd_block_user (
	id int4 NOT NULL DEFAULT nextval('wd_block_user_id_seq'::regclass),
	"userId" int4 NULL,
	"targetUserId" int4 NULL,
	"createdAtTime" int8 NULL DEFAULT 0,
	"updatedAtTime" int8 NULL DEFAULT 0,
	"userBlocked" bool NULL DEFAULT false,
	CONSTRAINT wd_block_user_pkey PRIMARY KEY (id),
	CONSTRAINT wd_block_user_actionuserid_wd_user_fk FOREIGN KEY ("userId") REFERENCES public.wd_user(id),
	CONSTRAINT wd_block_user_targetuserid_wd_user_fk FOREIGN KEY ("targetUserId") REFERENCES public.wd_user(id)
)
WITH (
	OIDS=FALSE
) ;
CREATE UNIQUE INDEX wd_block_user_id_uindex ON public.wd_block_user (id DESC) ;

CREATE TABLE public.wd_group (
	id int4 NOT NULL DEFAULT nextval('wd_group_id_seq'::regclass),
	"key" varchar(50) NULL,
	"clientGroupId" varchar(40) NULL,
	"name" varchar(100) NULL,
	status int4 NULL,
	"type" int4 NULL,
	"imageLinkUrl" varchar(200) NULL,
	"createdAtTime" int8 NULL,
	metadata varchar(100) NULL,
	"createdDate" timestamp NULL DEFAULT now(),
	"updatedDate" timestamp NULL DEFAULT now(),
	"updatedAtTime" int8 NULL,
	deactivated bool NULL DEFAULT false,
	"deactivatedDate" timestamp NULL,
	CONSTRAINT wd_group_pkey PRIMARY KEY (id)
)
WITH (
	OIDS=FALSE
) ;

CREATE TABLE public.wd_group_user_rel (
	"groupId" int4 NOT NULL,
	"userId" int4 NOT NULL,
	"role" int4 NULL,
	status int4 NULL,
	"unreadCount" int4 NULL,
	deleted bool NULL,
	"createdDate" timestamp NULL DEFAULT now(),
	"updatedDate" timestamp NULL DEFAULT now(),
	"deletedDate" timestamp NULL,
	"updatedAtTime" int8 NULL,
	"lastAddedAtTime" int8 NULL,
	"lastRemovedAtTime" int8 NULL,
	CONSTRAINT wd_group_user_rel_pkey PRIMARY KEY ("groupId","userId"),
	CONSTRAINT wd_group_fkey FOREIGN KEY ("groupId") REFERENCES public.wd_group(id) ON DELETE CASCADE ON UPDATE CASCADE,
	CONSTRAINT wd_user_fkey FOREIGN KEY ("userId") REFERENCES public.wd_user(id) ON DELETE CASCADE ON UPDATE CASCADE
)
WITH (
	OIDS=FALSE
) ;

CREATE TABLE public.wd_menu (
	id int4 NOT NULL DEFAULT nextval('wd_menu_id_seq'::regclass),
	"menuId" varchar(200) NOT NULL,
	"name" varchar(200) NOT NULL,
	link varchar(200) NULL,
	image varchar(250) NULL,
	image_cover varchar(200) NULL,
	seq int4 NULL,
	actived bool NULL,
	CONSTRAINT wd_menu_pkey PRIMARY KEY (id),
	CONSTRAINT wd_menu_uni01 UNIQUE (seq),
	CONSTRAINT wd_menu_uni02 UNIQUE ("menuId")
)
WITH (
	OIDS=FALSE
) ;

CREATE TABLE public.wd_sys_config (
	id int4 NOT NULL DEFAULT nextval('wd_sys_config_id_seq'::regclass),
	sys_group varchar(100) NOT NULL,
	sys_key varchar(100) NOT NULL,
	sys_value varchar(500) NULL,
	sys_description varchar(200) NULL,
	CONSTRAINT wd_sys_config_pk PRIMARY KEY (id)
)
WITH (
	OIDS=FALSE
) ;
CREATE UNIQUE INDEX wd_sys_config_unique_index ON public.wd_sys_config (sys_group,sys_key) ;

CREATE TABLE public.wd_user_role (
	id int4 NOT NULL DEFAULT nextval('wd_user_role_id_seq'::regclass),
	"name" varchar(50) NULL,
	remark varchar(150) NULL,
	active bool NULL,
	user_authen_temp_id int4 NOT NULL,
	user_authen_publish_id int4 NULL,
	CONSTRAINT wd_user_role_pkey PRIMARY KEY (id),
	CONSTRAINT wd_user_role_uni01 UNIQUE ("name"),
	CONSTRAINT wd_user_role_wd_user_authen_publish_fk FOREIGN KEY (user_authen_publish_id) REFERENCES public.wd_user_authen_publish(id),
	CONSTRAINT wd_user_role_wd_user_authen_temp_fk FOREIGN KEY (user_authen_temp_id) REFERENCES public.wd_user_authen_temp(id)
)
WITH (
	OIDS=FALSE
) ;

CREATE TABLE public.wd_version (
	id int4 NOT NULL DEFAULT nextval('wd_version_id_seq'::regclass),
	version_id varchar NULL,
	CONSTRAINT wd_version_pkey PRIMARY KEY (id)
)
WITH (
	OIDS=FALSE
) ;

CREATE TABLE public.wd_user_additional (
	id int4 NOT NULL DEFAULT nextval('wd_user_additional_id_seq'::regclass),
	"userId" varchar(40) NOT NULL,
	"roleId" int4 NULL,
	email varchar(80) NULL,
	mobile varchar(10) NULL,
	team varchar(200) NULL,
	department varchar(250) NULL,
	branch varchar(250) NULL,
	"createdDate" timestamp NULL DEFAULT now(),
	"updatedDate" timestamp NULL DEFAULT now(),
	"defaultUser" bool NULL DEFAULT false,
	"defaultUserUpdatedAtTime" timestamp NULL,
	CONSTRAINT wd_user_additional_pkey PRIMARY KEY (id),
	CONSTRAINT wd_user_additional_uni01 UNIQUE ("userId"),
	CONSTRAINT wd_user_additional_fkey01 FOREIGN KEY ("userId") REFERENCES public.wd_user("userId") ON DELETE CASCADE,
	CONSTRAINT wd_user_additional_fkey02 FOREIGN KEY ("roleId") REFERENCES public.wd_user_role(id)
)
WITH (
	OIDS=FALSE
) ;

CREATE TABLE public.wd_user_app_rel (
	id int4 NOT NULL DEFAULT nextval('wd_user_app_rel_id_seq'::regclass),
	"userId" int4 NOT NULL,
	"applicationId" varchar(200) NULL,
	"deviceKey" varchar(200) NULL,
	"createdDate" timestamp NULL DEFAULT now(),
	"updatedDate" timestamp NULL DEFAULT now(),
	"registrationId" varchar(200) NULL,
	"userKey" varchar(200) NULL,
	"pushNotificationFormat" bpchar(1) NULL,
	"deviceType" int4 NULL,
	"lastSyncTime" int8 NULL,
	CONSTRAINT wd_user_app_rel_pkey PRIMARY KEY (id),
	CONSTRAINT wd_user_app_rel_fkey FOREIGN KEY ("userId") REFERENCES public.wd_user(id) ON DELETE CASCADE ON UPDATE CASCADE
)
WITH (
	OIDS=FALSE
) ;
CREATE INDEX wd_user_app_rel_idx_devicekey ON public.wd_user_app_rel ("deviceKey" DESC) ;
CREATE INDEX wd_user_app_rel_idx_userid ON public.wd_user_app_rel ("userId" DESC) ;

CREATE TABLE public.wd_chat_message (
	id int4 NOT NULL DEFAULT nextval('wd_chat_message_id_seq'::regclass),
	"key" varchar(100) NULL,
	"fromUserId" int4 NULL,
	"toUserId" int4 NULL,
	"contactIds" varchar(2000) NULL,
	message varchar(4096) NULL,
	sent bool NULL DEFAULT false,
	delivered bool NULL DEFAULT false,
	"read" bool NULL DEFAULT false,
	"createdAtTime" int8 NULL,
	"type" int4 NULL,
	"source" int4 NULL,
	status int4 NOT NULL DEFAULT 0,
	paired_message_id int4 NULL,
	"contentType" int4 NULL,
	group_id int4 NULL,
	group_name varchar(200) NULL,
	from_user_name varchar(80) NULL,
	metadata varchar(100) NULL,
	delivered_time timestamp NULL,
	"createdDate" timestamp NULL DEFAULT now(),
	"updatedDate" timestamp NULL DEFAULT now(),
	"deviceKey" varchar(200) NULL,
	shared bool NULL DEFAULT false,
	"storeOnDevice" bool NULL DEFAULT false,
	"sentToServer" bool NULL DEFAULT false,
	"sendToDevice" bool NULL DEFAULT false,
	"sentMessageTimeAtServer" int4 NULL,
	"documentId" varchar NULL,
	"delete" bool NULL DEFAULT false,
	"paredUserId" varchar(100) NULL,
	CONSTRAINT wd_chat_message_pkey PRIMARY KEY (id),
	CONSTRAINT wd_chat_message_fromuserid_wd_user_fk FOREIGN KEY ("fromUserId") REFERENCES public.wd_user(id),
	CONSTRAINT wd_chat_message_togroupid_wd_group_fk FOREIGN KEY (group_id) REFERENCES public.wd_group(id),
	CONSTRAINT wd_chat_message_touserid_wd_user_fk FOREIGN KEY ("toUserId") REFERENCES public.wd_user(id)
)
WITH (
	OIDS=FALSE
) ;
CREATE INDEX wd_chat_message_idx_createdattime ON public.wd_chat_message ("createdAtTime" DESC) ;

CREATE TABLE public.wd_message_user_rel (
	id int4 NOT NULL DEFAULT nextval('wd_message_user_rel_id_seq'::regclass),
	"messageId" int4 NOT NULL,
	"userId" int4 NOT NULL,
	"read" bool NULL DEFAULT false,
	"delete" bool NULL DEFAULT false,
	"createdDate" timestamp NOT NULL DEFAULT now(),
	"updatedDate" timestamp NOT NULL DEFAULT now(),
	delivered bool NULL DEFAULT false,
	"deliveredAtTime" timestamp NULL,
	"readAtTime" timestamp NULL,
	blocked bool NULL DEFAULT false,
	"blockedAtTime" timestamp NULL,
	"deleteAtTime" timestamp NULL,
	CONSTRAINT wd_message_user_rel_pk PRIMARY KEY (id),
	CONSTRAINT wd_message_user_rel_wd_chat_message_fk FOREIGN KEY ("messageId") REFERENCES public.wd_chat_message(id),
	CONSTRAINT wd_message_user_rel_wd_user_fk FOREIGN KEY ("userId") REFERENCES public.wd_user(id)
)
WITH (
	OIDS=FALSE
) ;
CREATE UNIQUE INDEX wd_message_user_rel_constain_unique ON public.wd_message_user_rel ("messageId" DESC,"userId" DESC) ;

CREATE TABLE public.wd_personal_contract (
	id int4 NOT NULL DEFAULT nextval('wd_personal_contract_seq'::regclass),
	"actionUserId" int4 NOT NULL,
	"addedUserId" int4 NOT NULL,
	"createdDate" timestamp NOT NULL,
	"updatedDate" timestamp NULL,
	deleted bool NULL DEFAULT false,
	CONSTRAINT wd_personal_contract_pk PRIMARY KEY (id),
	CONSTRAINT wd_personal_contract_actionuser_wd_user_fk FOREIGN KEY ("actionUserId") REFERENCES public.wd_user(id),
	CONSTRAINT wd_personal_contract_addeduser_wd_user_fk FOREIGN KEY ("addedUserId") REFERENCES public.wd_user(id)
)
WITH (
	OIDS=FALSE
) ;
CREATE UNIQUE INDEX wd_personal_contract_unique_index ON public.wd_personal_contract ("actionUserId","addedUserId") ;

/* database : WDCHAT 
   version  : ?.?.? build 16/12/2560 deploy to kachat.beebuddy.net(100.100.20.211:9990)
   detail   : change action message in group. / in message, show only displayName 
   example  : when have action add member to group.
			  old : chat : id add id. 
			      : ICM  : id add id.
			  new : chat : displayName add displayName.
			      : ICM  : id(displayName) add id(displayName).
*/

ALTER TABLE wd_chat_message ADD COLUMN announce_message character varying(4096)