-- //////////////////////////////
-- // $RCSfile$
-- // $Revision: 16342 $
-- // $Date: 2005-06-10 13:19:18 -0700 (Fri, 10 Jun 2005) $
-- //////////////////////////////

-- Add new tables

CREATE TABLE jiveAnnounce (
  announcementID        BIGINT          NOT NULL,
  objectType            INTEGER         NOT NULL,
  objectID              BIGINT          NULL,
  userID                BIGINT          NOT NULL,
  subject               VARCHAR(255)    NOT NULL,
  body                  TEXT            NOT NULL,
  startDate             CHAR(15)        NOT NULL,
  endDate               CHAR(15),
  CONSTRAINT jiveAnnounce_pk PRIMARY KEY (announcementID)
);
CREATE INDEX jiveAnnounce_sDate_idx ON jiveAnnounce (startDate);
CREATE INDEX jiveAnnounce_eDate_idx ON jiveAnnounce (endDate);
CREATE INDEX jiveAnnounce_container_idx ON jiveAnnounce (objectType, objectID);
CREATE INDEX jiveAnnounce_user_idx ON jiveAnnounce (userID);


CREATE TABLE jiveAnnounceProp (
  announcementID        BIGINT          NOT NULL,
  name                  VARCHAR(100)    NOT NULL,
  propValue             TEXT            NOT NULL,
  CONSTRAINT jiveAnnounceProp_pk PRIMARY KEY (announcementID, name)
);

CREATE TABLE jivePMessage (
  pMessageID            BIGINT          NOT NULL,
  ownerID               BIGINT          NOT NULL,
  senderID              BIGINT          NULL,
  recipientID           BIGINT          NULL,
  subject               VARCHAR(255)    NULL,
  body                  TEXT            NULL,
  readStatus            INTEGER         NOT NULL,
  folderID              BIGINT          NOT NULL,
  pMessageDate          CHAR(15)        NOT NULL,
  CONSTRAINT jivePMessage_pmID_pk PRIMARY KEY (pMessageID)
);
CREATE INDEX jivePMessage_rID_idx ON jivePMessage (recipientID);


CREATE TABLE jivePMessageProp (
  pMessageID            BIGINT          NOT NULL,
  name                  VARCHAR(100)    NOT NULL,
  propValue             TEXT            NOT NULL,
  CONSTRAINT jivePMP_pmID_name_idx PRIMARY KEY (pMessageID, name)
);


CREATE TABLE jivePMessageFldr (
  folderID              BIGINT          NOT NULL,
  userID                BIGINT          NOT NULL,
  name                  VARCHAR(255)    NOT NULL,
  CONSTRAINT jivePF_pk PRIMARY KEY (folderID, userID)
);


CREATE TABLE jiveBatchWatch (
  userID                BIGINT          NOT NULL,
  frequency             VARCHAR(50)     NOT NULL,
  prevEmailDate         CHAR(15)        NULL,
  CONSTRAINT jiveBatchWatch_pk PRIMARY KEY (userID)
);
CREATE INDEX jiveBatchWatch_userID_idx ON jiveBatchWatch (userID);


CREATE TABLE jiveRatingType (
  score                 INTEGER        NOT NULL,
  description           VARCHAR(255)   NOT NULL,
  CONSTRAINT jiveRatingType_pk PRIMARY KEY (score)
);


CREATE TABLE jiveRating (
  objectType            INTEGER         NOT NULL,
  objectID              BIGINT          NOT NULL,  
  userID                BIGINT          NULL,
  score                 INTEGER         NOT NULL
);
CREATE INDEX jiveRating_userID_idx ON jiveRating (userID);
CREATE INDEX jiveRating_oID_oType_idx ON jiveRating (objectID, objectType);


CREATE TABLE jivePoll (
  pollID                BIGINT          NOT NULL,
  objectType            INTEGER         NOT NULL,
  objectID              BIGINT          NOT NULL,
  userID                BIGINT          NOT NULL,
  name                  VARCHAR(255)    NOT NULL,
  description           VARCHAR(4000)   NULL,
  pollMode              INTEGER         NOT NULL,
  creationDate          CHAR(15)        NOT NULL,
  modificationDate      CHAR(15)        NOT NULL,
  startDate             CHAR(15)        NOT NULL,
  endDate               CHAR(15)        NOT NULL,
  expireDate            CHAR(15)        NOT NULL,
  CONSTRAINT jivePoll_pk PRIMARY KEY (pollID)
);
CREATE INDEX jivePoll_oID_oType_idx ON jivePoll (objectID, objectType);


CREATE TABLE jivePollOption (
  pollID                BIGINT          NOT NULL,
  optionIndex           INTEGER         NOT NULL,
  optionText            VARCHAR(255)    NOT NULL,
  CONSTRAINT jivePollOption_pk PRIMARY KEY (pollID, optionIndex)
);


