CREATE TABLE jiveForum (
  forumID               INTEGER NOT NULL,
  name                  VARCHAR(100) NOT NULL,
  description           BLOB SUB_TYPE TEXT,
  modDefaultThreadVal   INTEGER NOT NULL,
  modMinThreadVal       INTEGER NOT NULL,
  modDefaultMsgVal      INTEGER NOT NULL,
  modMinMsgVal          INTEGER NOT NULL,
  modifiedDate          VARCHAR(15) NOT NULL,
  creationDate          VARCHAR(15) NOT NULL,
  CONSTRAINT PK_JIVEFORUM PRIMARY KEY (forumID)
);

CREATE UNIQUE INDEX jiveForum_name_idx ON jiveForum (name);


CREATE TABLE jiveForumProp (
  forumID       INTEGER NOT NULL,
  name          VARCHAR(100) NOT NULL,
  propValue     BLOB SUB_TYPE TEXT NOT NULL,
  CONSTRAINT PK_JIVEFORUMPROP PRIMARY KEY (forumID, name)
);


CREATE TABLE jiveThread (
  threadID          INTEGER NOT NULL,
  forumID           INTEGER NOT NULL,
  rootMessageID     INTEGER NOT NULL,
  modValue          INTEGER NOT NULL,
  rewardPoints      INT NOT NULL,
  creationDate      VARCHAR(15) NOT NULL,
  modifiedDate      VARCHAR(15) NOT NULL,
  CONSTRAINT PK_JIVETHREAD PRIMARY KEY (threadID)
);
CREATE INDEX jiveThread_forumID_idx ON jiveThread (forumID);
CREATE INDEX jiveThread_modValue_idx ON jiveThread (modValue);
CREATE INDEX jiveThread_cDate_idx   ON jiveThread (creationDate);
CREATE INDEX jiveThread_mDate_idx   ON jiveThread (modifiedDate);


CREATE TABLE jiveThreadProp (
  threadID      INTEGER NOT NULL,
  name          VARCHAR(100) NOT NULL,
  propValue     BLOB SUB_TYPE TEXT NOT NULL,
  CONSTRAINT PK_JIVETHREADPROP PRIMARY KEY (threadID,name)
);


CREATE TABLE jiveMessage (
  messageID             INTEGER NOT NULL,
  parentMessageID       INTEGER,
  threadID              INTEGER NOT NULL,
  forumID               INTEGER NOT NULL,
  userID                INTEGER,
  subject               VARCHAR(255),  
  body                  BLOB SUB_TYPE TEXT,
  modValue              INTEGER NOT NULL,
  rewardPoints          INTEGER NOT NULL,
  creationDate          VARCHAR(15) NOT NULL,
  modifiedDate          VARCHAR(15) NOT NULL,
  CONSTRAINT PK_JIVEMESSAGE PRIMARY KEY (messageID)
);
CREATE INDEX jiveMessage_threadID_idx ON jiveMessage (threadID);
CREATE INDEX jiveMessage_forumID_idx  ON jiveMessage (forumID);
CREATE INDEX jiveMessage_userID_idx   ON jiveMessage (userID);
CREATE INDEX jiveMessage_modValue_idx ON jiveMessage (modValue);
CREATE INDEX jiveMessage_cDate_idx    ON jiveMessage (creationDate);
CREATE INDEX jiveMessage_mDate_idx    ON jiveMessage (modifiedDate);


CREATE TABLE jiveMessageProp (
  messageID    INTEGER NOT NULL,
  name         VARCHAR(100) NOT NULL,
  propValue    BLOB SUB_TYPE TEXT NOT NULL,
  CONSTRAINT PK_JIVEMESSAGEPROP PRIMARY KEY (messageID, name)
);


CREATE TABLE jiveUser (
  userID        INTEGER NOT NULL,
  username      VARCHAR(30) NOT NULL,
  passwordHash  VARCHAR(32) NOT NULL,   
  name          VARCHAR(100),  
  nameVisible   INTEGER NOT NULL,
  email         VARCHAR(100) NOT NULL,
  emailVisible  INTEGER NOT NULL,
  rewardPoints  INTEGER NOT NULL,
  creationDate  VARCHAR(15) NOT NULL,
  modifiedDate  VARCHAR(15) NOT NULL,
  CONSTRAINT PK_JIVEUSER PRIMARY KEY (userID)
);
CREATE UNIQUE INDEX jiveUser_username_idx ON jiveUser (username);
CREATE INDEX jiveUser_cDate_idx    ON jiveUser (creationDate);


