fluent-support / database / Migrations / TicketsMigrator.php

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

139 lines 6.1 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	`created_at` TIMESTAMP NULL,
47	`updated_at` TIMESTAMP NULL,
48	INDEX `idx_customer_id` (`customer_id`),
49	INDEX `idx_agent_id` (`agent_id`),
50	INDEX `idx_mailbox_id` (`mailbox_id`),
51	INDEX `idx_product_id` (`product_id`),
52	INDEX `idx_priority` (`priority`),
53	INDEX `idx_status` (`status`),
54	INDEX `idx_created_at` (`created_at`),
55	INDEX `idx_resolved_at` (`resolved_at`),
56	INDEX `idx_status_resolved_at` (`status`, `resolved_at`)
57	) $charsetCollate;";
58	$created = dbDelta($sql);
59	return $created;
60	} else {
61	static::alterTable($table);
62	}
63
64	return false;
65	}
66
67	public static function alterTable($table)
68	{
69	static::addMissingColumns($table);
70	static::addMissingIndexes($table);
71	}
72
73	public static function addMissingColumns($table)
74	{
75	global $wpdb;
76
77	// $table is always $wpdb->prefix . 'fs_tickets' — not user input.
78	// esc_sql() is the correct escaping for SQL identifiers; $wpdb->prepare()
79	// cannot quote identifiers in WP < 6.2 (no %i placeholder available).
80	$table = esc_sql($table);
81
82	// Get existing columns
83	$existing_columns = $wpdb->get_col("DESC `{$table}`", 0); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
84
85	// Add waiting_since column if missing (beta user migration)
86	if (!in_array('waiting_since', $existing_columns)) {
87	// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- $table is sanitized via esc_sql(); column name is a hardcoded literal.
88	$wpdb->query("ALTER TABLE `{$table}` ADD `waiting_since` TIMESTAMP NULL AFTER `last_customer_response`");
89	}
90
91	// Add created_by column to track agent who created ticket on behalf of customer
92	if (!in_array('created_by', $existing_columns)) {
93	// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- $table is sanitized via esc_sql(); column name is a hardcoded literal.
94	$wpdb->query("ALTER TABLE `{$table}` ADD `created_by` BIGINT(20) UNSIGNED NULL AFTER `closed_by`");
95	}
96	}
97
98	public static function addMissingIndexes($table)
99	{
100	global $wpdb;
101
102	// $table is always $wpdb->prefix . 'fs_tickets' — not user input.
103	// esc_sql() is the correct escaping for SQL identifiers; $wpdb->prepare()
104	// cannot quote identifiers in WP < 6.2 (no %i placeholder available).
105	$table = esc_sql($table);
106
107	// Get existing indexes
108	$existing_indexes = $wpdb->get_results("SHOW INDEX FROM `{$table}`"); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
109	$existing_index_names = [];
110
111	foreach ($existing_indexes as $index) {
112	$existing_index_names[] = $index->Key_name;
113	}
114
115	// Desired indexes — keys and values (including composite column lists) are
116	// all hardcoded string literals; no user input reaches these queries.
117	$indexes = [
118	'idx_customer_id' => '`customer_id`',
119	'idx_agent_id' => '`agent_id`',
120	'idx_mailbox_id' => '`mailbox_id`',
121	'idx_product_id' => '`product_id`',
122	'idx_priority' => '`priority`',
123	'idx_status' => '`status`',
124	'idx_created_at' => '`created_at`',
125	'idx_resolved_at' => '`resolved_at`',
126	'idx_status_resolved_at' => '`status`, `resolved_at`',
127	];
128
129	// Add missing indexes. $table is esc_sql()'d above; $index_name and
130	// $columns are hardcoded array literals — no user input reaches this query.
131	foreach ($indexes as $index_name => $columns) {
132	if (!in_array($index_name, $existing_index_names)) {
133	// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- all identifiers are either esc_sql()'d or hardcoded literals.
134	$wpdb->query("ALTER TABLE `{$table}` ADD INDEX `{$index_name}` ({$columns})");
135	}
136	}
137	}
138	}
139