CREATE TABLE jiveForum (
  forumID               INT8 NOT NULL,
  name                  VARCHAR(255) UNIQUE NOT NULL,
  description           TEXT,
  modDefaultThreadVal   INT8 NOT NULL,
  modMinThreadVal       INT8 NOT NULL,
  modDefaultMsgVal      INT8 NOT NULL,
  modMinMsgVal          INT8 NOT NULL,
  modifiedDate          VARCHAR(15) NOT NULL,
  creationDate          VARCHAR(15) NOT NULL,
  CONSTRAINT PK_JIVEFORUM PRIMARY KEY (forumID)
);
CREATE INDEX jiveForum_name_idx ON jiveForum (name);

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

CREATE TABLE jiveThread (
  threadID          INT8 NOT NULL,
  forumID           INT8 NOT NULL,
  rootMessageID     INT8 NOT NULL,
  modValue          INT8 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      INT8 NOT NULL,
  name          VARCHAR(100) NOT NULL,
  propValue     TEXT NOT NULL,
  CONSTRAINT PK_JIVETHREADPROP PRIMARY KEY (threadID,name)
);

CREATE TABLE jiveMessage (
  messageID             INT8 NOT NULL,
  parentMessageID       INT8 NULL,
  threadID              INT8 NOT NULL,
  forumID               INT8 NOT NULL,
  userID                INT8 NULL,
  subject               VARCHAR(255),  
  body                  TEXT,
  modValue              INT8 NOT NULL,
  rewardPoints          INT 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    INT8 NOT NULL,
  name         VARCHAR(100) NOT NULL,
  propValue    TEXT NOT NULL,
  CONSTRAINT PK_JIVEMESSAGEPROP PRIMARY KEY (messageID, name)
);

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

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

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

CREATE TABLE jiveGroup (
  groupID       INT8 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      INT8 NOT NULL,
  groupID      INT8 NOT NULL,
  permission   INT NOT NULL
);
CREATE INDEX jiveGroupPerm_forumID_idx ON jiveGroupPerm (forumID);
CREATE INDEX jiveGroupPerm_groupID_idx ON jiveGroupPerm (groupID);

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

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

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

CREATE TABLE jiveModeration (
  objectID    INT8 NOT NULL,
  objectType  INT8 NOT NULL,
  userID      INT8 NULL,
  modDate     VARCHAR(15) NOT NULL,
  modValue    INT8 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 INDEX jiveWatch_userID_idx ON jiveWatch (userID);
CREATE INDEX jiveWatch_forumID_idx ON jiveWatch (forumID);
CREATE 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 NULL,
  threadID        INTEGER NULL
);
CREATE INDEX jiveReward_userID_idx ON jiveReward (userID);
CREATE INDEX jiveReward_creationDate_idx ON jiveReward (creationDate);
CREATE INDEX jiveReward_messageID_idx ON jiveReward (messageID);
CREATE INDEX jiveReward_threadID_idx ON jiveReward (threadID);


--Foreign key constraints. Note that all are deferrable until the commit of a transaction.

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

ALTER TABLE jiveThread ADD CONSTRAINT jiveThread_forumID_fk FOREIGN KEY (forumID) REFERENCES jiveForum INITIALLY DEFERRED DEFERRABLE;
ALTER TABLE jiveThread ADD CONSTRAINT jiveThread_rootMsgID_fk FOREIGN KEY (rootMessageID) REFERENCES jiveMessage(messageID) INITIALLY DEFERRED DEFERRABLE;

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

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

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

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

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

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

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

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

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

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

-- Finally, insert default table values.

-- Unique ID entry for forum, thread, messages, user, group.
-- The User ID entry starts at 2 (after admin user entry). 
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);

-- Entry for admin user -- password is "admin"
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);