CREATE TABLE jiveUserPerm (
  forumID      INTEGER,
  userID       INTEGER,
  userType     INTEGER NOT NULL,
  permission   INTEGER NOT NULL
);
CREATE INDEX jiveUserPerm_forumID_idx ON jiveUserPerm (forumID);
CREATE INDEX jiveUserPerm_userID_idx  ON jiveUserPerm (userID);


CREATE TABLE jiveUserProp (
  userID        INTEGER NOT NULL,
  name          VARCHAR(100) NOT NULL,
  propValue     BLOB SUB_TYPE TEXT NOT NULL,
  CONSTRAINT PK_JIVEUSERPROP PRIMARY KEY (userID, name)
);


CREATE TABLE jiveGroup (
  groupID       INTEGER NOT NULL,
  name          VARCHAR(50) NOT NULL,
  description   VARCHAR(255),
  creationDate  VARCHAR(15) NOT NULL,
  modifiedDate  VARCHAR(15) NOT NULL,
  CONSTRAINT PK_JIVEGROUP PRIMARY KEY (groupID)
);
CREATE INDEX jiveGroup_name_idx  ON jiveGroup (name);
CREATE INDEX jiveGroup_cDate_idx ON jiveGroup (creationDate);


CREATE TABLE jiveGroupPerm (
  forumID      INTEGER NOT NULL,
  groupID      INTEGER NOT NULL,
  permission   INTEGER NOT NULL
);
CREATE INDEX jiveGroupPerm_forumID_idx ON jiveGroupPerm (forumID);
CREATE INDEX jiveGroupPerm_groupID_idx ON jiveGroupPerm (groupID);


CREATE TABLE jiveGroupProp (
  groupID       INTEGER NOT NULL,
  name          VARCHAR(100) NOT NULL,
  propValue     BLOB SUB_TYPE TEXT NOT NULL,
  CONSTRAINT PK_JIVEGROUPPROP PRIMARY KEY (groupID, name)
);


CREATE TABLE jiveGroupUser (
  groupID        INTEGER NOT NULL,
  userID         INTEGER NOT NULL,
  administrator  INTEGER NOT NULL,
  CONSTRAINT PK_JIVEGROUPUSER PRIMARY KEY (groupID, userID)
);


CREATE TABLE jiveID (
  idType        INTEGER NOT NULL,
  id            INTEGER NOT NULL,
  CONSTRAINT PK_JIVEID PRIMARY KEY (idType)
);


CREATE TABLE jiveModeration (
  objectID    INTEGER NOT NULL,
  objectType  INTEGER NOT NULL,
  userID      INTEGER,
  modDate     VARCHAR(15) NOT NULL,
  modValue    INTEGER NOT NULL
);
CREATE INDEX jiveModeration_objectID_idx ON jiveModeration (objectID);
CREATE INDEX jiveModeration_objectType_idx on jiveModeration (objectType);
CREATE INDEX jiveModeration_userID_idx ON jiveModeration (userID);


CREATE TABLE jiveWatch (
  userID            INTEGER NOT NULL,
  forumID           INTEGER NOT NULL,
  threadID          INTEGER NOT NULL,
  watchType         INTEGER NOT NULL,
  expirable         INTEGER NOT NULL,
  CONSTRAINT jiveWatch_pk PRIMARY KEY (userID, threadID, watchType)  
);
CREATE ASC INDEX jiveWatch_userID_idx ON jiveWatch (userID);
CREATE ASC INDEX jiveWatch_forumID_idx ON jiveWatch (forumID);
CREATE ASC INDEX jiveWatch_threadID_idx ON jiveWatch (threadID);
CREATE INDEX jiveWatch_type_idx ON jiveWatch (watchType);


