| @@ -1,487 +1,487 @@ | ||
| 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 | -} | |
| 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 | +} | |
