fluent-support / database / Migrations / TicketsMigrator.php

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

166 lines 8.0 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	// $table is always $wpdb->prefix . 'fs_tickets' — not user input.
85	// esc_sql() is the correct escaping for SQL identifiers; $wpdb->prepare()
86	// cannot quote identifiers in WP < 6.2 (no %i placeholder available).
87	$table = esc_sql($table);
88
89	// Get existing columns
90	$existing_columns = $wpdb->get_col("DESC `{$table}`", 0); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
91
92	// Add waiting_since column if missing (beta user migration)
93	if (!in_array('waiting_since', $existing_columns)) {
94	// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- $table is sanitized via esc_sql(); column name is a hardcoded literal.
95	$wpdb->query("ALTER TABLE `{$table}` ADD `waiting_since` TIMESTAMP NULL AFTER `last_customer_response`");
96	}
97
98	// Add created_by column to track agent who created ticket on behalf of customer
99	if (!in_array('created_by', $existing_columns)) {
100	// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- $table is sanitized via esc_sql(); column name is a hardcoded literal.
101	$wpdb->query("ALTER TABLE `{$table}` ADD `created_by` BIGINT(20) UNSIGNED NULL AFTER `closed_by`");
102	}
103
104	if (!in_array('serial_number', $existing_columns)) {
105	// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- $table is sanitized via esc_sql(); column name is a hardcoded literal.
106	$wpdb->query("ALTER TABLE `{$table}` ADD `serial_number` BIGINT UNSIGNED NULL AFTER `created_by`");
107
108	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table is sanitized via esc_sql().
109	$wpdb->query("UPDATE `{$table}` SET `serial_number` = `id` WHERE `serial_number` IS NULL");
110	}
111
112	if (!in_array('ticket_number', $existing_columns)) {
113	// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- $table is sanitized via esc_sql(); column name is a hardcoded literal.
114	$wpdb->query("ALTER TABLE `{$table}` ADD `ticket_number` VARCHAR(192) NULL DEFAULT NULL AFTER `serial_number`");
115	}
116
117	}
118
119	public static function addMissingIndexes($table)
120	{
121	global $wpdb;
122
123	// $table is always $wpdb->prefix . 'fs_tickets' — not user input.
124	// esc_sql() is the correct escaping for SQL identifiers; $wpdb->prepare()
125	// cannot quote identifiers in WP < 6.2 (no %i placeholder available).
126	$table = esc_sql($table);
127
128	// Get existing indexes
129	$existing_indexes = $wpdb->get_results("SHOW INDEX FROM `{$table}`"); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
130	$existing_index_names = [];
131
132	foreach ($existing_indexes as $index) {
133	$existing_index_names[] = $index->Key_name;
134	}
135
136	// Desired indexes — keys and values (including composite column lists) are
137	// all hardcoded string literals; no user input reaches these queries.
138	$indexes = [
139	'idx_customer_id' => '`customer_id`',
140	'idx_agent_id' => '`agent_id`',
141	'idx_mailbox_id' => '`mailbox_id`',
142	'idx_product_id' => '`product_id`',
143	'idx_priority' => '`priority`',
144	'idx_client_priority' => '`client_priority`',
145	'idx_status' => '`status`',
146	'idx_created_at' => '`created_at`',
147	'idx_resolved_at' => '`resolved_at`',
148	'idx_status_resolved_at' => '`status`, `resolved_at`',
149	'idx_ticket_number' => '`ticket_number`(191)',
150	'uniq_serial_number' => '`serial_number`',
151	'idx_waiting_since_id' => '`waiting_since`, `id`',
152	'idx_updated_at_id' => '`updated_at`, `id`',
153	'idx_response_count_id' => '`response_count`, `id`',
154	];
155	// Add missing indexes. $table is esc_sql()'d above; $index_name and
156	// $columns are hardcoded array literals — no user input reaches this query.
157	foreach ($indexes as $index_name => $columns) {
158	if (!in_array($index_name, $existing_index_names)) {
159	// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- all identifiers are either esc_sql()'d or hardcoded literals.
160	$indexType = $index_name === 'uniq_serial_number' ? 'UNIQUE KEY' : 'INDEX';
161	$wpdb->query("ALTER TABLE `{$table}` ADD {$indexType} `{$index_name}` ({$columns})");
162	}
163	}
164	}
165	}
166