fluent-support / database / Migrations / TicketsMigrator.php

TicketsMigrator.php in Fluent Support – Helpdesk & Customer Support Ticket System 2.4.5, at database/Migrations/TicketsMigrator.php

155 lines 7.2 KB

Raw Download Zip

1	<?php
2
3	namespace FluentSupport\Database\Migrations;
4
5	class TicketsMigrator
6	{
7	static $tableName = 'fs_tickets';
8
9	public static function migrate()
10	{
11	global $wpdb;
12
13	$charsetCollate = $wpdb->get_charset_collate();
14
15	$table = $wpdb->prefix . static::$tableName;
16
17	if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table)) != $table) {
18	$sql = "CREATE TABLE $table (
19	`id` BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
20	`customer_id` BIGINT(20) UNSIGNED NULL,
21	`agent_id` BIGINT(20) UNSIGNED NULL,
22	`mailbox_id` BIGINT(20) UNSIGNED NULL,
23	`product_id` BIGINT(20) UNSIGNED NULL,
24	`product_source` VARCHAR(192) NULL,
25	`privacy` VARCHAR(100) DEFAULT 'private',
26	`priority` VARCHAR(100) DEFAULT 'normal',
27	`client_priority` VARCHAR(100) DEFAULT 'normal',
28	`status` VARCHAR(100) DEFAULT 'new',
29	`title` VARCHAR(192) NULL,
30	`slug` VARCHAR(192) NULL,
31	`hash` VARCHAR(192) NULL,
32	`content_hash` VARCHAR(192) NULL,
33	`message_id` VARCHAR(192) NULL,
34	`source` VARCHAR(192) NULL,
35	`content` LONGTEXT NULL,
36	`secret_content` LONGTEXT NULL,
37	`last_agent_response` TIMESTAMP NULL,
38	`last_customer_response` TIMESTAMP NULL,
39	`waiting_since` TIMESTAMP NULL,
40	`response_count` INT(11) DEFAULT 0,
41	`first_response_time` INT(11) NULL, /* Seconds took for first contact */
42	`total_close_time` INT(11) NULL, /* Seconds took for closing this ticket */
43	`resolved_at` TIMESTAMP NULL,
44	`closed_by` BIGINT(20) UNSIGNED NULL,
45	`created_by` BIGINT(20) UNSIGNED NULL,
46	`serial_number` BIGINT UNSIGNED NULL,
47	`ticket_number` VARCHAR(192) NULL DEFAULT NULL,
48	`created_at` TIMESTAMP NULL,
49	`updated_at` TIMESTAMP NULL,
50	INDEX `idx_customer_id` (`customer_id`),
51	INDEX `idx_agent_id` (`agent_id`),
52	INDEX `idx_mailbox_id` (`mailbox_id`),
53	INDEX `idx_product_id` (`product_id`),
54	INDEX `idx_priority` (`priority`),
55	INDEX `idx_client_priority` (`client_priority`),
56	INDEX `idx_status` (`status`),
57	INDEX `idx_created_at` (`created_at`),
58	INDEX `idx_resolved_at` (`resolved_at`),
59	INDEX `idx_status_resolved_at` (`status`, `resolved_at`),
60	INDEX `idx_ticket_number` (`ticket_number`(191)),
61	INDEX `idx_waiting_since_id` (`waiting_since`, `id`),
62	INDEX `idx_updated_at_id` (`updated_at`, `id`),
63	INDEX `idx_response_count_id` (`response_count`, `id`),
64	UNIQUE KEY `uniq_serial_number` (`serial_number`)
65	) $charsetCollate;";
66	$created = dbDelta($sql);
67	return $created;
68	} else {
69	static::alterTable($table); }
70
71	return false;
72	}
73
74	public static function alterTable($table)
75	{
76	static::addMissingColumns($table);
77	static::addMissingIndexes($table);
78	}
79
80	public static function addMissingColumns($table)
81	{
82	global $wpdb;
83
84	// Our own table name, not user input. esc_sql() because %i needs WP 6.2+.
85	$table = esc_sql($table);
86
87	$existing_columns = $wpdb->get_col("DESC `{$table}`", 0); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
88
89	if (!in_array('waiting_since', $existing_columns)) {
90	// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- $table is sanitized via esc_sql(); column name is a hardcoded literal.
91	$wpdb->query("ALTER TABLE `{$table}` ADD `waiting_since` TIMESTAMP NULL AFTER `last_customer_response`");
92	}
93
94	// Agent who created the ticket on behalf of the customer.
95	if (!in_array('created_by', $existing_columns)) {
96	// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- $table is sanitized via esc_sql(); column name is a hardcoded literal.
97	$wpdb->query("ALTER TABLE `{$table}` ADD `created_by` BIGINT(20) UNSIGNED NULL AFTER `closed_by`");
98	}
99
100	if (!in_array('serial_number', $existing_columns)) {
101	// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- $table is sanitized via esc_sql(); column name is a hardcoded literal.
102	$wpdb->query("ALTER TABLE `{$table}` ADD `serial_number` BIGINT UNSIGNED NULL AFTER `created_by`");
103
104	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table is sanitized via esc_sql().
105	$wpdb->query("UPDATE `{$table}` SET `serial_number` = `id` WHERE `serial_number` IS NULL");
106	}
107
108	if (!in_array('ticket_number', $existing_columns)) {
109	// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- $table is sanitized via esc_sql(); column name is a hardcoded literal.
110	$wpdb->query("ALTER TABLE `{$table}` ADD `ticket_number` VARCHAR(192) NULL DEFAULT NULL AFTER `serial_number`");
111	}
112
113	}
114
115	public static function addMissingIndexes($table)
116	{
117	global $wpdb;
118
119	// Our own table name, not user input. esc_sql() because %i needs WP 6.2+.
120	$table = esc_sql($table);
121
122	$existing_indexes = $wpdb->get_results("SHOW INDEX FROM `{$table}`"); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
123	$existing_index_names = [];
124
125	foreach ($existing_indexes as $index) {
126	$existing_index_names[] = $index->Key_name;
127	}
128
129	$indexes = [
130	'idx_customer_id' => '`customer_id`',
131	'idx_agent_id' => '`agent_id`',
132	'idx_mailbox_id' => '`mailbox_id`',
133	'idx_product_id' => '`product_id`',
134	'idx_priority' => '`priority`',
135	'idx_client_priority' => '`client_priority`',
136	'idx_status' => '`status`',
137	'idx_created_at' => '`created_at`',
138	'idx_resolved_at' => '`resolved_at`',
139	'idx_status_resolved_at' => '`status`, `resolved_at`',
140	'idx_ticket_number' => '`ticket_number`(191)',
141	'uniq_serial_number' => '`serial_number`',
142	'idx_waiting_since_id' => '`waiting_since`, `id`',
143	'idx_updated_at_id' => '`updated_at`, `id`',
144	'idx_response_count_id' => '`response_count`, `id`',
145	];
146	foreach ($indexes as $index_name => $columns) {
147	if (!in_array($index_name, $existing_index_names)) {
148	$indexType = $index_name === 'uniq_serial_number' ? 'UNIQUE KEY' : 'INDEX';
149	// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- all identifiers are either esc_sql()'d or hardcoded literals.
150	$wpdb->query("ALTER TABLE `{$table}` ADD {$indexType} `{$index_name}` ({$columns})");
151	}
152	}
153	}
154	}
155