CREATE TABLE b_sender_list (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  NAME varchar(100),
  CODE varchar(60),
  SORT int NOT NULL DEFAULT 100,
  PRIMARY KEY (ID)
);

CREATE TABLE b_sender_contact (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  DATE_INSERT timestamp NOT NULL,
  DATE_UPDATE timestamp,
  TYPE_ID int NOT NULL DEFAULT 1,
  CODE varchar(255) NOT NULL,
  NAME varchar(255),
  USER_ID int,
  BLACKLISTED char(1) NOT NULL DEFAULT 'N',
  IS_READ char(1) NOT NULL DEFAULT 'N',
  IS_CLICK char(1) NOT NULL DEFAULT 'N',
  IS_UNSUB char(1) NOT NULL DEFAULT 'N',
  IS_SEND_SUCCESS char(1) NOT NULL DEFAULT 'N',
  CONSENT_STATUS char(1) NOT NULL DEFAULT 'N',
  CONSENT_REQUEST int NOT NULL DEFAULT 0,
  IP varchar(15),
  AGENT int NOT NULL DEFAULT 0,
  PRIMARY KEY (ID)
);
CREATE UNIQUE INDEX ux_b_sender_contact_type_id_code ON b_sender_contact (type_id, code);
CREATE INDEX ix_sender_contact_blacklisted_date_insert on b_sender_contact(blacklisted, date_insert);

CREATE TABLE b_sender_contact_list (
  CONTACT_ID int NOT NULL,
  LIST_ID int NOT NULL
);
CREATE UNIQUE INDEX ux_b_sender_contact_list_contact_id_list_id ON b_sender_contact_list (contact_id, list_id);
CREATE INDEX ix_b_sender_contact_list_list_id ON b_sender_contact_list (list_id);

CREATE TABLE b_sender_group (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  CODE varchar(30),
  NAME varchar(100),
  DESCRIPTION text,
  SORT int NOT NULL DEFAULT 100,
  ACTIVE char(1) NOT NULL DEFAULT 'Y',
  HIDDEN char(1) NOT NULL DEFAULT 'N',
  IS_SYSTEM char(1) NOT NULL DEFAULT 'N',
  ADDRESS_COUNT int NOT NULL DEFAULT 0,
  USE_COUNT int NOT NULL DEFAULT 0,
  USE_COUNT_EXCLUDE int NOT NULL DEFAULT 0,
  DATE_INSERT timestamp,
  DATE_UPDATE timestamp,
  DATE_USE timestamp,
  DATE_USE_EXCLUDE timestamp,
  STATUS varchar(1) DEFAULT 'N',
  PRIMARY KEY (ID)
);
CREATE UNIQUE INDEX ux_b_sender_group_code ON b_sender_group (code);
CREATE INDEX ix_b_sender_group_status ON b_sender_group (status);

CREATE TABLE b_sender_group_connector (
  GROUP_ID int NOT NULL,
  NAME varchar(100),
  ENDPOINT text,
  ADDRESS_COUNT int NOT NULL DEFAULT 0,
  FILTER_ID varchar(256)
);
CREATE INDEX ix_b_sender_group_connector_group_id ON b_sender_group_connector (group_id);

CREATE TABLE b_sender_group_counter (
  GROUP_ID int NOT NULL,
  TYPE_ID int NOT NULL,
  CNT int NOT NULL DEFAULT 0
);
CREATE UNIQUE INDEX ux_b_sender_group_counter_group_id_type_id ON b_sender_group_counter (group_id, type_id);

CREATE TABLE b_sender_mailing (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  DATE_INSERT timestamp,
  NAME varchar(100),
  DESCRIPTION text,
  ACTIVE char(1) NOT NULL DEFAULT 'Y',
  SITE_ID char(2) NOT NULL,
  SORT int NOT NULL DEFAULT 100,
  IS_PUBLIC char(1) NOT NULL DEFAULT 'Y',
  TRACK_CLICK char(1) NOT NULL DEFAULT 'N',
  TRIGGER_FIELDS text,
  EMAIL_FROM varchar(255),
  IS_TRIGGER char(1) NOT NULL DEFAULT 'N',
  PRIMARY KEY (ID)
);

