fluent-support / database / Migrations / TicketsMigrator.php

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

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