--- {{{ Attachments

CREATE TABLE Attachments (
  id INTEGER PRIMARY KEY  ,
  TransactionId INTEGER  ,
  Parent integer NULL DEFAULT 0 ,
  MessageId varchar(160) collate NOCASE NULL  ,
  Subject varchar(255) collate NOCASE NULL  ,
  Filename varchar(255) collate NOCASE NULL  ,
  ContentType varchar(80) collate NOCASE NULL  ,
  ContentEncoding varchar(80) collate NOCASE NULL  ,
  Content LONGTEXT collate NOCASE NULL  ,
  Headers LONGTEXT collate NOCASE NULL  ,
  Creator integer NULL DEFAULT 0 ,
  Created DATETIME NULL 
  
) ;

CREATE INDEX Attachments1 ON Attachments (Parent) ;
CREATE INDEX Attachments2 ON Attachments (TransactionId) ;
CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId) ;
--- }}}

--- {{{ Queues
CREATE TABLE Queues (
  id INTEGER PRIMARY KEY  ,
  Name varchar(200) collate NOCASE NOT NULL  ,
  Description varchar(255) collate NOCASE NULL  ,
  CorrespondAddress varchar(120) collate NOCASE NULL  ,
  CommentAddress varchar(120) collate NOCASE NULL  ,
  Lifecycle varchar(32) collate NOCASE NULL  ,
  SubjectTag varchar(120) collate NOCASE NULL  ,
  InitialPriority integer NULL DEFAULT 0 ,
  FinalPriority integer NULL DEFAULT 0 ,
  DefaultDueIn integer NULL DEFAULT 0 ,
  Creator integer NULL DEFAULT 0 ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NULL DEFAULT 0 ,
  LastUpdated DATETIME NULL  ,
  Disabled int2 NOT NULL DEFAULT 0 
 
) ;
CREATE UNIQUE INDEX Queues1 ON Queues (Name) ;

--- }}}

--- {{{ Links

CREATE TABLE Links (
  id INTEGER PRIMARY KEY  ,
  Base varchar(240) collate NOCASE NULL  ,
  Target varchar(240) collate NOCASE NULL  ,
  Type varchar(20) collate NOCASE NOT NULL  ,
  LocalTarget integer NULL DEFAULT 0 ,
  LocalBase integer NULL DEFAULT 0 ,
  LastUpdatedBy integer NULL DEFAULT 0 ,
  LastUpdated DATETIME NULL  ,
  Creator integer NULL DEFAULT 0 ,
  Created DATETIME NULL  
  
) ;
CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type) ;
CREATE INDEX Links4 ON Links(Type,LocalBase);

--- }}}

--- {{{ Principals

CREATE TABLE Principals (
        id INTEGER PRIMARY KEY,
        PrincipalType VARCHAR(16) collate NOCASE not null,
        ObjectId integer,
        Disabled int2 NOT NULL DEFAULT 0 
        
) ;

--- }}}

--- {{{ Groups

CREATE TABLE Groups (
  id INTEGER ,
  Name varchar(200) collate NOCASE NULL  ,
  Description varchar(255) collate NOCASE NULL  ,
  Domain varchar(64) collate NOCASE,
  Type varchar(64) collate NOCASE,
  Instance integer,
  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL
  
) ;

CREATE INDEX Groups1 ON Groups (Domain,Type,Instance);
CREATE INDEX Groups2 ON Groups (Domain,Name,Instance);
CREATE INDEX Groups3 ON Groups (Instance);

--- }}}

--- {{{ ScripConditions

CREATE TABLE ScripConditions (
  id INTEGER PRIMARY KEY  ,
  Name varchar(200) collate NOCASE NULL  ,
  Description varchar(255) collate NOCASE NULL  ,
  ExecModule varchar(60) collate NOCASE NULL  ,
  Argument varchar(255) collate NOCASE NULL  ,
  ApplicableTransTypes varchar(60) collate NOCASE NULL  ,

  Creator integer NULL DEFAULT 0 ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NULL DEFAULT 0 ,
  LastUpdated DATETIME NULL  
  
) ;

--- }}}

--- {{{ Transactions
CREATE TABLE Transactions (
  id INTEGER PRIMARY KEY  ,
  ObjectType varchar(255) collate NOCASE NULL  ,
  ObjectId integer NULL DEFAULT 0 ,
  TimeTaken integer NULL DEFAULT 0 ,
  Type varchar(20) collate NOCASE NULL  ,
  Field varchar(40) collate NOCASE NULL  ,
  OldValue varchar(255) collate NOCASE NULL  ,
  NewValue varchar(255) collate NOCASE NULL  ,
  ReferenceType varchar(255) collate NOCASE NULL  ,
  OldReference integer NULL  ,
  NewReference integer NULL  ,
  Data varchar(255) collate NOCASE NULL  ,

  Creator integer NULL DEFAULT 0 ,
  Created DATETIME NULL  
  
) ;
CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId);

--- }}}

