CREATE TABLE b_bp_workflow_template (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  MODULE_ID varchar(32),
  ENTITY varchar(64) NOT NULL,
  DOCUMENT_TYPE varchar(128) NOT NULL,
  DOCUMENT_STATUS varchar(50),
  AUTO_EXECUTE int NOT NULL DEFAULT 0,
  NAME varchar(255),
  DESCRIPTION text,
  TEMPLATE bytea,
  PARAMETERS bytea,
  VARIABLES bytea,
  CONSTANTS bytea,
  MODIFIED timestamp NOT NULL,
  IS_MODIFIED char(1) NOT NULL DEFAULT 'N',
  USER_ID int,
  SYSTEM_CODE varchar(50),
  ACTIVE char(1) NOT NULL DEFAULT 'Y',
  ORIGINATOR_ID varchar(255),
  ORIGIN_ID varchar(255),
  IS_SYSTEM char(1) NOT NULL DEFAULT 'N',
  SORT int NOT NULL DEFAULT 10,
  TYPE varchar(15) NOT NULL DEFAULT 'default',
  SETTINGS TEXT NULL,
  PRIMARY KEY (ID)
);
CREATE INDEX ix_b_bp_workflow_template_module_id_entity_document_type ON b_bp_workflow_template (module_id, entity, document_type);

CREATE TABLE b_bp_workflow_state (
  ID varchar(32) NOT NULL,
  MODULE_ID varchar(32),
  ENTITY varchar(64) NOT NULL,
  DOCUMENT_ID varchar(128) NOT NULL,
  DOCUMENT_ID_INT int NOT NULL,
  WORKFLOW_TEMPLATE_ID int NOT NULL,
  STATE varchar(128),
  STATE_TITLE varchar(255),
  STATE_PARAMETERS text,
  MODIFIED timestamp NOT NULL,
  STARTED timestamp,
  STARTED_BY int,
  PRIMARY KEY (ID)
);
CREATE INDEX ix_b_bp_workflow_state_document_id_entity_module_id ON b_bp_workflow_state (document_id, entity, module_id);
CREATE INDEX ix_b_bp_workflow_state_document_id_int_entity_module_id_state ON b_bp_workflow_state (document_id_int, entity, module_id, state);
CREATE INDEX ix_b_bp_workflow_state_started_by ON b_bp_workflow_state (started_by);
CREATE INDEX ix_b_bp_workflow_state_started ON b_bp_workflow_state (started);
CREATE INDEX ix_b_bp_workflow_state_workflow_template_id_started ON b_bp_workflow_state (workflow_template_id, started);

CREATE TABLE b_bp_workflow_permissions (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  WORKFLOW_ID varchar(32) NOT NULL,
  OBJECT_ID varchar(64) NOT NULL,
  PERMISSION varchar(64) NOT NULL,
  PRIMARY KEY (ID)
);
CREATE INDEX ix_b_bp_workflow_permissions_workflow_id ON b_bp_workflow_permissions (workflow_id);

CREATE TABLE b_bp_workflow_instance (
  ID varchar(32) NOT NULL,
  MODULE_ID varchar(32),
  ENTITY varchar(64) NOT NULL,
  DOCUMENT_ID varchar(128) NOT NULL,
  WORKFLOW_TEMPLATE_ID int NOT NULL,
  WORKFLOW bytea,
  WORKFLOW_RO bytea,
  STARTED timestamp,
  STARTED_BY int,
  STARTED_EVENT_TYPE smallint NOT NULL DEFAULT 0,
  STATUS int,
  MODIFIED timestamp NOT NULL,
  OWNER_ID varchar(32),
  OWNED_UNTIL timestamp,
  PRIMARY KEY (ID)
);
CREATE INDEX ix_b_bp_workflow_instance_document_id_entity_module_id_started_ ON b_bp_workflow_instance (document_id, entity, module_id, started_event_type);
CREATE INDEX ix_b_bp_workflow_instance_started_by ON b_bp_workflow_instance (started_by);
CREATE INDEX ix_b_bp_workflow_instance_workflow_template_id_started ON b_bp_workflow_instance (workflow_template_id, started);

