DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

CREATE TABLE Attachment (
  AttachmentId            INT8 NOT NULL,
  accessType              INT4,
  attachedAt              TIMESTAMP,
  attachmentContentId     INT8 NOT NULL,
  contentType             VARCHAR(255),
  name                    VARCHAR(255),
  attachment_size         INT4,
  attachedBy_id           VARCHAR(255),
  TaskData_Attachments_Id INT8,
  PRIMARY KEY (AttachmentId)
);

CREATE TABLE BAMTaskSummary (
  BAMTaskId         INT8 NOT NULL,
  createdDate       TIMESTAMP,
  duration          INT8,
  endDate           TIMESTAMP,
  processInstanceId INT8 NOT NULL,
  startDate         TIMESTAMP,
  status            VARCHAR(255),
  taskId            INT8 NOT NULL,
  taskName          VARCHAR(255),
  userId            VARCHAR(255),
  PRIMARY KEY (BAMTaskId)
);

CREATE TABLE BooleanExpression (
  id                        INT8 NOT NULL,
  expression                TEXT,
  type                      VARCHAR(255),
  Escalation_Constraints_Id INT8,
  PRIMARY KEY (id)
);

CREATE TABLE Content (
  id      INT8 NOT NULL,
  content OID,
--   content BYTEA,
  PRIMARY KEY (id)
);

CREATE TABLE ContextMappingInfo (
  mappingId   INT8         NOT NULL,
  CONTEXT_ID  VARCHAR(255) NOT NULL,
  KSESSION_ID INT4         NOT NULL,
  OPTLOCK     INT4,
  PRIMARY KEY (mappingId)
);

CREATE TABLE CorrelationKeyInfo (
  keyId             INT8 NOT NULL,
  name              VARCHAR(255),
  processInstanceId INT8 NOT NULL,
  OPTLOCK           INT4,
  PRIMARY KEY (keyId)
);

CREATE TABLE CorrelationPropertyInfo (
  propertyId           INT8 NOT NULL,
  name                 VARCHAR(255),
  value                VARCHAR(255),
  OPTLOCK              INT4,
  correlationKey_keyId INT8,
  PRIMARY KEY (propertyId)
);

CREATE TABLE Deadline (
  id                         INT8 NOT NULL,
  deadline_date              TIMESTAMP,
  escalated                  INT2,
  Deadlines_StartDeadLine_Id INT8,
  Deadlines_EndDeadLine_Id   INT8,
  PRIMARY KEY (id)
);

CREATE TABLE Delegation_delegates (
  task_id   INT8         NOT NULL,
  entity_id VARCHAR(255) NOT NULL
);