--- {{{ Scrips 

CREATE TABLE Scrips (
  id INTEGER PRIMARY KEY  ,
  Description varchar(255) collate NOCASE,
  ScripCondition integer NULL DEFAULT 0 ,
  ScripAction integer NULL DEFAULT 0 ,
  CustomIsApplicableCode text collate NOCASE NULL  ,
  CustomPrepareCode text collate NOCASE NULL  ,
  CustomCommitCode text collate NOCASE NULL  ,
  Disabled int2 NOT NULL DEFAULT 0 ,
  Template varchar(200) collate NOCASE NOT NULL ,
  Creator integer NULL DEFAULT 0 ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NULL DEFAULT 0 ,
  LastUpdated DATETIME NULL  
  
) ;

--- }}}

CREATE TABLE ObjectScrips (
  id INTEGER NOT NULL  ,
  Scrip int NOT NULL  ,
  Stage varchar(32) collate NOCASE NOT NULL DEFAULT 'TransactionCreate' ,
  ObjectId integer NOT NULL,
  SortOrder integer NOT NULL DEFAULT 0  ,

  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL  ,
  PRIMARY KEY (id)
);
CREATE UNIQUE INDEX ObjectScrips1 ON ObjectScrips (ObjectId, Scrip);

--- {{{ ACL
CREATE TABLE ACL (
  id INTEGER PRIMARY KEY  ,
  PrincipalType varchar(25) collate NOCASE NOT NULL,

  PrincipalId INTEGER DEFAULT 0,
  RightName varchar(25) collate NOCASE NOT NULL  ,
  ObjectType varchar(25) collate NOCASE NOT NULL  ,
  ObjectId INTEGER default 0,
  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL
  
) ;


--- }}}

--- {{{ GroupMembers 

CREATE TABLE GroupMembers (
  id INTEGER PRIMARY KEY  ,
  GroupId integer NULL DEFAULT 0,
  MemberId integer NULL DEFAULT 0,
  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL
  
) ;

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

--- }}}

--- {{{ CachedGroupMembers

create table CachedGroupMembers (
        id integer primary key ,
        GroupId int, 
        MemberId int, 
        Via int, 
        ImmediateParentId int,
        Disabled int2 NOT NULL DEFAULT 0  # if this cached group member is a member of this group by way of a disabled
                                           # group or this group is disabled, this will be set to 1
                                           # this allows us to not find members of disabled subgroups when listing off
                                           # group members recursively.
                                           # Also, this allows us to have the ACL system elide members of disabled groups

        
) ;

CREATE INDEX CachedGroupMembers1 ON CachedGroupMembers (GroupId, MemberId, Disabled);
CREATE INDEX CachedGroupMembers2 ON CachedGroupMembers (MemberId, GroupId, Disabled);
CREATE INDEX CachedGroupMembers3 ON CachedGroupMembers (MemberId, ImmediateParentId);

--- }}}

--- {{{ Users

CREATE TABLE Users (
  id INTEGER ,
  Name varchar(200) collate NOCASE NOT NULL  ,
  Password varchar(256) collate NOCASE NULL  ,
  AuthToken varchar(16) collate NOCASE NULL  ,
  Comments blob NULL  ,
  Signature blob NULL  ,
  EmailAddress varchar(120) collate NOCASE NULL  ,
  FreeformContactInfo blob NULL  ,
  Organization varchar(200) collate NOCASE NULL  ,
  RealName varchar(120) collate NOCASE NULL  ,
  NickName varchar(16) collate NOCASE NULL  ,
  Lang varchar(16) collate NOCASE NULL  ,
  EmailEncoding varchar(16) collate NOCASE NULL  ,
  WebEncoding varchar(16) collate NOCASE NULL  ,
  ExternalContactInfoId varchar(100) collate NOCASE NULL  ,
  ContactInfoSystem varchar(30) collate NOCASE NULL  ,
  ExternalAuthId varchar(100) collate NOCASE NULL  ,
  AuthSystem varchar(30) collate NOCASE NULL  ,
  Gecos varchar(16) collate NOCASE NULL  ,
  HomePhone varchar(30) collate NOCASE NULL  ,
  WorkPhone varchar(30) collate NOCASE NULL  ,
  MobilePhone varchar(30) collate NOCASE NULL  ,
  PagerPhone varchar(30) collate NOCASE NULL  ,
  Address1 varchar(200) collate NOCASE NULL  ,
  Address2 varchar(200) collate NOCASE NULL  ,
  City varchar(100) collate NOCASE NULL  ,
  State varchar(100) collate NOCASE NULL  ,
  Zip varchar(16) collate NOCASE NULL  ,
  Country varchar(50) collate NOCASE NULL  ,
  Timezone char(50) NULL  ,
  PGPKey text collate NOCASE NULL,
  SMIMECertificate text collate NOCASE NULL,

  Creator integer NULL DEFAULT 0 ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NULL DEFAULT 0 ,
  LastUpdated DATETIME NULL  
  
) ;


CREATE UNIQUE INDEX Users1 ON Users (Name) ;
CREATE INDEX Users4 ON Users (EmailAddress);


--- }}}