CREATE TABLE b_sender_mailing_chain (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  MAILING_ID int NOT NULL,
  STATUS char(1) NOT NULL,
  POSTING_ID int,
  CREATED_BY int,
  UPDATED_BY int,
  PARENT_ID int,
  MESSAGE_CODE varchar(20) NOT NULL DEFAULT 'mail',
  MESSAGE_ID varchar(20) NOT NULL,
  IS_TRIGGER char(1) NOT NULL DEFAULT 'N',
  IS_ADS char(1) NOT NULL DEFAULT 'N',
  DATE_INSERT timestamp,
  DATE_UPDATE timestamp,
  TIME_SHIFT int NOT NULL DEFAULT 0,
  LAST_EXECUTED timestamp,
  AUTO_SEND_TIME timestamp,
  TITLE varchar(255),
  EMAIL_FROM varchar(255),
  SUBJECT varchar(255),
  MESSAGE text,
  PRIORITY varchar(60),
  LINK_PARAMS varchar(255),
  TEMPLATE_TYPE varchar(30),
  TEMPLATE_ID varchar(60),
  REITERATE char(1) NOT NULL DEFAULT 'N',
  MONTHS_OF_YEAR varchar(100),
  DAYS_OF_MONTH varchar(100),
  DAYS_OF_WEEK varchar(15),
  TIMES_OF_DAY varchar(255),
  ERROR_MESSAGE text,
  SEARCH_CONTENT text,
  WAITING_RECIPIENT char(1) NOT NULL DEFAULT 'N',
  PRIMARY KEY (ID)
);
CREATE INDEX ix_b_sender_mailing_chain_mailing_id_status ON b_sender_mailing_chain (mailing_id, status);
CREATE INDEX ix_b_sender_mailing_chain_reiterate_status ON b_sender_mailing_chain (reiterate, status);
CREATE INDEX ix_b_sender_mailing_chain_posting_id ON b_sender_mailing_chain (POSTING_ID);

CREATE TABLE b_sender_mailing_chain_group (
  CHAIN_ID int NOT NULL,
  GROUP_ID int NOT NULL,
  INCLUDE int NOT NULL DEFAULT 0
);
CREATE UNIQUE INDEX ux_b_sender_mailing_chain_group_chain_id_group_id_include ON b_sender_mailing_chain_group (chain_id, group_id, include);

CREATE TABLE b_sender_mailing_group (
  MAILING_ID int NOT NULL,
  GROUP_ID int NOT NULL,
  INCLUDE int NOT NULL DEFAULT 0
);
CREATE UNIQUE INDEX ux_b_sender_mailing_group_mailing_id_group_id_include ON b_sender_mailing_group (mailing_id, group_id, include);

CREATE TABLE b_sender_mailing_subscription (
  MAILING_ID int NOT NULL,
  CONTACT_ID int NOT NULL,
  DATE_INSERT timestamp,
  IS_UNSUB char(1) NOT NULL DEFAULT 'N',
  PRIMARY KEY (MAILING_ID, CONTACT_ID, IS_UNSUB)
);

CREATE TABLE b_sender_posting (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  DATE_UPDATE timestamp,
  MAILING_ID int NOT NULL,
  MAILING_CHAIN_ID int NOT NULL,
  STATUS char(1) NOT NULL DEFAULT 'D',
  DATE_SEND timestamp,
  DATE_PAUSE timestamp,
  DATE_SENT timestamp,
  DATE_CREATE timestamp,
  COUNT_SEND_ALL int NOT NULL DEFAULT 0,
  COUNT_SEND_NONE int NOT NULL DEFAULT 0,
  COUNT_SEND_ERROR int NOT NULL DEFAULT 0,
  COUNT_SEND_SUCCESS int NOT NULL DEFAULT 0,
  COUNT_SEND_DENY int NOT NULL DEFAULT 0,
  COUNT_READ int NOT NULL DEFAULT 0,
  COUNT_CLICK int NOT NULL DEFAULT 0,
  COUNT_UNSUB int NOT NULL DEFAULT 0,
  CONSENT_SUPPORT char(1) NOT NULL DEFAULT 'N',
  PRIMARY KEY (ID)
);
CREATE INDEX ix_b_sender_posting_mailing_id_status ON b_sender_posting (mailing_id, status);
CREATE INDEX ix_b_sender_posting_mailing_chain_id_status ON b_sender_posting (mailing_chain_id, status);