CREATE TABLE ErrorInfo (
  id         INT8 NOT NULL,
  message    VARCHAR(255),
  stacktrace VARCHAR(5000),
  timestamp  TIMESTAMP,
  REQUEST_ID INT8 NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE Escalation (
  id                     INT8 NOT NULL,
  name                   VARCHAR(255),
  Deadline_Escalation_Id INT8,
  PRIMARY KEY (id)
);

CREATE TABLE EventTypes (
  InstanceId INT8 NOT NULL,
  eventTypes VARCHAR(255)
);

CREATE TABLE I18NText (
  I18NTextId                    INT8 NOT NULL,
  language                      VARCHAR(255),
  shortText                     VARCHAR(255),
  text                          TEXT,
  Task_Subjects_Id              INT8,
  Task_Names_Id                 INT8,
  Task_Descriptions_Id          INT8,
  Reassignment_Documentation_Id INT8,
  Notification_Subjects_Id      INT8,
  Notification_Names_Id         INT8,
  Notification_Documentation_Id INT8,
  Notification_Descriptions_Id  INT8,
  Deadline_Documentation_Id     INT8,
  PRIMARY KEY (I18NTextId)
);

CREATE TABLE NodeInstanceLog (
  id                INT8 NOT NULL,
  connection        VARCHAR(255),
  log_date          TIMESTAMP,
  externalId        VARCHAR(255),
  nodeId            VARCHAR(255),
  nodeInstanceId    VARCHAR(255),
  nodeName          VARCHAR(255),
  nodeType          VARCHAR(255),
  processId         VARCHAR(255),
  processInstanceId INT8 NOT NULL,
  type              INT4 NOT NULL,
  workItemId        INT8,
  PRIMARY KEY (id)
);

CREATE TABLE Notification (
  DTYPE                       VARCHAR(31) NOT NULL,
  NotificationId              INT8        NOT NULL,
  priority                    INT4        NOT NULL,
  Escalation_Notifications_Id INT8,
  PRIMARY KEY (NotificationId)
);

CREATE TABLE Notification_BAs (
  task_id   INT8         NOT NULL,
  entity_id VARCHAR(255) NOT NULL
);

CREATE TABLE Notification_Recipients (
  task_id   INT8         NOT NULL,
  entity_id VARCHAR(255) NOT NULL
);

CREATE TABLE Notification_email_header (
  Notification_NotificationId INT8         NOT NULL,
  emailHeaders_id             INT8         NOT NULL,
  mapkey                      VARCHAR(255) NOT NULL,
  PRIMARY KEY (Notification_NotificationId, mapkey)
);

CREATE TABLE OrganizationalEntity (
  DTYPE VARCHAR(31)  NOT NULL,
  id    VARCHAR(255) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE PeopleAssignments_BAs (
  task_id   INT8         NOT NULL,
  entity_id VARCHAR(255) NOT NULL
);

CREATE TABLE PeopleAssignments_ExclOwners (
  task_id   INT8         NOT NULL,
  entity_id VARCHAR(255) NOT NULL
);

CREATE TABLE PeopleAssignments_PotOwners (
  task_id   INT8         NOT NULL,
  entity_id VARCHAR(255) NOT NULL
);

CREATE TABLE PeopleAssignments_Recipients (
  task_id   INT8         NOT NULL,
  entity_id VARCHAR(255) NOT NULL
);

CREATE TABLE PeopleAssignments_Stakeholders (
  task_id   INT8         NOT NULL,
  entity_id VARCHAR(255) NOT NULL
);

CREATE TABLE ProcessInstanceInfo (
  InstanceId               INT8 NOT NULL,
  lastModificationDate     TIMESTAMP,
  lastReadDate             TIMESTAMP,
  processId                VARCHAR(255),
  processInstanceByteArray OID,
--   processInstanceByteArray BYTEA,
  startDate                TIMESTAMP,
  state                    INT4 NOT NULL,
  OPTLOCK                  INT4,
  PRIMARY KEY (InstanceId)
);

CREATE TABLE ProcessInstanceLog (
  id                      INT8 NOT NULL,
  duration                INT8,
  end_date                TIMESTAMP,
  externalId              VARCHAR(255),
  user_identity           VARCHAR(255),
  outcome                 VARCHAR(255),
  parentProcessInstanceId INT8,
  processId               VARCHAR(255),
  processInstanceId       INT8 NOT NULL,
  processName             VARCHAR(255),
  processVersion          VARCHAR(255),
  start_date              TIMESTAMP,
  status                  INT4,
  PRIMARY KEY (id)
);

CREATE TABLE Reassignment (
  id                          INT8 NOT NULL,
  Escalation_Reassignments_Id INT8,
  PRIMARY KEY (id)
);

CREATE TABLE Reassignment_potentialOwners (
  task_id   INT8         NOT NULL,
  entity_id VARCHAR(255) NOT NULL
);

CREATE TABLE RequestInfo (
  id           INT8 NOT NULL,
  commandName  VARCHAR(255),
  deploymentId VARCHAR(255),
  executions   INT4 NOT NULL,
  businessKey  VARCHAR(255),
  message      VARCHAR(255),
  requestData  OID,
--   requestData  BYTEA,
  responseData OID,
--   responseData BYTEA,
  retries      INT4 NOT NULL,
  status       VARCHAR(255),
  timestamp    TIMESTAMP,
  PRIMARY KEY (id)
);

CREATE TABLE SessionInfo (
  id                   INT4 NOT NULL,
  lastModificationDate TIMESTAMP,
  rulesByteArray       OID,
--   rulesByteArray       BYTEA,
  startDate            TIMESTAMP,
  OPTLOCK              INT4,
  PRIMARY KEY (id)
);

CREATE TABLE Task (
  TaskId             INT8    NOT NULL,
  archived           INT2,
  allowedToDelegate  VARCHAR(255),
  formName           VARCHAR(255),
  priority           INT4    NOT NULL,
  subTaskStrategy    VARCHAR(255),
  activationTime     TIMESTAMP,
  createdOn          TIMESTAMP,
  deploymentId       VARCHAR(255),
  documentAccessType INT4,
  documentContentId  INT8    NOT NULL,
  documentType       VARCHAR(255),
  expirationTime     TIMESTAMP,
  faultAccessType    INT4,
  faultContentId     INT8    NOT NULL,
  faultName          VARCHAR(255),
  faultType          VARCHAR(255),
  outputAccessType   INT4,
  outputContentId    INT8    NOT NULL,
  outputType         VARCHAR(255),
  parentId           INT8    NOT NULL,
  previousStatus     INT4,
  processId          VARCHAR(255),
  processInstanceId  INT8    NOT NULL,
  processSessionId   INT4    NOT NULL,
  skipable           BOOLEAN NOT NULL,
  status             VARCHAR(255),
  workItemId         INT8    NOT NULL,
  taskType           VARCHAR(255),
  OPTLOCK            INT4,
  taskInitiator_id   VARCHAR(255),
  actualOwner_id     VARCHAR(255),
  createdBy_id       VARCHAR(255),
  PRIMARY KEY (TaskId)
);

CREATE TABLE TaskDef (
  TaskDefId INT8 NOT NULL,
  name      VARCHAR(255),
  priority  INT4 NOT NULL,
  PRIMARY KEY (TaskDefId)
);

CREATE TABLE TaskEvent (
  id      INT8 NOT NULL,
  logTime TIMESTAMP,
  taskId  INT8,
  type    VARCHAR(255),
  userId  VARCHAR(255),
  PRIMARY KEY (id)
);

CREATE TABLE VariableInstanceLog (
  id                 INT8 NOT NULL,
  log_date           TIMESTAMP,
  externalId         VARCHAR(255),
  oldValue           VARCHAR(255),
  processId          VARCHAR(255),
  processInstanceId  INT8 NOT NULL,
  value              VARCHAR(255),
  variableId         VARCHAR(255),
  variableInstanceId VARCHAR(255),
  PRIMARY KEY (id)
);

CREATE TABLE WorkItemInfo (
  workItemId        INT8 NOT NULL,
  creationDate      TIMESTAMP,
  name              VARCHAR(255),
  processInstanceId INT8 NOT NULL,
  state             INT8 NOT NULL,
  OPTLOCK           INT4,
  workItemByteArray OID,
--   workItemByteArray BYTEA,
  PRIMARY KEY (workItemId)
);

CREATE TABLE email_header (
  id             INT8 NOT NULL,
  body           TEXT,
  fromAddress    VARCHAR(255),
  language       VARCHAR(255),
  replyToAddress VARCHAR(255),
  subject        VARCHAR(255),
  PRIMARY KEY (id)
);

CREATE TABLE task_comment (
  id                   INT8 NOT NULL,
  addedAt              TIMESTAMP,
  text                 TEXT,
  addedBy_id           VARCHAR(255),
  TaskData_Comments_Id INT8,
  PRIMARY KEY (id)
);

ALTER TABLE Attachment
ADD CONSTRAINT FK1C93543D937BFB5
FOREIGN KEY (attachedBy_id)
REFERENCES OrganizationalEntity;

ALTER TABLE Attachment
ADD CONSTRAINT FK1C9354333CA892A
FOREIGN KEY (TaskData_Attachments_Id)
REFERENCES Task;

ALTER TABLE BooleanExpression
ADD CONSTRAINT FKE3D208C06C97C90E
FOREIGN KEY (Escalation_Constraints_Id)
REFERENCES Escalation;

ALTER TABLE CorrelationPropertyInfo
ADD CONSTRAINT FK761452A5D87156ED
FOREIGN KEY (correlationKey_keyId)
REFERENCES CorrelationKeyInfo;

ALTER TABLE Deadline
ADD CONSTRAINT FK21DF3E78A9FE0EF4
FOREIGN KEY (Deadlines_StartDeadLine_Id)
REFERENCES Task;

ALTER TABLE Deadline
ADD CONSTRAINT FK21DF3E78695E4DDB
FOREIGN KEY (Deadlines_EndDeadLine_Id)
REFERENCES Task;

ALTER TABLE Delegation_delegates
ADD CONSTRAINT FK47485D5772B3A123
FOREIGN KEY (entity_id)
REFERENCES OrganizationalEntity;

ALTER TABLE Delegation_delegates
ADD CONSTRAINT FK47485D57786553A5
FOREIGN KEY (task_id)
REFERENCES Task;

ALTER TABLE ErrorInfo
ADD CONSTRAINT FK8B1186B6724A467
FOREIGN KEY (REQUEST_ID)
REFERENCES RequestInfo;

ALTER TABLE Escalation
ADD CONSTRAINT FK67B2C6B5D1E5CC1
FOREIGN KEY (Deadline_Escalation_Id)
REFERENCES Deadline;

ALTER TABLE EventTypes
ADD CONSTRAINT FKB0E5621F7665489A
FOREIGN KEY (InstanceId)
REFERENCES ProcessInstanceInfo;

ALTER TABLE I18NText
ADD CONSTRAINT FK2349686BF4ACCD69
FOREIGN KEY (Task_Subjects_Id)
REFERENCES Task;

ALTER TABLE I18NText
ADD CONSTRAINT FK2349686B424B187C
FOREIGN KEY (Task_Names_Id)
REFERENCES Task;

ALTER TABLE I18NText
ADD CONSTRAINT FK2349686BAB648139
FOREIGN KEY (Task_Descriptions_Id)
REFERENCES Task;

ALTER TABLE I18NText
ADD CONSTRAINT FK2349686BB340A2AA
FOREIGN KEY (Reassignment_Documentation_Id)
REFERENCES Reassignment;

ALTER TABLE I18NText
ADD CONSTRAINT FK2349686BF0CDED35
FOREIGN KEY (Notification_Subjects_Id)
REFERENCES Notification;

ALTER TABLE I18NText
ADD CONSTRAINT FK2349686BCC03ED3C
FOREIGN KEY (Notification_Names_Id)
REFERENCES Notification;

ALTER TABLE I18NText
ADD CONSTRAINT FK2349686B77C1C08A
FOREIGN KEY (Notification_Documentation_Id)
REFERENCES Notification;

ALTER TABLE I18NText
ADD CONSTRAINT FK2349686B18DDFE05
FOREIGN KEY (Notification_Descriptions_Id)
REFERENCES Notification;

ALTER TABLE I18NText
ADD CONSTRAINT FK2349686B78AF072A
FOREIGN KEY (Deadline_Documentation_Id)
REFERENCES Deadline;

ALTER TABLE Notification
ADD CONSTRAINT FK2D45DD0BC0C0F29C
FOREIGN KEY (Escalation_Notifications_Id)
REFERENCES Escalation;

ALTER TABLE Notification_BAs
ADD CONSTRAINT FK2DD68EE072B3A123
FOREIGN KEY (entity_id)
REFERENCES OrganizationalEntity;

ALTER TABLE Notification_BAs
ADD CONSTRAINT FK2DD68EE093F2090B
FOREIGN KEY (task_id)
REFERENCES Notification;

ALTER TABLE Notification_Recipients
ADD CONSTRAINT FK98FD214E72B3A123
FOREIGN KEY (entity_id)
REFERENCES OrganizationalEntity;

ALTER TABLE Notification_Recipients
ADD CONSTRAINT FK98FD214E93F2090B
FOREIGN KEY (task_id)
REFERENCES Notification;

ALTER TABLE Notification_email_header
ADD CONSTRAINT UK_F30FE3446CEA0510 UNIQUE (emailHeaders_id);

ALTER TABLE Notification_email_header
ADD CONSTRAINT FKF30FE3448BED1339
FOREIGN KEY (emailHeaders_id)
REFERENCES email_header;

ALTER TABLE Notification_email_header
ADD CONSTRAINT FKF30FE344DD2D7416
FOREIGN KEY (Notification_NotificationId)
REFERENCES Notification;

ALTER TABLE PeopleAssignments_BAs
ADD CONSTRAINT FK9D8CF4EC72B3A123
FOREIGN KEY (entity_id)
REFERENCES OrganizationalEntity;

ALTER TABLE PeopleAssignments_BAs
ADD CONSTRAINT FK9D8CF4EC786553A5
FOREIGN KEY (task_id)
REFERENCES Task;

ALTER TABLE PeopleAssignments_ExclOwners
ADD CONSTRAINT FKC77B97E472B3A123
FOREIGN KEY (entity_id)
REFERENCES OrganizationalEntity;

ALTER TABLE PeopleAssignments_ExclOwners
ADD CONSTRAINT FKC77B97E4786553A5
FOREIGN KEY (task_id)
REFERENCES Task;

ALTER TABLE PeopleAssignments_PotOwners
ADD CONSTRAINT FK1EE418D72B3A123
FOREIGN KEY (entity_id)
REFERENCES OrganizationalEntity;

ALTER TABLE PeopleAssignments_PotOwners
ADD CONSTRAINT FK1EE418D786553A5
FOREIGN KEY (task_id)
REFERENCES Task;

ALTER TABLE PeopleAssignments_Recipients
ADD CONSTRAINT FKC6F615C272B3A123
FOREIGN KEY (entity_id)
REFERENCES OrganizationalEntity;

ALTER TABLE PeopleAssignments_Recipients
ADD CONSTRAINT FKC6F615C2786553A5
FOREIGN KEY (task_id)
REFERENCES Task;

ALTER TABLE PeopleAssignments_Stakeholders
ADD CONSTRAINT FK482F79D572B3A123
FOREIGN KEY (entity_id)
REFERENCES OrganizationalEntity;

ALTER TABLE PeopleAssignments_Stakeholders
ADD CONSTRAINT FK482F79D5786553A5
FOREIGN KEY (task_id)
REFERENCES Task;

ALTER TABLE Reassignment
ADD CONSTRAINT FK724D056062A1E871
FOREIGN KEY (Escalation_Reassignments_Id)
REFERENCES Escalation;

ALTER TABLE Reassignment_potentialOwners
ADD CONSTRAINT FK90B59CFF72B3A123
FOREIGN KEY (entity_id)
REFERENCES OrganizationalEntity;

ALTER TABLE Reassignment_potentialOwners
ADD CONSTRAINT FK90B59CFF35D2FEE0
FOREIGN KEY (task_id)
REFERENCES Reassignment;

ALTER TABLE Task
ADD CONSTRAINT FK27A9A53C55C806
FOREIGN KEY (taskInitiator_id)
REFERENCES OrganizationalEntity;

ALTER TABLE Task
ADD CONSTRAINT FK27A9A5B723BE8B
FOREIGN KEY (actualOwner_id)
REFERENCES OrganizationalEntity;

ALTER TABLE Task
ADD CONSTRAINT FK27A9A55427E8F1
FOREIGN KEY (createdBy_id)
REFERENCES OrganizationalEntity;

ALTER TABLE task_comment
ADD CONSTRAINT FK61F475A57A3215D9
FOREIGN KEY (addedBy_id)
REFERENCES OrganizationalEntity;

ALTER TABLE task_comment
ADD CONSTRAINT FK61F475A5F510CB46
FOREIGN KEY (TaskData_Comments_Id)
REFERENCES Task;

CREATE SEQUENCE ATTACHMENT_ID_SEQ;

CREATE SEQUENCE BAM_TASK_ID_SEQ;

CREATE SEQUENCE BOOLEANEXPR_ID_SEQ;

CREATE SEQUENCE COMMENT_ID_SEQ;

CREATE SEQUENCE CONTENT_ID_SEQ;

CREATE SEQUENCE CONTEXT_MAPPING_INFO_ID_SEQ;

CREATE SEQUENCE CORRELATION_KEY_ID_SEQ;

CREATE SEQUENCE CORRELATION_PROP_ID_SEQ;

CREATE SEQUENCE DEADLINE_ID_SEQ;

CREATE SEQUENCE EMAILNOTIFHEAD_ID_SEQ;

CREATE SEQUENCE ERROR_INFO_ID_SEQ;

CREATE SEQUENCE ESCALATION_ID_SEQ;

CREATE SEQUENCE I18NTEXT_ID_SEQ;

CREATE SEQUENCE NODE_INST_LOG_ID_SEQ;

CREATE SEQUENCE NOTIFICATION_ID_SEQ;

CREATE SEQUENCE PROCESS_INSTANCE_INFO_ID_SEQ;

CREATE SEQUENCE PROC_INST_LOG_ID_SEQ;

CREATE SEQUENCE REASSIGNMENT_ID_SEQ;

CREATE SEQUENCE REQUEST_INFO_ID_SEQ;

CREATE SEQUENCE SESSIONINFO_ID_SEQ;

CREATE SEQUENCE TASK_DEF_ID_SEQ;

CREATE SEQUENCE TASK_EVENT_ID_SEQ;

CREATE SEQUENCE TASK_ID_SEQ;

CREATE SEQUENCE VAR_INST_LOG_ID_SEQ;

CREATE SEQUENCE WORKITEMINFO_ID_SEQ;