CREATE TABLE b_bp_tracking (
  ID int8 GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  WORKFLOW_ID varchar(32) NOT NULL,
  TYPE int NOT NULL,
  MODIFIED timestamp NOT NULL,
  ACTION_NAME varchar(128) NOT NULL,
  ACTION_TITLE varchar(255),
  EXECUTION_STATUS int NOT NULL DEFAULT 0,
  EXECUTION_RESULT int NOT NULL DEFAULT 0,
  ACTION_NOTE text,
  MODIFIED_BY int,
  COMPLETED char(1) NOT NULL DEFAULT 'N',
  PRIMARY KEY (ID)
);
CREATE INDEX ix_b_bp_tracking_workflow_id_type ON b_bp_tracking (workflow_id, type);
CREATE INDEX ix_b_bp_tracking_modified ON b_bp_tracking (modified);
CREATE INDEX ix_b_bp_tracking_completed_type_modified ON b_bp_tracking (completed, type, modified);

CREATE TABLE b_bp_task (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  WORKFLOW_ID varchar(32) NOT NULL,
  ACTIVITY varchar(128) NOT NULL,
  ACTIVITY_NAME varchar(128) NOT NULL,
  CREATED_DATE timestamp,
  MODIFIED timestamp NOT NULL,
  OVERDUE_DATE timestamp,
  NAME varchar(128) NOT NULL,
  DESCRIPTION text,
  PARAMETERS text,
  STATUS int NOT NULL DEFAULT 0,
  IS_INLINE char(1) NOT NULL DEFAULT 'N',
  DELEGATION_TYPE int NOT NULL DEFAULT 0,
  DOCUMENT_NAME varchar(255),
  PRIMARY KEY (ID)
);
CREATE INDEX ix_b_bp_task_overdue_date_modified ON b_bp_task (overdue_date, modified);
CREATE INDEX ix_b_bp_task_workflow_id_activity ON b_bp_task (workflow_id, activity);
CREATE INDEX ix_b_bp_task_workflow_id_overdue_date ON b_bp_task (workflow_id, overdue_date);
CREATE INDEX ix_b_bp_task_modified ON b_bp_task (modified);
CREATE INDEX ix_b_bp_task_created ON b_bp_task (created_date);

CREATE TABLE b_bp_task_user (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  USER_ID int NOT NULL,
  TASK_ID int NOT NULL,
  STATUS int NOT NULL DEFAULT 0,
  DATE_UPDATE timestamp,
  ORIGINAL_USER_ID int NOT NULL DEFAULT 0,
  PRIMARY KEY (ID)
);
CREATE UNIQUE INDEX ux_b_bp_task_user_user_id_task_id ON b_bp_task_user (user_id, task_id);
CREATE INDEX ix_b_bp_task_user_task_id ON b_bp_task_user (task_id);
CREATE INDEX ix_b_bp_task_user_user_id_status ON b_bp_task_user (user_id, status);

CREATE TABLE b_bp_history (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  MODULE_ID varchar(32),
  ENTITY varchar(64) NOT NULL,
  DOCUMENT_ID varchar(128) NOT NULL,
  NAME varchar(255) NOT NULL,
  DOCUMENT bytea,
  MODIFIED timestamp NOT NULL,
  USER_ID int,
  PRIMARY KEY (ID)
);
CREATE INDEX ix_b_bp_history_document_id_entity_module_id ON b_bp_history (document_id, entity, module_id);

CREATE TABLE b_bp_workflow_state_identify (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  WORKFLOW_ID varchar(32) NOT NULL,
  PRIMARY KEY (ID)
);
CREATE UNIQUE INDEX ux_b_bp_workflow_state_identify_workflow_id ON b_bp_workflow_state_identify (workflow_id);

