#
# CERBERUS HELPDESK 1.3.0 to 1.4.0 - DATABASE UPDATE SQL
#
# NOTE: It is VERY important you make a backup of your database before
#  running this upgrade script.
#

DROP TABLE IF EXISTS recipient;

ALTER TABLE `address` DROP INDEX `sender_address`;  
ALTER TABLE `address` CHANGE `address_address` `address_address_old` CHAR(128) NOT NULL; 
ALTER TABLE `address` CHANGE `address_replyto` `address_address` CHAR(128) NOT NULL; 
ALTER TABLE `address` ADD INDEX(`address_address`);

ALTER TABLE `thread` CHANGE `thread_message_id` `thread_message_id` CHAR( 255 ) NOT NULL;  

ALTER TABLE `thread` ADD `thread_inreplyto_id` INT( 11 ) NOT NULL AFTER `thread_message_id`;
  
ALTER TABLE `thread` ADD INDEX (`thread_inreplyto_id`);

DROP TABLE IF EXISTS `requestor`;

CREATE TABLE requestor (`requestor_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY) select t.ticket_id,a.address_id from ticket t LEFT JOIN thread th ON (t.min_thread_id=th.thread_id) LEFT JOIN address a ON (th.thread_address_id=a.address_id) ORDER BY t.ticket_id ASC,th.thread_id ASC;

ALTER TABLE `requestor` ADD INDEX(`ticket_id`);

ALTER TABLE `requestor` ADD INDEX (`address_id`);

CREATE TABLE email_templates (
  template_id bigint(20) unsigned NOT NULL auto_increment,
  template_name varchar(128) NOT NULL default '',
  template_description varchar(255) NOT NULL default '',
  template_text text NOT NULL,
  template_created_by bigint(20) NOT NULL default '0',
  template_private tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (template_id),
  KEY template_id (template_id)
) TYPE=MyISAM;

CREATE TABLE `ticket_views` (
`view_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
`view_name` CHAR(64) NOT NULL, 
`view_private` TINYINT DEFAULT '0' NOT NULL, 
`view_queues` CHAR(255) NOT NULL, 
`view_columns` CHAR(255) NOT NULL,
INDEX (`view_id`)
);

ALTER TABLE `ticket_views` ADD `view_created_by_id` BIGINT DEFAULT '0' NOT NULL AFTER `view_name`;  

CREATE TABLE rule_action (
  rule_id bigint(20) unsigned NOT NULL default '0',
  action_type int(10) unsigned NOT NULL default '0',
  action_value char(128) NOT NULL default '',
  INDEX rule_id (rule_id)
) TYPE=MyISAM;

CREATE TABLE rule_entry (
  rule_id bigint(20) unsigned NOT NULL auto_increment,
  rule_name char(128) NOT NULL default '',
  rule_order int(10) unsigned NOT NULL default '0',
  PRIMARY KEY (rule_id)
) TYPE=MyISAM;

CREATE TABLE rule_fov (
  rule_id bigint(20) unsigned NOT NULL default '0',
  fov_field int(10) unsigned NOT NULL default '0',
  fov_oper int(10) unsigned NOT NULL default '0',
  fov_value char(128) NOT NULL default '',
  INDEX rule_id (rule_id)
) TYPE=MyISAM;

CREATE TABLE `sla` (  
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,   
`name` CHAR(64) NOT NULL,   
`queues` CHAR(255) NOT NULL,  
INDEX (`id`)  
);  
  
CREATE TABLE `company` (  
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,   
`name` VARCHAR(128) NOT NULL,   
`sla_id` BIGINT NOT NULL,  
INDEX (`id`)  
);  
  
CREATE TABLE `email_domains` (  
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,   
`domain` VARCHAR(128) NOT NULL,   
`company_id` BIGINT NOT NULL,  
INDEX (`id`, `company_id`)  
);  
  
ALTER TABLE `company` ADD INDEX(`sla_id`); 
  
ALTER TABLE `queue` ADD `queue_restricted` TINYINT DEFAULT '0' NOT NULL, ADD `queue_response_restricted` TEXT NOT NULL;   
  
ALTER TABLE `address` ADD `company_id` BIGINT NOT NULL;  
ALTER TABLE `address` ADD INDEX (`company_id`);

ALTER TABLE `email_domains` DROP INDEX `id`;
ALTER TABLE `email_domains` ADD INDEX(`id`); 
ALTER TABLE `email_domains` ADD INDEX(`company_id`);