# {{{ Attachments

CREATE TABLE Attachments (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  TransactionId integer NOT NULL  ,
  Parent integer NOT NULL DEFAULT 0  ,
  MessageId varchar(160) CHARACTER SET ascii NULL  ,
  Subject varchar(255) NULL  ,
  Filename varchar(255) NULL  ,
  ContentType varchar(80) CHARACTER SET ascii NULL  ,
  ContentEncoding varchar(80) CHARACTER SET ascii NULL  ,
  Content LONGBLOB NULL  ,
  Headers LONGTEXT NULL  ,
  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  PRIMARY KEY (id)
) ENGINE=InnoDB  CHARACTER SET utf8;

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

# {{{ Queues
CREATE TABLE Queues (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Name varchar(200) NOT NULL  ,
  Description varchar(255) NULL  ,
  CorrespondAddress varchar(120) CHARACTER SET ascii NULL,
  CommentAddress varchar(120) CHARACTER SET ascii NULL,
  InitialPriority integer NOT NULL DEFAULT 0  ,
  FinalPriority integer NOT NULL DEFAULT 0  ,
  DefaultDueIn integer NOT NULL DEFAULT 0  ,
  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)
) ENGINE=InnoDB CHARACTER SET utf8;

CREATE UNIQUE INDEX Queues1 ON Queues (Name) ;
CREATE INDEX Queues2 ON Queues (Disabled) ;

# }}}

# {{{ Links

