/* ============================================================ */
/* $RCSfile: Jive_sqlserver_7.sql,v $                           */
/* $RevisiON: 1.6 $                                             */
/* $Date: 2002/09/09 13:50:35 $                                 */
/* ============================================================ */

CREATE TABLE jiveForum (
  forumID              INT            NOT NULL,
  name                 VARCHAR(255)   UNIQUE NOT NULL,
  description          TEXT           NULL,
  modDefaultThreadVal  INT            NOT NULL,
  modMinThreadVal      INT            NOT NULL,
  modDefaultMsgVal     INT            NOT NULL,
  modMinMsgVal         INT            NOT NULL,
  modifiedDate         VARCHAR(15)    NOT NULL,
  creationDate         VARCHAR(15)    NOT NULL,
  CONSTRAINT forum_pk PRIMARY KEY (forumID)
);


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


CREATE TABLE jiveThread (
  threadID       INT       NOT NULL,
  forumID        INT       NOT NULL,
  rootMessageID  INT       NOT NULL,
  modValue       INT       NOT NULL,
  rewardPoints   INT       NOT NULL,
  creationDate   CHAR(15)  NOT NULL,
  modifiedDate   CHAR(15)  NOT NULL,
  CONSTRAINT thread_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);
CREATE INDEX jiveThread_mDate_idx    ON jiveThread (modifiedDate);


CREATE TABLE jiveThreadProp (
  threadID      INT            NOT NULL,
  name          VARCHAR(100)   NOT NULL,
  propValue     TEXT           NOT NULL,
  CONSTRAINT jiveThreadProp_pk PRIMARY KEY (threadID, name)
);


CREATE TABLE jiveMessage (
  messageID         INT          NOT NULL,
  parentMessageID   INT          NULL,
  threadID          INT          NOT NULL,
  forumID			INT			 NOT NULL,
  userID            INT          NULL,
  subject           VARCHAR(255) NULL,
  body              TEXT         NULL,
  modValue          INT          NOT NULL,
  rewardPoints      INT          NOT NULL,
  creationDate      CHAR(15)     NOT NULL,
  modifiedDate      CHAR(15)     NOT NULL,
  CONSTRAINT  message_pk PRIMARY KEY (messageID)
);
CREATE INDEX jiveMessage_threadID_idx ON jiveMessage (threadID);
CREATE INDEX jiveMessage_forumID_modVal_idx ON jiveMessage(forumID, modValue);
CREATE INDEX jiveMessage_userID_idx   ON jiveMessage (userID);
CREATE INDEX jiveMessage_cDate_idx    ON jiveMessage (creationDate);
CREATE INDEX jiveMessage_mDate_idx    ON jiveMessage (modifiedDate);


CREATE TABLE jiveMessageProp (
  messageID     INT            NOT NULL,
  name          VARCHAR(100)   NOT NULL,
  propValue     TEXT           NOT NULL,
  CONSTRAINT messagePropPK PRIMARY KEY (messageID, name)
);


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


CREATE TABLE jiveUserPerm (
  forumID       INT            NULL,
  userID        INT            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        INT            NOT NULL,
  name          VARCHAR(100)   NOT NULL,
  propValue     TEXT           NOT NULL,
  CONSTRAINT jiveUserProp_pk PRIMARY KEY (userID, name)
);


CREATE TABLE jiveGroup (
  groupID       INT             NOT NULL,
  name          VARCHAR(100)     NOT NULL,
  description   VARCHAR(255)    NULL,
  modifiedDate  CHAR(15)        NOT NULL,
  creationDate  CHAR(15)        NOT NULL,
  CONSTRAINT group_pk PRIMARY KEY (groupID)
);
CREATE INDEX jiveGroup_cDate_idx ON jiveGroup (creationDate);


CREATE TABLE jiveGroupPerm (
  forumID       INT            NOT NULL,
  groupID       INT            NOT NULL,
  permission    INT            NOT NULL,
  CONSTRAINT groupPerm_pk PRIMARY KEY (forumID, groupID, permission)
);
CREATE INDEX jiveGroupPerm_groupID_idx ON jiveGroupPerm (groupID);


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


