CREATE TABLE b_search_content (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  DATE_CHANGE timestamp NOT NULL,
  MODULE_ID varchar(50) NOT NULL,
  ITEM_ID varchar(255) NOT NULL,
  CUSTOM_RANK int NOT NULL DEFAULT 0,
  USER_ID int,
  ENTITY_TYPE_ID varchar(50),
  ENTITY_ID varchar(255),
  URL text,
  TITLE text,
  BODY text,
  TAGS text,
  PARAM1 text,
  PARAM2 text,
  UPD varchar(32),
  DATE_FROM timestamp,
  DATE_TO timestamp,
  PRIMARY KEY (ID)
);
CREATE UNIQUE INDEX ux_b_search_content_module_id_item_id ON b_search_content (module_id, item_id);
CREATE INDEX ix_b_search_content_module_id_param1_param2 ON b_search_content (module_id, param1, param2);
CREATE INDEX ix_b_search_content_entity_id_entity_type_id ON b_search_content (entity_id, entity_type_id);

CREATE TABLE b_search_content_text (
  SEARCH_CONTENT_ID int NOT NULL,
  SEARCH_CONTENT_MD5 char(32) NOT NULL,
  SEARCHABLE_CONTENT text,
  PRIMARY KEY (SEARCH_CONTENT_ID)
);

CREATE TABLE b_search_content_param (
  SEARCH_CONTENT_ID int NOT NULL,
  PARAM_NAME varchar(100) NOT NULL,
  PARAM_VALUE varchar(100) NOT NULL
);
CREATE INDEX ix_b_search_content_param_search_content_id_param_name ON b_search_content_param (search_content_id, param_name);
CREATE INDEX ix_b_search_content_param_param_name_param_value_search_content ON b_search_content_param (param_name, param_value, search_content_id);

CREATE TABLE b_search_content_right (
  SEARCH_CONTENT_ID int NOT NULL,
  GROUP_CODE varchar(100) NOT NULL
);
CREATE UNIQUE INDEX ux_b_search_content_right_search_content_id_group_code ON b_search_content_right (search_content_id, group_code);

CREATE TABLE b_search_user_right (
  USER_ID int NOT NULL,
  GROUP_CODE varchar(100) NOT NULL
);
CREATE UNIQUE INDEX ux_b_search_user_right_user_id_group_code ON b_search_user_right (user_id, group_code);

CREATE TABLE b_search_content_site (
  SEARCH_CONTENT_ID int NOT NULL,
  SITE_ID char(2) NOT NULL,
  URL text,
  PRIMARY KEY (SEARCH_CONTENT_ID, SITE_ID)
);

CREATE TABLE b_search_content_stem (
  SEARCH_CONTENT_ID int NOT NULL,
  LANGUAGE_ID char(2) NOT NULL,
  STEM int NOT NULL,
  TF real NOT NULL,
  PS real NOT NULL
);
CREATE UNIQUE INDEX ux_b_search_content_stem_stem_language_id_tf_ps_search_content_ ON b_search_content_stem (stem, language_id, tf, ps, search_content_id);
CREATE INDEX ix_b_search_content_stem_search_content_id ON b_search_content_stem (search_content_id);

CREATE TABLE b_search_stem (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  STEM varchar(50) NOT NULL,
  PRIMARY KEY (ID)
);
CREATE UNIQUE INDEX ux_b_search_stem_stem ON b_search_stem (stem);

CREATE TABLE b_search_content_title (
  SEARCH_CONTENT_ID int NOT NULL,
  SITE_ID char(2) NOT NULL,
  POS int NOT NULL,
  WORD varchar(100) NOT NULL
);
CREATE UNIQUE INDEX ux_b_search_content_title_site_id_word_search_content_id_pos ON b_search_content_title (site_id, word, search_content_id, pos);
CREATE INDEX ix_b_search_content_title_search_content_id ON b_search_content_title (search_content_id);

CREATE TABLE b_search_content_freq (
  STEM int NOT NULL DEFAULT '0',
  LANGUAGE_ID char(2) NOT NULL,
  SITE_ID char(2),
  FREQ real,
  TF real
);
CREATE UNIQUE INDEX ux_b_search_content_freq_stem_language_id_site_id ON b_search_content_freq (stem, language_id, site_id);

CREATE TABLE b_search_custom_rank (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  APPLIED char(1) NOT NULL DEFAULT 'N',
  RANK int NOT NULL DEFAULT 0,
  SITE_ID char(2) NOT NULL,
  MODULE_ID varchar(200) NOT NULL,
  PARAM1 text,
  PARAM2 text,
  ITEM_ID varchar(255),
  PRIMARY KEY (ID)
);
CREATE INDEX ix_b_search_custom_rank_site_id_module_id ON b_search_custom_rank (site_id, module_id);

CREATE TABLE b_search_tags (
  SEARCH_CONTENT_ID int NOT NULL,
  SITE_ID char(2) NOT NULL,
  NAME varchar(255) NOT NULL,
  PRIMARY KEY (SEARCH_CONTENT_ID, SITE_ID, NAME)
);
CREATE INDEX ix_b_search_tags_name ON b_search_tags (name);

CREATE TABLE b_search_suggest (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  SITE_ID char(2) NOT NULL,
  FILTER_MD5 varchar(32) NOT NULL,
  PHRASE varchar(250) NOT NULL,
  RATE real NOT NULL,
  TIMESTAMP_X timestamp NOT NULL,
  RESULT_COUNT int NOT NULL,
  PRIMARY KEY (ID)
);
CREATE INDEX ix_b_search_suggest_filter_md5_phrase_rate ON b_search_suggest (filter_md5, phrase, rate);
CREATE INDEX ix_b_search_suggest_phrase_rate ON b_search_suggest (phrase, rate);
CREATE INDEX ix_b_search_suggest_timestamp_x ON b_search_suggest (timestamp_x);