CREATE TABLE b_sender_posting_recipient (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  POSTING_ID int NOT NULL,
  CONTACT_ID int NOT NULL,
  STATUS char(1) NOT NULL,
  DATE_SENT timestamp,
  DATE_UPDATE timestamp DEFAULT CURRENT_TIMESTAMP,
  USER_ID int,
  DATE_DENY timestamp,
  FIELDS text,
  ROOT_ID int,
  IS_READ char(1) NOT NULL DEFAULT 'N',
  IS_CLICK char(1) NOT NULL DEFAULT 'N',
  IS_UNSUB char(1) NOT NULL DEFAULT 'N',
  PRIMARY KEY (ID)
);
CREATE UNIQUE INDEX ux_b_sender_posting_recipient_posting_id_contact_id ON b_sender_posting_recipient (posting_id, contact_id);
CREATE INDEX ix_b_sender_posting_recipient_posting_id_status ON b_sender_posting_recipient (posting_id, status);
CREATE INDEX ix_b_sender_posting_recipient_contact_id ON b_sender_posting_recipient (CONTACT_ID);
CREATE INDEX ix_b_sender_posting_recipient_contact_id_status ON b_sender_posting_recipient (CONTACT_ID, STATUS);

CREATE TABLE b_sender_posting_read (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  POSTING_ID int NOT NULL,
  RECIPIENT_ID int,
  DATE_INSERT timestamp,
  PRIMARY KEY (ID)
);
CREATE INDEX ix_b_sender_posting_read_posting_id_recipient_id ON b_sender_posting_read (posting_id, recipient_id);
CREATE INDEX ix_b_sender_posting_read_recipient_id ON b_sender_posting_read (recipient_id);

CREATE TABLE b_sender_posting_click (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  POSTING_ID int NOT NULL,
  RECIPIENT_ID int,
  DATE_INSERT timestamp,
  URL varchar(2000),
  PRIMARY KEY (ID)
);
CREATE INDEX ix_b_sender_posting_click_posting_id_recipient_id ON b_sender_posting_click (posting_id, recipient_id);
CREATE INDEX ix_b_sender_posting_click_recipient_id ON b_sender_posting_click (recipient_id);

CREATE TABLE b_sender_posting_unsub (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  RECIPIENT_ID int NOT NULL,
  POSTING_ID int NOT NULL,
  DATE_INSERT timestamp,
  PRIMARY KEY (ID)
);
CREATE INDEX ix_b_sender_posting_unsub_posting_id_recipient_id ON b_sender_posting_unsub (posting_id, recipient_id);

CREATE TABLE b_sender_preset_template (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  ACTIVE char(1) NOT NULL DEFAULT 'Y',
  NAME varchar(255) NOT NULL,
  CONTENT text,
  USE_COUNT int NOT NULL DEFAULT 0,
  DATE_INSERT timestamp,
  DATE_USE timestamp,
  PRIMARY KEY (ID)
);

CREATE TABLE b_sender_mailing_attachment (
  CHAIN_ID int NOT NULL,
  FILE_ID int NOT NULL,
  PRIMARY KEY (CHAIN_ID, FILE_ID)
);

CREATE TABLE b_sender_mailing_trigger (
  MAILING_CHAIN_ID int NOT NULL,
  IS_TYPE_START int NOT NULL DEFAULT 1,
  NAME varchar(255),
  EVENT varchar(255) NOT NULL,
  ENDPOINT text NOT NULL
);

CREATE TABLE b_sender_message (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  CODE varchar(255) NOT NULL,
  PRIMARY KEY (ID)
);

CREATE TABLE b_sender_message_field (
  MESSAGE_ID int NOT NULL,
  CODE varchar(255) NOT NULL,
  TYPE varchar(20) NOT NULL,
  VALUE text,
  PRIMARY KEY (MESSAGE_ID, CODE)
);

