-- //////////////////////////////
-- // $RCSfile$
-- // $Revision: 13717 $
-- // $Date: 2005-01-06 12:53:53 -0800 (Thu, 06 Jan 2005) $
-- //////////////////////////////


-- // Create new tables

CREATE TABLE jiveStatusLevel (
	statusLevelID	BIGINT		    NOT NULL,
	name			varchar(255)	NOT NULL,
	description		TEXT         	NULL,
	imagePath		varchar(255)	NOT NULL,
    largeImagePath  VARCHAR(255)    NULL,
	minPoints		INTEGER		    NOT NULL,
	maxPoints		INTEGER		    NOT NULL,
	groupID			BIGINT		    NOT NULL,
	CONSTRAINT jiveStatusLevel_pk PRIMARY KEY (statusLevelID),
	CONSTRAINT jiveStatusLevel_name_unq UNIQUE (name)
);

CREATE TABLE jiveStatusLevelProp (
  statusLevelID     BIGINT          NOT NULL,
  name              VARCHAR(100)    NOT NULL,
  propValue         TEXT            NOT NULL,
  CONSTRAINT jiveStatusLevelProp_pk PRIMARY KEY (statusLevelID,name)
);

CREATE TABLE jiveAvatar (
	avatarID	BIGINT	        	NOT NULL,
	modValue	INTEGER		        NOT NULL,
	ownerID		BIGINT              NULL,
	CONSTRAINT jiveAvatar_pk PRIMARY KEY (avatarID)
);
CREATE INDEX jiveAvatar_ownerID_idx ON jiveAvatar (ownerID);

CREATE TABLE jiveAvatarProp (
	avatarID	BIGINT		        NOT NULL,
    name		VARCHAR(100)	    NOT NULL,
	propValue	TEXT    	        NOT NULL,
	CONSTRAINT	jiveAvatarProp_pk PRIMARY KEY (avatarID, name)
);

CREATE TABLE jiveAvatarUser (
	avatarID	BIGINT		        NOT NULL,
	userID		BIGINT		        NOT NULL,
	CONSTRAINT jiveAvatarUser_pk PRIMARY KEY (avatarID,userID)
);


CREATE TABLE jiveQuestion (
    threadID        BIGINT  NOT NULL,
    forumID         BIGINT  NOT NULL,
    userID          BIGINT  NOT NULL,
    creationDate    BIGINT NOT NULL,
    resolutionDate  BIGINT NULL,
    resolutionState BIGINT NOT NULL,
    CONSTRAINT jiveQuestion_pk PRIMARY KEY (threadID)
);
CREATE INDEX jiveQuestion_cdate_idx ON jiveQuestion (creationDate);
CREATE INDEX jiveQuestion_rdate_idx ON jiveQuestion (resolutionDate);
CREATE INDEX jiveQuestion_state_idx ON jiveQuestion (resolutionState);

CREATE TABLE jiveQuestionProp (
    threadID        BIGINT              NOT NULL,
    name            VARCHAR(100)        NOT NULL,
    propValue       VARCHAR(4000)       NOT NULL,
    CONSTRAINT jiveQuestionProp_pk PRIMARY KEY (threadID, name)
);

CREATE TABLE jiveAnswer (
    threadID       BIGINT NOT NULL,
    messageID      BIGINT NOT NULL,
    forumID        BIGINT NOT NULL,
    userID         BIGINT NULL,
    helpfulAnswer  INTEGER NOT NULL,
    correctAnswer  INTEGER NOT NULL,
    CONSTRAINT jiveAnswer_pk PRIMARY KEY (threadID, messageID)
);

ALTER TABLE jiveStatusLevelProp ADD CONSTRAINT jiveForumProp_statusLevelID_fk FOREIGN KEY (statusLevelID) references jiveStatusLevel INITIALLY DEFERRED DEFERRABLE;
ALTER TABLE jiveAvatarProp ADD CONSTRAINT jiveAvatarProp_avatarID_fk FOREIGN KEY (avatarID) REFERENCES jiveAvatar INITIALLY DEFERRED DEFERRABLE;
ALTER TABLE jiveAvatarUser ADD CONSTRAINT jiveAvatarUser_avatarID_fk FOREIGN KEY (avatarID) REFERENCES jiveAvatar INITIALLY DEFERRED DEFERRABLE;
ALTER TABLE jiveQuestion ADD CONSTRAINT jiveQuestion_threadID_fk FOREIGN KEY (threadID) REFERENCES jiveThread INITIALLY DEFERRED DEFERRABLE;
ALTER TABLE jiveQuestionProp ADD CONSTRAINT jiveQuestionProp_threadID_fk FOREIGN KEY (threadID) REFERENCES jiveThread INITIALLY DEFERRED DEFERRABLE;

-- // Add new type ID's

INSERT INTO jiveID (idType, id) VALUES (25, 1);
INSERT INTO jiveID (idType, id) VALUES (26, 1);