/**
  * Database script for onet forums 4.0
  * MYSQL script
  * Creation Date:2006/4/25
  */
CREATE TABLE onetUser (
	key_id BIGINT NOT NULL ,
	username VARCHAR (50) UNIQUE NOT NULL ,
	passwordEncrypted VARCHAR (50) NOT NULL ,
	email VARCHAR (255) NOT NULL ,
	emailVisible INTEGER NOT NULL ,
	activeValue INTEGER NOT NULL ,
	reminderQuestion VARCHAR (255),
	reminderAnswer TEXT ,
	creationTime BIGINT NOT NULL ,
	modificationTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_us PRIMARY KEY(key_id)
);

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

CREATE TABLE onetGroup (
	key_id BIGINT NOT NULL ,
	groupName VARCHAR (150) UNIQUE NOT NULL ,
	description TEXT ,
	creationTime BIGINT NOT NULL ,
	modificationTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_gr PRIMARY KEY(key_id)
);

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

CREATE TABLE onetUserGroup (
	userID BIGINT NOT NULL ,
	groupID BIGINT NOT NULL,
	CONSTRAINT onet_pk_usgr PRIMARY KEY(userID,groupID)
);

CREATE TABLE onetUserPerm (
	key_id BIGINT NOT NULL ,
	objectID BIGINT NOT NULL ,
	objectType INTEGER NOT NULL ,
	userID BIGINT NOT NULL ,
	permission INTEGER NOT NULL,
	CONSTRAINT onet_pk_uspe PRIMARY KEY(key_id)
);
CREATE UNIQUE INDEX onet_uk_uspe ON onetUserPerm (objectType,objectID,userID,permission);

CREATE TABLE onetGroupPerm (
	key_id BIGINT NOT NULL ,
	objectID BIGINT NOT NULL ,
	objectType INTEGER NOT NULL ,
	groupID BIGINT NOT NULL ,
	permission INTEGER NOT NULL,
	CONSTRAINT onet_pk_grpe PRIMARY KEY(key_id)
);
CREATE UNIQUE INDEX onet_uk_grpe ON onetGroupPerm(objectID,objectType,groupID,permission);

CREATE TABLE onetZone (
	key_id BIGINT NOT NULL ,
	parentID BIGINT NOT NULL ,
	zoneName VARCHAR (255) NOT NULL ,
	description TEXT,
	indexValue INTEGER NOT NULL,
	modificationTime BIGINT NOT NULL ,
	creationTime BIGINT NOT NULL ,
	CONSTRAINT onet_pk_zo PRIMARY KEY(key_id)
);
CREATE TABLE onetZoneProp (
	objectID BIGINT NOT NULL ,
	propName VARCHAR (150) NOT NULL ,
	propValue TEXT NOT NULL,
	CONSTRAINT onet_pk_zopr PRIMARY KEY(objectID,propName)
);

CREATE TABLE onetForum (
	key_id BIGINT NOT NULL ,
	zoneID BIGINT NOT NULL ,
	forumName VARCHAR (255) NOT NULL ,
	description TEXT ,
	indexValue INTEGER NOT NULL,
	modificationtime BIGINT NOT NULL ,
	creationTime BIGINT NOT NULL ,
	CONSTRAINT onet_pk_fo PRIMARY KEY(key_id)
);

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

CREATE TABLE onetForumThread (
	key_id BIGINT NOT NULL ,
	forumID BIGINT NOT NULL ,
	categoryID BIGINT NOT NULL ,
	rootID BIGINT NOT NULL ,
	userID BIGINT NOT NULL ,
	modValue INTEGER NOT NULL ,
	delValue INTEGER NOT NULL ,
	msgCount INTEGER NOT NULL ,
	viewCount INTEGER NOT NULL ,
	modificationTime BIGINT NOT NULL ,
	creationTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_fot PRIMARY KEY(key_id)
);

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

CREATE TABLE onetForumMessage (
	key_id BIGINT NOT NULL ,
	parentID BIGINT NOT NULL ,
	threadID BIGINT NOT NULL ,
	forumID BIGINT NOT NULL ,
	userID BIGINT NOT NULL ,
	author VARCHAR(255) NOT NULL ,
	subject VARCHAR (255) NOT NULL ,
	body TEXT ,
	modValue INTEGER NOT NULL ,
	delValue INTEGER NOT NULL ,
	viewCount INTEGER NOT NULL ,
	modificationTime BIGINT NOT NULL ,
	creationTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_fom PRIMARY KEY(key_id)
);

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

CREATE TABLE onetForumThreadExc (
	key_id BIGINT NOT NULL ,
	forumID BIGINT NOT NULL,
	CONSTRAINT onet_pk_fote PRIMARY KEY(key_id)
);
CREATE INDEX onet_ix_fote ON onetForumThreadExc(forumID);

