From 42fa8304ddb811b0f725f245130f70c0f5e86a6c Mon Sep 17 00:00:00 2001
From: Jake Vanderwerf <get@jakevanderwerf.ca>
Date: Tue, 04 Nov 2025 06:12:02 +0000
Subject: [PATCH] =Refactored LoginManager to be more extensible and configurable, as well as an AjaxRateLimiter
---
inc/registry/CheckCustomTables.php | 451 ++++++++++++++++++++++++++++++++++++++++---------------
1 files changed, 324 insertions(+), 127 deletions(-)
diff --git a/inc/registry/CheckCustomTables.php b/inc/registry/CheckCustomTables.php
index 1d4bcaa..ed5166a 100644
--- a/inc/registry/CheckCustomTables.php
+++ b/inc/registry/CheckCustomTables.php
@@ -19,6 +19,11 @@
protected array $JVB_TAXONOMY;
protected array $JVB_USER;
+ protected string $userTable;
+ protected string $userIDType;
+ protected string $termIDType;
+ protected string $postIDType;
+
public function __construct()
{
global $wpdb;
@@ -33,7 +38,65 @@
$this->JVB_CONTENT = apply_filters('jvb_content', []);
$this->JVB_TAXONOMY = apply_filters('jvb_taxonomy', []);
$this->JVB_USER = apply_filters('jvb_user', []);
- }
+
+ $this->userTable = (is_multisite()) ? $this->getMultisiteUsersTable() : $this->wpdb->users;
+
+ $this->userIDType = $this->getColumnType($this->userTable, 'ID');
+ $this->termIDType = $this->getColumnType($this->wpdb->terms, 'term_id');
+ $this->postIDType = $this->getColumnType($this->wpdb->posts, 'ID');
+ error_log("JVB FK Types: users.ID={$this->userIDType}, terms.term_id={$this->termIDType}, posts.ID={$this->postIDType}");
+ }
+
+ protected function getMultisiteUsersTable():string
+ {
+ $siteUsersTable = $this->wpdb->prefix . 'users';
+ $siteExists = $this->wpdb->get_var(
+ $this->wpdb->prepare("SHOW TABLES LIKE %s", $siteUsersTable)
+ );
+ if ($siteExists) {
+ return $siteUsersTable;
+ }
+ //fallback to main one
+ return $this->wpdb->users;
+ }
+
+ /**
+ * Get the exact column type from a WordPress core table
+ * This ensures foreign keys match the parent table exactly
+ */
+ protected function getColumnType(string $table, string $column): string
+ {
+ // First verify the table exists
+ $tableExists = $this->wpdb->get_var(
+ $this->wpdb->prepare("SHOW TABLES LIKE %s", $table)
+ );
+
+ if (!$tableExists) {
+ error_log("JVB ERROR: Table {$table} does not exist!");
+ return 'bigint(20)'; // Fallback
+ }
+
+ $result = $this->wpdb->get_row(
+ $this->wpdb->prepare(
+ "SELECT COLUMN_TYPE
+ FROM INFORMATION_SCHEMA.COLUMNS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = %s
+ AND COLUMN_NAME = %s",
+ $table,
+ $column
+ )
+ );
+
+ if ($result && isset($result->COLUMN_TYPE)) {
+ error_log("JVB: Found Column Type for {$table}.{$column}: " . $result->COLUMN_TYPE);
+ return $result->COLUMN_TYPE;
+ }
+
+ // Fallback to signed bigint if we can't determine
+ error_log("JVB WARNING: Could not determine column type for {$table}.{$column}, using bigint(20) as fallback");
+ return 'bigint(20)';
+ }
public function maybeCreateTables()
{
@@ -72,6 +135,13 @@
error_log("JVB: Error in dashboard section: " . $e->getMessage());
}
+ try {
+ if (array_key_exists('referrals', $this->JVB_SITE) && $this->JVB_SITE['referrals']) {
+ $tables = array_merge($tables, $this->referralTables());
+ }
+ } catch (Exception $e) {
+ error_log("JVB: Error in referrals section: " . $e->getMessage());
+ }
// RE-ENABLE favourites tables
try {
if ($this->JVB_SITE['favourites']) {
@@ -372,11 +442,12 @@
protected function queueTables():array
{
+
return [
'_operation_queue' => "(
`id` VARCHAR(64) NOT NULL,
`type` varchar(50) NOT NULL,
- `user_id` bigint(20) NOT NULL,
+ `user_id` {$this->userIDType} NOT NULL,
`request_data` JSON NOT NULL,
`count` int(11) NOT NULL DEFAULT 1,
`progress_count` int(11) DEFAULT 0,
@@ -428,6 +499,7 @@
protected function errorLogTables():array
{
+
return [
'error_log'=> "(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
@@ -436,7 +508,7 @@
`message` text NOT NULL,
`context` JSON,
`severity` varchar(20) NOT NULL,
- `user_id` bigint(20) unsigned,
+ `user_id` {$this->userIDType} NOT NULL,
`created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `error_lookup` (`error_type`, `severity`, `created_at`),
@@ -447,10 +519,11 @@
protected function userIntegrationsTable():array
{
+
return [
'user_integration_logs'=> "(
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
- user_id bigint(20) unsigned NOT NULL,
+ `user_id` {$this->userIDType} NOT NULL,
service varchar(50) NOT NULL,
action varchar(50) NOT NULL,
status enum('success','error','warning') DEFAULT 'success',
@@ -467,13 +540,14 @@
protected function notificationTables():array
{
+
return [
// Main notifications table
'notifications' => "(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
- `owner_id` bigint(20) unsigned NOT NULL,
- `action_user_id` bigint(20) unsigned DEFAULT NULL,
- `target_id` bigint(20) unsigned DEFAULT NULL,
+ `owner_id` {$this->userIDType} NOT NULL,
+ `action_user_id` {$this->userIDType} NOT NULL,
+ `target_id` bigint(20) DEFAULT NULL,
`target_type` varchar(30) DEFAULT NULL,
`type` enum('new_favourite','new_artist','artist_approved','artist_invitation',
'new_term','term_approved','term_rejected','list_shared',
@@ -496,15 +570,15 @@
KEY `requires_action` (`owner_id`, `requires_action`, `action_taken`),
KEY `acting_user_lookup` (`owner_id`, `action_user_id`, `type`, `status`, `created_at`),
CONSTRAINT `{$this->base}notify_owner` FOREIGN KEY (`owner_id`)
- REFERENCES {$this->wpdb->users} (`ID`) ON DELETE CASCADE,
+ REFERENCES {$this->userTable} (`ID`) ON DELETE CASCADE,
CONSTRAINT `{$this->base}action_id` FOREIGN KEY (`action_user_id`)
- REFERENCES {$this->wpdb->users} (`ID`) ON DELETE CASCADE
+ REFERENCES {$this->userTable} (`ID`) ON DELETE CASCADE
)",
'notifications_content' => "(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
- `user_id` bigint(20) unsigned NOT NULL,
+ `user_id` {$this->userIDType} NOT NULL,
`date` date NOT NULL,
`frequency` enum('daily','weekly','monthly') NOT NULL,
`tattoo_count` int unsigned NOT NULL DEFAULT 0,
@@ -524,12 +598,12 @@
KEY `recent_content` (`date`, `frequency`),
KEY `artist_frequency` (`user_id`, `frequency`),
CONSTRAINT `{$this->base}content_artist` FOREIGN KEY (`user_id`)
- REFERENCES `wp_users` (`ID`) ON DELETE CASCADE
+ REFERENCES `{$this->userTable}` (`ID`) ON DELETE CASCADE
)",
'notifications_user_seen' => "(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
- `user_id` bigint(20) unsigned NOT NULL,
+ `user_id` {$this->userIDType} NOT NULL,
`content_notification_id` bigint(20) unsigned NOT NULL,
`status` enum('unread','read','dismissed') NOT NULL DEFAULT 'unread',
`read_at` datetime DEFAULT NULL,
@@ -538,7 +612,7 @@
UNIQUE KEY `user_content_notif` (`user_id`, `content_notification_id`),
KEY `user_status` (`user_id`, `status`),
CONSTRAINT `{$this->base}user_content_user` FOREIGN KEY (`user_id`)
- REFERENCES {$this->wpdb->users} (`ID`) ON DELETE CASCADE,
+ REFERENCES {$this->userTable} (`ID`) ON DELETE CASCADE,
CONSTRAINT `{$this->base}user_content_notification` FOREIGN KEY (`content_notification_id`)
REFERENCES `{$this->prefixed}notifications_content` (`id`) ON DELETE CASCADE
)",
@@ -546,8 +620,8 @@
// User notification preferences
'notification_preferences' => "(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
- `user_id` bigint(20) unsigned NOT NULL,
- `item_id` bigint(20) unsigned NOT NULL,
+ `user_id` {$this->userIDType} NOT NULL,
+ `item_id` bigint(20) NOT NULL,
`notification_type` varchar(50) NOT NULL,
`frequency` enum('never','daily','weekly','monthly') DEFAULT 'never',
`last_sent` datetime DEFAULT NULL,
@@ -558,13 +632,13 @@
KEY `user_frequency` (`user_id`, `frequency`),
KEY `frequency_lookup` (`frequency`, `last_sent`),
CONSTRAINT `{$this->base}notification_pref_user` FOREIGN KEY (`user_id`)
- REFERENCES {$this->wpdb->users} (`ID`) ON DELETE CASCADE
+ REFERENCES {$this->userTable} (`ID`) ON DELETE CASCADE
)",
// Notification digest scheduling and tracking
'notification_digests' => "(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
- `user_id` bigint(20) unsigned NOT NULL,
+ `user_id` {$this->userIDType} NOT NULL,
`frequency` enum('daily','weekly','monthly') NOT NULL,
`scheduled_at` datetime NOT NULL,
`sent_at` datetime DEFAULT NULL,
@@ -575,14 +649,14 @@
KEY `scheduled_digests` (`frequency`, `scheduled_at`, `status`),
KEY `user_digests` (`user_id`, `frequency`),
CONSTRAINT `{$this->base}digest_user` FOREIGN KEY (`user_id`)
- REFERENCES {$this->wpdb->users} (`ID`) ON DELETE CASCADE
+ REFERENCES {$this->userTable} (`ID`) ON DELETE CASCADE
)",
// Analytics on notification interactions
'stats__notifications' => "(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`notification_id` bigint(20) unsigned NOT NULL,
- `user_id` bigint(20) unsigned NOT NULL,
+ `user_id` {$this->userIDType} NOT NULL,
`action` varchar(30) NOT NULL,
`action_source` enum('web','email','app') DEFAULT 'web',
`action_details` JSON DEFAULT NULL,
@@ -592,7 +666,7 @@
KEY `user_actions` (`user_id`, `action`),
KEY `action_analysis` (`action`, `action_source`),
CONSTRAINT `{$this->base}metrics_user` FOREIGN KEY (`user_id`)
- REFERENCES {$this->wpdb->users} (`ID`) ON DELETE CASCADE,
+ REFERENCES {$this->userTable} (`ID`) ON DELETE CASCADE,
CONSTRAINT `{$this->base}metrics_notification` FOREIGN KEY (`notification_id`)
REFERENCES {$this->prefixed}notifications (`id`) ON DELETE CASCADE
)"
@@ -603,11 +677,12 @@
{
$tables = [];
$save = [];
+
foreach ($types as $type => $config) {
$save[$type] = ($type === 'term') ? $config : 'user';
$tables['approval_'.$type.'_requests'] = "(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
- `user_id` bigint(20) unsigned NOT NULL,
+ `user_id` {$this->userIDType} NOT NULL,
`status` enum('pending','approved','rejected','appealed','expired') DEFAULT 'pending',
`required_approvals` int unsigned DEFAULT 3,
`current_approvals` int unsigned DEFAULT 0,
@@ -622,12 +697,12 @@
KEY `status` (`status`),
KEY `expiring_requests` (`status`, `expires_at`),
CONSTRAINT `{$this->base}{$type}_approval_requester` FOREIGN KEY (`user_id`)
- REFERENCES {$this->wpdb->users} (`ID`) ON DELETE CASCADE
+ REFERENCES {$this->userTable} (`ID`) ON DELETE CASCADE
)";
$tables['approval_'.$type.'_votes'] = "(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`request_id` bigint(20) unsigned NOT NULL,
- `user_id` bigint(20) unsigned NOT NULL,
+ `user_id` {$this->userIDType} NOT NULL,
`vote` enum('approve','reject','dismissed') NOT NULL,
`notes` text DEFAULT NULL,
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
@@ -637,7 +712,7 @@
CONSTRAINT `{$this->base}{$type}_user_approval_request` FOREIGN KEY (`request_id`)
REFERENCES {$this->prefixed}approval_{$type}_requests (`id`) ON DELETE CASCADE,
CONSTRAINT `{$this->base}{$type}_user_approval_voter` FOREIGN KEY (`user_id`)
- REFERENCES {$this->wpdb->users} (`ID`) ON DELETE CASCADE
+ REFERENCES {$this->userTable} (`ID`) ON DELETE CASCADE
)";
}
if (!empty($save)) {
@@ -647,73 +722,76 @@
}
- protected function taxonomyRelationshipsTables():array
- {
+ protected function taxonomyRelationshipsTables():array
+ {
+ $tables = [
+ 'taxonomy_relationships' => "(
+ `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ `term_id` {$this->termIDType} NOT NULL,
+ `related_term_id` {$this->termIDType} NOT NULL,
+ `taxonomy` varchar(32) NOT NULL,
+ `related_taxonomy` varchar(32) NOT NULL,
+ `post_count` int(11) NOT NULL DEFAULT 0,
+ `is_direct` tinyint(1) NOT NULL DEFAULT 1,
+ `is_hierarchical` tinyint(1) NOT NULL DEFAULT 0,
+ `last_updated` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+ PRIMARY KEY (`id`),
+ KEY `term_id` (`term_id`),
+ KEY `related_term_id` (`related_term_id`),
+ KEY `taxonomy` (`taxonomy`),
+ KEY `related_taxonomy` (`related_taxonomy`),
+ UNIQUE KEY `term_relation` (`term_id`, `related_term_id`, `taxonomy`, `related_taxonomy`),
+ CONSTRAINT `{$this->base}tax_rel_term_id` FOREIGN KEY (`term_id`)
+ REFERENCES {$this->wpdb->terms} (`term_id`) ON DELETE CASCADE,
+ CONSTRAINT `{$this->base}tax_rel_related_id` FOREIGN KEY (`related_term_id`)
+ REFERENCES {$this->wpdb->terms} (`term_id`) ON DELETE CASCADE
+ )"
+ ];
- $tables = [
- 'taxonomy_relationships' => "(
- `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
- `term_id` bigint(20) unsigned NOT NULL,
- `related_term_id` bigint(20) unsigned NOT NULL,
- `taxonomy` varchar(32) NOT NULL,
- `related_taxonomy` varchar(32) NOT NULL,
- `post_count` int(11) NOT NULL DEFAULT 0,
- `is_direct` tinyint(1) NOT NULL DEFAULT 1,
- `is_hierarchical` tinyint(1) NOT NULL DEFAULT 0,
- `last_updated` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- PRIMARY KEY (`id`),
- KEY `term_id` (`term_id`),
- KEY `related_term_id` (`related_term_id`),
- KEY `taxonomy` (`taxonomy`),
- KEY `related_taxonomy` (`related_taxonomy`),
- UNIQUE KEY `term_relation` (`term_id`, `related_term_id`, `taxonomy`, `related_taxonomy`),
- CONSTRAINT `{$this->base}tax_rel_term_id` FOREIGN KEY (`term_id`)
- REFERENCES {$this->wpdb->terms} (`term_id`) ON DELETE CASCADE,
- CONSTRAINT `{$this->base}related_term_id` FOREIGN KEY (`related_term_id`)
- REFERENCES {$this->wpdb->terms} (`term_id`) ON DELETE CASCADE
- )"
- ];
- if ((array_key_exists('dashboard', $this->JVB_SITE) && $this->JVB_SITE['dashboard'] === true) || array_key_exists('use_feed_block', $this->JVB_SITE) && $this->JVB_SITE['use_feed_block']) {
- $tables['user_term_index'] = "(
- `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
- `user_id` bigint(20) unsigned NOT NULL,
- `term_id` bigint(20) unsigned NOT NULL,
- `taxonomy` varchar(32) NOT NULL,
- `post_count` int(11) NOT NULL DEFAULT 1,
- `is_parent` tinyint(1) NOT NULL DEFAULT 0,
- `last_used` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- PRIMARY KEY (`id`),
- UNIQUE KEY `user_term` (`user_id`, `term_id`, `taxonomy`),
- KEY `user_taxonomy` (`user_id`, `taxonomy`),
- KEY `taxonomy` (`taxonomy`),
- CONSTRAINT `{$this->base}user_term_user` FOREIGN KEY (`user_id`)
- REFERENCES {$this->wpdb->users} (`ID`) ON DELETE CASCADE,
- CONSTRAINT `{$this->base}user_term_term` FOREIGN KEY (`term_id`)
- REFERENCES {$this->wpdb->terms} (`term_id`) ON DELETE CASCADE
- )";
- }
+ if ((array_key_exists('dashboard', $this->JVB_SITE) && $this->JVB_SITE['dashboard'] === true) || array_key_exists('use_feed_block', $this->JVB_SITE) && $this->JVB_SITE['use_feed_block']) {
+ $tables['user_term_index'] = "(
+ `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ `user_id` {$this->userIDType} NOT NULL,
+ `term_id` {$this->termIDType} NOT NULL,
+ `taxonomy` varchar(32) NOT NULL,
+ `post_count` int(11) NOT NULL DEFAULT 1,
+ `is_parent` tinyint(1) NOT NULL DEFAULT 0,
+ `last_used` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+ PRIMARY KEY (`id`),
+ UNIQUE KEY `user_term` (`user_id`, `term_id`, `taxonomy`),
+ KEY `user_taxonomy` (`user_id`, `taxonomy`),
+ KEY `taxonomy` (`taxonomy`),
+ CONSTRAINT `{$this->base}user_term_user_fk` FOREIGN KEY (`user_id`)
+ REFERENCES {$this->userTable} (`ID`) ON DELETE CASCADE,
+ CONSTRAINT `{$this->base}user_term_term_fk` FOREIGN KEY (`term_id`)
+ REFERENCES {$this->wpdb->terms} (`term_id`) ON DELETE CASCADE
+ )";
+ }
- return $tables;
- }
+ return $tables;
+ }
protected function favouriteTables():array
{
+
return [
'favourites' => "(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
- `user_id` bigint(20) unsigned NOT NULL,
+ `user_id` {$this->userIDType} NOT NULL,
`type` varchar(50) NOT NULL,
- `target_id` bigint(20) unsigned NOT NULL,
+ `target_id` bigint(20) NOT NULL,
`notes` text DEFAULT NULL,
`date_added` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_favourite` (`user_id`, `type`, `target_id`),
KEY `user_type` (`user_id`, `type`),
- KEY `target_type` (`target_id`, `type`)
+ KEY `target_type` (`target_id`, `type`),
+ CONSTRAINT `{$this->base}favourites_user` FOREIGN KEY (`user_id`)
+ REFERENCES {$this->userTable} (`ID`) ON DELETE CASCADE
)",
'favourites_lists' => "(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
- `user_id` bigint(20) unsigned NOT NULL,
+ `user_id` {$this->userIDType} NOT NULL,
`name` varchar(255) NOT NULL,
`description` text,
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
@@ -721,13 +799,13 @@
PRIMARY KEY (`id`),
KEY `user_lists` (`user_id`),
CONSTRAINT `{$this->base}list_user` FOREIGN KEY (`user_id`)
- REFERENCES {$this->wpdb->users} (`ID`) ON DELETE CASCADE
+ REFERENCES {$this->userTable} (`ID`) ON DELETE CASCADE
)",
'favourites_list_items' => "(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`list_id` bigint(20) unsigned NOT NULL,
`item_type` varchar(50) NOT NULL,
- `item_id` bigint(20) unsigned NOT NULL,
+ `item_id` bigint(20) NOT NULL,
`favourite_id` bigint(20) unsigned DEFAULT NULL,
`added_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
@@ -742,7 +820,7 @@
'favourites_list_shares' => "(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`list_id` bigint(20) unsigned NOT NULL,
- `user_id` bigint(20) unsigned NULL,
+ `user_id` {$this->userIDType} NOT NULL,
`email` varchar(255) NOT NULL,
`permission_type` enum('view', 'edit') NOT NULL DEFAULT 'view',
`status` enum('pending', 'accepted', 'rejected', 'revoked') NOT NULL DEFAULT 'pending',
@@ -757,12 +835,12 @@
CONSTRAINT `{$this->base}share_list` FOREIGN KEY (`list_id`)
REFERENCES {$this->prefixed}favourites_lists (`id`) ON DELETE CASCADE,
CONSTRAINT `{$this->base}share_user` FOREIGN KEY (`user_id`)
- REFERENCES {$this->wpdb->users} (`ID`) ON DELETE CASCADE
+ REFERENCES {$this->userTable} (`ID`) ON DELETE CASCADE
)",
'favourites_list_stats' => "(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`item_type` varchar(50) NOT NULL,
- `item_id` bigint(20) unsigned NOT NULL,
+ `item_id` bigint(20) NOT NULL,
`list_count` int NOT NULL DEFAULT 0,
`last_added` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
@@ -777,9 +855,9 @@
return [
'news_relationships' => "(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
- `shop_id` bigint(20) unsigned NOT NULL,
- `user_id` bigint(20) unsigned NOT NULL,
- `artist_id` bigint(20) unsigned DEFAULT NULL,
+ `shop_id` {$this->termIDType} NOT NULL,
+ `user_id` {$this->userIDType} NOT NULL,
+ `artist_id` {$this->postIDType} NOT NULL,
`news_count` int(10) unsigned NOT NULL DEFAULT 0,
`last_post_date` datetime DEFAULT NULL,
`last_updated` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
@@ -791,7 +869,7 @@
CONSTRAINT `{$this->base}nr_shop_news_shop` FOREIGN KEY (`shop_id`)
REFERENCES {$this->wpdb->terms} (`term_id`) ON DELETE CASCADE,
CONSTRAINT `{$this->base}nr_shop_news_user` FOREIGN KEY (`user_id`)
- REFERENCES {$this->wpdb->users} (`ID`) ON DELETE CASCADE,
+ REFERENCES {$this->userTable} (`ID`) ON DELETE CASCADE,
CONSTRAINT `{$this->base}nr_shop_news_artist` FOREIGN KEY (`artist_id`)
REFERENCES {$this->wpdb->posts} (`ID`) ON DELETE SET NULL
)"
@@ -803,9 +881,9 @@
return [
'responses' => "(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
- `item_id` bigint(20) unsigned NOT NULL,
+ `item_id` {$this->postIDType} NOT NULL,
`content` text NOT NULL,
- `user_id` bigint(20) unsigned DEFAULT NULL,
+ `user_id` {$this->userIDType} NOT NULL,
`parent_id` bigint(20) unsigned DEFAULT NULL,
`response` text NOT NULL,
`status` enum('published','hidden','flagged','deleted') DEFAULT 'published',
@@ -827,8 +905,8 @@
)",
'karma_response' => "(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
- `item_id` bigint(20) unsigned NOT NULL,
- `user_id` bigint(20) unsigned NOT NULL,
+ `item_id` bigint(20) NOT NULL,
+ `user_id` {$this->userIDType} NOT NULL,
`vote` enum('up','down') NOT NULL,
`date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
@@ -838,7 +916,7 @@
CONSTRAINT `{$this->base}_response_item_id` FOREIGN KEY (`item_id`)
REFERENCES {$this->prefixed}responses (`id`) ON DELETE CASCADE,
CONSTRAINT `{$this->base}_response_user_id` FOREIGN KEY (`user_id`)
- REFERENCES {$this->wpdb->users} (`ID`) ON DELETE CASCADE
+ REFERENCES {$this->userTable} (`ID`) ON DELETE CASCADE
)"
];
}
@@ -859,25 +937,29 @@
if (!$t) {
continue;
}
+
switch ($t) {
case 'posts':
+ $referenceType = $this->postIDType;
$reference_table = $this->wpdb->posts;
$reference_column = 'ID';
break;
case 'terms':
+ $referenceType = $this->termIDType;
$reference_table = $this->wpdb->terms;
$reference_column = 'term_id';
break;
case 'users':
- $reference_table = $this->wpdb->users;
+ $referenceType = $this->userIDType;
+ $reference_table = $this->userTable;
$reference_column = 'ID';
break;
}
$tables['karma_'.$type] = "(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
- `item_id` bigint(20) unsigned NOT NULL,
- `user_id` bigint(20) unsigned NOT NULL,
+ `item_id` {$referenceType} NOT NULL,
+ `user_id` {$this->userIDType} NOT NULL,
`vote` enum('up','down') NOT NULL,
`date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
@@ -887,7 +969,7 @@
CONSTRAINT `{$this->base}kt_{$type}_item_id` FOREIGN KEY (`item_id`)
REFERENCES {$reference_table} ({$reference_column}) ON DELETE CASCADE,
CONSTRAINT `{$this->base}kt_{$type}_user_id` FOREIGN KEY (`user_id`)
- REFERENCES {$this->wpdb->users} (`ID`) ON DELETE CASCADE
+ REFERENCES {$this->userTable} (`ID`) ON DELETE CASCADE
)";
}
@@ -901,13 +983,13 @@
foreach ($types as $type => $config) {
$tables['calendar_'.$type] = "(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
- `post_id` bigint(20) unsigned NOT NULL,
- `event_type` bigint(20) unsigned,
+ `post_id` {$this->postIDType} NOT NULL,
+ `event_type` {$this->termIDType} unsigned,
-- Basic event details
`title` varchar(255) NOT NULL,
- `shop_id` bigint(20) unsigned,
- `user_id` bigint(20) unsigned,
+ `shop_id` {$this->termIDType} NOT NULL
+ `user_id` {$this->userIDType} NOT NULL,
-- Location handling
`location_type` enum('shop', 'custom', 'online') DEFAULT 'shop',
@@ -970,13 +1052,13 @@
CONSTRAINT `{$this->base}cal_{$type}_shop` FOREIGN KEY (`shop_id`)
REFERENCES {$this->wpdb->terms} (`term_id`) ON DELETE SET NULL,
CONSTRAINT `{$this->base}cal_{$type}_user` FOREIGN KEY (`user_id`)
- REFERENCES {$this->wpdb->users} (`ID`) ON DELETE SET NULL
+ REFERENCES {$this->userTable} (`ID`) ON DELETE SET NULL
)";
$tables['calendar_'.$type.'_participants'] = "(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`event_id` bigint(20) unsigned NOT NULL,
- `user_id` bigint(20) unsigned NOT NULL,
+ `user_id` {$this->userIDType} NOT NULL,
`status` enum('interested','going') NOT NULL,
`created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
@@ -984,7 +1066,7 @@
CONSTRAINT `{$this->base}cal_{$type}_participant_event` FOREIGN KEY (`event_id`)
REFERENCES {$this->prefixed}calendar_{$type} (`id`) ON DELETE CASCADE,
CONSTRAINT `{$this->base}cal_{$type}_participant_user` FOREIGN KEY (`user_id`)
- REFERENCES {$this->wpdb->users} (`ID`) ON DELETE CASCADE
+ REFERENCES {$this->userTable} (`ID`) ON DELETE CASCADE
)";
$tables['calendar_'.$type.'_recurrence_exceptions'] = "(
@@ -1005,6 +1087,7 @@
protected function umamiTracking():array
{
+
return [
'umami_events' => "(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
@@ -1012,12 +1095,12 @@
`timestamp` datetime NOT NULL,
`event` varchar(50) NOT NULL,
`event_type` varchar(50) NOT NULL,
- `user_id` bigint(20) unsigned DEFAULT NULL,
- `content_id` bigint(20) unsigned DEFAULT NULL,
+ `user_id` {$this->userIDType} NOT NULL,
+ `content_id` bigint(20) DEFAULT NULL,
`content_type` varchar(50) DEFAULT NULL,
- `source_id` bigint(20) unsigned DEFAULT NULL,
+ `source_id` bigint(20) DEFAULT NULL,
`source_type` varchar(50) DEFAULT NULL,
- `owner_id` bigint(20) unsigned DEFAULT NULL,
+ `owner_id` {$this->userIDType} NOT NULL,
`owner_type` varchar(50) DEFAULT NULL,
`referrer` varchar(100) DEFAULT NULL,
`metadata` JSON DEFAULT NULL,
@@ -1029,14 +1112,14 @@
KEY `user_idx` (`user_id`),
KEY `owner_idx` (`owner_id`),
CONSTRAINT `{$this->base}umami_user_id_link` FOREIGN KEY (`user_id`)
- REFERENCES {$this->wpdb->users} (`ID`) ON DELETE CASCADE,
+ REFERENCES {$this->userTable} (`ID`) ON DELETE CASCADE,
CONSTRAINT `{$this->base}umami_owner_id_link` FOREIGN KEY (`owner_id`)
- REFERENCES {$this->wpdb->users} (`ID`) ON DELETE CASCADE
+ REFERENCES {$this->userTable} (`ID`) ON DELETE CASCADE
)",
'stats_performance' => "(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`date` date NOT NULL,
- `user_id` bigint(20) unsigned DEFAULT NULL,
+ `user_id` {$this->userIDType} NOT NULL,
`profile_view_count` bigint(20) unsigned DEFAULT 0,
`feed_view_count` bigint(20) unsigned DEFAULT 0,
`top_content` json DEFAULT null,
@@ -1049,13 +1132,14 @@
PRIMARY KEY (`id`),
KEY `user_date_idx` (`user_id`, `date`),
CONSTRAINT `{$this->base}performance_user_id_link` FOREIGN KEY (`user_id`)
- REFERENCES {$this->wpdb->users} (`ID`) ON DELETE CASCADE
+ REFERENCES {$this->userTable} (`ID`) ON DELETE CASCADE
)"
];
}
protected function invitationTables($types)
{
+
$tables = [];
foreach ($types as $role => $config) {
$definitions = "(
@@ -1066,9 +1150,9 @@
`status` enum('pending', 'accepted', 'rejected', 'expired','revoked') DEFAULT 'pending',
`inviters` JSON NOT NULL,";
foreach($config['to_terms']??[] as $term) {
- $definitions .= "`to_{$term}` bigint(20) unsigned DEFAULT NULL,";
+ $definitions .= "`to_{$term}` {$this->termIDType} DEFAULT NULL,";
}
- $definitions .= "`new_user_id` bigint(20) unsigned DEFAULT NULL,
+ $definitions .= "`new_user_id` bigint(20) NOT NULL,
`expires_at` datetime NOT NULL,
`accepted_at` datetime DEFAULT NULL,
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
@@ -1079,6 +1163,10 @@
KEY `status_expiry` (`status`, `expires_at`),
KEY `name_status` (`name`, `status`)
)";
+ foreach($config['to_terms']??[] as $term) {
+ $definitions .= "CONSTRAINT `{$this->base}_{$term}_link` FOREIGN KEY (`to_{$term}`)
+ REFERENCES {$this->wpdb->terms} (`term_id`) ON DELETE CASCADE";
+ }
$tables['invitations_'.$role] = $definitions;
}
@@ -1094,9 +1182,9 @@
foreach ($contents as $content) {
$tables['history_'.$content.'_'.$type] = "(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
- `user_id` bigint(20) unsigned NOT NULL,
- `content_id` bigint(20) unsigned NOT NULL,
- `term_id` bigint(20) unsigned NOT NULL,
+ `user_id` {$this->userIDType} NOT NULL,
+ `content_id` bigint(20) NOT NULL,
+ `term_id` {$this->termIDType} NOT NULL,
`role` varchar(50) DEFAULT 'artist',
`is_primary` tinyint(1) DEFAULT 0,
`start_date` date DEFAULT NULL,
@@ -1106,7 +1194,7 @@
UNIQUE KEY `content_term` (`content_id`, `term_id`),
KEY content_role (`term_id`, `role`),
CONSTRAINT `{$this->base}{$content}_{$type}_history_user` FOREIGN KEY (`user_id`)
- REFERENCES {$this->wpdb->users} (`ID`) ON DELETE CASCADE,
+ REFERENCES {$this->userTable} (`ID`) ON DELETE CASCADE,
CONSTRAINT `{$this->base}{$content}_{$type}_history_term` FOREIGN KEY (`term_id`)
REFERENCES {$this->wpdb->terms} (`term_id`) ON DELETE CASCADE
)";
@@ -1123,9 +1211,9 @@
foreach ($contents as $content) {
$tables[$content.'_'.$type.'_requests'] = "(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
- `user_id` bigint(20) unsigned NOT NULL,
- `content_id` bigint(20) unsigned NOT NULL,
- `term_id` bigint(20) unsigned NOT NULL,
+ `user_id` {$this->userIDType} NOT NULL,
+ `content_id` bigint(20) NOT NULL,
+ `term_id` {$this->termIDType} NOT NULL,
`managers` json DEFAULT NULL,
`status` ENUM('requested', 'rejected', 'accepted') DEFAULT 'requested',
`dismissed` smallint(1) unsigned DEFAULT NULL,
@@ -1135,7 +1223,7 @@
PRIMARY KEY (`id`),
UNIQUE KEY `{$this->base}content_term` (`content_id`, `term_id`),
CONSTRAINT `{$this->base}{$content}_{$type}_request_user` FOREIGN KEY (`user_id`)
- REFERENCES {$this->wpdb->users} (`ID`) ON DELETE CASCADE,
+ REFERENCES {$this->userTable} (`ID`) ON DELETE CASCADE,
CONSTRAINT `{$this->base}{$content}_{$type}_request_term` FOREIGN KEY (`term_id`)
REFERENCES {$this->wpdb->terms} (`term_id`) ON DELETE CASCADE
)";
@@ -1146,6 +1234,116 @@
}
+ /**
+ * Create referral tracking tables
+ *
+ * Call this from the main table creation method in CheckCustomTables.php:
+ * $tables = array_merge($tables, $this->referralTables());
+ */
+ protected function referralTables(): array
+ {
+ // Create tables in dependency order
+ // First: referrals (depends only on wp_users)
+ $mainTable['referrals'] = "(
+ `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ `referrer_id` {$this->userIDType} NOT NULL,
+ `referee_id` {$this->userIDType} NOT NULL,
+ `referee_name` varchar(255) NOT NULL,
+ `referee_email` varchar(255) NOT NULL,
+ `referee_phone` varchar(50) DEFAULT NULL,
+ `referral_code` varchar(50) NOT NULL,
+ `status` enum('pending', 'consulted', 'treated', 'cancelled') DEFAULT 'pending',
+ `referred_at` datetime NOT NULL,
+ `consulted_at` datetime DEFAULT NULL,
+ `treated_at` datetime DEFAULT NULL,
+ `treatment_count` int DEFAULT 0,
+ `notes` text DEFAULT NULL,
+ PRIMARY KEY (`id`),
+ UNIQUE KEY `referee_unique` (`referee_id`),
+ KEY `referrer_idx` (`referrer_id`),
+ KEY `status_idx` (`status`),
+ KEY `code_idx` (`referral_code`),
+ KEY `date_idx` (`referred_at`),
+ KEY `consult_idx` (`consulted_at`),
+ CONSTRAINT `{$this->base}referral_referrer_fk` FOREIGN KEY (`referrer_id`)
+ REFERENCES {$this->userTable} (`ID`) ON DELETE CASCADE,
+ CONSTRAINT `{$this->base}referral_referee_fk` FOREIGN KEY (`referee_id`)
+ REFERENCES {$this->userTable} (`ID`) ON DELETE CASCADE
+)";
+
+ // Create the main referrals table first
+ $this->createTables($mainTable);
+
+ // Now create dependent tables
+ $dependentTables = [];
+
+ // Second: jane_clients (depends only on wp_users)
+ $dependentTables['jane_clients'] = "(
+ `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ `patient_guid` varchar(50) NOT NULL,
+ `user_id` {$this->userIDType} NOT NULL,
+ `first_name` varchar(100) NOT NULL,
+ `last_name` varchar(100) NOT NULL,
+ `email` varchar(255) NOT NULL,
+ `imported_at` datetime DEFAULT CURRENT_TIMESTAMP,
+ `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+ PRIMARY KEY (`id`),
+ UNIQUE KEY `patient_guid_unique` (`patient_guid`),
+ KEY `user_idx` (`user_id`),
+ KEY `email_idx` (`email`),
+ CONSTRAINT `{$this->base}jane_client_user_fk` FOREIGN KEY (`user_id`)
+ REFERENCES {$this->userTable} (`ID`) ON DELETE CASCADE
+)";
+
+ // Third: referral_treatments (depends on referrals AND wp_users)
+ $dependentTables['referral_treatments'] = "(
+ `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ `referral_id` bigint(20) unsigned NOT NULL,
+ `user_id` {$this->userIDType} NOT NULL,
+ `treatment_type` varchar(100) NOT NULL COMMENT 'Tier 1-6, Brows, etc',
+ `treatment_date` datetime NOT NULL,
+ `invoice_number` varchar(50) DEFAULT NULL,
+ `amount` decimal(10,2) DEFAULT NULL,
+ `status` enum('completed', 'no_show', 'cancelled') DEFAULT 'completed',
+ `imported_at` datetime DEFAULT CURRENT_TIMESTAMP,
+ PRIMARY KEY (`id`),
+ KEY `referral_idx` (`referral_id`),
+ KEY `user_idx` (`user_id`),
+ KEY `date_idx` (`treatment_date`),
+ KEY `type_idx` (`treatment_type`),
+ CONSTRAINT `{$this->base}treatment_referral_fk` FOREIGN KEY (`referral_id`)
+ REFERENCES {$this->prefixed}referrals (`id`) ON DELETE CASCADE,
+ CONSTRAINT `{$this->base}treatment_user_fk` FOREIGN KEY (`user_id`)
+ REFERENCES {$this->userTable} (`ID`) ON DELETE CASCADE
+)";
+
+ // Fourth: referral_rewards (depends on referrals AND wp_users)
+ $dependentTables['referral_rewards'] = "(
+ `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ `referral_id` bigint(20) unsigned NOT NULL,
+ `user_id` {$this->userIDType} NOT NULL,
+ `reward_type` enum('referrer', 'referee') NOT NULL,
+ `amount` decimal(10,2) NOT NULL,
+ `reward_calculation` varchar(20) DEFAULT NULL COMMENT 'percentage or fixed',
+ `status` enum('available', 'redeemed', 'expired', 'cancelled') DEFAULT 'available',
+ `created_at` datetime NOT NULL,
+ `redeemed_at` datetime DEFAULT NULL,
+ `expires_at` datetime DEFAULT NULL,
+ `notes` text DEFAULT NULL,
+ PRIMARY KEY (`id`),
+ KEY `referral_idx` (`referral_id`),
+ KEY `user_idx` (`user_id`),
+ KEY `status_idx` (`status`),
+ KEY `type_idx` (`reward_type`),
+ CONSTRAINT `{$this->base}reward_referral_fk` FOREIGN KEY (`referral_id`)
+ REFERENCES {$this->prefixed}referrals (`id`) ON DELETE CASCADE,
+ CONSTRAINT `{$this->base}reward_user_fk` FOREIGN KEY (`user_id`)
+ REFERENCES {$this->userTable} (`ID`) ON DELETE CASCADE
+)";
+
+ return $dependentTables;
+ }
+
/*******************************************************************************************
* These methods help create a content-type taxonomy, like the tattoo shops in edmonton.ink
* To set up, ensure that some fields in the registered taxonomy include 'content_table' => true
@@ -1169,9 +1367,9 @@
'time' => "time {$nullable}",
'datetime' => "datetime {$nullable}",
'true_false' => "boolean DEFAULT FALSE",
- 'image', 'file' => "bigint(20) unsigned {$nullable}",
+ 'image', 'file' => "bigint(20) {$nullable}",
'taxonomy' => $this->getTaxonomyColumnDefinition($fieldConfig, $nullable),
- 'user' => "bigint(20) unsigned {$nullable}",
+ 'user' => "bigint(20) {$nullable}",
'location' => $this->getLocationColumnDefinition($fieldName, $nullable),
'select', 'radio' => $this->getSelectColumnDefinition($fieldConfig, $nullable),
'set', 'checkbox', 'repeater', 'gallery' => "json {$nullable}",
@@ -1220,7 +1418,7 @@
$limit = $fieldConfig['limit'] ?? null;
if ($limit === 1) {
- return "bigint(20) unsigned {$nullable}";
+ return "{$this->termIDType} {$nullable}";
}
// Multiple selections stored as JSON
@@ -1265,7 +1463,7 @@
REFERENCES {$this->wpdb->terms} (`term_id`) ON DELETE SET NULL";
} elseif ($type === 'user') {
$constraints[] = "CONSTRAINT `{$constraintName}` FOREIGN KEY (`{$fieldName}`)
- REFERENCES {$this->wpdb->users} (`ID`) ON DELETE SET NULL";
+ REFERENCES {$this->userTable} (`ID`) ON DELETE SET NULL";
} elseif ($type === 'image' || $type === 'file') {
$constraints[] = "CONSTRAINT `{$constraintName}` FOREIGN KEY (`{$fieldName}`)
REFERENCES {$this->wpdb->posts} (`ID`) ON DELETE SET NULL";
@@ -1297,9 +1495,8 @@
try {
$tableName = 'content_' . $type;
$columns = [];
-
// Always include primary key
- $columns[] = "`term_id` bigint(20) unsigned NOT NULL";
+ $columns[] = "`term_id` {$this->termIDType} NOT NULL";
$columns[] = "`name` varchar(255) NOT NULL";
$columns[] = "`slug` varchar(255) NOT NULL";
@@ -1408,7 +1605,7 @@
// Base columns that every user stats table should have
$columns = [
- "`user_id` bigint(20) unsigned NOT NULL",
+ "`user_id` {$this->userIDType} NOT NULL",
"`display_name` VARCHAR(255) NULL",
"`email` VARCHAR(255) NULL",
"`city` VARCHAR(100) NULL",
@@ -1431,12 +1628,11 @@
error_log('JVB: Favourites column added');
}
- // CRITICAL FIX: Simplified profile-specific fields processing
if (isset($config['profile_type'])) {
$profileType = $config['profile_type'];
error_log("JVB: Processing profile type: {$profileType}");
- // SAFE check for profile fields
+
if (isset($this->JVB_CONTENT[$profileType]) &&
isset($this->JVB_CONTENT[$profileType]['fields']) &&
is_array($this->JVB_CONTENT[$profileType]['fields'])) {
@@ -1531,11 +1727,12 @@
$constraints = [
"CONSTRAINT `{$this->base}_{$userType}_stats_user` FOREIGN KEY (`user_id`)
- REFERENCES {$this->wpdb->users} (`ID`) ON DELETE CASCADE"
+ REFERENCES {$this->userTable} (`ID`) ON DELETE CASCADE"
];
$allDefinitions = array_merge($columns, $indexes, $constraints);
return "(\n " . implode(",\n ", $allDefinitions) . "\n)";
}
+
}
--
Gitblit v1.10.0