CREATE TABLE b_bp_rest_activity (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  APP_ID varchar(128) NOT NULL,
  APP_NAME text,
  CODE varchar(128) NOT NULL,
  INTERNAL_CODE varchar(32) NOT NULL,
  HANDLER varchar(1000) NOT NULL,
  AUTH_USER_ID int NOT NULL DEFAULT 0,
  USE_SUBSCRIPTION char(1) NOT NULL DEFAULT '',
  USE_PLACEMENT char(1) NOT NULL DEFAULT 'N',
  NAME text,
  DESCRIPTION text,
  PROPERTIES text,
  RETURN_PROPERTIES text,
  DOCUMENT_TYPE text,
  FILTER text,
  IS_ROBOT char(1) NOT NULL DEFAULT 'N',
  PRIMARY KEY (ID)
);
CREATE UNIQUE INDEX ux_b_bp_rest_activity_internal_code ON b_bp_rest_activity (internal_code);

CREATE TABLE b_bp_scheduler_event (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  WORKFLOW_ID varchar(32) NOT NULL,
  HANDLER varchar(128) NOT NULL,
  EVENT_MODULE varchar(32) NOT NULL,
  EVENT_TYPE varchar(100) NOT NULL,
  ENTITY_ID varchar(100),
  EVENT_PARAMETERS text,
  PRIMARY KEY (ID)
);
CREATE INDEX ix_b_bp_scheduler_event_event_module_event_type_entity_id ON b_bp_scheduler_event (event_module, event_type, entity_id);
CREATE INDEX ix_b_bp_scheduler_event_workflow_id ON b_bp_scheduler_event (workflow_id);

CREATE TABLE b_bp_rest_provider (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  APP_ID varchar(128) NOT NULL,
  APP_NAME text,
  CODE varchar(128) NOT NULL,
  TYPE varchar(30) NOT NULL,
  HANDLER varchar(1000) NOT NULL,
  NAME text,
  DESCRIPTION text,
  PRIMARY KEY (ID)
);

CREATE TABLE b_bp_automation_trigger (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  NAME varchar(255) NOT NULL,
  CODE varchar(30) NOT NULL,
  MODULE_ID varchar(32) NOT NULL,
  ENTITY varchar(64) NOT NULL,
  DOCUMENT_TYPE varchar(128) NOT NULL,
  DOCUMENT_STATUS varchar(50) NOT NULL,
  APPLY_RULES text,
  PRIMARY KEY (ID)
);
CREATE INDEX ix_b_bp_automation_trigger_document_type_document_status ON b_bp_automation_trigger (document_type, document_status);

CREATE TABLE b_bp_global_const (
  ID varchar(50) NOT NULL,
  NAME text NOT NULL,
  DESCRIPTION text,
  PROPERTY_TYPE varchar(30) NOT NULL,
  IS_REQUIRED char(1) NOT NULL DEFAULT 'N',
  IS_MULTIPLE char(1) NOT NULL DEFAULT 'N',
  PROPERTY_OPTIONS text,
  PROPERTY_SETTINGS text,
  PROPERTY_VALUE text,
  CREATED_DATE timestamp,
  CREATED_BY int,
  VISIBILITY varchar(30) DEFAULT 'GLOBAL',
  MODIFIED_DATE timestamp,
  MODIFIED_BY int,
  PRIMARY KEY (ID)
);
CREATE INDEX ix_b_bp_global_const_visibility ON b_bp_global_const (visibility);

CREATE TABLE b_bp_script (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  MODULE_ID varchar(32),
  ENTITY varchar(64) NOT NULL,
  DOCUMENT_TYPE varchar(128) NOT NULL,
  NAME varchar(255),
  DESCRIPTION text,
  WORKFLOW_TEMPLATE_ID int NOT NULL,
  CREATED_DATE timestamp NOT NULL,
  CREATED_BY int NOT NULL,
  MODIFIED_DATE timestamp NOT NULL,
  MODIFIED_BY int NOT NULL,
  ORIGINATOR_ID varchar(255),
  ORIGIN_ID varchar(255),
  SORT int NOT NULL DEFAULT 10,
  ACTIVE char(1) NOT NULL DEFAULT 'Y',
  PRIMARY KEY (ID)
);
CREATE INDEX ix_b_bp_script_module_id_entity_document_type ON b_bp_script (module_id, entity, document_type);

