=Refactored LoginManager to be more extensible and configurable, as well as...

Jake Vanderwerf

2025-11-04 42fa8304ddb811b0f725f245130f70c0f5e86a6c

 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()
   {
@@ -379,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,
@@ -435,6 +499,7 @@

    protected function errorLogTables():array
    {

        return [
            'error_log'=> "(
            `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
@@ -443,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`),
@@ -454,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',
@@ -474,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',
@@ -503,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,
@@ -531,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,
@@ -545,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
        )",
@@ -553,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,
@@ -565,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,
@@ -582,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,
@@ -599,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
        )"
@@ -610,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,
@@ -629,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,
@@ -644,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)) {
@@ -654,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,
@@ -728,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`),
@@ -749,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',
@@ -764,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`),
@@ -784,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,
@@ -798,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
        )"
@@ -810,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',
@@ -834,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`),
@@ -845,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
                )"
        ];
    }
@@ -866,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`),
@@ -894,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
            )";

        }
@@ -908,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',
@@ -977,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`),
@@ -991,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'] = "(
@@ -1012,6 +1087,7 @@

    protected function umamiTracking():array
    {

        return [
            'umami_events' => "(
            `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
@@ -1019,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,
@@ -1036,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,
@@ -1056,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 = "(
@@ -1073,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,
@@ -1086,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;
      }
@@ -1101,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,
@@ -1113,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
                )";
@@ -1130,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,
@@ -1142,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
                )";
@@ -1161,58 +1242,106 @@
    */
   protected function referralTables(): array
   {
      $tables = [];
      // 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
)";

      // Main referrals table
      $tables[BASE . 'referrals'] = "(
        `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        `referrer_id` bigint(20) unsigned NOT NULL,
        `referee_id` bigint(20) unsigned 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', 'treated', 'cancelled') DEFAULT 'pending',
        `referred_at` datetime NOT NULL,
        `treated_at` datetime DEFAULT NULL,
        `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`),
        CONSTRAINT `{$this->base}referral_referrer_fk` FOREIGN KEY (`referrer_id`)
            REFERENCES {$this->wpdb->users} (`ID`) ON DELETE CASCADE,
        CONSTRAINT `{$this->base}referral_referee_fk` FOREIGN KEY (`referee_id`)
            REFERENCES {$this->wpdb->users} (`ID`) ON DELETE CASCADE
    )";
      // Create the main referrals table first
      $this->createTables($mainTable);

      // Rewards table
      $tables[BASE . 'referral_rewards'] = "(
        `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        `referral_id` bigint(20) unsigned NOT NULL,
        `user_id` bigint(20) unsigned 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->wpdb->prefix}" . BASE . "referrals (`id`) ON DELETE CASCADE,
        CONSTRAINT `{$this->base}reward_user_fk` FOREIGN KEY (`user_id`)
            REFERENCES {$this->wpdb->users} (`ID`) ON DELETE CASCADE
    )";
      // Now create dependent tables
      $dependentTables = [];

      return $tables;
      // 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;
   }

    /*******************************************************************************************
@@ -1238,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}",
@@ -1289,7 +1418,7 @@
        $limit = $fieldConfig['limit'] ?? null;

        if ($limit === 1) {
            return "bigint(20) unsigned {$nullable}";
            return "{$this->termIDType} {$nullable}";
        }

        // Multiple selections stored as JSON
@@ -1334,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";
@@ -1366,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";

@@ -1477,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",
@@ -1500,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'])) {
@@ -1600,7 +1727,7 @@

        $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);