CREATE TABLE b_im_chat (
	ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	PARENT_ID int DEFAULT 0,
	PARENT_MID int DEFAULT 0,
	TITLE varchar(255),
	DESCRIPTION text,
	COLOR varchar(255),
	TYPE char(1),
	EXTRANET char(1),
	AUTHOR_ID int NOT NULL,
	AVATAR int,
	PIN_MESSAGE_ID int DEFAULT 0,
	CALL_TYPE smallint DEFAULT 0,
	CALL_NUMBER varchar(20),
	ENTITY_TYPE varchar(50),
	ENTITY_ID varchar(255),
	ENTITY_DATA_1 varchar(255),
	ENTITY_DATA_2 varchar(255),
	ENTITY_DATA_3 varchar(255),
	DISK_FOLDER_ID int,
	MESSAGE_COUNT int DEFAULT 0,
	USER_COUNT int DEFAULT 0,
	PREV_MESSAGE_ID int,
	LAST_MESSAGE_ID int,
	LAST_MESSAGE_STATUS varchar(50) DEFAULT 'received',
	DATE_CREATE timestamp,
	MANAGE_USERS varchar(255) NOT NULL DEFAULT 'MEMBER',
	MANAGE_USERS_ADD varchar(255) NOT NULL DEFAULT 'MEMBER',
	MANAGE_USERS_DELETE varchar(255) NOT NULL DEFAULT 'MANAGER',
	MANAGE_UI varchar(255) NOT NULL DEFAULT 'MEMBER',
	MANAGE_SETTINGS varchar(255) NOT NULL DEFAULT 'OWNER',
	DISAPPEARING_TIME int,
	CAN_POST varchar(255) NOT NULL DEFAULT 'MEMBER',
	PRIMARY KEY (ID)
);
CREATE INDEX ix_b_im_chat_author_id_type ON b_im_chat (author_id, type);
CREATE INDEX ix_b_im_chat_entity_type_entity_id_author_id ON b_im_chat (entity_type, entity_id, author_id);
CREATE INDEX ix_b_im_chat_call_number_author_id ON b_im_chat (call_number, author_id);
CREATE INDEX ix_b_im_chat_type ON b_im_chat (type);
CREATE INDEX ix_b_im_chat_parent_id_parent_mid ON b_im_chat (parent_id, parent_mid);
CREATE INDEX ix_b_im_chat_avatar ON b_im_chat (avatar);

CREATE TABLE b_im_chat_index (
	CHAT_ID int NOT NULL,
	SEARCH_CONTENT text,
	SEARCH_TITLE varchar(511),
	PRIMARY KEY (CHAT_ID)
);
CREATE INDEX ix_b_im_chat_index_search_title ON b_im_chat_index (search_title);

CREATE TABLE b_im_message (
	ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	CHAT_ID int NOT NULL,
	AUTHOR_ID int NOT NULL,
	MESSAGE text,
	MESSAGE_OUT text,
	DATE_CREATE timestamp NOT NULL,
	EMAIL_TEMPLATE varchar(255),
	NOTIFY_TYPE smallint DEFAULT 0,
	NOTIFY_MODULE varchar(255),
	NOTIFY_EVENT varchar(255),
	NOTIFY_TAG varchar(255),
	NOTIFY_SUB_TAG varchar(255),
	NOTIFY_TITLE varchar(255),
	NOTIFY_BUTTONS text,
	NOTIFY_READ char(1) DEFAULT 'N',
	IMPORT_ID int,
	PRIMARY KEY (ID)
);
CREATE INDEX ix_b_im_message_notify_tag_author_id ON b_im_message (notify_tag, author_id);
CREATE INDEX ix_b_im_message_notify_sub_tag_author_id ON b_im_message (notify_sub_tag, author_id);
CREATE INDEX ix_b_im_message_chat_id_notify_read ON b_im_message (chat_id, notify_read);
CREATE INDEX ix_b_im_message_chat_id_date_create ON b_im_message (chat_id, date_create);
CREATE INDEX ix_b_im_message_notify_type_date_create ON b_im_message (notify_type, date_create);
CREATE INDEX ix_b_im_message_author_id_chat_id ON b_im_message (author_id, chat_id);

CREATE TABLE b_im_message_index (
	MESSAGE_ID int NOT NULL,
	SEARCH_CONTENT text,
	PRIMARY KEY (MESSAGE_ID)
);

CREATE TABLE b_im_message_param (
	ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	MESSAGE_ID int NOT NULL,
	PARAM_NAME varchar(100) NOT NULL,
	PARAM_VALUE varchar(100),
	PARAM_JSON text,
	PRIMARY KEY (ID)
);
CREATE INDEX ix_b_im_message_param_message_id_param_name ON b_im_message_param (message_id, param_name);
CREATE INDEX ix_b_im_message_param_param_name_param_value_message_id ON b_im_message_param (param_name, param_value, message_id);

