chatbot / addons / automator / includes / core / database.php

database.php in WPBot – AI ChatBot for Live Support, Lead Generation, WordPress Automation, AI Services 8.7.6, at addons/automator/includes/core/database.php

488 lines 15.7 KB

Raw Download Zip

1	<?php
2	/**
3	* Database handler for WPbot Automator
4	*
5	* @package WPbot_Automator
6	*/
7
8	namespace WPbot_Automator\Core;
9
10	if ( ! defined( 'ABSPATH' ) ) {
11	exit;
12	}
13
14	/**
15	* Database class
16	*/
17	class Database {
18
19	/**
20	* Table names
21	*/
22	const TABLE_WORKFLOWS = 'wpbot_automator_workflows';
23	const TABLE_LOGS = 'wpbot_automator_logs';
24	const TABLE_EMAIL_TEMPLATES = 'wpbot_automator_email_templates';
25	const TABLE_TABLES = 'wpbot_automator_tables';
26	const TABLE_SCHEDULED_TASKS = 'wpbot_automator_scheduled_tasks';
27
28	/**
29	* Install database tables
30	*/
31	public static function install() {
32	global $wpdb;
33
34	$charset_collate = $wpdb->get_charset_collate();
35
36	// Workflows table.
37	$table_workflows = $wpdb->prefix . self::TABLE_WORKFLOWS;
38	$sql_workflows = "CREATE TABLE IF NOT EXISTS {$table_workflows} (
39	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
40	name varchar(255) NOT NULL,
41	description text,
42	status varchar(20) NOT NULL DEFAULT 'active',
43	workflow_data longtext NOT NULL,
44	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
45	updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
46	PRIMARY KEY (id),
47	KEY status (status)
48	) {$charset_collate};";
49
50	// Email templates table.
51	$table_templates = $wpdb->prefix . self::TABLE_EMAIL_TEMPLATES;
52	$sql_templates = "CREATE TABLE IF NOT EXISTS {$table_templates} (
53	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
54	name varchar(255) NOT NULL,
55	subject varchar(255),
56	body_html longtext,
57	body_json longtext,
58	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
59	updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
60	PRIMARY KEY (id)
61	) {$charset_collate};";
62
63	// Logs table.
64	$table_logs = $wpdb->prefix . self::TABLE_LOGS;
65	$sql_logs = "CREATE TABLE IF NOT EXISTS {$table_logs} (
66	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
67	workflow_id bigint(20) unsigned NOT NULL,
68	trigger_type varchar(100),
69	action_id varchar(100) DEFAULT '',
70	status varchar(20) NOT NULL DEFAULT 'success',
71	log_data longtext,
72	error_message text,
73	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
74	PRIMARY KEY (id),
75	KEY workflow_id (workflow_id),
76	KEY status (status)
77	) {$charset_collate};";
78
79	// Tables meta (stores user-defined table schemas).
80	$table_tables = $wpdb->prefix . self::TABLE_TABLES;
81	$sql_tables = "CREATE TABLE IF NOT EXISTS {$table_tables} (
82	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
83	name varchar(255) NOT NULL,
84	slug varchar(60) NOT NULL,
85	columns_json longtext NOT NULL,
86	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
87	PRIMARY KEY (id),
88	UNIQUE KEY slug (slug)
89	) {$charset_collate};";
90
91	// Scheduled tasks table (used by Delay action).
92	$table_scheduled = $wpdb->prefix . self::TABLE_SCHEDULED_TASKS;
93	$sql_scheduled = "CREATE TABLE IF NOT EXISTS {$table_scheduled} (
94	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
95	workflow_id bigint(20) unsigned NOT NULL,
96	trigger_id varchar(100) NOT NULL DEFAULT '',
97	trigger_data longtext NOT NULL,
98	remaining_nodes longtext NOT NULL,
99	connections longtext NOT NULL,
100	scheduled_at datetime NOT NULL,
101	status varchar(20) NOT NULL DEFAULT 'pending',
102	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
103	PRIMARY KEY (id),
104	KEY workflow_id (workflow_id),
105	KEY status (status),
106	KEY scheduled_at (scheduled_at)
107	) {$charset_collate};";
108
109	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
110	dbDelta( $sql_workflows );
111	dbDelta( $sql_logs );
112	dbDelta( $sql_templates );
113	dbDelta( $sql_tables );
114	dbDelta( $sql_scheduled );
115
116	// Store database version.
117	update_option( 'wpbot_automator_db_version', WPBOT_AUTOMATOR_VERSION );
118
119	self::seed_default_email_templates();
120	}
121
122	/**
123	* Insert one pre-built email template on first install.
124	* Guarded by an option flag so it only runs once.
125	*/
126	public static function seed_default_email_templates() {
127	if ( get_option( 'wpbot_automator_email_templates_seeded' ) ) {
128	return;
129	}
130
131	global $wpdb;
132	$table = self::get_email_templates_table();
133
134	// If the user already has templates (e.g. upgrading), just mark as done.
135	$count = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" );
136	if ( $count > 0 ) {
137	update_option( 'wpbot_automator_email_templates_seeded', '1' );
138	return;
139	}
140
141	$blocks = array(
142	array(
143	'id' => 'b1',
144	'type' => 'text',
145	'content' => '<h1 style="margin:0 0 8px;font-size:28px;color:#1a1a2e;">Hello, {name}! 👋</h1><p style="margin:0;font-size:16px;color:#555555;">Thank you for being with us. Here\'s a message just for you.</p>',
146	),
147	array(
148	'id' => 'b2',
149	'type' => 'divider',
150	'content' => '',
151	'styles' => array( 'borderTop' => '1px solid #e5e7eb', 'margin' => '20px 0' ),
152	),
153	array(
154	'id' => 'b3',
155	'type' => 'text',
156	'content' => '<p style="font-size:15px;color:#444444;line-height:1.7;">We wanted to reach out with an important update. If you have any questions or need assistance, don\'t hesitate to get in touch — we\'re always here to help.</p>',
157	),
158	array(
159	'id' => 'b4',
160	'type' => 'button',
161	'content' => 'Visit Our Website',
162	'styles' => array(
163	'backgroundColor' => '#3b82f6',
164	'color' => '#ffffff',
165	'padding' => '12px 28px',
166	'borderRadius' => '6px',
167	'url' => '#',
168	),
169	),
170	array(
171	'id' => 'b5',
172	'type' => 'divider',
173	'content' => '',
174	'styles' => array( 'borderTop' => '1px solid #e5e7eb', 'margin' => '24px 0' ),
175	),
176	array(
177	'id' => 'b6',
178	'type' => 'text',
179	'content' => '<p style="font-size:13px;color:#9ca3af;text-align:center;margin:0;">You\'re receiving this because you\'re a valued customer.<br>© ' . gmdate( 'Y' ) . ' Your Brand. All rights reserved.</p>',
180	),
181	);
182
183	$wpdb->insert(
184	$table,
185	array(
186	'name' => 'Welcome Email',
187	'subject' => 'Welcome, {name}!',
188	'body_html' => self::render_blocks_to_html( $blocks ),
189	'body_json' => wp_json_encode( $blocks ),
190	)
191	);
192
193	update_option( 'wpbot_automator_email_templates_seeded', '1' );
194	}
195
196	/**
197	* Render block array to the same HTML structure the front-end generateHTML() produces.
198	*
199	* @param array $blocks
200	* @return string
201	*/
202	private static function render_blocks_to_html( array $blocks ) {
203	$inner = '';
204	foreach ( $blocks as $block ) {
205	switch ( $block['type'] ) {
206	case 'text':
207	$inner .= '<div class="block-text">' . $block['content'] . '</div>';
208	break;
209	case 'button':
210	$s = $block['styles'];
211	$inner .= '<div style="text-align:center;margin:20px 0;">'
212	. '<a href="' . esc_attr( $s['url'] ) . '" class="btn" style="'
213	. 'background-color:' . esc_attr( $s['backgroundColor'] ) . ';'
214	. 'color:' . esc_attr( $s['color'] ) . ';'
215	. 'padding:' . esc_attr( $s['padding'] ) . ';'
216	. 'border-radius:' . esc_attr( $s['borderRadius'] ) . ';">'
217	. esc_html( $block['content'] )
218	. '</a></div>';
219	break;
220	case 'image':
221	$inner .= '<div style="margin:20px 0;"><img src="' . esc_attr( $block['content'] ) . '" alt="Image"></div>';
222	break;
223	case 'divider':
224	$inner .= '<hr style="border:none;border-top:' . esc_attr( $block['styles']['borderTop'] ) . ';margin:' . esc_attr( $block['styles']['margin'] ) . ';">';
225	break;
226	case 'spacer':
227	$inner .= '<div style="height:' . esc_attr( $block['styles']['height'] ) . ';"></div>';
228	break;
229	}
230	}
231
232	return '<!DOCTYPE html><html><head><meta charset="utf-8"><style>'
233	. 'body{font-family:-apple-system,BlinkMacSystemFont,"Segoe UI",Roboto,Helvetica,Arial,sans-serif;line-height:1.6;color:#333;margin:0;padding:0;background-color:#f4f7f9;}'
234	. '.container{max-width:600px;margin:40px auto;background:#ffffff;border-radius:8px;overflow:hidden;box-shadow:0 4px 6px rgba(0,0,0,0.05);}'
235	. '.content{padding:40px;}'
236	. '.btn{display:inline-block;text-decoration:none;font-weight:bold;text-align:center;}'
237	. 'img{max-width:100%;height:auto;display:block;}'
238	. '@media only screen and (max-width:600px){.container{margin:0;border-radius:0;}}'
239	. '</style></head><body>'
240	. '<div class="container"><div class="content">' . $inner . '</div>'
241	. '<div style="background:#f9fafb;padding:20px;text-align:center;font-size:12px;color:#6b7280;border-top:1px solid #f3f4f6;">Sent by Your Website</div>'
242	. '</div></body></html>';
243	}
244
245	/**
246	* Get workflows table name
247	*
248	* @return string
249	*/
250	public static function get_workflows_table() {
251	global $wpdb;
252	return $wpdb->prefix . self::TABLE_WORKFLOWS;
253	}
254
255	/**
256	* Get logs table name
257	*
258	* @return string
259	*/
260	public static function get_logs_table() {
261	global $wpdb;
262	return $wpdb->prefix . self::TABLE_LOGS;
263	}
264
265	/**
266	* Get email templates table name
267	*
268	* @return string
269	*/
270	public static function get_email_templates_table() {
271	global $wpdb;
272	return $wpdb->prefix . self::TABLE_EMAIL_TEMPLATES;
273	}
274
275	/**
276	* Get tables meta table name
277	*
278	* @return string
279	*/
280	public static function get_tables_table() {
281	global $wpdb;
282	return $wpdb->prefix . self::TABLE_TABLES;
283	}
284
285	/**
286	* Get scheduled tasks table name.
287	*
288	* @return string
289	*/
290	public static function get_scheduled_tasks_table() {
291	global $wpdb;
292	return $wpdb->prefix . self::TABLE_SCHEDULED_TASKS;
293	}
294
295	/**
296	* Insert a scheduled task checkpoint.
297	*
298	* @param array $data {
299	* @type int $workflow_id
300	* @type string $trigger_id
301	* @type array $trigger_data
302	* @type array $remaining_nodes
303	* @type array $connections
304	* @type string $scheduled_at MySQL datetime string.
305	* }
306	* @return int\|false Inserted row ID or false on failure.
307	*/
308	public static function add_scheduled_task( $data ) {
309	global $wpdb;
310	$table = self::get_scheduled_tasks_table();
311
312	$result = $wpdb->insert(
313	$table,
314	array(
315	'workflow_id' => absint( $data['workflow_id'] ),
316	'trigger_id' => sanitize_text_field( $data['trigger_id'] ),
317	'trigger_data' => wp_json_encode( $data['trigger_data'] ),
318	'remaining_nodes' => wp_json_encode( $data['remaining_nodes'] ),
319	'connections' => wp_json_encode( $data['connections'] ),
320	'scheduled_at' => $data['scheduled_at'],
321	'status' => 'pending',
322	),
323	array( '%d', '%s', '%s', '%s', '%s', '%s', '%s' )
324	);
325
326	return $result ? $wpdb->insert_id : false;
327	}
328
329	/**
330	* Get a scheduled task by ID.
331	*
332	* @param int $task_id
333	* @return array\|null
334	*/
335	public static function get_scheduled_task( $task_id ) {
336	global $wpdb;
337	$table = self::get_scheduled_tasks_table();
338	$row = $wpdb->get_row(
339	$wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", absint( $task_id ) ),
340	ARRAY_A
341	);
342	if ( ! $row ) {
343	return null;
344	}
345	$row['trigger_data'] = json_decode( $row['trigger_data'], true );
346	$row['remaining_nodes'] = json_decode( $row['remaining_nodes'], true );
347	$row['connections'] = json_decode( $row['connections'], true );
348	return $row;
349	}
350
351	/**
352	* Mark a scheduled task as completed.
353	*
354	* @param int $task_id
355	*/
356	public static function complete_scheduled_task( $task_id ) {
357	global $wpdb;
358	$table = self::get_scheduled_tasks_table();
359	$wpdb->update(
360	$table,
361	array( 'status' => 'completed' ),
362	array( 'id' => absint( $task_id ) ),
363	array( '%s' ),
364	array( '%d' )
365	);
366	}
367
368	/**
369	* Delete a scheduled task by ID.
370	*
371	* @param int $task_id
372	*/
373	public static function delete_scheduled_task( $task_id ) {
374	global $wpdb;
375	$table = self::get_scheduled_tasks_table();
376	$wpdb->delete( $table, array( 'id' => absint( $task_id ) ), array( '%d' ) );
377	}
378
379	/**
380	* Run schema upgrades for existing installs (adds columns that didn't exist at activation).
381	*/
382	public static function maybe_upgrade() {
383	global $wpdb;
384	$table = $wpdb->prefix . self::TABLE_LOGS;
385	if ( ! $wpdb->get_var( "SHOW TABLES LIKE '{$table}'" ) ) {
386	return;
387	}
388
389	self::seed_default_email_templates();
390
391	// Ensure tables meta table exists for existing installs.
392	$charset_collate = $wpdb->get_charset_collate();
393	$table_tables = $wpdb->prefix . self::TABLE_TABLES;
394	$sql_tables = "CREATE TABLE IF NOT EXISTS {$table_tables} (
395	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
396	name varchar(255) NOT NULL,
397	slug varchar(60) NOT NULL,
398	columns_json longtext NOT NULL,
399	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
400	PRIMARY KEY (id),
401	UNIQUE KEY slug (slug)
402	) {$charset_collate};";
403	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
404	dbDelta( $sql_tables );
405
406	// Ensure scheduled tasks table exists for existing installs.
407	$table_scheduled = $wpdb->prefix . self::TABLE_SCHEDULED_TASKS;
408	$sql_scheduled = "CREATE TABLE IF NOT EXISTS {$table_scheduled} (
409	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
410	workflow_id bigint(20) unsigned NOT NULL,
411	trigger_id varchar(100) NOT NULL DEFAULT '',
412	trigger_data longtext NOT NULL,
413	remaining_nodes longtext NOT NULL,
414	connections longtext NOT NULL,
415	scheduled_at datetime NOT NULL,
416	status varchar(20) NOT NULL DEFAULT 'pending',
417	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
418	PRIMARY KEY (id),
419	KEY workflow_id (workflow_id),
420	KEY status (status),
421	KEY scheduled_at (scheduled_at)
422	) {$charset_collate};";
423	dbDelta( $sql_scheduled );
424
425	$col = $wpdb->get_results( "SHOW COLUMNS FROM `{$table}` LIKE 'action_id'" );
426	if ( empty( $col ) ) {
427	$wpdb->query( "ALTER TABLE `{$table}` ADD COLUMN `action_id` varchar(100) DEFAULT '' AFTER `trigger_type`" );
428	}
429	$key = $wpdb->get_results( "SHOW INDEX FROM `{$table}` WHERE Key_name = 'status'" );
430	if ( empty( $key ) ) {
431	$wpdb->query( "ALTER TABLE `{$table}` ADD INDEX `status` (`status`)" );
432	}
433	}
434
435	/**
436	* Add a log entry
437	*
438	* @param array $data Log data.
439	* @return int\|false
440	*/
441	public static function add_log( $data ) {
442	global $wpdb;
443	$table = self::get_logs_table();
444
445	$defaults = array(
446	'workflow_id' => 0,
447	'trigger_type' => '',
448	'action_id' => '',
449	'status' => 'success',
450	'log_data' => '',
451	'error_message' => '',
452	'created_at' => current_time( 'mysql' ),
453	);
454
455	$data = wp_parse_args( $data, $defaults );
456
457	// Diagnostic check for non-scalar values before encoding.
458	foreach ( array( 'trigger_type', 'status', 'error_message' ) as $key ) {
459	if ( ! empty( $data[ $key ] ) && ! is_scalar( $data[ $key ] ) ) {
460	//error_log( sprintf( 'WPBOT-AUTOMATOR-DIAGNOSTIC: Non-scalar value detected for field "%s" in add_log. Type: %s. Value: %s', $key, gettype( $data[ $key ] ), wp_json_encode( $data[ $key ] ) ) );
461	//error_log( 'BACKTRACE: ' . wp_debug_backtrace_summary() );
462	}
463	}
464
465	$result = $wpdb->insert(
466	$table,
467	array(
468	'workflow_id' => absint( $data['workflow_id'] ),
469	'trigger_type' => sanitize_text_field( ! is_scalar( $data['trigger_type'] ) ? wp_json_encode( $data['trigger_type'] ) : strval( $data['trigger_type'] ) ),
470	'action_id' => sanitize_text_field( ! is_scalar( $data['action_id'] ) ? '' : strval( $data['action_id'] ) ),
471	'status' => sanitize_text_field( ! is_scalar( $data['status'] ) ? wp_json_encode( $data['status'] ) : strval( $data['status'] ) ),
472	'log_data' => is_scalar( $data['log_data'] ) ? strval( $data['log_data'] ) : wp_json_encode( $data['log_data'] ),
473	'error_message' => sanitize_textarea_field( ! is_scalar( $data['error_message'] ) ? wp_json_encode( $data['error_message'] ) : strval( $data['error_message'] ) ),
474	'created_at' => sanitize_text_field( ! is_scalar( $data['created_at'] ) ? wp_json_encode( $data['created_at'] ) : strval( $data['created_at'] ) ),
475	),
476	array( '%d', '%s', '%s', '%s', '%s', '%s', '%s' )
477	);
478
479	if ( false === $result ) {
480	error_log( 'WPbot Automator - Database::add_log failed. Table: ' . $table . '. DB Error: ' . $wpdb->last_error );
481	} else {
482	error_log( 'WPbot Automator - Database::add_log success. ID: ' . $wpdb->insert_id );
483	}
484
485	return $result ? $wpdb->insert_id : false;
486	}
487	}
488