CREATE TABLE onetForumThreadTop (
	key_id BIGINT NOT NULL ,
	objectID BIGINT NOT NULL ,
	objectType INTEGER NOT NULL ,
	creationTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_fott PRIMARY KEY(key_id)
);

CREATE TABLE onetAttachment (
	key_id BIGINT NOT NULL ,
	objectID BIGINT ,
	objectType INTEGER NOT NULL ,
	fileName VARCHAR (255) NOT NULL ,
	fileSize BIGINT NOT NULL ,
	fileSpecs VARCHAR (50) ,
	contentType VARCHAR (50) NOT NULL ,
	creationTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_at PRIMARY KEY(key_id)
);

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

CREATE TABLE onetAttachData (
	attachID BIGINT NOT NULL ,
	attachData LONGBLOB NOT NULL ,
	CONSTRAINT onet_pk_atda PRIMARY KEY(attachID)
);

CREATE TABLE onetForumCategory(
    key_id BIGINT NOT NULL,
    forumID BIGINT NOT NULL,
    cateName VARCHAR (255) NOT NULL,
    cateDescription VARCHAR (255) NOT NULL,
    cateImage VARCHAR(255) NOT NULL,
    indexValue INTEGER NOT NULL,
    creationTime BIGINT NOT NULL,
    CONSTRAINT onet_pk_foc PRIMARY KEY(key_id)
);

CREATE TABLE onetForumAuthorized(
    key_id BIGINT NOT NULL,
    forumID BIGINT NOT NULL,
    paidCount INTEGER NOT NULL,
    lastPaidTime BIGINT NOT NULL,
    creationTime BIGINT NOT NULL,
    CONSTRAINT onet_pk_foa PRIMARY KEY(key_id)
);

CREATE TABLE onetAvatar (
	key_id BIGINT NOT NULL ,
	ownerID BIGINT ,
	CONSTRAINT onet_pk_av PRIMARY KEY(key_id)
);

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

CREATE TABLE onetAvatarUser (
	userID BIGINT NOT NULL ,
	avatarID BIGINT NOT NULL,
	CONSTRAINT onet_pk_avu PRIMARY KEY(userID)
);

CREATE TABLE onetFaction (
	key_id BIGINT NOT NULL ,
	name VARCHAR (255) NOT NULL ,
	description TEXT ,
	CONSTRAINT onet_pk_fac PRIMARY KEY(key_id)
);

CREATE TABLE onetFactionUser (
	factionID BIGINT NOT NULL ,
	userID BIGINT NOT NULL,
	CONSTRAINT onet_pk_faus PRIMARY KEY(factionID,userID)
);

CREATE TABLE onetFavorite (
	key_id BIGINT UNIQUE NOT NULL ,
	userID BIGINT NOT NULL ,
	objectID BIGINT NOT NULL ,
	objectType INTEGER NOT NULL,
	CONSTRAINT onet_pk_fav PRIMARY KEY(userID,objectID,objectType)
);

CREATE TABLE onetFriend (
	key_id BIGINT UNIQUE NOT NULL ,
	userID BIGINT NOT NULL ,
	memberID BIGINT NOT NULL ,
	groupID BIGINT NOT NULL ,
	creationTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_fr PRIMARY KEY(userID,memberID)
);

CREATE TABLE onetFriendGroup (
	key_id BIGINT NOT NULL ,
	userID BIGINT NOT NULL ,
	name VARCHAR (255) NOT NULL,
	CONSTRAINT onet_pk_frg PRIMARY KEY(key_id)
);

CREATE TABLE onetPMessage (
	key_id BIGINT NOT NULL ,
	userID BIGINT NOT NULL ,
	senderID BIGINT NOT NULL ,
	recipientID BIGINT NOT NULL ,
	subject VARCHAR(50) NOT NULL ,
	body TEXT ,
	readValue INTEGER NOT NULL ,
	folderID BIGINT NOT NULL ,
	creationTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_pm PRIMARY KEY(key_id)
);

CREATE TABLE onetPMessageFldr (
	key_id BIGINT NOT NULL ,
	userID BIGINT NOT NULL ,
	name VARCHAR (255) NOT NULL,
	CONSTRAINT onet_pk_pmfl PRIMARY KEY(key_id)
);

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

CREATE TABLE onetPoll (
	key_id BIGINT NOT NULL ,
	pollMode INTEGER NOT NULL ,
	lifeTime INTEGER NOT NULL ,
	creationTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_poll PRIMARY KEY(key_id)
);

