/**
  * Database script upgrading from onet forums 4.1 to 5.0
  * MYSQL script
  * Creation Date:2007/1/25
  */
DROP INDEX onet_ix_fome_th ON onetForumMessage;
DROP INDEX onet_ix_fome_fxc ON onetForumMessage; 

CREATE INDEX onet_ix_fome_th ON onetForumMessage (threadID DESC);
CREATE INDEX onet_ix_fome_fxc ON onetForumMessage (threadID,modValue,delValue,key_id);
CREATE INDEX onet_ix_fome_fxh ON onetForumMessage (threadID,userID,modValue,delValue,creationTime ASC);

ALTER TABLE onetForumThread ADD COLUMN pollValue INTEGER NOT NULL DEFAULT 0 AFTER delValue;
ALTER TABLE onetForumThread ADD COLUMN pointType INTEGER NOT NULL DEFAULT 0 AFTER viewCount;
ALTER TABLE onetForumThread ADD COLUMN pointValue INTEGER NOT NULL DEFAULT 0 AFTER pointType;
CREATE INDEX onet_ix_foth_fxf ON onetForumThread (pointType,pointValue);

CREATE TABLE onetRate(
    messageID BIGINT NOT NULL,
    forumID BIGINT NOT NULL,
    rateType INTEGER NOT NULL,
    rateCount INTEGER NOT NULL,
    CONSTRAINT onet_pk_rate PRIMARY KEY(messageID,rateType)
);
CREATE INDEX onet_ix_mid ON onetRate(messageID);

CREATE TABLE onetRateUser(
    messageID BIGINT NOT NULL,
    userID BIGINT NOT NULL,
    CONSTRAINT onet_pk_raua PRIMARY KEY(messageID,userID)
);
CREATE INDEX onet_ix_raus ON onetRateUser(messageID);

CREATE TABLE onetForumThreadCom (
	key_id BIGINT NOT NULL,
	forumID BIGINT NOT NULL,
	modValue INTEGER NOT NULL,
	commendCount INTEGER NOT NULL,
	firstTime BIGINT NOT NULL,
	lastTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_fotc PRIMARY KEY(key_id)
);
CREATE INDEX onet_ix_ftc_fxa ON onetForumThreadCom(forumID,modValue,lastTime);

CREATE TABLE onetForumThreadComx (
	threadID BIGINT NOT NULL,
	objectID BIGINT NOT NULL,
	objectType INTEGER NOT NULL,
	comxType INTEGER NOT NULL,
	showValue INTEGER NOT NULL,
	lastOrdered BIGINT NOT NULL,
	CONSTRAINT onet_pk_fotc PRIMARY KEY(threadID,objectID,objectType,comxType)
);

CREATE TABLE onetLayoutProp (
	objectID BIGINT NOT NULL,
	propName VARCHAR (150) NOT NULL,
	propValue TEXT NOT NULL,
	CONSTRAINT onet_pk_lppr PRIMARY KEY (objectID,propName)
);

UPDATE onetForumThread SET pollValue=1 WHERE onetForumThread.key_id IN (SELECT key_id FROM onetPoll);

DELETE FROM onetStyleProp;
DELETE FROM onetStyle;
DELETE FROM onetSkinProp;
DELETE FROM onetSkin;
DELETE FROM onetLayout;
DELETE FROM onetProperty WHERE propName = 'system.skins.initialized';

DELETE FROM onetProperty WHERE propName LIKE 'plugin%';
DELETE FROM onetPluginProp;
DELETE FROM onetProperty WHERE propName LIKE 'advertise%';