CREATE TABLE b_im_message_favorite (
	ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	USER_ID int NOT NULL,
	CHAT_ID int NOT NULL,
	MESSAGE_ID int NOT NULL,
	DATE_CREATE timestamp NOT NULL
);
CREATE UNIQUE INDEX ux_b_im_message_favorite_id ON b_im_message_favorite (id);
CREATE INDEX ix_b_im_message_favorite_user_id_date_create ON b_im_message_favorite (user_id, date_create);
CREATE INDEX ix_b_im_message_favorite_chat_id_date_create ON b_im_message_favorite (chat_id, date_create);

CREATE TABLE b_im_status (
	USER_ID int NOT NULL,
	COLOR varchar(255),
	STATUS varchar(50) DEFAULT 'online',
	STATUS_TEXT varchar(255),
	IDLE timestamp,
	DESKTOP_LAST_DATE timestamp,
	MOBILE_LAST_DATE timestamp,
	EVENT_ID int,
	EVENT_UNTIL_DATE timestamp,
	INVITED char(1) DEFAULT 'N',
	PRIMARY KEY (USER_ID)
);
CREATE INDEX ix_b_im_status_event_until_date ON b_im_status (event_until_date);

CREATE TABLE b_im_relation (
	ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	CHAT_ID int NOT NULL,
	MESSAGE_TYPE char(1) DEFAULT 'P',
	USER_ID int NOT NULL,
	START_ID int DEFAULT 0,
	UNREAD_ID int DEFAULT 0,
	LAST_ID int DEFAULT 0,
	LAST_SEND_ID int DEFAULT 0,
	LAST_FILE_ID int DEFAULT 0,
	LAST_READ timestamp,
	STATUS smallint DEFAULT 0,
	CALL_STATUS smallint DEFAULT 0,
	MESSAGE_STATUS varchar(50) DEFAULT 'received',
	NOTIFY_BLOCK char(1) DEFAULT 'N',
	MANAGER char(1) DEFAULT 'N',
	COUNTER int DEFAULT 0,
	START_COUNTER int DEFAULT 0,
	LAST_SEND_MESSAGE_ID int NOT NULL DEFAULT 0,
	REASON varchar(50) NOT NULL DEFAULT '',
	IS_HIDDEN char(1) NOT NULL DEFAULT 'N',
	PRIMARY KEY (ID)
);
CREATE INDEX ix_b_im_relation_user_id_message_type_status ON b_im_relation (user_id, message_type, status);
CREATE INDEX ix_b_im_relation_user_id_message_type_chat_id ON b_im_relation (user_id, message_type, chat_id);
CREATE INDEX ix_b_im_relation_user_id_status ON b_im_relation (user_id, status);
CREATE INDEX ix_b_im_relation_message_type_status ON b_im_relation (message_type, status);
CREATE INDEX ix_b_im_relation_chat_id_user_id ON b_im_relation (chat_id, user_id);
CREATE INDEX ix_b_im_relation_status_counter ON b_im_relation (status, counter);
CREATE INDEX ix_b_im_relation_user_id_chat_id ON b_im_relation (user_id, chat_id);
CREATE INDEX ix_b_im_relation_chat_id_last_send_message_id ON b_im_relation (chat_id, last_send_message_id);

CREATE TABLE b_im_recent (
	USER_ID int NOT NULL,
	ITEM_TYPE char(1) NOT NULL DEFAULT 'P',
	ITEM_ID int NOT NULL,
	ITEM_MID int NOT NULL,
	ITEM_CID int DEFAULT 0,
	ITEM_RID int DEFAULT 0,
	ITEM_OLID int DEFAULT 0,
	PINNED char(1) DEFAULT 'N',
	UNREAD char(1) DEFAULT 'N',
	DATE_MESSAGE timestamp,
	DATE_UPDATE timestamp,
	MARKED_ID int,
	PIN_SORT int,
	DATE_LAST_ACTIVITY timestamp NOT NULL DEFAULT current_timestamp,
	PRIMARY KEY (USER_ID, ITEM_TYPE, ITEM_ID)
);
CREATE INDEX ix_b_im_recent_item_type_item_id ON b_im_recent (item_type, item_id);
CREATE INDEX ix_b_im_recent_date_update ON b_im_recent (date_update);
CREATE INDEX ix_b_im_recent_item_rid ON b_im_recent (item_rid);
CREATE INDEX ix_b_im_recent_item_mid ON b_im_recent (item_mid);
CREATE INDEX ix_b_im_recent_user_id_item_cid ON b_im_recent (user_id, item_cid);
CREATE INDEX ix_b_im_recent_pinned_user_id_pin_sort ON b_im_recent (pinned, user_id, pin_sort);
CREATE INDEX ix_b_im_recent_item_cid_user_id ON b_im_recent (item_cid, user_id);