CREATE TABLE b_sender_call_log (
  CALL_ID varchar(255) NOT NULL,
  RECIPIENT_ID int NOT NULL,
  DATE_INSERT timestamp NOT NULL,
  PRIMARY KEY (CALL_ID, RECIPIENT_ID)
);

CREATE TABLE b_sender_agreement (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  USER_ID int8 NOT NULL,
  NAME varchar(100) NOT NULL,
  EMAIL varchar(255),
  DATE timestamp NOT NULL,
  IP_ADDRESS varchar(39) NOT NULL,
  PRIMARY KEY (ID)
);

CREATE TABLE b_sender_abuse (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  TEXT text,
  CONTACT_ID int8,
  CONTACT_TYPE_ID int8,
  CONTACT_CODE varchar(255) NOT NULL,
  DATE_INSERT timestamp NOT NULL,
  PRIMARY KEY (ID)
);
CREATE INDEX ix_b_sender_abuse_date_insert ON b_sender_abuse (date_insert);

CREATE TABLE b_sender_counter_daily (
  DATE_STAT date NOT NULL,
  SENT_CNT int NOT NULL DEFAULT 0,
  TEST_SENT_CNT int NOT NULL DEFAULT 0,
  ERROR_CNT int NOT NULL DEFAULT 0,
  ABUSE_CNT int NOT NULL DEFAULT 0,
  PRIMARY KEY (DATE_STAT)
);

CREATE TABLE b_sender_queue (
  ENTITY_TYPE varchar(20) NOT NULL,
  ENTITY_ID varchar(10) NOT NULL,
  LAST_ITEM varchar(255) NOT NULL,
  PRIMARY KEY (ENTITY_TYPE, ENTITY_ID)
);

CREATE TABLE b_sender_role (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  NAME varchar(255) NOT NULL,
  DEAL_CATEGORY_ID int DEFAULT -1,
  XML_ID varchar(255),
  PRIMARY KEY (ID)
);
CREATE INDEX ix_b_sender_role_xml_id ON b_sender_role (xml_id);

CREATE TABLE b_sender_role_permission (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  ROLE_ID int NOT NULL,
  ENTITY varchar(50) NOT NULL,
  ACTION varchar(50) NOT NULL,
  PERMISSION char(1),
  PRIMARY KEY (ID)
);

CREATE TABLE b_sender_role_access (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  ROLE_ID int NOT NULL,
  ACCESS_CODE varchar(100) NOT NULL,
  PRIMARY KEY (ID)
);
CREATE INDEX ix_b_sender_role_access_role_id ON b_sender_role_access (role_id);

CREATE TABLE b_sender_counter (
  CODE varchar(25) NOT NULL,
  VALUE int NOT NULL DEFAULT 0,
  DATE_UPDATE timestamp NOT NULL,
  PRIMARY KEY (CODE)
);

CREATE TABLE b_sender_role_relation (
  ID int8 GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  ROLE_ID int8 NOT NULL,
  RELATION varchar(8) NOT NULL DEFAULT '',
  PRIMARY KEY (ID)
);
CREATE INDEX ix_b_sender_role_relation_role_id ON b_sender_role_relation (role_id);
CREATE INDEX ix_b_sender_role_relation_relation ON b_sender_role_relation (relation);

CREATE TABLE b_sender_permission (
  ID int8 GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  ROLE_ID int8 NOT NULL,
  PERMISSION_ID varchar(32) NOT NULL DEFAULT '0',
  VALUE int NOT NULL DEFAULT 0,
  PRIMARY KEY (ID),
  UNIQUE (ROLE_ID, PERMISSION_ID)
);

CREATE TABLE b_sender_group_deal_category (
  GROUP_ID int8 NOT NULL,
  DEAL_CATEGORY_ID int8 NOT NULL
);
CREATE INDEX ix_b_sender_group_deal_category_group_id ON b_sender_group_deal_category (group_id);
CREATE INDEX ix_b_sender_group_deal_category_deal_category_id ON b_sender_group_deal_category (deal_category_id);