--- {{{ Tickets

CREATE TABLE Tickets (
  id INTEGER PRIMARY KEY  ,
  EffectiveId integer NULL DEFAULT 0 ,
  IsMerged int2 NULL DEFAULT NULL,
  Queue integer NULL DEFAULT 0 ,
  Type varchar(16) collate NOCASE NULL  ,
  IssueStatement integer NULL DEFAULT 0 ,
  Resolution integer NULL DEFAULT 0 ,
  Owner integer NULL DEFAULT 0 ,
  Subject varchar(200) collate NOCASE NULL DEFAULT '[no subject]' ,
  InitialPriority integer NULL DEFAULT 0 ,
  FinalPriority integer NULL DEFAULt 0 ,
  Priority integer NULL DEFAULT 0 ,
  TimeEstimated integer NULL DEFAULT 0 ,
  TimeWorked integer NULL DEFAULT 0 ,
  Status varchar(64) collate NOCASE NULL  ,
  TimeLeft integer NULL DEFAULT 0 ,
  Told DATETIME NULL  ,
  Starts DATETIME NULL  ,
  Started DATETIME NULL  ,
  Due DATETIME NULL  ,
  Resolved DATETIME NULL  ,


  LastUpdatedBy integer NULL DEFAULT 0 ,
  LastUpdated DATETIME NULL  ,
  Creator integer NULL DEFAULT 0 ,
  Created DATETIME NULL  ,
  Disabled int2 NOT NULL DEFAULT 0
  
) ;

CREATE INDEX Tickets1 ON Tickets (Queue, Status) ;
CREATE INDEX Tickets2 ON Tickets (Owner) ;
CREATE INDEX Tickets3 ON Tickets (EffectiveId) ;

--- }}}

--- {{{ ScripActions

CREATE TABLE ScripActions (
  id INTEGER PRIMARY KEY  ,
  Name varchar(200) collate NOCASE NULL  ,
  Description varchar(255) collate NOCASE NULL  ,
  ExecModule varchar(60) collate NOCASE NULL  ,
  Argument varchar(255) collate NOCASE NULL  ,
  Creator integer NULL DEFAULT 0 ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NULL DEFAULT 0 ,
  LastUpdated DATETIME NULL  
  
) ;

--- }}}

--- {{{ Templates

CREATE TABLE Templates (
  id INTEGER PRIMARY KEY  ,
  Queue integer NOT NULL DEFAULT 0 ,
  Name varchar(200) collate NOCASE NOT NULL  ,
  Description varchar(255) collate NOCASE NULL  ,
  Type varchar(16) collate NOCASE NULL  ,
  Content blob NULL  ,
  LastUpdated DATETIME NULL  ,
  LastUpdatedBy integer NULL DEFAULT 0 ,
  Creator integer NULL DEFAULT 0 ,
  Created DATETIME NULL  
  
) ;

--- }}}




CREATE TABLE ObjectCustomFieldValues (
  id INTEGER NOT NULL  ,
  CustomField int NOT NULL  ,
  ObjectType varchar(255) collate NOCASE NOT NULL,         # Final target of the Object
  ObjectId int NOT NULL  ,                  # New -- Replaces Ticket
  SortOrder integer NOT NULL DEFAULT 0  ,

  Content varchar(255) collate NOCASE NULL  ,
  LargeContent LONGTEXT collate NOCASE NULL,               # New -- to hold 255+ strings
  ContentType varchar(80) collate NOCASE NULL,             # New -- only text/* gets searched
  ContentEncoding varchar(80) collate NOCASE NULL  ,       # New -- for binary Content

  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL  ,
  Disabled int2 NOT NULL DEFAULT 0 ,
  PRIMARY KEY (id)
) ;

CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (Content); 
CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId); 



