CREATE TABLE `troubleticket` (
  `id` int(11) NOT NULL auto_increment,
  `userid` int(11) NOT NULL default '0',
  `domainid` int(11) NULL default '0',
  `subject` text NOT NULL,
  `priority` int(2) NOT NULL default '3',
  `support_email` varchar(60) default NULL,
  `datesubmitted` datetime NOT NULL default '0000-00-00 00:00:00',
  `status` int(2) NOT NULL default '0',
  `messagetype` int(11) NOT NULL default '0',
  `assignedtoid` int(11) NOT NULL default '0',
  `assignedtodeptid` int(11) NOT NULL default '0',
  `lastlog_datetime` datetime NOT NULL default '0000-00-00 00:00:00',
  `rate_hash` char(32) default NULL,
  `rate` tinyint(4) NOT NULL default '0',
  `feedback` TEXT,
  `autoclose` int(2) NOT NULL default '0',
  `tag` varchar(10) NOT NULL default 'clear',
  PRIMARY KEY  (`id`)
) DEFAULT CHARACTER SET utf8, ENGINE=MyISAM AUTO_INCREMENT=1 ;
ALTER TABLE troubleticket ADD FULLTEXT(subject);

CREATE TABLE `troubleticket_files` (
  `id` int(11) NOT NULL auto_increment,
  `ticketid` int(11) NOT NULL,
  `filename` varchar(50) NOT NULL,
  `dateadded` datetime NOT NULL,
  `filekey` varchar(16) NOT NULL,
  `userid` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `filekey` (`filekey`)
) DEFAULT CHARACTER SET utf8, ENGINE=MyISAM AUTO_INCREMENT=1 ;

CREATE TABLE `troubleticket_log` (
  `id` int(11) NOT NULL auto_increment,
  `troubleticketid` int(11) NOT NULL default '0',
  `message` text NOT NULL,
  `userid` int(11) NOT NULL default '0',
  `externalemail` tinyint(4) NOT NULL default '0',
  `mydatetime` datetime NOT NULL default '0000-00-00 00:00:00',
  `logaction` tinyint(4) NOT NULL default '0',
  `private` int(2) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  FULLTEXT KEY `message` (`message`)
) DEFAULT CHARACTER SET utf8, ENGINE=MyISAM AUTO_INCREMENT=1 ;
ALTER TABLE troubleticket_log ADD FULLTEXT(message);

CREATE TABLE `troubleticket_type` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(40) NOT NULL default '',
  `description` text NOT NULL,
  `myorder` int(2) NOT NULL default '0',
  `enabled` tinyint(4) NOT NULL default '1',
  `enabled_public` tinyint(4) NOT NULL default '1',
  `is_billing_type` tinyint(4) NOT NULL default '0',
  `target_dept` int(11) NOT NULL,
  `target_staff` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) DEFAULT CHARACTER SET utf8, ENGINE=MyISAM;

CREATE TABLE `autoresponders` (
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    `type` TINYINT( 4 ) NOT NULL DEFAULT '1',
    `name` VARCHAR( 75 ) NOT NULL ,
    `subject` VARCHAR( 100 ) NOT NULL ,
    `contents` TEXT NOT NULL,
    `contents_html` TEXT NOT NULL,
    `helpid` INT( 3 ) NOT NULL DEFAULT '0',
    `description` TEXT NOT NULL DEFAULT ''
) DEFAULT CHARACTER SET utf8, ENGINE = MYISAM ;

CREATE TABLE `escalationrules` (
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    `name` VARCHAR( 50 ) NOT NULL ,
    `time_elapsed` INT NOT NULL ,
    `ticket_status` TINYINT NOT NULL ,
    `ticket_priority` TINYINT NOT NULL ,
	`ticket_tag` varchar(10) NOT NULL,
    `reassign_dept` INT NOT NULL ,
    `reassign_staff` INT NOT NULL ,
    `new_priority` TINYINT NOT NULL ,
    `new_tag` varchar(10) NOT NULL,
    `only_apply_once` TINYINT NOT NULL ,
    `transcription_destinataries` TEXT NOT NULL
) DEFAULT CHARACTER SET utf8, ENGINE=MYISAM ;

