CREATE SEQUENCE KEYWORDSELECTS_seq;
CREATE TABLE KeywordSelects (
	id		NUMBER(11, 0) PRIMARY KEY,
	Name		VARCHAR2(255),
	Keyword		NUMBER(11, 0),
	Single		NUMBER(11, 0),
	Depth		NUMBER(11, 0) DEFAULT 0,
	ObjectType	VARCHAR2(32) NOT NULL,
	ObjectField	VARCHAR2(32),
	ObjectValue	VARCHAR2(255),
	Disabled		NUMBER(11, 0) DEFAULT 0
);

CREATE INDEX KeywordSelects1 ON KeywordSelects (Keyword);
CREATE INDEX KeywordSelects2 ON 
	KeywordSelects(ObjectType, ObjectField, ObjectValue);


CREATE SEQUENCE ATTACHMENTS_seq;
CREATE TABLE Attachments (
  	id 		NUMBER(11,0) PRIMARY KEY,
  	TransactionId	NUMBER(11,0) NOT NULL,
  	Parent 		NUMBER(11,0), 		
  	MessageId 	VARCHAR2(160),
  	Subject 	VARCHAR2(255),
  	Filename 	VARCHAR2(255),
  	ContentType 	VARCHAR2(80),
  	ContentEncoding 	VARCHAR2(80),
  	Content 	CLOB,
  	Headers 	CLOB,
  	Creator 	NUMBER(11,0),
  	Created 	DATE,
  	Disabled 	NUMBER(11,0) DEFAULT 0
);

CREATE SEQUENCE QUEUES_seq;
CREATE TABLE Queues (
 	id 			NUMBER(11, 0) PRIMARY KEY,
	Name 			VARCHAR2(40) NOT NULL UNIQUE,
	Description 		VARCHAR2(120),
 	CorrespondAddress 	VARCHAR2(40),
 	CommentAddress 		VARCHAR2(40),
 	InitialPriority 	NUMBER(11, 0),		
 	FinalPriority 		NUMBER(11, 0),
 	DefaultDueIn 		NUMBER(11, 0),
  	Creator 		NUMBER(11, 0),
  	Created 		DATE,
  	LastUpdatedBy 		NUMBER(11, 0),
  	LastUpdated		DATE,
  	Disabled 		NUMBER(11,0) DEFAULT 0
);

CREATE SEQUENCE LINKS_seq;
CREATE TABLE Links (
  	id 		NUMBER(11,0) PRIMARY KEY,
  	Base 		VARCHAR2(255),
  	Target 		VARCHAR2(255),
  	Type 		VARCHAR2(20) NOT NULL,
	LocalTarget	NUMBER(11,0),
	LocalBase	NUMBER(11,0),
  	LastUpdatedBy 	NUMBER(11,0),
  	LastUpdated 	DATE,
  	Creator 	NUMBER(11,0),
  	Created 	DATE
);

CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type);



CREATE SEQUENCE GROUPS_seq;
CREATE TABLE Groups (
	id 		NUMBER(11,0) PRIMARY KEY,
	Name 		VARCHAR2(16) UNIQUE,
	Description 	VARCHAR(64),
	Pseudo		NUMBER(11,0) DEFAULT 0
);

CREATE SEQUENCE WATCHERS_seq;
CREATE TABLE Watchers (
   	id 		NUMBER(11,0) PRIMARY KEY,
   	Type 		VARCHAR2(16), 	
   	Scope 		VARCHAR2(16),	
   	Value 		NUMBER(11,0),	
   	Email 		VARCHAR2(255),  
   	Quiet 		NUMBER(11,0),	
   	Owner 		NUMBER(11,0),	
   	Creator 	NUMBER(11,0),
   	Created 	DATE,
   	LastUpdatedBy 	NUMBER(11,0),
   	LastUpdated 	DATE
);