CREATE TABLE onetPollItem (
	key_id BIGINT NOT NULL ,
	pollID BIGINT NOT NULL ,
	itemName VARCHAR (255) NOT NULL ,
	voteCount INTEGER NOT NULL,
	CONSTRAINT onet_pk_poit PRIMARY KEY(key_id)
);

CREATE TABLE onetProperty (
	propName VARCHAR (150) NOT NULL ,
	propValue TEXT NOT NULL,
	CONSTRAINT onet_pk_pr PRIMARY KEY(propName)
);

CREATE TABLE onetReadTrack (
	userID BIGINT NOT NULL ,
	objectID BIGINT NOT NULL ,
	objectType INTEGER NOT NULL ,
	readTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_retr PRIMARY KEY(userID,objectID,objectType)
);

CREATE TABLE onetSequence (
	key_id INTEGER NOT NULL ,
	currentID BIGINT NOT NULL,
	CONSTRAINT onet_pk_se PRIMARY KEY(key_id)
);

CREATE TABLE onetStatusLevel (
	key_id BIGINT NOT NULL ,
	name VARCHAR (150) UNIQUE NOT NULL ,
	description TEXT ,
	imagePath VARCHAR (255) NOT NULL ,
	minPoints INTEGER ,
	maxPoints INTEGER ,
	groupID BIGINT,
	CONSTRAINT onet_pk_stle PRIMARY KEY(key_id)
);

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

CREATE TABLE onetLayout (
	key_id BIGINT NOT NULL ,
	pathId VARCHAR (150) NOT NULL ,
	name VARCHAR (255) NOT NULL ,
	description TEXT ,
	creationtime BIGINT NOT NULL,
	CONSTRAINT onet_pk_lay PRIMARY KEY(key_id)
);

CREATE TABLE onetSkin (
	key_id BIGINT NOT NULL ,
	layoutID BIGINT NOT NULL ,
	pathId VARCHAR (150) NOT NULL ,
	name VARCHAR (255) NOT NULL ,
	description TEXT ,
	creationtime BIGINT NOT NULL,
	CONSTRAINT onet_pk_sk PRIMARY KEY(key_id)
);
CREATE UNIQUE INDEX onet_uk_sk ON onetSkin(pathId,layoutID);

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

CREATE TABLE onetStyle (
	key_id BIGINT NOT NULL ,
	skinID BIGINT NOT NULL ,
	filename VARCHAR (150) NOT NULL,
	name VARCHAR (255) NOT NULL ,
	description TEXT ,
	creationTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_st PRIMARY KEY(key_id)
);

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

CREATE TABLE onetTask (
	key_id BIGINT NOT NULL ,
	name VARCHAR (255) NOT NULL ,
	receiverType INTEGER NOT NULL ,
	sendType INTEGER NOT NULL ,
	activeType INTEGER NOT NULL ,
	activeValue INTEGER NOT NULL ,
	currentSequence INTEGER NOT NULL ,
	creationTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_ta PRIMARY KEY(key_id)
);

CREATE TABLE onetTaskDetail (
	key_id BIGINT UNIQUE NOT NULL ,
	taskID BIGINT NOT NULL ,
	sequenceValue INTEGER NOT NULL ,
	startTime BIGINT NOT NULL ,
	endTime BIGINT NOT NULL ,
	stopValue INTEGER NOT NULL ,
	lastUserID BIGINT NOT NULL ,
	sentTotal INTEGER NOT NULL,
	CONSTRAINT onet_pk_tade PRIMARY KEY(taskID,sequenceValue)
);

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

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


CREATE TABLE onetVoteTrack (
	key_id BIGINT NOT NULL ,
	objectID BIGINT NOT NULL ,
	objectType INTEGER NOT NULL ,
	userID BIGINT NOT NULL ,
	voteTime BIGINT ,
	CONSTRAINT onet_pk_votr PRIMARY KEY(objectID,objectType,userID)
);

CREATE TABLE onetWatch (
	key_id BIGINT UNIQUE NOT NULL,
	userID BIGINT NOT NULL ,
	objectID BIGINT NOT NULL ,
	objectType INTEGER NOT NULL ,
	watchType INTEGER NOT NULL ,
	scheduled INTEGER NOT NULL ,
	expirable INTEGER NOT NULL,
	CONSTRAINT onet_pk_wa PRIMARY KEY(userID,objectID,objectType)
);

CREATE TABLE onetWatchScheduled (
	key_id BIGINT NOT NULL ,
	intervals INTEGER NOT NULL ,
	prevNotifiedTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_wasc PRIMARY KEY(key_id)
);

CREATE TABLE onetAlliance(
    key_id BIGINT NOT NULL,
    forumName VARCHAR (150) NOT NULL,
    description VARCHAR (255) NOT NULL,
    forumURL VARCHAR (255) NOT NULL,
    logoURL VARCHAR (255) NOT NULL,
    showMode INTEGER NOT NULL,
    creationTime BIGINT NOT NULL,
    CONSTRAINT onet_pk_al PRIMARY KEY (key_id)
);