CREATE TABLE b_im_last_search (
	ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	USER_ID int NOT NULL,
	DIALOG_ID varchar(50) NOT NULL,
	ITEM_CID int NOT NULL DEFAULT 0,
	ITEM_RID int NOT NULL DEFAULT 0,
	PRIMARY KEY (ID)
);
CREATE INDEX ix_b_im_last_search_user_id_dialog_id ON b_im_last_search (user_id, dialog_id);

CREATE TABLE b_im_bot (
	BOT_ID int NOT NULL,
	MODULE_ID varchar(50) NOT NULL,
	CODE varchar(50) NOT NULL,
	TYPE char(1) NOT NULL DEFAULT 'B',
	CLASS varchar(255),
	LANG varchar(50) DEFAULT '',
	METHOD_BOT_DELETE varchar(255),
	METHOD_MESSAGE_ADD varchar(255),
	METHOD_MESSAGE_UPDATE varchar(255),
	METHOD_MESSAGE_DELETE varchar(255),
	METHOD_WELCOME_MESSAGE varchar(255),
	METHOD_CONTEXT_GET varchar(255),
	TEXT_PRIVATE_WELCOME_MESSAGE text,
	TEXT_CHAT_WELCOME_MESSAGE text,
	COUNT_COMMAND int DEFAULT 0,
	COUNT_MESSAGE int DEFAULT 0,
	COUNT_CHAT int DEFAULT 0,
	COUNT_USER int DEFAULT 0,
	APP_ID varchar(128),
	VERIFIED char(1) DEFAULT 'N',
	OPENLINE char(1) DEFAULT 'N',
	HIDDEN char(1) DEFAULT 'N',
	BACKGROUND_ID varchar(50),
	PRIMARY KEY (BOT_ID)
);

CREATE TABLE b_im_bot_chat (
	ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	BOT_ID int NOT NULL,
	CHAT_ID int NOT NULL,
	PRIMARY KEY (ID)
);
CREATE INDEX ix_b_im_bot_chat_bot_id_chat_id ON b_im_bot_chat (bot_id, chat_id);

CREATE TABLE b_im_bot_token (
	ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	TOKEN varchar(32),
	DATE_CREATE timestamp NOT NULL,
	DATE_EXPIRE timestamp,
	BOT_ID int DEFAULT 0,
	DIALOG_ID varchar(255) NOT NULL,
	PRIMARY KEY (ID)
);
CREATE INDEX ix_b_im_bot_token_date_expire_bot_id ON b_im_bot_token (date_expire, bot_id);
CREATE INDEX ix_b_im_bot_token_token ON b_im_bot_token (token);

CREATE TABLE b_im_command (
	ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	MODULE_ID varchar(50) NOT NULL,
	BOT_ID int DEFAULT 0,
	APP_ID varchar(128),
	COMMAND varchar(255) NOT NULL,
	COMMON char(1) DEFAULT 'N',
	HIDDEN char(1) DEFAULT 'N',
	EXTRANET_SUPPORT char(1) DEFAULT 'N',
	SONET_SUPPORT char(1) DEFAULT 'N',
	CLASS varchar(255),
	METHOD_COMMAND_ADD varchar(255),
	METHOD_LANG_GET varchar(255),
	PRIMARY KEY (ID)
);
CREATE INDEX ix_b_im_command_bot_id ON b_im_command (bot_id);

CREATE TABLE b_im_command_lang (
	ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	COMMAND_ID int NOT NULL,
	LANGUAGE_ID char(2) NOT NULL,
	TITLE varchar(255),
	PARAMS varchar(255),
	PRIMARY KEY (ID)
);
CREATE UNIQUE INDEX ux_b_im_command_lang_command_id_language_id ON b_im_command_lang (command_id, language_id);

CREATE TABLE b_im_app (
	ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	MODULE_ID varchar(50) NOT NULL,
	BOT_ID int DEFAULT 0,
	APP_ID varchar(128),
	CODE varchar(255) NOT NULL,
	HASH varchar(32),
	REGISTERED varchar(32) DEFAULT 'N',
	ICON_FILE_ID int,
	CONTEXT varchar(128),
	IFRAME varchar(255),
	IFRAME_WIDTH int,
	IFRAME_HEIGHT int,
	IFRAME_POPUP char(1) DEFAULT 'N',
	JS varchar(255),
	HIDDEN char(1) DEFAULT 'N',
	EXTRANET_SUPPORT char(1) DEFAULT 'N',
	LIVECHAT_SUPPORT char(1) DEFAULT 'N',
	CLASS varchar(255),
	METHOD_LANG_GET varchar(255),
	PRIMARY KEY (ID)
);
CREATE INDEX ix_b_im_app_bot_id ON b_im_app (bot_id);

