-- This schema was adopted from the oracle schema by
-- Andre Koppel. 
-- Version 0.2 Date 2003.10.21
-- The work is still in progress

CREATE TABLE Attachments (
  	id 		SERIAL,
  	TransactionId	INTEGER NOT NULL,
	Parent 		INTEGER DEFAULT 0 NOT NULL, 
  	MessageId 	VARCHAR(160),
  	Subject 	VARCHAR(255),
  	Filename 	VARCHAR(255),
  	ContentType 	VARCHAR(80),
  	ContentEncoding VARCHAR(80),
  	Content 	BYTE,
  	Headers 	BYTE,
	Creator 	INTEGER DEFAULT 0 NOT NULL,
  	Created 	DATETIME YEAR TO SECOND,
        PRIMARY KEY (id)
); 
CREATE INDEX Attachments1 ON Attachments (Parent);
CREATE INDEX Attachments2 ON Attachments (TransactionId);
CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId);


CREATE TABLE Queues (
	id 			SERIAL,
	Name 			VARCHAR(200) DEFAULT '' NOT NULL,
	Description 		VARCHAR(255) DEFAULT NULL,
	CorrespondAddress 	VARCHAR(120) DEFAULT NULL,
	CommentAddress 		VARCHAR(120) DEFAULT NULL,
	InitialPriority 	INTEGER DEFAULT 0 NOT NULL,
	FinalPriority 		INTEGER DEFAULT 0 NOT NULL,
	DefaultDueIn 		INTEGER DEFAULT 0 NOT NULL,
	Creator 		INTEGER DEFAULT 0 NOT NULL,
	Created 		DATETIME YEAR TO SECOND,
	LastUpdatedBy 		INTEGER DEFAULT 0 NOT NULL,
	LastUpdated		DATETIME YEAR TO SECOND,
	Disabled 		INTEGER DEFAULT 0 NOT NULL,
        PRIMARY KEY (id)
);
CREATE UNIQUE INDEX Queues1 ON Queues (Name);
CREATE INDEX Queues2 ON Queues (Disabled);


CREATE TABLE Links (
  	id 		SERIAL,
	Base 		VARCHAR(240) DEFAULT NULL,
	Target 		VARCHAR(240) DEFAULT NULL,
  	Type 		VARCHAR(20) DEFAULT '' NOT NULL,
	LocalTarget	INTEGER DEFAULT 0 NOT NULL,
	LocalBase	INTEGER DEFAULT 0 NOT NULL,
	LastUpdatedBy 	INTEGER DEFAULT 0 NOT NULL,
  	LastUpdated 	DATETIME YEAR TO SECOND,
	Creator 	INTEGER DEFAULT 0 NOT NULL,
  	Created 	DATETIME YEAR TO SECOND,
        PRIMARY KEY (id)
);
-- 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 TABLE Principals (
	id		SERIAL,
	PrincipalType	VARCHAR(16) DEFAULT '' NOT NULL,
	ObjectId	INTEGER DEFAULT 0,
	Disabled	INTEGER DEFAULT 0 NOT NULL,
        PRIMARY KEY (id)
);
CREATE INDEX Principals2 ON Principals (ObjectId);


CREATE TABLE Groups (
	id 		SERIAL,
	Name 		VARCHAR(200) DEFAULT NULL,
	Description 	VARCHAR(255) DEFAULT NULL,
	Domain		VARCHAR(64) DEFAULT '',
	Type		VARCHAR(64) DEFAULT '',
	Instance	INTEGER DEFAULT 0 NOT NULL,
--	Instance	VARCHAR(64) DEFAULT '' NOT NULL,
        PRIMARY KEY (id)
);
CREATE INDEX Groups1 ON Groups (Domain, Instance, Type, id);
CREATE INDEX Groups2 ON Groups (Type, Instance, Domain);


CREATE TABLE ScripConditions (
 	id			SERIAL,
	Name 			VARCHAR(200),
	Description 		VARCHAR(255),
	ExecModule		VARCHAR(60),
	Argument		VARCHAR(255),
	ApplicableTransTypes	VARCHAR(60),
  	Creator 		INTEGER DEFAULT 0 NOT NULL,
  	Created 		DATETIME YEAR TO SECOND,
  	LastUpdatedBy 		INTEGER DEFAULT 0 NOT NULL,
  	LastUpdated		DATETIME YEAR TO SECOND,
        PRIMARY KEY (id)
);


CREATE TABLE Transactions (
  	id 			SERIAL,
  	EffectiveTicket 	INTEGER DEFAULT 0 NOT NULL,
  	Ticket 			INTEGER DEFAULT 0 NOT NULL,
  	TimeTaken 		INTEGER DEFAULT 0 NOT NULL,
  	Type 			VARCHAR(20),
  	Field 			VARCHAR(40),
  	OldValue 		VARCHAR(255),
  	NewValue 		VARCHAR(255),
  	Data 			VARCHAR(255),
  	Creator 		INTEGER DEFAULT 0 NOT NULL,
  	Created 		DATETIME YEAR TO SECOND,
        PRIMARY KEY (id)
);
CREATE INDEX Transactions1 ON Transactions (Ticket);
CREATE INDEX Transactions2 ON Transactions (EffectiveTicket);