CREATE TABLE `troubleticket_escalated` (
    `ticket_id` INT NOT NULL ,
    `escalationrule_id` INT NOT NULL ,
    PRIMARY KEY ( `ticket_id` , `escalationrule_id` )
) DEFAULT CHARACTER SET utf8, ENGINE = MYISAM ;

CREATE TABLE `escalationrules_departments` (
    `escalationrule_id` INT NOT NULL ,
    `department_id` INT NOT NULL ,
    PRIMARY KEY ( `escalationrule_id` , `department_id` )
) DEFAULT CHARACTER SET utf8, ENGINE=MYISAM ;

CREATE TABLE `routingrules` (
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    `name` VARCHAR( 100 ) NOT NULL ,
    `order` int(11) NOT NULL default '0',
    `emails` TEXT NOT NULL ,
    `user_type` TINYINT NOT NULL ,
    `autoresponder_template` INT NOT NULL ,
    `openticket` tinyint(4) NOT NULL default '0',
    `target_type` INT NOT NULL ,
    `target_priority` INT NOT NULL ,
    `target_dept` INT NOT NULL ,
    `target_staff` INT NOT NULL ,
    `copy_destinataries` TEXT NOT NULL ,
    `routing_type` TINYINT NOT NULL ,
    `filter_out` text NOT NULL,
    `pop3_hostname` varchar(50) NOT NULL,
    `pop3_port` varchar(4) NOT NULL,
    `pop3_username` VARCHAR( 50 ) NOT NULL ,
    `pop3_password` VARCHAR( 50 ) NOT NULL ,
    `pop3_delete_emails` tinyint(4) NOT NULL
) DEFAULT CHARACTER SET utf8, ENGINE=MYISAM ;

CREATE TABLE `routingrules_groups` (
  `routingrule_id` int(11) NOT NULL,
  `group_id` int(11) NOT NULL,
  PRIMARY KEY  (`routingrule_id`,`group_id`)
) DEFAULT CHARACTER SET utf8, ENGINE=MyISAM;


CREATE TABLE `troubleticket_filters` (
  `id` int(11) NOT NULL auto_increment,
  `user_id` int(10) NOT NULL default '0',
  `private` int(2) NOT NULL default '0',
  `temp` tinyint(4) NOT NULL,
  `name` varchar(50) NOT NULL,
  `sql` text NOT NULL,
  `lastlog_datetime` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) DEFAULT CHARACTER SET utf8, ENGINE=MyISAM;


CREATE TABLE `troubleticket_additionalnotification` (
  `id` int(11) NOT NULL auto_increment,
  `ticketid` int(11) NOT NULL,
  `userid` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE(`ticketid`,`userid`)	
) DEFAULT CHARACTER SET utf8, ENGINE=MyISAM AUTO_INCREMENT=1 ;

CREATE TABLE `departments` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  `is_generaldep` tinyint(4) NOT NULL default '0',
  `lead_id` int(11) NOT NULL,`emails_to_notify` TEXT,
  PRIMARY KEY  (`id`)
) DEFAULT CHARACTER SET utf8, ENGINE=MyISAM AUTO_INCREMENT=3 ;


CREATE TABLE `departments_members` (
  `department_id` int(11) NOT NULL,
  `member_id` int(11) NOT NULL,
  `is_group` tinyint(4) NOT NULL,
  `assign_to` tinyint(4) NOT NULL default '0',
  `notify_to` tinyint(4) NOT NULL default '0',
  `sendclosed` int(2) NOT NULL default '0',
  `sendfeedback` int(2) NOT NULL default '0',
  `sendresolution` int(2) NOT NULL default '0'
) DEFAULT CHARACTER SET utf8, ENGINE=MyISAM;