CREATE TABLE jivePollVote (
  pollID                BIGINT          NOT NULL,
  userID                BIGINT          NULL,
  guestID               VARCHAR(255)    NULL,
  optionIndex           INTEGER         NOT NULL,
  voteDate              CHAR(15)        NOT NULL
);
CREATE INDEX jivePollVote_pollID_idx ON jivePollVote (pollID);


CREATE TABLE jiveSearch (
  searchID              BIGINT          NOT NULL,
  searchType            INTEGER         NOT NULL,
  userID                BIGINT          NULL,
  query                 VARCHAR(1000)   NOT NULL,
  searchDuration        INTEGER         NOT NULL,
  numResults            INTEGER         NOT NULL,
  searchDate            CHAR(15)        NOT NULL,
  CONSTRAINT jiveSearch_pk PRIMARY KEY (searchID)
);
CREATE INDEX jiveSearch_userID_idx ON jiveSearch (userID);
CREATE INDEX jiveSearch_type_idx ON jiveSearch (searchType);


CREATE TABLE jiveSearchCriteria (
  searchID              BIGINT          NOT NULL,
  criteriaName          VARCHAR(100)    NOT NULL,
  criteriaValue         VARCHAR(4000)   NOT NULL
);
CREATE INDEX jiveSearchCrit_sID_idx ON jiveSearchCriteria (searchID);


CREATE TABLE jiveSearchClick (
  searchID              BIGINT          NOT NULL,
  messageID             BIGINT          NOT NULL,
  clickDate             CHAR(15)        NOT NULL,
  CONSTRAINT jiveSearchClick_pk PRIMARY KEY (searchID, messageID, clickDate)
);


CREATE TABLE jiveAttachData (
  attachmentID          BIGINT          NOT NULL,
  attachmentData        BLOB           NOT NULL,
  CONSTRAINT jiveAttachData_pk PRIMARY KEY (attachmentID)
);


CREATE TABLE jiveViewCount (
    objectType      INTEGER   NOT NULL,
    objectID        BIGINT    NOT NULL,
    parentObjectID  BIGINT    NULL,
    viewCount       INTEGER    DEFAULT 0,
    CONSTRAINT jiveViewCount_pk PRIMARY KEY (objectType, objectID)
);
CREATE INDEX jiveViewCount_vc_idx ON jiveViewCount (viewCount);
CREATE INDEX jiveViewCount_po_idx ON jiveViewCount (parentObjectID);


CREATE TABLE jiveReadStat (
    readStatID      BIGINT NOT NULL,
    userID          BIGINT NULL,
    objectType      INTEGER NOT NULL,
    objectID        BIGINT NOT NULL,
    creationDate    CHAR(15) NOT NULL,
    sessionID       BIGINT NOT NULL,
    CONSTRAINT jiveReadStat_pk PRIMARY KEY (readStatID)
);
CREATE INDEX jiveReadStat_cd_idx ON jiveReadStat (creationDate);


CREATE TABLE jiveReadStatSession (
    sessionID       BIGINT NOT NULL,
    visitorID       VARCHAR(32) NULL,
    creationDate    CHAR(15) NOT NULL,
    CONSTRAINT jiveRSS_pk PRIMARY KEY (sessionID)
);
CREATE INDEX jiveRSS_cd_idx ON jiveReadStatSession (creationDate);


CREATE TABLE jiveHTTPReadStatSession (
    referrer        VARCHAR(255) NULL,
    userAgent       VARCHAR(255) NULL,
    IP              VARCHAR(16) NULL,
    country         VARCHAR(4) NULL,
    bytesSent       INTEGER DEFAULT 0,
    sessionID       BIGINT NOT NULL
);
CREATE INDEX jiveHRSS_sID_idx ON jiveHTTPReadStatSession (sessionID);
CREATE INDEX jiveHRSS_bs_idx ON jiveHTTPReadStatSession (bytesSent);


CREATE TABLE jiveNNTPReadStatSession (
    creationDate    CHAR(15) NOT NULL,
    endDate         CHAR(15) NULL,
    bytesReceived   INTEGER DEFAULT 0,
    bytesSent       INTEGER DEFAULT 0,
    IP              VARCHAR(16) NULL,
    country         VARCHAR(4) NULL,
    sessionID       BIGINT NOT NULL
);
CREATE INDEX jiveNRSS_cd_idx ON jiveNNTPReadStatSession (creationDate);
CREATE INDEX jiveNRSS_ed_idx ON jiveNNTPReadStatSession (endDate);


-- add constraints

