<?php
|
namespace JVBase\registry;
|
|
if (!defined('ABSPATH')) {
|
exit;
|
}
|
|
use Exception;
|
|
class CheckCustomTables
|
{
|
protected $wpdb;
|
protected string $base;
|
protected string $prefix;
|
protected string $prefixed;
|
protected array $JVB_SITE;
|
protected array $JVB_MEMBERSHIP;
|
protected array $JVB_CONTENT;
|
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;
|
$this->wpdb = $wpdb;
|
$this->prefix = $wpdb->prefix;
|
$this->base = BASE;
|
$this->prefixed = $wpdb->prefix.BASE;
|
|
//Fetch the constants manually, in case they're not defined yet?
|
$this->JVB_SITE = apply_filters('jvb_site', []);
|
$this->JVB_MEMBERSHIP = apply_filters('jvb_membership', []);
|
$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');
|
}
|
|
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()
|
{
|
try {
|
error_log('JVB: Starting table creation process');
|
error_log('JVB: Memory usage at start: ' . memory_get_usage(true) / 1024 / 1024 . ' MB');
|
|
$tables = $calendar = $integrations = $karma = $stats = $invitable = $verifyEntry = $approval = $trackChanges = [];
|
|
// Basic tables (these worked fine)
|
try {
|
if (jvbCheck('has_membership', $this->JVB_SITE) && jvbCheck('member_content', $this->JVB_MEMBERSHIP)) {
|
$tables = array_merge($tables, $this->notificationTables());
|
// if ($this->JVB_SITE['umami']) {
|
// $tables = array_merge($tables, $this->umamiTracking());
|
// }
|
}
|
if (array_key_exists('can_invite', $this->JVB_MEMBERSHIP) && is_array($this->JVB_MEMBERSHIP['can_invite'])) {
|
foreach ($this->JVB_MEMBERSHIP['can_invite'] as $role => $canInvite) {
|
$invitable[$role]['can_invite'] = $canInvite;
|
}
|
}
|
|
// if (jvbCheck('social', $this->JVB_SITE) || jvbCheck('gmb', $this->JVB_SITE) || jvbCheck('square', $this->JVB_SITE) || jvbCheck('helcim', $this->JVB_SITE)) {
|
// $tables = array_merge($tables, $this->userIntegrationsTable());
|
// }
|
} catch (Exception $e) {
|
error_log("JVB: Error in notification section: " . $e->getMessage());
|
}
|
|
try {
|
// if (array_key_exists('dashboard', $this->JVB_SITE) && $this->JVB_SITE['dashboard']) {
|
$tables = array_merge($tables, $this->queueTables(), $this->errorLogTables());
|
// }
|
} catch (Exception $e) {
|
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 (array_key_exists('favourites', $this->JVB_SITE)) {
|
error_log('JVB: Creating favourites tables...');
|
$favouriteTables = $this->favouriteTables();
|
error_log('JVB: Favourites tables created: ' . count($favouriteTables));
|
$tables = array_merge($tables, $favouriteTables);
|
error_log('JVB: Memory after favourites: ' . memory_get_usage(true) / 1024 / 1024 . ' MB');
|
}
|
} catch (Exception $e) {
|
error_log("JVB: Error creating favourite tables: " . $e->getMessage());
|
}
|
|
// RE-ENABLE feed block tables
|
try {
|
if (jvbCheck('dashboard', $this->JVB_SITE) || jvbCheck('use_feed_block', $this->JVB_SITE)) {
|
error_log('JVB: Creating taxonomy relationship tables...');
|
$taxonomyTables = $this->taxonomyRelationshipsTables();
|
error_log('JVB: Taxonomy tables created: ' . count($taxonomyTables));
|
$tables = array_merge($tables, $taxonomyTables);
|
error_log('JVB: Memory after taxonomy relationships: ' . memory_get_usage(true) / 1024 / 1024 . ' MB');
|
}
|
} catch (Exception $e) {
|
error_log("JVB: Error creating taxonomy relationship tables: " . $e->getMessage());
|
}
|
|
// RE-ENABLE news relationships
|
try {
|
if (jvbCheck('has_membership', $this->JVB_SITE) && jvbCheck('forum', $this->JVB_MEMBERSHIP)) {
|
error_log('JVB: Creating news relationship tables...');
|
$newsTables = $this->newsRelationshipsTable();
|
error_log('JVB: News tables created: ' . count($newsTables));
|
$tables = array_merge($tables, $newsTables);
|
error_log('JVB: Memory after news relationships: ' . memory_get_usage(true) / 1024 / 1024 . ' MB');
|
}
|
} catch (Exception $e) {
|
error_log("JVB: Error creating news relationship tables: " . $e->getMessage());
|
}
|
|
$responseTable = false;
|
|
// Process content, taxonomy, and user types
|
foreach ($this->JVB_CONTENT as $type => $config) {
|
if (array_key_exists('is_calendar', $config) && $config['is_calendar']) {
|
$calendar[$type] = $config;
|
}
|
}
|
|
foreach ($this->JVB_TAXONOMY as $type => $config) {
|
// RE-ENABLE content type creation with error handling
|
try {
|
if (array_key_exists('is_content', $config) && $config['is_content']) {
|
error_log("JVB: Creating content type table for: {$type}");
|
error_log('JVB: Memory before content type ' . $type . ': ' . memory_get_usage(true) / 1024 / 1024 . ' MB');
|
|
$this->checkIfContentType($type, $config);
|
|
error_log('JVB: Memory after content type ' . $type . ': ' . memory_get_usage(true) / 1024 / 1024 . ' MB');
|
}
|
} catch (Exception $e) {
|
error_log("JVB: Error creating content type for {$type}: " . $e->getMessage());
|
}
|
|
if (array_key_exists('track_changes', $config) && $config['track_changes']) {
|
$trackChanges[$type] = $config;
|
}
|
if (array_key_exists('invitable', $config) && $config['invitable']) {
|
foreach ($config['for_content'] as $content) {
|
$invitable[$content]['to_terms'][] = $type;
|
}
|
}
|
if (array_key_exists('verify_entry', $config) && $config['verify_entry']) {
|
$verifyEntry[$type] = $config;
|
}
|
if (array_key_exists('approve_new', $config) && $config['approve_new']) {
|
$approval['term'][] = $type;
|
}
|
}
|
|
foreach ($this->JVB_USER as $type => $config) {
|
if (array_key_exists('keep_stats', $config) && $config['keep_stats']) {
|
$stats[$type] = $config;
|
}
|
if (array_key_exists('approve_new', $config) && $config['approve_new']) {
|
$approval[$type] = $config;
|
}
|
}
|
|
foreach (array_merge($this->JVB_CONTENT, $this->JVB_TAXONOMY, $this->JVB_USER) as $type => $config) {
|
if (array_key_exists('responses', $config) && $config['responses'] === true) {
|
$responseTable = true;
|
}
|
if (array_key_exists('karma', $config) && $config['karma']) {
|
$karma[$type] = $config;
|
}
|
}
|
|
// RE-ENABLE response tables
|
try {
|
if ($responseTable) {
|
error_log('JVB: Creating response tables...');
|
$responseTables = $this->responseTable();
|
error_log('JVB: Response tables created: ' . count($responseTables));
|
$tables = array_merge($tables, $responseTables);
|
error_log('JVB: Memory after responses: ' . memory_get_usage(true) / 1024 / 1024 . ' MB');
|
}
|
} catch (Exception $e) {
|
error_log("JVB: Error creating response tables: " . $e->getMessage());
|
}
|
|
// RE-ENABLE karma tables
|
try {
|
if (!empty($karma)) {
|
error_log('JVB: Creating karma tables for: ' . implode(', ', array_keys($karma)));
|
$karmaTables = $this->karmaTables($karma);
|
error_log('JVB: Karma tables created: ' . count($karmaTables));
|
$tables = array_merge($tables, $karmaTables);
|
error_log('JVB: Memory after karma: ' . memory_get_usage(true) / 1024 / 1024 . ' MB');
|
}
|
} catch (Exception $e) {
|
error_log("JVB: Error creating karma tables: " . $e->getMessage());
|
}
|
|
try {
|
if (!empty($stats)) {
|
error_log('JVB: Creating stats tables for: ' . implode(', ', array_keys($stats)));
|
error_log('JVB: Memory before stats: ' . memory_get_usage(true) / 1024 / 1024 . ' MB');
|
|
$statTables = $this->statTables($stats);
|
error_log('JVB: Stats tables created: ' . count($statTables));
|
$tables = array_merge($tables, $statTables);
|
|
error_log('JVB: Memory after stats: ' . memory_get_usage(true) / 1024 / 1024 . ' MB');
|
}
|
} catch (Exception $e) {
|
error_log("JVB: Error creating stats tables: " . $e->getMessage());
|
error_log("JVB: Stats error trace: " . $e->getTraceAsString());
|
}
|
|
// RE-ENABLE calendar tables
|
try {
|
if (!empty($calendar)) {
|
error_log('JVB: Creating calendar tables for: ' . implode(', ', array_keys($calendar)));
|
$calendarTables = $this->calendarTables($calendar);
|
error_log('JVB: Calendar tables created: ' . count($calendarTables));
|
$tables = array_merge($tables, $calendarTables);
|
error_log('JVB: Memory after calendar: ' . memory_get_usage(true) / 1024 / 1024 . ' MB');
|
}
|
} catch (Exception $e) {
|
error_log("JVB: Error creating calendar tables: " . $e->getMessage());
|
}
|
|
// RE-ENABLE other table types
|
try {
|
if (!empty($invitable)) {
|
error_log('JVB: Creating invitation tables...');
|
$invitationTables = $this->invitationTables($invitable);
|
error_log('JVB: Invitation tables created: ' . count($invitationTables));
|
$tables = array_merge($tables, $invitationTables);
|
error_log('JVB: Memory after invitations: ' . memory_get_usage(true) / 1024 / 1024 . ' MB');
|
}
|
} catch (Exception $e) {
|
error_log("JVB: Error creating invitation tables: " . $e->getMessage());
|
}
|
|
try {
|
if (!empty($approval)) {
|
error_log('JVB: Creating approval tables...');
|
$approvalTables = $this->approvalTables($approval);
|
error_log('JVB: Approval tables created: ' . count($approvalTables));
|
$tables = array_merge($tables, $approvalTables);
|
error_log('JVB: Memory after approvals: ' . memory_get_usage(true) / 1024 / 1024 . ' MB');
|
}
|
} catch (Exception $e) {
|
error_log("JVB: Error creating approval tables: " . $e->getMessage());
|
}
|
|
try {
|
if (!empty($trackChanges)) {
|
error_log('JVB: Creating track changes tables...');
|
$trackTables = $this->trackChangesTables($trackChanges);
|
error_log('JVB: Track changes tables created: ' . count($trackTables));
|
$tables = array_merge($tables, $trackTables);
|
error_log('JVB: Memory after track changes: ' . memory_get_usage(true) / 1024 / 1024 . ' MB');
|
}
|
} catch (Exception $e) {
|
error_log("JVB: Error creating track changes tables: " . $e->getMessage());
|
}
|
|
try {
|
if (!empty($verifyEntry)) {
|
error_log('JVB: Creating entry request tables...');
|
$entryTables = $this->requestEntryTables($verifyEntry);
|
error_log('JVB: Entry request tables created: ' . count($entryTables));
|
$tables = array_merge($tables, $entryTables);
|
error_log('JVB: Memory after entry requests: ' . memory_get_usage(true) / 1024 / 1024 . ' MB');
|
}
|
} catch (Exception $e) {
|
error_log("JVB: Error creating entry request tables: " . $e->getMessage());
|
}
|
|
error_log('JVB: Final table count: ' . count($tables));
|
error_log('JVB: Memory before table creation: ' . memory_get_usage(true) / 1024 / 1024 . ' MB');
|
|
if (!empty($tables)) {
|
$this->createTables($tables);
|
}
|
|
update_option(BASE.'calendar_tables', $calendar);
|
update_option(BASE.'karma_tables', $karma);
|
update_option(BASE.'stats_tables', $stats);
|
update_option(BASE.'invite_tables', $invitable);
|
update_option(BASE.'verify_entry_tables', $verifyEntry);
|
update_option(BASE.'approval_tables', $approval);
|
update_option(BASE.'history_tables', $trackChanges);
|
|
error_log('JVB: Memory at end: ' . memory_get_usage(true) / 1024 / 1024 . ' MB');
|
error_log("JVB: Table creation process completed");
|
|
} catch (Exception $e) {
|
error_log("JVB: Critical error in table creation: " . $e->getMessage());
|
error_log("JVB: Stack trace: " . $e->getTraceAsString());
|
error_log('JVB: Memory at error: ' . memory_get_usage(true) / 1024 / 1024 . ' MB');
|
}
|
}
|
|
public function createTables(array $tables)
|
{
|
require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
|
|
// Match collation from existing WP tables for FK compatibility
|
$usersStatus = $this->wpdb->get_row("SHOW TABLE STATUS LIKE '{$this->wpdb->users}'");
|
$parentCollation = $usersStatus->Collation ?? 'utf8mb4_general_ci';
|
$charset_collate = "DEFAULT CHARACTER SET utf8mb4 COLLATE {$parentCollation}";
|
|
error_log("JVB: Using charset_collate: " . $charset_collate);
|
|
$errors = [];
|
$created = [];
|
|
foreach ($tables as $name => $schema) {
|
$table_name = $this->prefix . BASE . $name;
|
|
// Skip if exists
|
if ($this->wpdb->get_var("SHOW TABLES LIKE '{$table_name}'")) {
|
$created[] = $table_name . ' (exists)';
|
continue;
|
}
|
|
$full_schema = "CREATE TABLE IF NOT EXISTS {$table_name} {$schema} {$charset_collate}";
|
|
$this->wpdb->flush();
|
|
// Use direct query - dbDelta mangles FK constraints
|
$hasForeignKey = stripos($schema, 'FOREIGN KEY') !== false;
|
|
if ($hasForeignKey) {
|
$result = $this->wpdb->query($full_schema);
|
$success = ($result !== false && !$this->wpdb->last_error);
|
} else {
|
dbDelta($full_schema . ';');
|
$success = !$this->wpdb->last_error;
|
}
|
|
if (!$success) {
|
$error_msg = "SQL Error creating table {$table_name}: " . $this->wpdb->last_error;
|
error_log($error_msg);
|
error_log("Failed SQL Query: " . $full_schema);
|
$errors[] = $error_msg;
|
} elseif ($this->wpdb->get_var("SHOW TABLES LIKE '{$table_name}'")) {
|
$created[] = $table_name;
|
error_log("Successfully created table: {$table_name}");
|
}
|
}
|
|
// Log summary
|
if (!empty($created)) {
|
error_log("JVB Tables Created Successfully: " . implode(', ', $created));
|
}
|
|
if (!empty($errors)) {
|
error_log("JVB Table Creation Errors (" . count($errors) . " total):");
|
foreach ($errors as $error) {
|
error_log(" - " . $error);
|
}
|
update_option(BASE . 'table_creation_errors', $errors);
|
} else {
|
delete_option(BASE . 'table_creation_errors');
|
}
|
}
|
|
/******************************************************
|
* Table Definitions
|
*****************************************************/
|
|
protected function queueTables():array
|
{
|
|
return [
|
'_operation_queue' => "(
|
`id` VARCHAR(64) NOT NULL,
|
`type` varchar(50) NOT NULL,
|
`user_id` {$this->userIDType} NOT NULL,
|
|
`request_data` JSON NOT NULL CHECK (JSON_VALID(request_data)),
|
|
`total_items` int(11) NOT NULL DEFAULT 1,
|
`processed_items` int(11) DEFAULT 0,
|
`failed_items` JSON,
|
|
`priority` ENUM('high', 'normal', 'low') DEFAULT 'normal',
|
`state` enum('pending', 'scheduled', 'processing', 'completed') DEFAULT 'pending',
|
`outcome` enum('pending', 'success', 'partial', 'failed','failed_permanent') DEFAULT 'pending',
|
|
`retries` int(11) DEFAULT 0,
|
`last_error_hash` CHAR(32) DEFAULT NULL,
|
`error_message` text,
|
|
`scheduled_at` datetime DEFAULT CURRENT_TIMESTAMP,
|
`started_at` datetime DEFAULT CURRENT_TIMESTAMP,
|
`completed_at` datetime DEFAULT NULL,
|
|
`metadata` JSON DEFAULT NULL,
|
`result` JSON,
|
`dependencies` JSON,
|
|
`user_dismissed` tinyint(1) DEFAULT 0,
|
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
PRIMARY KEY (`id`),
|
KEY `idx_run_queue` (state, priority, scheduled_at),
|
KEY `idx_user_ops` (user_id, state),
|
KEY `idx_user_type_pending` (user_id, type, state),
|
KEY `idx_completed_at` (completed_at),
|
KEY `idx_processing_stuck` (`state`, `started_at`)
|
)",
|
|
'stats__operation_queue' => "(
|
`id` bigint unsigned AUTO_INCREMENT,
|
`date` date NOT NULL,
|
`type` varchar(50) NOT NULL,
|
|
`total_operations` int NOT NULL DEFAULT 0,
|
`successful_operations` int NOT NULL DEFAULT 0,
|
`partial_operations` int NOT NULL DEFAULT 0,
|
`failed_operations` int NOT NULL DEFAULT 0,
|
`failed_permanent_operations` int NOT NULL DEFAULT 0,
|
|
`total_items_processed` int NOT NULL DEFAULT 0,
|
|
`average_duration` float DEFAULT NULL,
|
`max_duration` int DEFAULT NULL,
|
|
`peak_queue_size` int NOT NULL DEFAULT 0,
|
|
`peak_memory_usage` int DEFAULT NULL,
|
`peak_cpu_usage` float DEFAULT NULL,
|
|
`created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE KEY (date, type),
|
KEY `date_idx` (date),
|
KEY `type_idx` (type)
|
)"
|
];
|
}
|
|
protected function errorLogTables():array
|
{
|
return [
|
'error_log'=> "(
|
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
`error_type` varchar(50) NOT NULL,
|
`component` varchar(100) NOT NULL,
|
`method` varchar(100) DEFAULT NULL,
|
`page_url` varchar(255) DEFAULT NULL,
|
`message` text NOT NULL,
|
`context` JSON,
|
`severity` varchar(20) NOT NULL,
|
`user_id` {$this->userIDType} DEFAULT NULL,
|
`user_was_logged_in` tinyint(1) NOT NULL,
|
`source` enum('frontend','backend') NOT NULL,
|
`created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
|
PRIMARY KEY (`id`),
|
KEY `created_at` (`created_at`),
|
KEY `component_severity_date` (`component`, `severity`, `created_at`),
|
KEY `error_type_date` (`error_type`, `created_at`),
|
KEY `severity_date` (`severity`, `created_at`)
|
)"
|
];
|
}
|
|
protected function userIntegrationsTable():array
|
{
|
|
return [
|
'user_integration_logs'=> "(
|
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
`user_id` {$this->userIDType} NOT NULL,
|
service varchar(50) NOT NULL,
|
action varchar(50) NOT NULL,
|
status enum('success','error','warning') DEFAULT 'success',
|
message text DEFAULT NULL,
|
metadata json DEFAULT NULL,
|
created_at datetime DEFAULT CURRENT_TIMESTAMP,
|
PRIMARY KEY (id),
|
KEY user_service (user_id, service),
|
KEY created_at (created_at),
|
KEY status (status)
|
)"
|
];
|
}
|
|
protected function notificationTables():array
|
{
|
|
return [
|
// Main notifications table
|
'notifications' => "(
|
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
`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',
|
'system_message','shop_invitation') NOT NULL,
|
`status` enum('unread','read','actioned','dismissed') NOT NULL DEFAULT 'unread',
|
`priority` enum('low','normal','high') NOT NULL DEFAULT 'normal',
|
`message` varchar(255) DEFAULT NULL,
|
`context` JSON DEFAULT NULL,
|
`requires_action` tinyint(1) NOT NULL DEFAULT 0,
|
`action_taken` tinyint(1) NOT NULL DEFAULT 0,
|
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
`read_at` datetime DEFAULT NULL,
|
`actioned_at` datetime DEFAULT NULL,
|
`emailed_at` datetime DEFAULT NULL,
|
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
PRIMARY KEY (`id`),
|
KEY `user_status` (`owner_id`, `status`),
|
KEY `target_lookup` (`target_id`, `target_type`),
|
KEY `unread_notifications` (`owner_id`, `status`, `created_at`),
|
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->userTable}` (`ID`) ON DELETE CASCADE,
|
CONSTRAINT `{$this->base}action_id` FOREIGN KEY (`action_user_id`)
|
REFERENCES `{$this->userTable}` (`ID`) ON DELETE CASCADE
|
)",
|
|
|
'notifications_content' => "(
|
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
`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,
|
`artwork_count` int unsigned NOT NULL DEFAULT 0,
|
`piercing_count` int unsigned NOT NULL DEFAULT 0,
|
`event_count` int unsigned NOT NULL DEFAULT 0,
|
`news_count` int unsigned NOT NULL DEFAULT 0,
|
`offer_count` int unsigned NOT NULL DEFAULT 0,
|
`total_items` int unsigned NOT NULL DEFAULT 0,
|
`has_profile_update` tinyint(1) NOT NULL DEFAULT 0,
|
`new_items` JSON DEFAULT NULL,
|
`updated_items` JSON DEFAULT NULL,
|
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
PRIMARY KEY (`id`),
|
UNIQUE KEY `artist_date_frequency` (`user_id`, `date`, `frequency`),
|
KEY `recent_content` (`date`, `frequency`),
|
KEY `artist_frequency` (`user_id`, `frequency`),
|
CONSTRAINT `{$this->base}content_artist` FOREIGN KEY (`user_id`)
|
REFERENCES `{$this->userTable}` (`ID`) ON DELETE CASCADE
|
)",
|
|
'notifications_user_seen' => "(
|
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
`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,
|
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
PRIMARY KEY (`id`),
|
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->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
|
)",
|
|
// User notification preferences
|
'notification_preferences' => "(
|
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
`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,
|
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
|
`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
PRIMARY KEY (`id`),
|
UNIQUE KEY `user_type` (`user_id`, `item_id`),
|
KEY `user_frequency` (`user_id`, `frequency`),
|
KEY `frequency_lookup` (`frequency`, `last_sent`),
|
CONSTRAINT `{$this->base}notification_pref_user` FOREIGN KEY (`user_id`)
|
REFERENCES `{$this->userTable}` (`ID`) ON DELETE CASCADE
|
)",
|
|
// Notification digest scheduling and tracking
|
'notification_digests' => "(
|
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
`user_id` {$this->userIDType} NOT NULL,
|
`frequency` enum('daily','weekly','monthly') NOT NULL,
|
`scheduled_at` datetime NOT NULL,
|
`sent_at` datetime DEFAULT NULL,
|
`status` enum('pending','processing','sent','failed') DEFAULT 'pending',
|
`notification_count` int unsigned DEFAULT 0,
|
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
|
PRIMARY KEY (`id`),
|
KEY `scheduled_digests` (`frequency`, `scheduled_at`, `status`),
|
KEY `user_digests` (`user_id`, `frequency`),
|
CONSTRAINT `{$this->base}digest_user` FOREIGN KEY (`user_id`)
|
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` {$this->userIDType} NOT NULL,
|
`action` varchar(30) NOT NULL,
|
`action_source` enum('web','email','app') DEFAULT 'web',
|
`action_details` JSON DEFAULT NULL,
|
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
|
PRIMARY KEY (`id`),
|
KEY `notification_lookup` (`notification_id`),
|
KEY `user_actions` (`user_id`, `action`),
|
KEY `action_analysis` (`action`, `action_source`),
|
CONSTRAINT `{$this->base}metrics_user` FOREIGN KEY (`user_id`)
|
REFERENCES `{$this->userTable}` (`ID`) ON DELETE CASCADE,
|
CONSTRAINT `{$this->base}metrics_notification` FOREIGN KEY (`notification_id`)
|
REFERENCES `{$this->prefixed}notifications` (`id`) ON DELETE CASCADE
|
)"
|
];
|
}
|
|
protected function approvalTables($types):array
|
{
|
$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` {$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,
|
`current_rejections` int unsigned DEFAULT 0,
|
`expires_at` datetime DEFAULT NULL,
|
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
|
`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
`approved_by` json DEFAULT NULL,
|
`rejected_by` json DEFAULT NULL,
|
`created_item` json DEFAULT NULL,
|
PRIMARY KEY (`id`),
|
KEY `status` (`status`),
|
KEY `expiring_requests` (`status`, `expires_at`),
|
CONSTRAINT `{$this->base}{$type}_approval_requester` FOREIGN KEY (`user_id`)
|
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` {$this->userIDType} NOT NULL,
|
`vote` enum('approve','reject','dismissed') NOT NULL,
|
`notes` text DEFAULT NULL,
|
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
|
PRIMARY KEY (`id`),
|
UNIQUE KEY `unique_vote` (`request_id`, `user_id`),
|
KEY `user_votes` (`user_id`),
|
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->userTable}` (`ID`) ON DELETE CASCADE
|
)";
|
}
|
if (!empty($save)) {
|
update_option(BASE.'approvals_types', $save);
|
}
|
return $tables;
|
}
|
|
|
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
|
)"
|
];
|
|
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;
|
}
|
|
protected function favouriteTables():array
|
{
|
|
return [
|
'favourites' => "(
|
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
`user_id` {$this->userIDType} NOT NULL,
|
`type` varchar(50) 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`),
|
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` {$this->userIDType} NOT NULL,
|
`name` varchar(255) NOT NULL,
|
`description` text,
|
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
PRIMARY KEY (`id`),
|
KEY `user_lists` (`user_id`),
|
CONSTRAINT `{$this->base}list_user` FOREIGN KEY (`user_id`)
|
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) NOT NULL,
|
`favourite_id` bigint(20) unsigned DEFAULT NULL,
|
`added_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
PRIMARY KEY (`id`),
|
UNIQUE KEY `unique_list_item` (`list_id`, `item_type`, `item_id`),
|
KEY `list_items` (`list_id`),
|
KEY `favourite_id` (`favourite_id`),
|
CONSTRAINT `{$this->base}list_items` FOREIGN KEY (`list_id`)
|
REFERENCES `{$this->prefixed}favourites_lists` (`id`) ON DELETE CASCADE,
|
CONSTRAINT `{$this->base}list_favourite` FOREIGN KEY (`favourite_id`)
|
REFERENCES `{$this->prefixed}favourites` (`id`) ON DELETE SET NULL
|
)",
|
'favourites_list_shares' => "(
|
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
`list_id` bigint(20) unsigned NOT 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',
|
`invitation_token` varchar(64) NULL,
|
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
PRIMARY KEY (`id`),
|
UNIQUE KEY `unique_share_user` (`list_id`, `user_id`, `status`),
|
UNIQUE KEY `unique_share_email` (`list_id`, `email`, `status`),
|
KEY `list_shares` (`list_id`),
|
KEY `status_index` (`status`),
|
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->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) NOT NULL,
|
`list_count` int NOT NULL DEFAULT 0,
|
`last_added` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
PRIMARY KEY (`id`),
|
UNIQUE KEY `unique_item_stat` (`item_type`, `item_id`),
|
KEY `item_stats` (`item_type`, `item_id`)
|
)",
|
];
|
}
|
|
protected function newsRelationshipsTable():array
|
{
|
return [
|
'news_relationships' => "(
|
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
`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,
|
PRIMARY KEY (`id`),
|
UNIQUE KEY `shop_user` (`shop_id`, `user_id`),
|
KEY `shop_id` (`shop_id`),
|
KEY `user_id` (`user_id`),
|
KEY `artist_id` (`artist_id`),
|
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->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
|
)"
|
];
|
}
|
|
protected function responseTable():array
|
{
|
return [
|
'responses' => "(
|
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
`item_id` {$this->postIDType} NOT NULL,
|
`content` text NOT 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',
|
`is_user_deleted` tinyint(1) DEFAULT 0,
|
`upvotes` int NOT NULL DEFAULT 0,
|
`downvotes` int NOT NULL DEFAULT 0,
|
`karma` int NOT NULL DEFAULT 0,
|
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
|
`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
PRIMARY KEY (`id`),
|
KEY `item_lookup` (`item_id`, `status`),
|
KEY `user_comments` (`user_id`),
|
KEY `parent_child` (`parent_id`),
|
KEY `karma_order` (`karma`),
|
CONSTRAINT `{$this->base}re_responses_news` FOREIGN KEY (`item_id`)
|
REFERENCES `{$this->wpdb->posts}` (`ID`) ON DELETE CASCADE,
|
CONSTRAINT `{$this->base}re_responses_parent` FOREIGN KEY (`parent_id`)
|
REFERENCES `{$this->prefixed}responses` (`id`) ON DELETE SET NULL
|
)",
|
'karma_response' => "(
|
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
`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`),
|
UNIQUE KEY `user_post` (`user_id`,`item_id`),
|
KEY `item_id` (`item_id`),
|
KEY `user_id` (`user_id`),
|
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->userTable}` (`ID`) ON DELETE CASCADE
|
)"
|
];
|
}
|
|
protected function karmaTables(array $types):array
|
{
|
$tables = [];
|
foreach ($types as $type => $config) {
|
$t = false;
|
if (array_key_exists($type, $this->JVB_CONTENT)) {
|
$t = 'posts';
|
} elseif (array_key_exists($type, $this->JVB_TAXONOMY)) {
|
$t = 'terms';
|
} elseif (array_key_exists($type, $this->JVB_USER)) {
|
$t = 'users';
|
}
|
|
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':
|
$referenceType = $this->userIDType;
|
$reference_table = $this->userTable;
|
$reference_column = 'ID';
|
break;
|
}
|
|
$tables['karma_'.$type] = "(
|
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
`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`),
|
UNIQUE KEY `user_post` (`user_id`,`item_id`),
|
KEY `item_id` (`item_id`),
|
KEY `user_id` (`user_id`),
|
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->userTable}` (`ID`) ON DELETE CASCADE
|
)";
|
|
}
|
|
return $tables;
|
}
|
|
protected function calendarTables(array $types):array
|
{
|
$tables = [];
|
foreach ($types as $type => $config) {
|
$tables['calendar_'.$type] = "(
|
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
`post_id` {$this->postIDType} NOT NULL,
|
`event_type` {$this->termIDType} unsigned,
|
|
-- Basic event details
|
`title` varchar(255) NOT NULL,
|
`shop_id` {$this->termIDType} NOT NULL
|
`user_id` {$this->userIDType} NOT NULL,
|
|
-- Location handling
|
`location_type` enum('shop', 'custom', 'online') DEFAULT 'shop',
|
`address` text,
|
`lat` decimal(10,8),
|
`lng` decimal(11,8),
|
|
-- Date and time handling
|
`start_date` date NOT NULL,
|
`end_date` date NOT NULL,
|
`time_start` time,
|
`time_end` time,
|
`timezone` varchar(50) DEFAULT 'America/Edmonton',
|
|
-- Recurrence
|
`is_recurring` tinyint(1) DEFAULT 0,
|
`recurrence_type` enum('daily','weekly','monthly','custom') DEFAULT NULL,
|
`recurrence_interval` int unsigned DEFAULT NULL, -- every X days/weeks/months
|
`recurrence_days` set('monday','tuesday','wednesday','thursday','friday','saturday','sunday'),
|
`recurrence_ends` date,
|
`recurrence_count` int unsigned, -- number of occurrences
|
`parent_event_id` bigint(20) unsigned, -- for recurring event instances
|
|
-- Event specifics
|
`is_free` tinyint(1) DEFAULT 0,
|
`cost` decimal(10,2),
|
`currency` varchar(3) DEFAULT 'CAD',
|
`rsvp_required` tinyint(1) DEFAULT 0,
|
`rsvp_limit` int unsigned,
|
`rsvp_deadline` datetime,
|
`ticket_url` varchar(255),
|
|
-- Event settings
|
`all_day` tinyint(1) DEFAULT 0,
|
`drop_in_allowed` tinyint(1) DEFAULT 0,
|
`booking_url` varchar(255),
|
`external_url` varchar(255),
|
|
-- Tracking
|
`view_count` int unsigned DEFAULT 0,
|
`interest_count` int unsigned DEFAULT 0,
|
`going_count` int unsigned DEFAULT 0,
|
`created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
|
`updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
KEY `post_lookup` (`post_id`),
|
KEY `date_range` (`start_date`, `end_date`),
|
KEY `shop_events` (`shop_id`, `start_date`),
|
KEY `artist_events` (`user_id`, `start_date`),
|
KEY `event_cost` (`is_free`, `cost`),
|
KEY `recurring_events` (`parent_event_id`),
|
KEY `location` (`lat`, `lng`),
|
KEY `rsvp_events` (`rsvp_required`, `rsvp_deadline`),
|
|
CONSTRAINT `{$this->base}cal_{$type}_type` FOREIGN KEY (`event_type`)
|
REFERENCES `{$this->wpdb->terms}` (`term_id`) ON DELETE CASCADE,
|
CONSTRAINT `{$this->base}cal_{$type}_post` FOREIGN KEY (`post_id`)
|
REFERENCES `{$this->wpdb->posts}` (`ID`) ON DELETE CASCADE,
|
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->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` {$this->userIDType} NOT NULL,
|
`status` enum('interested','going') NOT NULL,
|
`created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
|
PRIMARY KEY (`id`),
|
UNIQUE KEY `event_user` (`event_id`, `user_id`),
|
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->userTable}` (`ID`) ON DELETE CASCADE
|
)";
|
|
$tables['calendar_'.$type.'_recurrence_exceptions'] = "(
|
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
`event_id` bigint(20) unsigned NOT NULL,
|
`exception_date` date NOT NULL,
|
`is_cancelled` tinyint(1) DEFAULT 0,
|
`alternate_start_time` time,
|
`alternate_end_time` time,
|
`notes` text,
|
PRIMARY KEY (`id`),
|
UNIQUE KEY `event_date` (`event_id`, `exception_date`)
|
)";
|
}
|
|
return $tables;
|
}
|
|
protected function umamiTracking():array
|
{
|
|
return [
|
'umami_events' => "(
|
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
`date` date NOT NULL,
|
`timestamp` datetime NOT NULL,
|
`event` varchar(50) NOT NULL,
|
`event_type` varchar(50) NOT NULL,
|
`user_id` {$this->userIDType} NOT NULL,
|
`content_id` bigint(20) DEFAULT NULL,
|
`content_type` varchar(50) DEFAULT NULL,
|
`source_id` bigint(20) DEFAULT NULL,
|
`source_type` varchar(50) DEFAULT NULL,
|
`owner_id` {$this->userIDType} NOT NULL,
|
`owner_type` varchar(50) DEFAULT NULL,
|
`referrer` varchar(100) DEFAULT NULL,
|
`metadata` JSON DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
KEY `date_idx` (`date`),
|
KEY `event_idx` (`event`, `event_type`),
|
KEY `content_idx` (`content_type`, `content_id`),
|
KEY `user_idx` (`user_id`),
|
KEY `owner_idx` (`owner_id`),
|
CONSTRAINT `{$this->base}umami_user_id_link` FOREIGN KEY (`user_id`)
|
REFERENCES `{$this->userTable}` (`ID`) ON DELETE CASCADE,
|
CONSTRAINT `{$this->base}umami_owner_id_link` FOREIGN KEY (`owner_id`)
|
REFERENCES `{$this->userTable}` (`ID`) ON DELETE CASCADE
|
)",
|
'stats_performance' => "(
|
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
`date` date NOT 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,
|
`favourite_count` bigint(20) unsigned DEFAULT 0,
|
`top_favourites` json DEFAULT null,
|
`upvote_count` bigint(20) unsigned DEFAULT 0,
|
`downvote_count` bigint(20) unsigned DEFAULT 0,
|
`karma` bigint(20) unsigned DEFAULT 0,
|
`voted_content` json DEFAULT null,
|
PRIMARY KEY (`id`),
|
KEY `user_date_idx` (`user_id`, `date`),
|
CONSTRAINT `{$this->base}performance_user_id_link` FOREIGN KEY (`user_id`)
|
REFERENCES `{$this->userTable}` (`ID`) ON DELETE CASCADE
|
)"
|
];
|
}
|
|
protected function invitationTables($types)
|
{
|
|
$tables = [];
|
foreach ($types as $role => $config) {
|
$definitions = "(
|
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
`name` varchar(255) NOT NULL,
|
`email` varchar(255) NOT NULL,
|
`invitation_token` varchar(64) NOT NULL,
|
`status` enum('pending', 'accepted', 'rejected', 'expired','revoked') DEFAULT 'pending',
|
`inviters` JSON NOT NULL,";
|
foreach($config['to_terms']??[] as $term) {
|
$definitions .= "`to_{$term}` {$this->termIDType} 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,
|
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
PRIMARY KEY (`id`),
|
UNIQUE KEY `unique_email` (`email`),
|
KEY `token_lookup` (`invitation_token`),
|
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;
|
}
|
|
return $tables;
|
}
|
|
protected function trackChangesTables($types)
|
{
|
$tables = [];
|
foreach ($types as $type => $config) {
|
$contents = $config['for_content'];
|
foreach ($contents as $content) {
|
$tables['history_'.$content.'_'.$type] = "(
|
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
`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,
|
`end_date` date DEFAULT NULL,
|
`created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
|
PRIMARY KEY (`id`),
|
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->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
|
)";
|
}
|
}
|
|
return $tables;
|
}
|
protected function requestEntryTables($types)
|
{
|
$tables = [];
|
foreach ($types as $type => $config) {
|
$contents = $config['for_content'];
|
foreach ($contents as $content) {
|
$tables[$content.'_'.$type.'_requests'] = "(
|
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
`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,
|
`created_date` timestamp DEFAULT CURRENT_TIMESTAMP,
|
`updated_date` timestamp DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
|
`notes` text DEFAULT NULL,
|
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->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
|
)";
|
}
|
}
|
|
return $tables;
|
}
|
|
|
/**
|
* 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
|
******************************************************************************************/
|
|
/**
|
* Generate SQL column definition based on field type and config
|
*/
|
private function generateColumnDefinition(string $fieldName, array $fieldConfig):string|array
|
{
|
$type = $fieldConfig['type'];
|
$nullable = ($fieldConfig['required'] ?? false) ? 'NOT NULL' : 'DEFAULT NULL';
|
|
return match ($type) {
|
'text', 'textarea' => $this->getTextColumnDefinition($fieldConfig, $nullable),
|
'email' => "varchar(255) {$nullable}",
|
'tel', 'phone' => "varchar(50) {$nullable}",
|
'url' => "varchar(2083) {$nullable}",
|
'number' => $this->getNumberColumnDefinition($fieldConfig, $nullable),
|
'date' => "date {$nullable}",
|
'time' => "time {$nullable}",
|
'datetime' => "datetime {$nullable}",
|
'true_false' => "boolean DEFAULT FALSE",
|
'image', 'file' => "bigint(20) {$nullable}",
|
'taxonomy' => $this->getTaxonomyColumnDefinition($fieldConfig, $nullable),
|
'user' => "bigint(20) {$nullable}",
|
'location' => $this->getLocationColumnDefinition($fieldName, $nullable),
|
'select', 'radio' => $this->getSelectColumnDefinition($fieldConfig, $nullable),
|
'set', 'checkbox', 'repeater', 'gallery' => "json {$nullable}",
|
default => "text {$nullable}"
|
};
|
}
|
|
private function getTextColumnDefinition(array $fieldConfig, string $nullable): string
|
{
|
$limit = $fieldConfig['limit'] ?? null;
|
|
if ($limit) {
|
if ($limit <= 255) {
|
return "varchar({$limit}) {$nullable}";
|
}
|
return "text {$nullable}";
|
}
|
|
// Default text field length
|
return ($fieldConfig['type'] === 'textarea') ? "text {$nullable}" : "varchar(255) {$nullable}";
|
}
|
|
private function getNumberColumnDefinition(array $fieldConfig, string $nullable): string
|
{
|
$min = $fieldConfig['min'] ?? null;
|
$max = $fieldConfig['max'] ?? null;
|
|
// Determine appropriate numeric type based on constraints
|
if ($min !== null && $min >= 0) {
|
if ($max !== null && $max <= 65535) {
|
return "smallint unsigned {$nullable}";
|
}
|
return "int unsigned {$nullable}";
|
}
|
|
if ($max !== null && $max <= 32767 && ($min === null || $min >= -32768)) {
|
return "smallint {$nullable}";
|
}
|
|
return "int {$nullable}";
|
}
|
|
//TODO: Add option to store name instead of id
|
private function getTaxonomyColumnDefinition(array $fieldConfig, string $nullable): string
|
{
|
$limit = $fieldConfig['limit'] ?? null;
|
|
if ($limit === 1) {
|
return "{$this->termIDType} {$nullable}";
|
}
|
|
// Multiple selections stored as JSON
|
return "json {$nullable}";
|
}
|
|
private function getLocationColumnDefinition(string $fieldName, string $nullable):array
|
{
|
return [
|
"{$fieldName}_address" => "text {$nullable}",
|
"{$fieldName}_lat" => "decimal(10,8) {$nullable}",
|
"{$fieldName}_lng" => "decimal(11,8) {$nullable}",
|
"{$fieldName}_street" => "varchar(255) {$nullable}",
|
"{$fieldName}_city" => "varchar(255) {$nullable}",
|
"{$fieldName}_province" => "varchar(255) {$nullable}",
|
"{$fieldName}_postal_code" => "varchar(20) {$nullable}",
|
"{$fieldName}_country" => "varchar(255) {$nullable}"
|
];
|
}
|
|
private function getSelectColumnDefinition(array $fieldConfig, string $nullable): string
|
{
|
$options = $fieldConfig['options'] ?? [];
|
$maxLength = max(array_map('strlen', array_keys($options)));
|
|
return "varchar(" . min(255, max(50, $maxLength + 10)) . ") {$nullable}";
|
}
|
|
/**
|
* Generate foreign key constraints for taxonomy and user fields
|
*/
|
private function generateConstraints(string $base, array $contentFields, string $tableName): array
|
{
|
$constraints = [];
|
|
foreach ($contentFields as $fieldName => $fieldConfig) {
|
$type = $fieldConfig['type'];
|
$constraintName = BASE."{$base}_{$fieldName}";
|
|
if ($type === 'taxonomy') {
|
$constraints[] = "CONSTRAINT `{$constraintName}` FOREIGN KEY (`{$fieldName}`)
|
REFERENCES `{$this->wpdb->terms}` (`term_id`) ON DELETE SET NULL";
|
} elseif ($type === 'user') {
|
$constraints[] = "CONSTRAINT `{$constraintName}` FOREIGN KEY (`{$fieldName}`)
|
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";
|
}
|
}
|
|
return $constraints;
|
}
|
|
private function checkIfContentType(string $type, array $config): void
|
{
|
if (!array_key_exists('is_content', $config) || !$config['is_content']) {
|
return;
|
}
|
|
try {
|
$contentFields = jvbGetFields($type);
|
|
if (!$contentFields || empty($contentFields)) {
|
return;
|
}
|
|
|
} catch (Exception $e) {
|
error_log("JVB: Error getting content fields for {$type}: " . $e->getMessage());
|
return;
|
}
|
|
try {
|
$tableName = 'content_' . $type;
|
$columns = [];
|
// Always include primary key
|
$columns[] = "`term_id` {$this->termIDType} NOT NULL";
|
$columns[] = "`name` varchar(255) NOT NULL";
|
$columns[] = "`slug` varchar(255) NOT NULL";
|
|
// Check if fields exist and are array
|
if (!isset($config['content_table']['fields']) || !is_array($config['content_table']['fields'])) {
|
error_log("JVB: Invalid or missing fields structure for {$type}");
|
return;
|
}
|
|
$fields = [];
|
// Generate columns for content table fields
|
foreach ($config['content_table']['fields'] as $fieldName) {
|
try {
|
$fieldConfig = $contentFields[$fieldName]??false;
|
if (!is_array($fieldConfig)) {
|
error_log("JVB: Invalid field config for {$fieldName}");
|
continue;
|
}
|
$fields[$fieldName] = $fieldConfig;
|
|
$columnDef = $this->generateColumnDefinition($fieldName, $fieldConfig);
|
|
if (is_array($columnDef)) {
|
// Handle location fields that create multiple columns
|
foreach ($columnDef as $colName => $colDef) {
|
$columns[] = "`{$colName}` {$colDef}";
|
}
|
} else {
|
$columns[] = "`{$fieldName}` {$columnDef}";
|
}
|
|
} catch (Exception $e) {
|
error_log("JVB: Error generating column definition for {$fieldName}: " . $e->getMessage());
|
continue;
|
}
|
}
|
|
// Add timestamp
|
$columns[] = "`updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP";
|
|
// Generate constraints and indexes
|
$base = BASE . 'content';
|
|
$constraints = $this->generateConstraints($base, $fields, $tableName);
|
|
$indexes = [];
|
|
// Add any defined keys
|
if (array_key_exists('keys', $config['content_table'])) {
|
error_log("JVB: Processing keys for {$type}");
|
foreach ($config['content_table']['keys'] as $key => $types) {
|
$types = "`" . implode("`,`", $types) . "`";
|
$indexes[] = "KEY `{$base}{$key}` ({$types})";
|
error_log("JVB: Added key: {$key}");
|
}
|
}
|
|
// Always add name index
|
$indexes[] = "KEY `{$base}name_idx` (`name`)";
|
|
// Combine everything
|
$allDefinitions = array_merge(
|
$columns,
|
["PRIMARY KEY (`term_id`)"],
|
$indexes,
|
$constraints,
|
["CONSTRAINT `{$base}_{$type}_term` FOREIGN KEY (`term_id`) REFERENCES `{$this->wpdb->terms}` (`term_id`) ON DELETE CASCADE"]
|
);
|
$sql = "(\n " . implode(",\n ", $allDefinitions) . "\n)";
|
error_log("JVB: Creating content table for type: {$type}");
|
$this->createTables([$tableName => $sql]);
|
|
error_log('JVB: Memory after creating table: ' . memory_get_usage(true) / 1024 / 1024 . ' MB');
|
|
} catch (Exception $e) {
|
error_log("JVB: Failed to create content table for {$type}: " . $e->getMessage());
|
}
|
|
error_log("JVB: Finished checkIfContentType for: {$type}");
|
}
|
|
/***********************************************
|
* The following methods are used to create the user stat tables
|
**********************************************/
|
protected function statTables(array $types): array
|
{
|
$tables = [];
|
|
foreach ($types as $userType => $config) {
|
// Get the post types this user can create
|
$canCreate = $config['can_create'] ?? [];
|
|
// Build dynamic columns based on what they can create
|
$columns = $this->buildUserStatsColumns($userType, $canCreate, $config);
|
|
$tables["stats_{$userType}"] = $this->generateUserStatsTable($userType, $columns);
|
}
|
|
return $tables;
|
}
|
|
private function buildUserStatsColumns(string $userType, array $canCreate, array $config): array
|
{
|
error_log("JVB: Building stats columns for user type: {$userType}");
|
error_log('JVB: Memory at start of buildUserStatsColumns: ' . memory_get_usage(true) / 1024 / 1024 . ' MB');
|
|
// Base columns that every user stats table should have
|
$columns = [
|
"`user_id` {$this->userIDType} NOT NULL",
|
"`display_name` VARCHAR(255) NULL",
|
"`email` VARCHAR(255) NULL",
|
"`city` VARCHAR(100) NULL",
|
"`last_login` TIMESTAMP",
|
"`joined` TIMESTAMP DEFAULT CURRENT_TIMESTAMP",
|
];
|
|
error_log('JVB: Base columns added: ' . count($columns));
|
|
// Email notification columns
|
if (array_key_exists('email_notifications', $this->JVB_SITE) && $this->JVB_SITE['email_notifications']) {
|
$columns[] = "`general_updates` BOOLEAN DEFAULT FALSE";
|
$columns[] = "`notification_preference` ENUM('never', 'daily', 'weekly', 'monthly') DEFAULT 'never'";
|
error_log('JVB: Email notification columns added');
|
}
|
|
// Favourites column
|
if (array_key_exists('favourites', $this->JVB_SITE) && $this->JVB_SITE['favourites']) {
|
$columns[] = "`favourites_count` INT DEFAULT 0";
|
error_log('JVB: Favourites column added');
|
}
|
|
if (isset($config['profile_type'])) {
|
$profileType = $config['profile_type'];
|
error_log("JVB: Processing profile type: {$profileType}");
|
|
|
if (isset($this->JVB_CONTENT[$profileType]) &&
|
isset($this->JVB_CONTENT[$profileType]['fields']) &&
|
is_array($this->JVB_CONTENT[$profileType]['fields'])) {
|
|
error_log('JVB: Profile fields found, processing...');
|
|
try {
|
// SAFE iteration through profile fields
|
foreach ($this->JVB_CONTENT[$profileType]['fields'] as $fieldName => $fieldConfig) {
|
// SAFE check for use_in_stats flag
|
if (isset($fieldConfig['use_in_stats']) && $fieldConfig['use_in_stats'] === true) {
|
error_log("JVB: Processing stats field: {$fieldName}");
|
|
try {
|
$columnDef = $this->generateColumnDefinition($fieldName, $fieldConfig);
|
if (is_array($columnDef)) {
|
// Handle location fields that create multiple columns
|
foreach ($columnDef as $colName => $colDef) {
|
$columns[] = "`{$colName}` {$colDef}";
|
}
|
} else {
|
$columns[] = "`{$fieldName}` {$columnDef}";
|
}
|
error_log("JVB: Added stats column for field: {$fieldName}");
|
} catch (Exception $e) {
|
error_log("JVB: Error processing stats field {$fieldName}: " . $e->getMessage());
|
continue;
|
}
|
}
|
}
|
} catch (Exception $e) {
|
error_log("JVB: Error processing profile fields: " . $e->getMessage());
|
}
|
} else {
|
error_log("JVB: No profile fields found for {$profileType}");
|
}
|
}
|
|
error_log('JVB: Processing canCreate: ' . json_encode($canCreate));
|
|
// SAFE processing of content types they can create
|
if (!empty($canCreate) && is_array($canCreate)) {
|
foreach ($canCreate as $contentType) {
|
try {
|
if (is_array($contentType)) {
|
// Handle nested array structure like: ['tattoo-artist' => ['tattoo', 'artwork']]
|
foreach ($contentType as $roleType => $contentTypes) {
|
if (is_array($contentTypes)) {
|
foreach ($contentTypes as $t) {
|
if (is_string($t) && array_key_exists($t, $this->JVB_CONTENT)) {
|
$columns[] = "`{$t}_count` INT DEFAULT 0";
|
error_log("JVB: Added nested content type column: {$t}");
|
}
|
}
|
}
|
}
|
} else {
|
// Handle simple content type
|
if (is_string($contentType) && array_key_exists($contentType, $this->JVB_CONTENT)) {
|
$columns[] = "`{$contentType}_count` INT DEFAULT 0";
|
error_log("JVB: Added simple content type column: {$contentType}");
|
}
|
}
|
} catch (Exception $e) {
|
error_log("JVB: Error processing content type in stats: " . $e->getMessage());
|
continue;
|
}
|
}
|
}
|
|
$uniqueColumns = array_unique($columns);
|
error_log('JVB: Final stats column count: ' . count($uniqueColumns));
|
error_log('JVB: Memory at end of buildUserStatsColumns: ' . memory_get_usage(true) / 1024 / 1024 . ' MB');
|
|
return $uniqueColumns;
|
}
|
|
|
/**
|
* Generate the complete SQL for a user stats table
|
*/
|
private function generateUserStatsTable(string $userType, array $columns): string
|
{
|
// Add indexes and constraints
|
$indexes = [
|
"PRIMARY KEY (`user_id`)",
|
"KEY `display_name_idx` (`display_name`)",
|
"KEY `email_idx` (`email`)",
|
"KEY `last_login_idx` (`last_login`)",
|
"KEY `notification_pref_idx` (`notification_preference`)"
|
];
|
|
$constraints = [
|
"CONSTRAINT `{$this->base}_{$userType}_stats_user` FOREIGN KEY (`user_id`)
|
REFERENCES `{$this->userTable}` (`ID`) ON DELETE CASCADE"
|
];
|
|
$allDefinitions = array_merge($columns, $indexes, $constraints);
|
|
return "(\n " . implode(",\n ", $allDefinitions) . "\n)";
|
}
|
|
}
|
