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


CREATE SEQUENCE QUEUES_seq;
CREATE TABLE Queues (
	id 			NUMBER(11,0) 
		CONSTRAINT Queues_Key PRIMARY KEY,
	Name 			VARCHAR2(200) CONSTRAINT Queues_Name_Unique UNIQUE NOT NULL,
	Description 		VARCHAR2(255),
	CorrespondAddress 	VARCHAR2(120),
	CommentAddress 		VARCHAR2(120),
	InitialPriority 	NUMBER(11,0) DEFAULT 0 NOT NULL,
	FinalPriority 		NUMBER(11,0) DEFAULT 0 NOT NULL,
	DefaultDueIn 		NUMBER(11,0) DEFAULT 0 NOT NULL,
	Creator 		NUMBER(11,0) DEFAULT 0 NOT NULL,
	Created 		DATE,
	LastUpdatedBy 		NUMBER(11,0) DEFAULT 0 NOT NULL,
	LastUpdated		DATE,
	Disabled 		NUMBER(11,0) DEFAULT 0 NOT NULL
);
 CREATE  INDEX Queues1 ON Queues (lower(Name));
CREATE INDEX Queues2 ON Queues (Disabled);


CREATE SEQUENCE LINKS_seq;
CREATE TABLE Links (
  	id 		NUMBER(11,0) 
		CONSTRAINT Links_Key PRIMARY KEY,
	Base 		VARCHAR2(240),
	Target 		VARCHAR2(240),
  	Type 		VARCHAR2(20) NOT NULL,
	LocalTarget	NUMBER(11,0) DEFAULT 0 NOT NULL,
	LocalBase	NUMBER(11,0) DEFAULT 0 NOT NULL,
	LastUpdatedBy 	NUMBER(11,0) DEFAULT 0 NOT NULL,
  	LastUpdated 	DATE,
	Creator 	NUMBER(11,0) DEFAULT 0 NOT NULL,
  	Created 	DATE
);
CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type);
CREATE INDEX Links2 ON Links (Base, Type);
CREATE INDEX Links3 ON Links (Target, Type);
CREATE INDEX Links4 ON Links(Type,LocalBase);


CREATE SEQUENCE PRINCIPALS_seq;
CREATE TABLE Principals (
	id		NUMBER(11,0) 
		CONSTRAINT Principals_Key PRIMARY KEY,
	PrincipalType	VARCHAR2(16),
	ObjectId	NUMBER(11,0),
	Disabled	NUMBER(11,0) DEFAULT 0 NOT NULL
);
CREATE UNIQUE  INDEX Principals2 ON Principals (ObjectId);


CREATE SEQUENCE GROUPS_seq;
CREATE TABLE Groups (
	id 		NUMBER(11,0) 
		CONSTRAINT Groups_Key PRIMARY KEY,
	Name 		VARCHAR2(200),
	Description 	VARCHAR2(255),
	Domain		VARCHAR2(64),
	Type		VARCHAR2(64),
	Instance	NUMBER(11,0) DEFAULT 0 -- NOT NULL
--	Instance	VARCHAR2(64)
);
CREATE INDEX Groups1 ON Groups (lower( Domain), Instance, lower(Type), id);
CREATE INDEX Groups2 ON Groups (lower(Type), Instance, lower(Domain));


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


CREATE SEQUENCE TRANSACTIONS_seq;
CREATE TABLE Transactions (
  	id 			NUMBER(11,0) 
		CONSTRAINT Transactions_Key PRIMARY KEY,
  	EffectiveTicket 	NUMBER(11,0) DEFAULT 0 NOT NULL,
  	Ticket 			NUMBER(11,0) DEFAULT 0 NOT NULL,
  	TimeTaken 		NUMBER(11,0) DEFAULT 0 NOT NULL,
  	Type 			VARCHAR2(20),
  	Field 			VARCHAR2(40),
  	OldValue 		VARCHAR2(255),
  	NewValue 		VARCHAR2(255),
  	Data 			VARCHAR2(255),
  	Creator 		NUMBER(11,0) DEFAULT 0 NOT NULL,
  	Created 		DATE
);
CREATE INDEX Transactions1 ON Transactions (Ticket);
CREATE INDEX Transactions2 ON Transactions (EffectiveTicket);