ALTER TABLE jiveRating ADD CONSTRAINT jiveRating_score_fk FOREIGN KEY (score) REFERENCES jiveRatingType INITIALLY DEFERRED DEFERRABLE;
ALTER TABLE jivePollOption ADD CONSTRAINT jivePollOption_pollID_fk FOREIGN KEY (pollID) REFERENCES jivePoll INITIALLY DEFERRED DEFERRABLE;
ALTER TABLE jivePollVote ADD CONSTRAINT jivePollVote_pollID_fk FOREIGN KEY (pollID) REFERENCES jivePoll INITIALLY DEFERRED DEFERRABLE;
ALTER TABLE jiveSearchCriteria ADD CONSTRAINT jiveSearchCriteria_sID_fk FOREIGN KEY (searchID) REFERENCES jiveSearch INITIALLY DEFERRED DEFERRABLE;
ALTER TABLE jiveSearchClick ADD CONSTRAINT jiveSearchClick_searchID_fk FOREIGN KEY (searchID) REFERENCES jiveSearch INITIALLY DEFERRED DEFERRABLE;


-- add new types

INSERT INTO jiveID (idType, id) VALUES (18, 1);
INSERT INTO jiveID (idType, id) VALUES (19, 1);
INSERT INTO jiveID (idType, id) VALUES (20, 1);
INSERT INTO jiveID (idType, id) VALUES (22, 1);
INSERT INTO jiveID (idType, id) VALUES (23, 1);
INSERT INTO jiveID (idType, id) VALUES (24, 1);
INSERT INTO jiveID (idType, id) VALUES (201, 1);
INSERT INTO jiveID (idType, id) VALUES (202, 1);

-- Add in the new permissionType column to the jiveUserPerm table
ALTER TABLE jiveUserPerm ADD COLUMN permissionType INTEGER;
UPDATE jiveUserPerm SET permissionType = '1';
ALTER TABLE jiveUserPerm ALTER COLUMN permissionType SET NOT NULL;

-- Add in the new permissionType column to the jiveGroupPerm table
ALTER TABLE jiveGroupPerm ADD COLUMN permissionType INTEGER;
UPDATE jiveGroupPerm SET permissionType = '1';
ALTER TABLE jiveGroupPerm ALTER COLUMN permissionType SET NOT NULL;

-- Add forumIndex column to the jiveMessage table
ALTER TABLE jiveMessage ADD COLUMN forumIndex INTEGER;
UPDATE jiveMessage SET forumIndex = '0';
ALTER TABLE jiveMessage ALTER COLUMN forumIndex SET NOT NULL;
ALTER TABLE jiveMessage DROP CONSTRAINT jiveMessage_forumID_idx;
CREATE INDEX jiveMessage_forum_idx ON jiveMessage(forumID, forumIndex);

-- Add forumIndexCounter column to the jiveForum table
ALTER TABLE jiveForum ADD COLUMN forumIndexCounter INTEGER;
UPDATE jiveForum SET forumIndexCounter = '0';
ALTER TABLE jiveForum ALTER COLUMN forumIndexCounter SET NOT NULL;
CREATE INDEX jiveForum_forumIndexCounter_idx ON jiveForum (forumIndexCounter);

-- Add nntpName column to jiveForum - this is a multi-step process since we need to
-- add the column then set unique values in it then add a unique non-null contraint.

ALTER TABLE jiveForum ADD COLUMN nntpName VARCHAR(255) NULL;
UPDATE jiveForum SET nntpName = forumID;
ALTER TABLE jiveForum ALTER COLUMN nntpName SET NOT NULL;
CREATE UNIQUE INDEX nntpUniqueIdx ON jiveForum (nntpName);

-- Drop columns from jiveForum that are no longer used.
ALTER TABLE jiveForum DROP COLUMN modMinThreadVal;
ALTER TABLE jiveForum DROP COLUMN modMinMsgVal;

-- Add index to the jiveWatch table
CREATE INDEX jiveWatch_combo_idx ON jiveWatch (objectType, objectID, watchType);

-- Upgrade the jiveAttachment table

ALTER TABLE jiveAttachment DROP CONSTRAINT jiveAttachment_msgID_fk;
ALTER TABLE jiveAttachment ADD COLUMN objectID INTEGER NULL;
UPDATE jiveAttachment SET objectID = messageID;
ALTER TABLE jiveAttachment DROP COLUMN messageID;
ALTER TABLE jiveAttachment ADD COLUMN objectType INTEGER NULL;
UPDATE jiveAttachment SET objectType=2;
ALTER TABLE jiveAttachment ALTER COLUMN objectID SET NOT NULL;
ALTER TABLE jiveAttachment ALTER COLUMN objectType SET NOT NULL;
CREATE INDEX jiveAttach_object_idx_new ON jiveAttachment (objectType, objectID);

-- Upgrade some property names

UPDATE jiveThreadProp set name='jive.locked' where name='locked';
UPDATE jiveThreadProp set name='jive.archived' where name='archived';