CREATE SEQUENCE SCRIPCONDITIONS_seq;
CREATE TABLE ScripConditions (
 	id 			NUMBER(11, 0) PRIMARY KEY,
	Name 			VARCHAR2(255),
	Description 		VARCHAR2(255),
	ExecModule		VARCHAR2(60),
	Argument		VARCHAR2(255),
	ApplicableTransTypes	VARCHAR2(60),
  	Creator 		NUMBER(11, 0),
  	Created 		DATE,
  	LastUpdatedBy 		NUMBER(11, 0),
  	LastUpdated		DATE
);


CREATE SEQUENCE TRANSACTIONS_seq;
CREATE TABLE Transactions (
  	id 			NUMBER(11,0) PRIMARY KEY,
  	EffectiveTicket 	NUMBER(11,0),
  	Ticket 			NUMBER(11,0),
  	TimeTaken 		NUMBER(11,0),
  	Type 			VARCHAR2(20),
  	Field 			VARCHAR2(40),
  	OldValue 		VARCHAR2(255),
  	NewValue 		VARCHAR2(255),
  	Data 			VARCHAR2(100),
  	Creator 		NUMBER(11,0),
  	Created 		DATE,
  	Disabled 		NUMBER(11,0) DEFAULT 0
);

CREATE SEQUENCE SCRIPS_seq;
CREATE TABLE Scrips (
  	id 		NUMBER(11,0) PRIMARY KEY,	
	ScripCondition 	NUMBER(11,0),
	ScripAction	NUMBER(11,0),
	Stage		VARCHAR2(32),
	Queue		NUMBER(11,0),
	Template	NUMBER(11,0),
  	Creator 	NUMBER(11,0),
  	Created 	DATE,
  	LastUpdatedBy 	NUMBER(11,0),
  	LastUpdated 	DATE  
);




CREATE SEQUENCE ACL_seq;
CREATE TABLE ACL (
	id 		NUMBER(11,0) PRIMARY KEY,
	PrincipalId 	NUMBER(11,0),
	PrincipalType 	VARCHAR2(25),
	RightName	VARCHAR2(25),
	RightScope	VARCHAR2(25),
	RightAppliesTo  NUMBER(11,0)
);

CREATE SEQUENCE GROUPMEMBERS_seq;
CREATE TABLE GroupMembers (
	id 		NUMBER(11,0) PRIMARY KEY,
	GroupId 	NUMBER(11,0),
	UserId 		NUMBER(11,0) 
);

CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers (GroupId, UserId);


CREATE SEQUENCE OBJECTKEYWORDS_seq;
CREATE TABLE ObjectKeywords (
  id 		NUMBER(11,0)  PRIMARY KEY,
  Keyword	NUMBER(11,0) NOT NULL,
  KeywordSelect NUMBER(11,0)  NOT NULL,
  ObjectType	VARCHAR2(32) NOT NULL,
  ObjectId	NUMBER(11,0) NOT NULL
);

CREATE UNIQUE INDEX ObjectKeywords1 ON ObjectKeywords
	(ObjectId, ObjectType, KeywordSelect, Keyword);
CREATE INDEX ObjectKeywords3 ON ObjectKeywords (Keyword);

CREATE SEQUENCE KEYWORDS_seq;
CREATE TABLE Keywords (
	id		NUMBER(11, 0) PRIMARY KEY,
	Name		VARCHAR2(255) NOT NULL,
	Description	VARCHAR2(255),
	Parent		NUMBER(11, 0),
	Disabled		NUMBER(11, 0) DEFAULT 0
);

CREATE UNIQUE INDEX Keywords1 ON Keywords (Name, Parent);
CREATE INDEX Keywords3 ON Keywords (Parent);