CREATE TABLE b_im_app_lang (
	ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	APP_ID int NOT NULL,
	LANGUAGE_ID char(2) NOT NULL,
	TITLE varchar(255),
	DESCRIPTION varchar(255),
	COPYRIGHT varchar(255),
	PRIMARY KEY (ID)
);
CREATE UNIQUE INDEX ux_b_im_app_lang_app_id_language_id ON b_im_app_lang (app_id, language_id);

CREATE TABLE b_im_alias (
	ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	ALIAS varchar(255) NOT NULL,
	DATE_CREATE timestamp,
	ENTITY_TYPE varchar(255) NOT NULL,
	ENTITY_ID int NOT NULL,
	PRIMARY KEY (ID)
);
CREATE UNIQUE INDEX ux_b_im_alias_alias ON b_im_alias (alias);
CREATE INDEX ix_b_im_alias_entity_id_entity_type ON b_im_alias (entity_id, entity_type);

CREATE TABLE b_im_external_avatar (
	ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	LINK_MD5 varchar(32) NOT NULL,
	AVATAR_ID int NOT NULL,
	PRIMARY KEY (ID)
);
CREATE INDEX ix_b_im_external_avatar_link_md5 ON b_im_external_avatar (link_md5);

CREATE TABLE b_im_no_relation_permission_disk (
	ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	CHAT_ID int,
	USER_ID int,
	ACTIVE_TO timestamp,
	PRIMARY KEY (ID)
);
CREATE INDEX ix_b_im_no_relation_permission_disk_user_id_chat_id ON b_im_no_relation_permission_disk (user_id, chat_id);

CREATE TABLE b_im_call (
	ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	TYPE int,
	SCHEME int,
	INITIATOR_ID int,
	IS_PUBLIC char(1) NOT NULL DEFAULT 'N',
	PUBLIC_ID varchar(32),
	PROVIDER varchar(32),
	ENTITY_TYPE varchar(32),
	ENTITY_ID varchar(32),
	PARENT_ID int,
	PARENT_UUID varchar(36),
	STATE varchar(50),
	START_DATE timestamp,
	END_DATE timestamp,
	CHAT_ID int,
	LOG_URL varchar(2000),
	UUID varchar(36),
	SECRET_KEY varchar(10),
	ENDPOINT varchar(255),
	RECORD_AUDIO char(1) NOT NULL DEFAULT 'N',
	AI_ANALYZE char(1) NOT NULL DEFAULT 'N',
	PRIMARY KEY (ID)
);
CREATE UNIQUE INDEX ux_b_im_call_public_id ON b_im_call (public_id);
CREATE INDEX ix_b_im_call_entity_type_entity_id_type_provider_end_date ON b_im_call (entity_type, entity_id, type, provider, end_date);
CREATE INDEX ix_b_im_call_chat_id ON b_im_call (chat_id);
CREATE INDEX ix_b_im_call_state_start ON b_im_call (state, start_date);

CREATE TABLE b_im_call_user (
	CALL_ID int NOT NULL,
	USER_ID int NOT NULL,
	STATE varchar(50),
	FIRST_JOINED timestamp,
	LAST_SEEN timestamp,
	IS_MOBILE char(1),
	SHARED_SCREEN char(1),
	RECORDED char(1),
	PRIMARY KEY (CALL_ID, USER_ID)
);

CREATE TABLE b_im_permission (
	ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	CHAT_ID int DEFAULT 0,
	USER_ID int DEFAULT 0,
	DATE_CREATE timestamp NOT NULL,
	AUTHOR_ID int DEFAULT 0,
	PERM_USER_PROMOTE char(1) DEFAULT 'N',
	PERM_CHAT_INFO char(1) DEFAULT 'N',
	PERM_USER_ADD char(1) DEFAULT 'N',
	PERM_USER_REMOVE char(1) DEFAULT 'N',
	PERM_MESSAGE_SEND char(1) DEFAULT 'N',
	PERM_MESSAGE_EDIT char(1) DEFAULT 'N',
	PERM_MESSAGE_DELETE char(1) DEFAULT 'N',
	PERM_MESSAGE_RICH char(1) DEFAULT 'N',
	PERM_MESSAGE_PIN char(1) DEFAULT 'N',
	PERM_MESSAGE_POLL char(1) DEFAULT 'N',
	PRIMARY KEY (ID)
);
CREATE INDEX ix_b_im_permission_chat_id_user_id ON b_im_permission (chat_id, user_id);