CREATE TABLE b_bp_script_queue (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  SCRIPT_ID int NOT NULL,
  STARTED_DATE timestamp,
  STARTED_BY int,
  STATUS int NOT NULL DEFAULT 0,
  MODIFIED_DATE timestamp NOT NULL,
  MODIFIED_BY int NOT NULL,
  WORKFLOW_PARAMETERS text,
  PRIMARY KEY (ID)
);
CREATE INDEX ix_b_bp_script_queue_script_id ON b_bp_script_queue (script_id);
CREATE INDEX ix_b_bp_script_queue_script_id_status ON b_bp_script_queue (script_id, status);
CREATE INDEX ix_b_bp_script_queue_started_by ON b_bp_script_queue (started_by);

CREATE TABLE b_bp_script_queue_document (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  QUEUE_ID int NOT NULL,
  DOCUMENT_ID varchar(128) NOT NULL,
  WORKFLOW_ID varchar(32) NOT NULL DEFAULT '',
  STATUS int NOT NULL DEFAULT 0,
  STATUS_MESSAGE varchar(255),
  PRIMARY KEY (ID)
);
CREATE INDEX ix_b_bp_script_queue_document_queue_id ON b_bp_script_queue_document (queue_id);
CREATE INDEX ix_b_bp_script_queue_document_workflow_id ON b_bp_script_queue_document (workflow_id);
CREATE INDEX ix_b_bp_script_queue_document_queue_id_workflow_id ON b_bp_script_queue_document (queue_id, workflow_id);

CREATE TABLE b_bp_storage_activity (
  ID int8 GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  WORKFLOW_TEMPLATE_ID int8 NOT NULL,
  ACTIVITY_NAME varchar(128) NOT NULL,
  KEY_ID varchar(128) NOT NULL,
  KEY_VALUE text,
  PRIMARY KEY (ID)
);
CREATE INDEX ix_b_bp_storage_activity_workflow_template_id_activity_name ON b_bp_storage_activity (workflow_template_id, activity_name);

CREATE TABLE b_bp_global_var (
  ID varchar(50) NOT NULL,
  NAME text NOT NULL,
  DESCRIPTION text,
  PROPERTY_TYPE varchar(30) NOT NULL,
  IS_REQUIRED char(1) NOT NULL DEFAULT 'N',
  IS_MULTIPLE char(1) NOT NULL DEFAULT 'N',
  PROPERTY_OPTIONS text,
  PROPERTY_SETTINGS text,
  PROPERTY_VALUE text,
  CREATED_DATE timestamp,
  CREATED_BY int,
  VISIBILITY varchar(30) DEFAULT 'GLOBAL',
  MODIFIED_DATE timestamp,
  MODIFIED_BY int,
  PRIMARY KEY (ID)
);
CREATE INDEX ix_b_bp_global_var_visibility ON b_bp_global_var (visibility);

CREATE TABLE b_bp_debugger_session (
  ID varchar(32) NOT NULL,
  MODULE_ID varchar(32),
  ENTITY varchar(64) NOT NULL,
  DOCUMENT_TYPE varchar(128) NOT NULL,
  DOCUMENT_CATEGORY_ID int,
  MODE int NOT NULL,
  TITLE varchar(256),
  STARTED_BY int NOT NULL,
  STARTED_DATE timestamp NOT NULL,
  FINISHED_DATE timestamp,
  ACTIVE char(1) NOT NULL,
  FIXED char(1) NOT NULL,
  DEBUGGER_STATE smallint NOT NULL DEFAULT -1,
  PRIMARY KEY (ID)
);

