fluent-cart / database / Migrations / SubscriptionsMigrator.php

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

146 lines 5.5 KB

Raw Download Zip

1	<?php
2
3	namespace FluentCart\Database\Migrations;
4
5	use FluentCart\App\Models\Subscription;
6	class SubscriptionsMigrator extends Migrator
7	{
8
9	public static string $tableName = "fct_subscriptions";
10
11
12	const CUSTOMER_RECOVERY_INDEX = 'idx_subscriptions_customer_recovery';
13
14	public static function getSqlSchema(): string
15	{
16	$indexPrefix = static::getDbPrefix() . 'fct_index_';
17
18	return "`id` BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
19	`uuid` VARCHAR(100) NOT NULL,
20	`customer_id` BIGINT(20) UNSIGNED NOT NULL,
21	`parent_order_id` BIGINT(20) UNSIGNED NOT NULL,
22	`product_id` BIGINT(20) UNSIGNED NOT NULL,
23	`item_name` TEXT NOT NULL,
24	`quantity` INT NOT NULL DEFAULT '1',
25	`variation_id` BIGINT(20) UNSIGNED NOT NULL,
26	`billing_interval` VARCHAR(45) NULL,
27	`signup_fee` BIGINT UNSIGNED NOT NULL DEFAULT 0,
28	`initial_tax_total` BIGINT UNSIGNED NOT NULL DEFAULT 0,
29	`recurring_amount` BIGINT UNSIGNED NOT NULL DEFAULT 0,
30	`recurring_tax_total` BIGINT UNSIGNED NOT NULL DEFAULT 0,
31	`recurring_total` BIGINT UNSIGNED NOT NULL DEFAULT 0,
32	`bill_times` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
33	`bill_count` INT UNSIGNED NOT NULL DEFAULT 0,
34	`expire_at` DATETIME NULL,
35	`trial_ends_at` DATETIME NULL,
36	`canceled_at` DATETIME NULL,
37	`restored_at` DATETIME NULL,
38	`collection_method` ENUM('automatic', 'manual', 'system') NOT NULL DEFAULT 'automatic',
39	`next_billing_date` DATETIME NULL,
40	`trial_days` INT(10) UNSIGNED NOT NULL DEFAULT 0,
41	`vendor_customer_id` VARCHAR(45) NULL,
42	`vendor_plan_id` VARCHAR(45) NULL,
43	`vendor_subscription_id` VARCHAR(45) NULL,
44	`status` VARCHAR(45) NULL,
45	`original_plan` LONGTEXT NULL,
46	`vendor_response` LONGTEXT NULL,
47	`current_payment_method` VARCHAR(45) NULL,
48	`config` json DEFAULT NULL,
49	`created_at` DATETIME NULL,
50	`updated_at` DATETIME NULL,
51
52	INDEX `{$indexPrefix}_order_subscription_idx` (`parent_order_id` ASC),
53	INDEX `{$indexPrefix}vendor_subscription_id_idx` (`vendor_subscription_id` ASC),
54	INDEX `{$indexPrefix}_expiry_scan_idx` (`status`, `next_billing_date`, `id`),
55	INDEX `{$indexPrefix}collection_method_idx` (`collection_method`),
56	INDEX `" . self::CUSTOMER_RECOVERY_INDEX . "` (`customer_id` ASC, `id` ASC)";
57	}
58
59	public static function migrated()
60	{
61	static::addCustomerRecoveryIndex();
62	static::addUuidColumn();
63	static::renameInitialAmountToSignupFee();
64	static::backfillEmptyUuids();
65	static::addVendorSubscriptionIdIndex();
66	static::addExpiryScanIndex();
67	static::addCollectionMethodIndex();
68	}
69
70	// Serves the store-managed cron guard: whereIn(collection_method,[manual,system])->exists().
71	// Online DDL, builds over existing rows; column is NOT NULL so no legacy backfill.
72	public static function addCollectionMethodIndex()
73	{
74	static::addIndexIfNotExists(
75	static::getDbPrefix() . 'fct_index_collection_method_idx',
76	'collection_method'
77	);
78	}
79
80	public static function addVendorSubscriptionIdIndex()
81	{
82	static::addIndexIfNotExists(
83	static::getDbPrefix() . 'fct_index_vendor_subscription_id_idx',
84	'vendor_subscription_id'
85	);
86	}
87
88	public static function addExpiryScanIndex()
89	{
90	static::addIndexIfNotExists(
91	static::getDbPrefix() . 'fct_index_expiry_scan_idx',
92	['status', 'next_billing_date', 'id']
93	);
94	}
95
96	public static function addUuidColumn()
97	{
98	// "ALTER TABLE %i ADD COLUMN `uuid` VARCHAR(100) NOT NULL DEFAULT '' AFTER `id`"
99	static::addColumnIfNotExists('uuid', "VARCHAR(100) NOT NULL DEFAULT ''", 'id');
100	}
101
102	public static function renameInitialAmountToSignupFee()
103	{
104	// "ALTER TABLE %i CHANGE `initial_amount` `signup_fee` BIGINT UNSIGNED NOT NULL DEFAULT 0"
105	static::renameColumnIfExists('initial_amount', 'signup_fee', 'BIGINT UNSIGNED NOT NULL DEFAULT 0');
106	}
107
108	public static function backfillEmptyUuids()
109	{
110	$chunkSize = 500;
111
112	do {
113	$subscriptions = Subscription::select('id')
114	->where(function ($query) {
115	$query->where('uuid', '')
116	->orWhereNull('uuid');
117	})
118	->limit($chunkSize)
119	->get();
120
121	if ($subscriptions->isEmpty()) {
122	break;
123	}
124
125	$uuids = [];
126	foreach ($subscriptions as $subscription) {
127	$uuids[] = [
128	'id' => $subscription->id,
129	'uuid' => md5(time() . wp_generate_uuid4())
130	];
131	}
132
133	(new Subscription())->batchUpdate($uuids);
134	} while ($subscriptions->count() >= $chunkSize);
135	}
136	public static function addCustomerRecoveryIndex(): void
137	{
138	static::addIndexIfNotExists(self::CUSTOMER_RECOVERY_INDEX, ['customer_id', 'id']);
139	}
140
141	public static function hasCustomerRecoveryIndex(): bool
142	{
143	return static::hasIndex(self::CUSTOMER_RECOVERY_INDEX);
144	}
145	}
146