CREATE TABLE jiveGroupUser (
  groupID       INT            NOT NULL,
  userID        INT            NOT NULL,
  administrator INT            NOT NULL,
  CONSTRAINT    groupUserPK PRIMARY KEY (groupID, userID)
);
CREATE INDEX jiveGroupUser_userID_idx ON jiveGroupUser (userID);


CREATE TABLE jiveID (
  idType   INT   NOT NULL,
  id       INT   NOT NULL,
  constraint jiveID_pk primary key (idType)
);


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


CREATE TABLE jiveWatch (
  userID       INT   NOT NULL,
  forumID      INT   NOT NULL,
  threadID     INT   NOT NULL,
  watchType    INT   NOT NULL,
  expirable    INT   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          INT          NOT NULL,
  creationDate    VARCHAR(15)  NOT NULL,
  rewardPoints    INT          NOT NULL,
  messageID       INT          NULL,
  threadID        INT          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);


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;

/*
  Note: need to figure out how to make constraints deferrable 
  under SQL Server. Until then, disable certain fk's.
  
  ALTER TABLE jiveThread ADD CONSTRAINT jiveThread_rootMsgID_fk 
  FOREIGN KEY (rootMessageID) REFERENCES jiveMessage(messageID);*/

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;


/* 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,username,passwordHash,name,nameVisible,email,emailVisible,rewardPoints,creationDate,modifiedDate) 
  VALUES (1,'admin','21232f297a57a5a743894a0e4a801fc3','Administrator',1,'admin@yoursite.com',1,0,'0','0');
INSERT INTO jiveUserPerm(forumID,userID,userType,permission) 
  VALUES (NULL,1,3,1);
  
  
/*
 * These are commented out to avoid running them and inadverently
 * trashing our tables.  Hilight within isqlw and press F5 to run.
 *
 * The following constraint was disabled above for the time being:
 * alter table jiveThread	drop constraint jiveThread_rootMsgID_fk ; 
 */
/*
alter table jiveForumProp   drop constraint jiveForumProp_forumID_fk;
alter table jiveThread	    drop constraint jiveThread_forumID_fk ;
alter table jiveThreadProp  drop constraint jiveThreadProp_threadID_fk ;
alter table jiveMessage	    drop constraint jiveMessage_parentMsgID_fk;
alter table jiveMessage	    drop constraint jiveMessage_threadID_fk;
alter table jiveMessage	    drop constraint jiveMessage_forumID_fk;
alter table jiveMessage	    drop constraint jiveMessage_userID_fk;
alter table jiveMessageProp drop constraint jiveMessageProp_msgID_fk;
alter table jiveUserPerm    drop constraint jiveUserPerm_forumID_fk;
alter table jiveUserPerm    drop constraint jiveUserPerm_userID_fk;
alter table jiveUserProp    drop constraint jiveUserProp_userID_fk;
alter table jiveGroupPerm   drop constraint jiveGroupPerm_forumID_fk;
alter table jiveGroupPerm   drop constraint jiveGroupPerm_groupID_fk;
alter table jiveGroupProp   drop constraint jiveGroupProp_groupID_fk;
alter table jiveGroupUser   drop constraint jiveGroupUser_groupID_fk;
alter table jiveGroupUser   drop constraint jiveGroupUser_userID_fk;

drop table jiveForum;
drop table jiveForumProp;
drop table jiveThread;
drop table jiveThreadProp;
drop table jiveMessage;
drop table jiveMessageProp;
drop table jiveUser;
drop table jiveUserPerm;
drop table jiveUserProp;
drop table jiveGroup;
drop table jiveGroupPerm;
drop table jiveGroupProp;
drop table jiveGroupUser;
drop table jiveID;
drop table jiveModeration;
drop table jiveWatch;
drop table jiveReward;
*/