CREATE TABLE b_bp_debugger_session_document (
  ID int8 GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  SESSION_ID varchar(32) NOT NULL,
  DOCUMENT_ID varchar(128) NOT NULL,
  DATE_EXPIRE timestamp,
  PRIMARY KEY (ID)
);

CREATE TABLE b_bp_debugger_session_workflow_context (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  SESSION_ID varchar(32) NOT NULL,
  WORKFLOW_ID varchar(32) NOT NULL,
  TEMPLATE_SHARDS_ID int,
  PRIMARY KEY (ID)
);

CREATE TABLE b_bp_debugger_session_template_shards (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  TEMPLATE_ID int NOT NULL,
  SHARDS bytea,
  TEMPLATE_TYPE int,
  MODIFIED timestamp NOT NULL,
  PRIMARY KEY (ID)
);

CREATE TABLE b_bp_workflow_duration_stat (
  ID int8 GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  WORKFLOW_ID varchar(32) NOT NULL,
  TEMPLATE_ID int8 NOT NULL,
  DURATION int8 NOT NULL,
  PRIMARY KEY (ID)
);
CREATE INDEX ix_b_bp_workflow_duration_stat_template_id ON b_bp_workflow_duration_stat (template_id);

CREATE TABLE b_bp_workflow_user (
  USER_ID int NOT NULL DEFAULT 0,
  WORKFLOW_ID varchar(32) NOT NULL,
  IS_AUTHOR int NOT NULL DEFAULT 1,
  WORKFLOW_STATUS int NOT NULL DEFAULT 0,
  TASK_STATUS int NOT NULL DEFAULT 0,
  MODIFIED timestamp NOT NULL,
  PRIMARY KEY (USER_ID, WORKFLOW_ID)
);
CREATE INDEX ix_b_bp_workflow_user_user_id_workflow_status_task_status_modif ON b_bp_workflow_user (user_id, workflow_status, task_status, modified);
CREATE INDEX ix_b_bp_workflow_user_user_id_is_author_task_status_modified ON b_bp_workflow_user (user_id, is_author, task_status, modified);
CREATE INDEX ix_b_bp_workflow_user_user_id_task_status_modified ON b_bp_workflow_user (user_id, task_status, modified);
CREATE INDEX ix_b_bp_workflow_user_workflow_id ON b_bp_workflow_user (workflow_id);

CREATE TABLE b_bp_workflow_meta (
  ID int8 GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  WORKFLOW_ID varchar(32) NOT NULL,
  START_DURATION int8,
  PRIMARY KEY (ID)
);
CREATE INDEX ix_b_bp_workflow_meta_workflow_id ON b_bp_workflow_meta (workflow_id);

CREATE TABLE b_bp_workflow_filter (
  WORKFLOW_ID varchar(32) NOT NULL,
  MODULE_ID varchar(32) NOT NULL,
  ENTITY varchar(64) NOT NULL,
  DOCUMENT_ID varchar(128) NOT NULL,
  TEMPLATE_ID int NOT NULL,
  STARTED timestamp NOT NULL,
  PRIMARY KEY (WORKFLOW_ID)
);
CREATE INDEX ix_b_bp_workflow_filter_document_id_entity_module_id ON b_bp_workflow_filter (document_id, entity, module_id);
CREATE INDEX ix_b_bp_workflow_filter_module_id ON b_bp_workflow_filter (module_id);
CREATE INDEX ix_b_bp_workflow_filter_template_id ON b_bp_workflow_filter (template_id);
CREATE INDEX ix_b_bp_workflow_filter_started ON b_bp_workflow_filter (started);

CREATE TABLE b_bp_task_search_content (
  TASK_ID int NOT NULL,
  WORKFLOW_ID varchar(32) NOT NULL,
  SEARCH_CONTENT text NOT NULL,
  PRIMARY KEY (TASK_ID)
);
CREATE INDEX ix_b_bp_task_search_content_workflow_id ON b_bp_task_search_content (workflow_id);
CREATE INDEX tx_b_bp_task_search_content_search_content ON b_bp_task_search_content USING GIN (to_tsvector('english', search_content));