CREATE TABLE Scrips (
  	id 		SERIAL,	
	Description	VARCHAR(255) DEFAULT '',
	ScripCondition 	INTEGER DEFAULT 0 NOT NULL,
	ScripAction	INTEGER DEFAULT 0 NOT NULL,
	ConditionRules	BYTE,
	ActionRules	BYTE,
	CustomIsApplicableCode	BYTE,
	CustomPrepareCode	BYTE,
	CustomCommitCode	BYTE,
	Stage		VARCHAR(32),
	Queue		INTEGER DEFAULT 0 NOT NULL,
	Template	INTEGER DEFAULT 0 NOT NULL,
  	Creator 	INTEGER DEFAULT 0 NOT NULL,
  	Created 	DATETIME YEAR TO SECOND,
  	LastUpdatedBy 	INTEGER DEFAULT 0 NOT NULL,
  	LastUpdated 	DATETIME YEAR TO SECOND,
        PRIMARY KEY (id)
);


CREATE TABLE ACL (
	id 		SERIAL,
	PrincipalType 	VARCHAR(25) NOT NULL,
	PrincipalId 	INTEGER NOT NULL,
	RightName	VARCHAR(25) NOT NULL,
	ObjectType	VARCHAR(25) NOT NULL,
	ObjectId	INTEGER DEFAULT 0 NOT NULL,
	DelegatedBy	INTEGER DEFAULT 0 NOT NULL,
	DelegatedFrom	INTEGER DEFAULT 0 NOT NULL,
        PRIMARY KEY (id)
);
CREATE INDEX ACL1 ON ACL(RightName, ObjectType, ObjectId, PrincipalType, PrincipalId);


CREATE TABLE GroupMembers (
	id 		SERIAL,
	GroupId 	INTEGER DEFAULT 0 NOT NULL,
	MemberId 	INTEGER DEFAULT 0 NOT NULL,
        PRIMARY KEY (id)
);
CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers (GroupId, MemberId);


CREATE TABLE CachedGroupMembers (
	id		SERIAL,
	GroupId		INTEGER DEFAULT 0,
	MemberId	INTEGER DEFAULT 0,
	Via		INTEGER DEFAULT 0,
	ImmediateParentId	INTEGER DEFAULT 0,
	Disabled	INTEGER DEFAULT 0 NOT NULL,
        PRIMARY KEY (id)
);
CREATE INDEX DisGrouMem ON CachedGroupMembers (GroupId, MemberId, Disabled);
CREATE INDEX GrouMem ON CachedGroupMembers (GroupId, MemberId);


CREATE TABLE Users (
  	id 			SERIAL,
  	Name 			VARCHAR(200) NOT NULL,
  	Password 		VARCHAR(40),
  	Comments 		BYTE,
  	Signature 		BYTE,
  	EmailAddress 		VARCHAR(120),
  	FreeFormContactInfo 	BYTE,
	Organization		VARCHAR(200),
  	RealName 		VARCHAR(120),
  	NickName 		VARCHAR(16),
  	Lang 			VARCHAR(16),
	EmailEncoding		VARCHAR(16),
	WebEncoding		VARCHAR(16),
	ExternalContactInfoId	VARCHAR(100),
	ContactInfoSystem	VARCHAR(30),
	ExternalAuthId 		VARCHAR(100),
	AuthSystem		VARCHAR(30),
	Gecos			VARCHAR(16),
  	HomePhone 		VARCHAR(30),
  	WorkPhone 		VARCHAR(30),
  	MobilePhone 		VARCHAR(30),
  	PagerPhone 		VARCHAR(30),
  	Address1	 	VARCHAR(200),
  	Address2 		VARCHAR(200),
  	City 			VARCHAR(100),
  	State 			VARCHAR(100),
  	Zip 			VARCHAR(16),
  	Country 		VARCHAR(50),
	Timezone		VARCHAR(50),
	PGPKey			BYTE,
  	Creator 		INTEGER DEFAULT 0 NOT NULL,
  	Created 		DATETIME YEAR TO SECOND,
  	LastUpdatedBy 		INTEGER DEFAULT 0 NOT NULL,
  	LastUpdated 		DATETIME YEAR TO SECOND,
        PRIMARY KEY (id)
);
-- CREATE UNIQUE INDEX Users1 ON Users (Name);
CREATE INDEX Users2 ON Users (Name);
CREATE INDEX Users3 ON Users (id, EmailAddress);
CREATE INDEX Users4 ON Users (EmailAddress);