CREATE TABLE b_im_permission_duration (
	ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	PERMISSION_ID int DEFAULT 0,
	DATE_REMOVE timestamp NOT NULL,
	PRIMARY KEY (ID)
);
CREATE INDEX ix_b_im_permission_duration_permission_id ON b_im_permission_duration (permission_id);
CREATE INDEX ix_b_im_permission_duration_date_remove ON b_im_permission_duration (date_remove);

CREATE TABLE b_im_permission_log (
	ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	CHAT_ID int DEFAULT 0,
	USER_ID int NOT NULL,
	TEXT char(1) DEFAULT 'N',
	DATE_CREATE timestamp NOT NULL,
	PRIMARY KEY (ID)
);
CREATE INDEX ix_b_im_permission_log_chat_id_user_id ON b_im_permission_log (chat_id, user_id);
CREATE INDEX ix_b_im_permission_log_date_create ON b_im_permission_log (date_create);

CREATE TABLE b_im_block_user (
	ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	CHAT_ID int NOT NULL,
	USER_ID int NOT NULL,
	BLOCK_DATE timestamp DEFAULT null,
	PRIMARY KEY (ID)
);

CREATE TABLE b_im_conference (
	ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	ALIAS_ID int NOT NULL,
	PASSWORD text,
	INVITATION text,
	IS_BROADCAST char(1) NOT NULL DEFAULT 'N',
	CONFERENCE_START timestamp,
	CONFERENCE_END timestamp,
	PRIMARY KEY (ID)
);

CREATE TABLE b_im_conference_user_role (
	CONFERENCE_ID int NOT NULL,
	USER_ID int NOT NULL,
	ROLE varchar(64),
	PRIMARY KEY (CONFERENCE_ID, USER_ID)
);

CREATE TABLE b_im_option_group (
	ID int8 GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	NAME varchar(255),
	USER_ID int8,
	SORT int8 NOT NULL,
	DATE_CREATE timestamp NOT NULL,
	CREATE_BY_ID int8 NOT NULL,
	DATE_MODIFY timestamp,
	MODIFY_BY_ID int8,
	PRIMARY KEY (ID)
);

CREATE TABLE b_im_option_state (
	GROUP_ID int8 NOT NULL,
	NAME varchar(64) NOT NULL,
	VALUE varchar(255),
	PRIMARY KEY (GROUP_ID, NAME)
);

CREATE TABLE b_im_option_access (
	ID int8 GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	GROUP_ID int8 NOT NULL,
	ACCESS_CODE varchar(100),
	PRIMARY KEY (ID)
);
CREATE UNIQUE INDEX ux_b_im_option_access_group_id_access_code ON b_im_option_access (group_id, access_code);

CREATE TABLE b_im_option_user (
	USER_ID int8 NOT NULL,
	NOTIFY_GROUP_ID int8 NOT NULL,
	GENERAL_GROUP_ID int8 NOT NULL,
	PRIMARY KEY (USER_ID)
);

CREATE TABLE b_im_message_uuid (
	UUID varchar(36) NOT NULL,
	MESSAGE_ID int8,
	DATE_CREATE timestamp NOT NULL,
	PRIMARY KEY (UUID)
);
CREATE INDEX ix_b_im_message_uuid_date_create ON b_im_message_uuid (date_create);
CREATE INDEX ix_b_im_message_uuid_message_id ON b_im_message_uuid (message_id);

CREATE TABLE b_im_message_viewed (
	ID int8 GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	USER_ID int NOT NULL,
	CHAT_ID int NOT NULL,
	MESSAGE_ID int NOT NULL,
	DATE_CREATE timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (ID)
);
CREATE UNIQUE INDEX ux_b_im_message_viewed_user_id_chat_id_message_id ON b_im_message_viewed (user_id, chat_id, message_id);
CREATE INDEX ix_b_im_message_viewed_message_id_user_id_date_create ON b_im_message_viewed (message_id, user_id, date_create);
CREATE INDEX ix_b_im_message_viewed_date_create ON b_im_message_viewed (date_create);
CREATE INDEX ix_b_im_message_viewed_message_id ON b_im_message_viewed (MESSAGE_ID);

