fluent-cart / database / Migrations / ProductVariationMigrator.php

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

240 lines 9.1 KB

Raw Download Zip

1	<?php
2
3	namespace FluentCart\Database\Migrations;
4
5	class ProductVariationMigrator extends Migrator
6	{
7	protected static int $chunkSize = 500;
8	protected static string $taxBackfillCompletionOption = '_fluent_cart_variation_tax_backfill_completed';
9
10	public static string $tableName = 'fct_product_variations';
11
12	public static function getSqlSchema(): string
13	{
14	$indexPrefix = static::getDbPrefix() . 'fct_pd_var_';
15	return "`id` BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
16	`post_id` BIGINT(20) UNSIGNED NOT NULL,
17	`media_id` BIGINT(20) UNSIGNED NULL,
18	`serial_index` INT(5) NULL,
19	`sold_individually` TINYINT(1) UNSIGNED NULL DEFAULT 0,
20	`variation_title` VARCHAR(192) NOT NULL,
21	`variation_identifier` VARCHAR(100) NULL,
22	`sku` VARCHAR(30) NULL DEFAULT NULL,
23	`manage_stock` TINYINT(1) NULL DEFAULT 0,
24	`payment_type` VARCHAR(50) NULL,
25	`stock_status` VARCHAR(30) NULL DEFAULT 'out-of-stock',
26	`backorders` TINYINT(1) UNSIGNED NULL DEFAULT 0,
27	`total_stock` INT(11) NULL DEFAULT 0,
28	`on_hold` INT(11) NULL DEFAULT 0,
29	`committed` INT(11) NULL DEFAULT 0,
30	`available` INT(11) NULL DEFAULT 0,
31	`fulfillment_type` VARCHAR(100) NULL DEFAULT 'physical', /* physicl, digital, service, mixed*/
32	`item_status` VARCHAR(30) NULL DEFAULT 'active',
33	`manage_cost` VARCHAR(30) NULL DEFAULT 'false',
34	`item_price` double DEFAULT 0 NOT NULL,
35	`item_cost` double DEFAULT 0 NOT NULL,
36	`compare_price` double DEFAULT 0 NULL,
37	`shipping_class` BIGINT(20) NULL,
38	`other_info` longtext NULL,
39	`downloadable` VARCHAR(30) NULL DEFAULT 'false',
40	`created_at` DATETIME NULL,
41	`updated_at` DATETIME NULL,
42	INDEX `{$indexPrefix}_post_id_idx` (`post_id` ASC),
43	UNIQUE INDEX `sku_unique` (`sku` ASC),
44	INDEX `{$indexPrefix}_stock_status_idx` (`stock_status` ASC)";
45	}
46
47	public static function migrated()
48	{
49	static::addSkuColumn();
50	static::backfillProductLevelTaxToVariations();
51	}
52
53	public static function addSkuColumn()
54	{
55	// "ALTER TABLE %i ADD COLUMN `sku` VARCHAR(30) NULL DEFAULT NULL AFTER `variation_identifier`"
56	static::addColumnIfNotExists('sku', 'VARCHAR(30) NULL DEFAULT NULL', 'variation_identifier');
57	// "ALTER TABLE %i ADD UNIQUE INDEX `sku_unique` (`sku` ASC)"
58	static::addIndexIfNotExists('sku_unique', 'sku', true);
59	}
60
61	/**
62	* Copy tax settings from product.detail.other_info down to each variation that has
63	* no explicit override. Runs only on variations that are missing the key entirely —
64	* any variation that already carries its own tax_exempt or tax_class is left untouched.
65	*
66	* Idempotent: updates only missing variation keys, and marks completion so future
67	* migration runs do not rescan every product detail row on activation.
68	*/
69	public static function backfillProductLevelTaxToVariations()
70	{
71	if (get_option(static::$taxBackfillCompletionOption) === 'yes') {
72	return;
73	}
74
75	global $wpdb;
76
77	$detailsTable = $wpdb->prefix . 'fct_product_details';
78	$variationsTable = $wpdb->prefix . 'fct_product_variations';
79	$lastId = 0;
80	$taxClassSlugMap = [];
81
82	do {
83	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
84	$rows = $wpdb->get_results($wpdb->prepare(
85	"SELECT `id`, `post_id`, `other_info`
86	FROM `{$detailsTable}`
87	WHERE `id` > %d
88	ORDER BY `id` ASC
89	LIMIT %d",
90	$lastId,
91	static::$chunkSize
92	));
93
94	if (empty($rows)) {
95	break;
96	}
97
98	$qualifyingRows = [];
99	$postIds = [];
100
101	foreach ($rows as $row) {
102	$lastId = (int) $row->id;
103
104	$detailInfo = json_decode($row->other_info, true);
105	if (!is_array($detailInfo)) {
106	continue;
107	}
108
109	$productTaxExempt = isset($detailInfo['tax_exempt']) ? (string) $detailInfo['tax_exempt'] : '';
110	$productTaxClass = isset($detailInfo['tax_class']) ? (string) $detailInfo['tax_class'] : '';
111
112	// Resolve tax_class stored as a numeric ID (product level) to its slug (variation level).
113	$taxClassSlug = static::resolveVariationTaxClassSlug($productTaxClass, $taxClassSlugMap);
114
115	$needsExempt = ($productTaxExempt === 'yes');
116	$needsClass = ($taxClassSlug !== '');
117
118	if (!$needsExempt && !$needsClass) {
119	continue;
120	}
121
122	$postId = (int) $row->post_id;
123	$qualifyingRows[] = [
124	'post_id' => $postId,
125	'needs_exempt' => $needsExempt,
126	'tax_class' => $taxClassSlug,
127	];
128	$postIds[$postId] = $postId;
129	}
130
131	if (empty($qualifyingRows)) {
132	continue;
133	}
134
135	$variationGroups = [];
136	$variationInfoMap = [];
137	$placeholders = implode(', ', array_fill(0, count($postIds), '%d'));
138
139	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
140	$variations = $wpdb->get_results($wpdb->prepare(
141	"SELECT `id`, `post_id`, `other_info`
142	FROM `{$variationsTable}`
143	WHERE `post_id` IN ({$placeholders})",
144	array_values($postIds)
145	));
146
147	foreach ($variations as $variation) {
148	$variationGroups[(int) $variation->post_id][] = $variation;
149	}
150
151	foreach ($qualifyingRows as $qualifyingRow) {
152	$variations = $variationGroups[$qualifyingRow['post_id']] ?? [];
153
154	foreach ($variations as $variation) {
155	$variationId = (int) $variation->id;
156
157	if (!array_key_exists($variationId, $variationInfoMap)) {
158	$varInfo = !empty($variation->other_info)
159	? json_decode($variation->other_info, true)
160	: [];
161
162	if (!is_array($varInfo)) {
163	$varInfo = [];
164	}
165
166	$variationInfoMap[$variationId] = $varInfo;
167	}
168
169	$varInfo = $variationInfoMap[$variationId];
170	$updated = false;
171
172	// Only set tax_exempt when the variation has no explicit value at all.
173	if ($qualifyingRow['needs_exempt'] && !array_key_exists('tax_exempt', $varInfo)) {
174	$varInfo['tax_exempt'] = 'yes';
175	$updated = true;
176	}
177
178	// Only set tax_class when the variation has no explicit value at all.
179	if ($qualifyingRow['tax_class'] !== '' && !array_key_exists('tax_class', $varInfo)) {
180	$varInfo['tax_class'] = $qualifyingRow['tax_class'];
181	$updated = true;
182	}
183
184	if (!$updated) {
185	continue;
186	}
187
188	$variationInfoMap[$variationId] = $varInfo;
189
190	$wpdb->update(
191	$variationsTable,
192	['other_info' => wp_json_encode($varInfo)],
193	['id' => $variationId],
194	['%s'],
195	['%d']
196	);
197	}
198	}
199	} while (count($rows) === static::$chunkSize);
200
201	update_option(static::$taxBackfillCompletionOption, 'yes', 'no');
202	}
203
204	protected static function resolveVariationTaxClassSlug($productTaxClass, array &$taxClassSlugMap): string
205	{
206	if ($productTaxClass === '') {
207	return '';
208	}
209
210	if (is_numeric($productTaxClass)) {
211	$taxClassId = (int) $productTaxClass;
212
213	if (!array_key_exists($taxClassId, $taxClassSlugMap)) {
214	global $wpdb;
215
216	$taxClassTable = $wpdb->prefix . 'fct_tax_classes';
217
218	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
219	$slug = $wpdb->get_var($wpdb->prepare(
220	"SELECT `slug` FROM `{$taxClassTable}` WHERE `id` = %d LIMIT 1",
221	$taxClassId
222	));
223
224	$taxClassSlugMap[$taxClassId] = $slug ? sanitize_key((string) $slug) : '';
225	}
226
227	$productTaxClass = $taxClassSlugMap[$taxClassId];
228	} else {
229	$productTaxClass = sanitize_key($productTaxClass);
230	}
231
232	// 'standard' is already the variation default — nothing to backfill.
233	if ($productTaxClass === 'standard') {
234	return '';
235	}
236
237	return $productTaxClass;
238	}
239	}
240