CREATE TABLE Links (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Base varchar(240) NULL,
  Target varchar(240) NULL,
  Type varchar(20) NOT NULL,
  LocalTarget integer NOT NULL DEFAULT 0  ,
  LocalBase integer NOT NULL DEFAULT 0  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL  ,
  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET ascii;

CREATE INDEX Links2 ON Links (Base,  Type) ;
CREATE INDEX Links3 ON Links (Target,  Type) ;
CREATE INDEX Links4 ON Links (Type,LocalBase);

# }}}

# {{{ Principals

CREATE TABLE Principals (
        id INTEGER  AUTO_INCREMENT not null,
        PrincipalType VARCHAR(16) not null,
        ObjectId integer, # foreign key to Users or Groups, depending
        Disabled int2 NOT NULL DEFAULT 0 ,
        PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET ascii;

CREATE INDEX Principals2 ON Principals (ObjectId);

# }}}

# {{{ Groups

CREATE TABLE Groups (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Name varchar(200) NULL  ,
  Description varchar(255) NULL  ,
  Domain varchar(64) CHARACTER SET ascii NULL,
  Type varchar(64) CHARACTER SET ascii NULL,
  Instance integer,
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET utf8;

CREATE INDEX Groups1 ON Groups (Domain,Instance,Type,id);
CREATE INDEX Groups2 On Groups (Type, Instance);   

# }}}

# {{{ ScripConditions

CREATE TABLE ScripConditions (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Name varchar(200) NULL  ,
  Description varchar(255) NULL  ,
  ExecModule varchar(60) CHARACTER SET ascii NULL,
  Argument VARBINARY(255) NULL  ,
  ApplicableTransTypes varchar(60) CHARACTER SET ascii NULL  ,

  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL  ,
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET utf8;

# }}}

# {{{ Transactions
CREATE TABLE Transactions (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  ObjectType varchar(64) CHARACTER SET ascii NOT NULL,
  ObjectId integer NOT NULL DEFAULT 0  ,
  TimeTaken integer NOT NULL DEFAULT 0  ,
  Type varchar(20) CHARACTER SET ascii NULL,
  Field varchar(40) CHARACTER SET ascii NULL,
  OldValue varchar(255) NULL  ,
  NewValue varchar(255) NULL  ,
  ReferenceType varchar(255) CHARACTER SET ascii NULL,
  OldReference integer NULL  ,
  NewReference integer NULL  ,
  Data varchar(255) NULL  ,

  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET utf8;

CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId);

# }}}

# {{{ Scrips 

CREATE TABLE Scrips (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Description varchar(255),
  ScripCondition integer NOT NULL DEFAULT 0  ,
  ScripAction integer NOT NULL DEFAULT 0  ,
  ConditionRules text NULL  ,
  ActionRules text NULL  ,
  CustomIsApplicableCode text NULL  ,
  CustomPrepareCode text NULL  ,
  CustomCommitCode text NULL  ,
  Stage varchar(32) CHARACTER SET ascii NULL  ,
  Queue integer NOT NULL DEFAULT 0  ,
  Template 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)
) ENGINE=InnoDB CHARACTER SET utf8;

# }}}

# {{{ ACL
CREATE TABLE ACL (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  PrincipalType varchar(25) CHARACTER SET ascii NOT NULL, #"User" "Group", "Owner", "Cc" "AdminCc", "Requestor"

  PrincipalId integer NOT NULL  , #Foreign key to principals
  RightName varchar(25) CHARACTER SET ascii NOT NULL,
  ObjectType varchar(25) CHARACTER SET ascii NOT NULL,
  ObjectId integer NOT NULL default 0,
  DelegatedBy integer NOT NULL default 0, #foreign key to principals with a userid
  DelegatedFrom integer NOT NULL default 0, #foreign key to ACL
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET utf8;

CREATE INDEX  ACL1 on ACL(RightName, ObjectType, ObjectId,PrincipalType,PrincipalId);

# }}}

# {{{ GroupMembers 

CREATE TABLE GroupMembers (
  id INTEGER NOT NULL AUTO_INCREMENT,
  GroupId integer NOT NULL DEFAULT 0,
  MemberId integer NOT NULL DEFAULT 0,  #Foreign key to Principals
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE UNIQUE INDEX GroupMembers1 on GroupMembers (GroupId, MemberId);


# }}}

# {{{ GroupMembersCache

create table CachedGroupMembers (
        id int auto_increment,
        GroupId int, # foreign key to Principals
        MemberId int, # foreign key to Principals
        Via int, #foreign key to CachedGroupMembers. (may point to $self->id)
        ImmediateParentId int, #foreign key to prinicpals.         
                               # this points to the group that the member is
                               # a member of, for ease of deletes.
        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
        PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET utf8;

CREATE INDEX DisGrouMem  on CachedGroupMembers (GroupId,MemberId,Disabled);
CREATE INDEX CachedGroupMembers3 on CachedGroupMembers (MemberId, ImmediateParentId);

# }}}

# {{{ Users

CREATE TABLE Users (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Name varchar(200) NOT NULL  ,
  Password VARBINARY(40) NULL  ,
  Comments TEXT NULL  ,
  Signature TEXT NULL  ,
  EmailAddress varchar(120) NULL  ,
  FreeformContactInfo TEXT NULL  ,
  Organization varchar(200) 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  ,
  Timezone varchar(50) NULL  ,
  PGPKey text NULL,

  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL  ,
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET utf8;


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


# }}}

# {{{ Tickets

CREATE TABLE Tickets (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  EffectiveId integer NOT NULL DEFAULT 0  ,
  Queue integer NOT NULL DEFAULT 0  ,
  Type varchar(16) CHARACTER SET ascii NULL  ,
  IssueStatement integer NOT NULL DEFAULT 0  ,
  Resolution integer NOT NULL DEFAULT 0  ,
  Owner integer NOT NULL DEFAULT 0  ,
  Subject varchar(200) NULL DEFAULT '[no subject]' ,
  InitialPriority integer NOT NULL DEFAULT 0  ,
  FinalPriority integer NOT NULL DEFAULT 0  ,
  Priority integer NOT NULL DEFAULT 0  ,
  TimeEstimated integer NOT NULL DEFAULT 0  ,
  TimeWorked integer NOT NULL DEFAULT 0  ,
  Status varchar(10) NULL  ,
  TimeLeft integer NOT NULL DEFAULT 0  ,
  Told DATETIME NULL  ,
  Starts DATETIME NULL  ,
  Started DATETIME NULL  ,
  Due DATETIME NULL  ,
  Resolved DATETIME NULL  ,

  WillResolve DATETIME NULL  ,

  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL  ,
  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  Disabled int2 NOT NULL DEFAULT 0 ,
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET utf8;

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

# }}}

# {{{ ScripActions

CREATE TABLE ScripActions (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Name varchar(200) NULL  ,
  Description varchar(255) NULL  ,
  ExecModule varchar(60) CHARACTER SET ascii NULL,
  Argument VARBINARY(255) NULL  ,
  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL  ,
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET utf8;

# }}}

# {{{ Templates

CREATE TABLE Templates (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Queue integer NOT NULL DEFAULT 0 ,
  Name varchar(200) NOT NULL  ,
  Description varchar(255) NULL  ,
  Type varchar(16) CHARACTER SET ascii NULL  ,
  Language varchar(16) CHARACTER SET ascii NULL  ,
  TranslationOf integer NOT NULL DEFAULT 0  ,
  Content TEXT NULL  ,
  LastUpdated DATETIME NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET utf8;

# }}}

# {{{ ObjectCustomFieldValues 

CREATE TABLE ObjectCustomFieldValues (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  CustomField int NOT NULL  ,
  ObjectType varchar(255) CHARACTER SET ascii NOT NULL,	    # Final target of the Object
  ObjectId int NOT NULL  ,		    # New -- Replaces Ticket
  SortOrder integer NOT NULL DEFAULT 0  ,   # New -- ordering for multiple values

  Content varchar(255) NULL  ,
  LargeContent LONGBLOB NULL,		    # New -- to hold 255+ strings
  ContentType varchar(80) CHARACTER SET ascii NULL,		    # New -- only text/* gets searched
  ContentEncoding varchar(80) CHARACTER SET ascii 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 ,        # New -- whether the value was current
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET utf8;

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

# }}}

# {{{ CustomFields

CREATE TABLE CustomFields (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Name varchar(200) NULL  ,
  Type varchar(200) CHARACTER SET ascii NULL  ,	# Changed -- 'Single' and 'Multiple' is moved out
  MaxValues integer,		# New -- was 'Single'(1) and 'Multiple'(0)
  Pattern TEXT NULL  ,	# New -- Must validate against this
  Repeated int2 NOT NULL DEFAULT 0 , # New -- repeated table entry
  Description varchar(255) NULL  ,
  SortOrder integer NOT NULL DEFAULT 0  ,
  LookupType varchar(255) CHARACTER SET ascii 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 ,
  Required int2 NOT NULL DEFAULT 0 ,
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET utf8;

# }}}

# {{{ ObjectCustomFields 

CREATE TABLE ObjectCustomFields (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  CustomField integer 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)
) ENGINE=InnoDB CHARACTER SET utf8;

# }}}

# {{{ CustomFieldValues 

CREATE TABLE CustomFieldValues (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  CustomField int NOT NULL  ,
  Name varchar(200) NULL  ,
  Description varchar(255) 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)
) ENGINE=InnoDB CHARACTER SET utf8;

CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
 
# }}}


# {{{ Attributes

CREATE TABLE Attributes (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Name varchar(255) NULL  ,
  Description varchar(255) NULL  ,
  Content BLOB,
  ContentType varchar(16) CHARACTER SET ascii,
  ObjectType varchar(64) CHARACTER SET ascii,
  ObjectId integer, # foreign key to anything
  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL  ,
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET utf8;

CREATE INDEX Attributes1 on Attributes(Name);
CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);

# }}}

# {{{ Sessions

# sessions is used by Apache::Session to keep sessions in the database.
# We should have a reaper script somewhere.

CREATE TABLE sessions (
    id char(32) NOT NULL,
    a_session LONGBLOB,
    LastUpdated TIMESTAMP,
    PRIMARY KEY (id)
);

# }}}