CREATE TABLE onetAnnounce (
	key_id BIGINT NOT NULL ,
	objectID BIGINT NOT NULL ,
	userID BIGINT NOT NULL ,
	objectType INTEGER NOT NULL ,
	annType INTEGER DEFAULT 1,
	subject VARCHAR (255)  ,
	body TEXT NOT NULL ,
	viewCount INTEGER NOT NULL,
	creationTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_an PRIMARY KEY (key_id)
);

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

CREATE TABLE onetForumEvent(
    key_id BIGINT NOT NULL,
    forumID BIGINT NOT NULL,
    eventSubject VARCHAR(255) NOT NULL,
    eventBody TEXT NOT NULL,
    eventType INTEGER NOT NULL,
    userID BIGINT NOT NULL,
    fromIP VARCHAR(50) NOT NULL,
    creationTime BIGINT NOT NULL,
    CONSTRAINT onet_pk_foev PRIMARY KEY (key_id)
);

CREATE TABLE onetPluginProp (
    key_id VARCHAR (50) NOT NULL ,
	propName VARCHAR (150) NOT NULL ,
	propValue TEXT NOT NULL,
	CONSTRAINT onet_pk_plp PRIMARY KEY(key_id,propName)
);
CREATE TABLE onetPointCategory(
    key_id BIGINT NOT NULL,
    forumID BIGINT NOT NULL,
    categoryName VARCHAR (255) NOT NULL,
    pointUnit VARCHAR (50) NOT NULL,
    settings VARCHAR (255) NOT NULL,
    showValue INTEGER NOT NULL,
    tradeValue INTEGER NOT NULL,
    modifyValue INTEGER NOT NULL,
    creationTime BIGINT NOT NULL,
    CONSTRAINT onet_pk_poic PRIMARY KEY(key_id,forumID)
);
CREATE TABLE onetPoints(
    userID BIGINT NOT NULL,
    categoryID BIGINT NOT NULL,
    messageValue INTEGER NOT NULL,
    operationValue INTEGER NOT NULL,
    onlineValue INTEGER NOT NULL,
    loginValue INTEGER NOT NULL,
    CONSTRAINT onet_pk_poin PRIMARY KEY(userID,categoryID)
);

CREATE TABLE onetPointsheet (
	key_id BIGINT NOT NULL ,
	currentTopics INTEGER NOT NULL ,
	removedTopics INTEGER NOT NULL ,
	currentReplies INTEGER NOT NULL ,
	removedReplies INTEGER NOT NULL ,
	excCount INTEGER NOT NULL ,
	loginCount INTEGER NOT NULL ,
	onlineTime BIGINT NOT NULL ,
	lastLogin BIGINT NOT NULL,
	CONSTRAINT onet_pk_pos PRIMARY KEY(key_id)
);
CREATE TABLE onetTrade (
    key_id BIGINT NOT NULL ,
	userID BIGINT NOT NULL ,
	orderNo VARCHAR (100) UNIQUE NOT NULL ,
	orderGross  INTEGER NOT NULL ,
	orderType INTEGER NOT NULL ,
	orderStatus INTEGER NOT NULL ,
	creationTime BIGINT NOT NULL ,
	CONSTRAINT onet_pk_tr PRIMARY KEY(key_id)
);

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

CREATE TABLE onetSynchronize (
    key_id VARCHAR (255) NOT NULL,
	keyName TEXT NOT NULL ,
	keyValue TEXT NOT NULL ,
	fromIP VARCHAR (50) NOT NULL ,
	creationTime BIGINT NOT NULL ,
	CONSTRAINT onet_pk_sy PRIMARY KEY(key_id)
);

CREATE TABLE onetStatusLevelPoints(
    key_id BIGINT NOT NULL,
    points INTEGER NOT NULL,
    CONSTRAINT onet_pk_stlepo PRIMARY KEY(key_id)
);

CREATE TABLE onetAPerm(
    objectID BIGINT NOT NULL,
    objectType INTEGER NOT NULL,
    userID INTEGER NOT NULL,
    userType INTEGER NOT NULL,
    keyName VARCHAR (150) NOT NULL,
    keyValue VARCHAR (255) NOT NULL,
    CONSTRAINT onet_pk_aper PRIMARY KEY(objectID,objectType,userID,userType,keyName)
);

insert into onetSequence values(1,2);
insert into onetSequence values(4,2);
insert into onetUser values(1,'admin','7a57a5a743894a0e','',0,0,'','',1119358440630,1119358440630);
insert into onetUserPerm values(1,-1,1,1,1);

