← All changes | database/Migrations/OrderMetaMigrator.php +220 -1 1.3.20 → 1.6.5 View file →

		@@ -1,10 +1,14 @@
1	1	<?php
2	2
3	3	namespace FluentCart\Database\Migrations;
4	4
	5	+use FluentCart\App\Models\OrderMeta;
	6	+use FluentCart\App\Services\DateTime\DateTime;
	7	+
5	8	class OrderMetaMigrator extends Migrator
6	9	{
	10	+ protected static int $chunkSize = 500;
7	11
8	12	public static string $tableName = 'fct_order_meta';
9	13
10	14	public static function getSqlSchema(): string
		@@ -16,9 +20,10 @@
16	20	`meta_value` LONGTEXT NULL,
17	21	`created_at` DATETIME NULL,
18	22	`updated_at` DATETIME NULL,
19	23
20		- INDEX `{$indexPrefix}_ord_id_idx` (`order_id` ASC)";
	24	+ INDEX `{$indexPrefix}_ord_id_idx` (`order_id` ASC),
	25	+ INDEX `{$indexPrefix}_ord_meta_key_idx` (`order_id` ASC, `meta_key` ASC)";
21	26	}
22	27
23	28	public static function migrated()
24	29	{
		@@ -23,8 +28,11 @@
23	28	public static function migrated()
24	29	{
25	30	static::renameKeyToMetaKey();
26	31	static::renameValueToMetaValue();
	32	+ static::addMetaKeyIndexes();
	33	+ static::migrateVatTaxIdToBusinessInfo();
	34	+ static::migrateVatReverseToBusinessInfo();
27	35	}
28	36
29	37	public static function renameKeyToMetaKey()
30	38	{
		@@ -35,6 +43,217 @@
35	43	public static function renameValueToMetaValue()
36	44	{
37	45	// "ALTER TABLE %i CHANGE `value` `meta_value` LONGTEXT"
38	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	+ }
39	258	}
40	259	}