CREATE TABLE CustomFields (
  id INTEGER NOT NULL  ,
  Name varchar(200) collate NOCASE NULL  ,
  Type varchar(200) collate NOCASE NULL  ,     # Changed -- 'Single' and 'Multiple' is moved out
  RenderType varchar(64) collate NOCASE NULL  ,
  MaxValues integer,            # New -- was 'Single'(1) and 'Multiple'(0)
  Pattern varchar(65536) collate NOCASE NULL  ,        # New -- Must validate against this
  BasedOn INTEGER NULL,
  ValuesClass varchar(64) collate NOCASE NULL  ,
  Description varchar(255) collate NOCASE NULL  ,
  SortOrder integer NOT NULL DEFAULT 0  ,
  LookupType varchar(255) collate NOCASE NOT NULL,

  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL  ,
  Disabled int2 NOT NULL DEFAULT 0 ,
  PRIMARY KEY (id)
) ;



CREATE TABLE ObjectCustomFields (
  id INTEGER NOT NULL  ,
  CustomField int NOT NULL  ,
  ObjectId integer NOT NULL,
  SortOrder integer NOT NULL DEFAULT 0  ,

  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL  ,
  PRIMARY KEY (id)
) ;



CREATE TABLE CustomFieldValues (
  id INTEGER NOT NULL  ,
  CustomField int NOT NULL  ,
  Name varchar(200) collate NOCASE NULL  ,
  Description varchar(255) collate NOCASE NULL  ,
  SortOrder integer NOT NULL DEFAULT 0  ,
  Category varchar(255) collate NOCASE NULL  ,

  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL  ,
  PRIMARY KEY (id)
) ;

CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
 

--- {{{ Attributes
CREATE TABLE Attributes (
  id INTEGER PRIMARY KEY  ,
  Name varchar(255) collate NOCASE NOT NULL  ,
  Description varchar(255) collate NOCASE NULL  ,
  Content LONGTEXT collate NOCASE NULL  ,
  ContentType varchar(16) collate NOCASE,
  ObjectType varchar(25) collate NOCASE NOT NULL  ,
  ObjectId INTEGER ,
  Creator integer NULL DEFAULT 0 ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NULL DEFAULT 0 ,
  LastUpdated DATETIME NULL  
 
) ;
CREATE INDEX Attributes1 on Attributes(Name);
CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);

--- }}}

CREATE TABLE Classes (
id INTEGER PRIMARY KEY,
Name varchar(255) collate NOCASE NOT NULL DEFAULT '',
Description varchar(255) collate NOCASE NOT NULL DEFAULT '',
SortOrder integer NOT NULL DEFAULT 0,
Disabled smallint NOT NULL DEFAULT 0,
Creator integer NOT NULL DEFAULT 0,
Created TIMESTAMP NULL,
LastUpdatedBy integer NOT NULL DEFAULT 0,
LastUpdated TIMESTAMP NULL,
HotList smallint NOT NULL DEFAULT 0
);

CREATE TABLE Articles (
id INTEGER PRIMARY KEY,
Name varchar(255) collate NOCASE NOT NULL DEFAULT '',
Summary varchar(255) collate NOCASE NOT NULL DEFAULT '',
SortOrder integer NOT NULL DEFAULT 0,
Class integer NOT NULL DEFAULT 0,
Parent integer NOT NULL DEFAULT 0,
URI varchar(255) collate NOCASE,
Creator integer NOT NULL DEFAULT 0,
Created TIMESTAMP NULL,
LastUpdatedBy integer NOT NULL DEFAULT 0,
LastUpdated TIMESTAMP NULL
);


CREATE TABLE Topics (
id INTEGER PRIMARY KEY,
Parent integer NOT NULL DEFAULT 0,
Name varchar(255) collate NOCASE NOT NULL DEFAULT '',
Description varchar(255) collate NOCASE NOT NULL DEFAULT '',
ObjectType varchar(64) collate NOCASE NOT NULL DEFAULT '',
ObjectId integer NOT NULL DEFAULT 0
);


CREATE TABLE ObjectTopics (
id INTEGER PRIMARY KEY,
Topic integer NOT NULL DEFAULT 0,
ObjectType varchar(64) collate NOCASE NOT NULL DEFAULT '',
ObjectId integer NOT NULL DEFAULT 0
);

CREATE TABLE ObjectClasses (
id INTEGER PRIMARY KEY,
Class integer NOT NULL DEFAULT 0,
ObjectType varchar(64) collate NOCASE NOT NULL DEFAULT '',
ObjectId integer NOT NULL DEFAULT 0,
Creator integer NOT NULL DEFAULT 0,
Created TIMESTAMP NULL,
LastUpdatedBy integer NOT NULL DEFAULT 0,
LastUpdated TIMESTAMP NULL
);