CREATE SEQUENCE attachments_id_seq;
CREATE TABLE Attachments (
  id INTEGER DEFAULT nextval('attachments_id_seq'),
  TransactionId integer NOT NULL  ,
  Parent integer NOT NULL DEFAULT 0  ,
  MessageId varchar(160) NULL  ,
  Subject varchar(255) NULL  ,
  Filename varchar(255) NULL  ,
  ContentType varchar(80) NULL  ,
  ContentEncoding varchar(80) NULL  ,
  Content text NULL  ,
  Headers text NULL  ,
  Creator integer NOT NULL DEFAULT 0  ,
  Created TIMESTAMP NULL
);

CREATE SEQUENCE queues_id_seq;
CREATE TABLE Queues (
  id INTEGER DEFAULT nextval('queues_id_seq'),
  Name varchar(200) NOT NULL  ,
  Description varchar(255) NULL  ,
  CorrespondAddress varchar(120) NULL  ,
  CommentAddress varchar(120) 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 TIMESTAMP NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated TIMESTAMP NULL  ,
  Disabled integer NOT NULL DEFAULT 0 
);

CREATE SEQUENCE links_id_seq;
CREATE TABLE Links (
  id INTEGER DEFAULT nextval('links_id_seq'),
  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 TIMESTAMP NULL  ,
  Creator integer NOT NULL DEFAULT 0  ,
  Created TIMESTAMP NULL
);

CREATE SEQUENCE principals_id_seq;
CREATE TABLE Principals (
        id INTEGER DEFAULT nextval('principals_id_seq') not null,
        PrincipalType VARCHAR(16) not null,
        ObjectId integer, 
        Disabled integer NOT NULL DEFAULT 0
);

CREATE SEQUENCE groups_id_seq;
CREATE TABLE Groups (
  id INTEGER DEFAULT nextval('groups_id_seq'),
  Name varchar(200) NULL  ,
  Description varchar(255) NULL  ,
  Domain varchar(64),
  Type varchar(64),
  Instance integer
);

CREATE SEQUENCE scripconditions_id_seq;
CREATE TABLE ScripConditions (
  id INTEGER DEFAULT nextval('scripconditions_id_seq'),
  Name varchar(200) NULL  ,
  Description varchar(255) NULL  ,
  ExecModule varchar(60) NULL  ,
  Argument varchar(255) NULL  ,
  ApplicableTransTypes varchar(60) NULL  ,
  Creator integer NOT NULL DEFAULT 0  ,
  Created TIMESTAMP NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated TIMESTAMP NULL
);

CREATE SEQUENCE transactions_id_seq;
CREATE TABLE Transactions (
  id INTEGER DEFAULT nextval('transactions_id_seq'),
  ObjectType varchar(255) NOT NULL  ,
  ObjectId integer NOT NULL DEFAULT 0  ,
  TimeTaken integer NOT NULL DEFAULT 0  ,
  Type varchar(20) NULL  ,
  Field varchar(40) NULL  ,
  OldValue varchar(255) NULL  ,
  NewValue varchar(255) NULL  ,
  ReferenceType varchar(255) NULL,
  OldReference integer NULL  ,
  NewReference integer NULL  ,
  Data varchar(255) NULL  ,
  Creator integer NOT NULL DEFAULT 0  ,
  Created TIMESTAMP NULL 
);

CREATE SEQUENCE scrips_id_seq;
CREATE TABLE Scrips (
  id INTEGER DEFAULT nextval('scrips_id_seq'),
  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) NULL  ,
  Queue integer NOT NULL DEFAULT 0  ,
  Template integer NOT NULL DEFAULT 0  ,
  Creator integer NOT NULL DEFAULT 0  ,
  Created TIMESTAMP NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated TIMESTAMP NULL 
);

CREATE SEQUENCE acl_id_seq;
CREATE TABLE ACL (
  id INTEGER DEFAULT nextval('acl_id_seq'),
  PrincipalType varchar(25) NOT NULL, 
  PrincipalId integer NOT NULL  , 
  RightName varchar(25) NOT NULL  ,
  ObjectType varchar(25) NOT NULL  ,
  ObjectId integer NOT NULL DEFAULT 0,
  DelegatedBy integer NOT NULL DEFAULT 0, 
  DelegatedFrom integer NOT NULL DEFAULT 0 
);

CREATE SEQUENCE groupmembers_id_seq;
CREATE TABLE GroupMembers (
  id INTEGER DEFAULT nextval('groupmembers_id_seq'),
  GroupId integer NOT NULL DEFAULT 0,
  MemberId integer NOT NULL DEFAULT 0  
);

CREATE SEQUENCE cachedgroupmembers_id_seq;
CREATE TABLE CachedGroupMembers (
        id int DEFAULT nextval('cachedgroupmembers_id_seq'),
        GroupId int, 
        MemberId int, 
        Via int, 
        ImmediateParentId int, 
        Disabled integer NOT NULL DEFAULT 0
);

