DROP TABLE SYSTEM_DATA;
CREATE TABLE SYSTEM_DATA (
  ID                   BIGINT NOT NULL,
  VERSION              VARCHAR(20) NOT NULL,
  CREATIONDATE         DATE NOT NULL
);

CREATE UNIQUE INDEX SYSTEM_DATA_PK 
ON SYSTEM_DATA (
   ID		       ASC
);

DROP TABLE SEEDS;
CREATE TABLE SEEDS (
  NAME                 VARCHAR(20) NOT NULL,
  SEED                 BIGINT NOT NULL
);

CREATE UNIQUE INDEX SEEDS_PK 
ON SEEDS (
  NAME		       ASC
);

DROP TABLE DESTINATIONS;
CREATE TABLE DESTINATIONS (
  NAME                 VARCHAR(255) NOT NULL,
  ISQUEUE              INT NOT NULL,
  DESTINATIONID        BIGINT NOT NULL
);

CREATE UNIQUE INDEX DESTINATIONS_PK 
ON DESTINATIONS (
  NAME		       ASC
);

DROP TABLE MESSAGES;
CREATE TABLE MESSAGES (
  MESSAGEID            VARCHAR(64) NOT NULL,
  DESTINATIONID        BIGINT NOT NULL,
  PRIORITY             INT,
  CREATETIME           BIGINT NOT NULL,
  EXPIRYTIME           BIGINT,
  PROCESSED            INT,
  MESSAGEBLOB          BLOB(100K) NOT NULL
);

CREATE INDEX MESSAGES_PK 
ON MESSAGES (
  MESSAGEID	       ASC
);

DROP TABLE MESSAGE_HANDLES;
CREATE TABLE MESSAGE_HANDLES (
   MESSAGEID           VARCHAR(64) NOT NULL,
   DESTINATIONID       BIGINT NOT NULL,
   CONSUMERID          BIGINT NOT NULL,
   PRIORITY            INT,
   ACCEPTEDTIME        BIGINT,
   SEQUENCENUMBER      BIGINT,
   EXPIRYTIME          BIGINT,
   DELIVERED           INT
);

CREATE INDEX MESSAGE_HANDLES_PK 
ON MESSAGE_HANDLES (
  DESTINATIONID	       ASC, 
  CONSUMERID	       ASC, 
  MESSAGEID	       ASC
);

DROP TABLE CONSUMERS;
CREATE TABLE CONSUMERS (
  NAME                 VARCHAR(255) NOT NULL,
  DESTINATIONID        BIGINT NOT NULL,
  CONSUMERID           BIGINT NOT NULL,
  CREATED              BIGINT NOT NULL
);

CREATE UNIQUE INDEX CONSUMERS_PK 
ON CONSUMERS (
  NAME		       ASC, 
  DESTINATIONID	       ASC
);

DROP TABLE USERS;
CREATE TABLE USERS (
  USERNAME             VARCHAR(50) NOT NULL,
  PASSWORD             VARCHAR(50) NOT NULL
);

CREATE UNIQUE INDEX USERS_PK
ON USERS (
  USERNAME             ASC
);