CREATE SEQUENCE USERS_seq;
CREATE TABLE Users (
  	id 			NUMBER(11,0) PRIMARY KEY,
  	Name 			VARCHAR2(120) NOT NULL UNIQUE,
  	Password 		VARCHAR2(40),
  	Comments 		CLOB,
  	Signature 		CLOB,
  	EmailAddress 		VARCHAR2(120),
  	FreeFormContactInfo 	CLOB,
	Organization		VARCHAR2(200),
	Privileged		NUMBER(11,0),
  	RealName 		VARCHAR2(120),
  	NickName 		VARCHAR2(16),
  	Lang 			VARCHAR2(16),
	EmailEncoding		VARCHAR2(16),
	WebEncoding		VARCHAR2(16),
	ExternalContactInfoId	VARCHAR2(100),
	ContactInfoSystem	VARCHAR2(30),
	ExternalAuthId 		VARCHAR2(100),
	AuthSystem		VARCHAR2(30),
	Gecos			VARCHAR2(16),
  	HomePhone 		VARCHAR2(30),
  	WorkPhone 		VARCHAR2(30),
  	MobilePhone 		VARCHAR2(30),
  	PagerPhone 		VARCHAR2(30),
  	Address1	 	VARCHAR2(200),
  	Address2 		VARCHAR2(200),
  	City 			VARCHAR2(100),
  	State 			VARCHAR2(100),
  	Zip 			VARCHAR2(16),
  	Country 		VARCHAR2(50),
  	Creator 		NUMBER(11,0),
  	Created 		DATE,
  	LastUpdatedBy 		NUMBER(11,0),
  	LastUpdated 		DATE,
	Disabled			NUMBER(11,0) DEFAULT 0
);




CREATE SEQUENCE TICKETS_seq;
CREATE TABLE Tickets (
  	id			NUMBER(11, 0) PRIMARY KEY,
  	EffectiveId		NUMBER(11, 0),
  	Queue 			NUMBER(11,0),
  	Type 			VARCHAR2(16), 		
  	IssueStatement 		NUMBER(11,0), 	
  	Resolution 		NUMBER(11,0),		
  	Owner 			NUMBER(11,0),		
  	Subject			VARCHAR2(200) DEFAULT '', 
  	InitialPriority 	NUMBER(11,0) DEFAULT 0,
  	FinalPriority 		NUMBER(11,0) DEFAULT 0,
  	Priority 		NUMBER(11,0) DEFAULT 0,
  	Status 			VARCHAR2(10), 		
  	TimeWorked 		NUMBER(11,0) DEFAULT 0,
	TimeLeft 		NUMBER(11,0) DEFAULT 0,
  	Told 			DATE,
	Starts			DATE,
	Started			DATE,
  	Due 			DATE,
	Resolved		DATE,
  	LastUpdatedBy 		NUMBER(11,0),
  	LastUpdated 		DATE,
	Creator 		NUMBER(11,0),
  	Created 		DATE,
  	Disabled 		NUMBER(11,0) DEFAULT 0
);

CREATE SEQUENCE SCRIPACTIONS_seq;
CREATE TABLE ScripActions (
  id		NUMBER(11,0) PRIMARY KEY,
  Name		VARCHAR2(255),
  Description	VARCHAR2(255),
  ExecModule	VARCHAR2(60),
  Argument	VARCHAR2(255),
  Creator	NUMBER(11,0),
  Created	DATE,
  LastUpdatedBy	NUMBER(11,0),
  LastUpdated	DATE
);


CREATE SEQUENCE TEMPLATES_seq;
CREATE TABLE Templates (
  	id 		NUMBER(11,0) PRIMARY KEY,
	Queue 		NUMBER(11,0) DEFAULT 0 NOT NULL,
	Name		VARCHAR2(40) NOT NULL UNIQUE,
	Description	VARCHAR2(120),
	Type		VARCHAR2(16),
  	Language 	VARCHAR2(16), 
  	TranslationOf 	NUMBER(11,0),
  	Content 	CLOB,
  	LastUpdated 	DATE,
  	LastUpdatedBy 	NUMBER(11,0),
  	Creator 	NUMBER(11,0),
  	Created 	DATE
);