superfrete / database / webhook_migrations.php

webhook_migrations.php in SuperFrete trunk, at database/webhook_migrations.php

158 lines 4.9 KB

Raw Download Zip

1	<?php
2
3	namespace SuperFrete_API\Database;
4
5	if (!defined('ABSPATH')) {
6	exit; // Security check
7	}
8
9	class WebhookMigrations
10	{
11
12	/**
13	* Create webhook retry queue table
14	*/
15	public static function create_webhook_retry_table()
16	{
17	global $wpdb;
18
19	$table_name = $wpdb->prefix . 'superfrete_webhook_retries';
20
21	$charset_collate = $wpdb->get_charset_collate();
22
23	$sql = "CREATE TABLE $table_name (
24	id mediumint(9) NOT NULL AUTO_INCREMENT,
25	order_id bigint(20) NOT NULL,
26	superfrete_id varchar(255) NOT NULL,
27	event_type varchar(50) NOT NULL,
28	payload longtext NOT NULL,
29	retry_count tinyint(4) NOT NULL DEFAULT 0,
30	max_retries tinyint(4) NOT NULL DEFAULT 3,
31	next_retry_at datetime DEFAULT NULL,
32	status varchar(20) NOT NULL DEFAULT 'pending',
33	error_message text DEFAULT NULL,
34	created_at datetime DEFAULT CURRENT_TIMESTAMP,
35	updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
36	PRIMARY KEY (id),
37	KEY order_id (order_id),
38	KEY superfrete_id (superfrete_id),
39	KEY status (status),
40	KEY next_retry_at (next_retry_at)
41	) $charset_collate;";
42
43	require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
44	dbDelta($sql);
45
46	// Store table version for future migrations
47	update_option('superfrete_webhook_table_version', '1.0');
48	}
49
50	/**
51	* Create webhook logs table
52	*/
53	public static function create_webhook_logs_table()
54	{
55	global $wpdb;
56
57	$table_name = $wpdb->prefix . 'superfrete_webhook_logs';
58
59	$charset_collate = $wpdb->get_charset_collate();
60
61	$sql = "CREATE TABLE $table_name (
62	id mediumint(9) NOT NULL AUTO_INCREMENT,
63	webhook_id varchar(255) DEFAULT NULL,
64	order_id bigint(20) DEFAULT NULL,
65	superfrete_id varchar(255) DEFAULT NULL,
66	event_type varchar(50) NOT NULL,
67	status varchar(20) NOT NULL,
68	http_status_code smallint(6) DEFAULT NULL,
69	payload longtext DEFAULT NULL,
70	response_data longtext DEFAULT NULL,
71	error_message text DEFAULT NULL,
72	processing_time_ms int(11) DEFAULT NULL,
73	created_at datetime DEFAULT CURRENT_TIMESTAMP,
74	PRIMARY KEY (id),
75	KEY order_id (order_id),
76	KEY superfrete_id (superfrete_id),
77	KEY event_type (event_type),
78	KEY status (status),
79	KEY created_at (created_at)
80	) $charset_collate;";
81
82	require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
83	dbDelta($sql);
84	}
85
86	/**
87	* Run all migrations
88	*/
89	public static function run_migrations()
90	{
91	self::create_webhook_retry_table();
92	self::create_webhook_logs_table();
93
94	// Initialize webhook options if they don't exist
95	if (!get_option('superfrete_webhook_secret')) {
96	update_option('superfrete_webhook_secret', wp_generate_password(32, false));
97	}
98
99	if (!get_option('superfrete_webhook_registered')) {
100	update_option('superfrete_webhook_registered', 'no');
101	}
102
103	if (!get_option('superfrete_webhook_url')) {
104	update_option('superfrete_webhook_url', '');
105	}
106	}
107
108	/**
109	* Clean up old webhook logs (older than 30 days)
110	*/
111	public static function cleanup_old_logs()
112	{
113	global $wpdb;
114
115	$logs_table = $wpdb->prefix . 'superfrete_webhook_logs';
116	$retries_table = $wpdb->prefix . 'superfrete_webhook_retries';
117
118	// Delete logs older than 30 days
119	$wpdb->query($wpdb->prepare(
120	"DELETE FROM $logs_table WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY)"
121	));
122
123	// Delete completed or failed retry records older than 7 days
124	$wpdb->query($wpdb->prepare(
125	"DELETE FROM $retries_table WHERE status IN ('completed', 'failed') AND updated_at < DATE_SUB(NOW(), INTERVAL 7 DAY)"
126	));
127	}
128
129	/**
130	* Drop webhook tables (for uninstallation)
131	*/
132	public static function drop_tables()
133	{
134	global $wpdb;
135
136	$tables = [
137	$wpdb->prefix . 'superfrete_webhook_retries',
138	$wpdb->prefix . 'superfrete_webhook_logs'
139	];
140
141	foreach ($tables as $table) {
142	$wpdb->query("DROP TABLE IF EXISTS $table");
143	}
144
145	// Clean up options
146	$options = [
147	'superfrete_webhook_secret',
148	'superfrete_webhook_registered',
149	'superfrete_webhook_url',
150	'superfrete_webhook_table_version',
151	'superfrete_webhook_migrated'
152	];
153
154	foreach ($options as $option) {
155	delete_option($option);
156	}
157	}
158	}