fluent-cart / database / Migrations / TaxClassesMigrator.php

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

176 lines 5.7 KB

Raw Download Zip

1	<?php
2
3	namespace FluentCart\Database\Migrations;
4
5
6	use FluentCart\App\Models\TaxClass;
7	use FluentCart\Framework\Database\Schema;
8	use FluentCart\Framework\Support\Str;
9
10	class TaxClassesMigrator extends Migrator
11	{
12
13	public static string $tableName = 'fct_tax_classes';
14
15	public static function getSqlSchema(): string
16	{
17	$indexPrefix = static::getDbPrefix() . 'fct_tcl_';
18	return "`id` BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
19	`title` VARCHAR(192) NULL,
20	`slug` VARCHAR(100) NULL,
21	`description` longtext NULL,
22	`meta` json DEFAULT NULL,
23	`created_at` DATETIME NULL ,
24	`updated_at` DATETIME NULL";
25	}
26
27	public static function migrated()
28	{
29	static::renameCategoriesToMeta();
30	static::addMetaColumn();
31	static::addSlugColumn();
32	static::addDescriptionColumn();
33	static::backfillNullSlugs();
34	static::deduplicateSlugs();
35	static::addSlugUniqueIndex();
36	static::seedDefaultTaxClass();
37	}
38
39	public static function renameCategoriesToMeta()
40	{
41	// "ALTER TABLE %i CHANGE `categories` `meta` JSON"
42	// Only rename if categories exists and meta doesn't (avoid collision)
43	if (Schema::hasColumn('categories', static::$tableName) && !Schema::hasColumn('meta', static::$tableName)) {
44	Schema::alterTable(
45	static::$tableName,
46	"CHANGE `categories` `meta` JSON"
47	);
48	}
49	}
50
51	public static function addMetaColumn()
52	{
53	// "ALTER TABLE %i ADD COLUMN `meta` JSON"
54	static::addColumnIfNotExists('meta', 'JSON');
55	}
56
57	public static function addSlugColumn()
58	{
59	// "ALTER TABLE %i ADD COLUMN `slug` VARCHAR(100) NULL AFTER `title`"
60	static::addColumnIfNotExists('slug', 'VARCHAR(100) NULL', 'title');
61	}
62
63	public static function addDescriptionColumn()
64	{
65	// "ALTER TABLE %i ADD COLUMN `description` LONGTEXT NULL AFTER `slug`"
66	static::addColumnIfNotExists('description', 'LONGTEXT NULL', 'slug');
67	}
68
69	/**
70	* Backfill NULL slugs for rows created before the slug column existed.
71	*/
72	public static function backfillNullSlugs()
73	{
74	$rows = TaxClass::query()->whereNull('slug')->get();
75
76	foreach ($rows as $row) {
77	$base = Str::slug($row->title);
78	if (!$base) {
79	$base = 'tax-class';
80	}
81
82	$slug = $base;
83	$suffix = 2;
84
85	while (TaxClass::query()->where('slug', $slug)->where('id', '!=', $row->id)->exists()) {
86	$slug = $base . '-' . $suffix;
87	$suffix++;
88	}
89
90	TaxClass::query()->where('id', $row->id)->update(['slug' => $slug]);
91	}
92	}
93
94	/**
95	* Remove duplicate slug rows, keeping the one with the lowest ID.
96	* Repoints `fct_tax_rates.class_id` references to the kept row first
97	* to avoid orphaning tax rates.
98	*/
99	public static function deduplicateSlugs()
100	{
101	global $wpdb;
102	$table = $wpdb->prefix . static::$tableName;
103	$ratesTable = $wpdb->prefix . 'fct_tax_rates';
104
105	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
106	$duplicates = $wpdb->get_results(
107	"SELECT `slug`, MIN(`id`) AS keep_id, COUNT(*) AS cnt FROM `{$table}` WHERE `slug` IS NOT NULL GROUP BY `slug` HAVING cnt > 1"
108	);
109
110	if (empty($duplicates)) {
111	return;
112	}
113
114	foreach ($duplicates as $dup) {
115	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
116	$deleteIds = $wpdb->get_col($wpdb->prepare(
117	"SELECT `id` FROM `{$table}` WHERE `slug` = %s AND `id` != %d",
118	$dup->slug,
119	$dup->keep_id
120	));
121
122	if (empty($deleteIds)) {
123	continue;
124	}
125
126	$placeholders = implode(',', array_fill(0, count($deleteIds), '%d'));
127
128	// Repoint tax rates to the kept class so rates aren't orphaned.
129	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
130	$wpdb->query($wpdb->prepare(
131	"UPDATE `{$ratesTable}` SET `class_id` = %d WHERE `class_id` IN ({$placeholders})",
132	array_merge([$dup->keep_id], $deleteIds)
133	));
134
135	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
136	$wpdb->query($wpdb->prepare(
137	"DELETE FROM `{$table}` WHERE `id` IN ({$placeholders})",
138	$deleteIds
139	));
140	}
141	}
142
143	/**
144	* Add a UNIQUE index on the slug column.
145	*/
146	public static function addSlugUniqueIndex()
147	{
148	$indexName = static::getDbPrefix() . 'fct_tcl_slug_unq';
149	static::addIndexIfNotExists($indexName, 'slug', true);
150	}
151
152	public static function seedDefaultTaxClass()
153	{
154	global $wpdb;
155
156	if (TaxClass::query()->where('slug', 'standard')->exists()) {
157	return;
158	}
159
160	$table = static::getTableName();
161
162	// INSERT IGNORE so a concurrent migration run that inserted the row
163	// between our exists() check and this insert can't raise a
164	// duplicate-entry error on the unique slug index.
165	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
166	$wpdb->query($wpdb->prepare(
167	"INSERT IGNORE INTO %i (`slug`, `title`, `created_at`, `updated_at`) VALUES (%s, %s, %s, %s)",
168	$table,
169	'standard',
170	'Standard',
171	current_time('mysql', true),
172	current_time('mysql', true)
173	));
174	}
175	}
176