CREATE SEQUENCE SCRIPS_seq;
CREATE TABLE Scrips (
  	id 		NUMBER(11,0) 
		CONSTRAINT Scrips_Key PRIMARY KEY,	
	Description	VARCHAR2(255),
	ScripCondition 	NUMBER(11,0) DEFAULT 0 NOT NULL,
	ScripAction	NUMBER(11,0) DEFAULT 0 NOT NULL,
	ConditionRules	CLOB,
	ActionRules	CLOB,
	CustomIsApplicableCode	CLOB,
	CustomPrepareCode	CLOB,
	CustomCommitCode	CLOB,
	Stage		VARCHAR2(32),
	Queue		NUMBER(11,0) DEFAULT 0 NOT NULL,
	Template	NUMBER(11,0) DEFAULT 0 NOT NULL,
  	Creator 	NUMBER(11,0) DEFAULT 0 NOT NULL,
  	Created 	DATE,
  	LastUpdatedBy 	NUMBER(11,0) DEFAULT 0 NOT NULL,
  	LastUpdated 	DATE  
);


CREATE SEQUENCE ACL_seq;
CREATE TABLE ACL (
	id 		NUMBER(11,0) 
		CONSTRAINT ACL_Key PRIMARY KEY,
	PrincipalType 	VARCHAR2(25) NOT NULL,
	PrincipalId 	NUMBER(11,0) NOT NULL,
	RightName	VARCHAR2(25) NOT NULL,
	ObjectType	VARCHAR2(25) NOT NULL,
	ObjectId	NUMBER(11,0) DEFAULT 0 NOT NULL,
	DelegatedBy	NUMBER(11,0) DEFAULT 0 NOT NULL,
	DelegatedFrom	NUMBER(11,0) DEFAULT 0 NOT NULL
);
CREATE INDEX ACL1 ON ACL(RightName, ObjectType, ObjectId, PrincipalType, PrincipalId);


CREATE SEQUENCE GROUPMEMBERS_seq;
CREATE TABLE GroupMembers (
	id 		NUMBER(11,0) 
		CONSTRAINT GroupMembers_Key PRIMARY KEY,
	GroupId 	NUMBER(11,0) DEFAULT 0 NOT NULL,
	MemberId 	NUMBER(11,0) DEFAULT 0 NOT NULL
);
CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers (GroupId, MemberId);


CREATE SEQUENCE CachedGroupMembers_seq;
CREATE TABLE CachedGroupMembers (
	id		NUMBER(11,0) 
		CONSTRAINT CachedGroupMembers_Key PRIMARY KEY,
	GroupId		NUMBER(11,0),
	MemberId	NUMBER(11,0),
	Via		NUMBER(11,0),
	ImmediateParentId	NUMBER(11,0),
	Disabled	NUMBER(11,0) DEFAULT 0 NOT NULL
);
CREATE INDEX DisGrouMem ON CachedGroupMembers (GroupId, MemberId, Disabled);
CREATE INDEX GrouMem ON CachedGroupMembers (GroupId, MemberId);


CREATE SEQUENCE USERS_seq;
CREATE TABLE Users (
  	id 			NUMBER(11,0) 
		CONSTRAINT Users_Key PRIMARY KEY,
  	Name 			VARCHAR2(200) CONSTRAINT Users_Name_Unique 
		unique  NOT NULL,
  	Password 		VARCHAR2(40),
  	Comments 		CLOB,
  	Signature 		CLOB,
  	EmailAddress 		VARCHAR2(120),
  	FreeFormContactInfo 	CLOB,
	Organization		VARCHAR2(200),
  	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),
	Timezone		VARCHAR2(50),
	PGPKey			CLOB,
  	Creator 		NUMBER(11,0) DEFAULT 0 NOT NULL,
  	Created 		DATE,
  	LastUpdatedBy 		NUMBER(11,0) DEFAULT 0 NOT NULL,
  	LastUpdated 		DATE
);
-- CREATE UNIQUE INDEX Users1 ON Users (Name);

CREATE INDEX Users2 ON Users( LOWER(name));
CREATE INDEX Users4 ON Users (lower(EmailAddress));


