defineTables(); $this->registerHooks(); } protected function registerHooks():void { add_action('before_delete_post', [$this, 'cleanupPostFavourites']); add_action('delete_term', [$this, 'cleanupTermFavourites'], 10, 3); add_action('jvbUserRegistered', [$this, 'maybeAcceptListInvite'], 10, 3); add_action('jvb_cleanupOrphanedFavourites', [$this, 'cleanupOrphanedFavourites']); } protected function defineTables():void { $this->defineFavouriteTable(); $this->defineListTable(); $this->defineListItemsTable(); $this->defineListSharesTable(); } private function defineFavouriteTable():void { $table = CustomTable::for('favourites', true); $table->setColumns([ 'id' => 'bigint(20) unsigned NOT NULL AUTO_INCREMENT', 'user_id' => "{$table->getUserIDType()} NOT NULL", 'type' => 'varchar(50) NOT NULL', //As in, post type/user/taxonomy 'target_id' => 'bigint(20) unsigned NOT NULL', 'notes' => 'text DEFAULT NULL', 'created_at' => 'datetime DEFAULT CURRENT_TIMESTAMP', 'updated_at' => 'datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP', ]); $table->setKeys([ ['key' => 'PRIMARY', 'value' => '(`id`)'], ['key' => 'UNIQUE', 'value' => '`unique_favourite` (`user_id`, `type`, `target_id`)'], '`user_type` (`user_id`, `type`)', '`target_type` (`target_id`, `type`)' ]); $base = BASE; $table->setConstraints([ "CONSTRAINT `{$base}favourites_user` FOREIGN KEY (`user_id`) REFERENCES `{$table->getUserTable()}` (`ID`) ON DELETE CASCADE" ]); $table->defineTable(); $this->favourites = $table; } private function defineListTable():void { $table = CustomTable::for('favourites_lists'); $table->setColumns([ 'id' => 'bigint(20) unsigned NOT NULL AUTO_INCREMENT', 'user_id' => "{$table->getUserIDType()} NOT NULL", 'name' => 'varchar(255) NOT NULL', 'description'=> 'text', 'notes' => 'JSON DEFAULT (\'{}\')', 'created_at'=> 'datetime NOT NULL DEFAULT CURRENT_TIMESTAMP', 'updated_at'=> 'datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP', ]); $table->setKeys([ ['key' => 'PRIMARY', 'value' => '(`id`)'], '`user_lists` (`user_id`)' ]); $base = BASE; $table->setConstraints([ "CONSTRAINT `{$base}favourites_list_user` FOREIGN KEY (`user_id`) REFERENCES `{$table->getUserTable()}` (`ID`) ON DELETE CASCADE" ]); $table->defineTable(); $this->lists = $table; } private function defineListItemsTable():void { $table = CustomTable::for('favourites_list_items'); $table->setColumns([ 'id' => 'bigint(20) unsigned NOT NULL AUTO_INCREMENT', 'user_id' => "{$table->getUserIDType()} NOT NULL", 'list_id' => 'bigint(20) unsigned NOT NULL', 'item_type' => 'varchar(50) NOT NULL', 'target_id' => 'bigint(20) unsigned NOT NULL', 'favourite_id'=> 'bigint(20) unsigned NOT NULL', 'notes' => 'JSON DEFAULT (\'{}\')', 'added_at' => 'datetime NOT NULL DEFAULT CURRENT_TIMESTAMP', ]); $table->setKeys([ ['key' => 'PRIMARY', 'value' => '(`id`)'], ['key' => 'UNIQUE', 'value' => '`unique_list_item` (`list_id`, `item_type`, `target_id`)'], '`list_items` (`list_id`)', '`favourite_id` (`favourite_id`)' ]); $base = BASE; $table->setConstraints([ "CONSTRAINT `{$base}favourites_list_items_user` FOREIGN KEY (`user_id`) REFERENCES `{$table->getUserTable()}` (`ID`) ON DELETE CASCADE", "CONSTRAINT `{$base}favourite_list_items` FOREIGN KEY (`list_id`) REFERENCES `{$this->lists->getFullTableName()}` (`id`) ON DELETE CASCADE", "CONSTRAINT `{$base}list_favourite` FOREIGN KEY (`favourite_id`) REFERENCES `{$this->favourites->getFullTableName()}` (`id`) ON DELETE CASCADE" ]); $table->defineTable(); $this->listItems = $table; } private function defineListSharesTable():void { $table = CustomTable::for('favourites_list_shares'); $table->setColumns([ 'id' => 'bigint(20) unsigned NOT NULL AUTO_INCREMENT', 'list_id' => 'bigint(20) unsigned NOT NULL', 'user_id' => "{$table->getUserIDType()} NOT NULL", 'email' => 'varchar(255) NOT NULL', 'invite_token' => 'varchar(255) NOT NULL', 'permission' => "ENUM('view', 'edit') NOT NULL DEFAULT 'view'", 'status' => "ENUM('pending', 'accepted', 'rejected', 'revoked') NOT NULL DEFAULT 'pending'", 'created_at' => 'datetime NOT NULL DEFAULT CURRENT_TIMESTAMP', 'updated_at' => 'datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP', ]); $table->setKeys([ ['key' => 'PRIMARY', 'value' => '(`id`)'], ['key' => 'UNIQUE', 'value' => '`unique_share_user` (`list_id`, `user_id`)'], ['key'=> 'UNIQUE', 'value' => '`unique_share_email` (`list_id`, `email`)'], '`list_shares` (`list_id`)', '`list_user` (`list_id`, `user_id`)', '`status_index` (`status`)' ]); $base = BASE; $table->setConstraints([ "CONSTRAINT `{$base}favourites_share_list` FOREIGN KEY (`list_id`) REFERENCES `{$this->lists->getFullTableName()}` (`id`) ON DELETE CASCADE", "CONSTRAINT `{$base}favourites_share_user` FOREIGN KEY (`user_id`) REFERENCES `{$table->getUserTable()}` (`ID`) ON DELETE CASCADE" ]); $table->defineTable(); $this->listShares = $table; } public function toggleFavourite(bool $favourite, int $user_id, int $target_id, string $type):bool { if (!get_userdata($user_id)) { error_log('[FAVOURITES]::toggleFavourite Invalid user '.$user_id); return false; } $registrar = Registrar::getInstance($type); if (!$registrar) { error_log('[FAVOURITES]::toggleFavourite Invalid type: '.print_r($type, true)); return false; } $type = jvbCheckBase($type); $registerType = $registrar->getType(); $test = false; switch ($registerType) { case 'post': $test = get_post($target_id); break; case 'term': $test = get_term($target_id, $type); break; case 'user': $test = get_userdata($target_id); break; } if (!$test || is_wp_error($test)) { error_log('[FAVOURITES]::toggleFavourite Could not find target: '.$target_id); return false; } $owner = null; if ($registrar->getType() === 'post') { $post = get_post($target_id); $owner = $post->post_author; } elseif ($registrar->hasFeature('is_ownable')) { $term = get_term($target_id, jvbCheckBase($type)); $owner = jvbGetTermOwners($target_id); } if ($favourite) { $result = $this->favourites->findOrCreate( [ 'user_id' => $user_id, 'target_id' => $target_id, 'type' => $type ], ); if ($owner) { JVB()->notification()->addNotification( $owner, 'new_favourite', $user_id, '', $target_id, $type ); } } else { $result = $this->favourites->delete([ 'user_id' => $user_id, 'target_id' => $target_id, 'type' => $type ]); if (in_array($registerType, ['term', 'user'])) { JVB()->notification()->deleteUserPreference($user_id, $target_id, $type); } if ($owner) { JVB()->notification()->removeNotification( $owner, [ 'from_user' => $user_id, 'target_id' => $target_id, 'target_type' => $type ] ); } } return (bool)$result; } public function favourite(int $user_id, int $target_id, string $type):bool { return $this->toggleFavourite(true, $user_id, $target_id, $type); } public function unfavourite(int $user_id, int $target_id, string $type):bool { return $this->toggleFavourite(false, $user_id, $target_id, $type); } public function addNoteFromTarget(int $user_id, int $target_id, string $type, string $note):bool { $favourite = $this->favourites->get([ 'user_id' => $user_id, 'target_id' => $target_id, 'type' => $type ]); if (!$favourite) { return false; } return $this->addNote($user_id, $favourite->id, $note); } public function addNote(int $user_id, int $favourite_id, string $note):bool { $favourite = $this->favourites->get(['id' => $favourite_id]); if (!$favourite) { return false; } if ($favourite->user_id !== $user_id) { return false; } return (bool) $this->favourites->update([ 'note' => sanitize_textarea_field($note) ], [ 'favourite_id' => $favourite_id ]); } public function deleteNote(int $user_id, int $favourite_id):bool { if (!$this->userCanEditFavourite($user_id, ['id' => $favourite_id])) { return false; } return (bool) $this->favourites->update([ 'note' => '', ], [ 'favourite_id' => $favourite_id ]); } public function addListItemNote(int $user_id, int $listItemId, string|array $note):bool { if (!$this->userCanEditListItem($user_id, $listItemId)) { return false; } $listItem = $this->listItems->get(['id' => $listItemId]); $notes = json_decode($listItem->notes, true); $notes = $this->addNoteToArray($notes, $user_id, $note); return $this->listItems->update(['id' => $listItemId], ['notes' => json_encode($notes)]); } public function removeListItemNote(int $user_id, int $listItemId, string $noteID):bool { if (!$this->userCanEditListItem($user_id, $listItemId)) { return false; } $listItem = $this->listItems->get(['id' => $listItemId]); $notes = json_decode($listItem->notes, true); $index = array_search($noteID, array_column($notes, 'id')); if ($index === false) { return false; } $note = $notes[$index]; if ($user_id !== $note['user_id']) { return false; } $notes = $this->removeNoteFromArray($notes, $user_id, $noteID); return $this->listItems->update(['id' => $listItemId], ['notes' => json_encode($notes)]); } public function addListNote(int $user_id, int $listId, string $note):bool { if (!$this->userCanEditList($user_id, $listId)) { return false; } $list = $this->listItems->get(['id' => $listId]); $notes = json_decode($list->notes, true); $notes = $this->addNoteToArray($notes, $user_id, $note); return $this->listItems->update(['id' => $listId], ['notes' => json_encode($notes)]); } public function removeListNote(int $user_id, int $listId, string $noteID):bool { if (!$this->userCanEditList($user_id, $listId)) { return false; } $list = $this->lists->get(['id' => $listId]); $notes = json_decode($list->notes, true); $index = array_search($noteID, array_column($notes, 'id')); if ($index === false) { return false; } $note = $notes[$index]; if ($user_id !== $note['user_id']) { return false; } $notes = $this->removeNoteFromArray($notes, $user_id, $noteID); return $this->lists->update(['id' => $listId], ['notes' => json_encode($notes)]); } public function shareList(int $user_id, int $listId, bool $viewOnly = true, ?int $shareWithUser = null, ?array $newUser = null):bool { $list = $this->lists->get(['id' => $listId]); if (!$list) { return false; } if ($list->user_id !== $user_id) { return false; } if (!$shareWithUser && (empty($newUser) || !array_key_exists('email', $newUser) || !array_key_exists('name', $newUser))) { return false; } $args = []; if ($shareWithUser) { $args['user_id'] = $shareWithUser; JVB()->notification()->addNotification($shareWithUser, 'list_shared', $user_id, '', $listId); } elseif ($newUser) { $email = sanitize_email($newUser['email']); $name = sanitize_text_field($newUser['name']); $args['invite_token'] = wp_generate_password(32, false); $args['email'] = $email; //TODO: Magic Link setup } if (empty($args)) { return false; } return (bool) $this->listShares->findOrCreate(array_merge($args, ['list_id' => $listId, 'permission' => $viewOnly ? 'view' : 'edit'])); } public function unshareList(int $user_id, int $listId, int|string $userIDorEmail):bool { $list = $this->lists->get(['id' => $listId]); if (!$list) { return false; } if ($list->user_id !== $user_id) { return false; } $where = [ 'list_id' => $listId ]; if (is_int($userIDorEmail)) { $where['user_id'] = $userIDorEmail; $notification = JVB()->notification()->table(); $n = $notification->get([ 'for_user' => $userIDorEmail, 'from_user' => $user_id, 'type' => 'list_shared', 'target_id' => $listId, ]); if ($n) { $notification->delete([ 'for_user' => $userIDorEmail, 'from_user' => $user_id, 'type' => 'list_shared', 'target_id' => $listId, ]); } } else if (is_email($userIDorEmail)) { $where['email'] = sanitize_email($userIDorEmail); } else { return false; } return $this->listShares->update($where, ['status' => 'revoked']); } public function acceptListShare(int $listId, int|string $userIDorEmail):bool { return $this->respondToShare(true, $listId, $userIDorEmail); } public function rejectListShare(int $listId, int|string $userIDorEmail):bool { return $this->respondToShare(false, $listId, $userIDorEmail); } public function respondToShare(bool $accept, int $listId, int|string $userIDorEmail):bool { $args = [ 'listId' => $listId ]; if (is_int($userIDorEmail)) { $args['user_id'] = $userIDorEmail; $list = $this->lists->get(['id' => $listId]); if ($list) { $user = get_userdata($userIDorEmail); $message = $accept ? 'accepted' : 'rejected'; $message = $user->first_name . ' ' . $message . ' your list share invitation.'; JVB()->notification()->addNotification($list->user_id, 'list_share_status', $userIDorEmail, $message); } }else if (is_email($userIDorEmail)) { $args['email'] = sanitize_email($userIDorEmail); } else { return false; } $share = $this->listShares->get($args); if (!$share) { return false; } return $this->listShares->update([ 'status' => $accept ? 'accepted' : 'rejected', ], $args); } public function getFavourites(array $args = []):array { return $this->favourites->getMany($args, false); } public function getLists(array $args = []):array { return $this->lists->getMany($args, false); } public function getAvailableLists(array $args = [], bool $include_shared = true):array { $lists = []; $owned = $this->lists->getMany($args, false); foreach ($owned['items'] as &$list) { $list['item_count'] = $this->listItems->count(['list_id' => $list['id']]); } $lists['owned'] = $owned; if ($include_shared) { $args['where']['status'] = 'accepted'; $sharedLists = $this->listShares->getMany($args); $shared = []; foreach ($sharedLists as $share) { $sharedList = $this->lists->get(['id' => $share->list_id]); if ($sharedList) { $sharedList['owner_name'] = jvbGetUsername($sharedList['user_id']); $sharedList['item_count'] = $this->listItems->count(['list_id' => $sharedList['id']]); $sharedList['permission_type'] = $sharedList->permission_type; $shared[] = $sharedList; } } $lists['shared'] = $shared; } return $lists; } public function userOwnsList(int $list_id, int $user_id):bool { return $this->lists->count(['id' => $list_id, 'user_id' => $user_id]) > 0; } public function getListDetails(int $list_id, int $user_id, int $page = 1):array { $list = JVB()->favourites()->getLists(['id' => $list_id, 'user_id' => $user_id]); if (!$list) { return []; } $items = $this->listItems->getMany([ 'where' => ['list_id' => $list_id], 'order_by' => 'added_at', 'order' => 'DESC', 'per_page' => 25, 'page' => $page, ]); $formatted = []; foreach($items as $item) { //See if we can get the actual favourite record first if ($item->favourite_id) { $fav = $this->favourites->get(['id' => $item->favourite_id]); if ($fav) { $formatted[] = $fav; continue; } } //Create a dummy favourite object otherwise $formatted[] = (object) [ 'type' => $item->item_type, 'target_id' => $item->item_id, 'created_at'=> $item->added_at ]; } $sharedWith = []; $is_owner = $this->userOwnsList($list_id, $user_id); if ($is_owner) { $shares = $this->listShares->getMany([ 'where' => ['list_id' => $list_id], 'order_by' => 'created_at', 'order' => 'DESC' ]); foreach ($shares as $share_item) { $user = [ 'email' => $share_item->email, 'status' => $share_item->status, 'date_added'=> $share_item->created_at, ]; if ($share_item->status === 'accepted' && $share_item->user_id) { $user['name'] = jvbGetUsername($share_item->user_id); $user['permission_type'] = $share_item->permission_type; } $sharedWith[] = $user; } } return [ 'id' => (int)$list['id'], 'name' => $list['name'], 'description' => $list['description']??'', 'created_at' => $list['created_at'], 'is_owner' => $is_owner, 'items' => $formatted, 'shared_users' => $sharedWith ]; } public function getFavouriteCounts(int $user_id): array { $results = $this->favourites->queryResults( "SELECT type, COUNT(*) as count FROM {table} WHERE user_id = %d GROUP BY type", [$user_id] ); $counts = []; foreach ($results as $row) { $type = str_replace(BASE, '', $row->type); $counts[$type] = (int) $row->count; } $defaults = array_fill_keys( array_map(fn($t) => str_replace(BASE, '', $t), Registrar::withFeature('favouritable')), 0 ); return array_merge($defaults, $counts); } public function cleanupOrphanedFavourites(): bool { global $wpdb; // Posts - no FK possible since target_id is generic $this->favourites->query( "DELETE f FROM {table} f LEFT JOIN {$wpdb->posts} p ON f.target_id = p.ID WHERE f.type IN ( SELECT CONCAT('" . BASE . "', post_type) FROM {$wpdb->posts} GROUP BY post_type ) AND p.ID IS NULL" ); // Terms - same reason $this->favourites->query( "DELETE f FROM {table} f LEFT JOIN {$wpdb->term_taxonomy} tt ON f.target_id = tt.term_id AND f.type = CONCAT('" . BASE . "', tt.taxonomy) WHERE tt.term_id IS NULL AND f.type != CONCAT('" . BASE . "', 'user')" ); $this->listItems->query( "DELETE li FROM {table} li LEFT JOIN {$wpdb->posts} p ON li.target_id = p.ID LEFT JOIN {$wpdb->term_taxonomy} tt ON li.target_id = tt.term_id WHERE p.ID IS NULL AND tt.term_id IS NULL" ); return true; } /*************************************************************** * UTILITY METHODS **************************************************************/ /** * @param int $user_id * @param array $args * @return bool */ public function userCanEditFavourite(int $user_id, array $args):bool { if (array_key_exists('id', $args)) { $where = [ 'id' => $args['id'] ]; } else { if (!array_key_exists('target_id', $args) || !array_key_exists('type', $args)) { error_log('[Favourites]::userCanEditFavourite missing required target_id or type'); return false; } $where = [ 'target_id' => absint($args['target_id']), 'type' => $args['type'] ]; } $get = $this->favourites->get($where); return !is_null($get) && $get->user_id === $user_id; } public function userCanViewList(int $user_id, int $list_id):bool { $list = $this->lists->get(['id' => $list_id]); if (!$list) { return false; } if ($list->user_id === $user_id) { return true; } $share = $this->listShares->get(['user_id' => $user_id, 'list_id' => $list_id]); return !is_null($share); } public function userCanEditList(int $user_id, int $list_id):bool { $list = $this->lists->get(['id' => $list_id]); if (!$list) { return false; } if ($list->user_id === $user_id) { return true; } $share = $this->listShares->get(['user_id' => $user_id, 'list_id' => $list_id]); if (!$share) return false; return $share->permission === 'edit'; } public function userCanEditListItem(int $user_id, int $list_item_id):bool { $item = $this->listItems->get(['id' => $list_item_id]); if (!$item) { return false; } if ($item->user_id === $user_id) { return true; } return $this->userCanEditList($user_id, $item->list_id); } protected function addNoteToArray(array $notes, int $user_id, string|array $note):array { $updated = date('Y-m-d H:i:s'); if (is_string($note)) { //It is a new note $notes[] = [ 'id' => uniqid(), 'note' => sanitize_textarea_field($note), 'user_id' => $user_id, 'updated' => $updated ]; } else { $index = array_search($note['id'], array_column($notes, 'id')); if ($index === false) { //Shouldn't happen, but just in case: add it to the array $notes[] = [ 'id' => sanitize_text_field($note['id']), 'note' => sanitize_textarea_field($note['note']), 'user_id' => $user_id, 'updated' => $updated ]; } else { $notes[$index]['note'] = sanitize_textarea_field($note['note']); $notes[$index]['updated'] = $updated; } } return $notes; } protected function removeNoteFromArray(array $notes, int $user_id, string $noteID):array { $index = array_search($noteID, array_column($notes, 'id')); if ($index === false) { return $notes; } $note = $notes[$index]; if ($user_id !== $note['user_id']) { return $notes; } unset($notes[$index]); return array_values($notes); //reindexed array } public function cleanupPostFavourites(int $post_id):void { try { $type = get_post_type($post_id); if (!$type) return; $this->favourites->where([ 'type' => $type, 'target_id' => $post_id ])->deleteResults(); $this->listItems->where([ 'item_type' => $type, 'item_id' => $post_id ])->deleteResults(); } catch (Exception $e) { JVB()->error()->log('cleanupPostFavourites', $e->getMessage(), [ 'post_id' => $post_id ]); } } public function cleanupTermFavourites(int $term_id, int $tt_id, string $taxonomy):void { try { $registrar = Registrar::getInstance($taxonomy); if (!$registrar || !$registrar->hasFeature('favouritable')) { return; } $this->favourites->where([ 'type' => $taxonomy, 'target_id' => $term_id, ])->deleteResults(); $this->listItems->where([ 'item_type' => $taxonomy, 'item_id' => $term_id ])->deleteResults(); } catch (Exception $e) { JVB()->error()->log('cleanupTermFavourites', $e->getMessage(), [ 'term_id' => $term_id, 'taxonomy'=> $taxonomy ]); } } }