CREATE TABLE b_bp_workflow_user_comment (
  USER_ID int NOT NULL DEFAULT 0,
  WORKFLOW_ID varchar(32) NOT NULL,
  UNREAD_CNT int NOT NULL DEFAULT 0,
  LAST_TYPE smallint NOT NULL DEFAULT 0,
  MODIFIED timestamp NOT NULL,
  PRIMARY KEY (USER_ID, WORKFLOW_ID)
);
CREATE INDEX ix_b_bp_workflow_user_comment_workflow_id ON b_bp_workflow_user_comment (workflow_id);
CREATE INDEX ix_b_bp_workflow_user_comment_last_type ON b_bp_workflow_user_comment (last_type);
CREATE INDEX ix_b_bp_workflow_user_comment_last_type_modified ON b_bp_workflow_user_comment (last_type, modified);

CREATE TABLE b_bp_workflow_result(
	ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	WORKFLOW_ID varchar(32) NOT NULL,
	ACTIVITY varchar(128) NOT NULL,
	RESULT text,
	CREATED_DATE timestamp NOT NULL,
	PRIORITY int NOT NULL DEFAULT 0,
	PRIMARY KEY (ID)
);
CREATE INDEX ix_bp_r_wf ON b_bp_workflow_result(workflow_id);

CREATE TABLE b_bp_workflow_template_settings
(
	ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	TEMPLATE_ID int NOT NULL,
	NAME varchar(255) NOT NULL,
	VALUE text DEFAULT NULL,
	PRIMARY KEY (ID)
);
CREATE INDEX ix_b_bp_workflow_template_settings_template_id ON b_bp_workflow_template_settings (TEMPLATE_ID);

CREATE TABLE b_bp_workflow_template_user_option
(
	ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	TEMPLATE_ID int NOT NULL,
	USER_ID int DEFAULT NULL,
	OPTION_CODE int NOT NULL,
	PRIMARY KEY (ID)
);
CREATE UNIQUE INDEX ux_bp_template_user_option ON b_bp_workflow_template_user_option (TEMPLATE_ID, USER_ID, OPTION_CODE);
CREATE INDEX ix_bp_user_option ON b_bp_workflow_template_user_option (USER_ID, OPTION_CODE);

CREATE TABLE b_bp_document_type_user_option
(
	ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	MODULE_ID varchar(32) NULL,
	ENTITY varchar(64) NOT NULL,
	DOCUMENT_TYPE varchar(128) NOT NULL,
	USER_ID int DEFAULT NULL,
	OPTION_CODE int NOT NULL,
	PRIMARY KEY (ID)
);
CREATE UNIQUE INDEX ux_bp_document_type_user_option ON b_bp_document_type_user_option(MODULE_ID, ENTITY, DOCUMENT_TYPE, USER_ID, OPTION_CODE);
CREATE INDEX ix_bp_document_type_user_option ON b_bp_document_type_user_option(MODULE_ID, ENTITY, USER_ID, OPTION_CODE);

CREATE TABLE b_bp_workflow_template_draft
(
	ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
	MODULE_ID varchar(32) NOT NULL,
	ENTITY varchar(64) NOT NULL,
	DOCUMENT_TYPE varchar(128) NOT NULL,
	TEMPLATE_ID int NULL,
	TEMPLATE_DATA bytea NOT NULL,
	STATUS int NOT NULL DEFAULT 0,
	USER_ID int NOT NULL,
	CREATED timestamp NOT NULL,
	PRIMARY KEY (ID)
);
CREATE INDEX ix_b_bp_wf_draft_template ON b_bp_workflow_template_draft (TEMPLATE_ID);
CREATE INDEX ix_b_bp_wf_draft_user ON b_bp_workflow_template_draft (USER_ID);