text: /**
   * @version v1.10.0
   * @signature 5cd0a25a54fd27ed95f00d62edda4c6d
   * @title Add support for ticket tasks
   *
   * This patch adds ability to thread anything and introduces tasks
   *
   */
  
  -- Add thread table
  DROP TABLE IF EXISTS `%TABLE_PREFIX%thread`;
  CREATE TABLE IF NOT EXISTS `%TABLE_PREFIX%thread` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `object_id` int(11) unsigned NOT NULL,
    `object_type` char(1) NOT NULL,
    `extra` text,
    `created` datetime NOT NULL,
    PRIMARY KEY (`id`),
    KEY `object_id` (`object_id`),
    KEY `object_type` (`object_type`)
  ) DEFAULT CHARSET=utf8;
  
  -- create threads
  INSERT INTO `%TABLE_PREFIX%thread`
      (`object_id`, `object_type`, `created`)
      SELECT t1.ticket_id, 'T', t1.created
          FROM `%TABLE_PREFIX%ticket_thread` t1
          JOIN (
              SELECT ticket_id, MIN(id) as id
              FROM `%TABLE_PREFIX%ticket_thread`
              WHERE `thread_type` = 'M'
              GROUP BY ticket_id
      ) t2
      ON (t1.ticket_id=t2.ticket_id and t1.id=t2.id)
      ORDER BY t1.created;
  
  -- convert ticket_thread to thread_entry
  CREATE TABLE `%TABLE_PREFIX%thread_entry` (
    `id` int(11) unsigned NOT NULL auto_increment,
    `pid` int(11) unsigned NOT NULL default '0',
    `thread_id` int(11) unsigned NOT NULL default '0',
    `staff_id` int(11) unsigned NOT NULL default '0',
    `user_id` int(11) unsigned not null default 0,
    `type` char(1) NOT NULL default '',
    `flags` int(11) unsigned NOT NULL default '0',
    `poster` varchar(128) NOT NULL default '',
    `editor` int(10) unsigned NULL,
    `editor_type` char(1) NULL,
    `source` varchar(32) NOT NULL default '',
    `title` varchar(255),
    `body` text NOT NULL,
    `format` varchar(16) NOT NULL default 'html',
    `ip_address` varchar(64) NOT NULL default '',
    `created` datetime NOT NULL,
    `updated` datetime NOT NULL,
    PRIMARY KEY  (`id`),
    KEY `pid` (`pid`),
    KEY `thread_id` (`thread_id`),
    KEY `staff_id` (`staff_id`),
    KEY `type` (`type`)
  ) DEFAULT CHARSET=utf8;
  
  -- Set the ORIGINAL_MESSAGE flag to all the first messages of each thread
  CREATE TABLE `%TABLE_PREFIX%_orig_msg_ids`
    (`id` INT NOT NULL, PRIMARY KEY (id))
    SELECT MIN(id) AS `id` FROM `%TABLE_PREFIX%ticket_thread`
    WHERE `thread_type` = 'M'
    GROUP BY `ticket_id`;
  
  INSERT INTO `%TABLE_PREFIX%thread_entry`
    (`id`, `pid`, `thread_id`, `staff_id`, `user_id`, `type`, `flags`,
      `poster`, `source`, `title`, `body`, `format`, `ip_address`, `created`,
      `updated`)
    SELECT t1.`id`, t1.`pid`, t2.`id`, t1.`staff_id`, t1.`user_id`, t1.`thread_type`,
      CASE WHEN t3.`id` IS NULL THEN 0 ELSE 1 END,
      t1.`poster`, t1.`source`, t1.`title`, t1.`body`, t1.`format`, t1.`ip_address`,
      t1.`created`, t1.`updated`
    FROM `%TABLE_PREFIX%ticket_thread` t1
    LEFT JOIN `%TABLE_PREFIX%thread` t2 ON (t2.object_id = t1.ticket_id AND t2.object_type = 'T')
    LEFT JOIN `%TABLE_PREFIX%_orig_msg_ids` t3 ON (t1.id = t3.id);
  
  DROP TABLE `%TABLE_PREFIX%ticket_thread`;
  DROP TABLE `%TABLE_PREFIX%_orig_msg_ids`;
  
  -- move records in ticket_attachment to generic attachment table
  ALTER TABLE  `%TABLE_PREFIX%attachment`
      DROP PRIMARY KEY,
      ADD  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST,
      ADD UNIQUE  `file-type` (`object_id`, `file_id`, `type`);
  
  INSERT INTO `%TABLE_PREFIX%attachment`
      (`object_id`, `type`, `file_id`, `inline`)
      SELECT `ref_id`, 'H', `file_id`, `inline`
      FROM `%TABLE_PREFIX%ticket_attachment` A
      WHERE A.file_id > 0;
  
  -- convert ticket_email_info to thread_entry_email
  ALTER TABLE  `%TABLE_PREFIX%ticket_email_info`
      ADD INDEX (  `thread_id` );
  
  ALTER TABLE  `%TABLE_PREFIX%ticket_email_info`
      CHANGE  `thread_id`  `thread_entry_id` INT( 11 ) UNSIGNED NOT NULL,
      CHANGE  `email_mid`  `mid` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
  
  RENAME TABLE `%TABLE_PREFIX%ticket_email_info` TO  `%TABLE_PREFIX%thread_entry_email`;
  
  -- create task task
  CREATE TABLE IF NOT EXISTS `%TABLE_PREFIX%task` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `object_id` int(11) NOT NULL DEFAULT '0',
    `object_type` char(1) NOT NULL,
    `number` varchar(20) DEFAULT NULL,
    `dept_id` int(10) unsigned NOT NULL DEFAULT '0',
    `sla_id` int(10) unsigned NOT NULL DEFAULT '0',
    `staff_id` int(10) unsigned NOT NULL DEFAULT '0',
    `team_id` int(10) unsigned NOT NULL DEFAULT '0',
    `flags` int(10) unsigned NOT NULL DEFAULT '0',
    `duedate` datetime DEFAULT NULL,
    `created` datetime NOT NULL,
    `updated` datetime NOT NULL,
    PRIMARY KEY (`id`),
    KEY `dept_id` (`dept_id`),
    KEY `staff_id` (`staff_id`),
    KEY `team_id` (`team_id`),
    KEY `created` (`created`),
    KEY `sla_id` (`sla_id`),
    KEY `object` (`object_id`,`object_type`)
  ) DEFAULT CHARSET=utf8;
  
  -- rename ticket sequence numbering
  
  UPDATE `%TABLE_PREFIX%config`
      SET `key` = 'ticket_number_format'
      WHERE `key` = 'number_format'  AND `namespace` = 'core';
  
  UPDATE `%TABLE_PREFIX%config`
      SET `key` = 'ticket_sequence_id'
      WHERE `key` = 'sequence_id'  AND `namespace` = 'core';
  
  -- add parent department support
  ALTER TABLE `%TABLE_PREFIX%department`
    DROP INDEX  `dept_name`,
    ADD `pid` int(11) unsigned default NULL AFTER `id`,
    ADD `path` varchar(128) NOT NULL default '/' AFTER `message_auto_response`,
    ADD UNIQUE  `name` (  `name` ,  `pid` );
  
  UPDATE `%TABLE_PREFIX%department`
    SET `path` = CONCAT('/', id, '/');
  
  -- Set new schema signature
  UPDATE `%TABLE_PREFIX%config`
      SET `value` = '5cd0a25a54fd27ed95f00d62edda4c6d'
      WHERE `key` = 'schema_signature' AND `namespace` = 'core';
  