CREATE TABLE b_im_message_unread (
	ID int8 GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	USER_ID int NOT NULL,
	CHAT_ID int NOT NULL,
	MESSAGE_ID int NOT NULL,
	IS_MUTED char(1) NOT NULL,
	CHAT_TYPE char(1) NOT NULL,
	DATE_CREATE timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PARENT_ID int DEFAULT 0 NOT NULL,
	PRIMARY KEY (ID)
);
CREATE UNIQUE INDEX ux_b_im_message_unread_user_id_chat_id_message_id ON b_im_message_unread (user_id, chat_id, message_id);
CREATE INDEX ix_b_im_message_unread_date_create ON b_im_message_unread (date_create);
CREATE INDEX ix_b_im_message_unread_user_id_chat_id_chat_type_is_muted ON b_im_message_unread (user_id, chat_id, chat_type, is_muted);
CREATE INDEX ix_b_im_message_unread_chat_id_chat_type_is_muted ON b_im_message_unread (chat_id, chat_type, is_muted);
CREATE INDEX ix_b_im_message_unread_chat_id_user_id_message_id ON b_im_message_unread (chat_id, user_id, message_id);
CREATE INDEX ix_b_im_message_unread_message_id_user_id ON b_im_message_unread (message_id, user_id);
CREATE INDEX ix_b_im_message_unread_chat_type_message_id ON b_im_message_unread (chat_type, message_id);
CREATE INDEX ix_b_im_message_unread_parent_id_user_id ON b_im_message_unread (parent_id, user_id);
CREATE INDEX ix_b_im_message_unread_user_id_chat_type ON b_im_message_unread (user_id, chat_type);

CREATE TABLE b_im_link_url (
	ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	MESSAGE_ID int DEFAULT null,
	CHAT_ID int DEFAULT null,
	URL varchar(2000) DEFAULT null,
	PREVIEW_URL_ID int DEFAULT null,
	DATE_CREATE timestamp NOT NULL,
	AUTHOR_ID int NOT NULL,
	IS_INDEXED char(1) NOT NULL DEFAULT 'N',
	PRIMARY KEY (ID)
);
CREATE INDEX ix_b_im_link_url_chat_id_author_id_date_create_message_id ON b_im_link_url (chat_id, author_id, date_create, message_id);
CREATE INDEX ix_b_im_link_url_chat_id_date_create_message_id ON b_im_link_url (chat_id, date_create, message_id);
CREATE INDEX ix_b_im_link_url_message_id ON b_im_link_url (message_id);
CREATE INDEX ix_b_im_link_url_is_indexed ON b_im_link_url (is_indexed);
CREATE INDEX ix_b_im_link_url_author_id ON b_im_link_url (author_id);

CREATE TABLE b_im_link_url_index (
	URL_ID int NOT NULL,
	SEARCH_CONTENT text,
	PRIMARY KEY (URL_ID)
);

CREATE TABLE b_im_link_file (
	ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	MESSAGE_ID int DEFAULT null,
	CHAT_ID int DEFAULT null,
	SUBTYPE varchar(50),
	DISK_FILE_ID int DEFAULT null,
	DATE_CREATE timestamp NOT NULL,
	AUTHOR_ID int NOT NULL,
	PRIMARY KEY (ID)
);
CREATE INDEX ix_b_im_link_file_chat_id_subtype_id ON b_im_link_file (chat_id, subtype, id);
CREATE INDEX ix_b_im_link_file_chat_id_subtype_author_id_id ON b_im_link_file (chat_id, subtype, author_id, id);
CREATE INDEX ix_b_im_link_file_message_id_id ON b_im_link_file (message_id, id);
CREATE INDEX ix_b_im_link_file_disk_file_id ON b_im_link_file (disk_file_id);
CREATE INDEX ix_b_im_link_file_chat_id_subtype_date_create_id ON b_im_link_file (chat_id, subtype, date_create, id);

CREATE TABLE b_im_link_task (
	ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	MESSAGE_ID int,
	CHAT_ID int,
	TASK_ID int,
	AUTHOR_ID int,
	DATE_CREATE timestamp NOT NULL,
	PRIMARY KEY (ID)
);
CREATE INDEX ix_b_im_link_task_chat_id_task_id ON b_im_link_task (chat_id, task_id);
CREATE INDEX ix_b_im_link_task_message_id ON b_im_link_task (message_id);
CREATE UNIQUE INDEX ux_b_im_link_task_task_id ON b_im_link_task (task_id);

CREATE TABLE b_im_link_favorite (
	ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	MESSAGE_ID int,
	CHAT_ID int,
	AUTHOR_ID int,
	DATE_CREATE timestamp NOT NULL,
	PRIMARY KEY (ID)
);
CREATE INDEX ix_b_im_link_favorite_chat_id_author_id_id ON b_im_link_favorite (chat_id, author_id, id);
CREATE UNIQUE INDEX ux_b_im_link_favorite_message_id_author_id ON b_im_link_favorite (message_id, author_id);

CREATE TABLE b_im_link_pin (
	ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	MESSAGE_ID int NOT NULL,
	CHAT_ID int NOT NULL,
	DATE_CREATE timestamp NOT NULL,
	AUTHOR_ID int NOT NULL,
	PRIMARY KEY (ID)
);
CREATE UNIQUE INDEX ux_b_im_link_pin_message_id_chat_id ON b_im_link_pin (message_id, chat_id);
CREATE INDEX ix_b_im_link_pin_chat_id_id ON b_im_link_pin (chat_id, id);