CREATE TABLE Tickets (
  	id			SERIAL,
  	EffectiveId		INTEGER DEFAULT 0 NOT NULL,
  	Queue 			INTEGER DEFAULT 0 NOT NULL,
  	Type 			VARCHAR(16), 		
  	IssueStatement 		INTEGER DEFAULT 0 NOT NULL,
  	Resolution 		INTEGER DEFAULT 0 NOT NULL,
  	Owner 			INTEGER DEFAULT 0 NOT NULL,
  	Subject			VARCHAR(200) DEFAULT '[no subject]', 
  	InitialPriority 	INTEGER DEFAULT 0 NOT NULL,
  	FinalPriority 		INTEGER DEFAULT 0 NOT NULL,
  	Priority 		INTEGER DEFAULT 0 NOT NULL,
	TimeEstimated		INTEGER DEFAULT 0 NOT NULL,
  	TimeWorked 		INTEGER DEFAULT 0 NOT NULL,
  	Status 			VARCHAR(10), 		
	TimeLeft 		INTEGER DEFAULT 0 NOT NULL,
  	Told 			DATETIME YEAR TO SECOND,
	Starts			DATETIME YEAR TO SECOND,
	Started			DATETIME YEAR TO SECOND,
  	Due 			DATETIME YEAR TO SECOND,
	Resolved		DATETIME YEAR TO SECOND,
  	LastUpdatedBy 		INTEGER DEFAULT 0 NOT NULL,
  	LastUpdated 		DATETIME YEAR TO SECOND,
	Creator 		INTEGER DEFAULT 0 NOT NULL,
  	Created 		DATETIME YEAR TO SECOND,
  	Disabled 		INTEGER DEFAULT 0 NOT NULL,
        PRIMARY KEY (id)
);
CREATE INDEX Tickets1 ON Tickets (Queue, Status);
CREATE INDEX Tickets2 ON Tickets (Owner);
CREATE INDEX Tickets3 ON Tickets (EffectiveId);
CREATE INDEX Tickets4 ON Tickets (id, Status);
CREATE INDEX Tickets5 ON Tickets (id, EffectiveId);
CREATE INDEX Tickets6 ON Tickets (EffectiveId, Type);


CREATE TABLE ScripActions (
  id		SERIAL,
  Name		VARCHAR(200),
  Description	VARCHAR(255),
  ExecModule	VARCHAR(60),
  Argument	VARCHAR(255),
  Creator	INTEGER DEFAULT 0 NOT NULL,
  Created	DATETIME YEAR TO SECOND,
  LastUpdatedBy	INTEGER DEFAULT 0 NOT NULL,
  LastUpdated	DATETIME YEAR TO SECOND,
  PRIMARY KEY (id)
);


CREATE TABLE Templates (
  	id 		SERIAL,
	Queue 		INTEGER DEFAULT 0 NOT NULL,
	Name		VARCHAR(200) NOT NULL,
	Description	VARCHAR(255),
	Type		VARCHAR(16),
  	Language 	VARCHAR(16), 
  	TranslationOf 	INTEGER DEFAULT 0 NOT NULL,
  	Content 	BYTE,
  	LastUpdated 	DATETIME YEAR TO SECOND,
  	LastUpdatedBy 	INTEGER DEFAULT 0 NOT NULL,
  	Creator 	INTEGER DEFAULT 0 NOT NULL,
  	Created 	DATETIME YEAR TO SECOND,
        PRIMARY KEY (id)
);


CREATE TABLE TicketCustomFieldValues (
	id		SERIAL,
	Ticket		INTEGER NOT NULL,
	CustomField	INTEGER NOT NULL,
	Content		VARCHAR(255),
	Creator		INTEGER DEFAULT 0 NOT NULL,
	Created		DATETIME YEAR TO SECOND,
	LastUpdatedBy	INTEGER DEFAULT 0 NOT NULL,
	LastUpdated	DATETIME YEAR TO SECOND,
        PRIMARY KEY (id)
);

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

CREATE TABLE CustomFields (
	id		SERIAL,
	Name		VARCHAR(200),
	Type		VARCHAR(200),
	Queue		INTEGER DEFAULT 0 NOT NULL,
	Description	VARCHAR(255),
	SortOrder	INTEGER DEFAULT 0 NOT NULL,
	Creator		INTEGER DEFAULT 0 NOT NULL,
	Created		DATETIME YEAR TO SECOND,
	LastUpdatedBy	INTEGER DEFAULT 0 NOT NULL,
	LastUpdated	DATETIME YEAR TO SECOND,
	Disabled	SMALLINT DEFAULT 0 NOT NULL,
        PRIMARY KEY (id)
);
CREATE INDEX CustomFields1 ON CustomFields (Disabled, Queue);


CREATE TABLE CustomFieldValues (
	id		SERIAL,
	CustomField	INTEGER NOT NULL,
	Name		VARCHAR(200),
	Description	VARCHAR(255),
	SortOrder	INTEGER DEFAULT 0 NOT NULL,
	Creator		INTEGER DEFAULT 0 NOT NULL,
	Created		DATETIME YEAR TO SECOND,
	LastUpdatedBy	INTEGER DEFAULT 0 NOT NULL,
	LastUpdated	DATETIME YEAR TO SECOND,
        PRIMARY KEY (id)
);

CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);

CREATE TABLE sessions (
	id		VARCHAR(32) NOT NULL,
	a_session	BYTE,
	LastUpdated	DATETIME YEAR TO SECOND,
        PRIMARY KEY (id)
);