1DROP TABLE IF EXISTS `%TABLE_PREFIX%ticket_event`;
2CREATE TABLE `%TABLE_PREFIX%ticket_event` (
3  `ticket_id` int(11) unsigned NOT NULL default '0',
4  `staff_id` int(11) unsigned NOT NULL,
5  `team_id` int(11) unsigned NOT NULL,
6  `dept_id` int(11) unsigned NOT NULL,
7  `topic_id` int(11) unsigned NOT NULL,
8  `state` enum('created','closed','reopened','assigned','transferred','overdue') NOT NULL,
9  `staff` varchar(255) NOT NULL default 'SYSTEM',
10  `timestamp` datetime NOT NULL,
11  KEY `ticket_state` (`ticket_id`, `state`, `timestamp`),
12  KEY `ticket_stats` (`timestamp`, `state`)
13) DEFAULT CHARSET=utf8;
14
15DROP TABLE IF EXISTS `%TABLE_PREFIX%ticket_history`;
16DROP TABLE IF EXISTS `%TABLE_PREFIX%history`;
17
18-- Transfer ticket statistics from the %ticket table (inaccurate)
19-- REOPENED
20INSERT INTO `%TABLE_PREFIX%ticket_event`
21    (`ticket_id`, `staff_id`, `team_id`, `dept_id`, `topic_id`,
22        `state`, `staff`, `timestamp`)
23    SELECT `ticket_id`, T1.`staff_id`, `team_id`, T1.`dept_id`, `topic_id`,
24        'reopened', T2.`username`, `reopened`
25    FROM `%TABLE_PREFIX%ticket` T1
26        INNER JOIN `%TABLE_PREFIX%staff` T2
27        ON (T1.`staff_id` = T2.`staff_id`)
28    WHERE `status` = 'open' and `reopened` is not null;
29
30-- CLOSED
31INSERT INTO `%TABLE_PREFIX%ticket_event`
32    (`ticket_id`, `staff_id`, `team_id`, `dept_id`, `topic_id`,
33        `state`, `staff`, `timestamp`)
34    SELECT `ticket_id`, T1.`staff_id`, `team_id`, T1.`dept_id`, `topic_id`,
35        'closed', COALESCE(T2.`username`,'unknown'), `closed`
36    FROM `%TABLE_PREFIX%ticket` T1
37        LEFT JOIN `%TABLE_PREFIX%staff` T2
38        ON (T1.`staff_id` = T2.`staff_id`)
39    WHERE `status` = 'closed' and `closed` is not null;
40
41-- OVERDUE
42INSERT INTO `%TABLE_PREFIX%ticket_event`
43    (`ticket_id`, `staff_id`, `team_id`, `dept_id`, `topic_id`,
44        `state`, `staff`, `timestamp`)
45    SELECT `ticket_id`, T1.`staff_id`, `team_id`, T1.`dept_id`, `topic_id`,
46        'overdue', 'SYSTEM', `duedate`
47    FROM `%TABLE_PREFIX%ticket` T1
48        INNER JOIN `%TABLE_PREFIX%staff` T2
49        ON (T1.`staff_id` = T2.`staff_id`)
50    WHERE `status` = 'open' and `isoverdue`;
51
52-- OPENED
53INSERT INTO `%TABLE_PREFIX%ticket_event`
54    (`ticket_id`, `staff_id`, `team_id`, `dept_id`, `topic_id`,
55        `state`, `staff`, `timestamp`)
56    SELECT `ticket_id`, 0, 0, 0, `topic_id`,
57        'created', 'SYSTEM', T1.`created`
58    FROM `%TABLE_PREFIX%ticket` T1;
59
60UPDATE `%TABLE_PREFIX%config`
61    SET `schema_signature`='f8856d56e51c5cc3416389de78b54515';
62