CREATE TABLE b_im_link_calendar (
	ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	MESSAGE_ID int,
	CHAT_ID int,
	AUTHOR_ID int,
	DATE_CREATE timestamp,
	CALENDAR_ID int,
	CALENDAR_TITLE varchar(255),
	CALENDAR_DATE_FROM timestamp,
	CALENDAR_DATE_TO timestamp,
	PRIMARY KEY (ID)
);
CREATE UNIQUE INDEX ux_b_im_link_calendar_calendar_id ON b_im_link_calendar (calendar_id);
CREATE INDEX ix_b_im_link_calendar_chat_id_author_id_id ON b_im_link_calendar (chat_id, author_id, id);
CREATE INDEX ix_b_im_link_calendar_chat_id_date_create_id ON b_im_link_calendar (chat_id, date_create, id);
CREATE INDEX ix_b_im_link_calendar_chat_id_calendar_date_from_calendar_date_ ON b_im_link_calendar (chat_id, calendar_date_from, calendar_date_to, id);
CREATE INDEX ix_b_im_link_calendar_chat_id_id ON b_im_link_calendar (chat_id, id);
CREATE INDEX ix_b_im_link_calendar_message_id ON b_im_link_calendar (message_id);

CREATE TABLE b_im_link_calendar_index (
	ID int NOT NULL,
	SEARCH_CONTENT text,
	PRIMARY KEY (ID)
);

CREATE TABLE b_im_link_reminder (
	ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	MESSAGE_ID int NOT NULL,
	CHAT_ID int NOT NULL,
	DATE_CREATE timestamp NOT NULL,
	AUTHOR_ID int NOT NULL,
	DATE_REMIND timestamp,
	IS_REMINDED char(1) DEFAULT 'N',
	PRIMARY KEY (ID)
);
CREATE UNIQUE INDEX ux_b_im_link_reminder_author_id_message_id ON b_im_link_reminder (author_id, message_id);
CREATE INDEX ix_b_im_link_reminder_date_remind_is_reminded ON b_im_link_reminder (date_remind, is_reminded);
CREATE INDEX ix_b_im_link_reminder_chat_id_author_id_is_reminded ON b_im_link_reminder (chat_id, author_id, is_reminded);
CREATE INDEX ix_b_im_link_reminder_chat_id_author_id_id ON b_im_link_reminder (chat_id, author_id, id);

CREATE TABLE b_im_file_temporary (
	ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	DISK_FILE_ID int NOT NULL,
	DATE_CREATE timestamp NOT NULL,
	SOURCE varchar(50) NOT NULL,
	PRIMARY KEY (ID)
);
CREATE UNIQUE INDEX ux_b_im_file_temporary_disk_file_id ON b_im_file_temporary (disk_file_id);
CREATE INDEX ix_b_im_file_temporary_date_create_source ON b_im_file_temporary (date_create, source);

CREATE TABLE b_im_reaction (
	ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	CHAT_ID int NOT NULL,
	MESSAGE_ID int NOT NULL,
	USER_ID int NOT NULL,
	REACTION varchar(50) NOT NULL,
	DATE_CREATE timestamp NOT NULL,
	PRIMARY KEY (ID)
);
CREATE UNIQUE INDEX ux_b_im_reaction_message_id_reaction_user_id ON b_im_reaction (message_id, reaction, user_id);
CREATE INDEX ix_b_im_reaction_message_id_reaction_id ON b_im_reaction (message_id, reaction, id);
CREATE INDEX ix_b_im_reaction_user_id_message_id_reaction ON b_im_reaction (user_id, message_id, reaction);
CREATE INDEX ix_b_im_reaction_message_id ON b_im_reaction (message_id);

CREATE TABLE b_im_message_disappearing (
	MESSAGE_ID int NOT NULL,
	DATE_CREATE timestamp NOT NULL,
	DATE_REMOVE timestamp NOT NULL
);
CREATE UNIQUE INDEX ux_b_im_message_disappearing_message_id ON b_im_message_disappearing (message_id);

CREATE TABLE b_im_log (
	ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	USER_ID int NOT NULL,
	ENTITY_TYPE varchar(50),
	ENTITY_ID int,
	EVENT varchar(50) NOT NULL,
	DATE_CREATE timestamp NOT NULL,
	DATE_DELETE timestamp,
	PRIMARY KEY (ID)
);
CREATE UNIQUE INDEX ux_b_im_log_user_id_entity_type_entity_id ON b_im_log (user_id, entity_type, entity_id);
CREATE INDEX ix_b_im_log_user_id_date_create ON b_im_log (user_id, date_create);
CREATE INDEX ix_b_im_log_user_id ON b_im_log (user_id);
CREATE INDEX ix_b_im_log_date_delete ON b_im_log (date_delete);

