fluent-cart / database / Migrations / OrderMetaMigrator.php

OrderMetaMigrator.php in FluentCart A New Era of eCommerce – Faster, Lighter, and Simpler 1.6.4, at database/Migrations/OrderMetaMigrator.php

260 lines 9.1 KB

Raw Download Zip

1	<?php
2
3	namespace FluentCart\Database\Migrations;
4
5	use FluentCart\App\Models\OrderMeta;
6	use FluentCart\App\Services\DateTime\DateTime;
7
8	class OrderMetaMigrator extends Migrator
9	{
10	protected static int $chunkSize = 500;
11
12	public static string $tableName = 'fct_order_meta';
13
14	public static function getSqlSchema(): string
15	{
16	$indexPrefix = static::getDbPrefix() . 'fct_om_';
17	return "`id` BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
18	`order_id` BIGINT(20) NULL,
19	`meta_key` VARCHAR(192) NOT NULL,
20	`meta_value` LONGTEXT NULL,
21	`created_at` DATETIME NULL,
22	`updated_at` DATETIME NULL,
23
24	INDEX `{$indexPrefix}_ord_id_idx` (`order_id` ASC),
25	INDEX `{$indexPrefix}_ord_meta_key_idx` (`order_id` ASC, `meta_key` ASC)";
26	}
27
28	public static function migrated()
29	{
30	static::renameKeyToMetaKey();
31	static::renameValueToMetaValue();
32	static::addMetaKeyIndexes();
33	static::migrateVatTaxIdToBusinessInfo();
34	static::migrateVatReverseToBusinessInfo();
35	}
36
37	public static function renameKeyToMetaKey()
38	{
39	// "ALTER TABLE %i CHANGE `key` `meta_key` VARCHAR(192)"
40	static::renameColumnIfExists('key', 'meta_key', 'VARCHAR(192)');
41	}
42
43	public static function renameValueToMetaValue()
44	{
45	// "ALTER TABLE %i CHANGE `value` `meta_value` LONGTEXT"
46	static::renameColumnIfExists('value', 'meta_value', 'LONGTEXT');
47	}
48
49	public static function addMetaKeyIndexes()
50	{
51	$indexPrefix = static::getDbPrefix() . 'fct_om_';
52
53	static::addIndexIfNotExists("{$indexPrefix}_ord_meta_key_idx", ['order_id', 'meta_key']);
54	}
55
56	/**
57	* Convert legacy `vat_tax_id` rows to the unified `business_info` structure.
58	*
59	* Old path: TaxModule::storeVatNumberOnOrder() wrote one string row per order:
60	* meta_key = 'vat_tax_id', meta_value = '<vat-number-string>'
61	*
62	* New path: TaxModule::storeBusinessInfoOnOrder() writes:
63	* meta_key = 'business_info', meta_value = JSON { company_name, legal_registration_id, tax_number, ... }
64	*
65	* This migration is idempotent: if a `business_info` row already exists for an order
66	* it merges the old VAT number in only when `tax_number` is absent.
67	*/
68	public static function migrateVatTaxIdToBusinessInfo()
69	{
70	global $wpdb;
71	$table = $wpdb->prefix . static::$tableName;
72	$lastId = 0;
73
74	do {
75	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
76	$rows = $wpdb->get_results($wpdb->prepare(
77	"SELECT `id`, `order_id`, `meta_value`
78	FROM `{$table}`
79	WHERE `meta_key` = 'vat_tax_id' AND `id` > %d
80	ORDER BY `id` ASC
81	LIMIT %d",
82	$lastId,
83	static::$chunkSize
84	));
85
86	if (empty($rows)) {
87	break;
88	}
89
90	$payloadByOrderId = [];
91	foreach ($rows as $row) {
92	$lastId = (int) $row->id;
93	$vatNumber = $row->meta_value;
94
95	// meta_value may be JSON-encoded by the model setter
96	if (is_string($vatNumber)) {
97	$decoded = json_decode($vatNumber, true);
98	if (is_string($decoded)) {
99	$vatNumber = $decoded;
100	}
101	}
102
103	$vatNumber = sanitize_text_field((string) $vatNumber);
104
105	if (empty($vatNumber)) {
106	continue;
107	}
108
109	if (!isset($payloadByOrderId[$row->order_id])) {
110	$payloadByOrderId[$row->order_id] = [
111	'tax_number' => $vatNumber,
112	'tax_number_validated' => false,
113	'tax_number_country' => '',
114	];
115	}
116	}
117
118	if (!empty($payloadByOrderId)) {
119	static::syncBusinessInfoChunk(array_keys($payloadByOrderId), $payloadByOrderId);
120	}
121	} while (count($rows) === static::$chunkSize);
122	}
123
124	/**
125	* Migrate EU-scoped VAT data from fct_order_tax_rate.meta.vat_reverse
126	* into the unified fct_order_meta.business_info row.
127	*
128	* Old path: TaxModule::prepareOtherData() wrote vat_reverse into the tax rate
129	* meta only when tax_data.valid === true (EU VIES-validated VAT). This meant
130	* validated EU VAT numbers were only readable from the tax rate table, not from
131	* a common order-level location.
132	*
133	* New path: business_info in fct_order_meta holds all tax identity fields for
134	* every country, with tax_number_validated=true marking a VIES-verified number.
135	*
136	* Idempotent: skips an order if business_info.tax_number is already populated.
137	*/
138	public static function migrateVatReverseToBusinessInfo()
139	{
140	global $wpdb;
141	$taxRateTable = $wpdb->prefix . 'fct_order_tax_rate';
142	$processed = [];
143	$lastId = 0;
144
145	do {
146	// LIKE filter is safe here — vat_reverse only ever appears as a JSON key written
147	// by TaxModule::prepareOtherData(), never as a value, so no false positives.
148	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
149	$rows = $wpdb->get_results($wpdb->prepare(
150	"SELECT `id`, `order_id`, `meta`
151	FROM `{$taxRateTable}`
152	WHERE `meta` LIKE '%%\"vat_reverse\"%%' AND `id` > %d
153	ORDER BY `id` ASC
154	LIMIT %d",
155	$lastId,
156	static::$chunkSize
157	));
158
159	if (empty($rows)) {
160	break;
161	}
162
163	$payloadByOrderId = [];
164	foreach ($rows as $row) {
165	$lastId = (int) $row->id;
166
167	// One business_info row per order — process only the first tax rate row that carries vat_reverse
168	if (isset($processed[$row->order_id])) {
169	continue;
170	}
171	$processed[$row->order_id] = true;
172
173	$meta = json_decode($row->meta, true);
174	if (empty($meta['vat_reverse']) \|\| !is_array($meta['vat_reverse'])) {
175	continue;
176	}
177
178	$vatReverse = $meta['vat_reverse'];
179	$vatNumber = sanitize_text_field((string) (isset($vatReverse['vat_number']) ? $vatReverse['vat_number'] : ''));
180
181	if (empty($vatNumber)) {
182	continue;
183	}
184
185	$vatCountry = sanitize_text_field((string) (isset($vatReverse['country']) ? $vatReverse['country'] : ''));
186	$vatName = sanitize_text_field((string) (isset($vatReverse['name']) ? $vatReverse['name'] : ''));
187
188	$payloadByOrderId[$row->order_id] = [
189	'tax_number' => $vatNumber,
190	'tax_number_validated' => true,
191	'tax_number_country' => $vatCountry,
192	'tax_number_name' => $vatName,
193	];
194	}
195
196	if (!empty($payloadByOrderId)) {
197	static::syncBusinessInfoChunk(array_keys($payloadByOrderId), $payloadByOrderId);
198	}
199	} while (count($rows) === static::$chunkSize);
200	}
201
202	protected static function syncBusinessInfoChunk(array $orderIds, array $payloadByOrderId)
203	{
204	if (empty($orderIds) \|\| empty($payloadByOrderId)) {
205	return;
206	}
207
208	$timestamp = DateTime::gmtNow()->format('Y-m-d H:i:s');
209	$existingRows = OrderMeta::query()
210	->select(['id', 'order_id', 'meta_value'])
211	->where('meta_key', 'business_info')
212	->whereIn('order_id', $orderIds)
213	->get();
214
215	$updates = [];
216	$existingByOrderId = [];
217
218	foreach ($existingRows as $existingRow) {
219	$existingByOrderId[$existingRow->order_id] = $existingRow;
220	}
221
222	$inserts = [];
223	foreach ($payloadByOrderId as $orderId => $payload) {
224	if (isset($existingByOrderId[$orderId])) {
225	$businessInfo = $existingByOrderId[$orderId]->meta_value;
226	if (!is_array($businessInfo)) {
227	$businessInfo = [];
228	}
229
230	if (!empty($businessInfo['tax_number'])) {
231	continue;
232	}
233
234	$updates[] = [
235	'id' => $existingByOrderId[$orderId]->id,
236	'meta_value' => wp_json_encode(array_merge($businessInfo, $payload)),
237	'updated_at' => $timestamp,
238	];
239	continue;
240	}
241
242	$inserts[] = [
243	'order_id' => $orderId,
244	'meta_key' => 'business_info',
245	'meta_value' => wp_json_encode($payload),
246	'created_at' => $timestamp,
247	'updated_at' => $timestamp,
248	];
249	}
250
251	if (!empty($inserts)) {
252	OrderMeta::query()->insert($inserts);
253	}
254
255	if (!empty($updates)) {
256	OrderMeta::query()->batchUpdate($updates);
257	}
258	}
259	}
260