CREATE SEQUENCE users_id_seq;
CREATE TABLE Users (
  id INTEGER DEFAULT nextval('users_id_seq'),
  Name varchar(200) NOT NULL  ,
  Password varchar(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 TIMESTAMP NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated TIMESTAMP NULL
);

CREATE SEQUENCE tickets_id_seq;
CREATE TABLE Tickets (
  id INTEGER DEFAULT nextval('tickets_id_seq'),
  EffectiveId integer NOT NULL DEFAULT 0  ,
  Queue integer NOT NULL DEFAULT 0  ,
  Type varchar(16) 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 TIMESTAMP NULL  ,
  Starts TIMESTAMP NULL  ,
  Started TIMESTAMP NULL  ,
  Due TIMESTAMP NULL  ,
  Resolved TIMESTAMP NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated TIMESTAMP NULL  ,
  Creator integer NOT NULL DEFAULT 0  ,
  Created TIMESTAMP NULL  ,
  Disabled integer NOT NULL DEFAULT 0
);

CREATE SEQUENCE scripactions_id_seq;
CREATE TABLE ScripActions (
  id INTEGER DEFAULT nextval('scripactions_id_seq'),
  Name varchar(200) NULL  ,
  Description varchar(255) NULL  ,
  ExecModule varchar(60) NULL  ,
  Argument varchar(255) NULL  ,
  Creator integer NOT NULL DEFAULT 0  ,
  Created TIMESTAMP NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated TIMESTAMP NULL 
);

CREATE SEQUENCE templates_id_seq;
CREATE TABLE Templates (
  id INTEGER DEFAULT nextval('templates_id_seq'),
  Queue integer NOT NULL DEFAULT 0 ,
  Name varchar(200) NOT NULL  ,
  Description varchar(255) NULL  ,
  Type varchar(16) NULL  ,
  Language varchar(16) NULL  ,
  TranslationOf integer NOT NULL DEFAULT 0  ,
  Content text NULL  ,
  LastUpdated TIMESTAMP NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  Creator integer NOT NULL DEFAULT 0  ,
  Created TIMESTAMP NULL 
);


CREATE SEQUENCE objectcustomfieldvalues_id_seq;
CREATE TABLE ObjectCustomFieldValues (
  id INTEGER DEFAULT nextval('objectcustomfieldvalues_id_seq'),
  CustomField int NOT NULL  ,
  ObjectType varchar(255) NULL  ,
  ObjectId int NOT NULL  ,
  SortOrder integer NOT NULL DEFAULT 0  ,
  Content varchar(255) NULL  ,
  LargeContent text NULL,
  ContentType varchar(80) NULL,
  ContentEncoding varchar(80) NULL  ,
  Creator integer NOT NULL DEFAULT 0  ,
  Created TIMESTAMP NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated TIMESTAMP NULL  ,
  Disabled integer NOT NULL DEFAULT 0
);

CREATE SEQUENCE customfields_id_seq;
CREATE TABLE CustomFields (
  id INTEGER DEFAULT nextval('customfields_id_seq'),
  Name varchar(200) NULL  ,
  Type varchar(200) NULL  ,
  MaxValues integer NOT NULL DEFAULT 0  ,
  Repeated integer NOT NULL DEFAULT 0 , 
  Pattern varchar(255) NULL  ,
  LookupType varchar(255) NOT NULL  ,
  Description varchar(255) NULL  ,
  SortOrder integer NOT NULL DEFAULT 0  ,
  Creator integer NOT NULL DEFAULT 0  ,
  Created TIMESTAMP NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated TIMESTAMP NULL  ,
  Disabled integer NOT NULL DEFAULT 0
);

CREATE SEQUENCE objectcustomfields_id_seq;
CREATE TABLE ObjectCustomFields (
  id INTEGER DEFAULT nextval('objectcustomfields_id_seq'),
  CustomField integer NOT NULL,
  ObjectId integer NOT NULL,
  SortOrder integer NOT NULL DEFAULT 0  ,

  Creator integer NOT NULL DEFAULT 0  ,
  Created TIMESTAMP NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated TIMESTAMP NULL
);

CREATE SEQUENCE customfieldvalues_id_seq;
CREATE TABLE CustomFieldValues (
  id INTEGER DEFAULT nextval('customfieldvalues_id_seq'),
  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 TIMESTAMP NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated TIMESTAMP NULL
);

CREATE SEQUENCE attributes_id_seq;
CREATE TABLE Attributes (
  id INTEGER DEFAULT nextval('attributes_id_seq'),
  Name varchar(255) NOT NULL  ,
  Description varchar(255) NULL  ,
  Content text,
  ContentType varchar(16),
  ObjectType varchar(64),
  ObjectId integer, 
  Creator integer NOT NULL DEFAULT 0  ,
  Created TIMESTAMP NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated TIMESTAMP NULL 
);

CREATE TABLE sessions (
    id char(32) NOT NULL,
    a_session bytea,
    LastUpdated TIMESTAMP not null default current_timestamp,
    PRIMARY KEY (id)
);