CREATE TABLE b_im_last_message
(
	ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	USER_ID INT NOT NULL,
	CHAT_ID INT NOT NULL,
	MESSAGE_ID INT NOT NULL,
	DATE_CREATE timestamp NOT NULL,
	PRIMARY KEY (ID)
);
CREATE UNIQUE INDEX ux_b_im_last_message_chat_id_user_id ON b_im_last_message (chat_id, user_id);
CREATE INDEX ix_b_im_last_message_chat_id_date_create ON b_im_last_message (chat_id, date_create);

CREATE TABLE b_im_chat_param (
	ID INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	CHAT_ID INT NOT NULL,
	PARAM_NAME VARCHAR(100) NOT NULL,
	PARAM_VALUE VARCHAR(100),
	PARAM_JSON TEXT,
	PRIMARY KEY (ID)
);
CREATE INDEX ix_b_im_chat_param_chat_id_param_name ON b_im_chat_param (chat_id, param_name);
CREATE INDEX ix_b_im_chat_param_param_name_param_value_chat_id ON b_im_chat_param (param_name, param_value, chat_id);

CREATE TABLE b_im_hr_sync_queue
(
	ID INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	ENTITY_TYPE VARCHAR(25) NOT NULL,
	ENTITY_ID INT NOT NULL,
	DIRECTION VARCHAR(100) NOT NULL,
	NODE_ID INT NOT NULL,
	WITH_CHILD_NODES CHAR(1) NOT NULL DEFAULT 'N',
	POINTER INT NOT NULL DEFAULT 0,
	STATUS VARCHAR(100) NOT NULL DEFAULT '',
	IS_LOCKED CHAR(1) NOT NULL DEFAULT 'N',
	DATE_CREATE TIMESTAMP NULL,
	DATE_UPDATE TIMESTAMP NULL,
	PRIMARY KEY (ID)
);
CREATE INDEX ix_b_im_hr_sync_queue_entity_type_entity_id ON b_im_hr_sync_queue (entity_type, entity_id);

CREATE TABLE b_im_counter_overflow
(
	ID INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	USER_ID INT NOT NULL,
	CHAT_ID INT NOT NULL,
	PRIMARY KEY (ID)
);
CREATE UNIQUE INDEX ux_b_im_counter_overflow_chat_id_user_id ON b_im_counter_overflow (chat_id, user_id);
CREATE INDEX ix_b_im_counter_overflow_user_id_chat_id ON b_im_counter_overflow (user_id, chat_id);

CREATE TABLE b_im_recent_init_queue
(
	ID INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	USER_ID INT NOT NULL,
	STAGE VARCHAR(100) NOT NULL DEFAULT '',
	SOURCE VARCHAR(100) NOT NULL DEFAULT '',
	SOURCE_ID INT NULL,
	POINTER VARCHAR(255) NOT NULL DEFAULT '',
	STATUS VARCHAR(100) NOT NULL DEFAULT '',
	IS_LOCKED CHAR(1) NOT NULL DEFAULT 'N',
	DATE_CREATE TIMESTAMP NULL,
	DATE_UPDATE TIMESTAMP NULL,
	PRIMARY KEY (ID)
);

CREATE TABLE b_im_anchor
(
	ID INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	CHAT_ID INT NOT NULL,
	MESSAGE_ID INT NOT NULL,
	USER_ID INT NOT NULL,
	FROM_USER_ID INT NOT NULL,
	TYPE VARCHAR(100) NOT NULL,
	SUB_TYPE VARCHAR(100) DEFAULT NULL,
	PRIMARY KEY (ID)
);

CREATE UNIQUE INDEX ix_b_im_anchor_1 ON b_im_anchor (MESSAGE_ID, type, user_id, from_user_id);
CREATE INDEX ix_b_im_anchor_2 ON b_im_anchor (user_id, chat_id);


CREATE TABLE b_im_notify_group
(
	ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	USER_ID int NOT NULL,
	TITLE varchar(255) not null,
	DATE_CREATE timestamp NULL,
	DATE_UPDATE timestamp NULL,
	PRIMARY KEY (ID)
);
CREATE INDEX ix_b_im_notify_group_user_id ON b_im_notify_group (user_id);

CREATE TABLE b_im_notify_group_condition
(
	ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	GROUP_ID int not null,
	MODULE varchar(255) not null,
	EVENT varchar(255) not null default '',
	USER_ID int not null,
	DATE_CREATE timestamp NULL,
	PRIMARY KEY (ID)
);
CREATE UNIQUE INDEX ux_b_im_notify_group_condition_group_id_module_event ON b_im_notify_group_condition (group_id, module, event);