CREATE SEQUENCE TICKETS_seq;
CREATE TABLE Tickets (
       id                      NUMBER(11, 0) 
               CONSTRAINT Tickets_Key PRIMARY KEY,
  	EffectiveId		NUMBER(11,0) DEFAULT 0 NOT NULL,
  	Queue 			NUMBER(11,0) DEFAULT 0 NOT NULL,
  	Type 			VARCHAR2(16), 		
  	IssueStatement 		NUMBER(11,0) DEFAULT 0 NOT NULL,
  	Resolution 		NUMBER(11,0) DEFAULT 0 NOT NULL,
  	Owner 			NUMBER(11,0) DEFAULT 0 NOT NULL,
  	Subject			VARCHAR2(200) DEFAULT '[no subject]', 
  	InitialPriority 	NUMBER(11,0) DEFAULT 0 NOT NULL,
  	FinalPriority 		NUMBER(11,0) DEFAULT 0 NOT NULL,
  	Priority 		NUMBER(11,0) DEFAULT 0 NOT NULL,
	TimeEstimated		NUMBER(11,0) DEFAULT 0 NOT NULL,
  	TimeWorked 		NUMBER(11,0) DEFAULT 0 NOT NULL,
  	Status 			VARCHAR2(10), 		
	TimeLeft 		NUMBER(11,0) DEFAULT 0 NOT NULL,
  	Told 			DATE,
	Starts			DATE,
	Started			DATE,
  	Due 			DATE,
	Resolved		DATE,
  	LastUpdatedBy 		NUMBER(11,0) DEFAULT 0 NOT NULL,
  	LastUpdated 		DATE,
	Creator 		NUMBER(11,0) DEFAULT 0 NOT NULL,
  	Created 		DATE,
  	Disabled 		NUMBER(11,0) DEFAULT 0 NOT NULL
);
CREATE INDEX Tickets1 ON Tickets (Queue, Status);
CREATE INDEX Tickets2 ON Tickets (Owner);
CREATE INDEX Tickets4 ON Tickets (id, Status);
CREATE INDEX Tickets5 ON Tickets (id, EffectiveId);
CREATE INDEX Tickets6 ON Tickets (EffectiveId, Type);


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


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


CREATE SEQUENCE TICKETCUSTOMFIELDVALUES_seq;
CREATE TABLE TicketCustomFieldValues (
	id		NUMBER(11,0) 
		CONSTRAINT TicketCustomFieldValues_Key PRIMARY KEY,
	Ticket		NUMBER(11,0),
	CustomField	NUMBER(11,0) NOT NULL,
	Content		VARCHAR2(255),
	Creator		NUMBER(11,0) DEFAULT 0 NOT NULL,
	Created		DATE,
	LastUpdatedBy	NUMBER(11,0) DEFAULT 0 NOT NULL,
	LastUpdated	DATE
);

CREATE INDEX TicketCustomFieldValues1 ON TicketCustomFieldValues (CustomField,Ticket,Content); 
CREATE INDEX TicketCustomFieldValues2 ON TicketCustomFieldValues (CustomField,Ticket); 

CREATE SEQUENCE CUSTOMFIELDS_seq;
CREATE TABLE CustomFields (
	id		NUMBER(11,0) 
		CONSTRAINT CustomFields_Key PRIMARY KEY,
	Name		VARCHAR2(200),
	Type		VARCHAR2(200),
	Queue		NUMBER(11,0) DEFAULT 0 NOT NULL,
	Description	VARCHAR2(255),
	SortOrder	NUMBER(11,0) DEFAULT 0 NOT NULL,
	Creator		NUMBER(11,0) DEFAULT 0 NOT NULL,
	Created		DATE,
	LastUpdatedBy	NUMBER(11,0) DEFAULT 0 NOT NULL,
	LastUpdated	DATE,
	Disabled	NUMBER(11,0) DEFAULT 0 NOT NULL
);
CREATE INDEX CustomFields1 ON CustomFields (Disabled, Queue);


CREATE SEQUENCE CUSTOMFIELDVALUES_seq;
CREATE TABLE CustomFieldValues (
	id		NUMBER(11,0) 
		CONSTRAINT CustomFieldValues_Key PRIMARY KEY,
	CustomField	NUMBER(11,0),
	Name		VARCHAR2(200),
	Description	VARCHAR2(255),
	SortOrder	NUMBER(11,0) DEFAULT 0 NOT NULL,
	Creator		NUMBER(11,0) DEFAULT 0 NOT NULL,
	Created		DATE,
	LastUpdatedBy	NUMBER(11,0) DEFAULT 0 NOT NULL,
	LastUpdated	DATE
);

CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);

CREATE TABLE sessions (
	id		VARCHAR2(32) 
		CONSTRAINT Sessions_Key PRIMARY KEY,
	a_session	CLOB,
	LastUpdated	DATE
);