CREATE TABLE jiveReward (
  userID          INTEGER NOT NULL,
  creationDate    VARCHAR(15) NOT NULL,
  rewardPoints    INTEGER NOT NULL,
  messageID       INTEGER,
  threadID        INTEGER
);
CREATE ASC INDEX jiveReward_userID_idx ON jiveReward (userID);
CREATE ASC INDEX jiveReward_creationDate_idx ON jiveReward (creationDate);
CREATE ASC INDEX jiveReward_messageID_idx ON jiveReward (messageID);
CREATE ASC INDEX jiveReward_threadID_idx ON jiveReward (threadID);

ALTER TABLE jiveForumProp ADD CONSTRAINT jiveForumProp_forumID_fk FOREIGN KEY (forumID) REFERENCES jiveForum;

ALTER TABLE jiveThread ADD CONSTRAINT jiveThread_forumID_fk FOREIGN KEY (forumID) REFERENCES jiveForum;

ALTER TABLE jiveThreadProp ADD CONSTRAINT jiveThreadProp_threadID_fk FOREIGN KEY (threadID) REFERENCES jiveThread;

ALTER TABLE jiveMessage ADD CONSTRAINT jiveMessage_parentMsgID_fk FOREIGN KEY (parentMessageID) REFERENCES jiveMessage(messageID);
ALTER TABLE jiveMessage ADD CONSTRAINT jiveMessage_threadID_fk FOREIGN KEY (threadID) REFERENCES jiveThread;
ALTER TABLE jiveMessage ADD CONSTRAINT jiveMessage_forumID_fk FOREIGN KEY (forumID) REFERENCES jiveForum;
ALTER TABLE jiveMessage ADD CONSTRAINT jiveMessage_userID_fk FOREIGN KEY (userID) REFERENCES jiveUser;

ALTER TABLE jiveMessageProp ADD CONSTRAINT jiveMessageProp_msgID_fk FOREIGN KEY (messageID) REFERENCES jiveMessage;

ALTER TABLE jiveUserPerm ADD CONSTRAINT jiveUserPerm_forumID_fk FOREIGN KEY (forumID) REFERENCES jiveForum;
ALTER TABLE jiveUserPerm ADD CONSTRAINT jiveUserPerm_userID_fk FOREIGN KEY (userID) REFERENCES jiveUser;

ALTER TABLE jiveUserProp ADD CONSTRAINT jiveUserProp_userID_fk FOREIGN KEY (userID) REFERENCES jiveUser;

ALTER TABLE jiveGroupPerm ADD CONSTRAINT jiveGroupPerm_forumID_fk FOREIGN KEY (forumID) REFERENCES jiveForum;
ALTER TABLE jiveGroupPerm ADD CONSTRAINT jiveGroupPerm_groupID_fk FOREIGN KEY (groupID) REFERENCES jiveGroup;

ALTER TABLE jiveGroupProp ADD CONSTRAINT jiveGroupProp_groupID_fk FOREIGN KEY (groupID) REFERENCES jiveGroup;

ALTER TABLE jiveGroupUser ADD CONSTRAINT jiveGroupUser_groupID_fk FOREIGN KEY (groupID) REFERENCES jiveGroup;
ALTER TABLE jiveGroupUser ADD CONSTRAINT jiveGroupUser_userID_fk FOREIGN KEY (userID) REFERENCES jiveUser;

ALTER TABLE jiveWatch ADD CONSTRAINT jiveWatch_userID_fk FOREIGN KEY (userID) REFERENCES jiveUser;
ALTER TABLE jiveWatch ADD CONSTRAINT jiveWatch_threadID_fk FOREIGN KEY (threadID) REFERENCES jiveThread;

ALTER TABLE jiveReward ADD CONSTRAINT jiveReward_userID_fk FOREIGN KEY (userID) REFERENCES jiveUser;


insert into jiveID values (0, 1);
insert into jiveID values (1, 1);
insert into jiveID values (2, 1);
insert into jiveID values (3, 2);
insert into jiveID values (4, 1);


insert into jiveUser (userID,name,username,passwordHash,email,emailVisible,nameVisible,creationDate,modifiedDate,rewardPoints) values (1,'Administrator','admin','21232f297a57a5a743894a0e4a801fc3','admin@yoursite.com',1,1,'0','0',0);
insert into jiveUserPerm(forumID,userID,userType,permission) values (NULL,1,3,1);