CREATE TABLE b_sender_posting_thread (
  THREAD_ID int8 NOT NULL,
  POSTING_ID int8 NOT NULL,
  STATUS varchar(1) NOT NULL DEFAULT 'N',
  THREAD_TYPE text NOT NULL,
  EXPIRE_AT timestamp NOT NULL,
  UNIQUE (THREAD_ID, POSTING_ID)
);

CREATE TABLE b_sender_message_utm (
  MESSAGE_ID int8 NOT NULL,
  CODE varchar(70) NOT NULL,
  VALUE varchar(512) NOT NULL,
  UNIQUE (MESSAGE_ID, CODE)
);
CREATE INDEX ix_b_sender_message_utm_code ON b_sender_message_utm (code);

CREATE TABLE b_sender_timeline_queue (
  ID int8 GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  POSTING_ID int8 NOT NULL,
  RECIPIENT_ID int8,
  FIELDS text,
  ENTITY_ID int8,
  CONTACT_TYPE_ID int8,
  CONTACT_CODE varchar(255) NOT NULL,
  STATUS varchar(1) NOT NULL DEFAULT 'N',
  DATE_INSERT timestamp NOT NULL
);

CREATE TABLE b_sender_group_data (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  GROUP_ID int NOT NULL,
  DATE_INSERT timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FILTER_ID varchar(256) NOT NULL,
  CRM_ENTITY_ID int,
  CRM_ENTITY_TYPE_ID int,
  NAME varchar(511),
  CRM_ENTITY_TYPE varchar(128),
  CONTACT_ID int,
  COMPANY_ID int,
  EMAIL varchar(511),
  IM varchar(511),
  PHONE varchar(128),
  HAS_EMAIL varchar(1),
  HAS_IMOL varchar(1),
  HAS_PHONE varchar(1),
  SENDER_TYPE_ID int
);
CREATE INDEX ix_b_sender_group_data_group_id_filter_id ON b_sender_group_data (group_id, filter_id);

CREATE TABLE b_sender_group_state (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  GROUP_ID int NOT NULL,
  DATE_INSERT timestamp DEFAULT CURRENT_TIMESTAMP,
  FILTER_ID varchar(256) NOT NULL,
  STATE int,
  ENDPOINT text,
  "offset" int
);
CREATE INDEX ix_b_sender_group_state_group_id_filter_id ON b_sender_group_state (group_id, filter_id);
CREATE INDEX ix_b_sender_group_state_state ON b_sender_group_state (state);

CREATE TABLE b_sender_group_queue (
  ID int8 GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  DATE_INSERT timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  GROUP_ID int NOT NULL,
  ENTITY_ID int NOT NULL,
  TYPE int NOT NULL
);
CREATE INDEX ix_b_sender_group_queue_type_entity_id_group_id ON b_sender_group_queue (type, entity_id, group_id);

CREATE TABLE b_sender_group_thread (
  THREAD_ID int8 NOT NULL,
  GROUP_STATE_ID int8 NOT NULL,
  STATUS varchar(1) NOT NULL DEFAULT 'N',
  STEP int8 NOT NULL DEFAULT 0,
  THREAD_TYPE text NOT NULL,
  EXPIRE_AT timestamp NOT NULL,
  UNIQUE (THREAD_ID, GROUP_STATE_ID)
);

CREATE TABLE b_sender_file (
  ID int8 GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  FILE_ID int8 NOT NULL,
  ENTITY_TYPE int8 NOT NULL,
  ENTITY_ID int8 NOT NULL,
  DATE_INSERT timestamp NOT NULL
);
CREATE INDEX ix_b_sender_file_entity_type_entity_id ON b_sender_file (entity_type, entity_id);
CREATE INDEX ix_b_sender_file_file_id ON b_sender_file (file_id);

CREATE TABLE IF NOT EXISTS b_sender_file_info (
	ID int NOT NULL, /*ID from b_file*/
	FILE_NAME varchar(255) NOT NULL,
	PRIMARY KEY (ID)
);
CREATE INDEX IF NOT EXISTS ix_b_sender_file_info_file_name ON b_sender_file_info (file_name);