CREATE TABLE KeywordSelects (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Name varchar(255) NULL  ,
  Keyword integer NULL  ,
  Single integer NULL  ,
  Depth integer NOT NULL DEFAULT 0 ,
  ObjectType varchar(32) NOT NULL  ,
  ObjectField varchar(32) NULL  ,
  ObjectValue varchar(255) NULL  ,
  Disabled int2 NOT NULL DEFAULT 0 ,
  PRIMARY KEY (id)
);
CREATE INDEX KeywordSelects1 ON KeywordSelects (Keyword);
CREATE INDEX KeywordSelects2 ON KeywordSelects (ObjectType, ObjectField, ObjectValue);
CREATE TABLE Attachments (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  TransactionId integer NOT NULL  ,
  Parent integer NULL  ,
  MessageId varchar(160) NULL  ,
  Subject varchar(255) NULL  ,
  Filename varchar(255) NULL  ,
  ContentType varchar(80) NULL  ,
  ContentEncoding varchar(80) NULL  ,
  Content LONGTEXT NULL  ,
  Headers LONGTEXT NULL  ,
  Creator integer NULL  ,
  Created DATETIME NULL  ,
  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 INTEGER NOT NULL  AUTO_INCREMENT,
  Name varchar(120) NOT NULL  ,
  Description varchar(120) NULL  ,
  CorrespondAddress varchar(120) NULL  ,
  CommentAddress varchar(120) NULL  ,
  InitialPriority integer NULL  ,
  FinalPriority integer NULL  ,
  DefaultDueIn integer NULL  ,
  Creator integer NULL  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NULL  ,
  LastUpdated DATETIME NULL  ,
  Disabled int2 NOT NULL DEFAULT 0 ,
  PRIMARY KEY (id)
);
CREATE UNIQUE INDEX Queues1 ON Queues (Name);
CREATE TABLE Links (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Base varchar(240) NULL  ,
  Target varchar(240) NULL  ,
  Type varchar(20) NOT NULL  ,
  LocalTarget integer NULL  ,
  LocalBase integer NULL  ,
  LastUpdatedBy integer NULL  ,
  LastUpdated DATETIME NULL  ,
  Creator integer NULL  ,
  Created DATETIME NULL  ,
  PRIMARY KEY (id)
);
CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type);
CREATE TABLE Groups (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Name varchar(16) NULL  ,
  Description varchar(64) NULL  ,
  Pseudo integer NOT NULL DEFAULT 0 ,
  PRIMARY KEY (id)
);
CREATE UNIQUE INDEX Groups1 ON Groups (Name);
CREATE TABLE Watchers (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Type varchar(16) NULL  ,
  Scope varchar(16) NULL  ,
  Value integer NULL  ,
  Email varchar(255) NULL  ,
  Quiet integer NULL  ,
  Owner integer NULL  ,
  Creator integer NULL  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NULL  ,
  LastUpdated DATETIME NULL  ,
  PRIMARY KEY (id)
);
CREATE INDEX Watchers1 ON Watchers (Scope, Value, Type, Owner);
CREATE TABLE ScripConditions (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Name varchar(255) NULL  ,
  Description varchar(255) NULL  ,
  ExecModule varchar(60) NULL  ,
  Argument varchar(255) NULL  ,
  ApplicableTransTypes varchar(60) NULL  ,
  Creator integer NULL  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NULL  ,
  LastUpdated DATETIME NULL  ,
  PRIMARY KEY (id)
);
CREATE TABLE Transactions (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  EffectiveTicket integer NULL  ,
  Ticket integer NULL  ,
  TimeTaken integer NULL  ,
  Type varchar(20) NULL  ,
  Field varchar(40) NULL  ,
  OldValue varchar(255) NULL  ,
  NewValue varchar(255) NULL  ,
  Data varchar(100) NULL  ,
  Creator integer NULL  ,
  Created DATETIME NULL  ,
  PRIMARY KEY (id)
);
CREATE INDEX Transactions1 ON Transactions (Ticket);
CREATE INDEX Transactions2 ON Transactions (EffectiveTicket);
CREATE TABLE Scrips (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  ScripCondition integer NULL  ,
  ScripAction integer NULL  ,
  Stage varchar(32) NULL  ,
  Queue integer NULL  ,
  Template integer NULL  ,
  Creator integer NULL  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NULL  ,
  LastUpdated DATETIME NULL  ,
  PRIMARY KEY (id)
);
CREATE TABLE ACL (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  PrincipalId integer NULL  ,
  PrincipalType varchar(25) NULL  ,
  RightName varchar(25) NULL  ,
  RightScope varchar(25) NULL  ,
  RightAppliesTo integer NULL  ,
  PRIMARY KEY (id)
);
CREATE INDEX ACL1 ON ACL (RightScope, PrincipalId);
CREATE INDEX ACL2 ON ACL (RightScope, RightAppliesTo, RightName, PrincipalType, PrincipalId);
CREATE TABLE GroupMembers (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  GroupId integer NULL  ,
  UserId integer NULL  ,
  PRIMARY KEY (id)
);
CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers (GroupId, UserId);
CREATE TABLE ObjectKeywords (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Keyword integer NOT NULL  ,
  KeywordSelect integer NOT NULL  ,
  ObjectType varchar(32) NOT NULL  ,
  ObjectId integer NOT NULL  ,
  PRIMARY KEY (id)
);
CREATE UNIQUE INDEX ObjectKeywords1 ON ObjectKeywords (ObjectId, ObjectType, KeywordSelect, Keyword);
CREATE INDEX ObjectKeywords2 ON ObjectKeywords (ObjectId, ObjectType);
CREATE INDEX ObjectKeywords3 ON ObjectKeywords (Keyword);
CREATE TABLE Keywords (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Name varchar(255) NOT NULL  ,
  Description varchar(255) NULL  ,
  Parent integer NULL  ,
  Disabled int2 NOT NULL DEFAULT 0 ,
  PRIMARY KEY (id)
);
CREATE UNIQUE INDEX Keywords1 ON Keywords (Name, Parent);
CREATE INDEX Keywords2 ON Keywords (Name);
CREATE INDEX Keywords3 ON Keywords (Parent);
CREATE TABLE Users (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Name varchar(120) NOT NULL  ,
  Password varchar(40) NULL  ,
  Comments blob NULL  ,
  Signature blob NULL  ,
  EmailAddress varchar(120) NULL  ,
  FreeformContactInfo blob NULL  ,
  Organization varchar(200) NULL  ,
  Privileged integer NULL  ,
  RealName varchar(120) NULL  ,
  Nickname varchar(16) NULL  ,
  Lang varchar(16) NULL  ,
  EmailEncoding varchar(16) NULL  ,
  WebEncoding varchar(16) NULL  ,
  ExternalContactInfoId varchar(100) NULL  ,
  ContactInfoSystem varchar(30) NULL  ,
  ExternalAuthId varchar(100) NULL  ,
  AuthSystem varchar(30) NULL  ,
  Gecos varchar(16) NULL  ,
  HomePhone varchar(30) NULL  ,
  WorkPhone varchar(30) NULL  ,
  MobilePhone varchar(30) NULL  ,
  PagerPhone varchar(30) NULL  ,
  Address1 varchar(200) NULL  ,
  Address2 varchar(200) NULL  ,
  City varchar(100) NULL  ,
  State varchar(100) NULL  ,
  Zip varchar(16) NULL  ,
  Country varchar(50) NULL  ,
  Creator integer NULL  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NULL  ,
  LastUpdated DATETIME NULL  ,
  Disabled int2 NOT NULL DEFAULT 0 ,
  PRIMARY KEY (id)
);
CREATE UNIQUE INDEX Users1 ON Users (Name);
CREATE INDEX Users3 ON Users (id, EmailAddress);
CREATE INDEX Users4 ON Users (EmailAddress);
CREATE TABLE Tickets (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  EffectiveId integer NULL  ,
  Queue integer NULL  ,
  Type varchar(16) NULL  ,
  IssueStatement integer NULL  ,
  Resolution integer NULL  ,
  Owner integer NULL  ,
  Subject varchar(200) NULL DEFAULT '[no subject]' ,
  InitialPriority integer NULL  ,
  FinalPriority integer NULL  ,
  Priority integer NULL  ,
  Status varchar(10) NULL  ,
  TimeWorked integer NULL  ,
  TimeLeft integer NULL  ,
  Told DATETIME NULL  ,
  Starts DATETIME NULL  ,
  Started DATETIME NULL  ,
  Due DATETIME NULL  ,
  Resolved DATETIME NULL  ,
  LastUpdatedBy integer NULL  ,
  LastUpdated DATETIME NULL  ,
  Creator integer NULL  ,
  Created DATETIME NULL  ,
  Disabled int2 NOT NULL DEFAULT 0 ,
  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 TABLE ScripActions (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Name varchar(255) NULL  ,
  Description varchar(255) NULL  ,
  ExecModule varchar(60) NULL  ,
  Argument varchar(255) NULL  ,
  Creator integer NULL  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NULL  ,
  LastUpdated DATETIME NULL  ,
  PRIMARY KEY (id)
);
CREATE TABLE Templates (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Queue integer NOT NULL DEFAULT 0 ,
  Name varchar(40) NOT NULL  ,
  Description varchar(120) NULL  ,
  Type varchar(16) NULL  ,
  Language varchar(16) NULL  ,
  TranslationOf integer NULL  ,
  Content blob NULL  ,
  LastUpdated DATETIME NULL  ,
  LastUpdatedBy integer NULL  ,
  Creator integer NULL  ,
  Created DATETIME NULL  ,
  PRIMARY KEY (id)
);