fluent-support / database / Migrations / TicketsMigrator.php

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

122 lines 4.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("SHOW TABLES LIKE '$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_at` TIMESTAMP NULL,
46	`updated_at` TIMESTAMP NULL,
47	INDEX `idx_customer_id` (`customer_id`),
48	INDEX `idx_agent_id` (`agent_id`),
49	INDEX `idx_mailbox_id` (`mailbox_id`),
50	INDEX `idx_product_id` (`product_id`),
51	INDEX `idx_priority` (`priority`),
52	INDEX `idx_status` (`status`),
53	INDEX `idx_created_at` (`created_at`)
54	) $charsetCollate;";
55	$created = dbDelta($sql);
56	return $created;
57	} else {
58	static::alterTable($table);
59	}
60
61	return false;
62	}
63
64	public static function alterTable($table)
65	{
66	static::addMissingColumns($table);
67	static::addMissingIndexes($table);
68	}
69
70	public static function addMissingColumns($table)
71	{
72	global $wpdb;
73
74	// Escape table name
75	$table = esc_sql($table);
76
77	// Get existing columns
78	$existing_columns = $wpdb->get_col("DESC `$table`", 0);
79
80	// Add waiting_since column if missing (beta user migration)
81	if (!in_array('waiting_since', $existing_columns)) {
82	$query = 'ALTER TABLE `' . $table . '` ADD `waiting_since` TIMESTAMP NULL AFTER `last_customer_response`';
83	$wpdb->query($query);
84	}
85	}
86
87	public static function addMissingIndexes($table)
88	{
89	global $wpdb;
90
91	// Escape table name
92	$table = esc_sql($table);
93
94	// Get existing indexes
95	$existing_indexes = $wpdb->get_results("SHOW INDEX FROM `$table`");
96	$existing_index_names = [];
97
98	foreach ($existing_indexes as $index) {
99	$existing_index_names[] = $index->Key_name;
100	}
101
102	// Desired indexes
103	$indexes = [
104	'idx_customer_id' => 'customer_id',
105	'idx_agent_id' => 'agent_id',
106	'idx_mailbox_id' => 'mailbox_id',
107	'idx_product_id' => 'product_id',
108	'idx_priority' => 'priority',
109	'idx_status' => 'status',
110	'idx_created_at' => 'created_at',
111	];
112
113	// Add missing indexes
114	foreach ($indexes as $index_name => $column_name) {
115	if (!in_array($index_name, $existing_index_names)) {
116	$sql = "ALTER TABLE `$table` ADD INDEX `$index_name` (`$column_name`)";
117	$wpdb->query($sql);
118	}
119	}
120	}
121	}
122