# //////////////////////////////
# // $RCSfile: Jive_pointbase_8.sql,v $
# // $Revision: 1.1.1.1 $
# // $Date: 2002/09/09 13:50:35 $
# //////////////////////////////

CREATE TABLE jiveForum (
  forumID              DECIMAL(19) NOT NULL,
  name                 VARCHAR(255) UNIQUE NOT NULL,
  description          VARCHAR(4000) NULL,
  modDefaultThreadVal  INTEGER NOT NULL,
  modMinThreadVal      INTEGER NOT NULL,
  modDefaultMsgVal     INTEGER NOT NULL,
  modMinMsgVal         INTEGER NOT NULL,
  creationDate         VARCHAR(15) NOT NULL,
  modifiedDate         VARCHAR(15) NOT NULL,
  CONSTRAINT jiveForum_pk PRIMARY KEY (forumID)
);


CREATE TABLE jiveForumProp (
  forumID     DECMIAL(15) NOT NULL,
  name        VARCHAR2(100) NOT NULL,
  propValue   VARCHAR2(4000) NOT NULL,
  CONSTRAINT jiveForumProp_pk PRIMARY KEY (forumID,name)
);


CREATE TABLE jiveThread (
  threadID            DECIMAL(15) NOT NULL,
  forumID             DECIMAL(15) NOT NULL,
  rootMessageID       DECIMAL(15) NOT NULL,
  modValue            INTEGER NOT NULL,
  rewardPoints        INTEGER NOT NULL,
  creationDate        VARCHAR(15) NOT NULL,
  modifiedDate        VARCHAR(15) NOT NULL,
  CONSTRAINT jiveThread_pk 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 ASC);
CREATE INDEX jiveThread_mDate_idx ON jiveThread (modifiedDate DESC);


CREATE TABLE jiveThreadProp (
  threadID      DECIMAL(15) NOT NULL,
  name          VARCHAR(100) NOT NULL,
  propValue     VARCHAR(4000) NOT NULL,
  CONSTRAINT jiveThreadProp_pk PRIMARY KEY (threadID, name)
);


CREATE TABLE jiveMessage (
  messageID         DECIMAL(15) NOT NULL,
  parentMessageID	  DECIMAL(15) NULL,
  threadID          DECIMAL(15) NOT NULL,
  forumID           DECIMAL(15) NOT NULL,
  userID            DECIMAL(15) NULL,
  subject           VARCHAR(255) NULL,  
  body              CLOB(32[K]) NULL,
  modValue          INTEGER NOT NULL,
  rewardPoints      INTEGER NOT NULL,
  creationDate      VARCHAR(15) NOT NULL,
  modifiedDate      VARCHAR(15) NOT NULL,
  CONSTRAINT jiveMessage_pk PRIMARY KEY (messageID)  
);
CREATE INDEX jiveMessage_threadID_idx ON jiveMessage (threadID ASC);
CREATE INDEX jiveMessage_userID_idx ON jiveMessage (userID ASC);
CREATE INDEX jiveMessage_forumId_modVal_idx ON jiveMessage(forumID, modValue);
CREATE INDEX jiveMessage_cDate_idx ON jiveMessage (creationDate ASC);
CREATE INDEX jiveMessage_mDate_idx ON jiveMessage (modifiedDate DESC);


CREATE TABLE jiveMessageProp (
  messageID     DECIMAL(15) NOT NULL,
  name          VARCHAR(100) NOT NULL,
  propValue     VARCHAR(4000) NOT NULL,
  CONSTRAINT jiveMessageProp_pk PRIMARY KEY (messageID, name)
);


CREATE TABLE jiveUser (
  userID        	  DECIMAL(15) NOT NULL,
  username          VARCHAR(30) UNIQUE NOT NULL,
  passwordHash      VARCHAR(32) NOT NULL,
  name              VARCHAR(100) NULL,
  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 jiveUser_pk PRIMARY KEY (userID)
);
CREATE INDEX jiveUser_cDate_idx on jiveUser (creationDate ASC);


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


CREATE TABLE jiveUserProp (
  userID      DECIMAL(15) NOT NULL,
  name        VARCHAR(100) NOT NULL,
  propValue   VARCHAR(4000) NOT NULL,
  CONSTRAINT jiveUserProp_pk PRIMARY KEY (userID, name)
);


CREATE TABLE jiveGroup (
  groupID       DECIMAL(15) NOT NULL,
  name          VARCHAR(100) UNIQUE NOT NULL,
  description   VARCHAR(255) NULL,
  creationDate  VARCHAR(15) NOT NULL,
  modifiedDate  VARCHAR(15) NOT NULL,
  CONSTRAINT jiveGroup_pk PRIMARY KEY (groupID)
);
CREATE INDEX jiveGroup_cDate_idx on jiveGroup (creationDate ASC);


CREATE TABLE jiveGroupPerm (
  forumID       DECIMAL(15) NULL,
  groupID       DECIMAL(15) NOT NULL,
  permission    INTEGER NOT NULL,
  CONSTRAINT jiveGroupPerm_pk PRIMARY KEY (forumID, groupID, permission)
);
CREATE INDEX jiveGroupPerm_groupID_idx ON jiveGroupPerm (groupID ASC);


CREATE TABLE jiveGroupProp (
  groupID     DECIMAL(15) NOT NULL,
  name        VARCHAR(100) NOT NULL,
  propValue   VARCHAR(4000) NOT NULL,
  CONSTRAINT jiveGroupProp_pk PRIMARY KEY (groupID, name)
);


CREATE TABLE jiveGroupUser (
  groupID          DECIMAL(15) NOT NULL,
  userID           DECIMAL(15) NOT NULL,
  administrator    INTEGER NOT NULL,
  CONSTRAINT jiveGroupUser_pk PRIMARY KEY (groupID, userID)
);
CREATE INDEX jiveGroupUser_userID_idx ON jiveGroupUser (userID ASC);


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


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


CREATE TABLE jiveWatch (
  userID            DECIMAL(15) NOT NULL,
  forumID           DECIMAL(15) NOT NULL,
  threadID          DECIMAL(15) 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 ASC);
CREATE INDEX jiveWatch_forumID_idx ON jiveWatch (forumID ASC);
CREATE INDEX jiveWatch_threadID_idx ON jiveWatch (threadID ASC);
CREATE INDEX jiveWatch_type_idx ON jiveWatch (watchType);


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


/// 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);

COMMIT;