2021 lines
92 KiB
PHP
2021 lines
92 KiB
PHP
<?php
|
||
if ( ! defined( 'ABSPATH' ) ) exit;
|
||
|
||
class WBF_DB {
|
||
|
||
public static function install() {
|
||
global $wpdb;
|
||
$charset = $wpdb->get_charset_collate();
|
||
|
||
$sql_users = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}forum_users (
|
||
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
username VARCHAR(60) NOT NULL UNIQUE,
|
||
email VARCHAR(100) NOT NULL UNIQUE,
|
||
password VARCHAR(255) NOT NULL,
|
||
display_name VARCHAR(100) NOT NULL,
|
||
avatar_url VARCHAR(255) DEFAULT '',
|
||
bio TEXT DEFAULT '',
|
||
signature TEXT DEFAULT '',
|
||
role VARCHAR(20) DEFAULT 'member',
|
||
post_count INT DEFAULT 0,
|
||
registered DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
last_active DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (id)
|
||
) $charset;";
|
||
|
||
$sql_cats = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}forum_categories (
|
||
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
parent_id BIGINT UNSIGNED DEFAULT 0,
|
||
name VARCHAR(100) NOT NULL,
|
||
slug VARCHAR(100) NOT NULL UNIQUE,
|
||
description TEXT DEFAULT '',
|
||
icon VARCHAR(50) DEFAULT 'fas fa-comments',
|
||
sort_order INT DEFAULT 0,
|
||
thread_count INT DEFAULT 0,
|
||
post_count INT DEFAULT 0,
|
||
min_role VARCHAR(20) DEFAULT 'member',
|
||
PRIMARY KEY (id),
|
||
KEY parent_id (parent_id)
|
||
) $charset;";
|
||
|
||
$sql_threads = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}forum_threads (
|
||
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
category_id BIGINT UNSIGNED NOT NULL,
|
||
user_id BIGINT UNSIGNED NOT NULL,
|
||
title VARCHAR(255) NOT NULL,
|
||
slug VARCHAR(255) NOT NULL,
|
||
content LONGTEXT NOT NULL,
|
||
status VARCHAR(20) DEFAULT 'open',
|
||
pinned TINYINT(1) DEFAULT 0,
|
||
views INT DEFAULT 0,
|
||
reply_count INT DEFAULT 0,
|
||
like_count INT DEFAULT 0,
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
last_reply_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (id),
|
||
KEY category_id (category_id)
|
||
) $charset;";
|
||
|
||
$sql_posts = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}forum_posts (
|
||
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
thread_id BIGINT UNSIGNED NOT NULL,
|
||
user_id BIGINT UNSIGNED NOT NULL,
|
||
content LONGTEXT NOT NULL,
|
||
like_count INT DEFAULT 0,
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (id),
|
||
KEY thread_id (thread_id)
|
||
) $charset;";
|
||
|
||
$sql_likes = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}forum_likes (
|
||
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
user_id BIGINT UNSIGNED NOT NULL,
|
||
object_id BIGINT UNSIGNED NOT NULL,
|
||
object_type VARCHAR(20) NOT NULL,
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY unique_like (user_id, object_id, object_type)
|
||
) $charset;";
|
||
|
||
$sql_reports = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}forum_reports (
|
||
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
object_id BIGINT UNSIGNED NOT NULL,
|
||
object_type VARCHAR(20) NOT NULL DEFAULT 'post',
|
||
reporter_id BIGINT UNSIGNED NOT NULL,
|
||
reason VARCHAR(100) NOT NULL DEFAULT '',
|
||
note TEXT DEFAULT '',
|
||
status VARCHAR(20) NOT NULL DEFAULT 'open',
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (id),
|
||
KEY object_id (object_id),
|
||
KEY status (status)
|
||
) $charset;";
|
||
|
||
$sql_tags = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}forum_tags (
|
||
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
name VARCHAR(60) NOT NULL,
|
||
slug VARCHAR(60) NOT NULL,
|
||
use_count INT DEFAULT 0,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY slug (slug)
|
||
) $charset;";
|
||
|
||
$sql_thread_tags = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}forum_thread_tags (
|
||
thread_id BIGINT UNSIGNED NOT NULL,
|
||
tag_id BIGINT UNSIGNED NOT NULL,
|
||
PRIMARY KEY (thread_id, tag_id),
|
||
KEY tag_id (tag_id)
|
||
) $charset;";
|
||
|
||
$sql_messages = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}forum_messages (
|
||
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
from_id BIGINT UNSIGNED NOT NULL,
|
||
to_id BIGINT UNSIGNED NOT NULL,
|
||
content TEXT NOT NULL,
|
||
is_read TINYINT(1) DEFAULT 0,
|
||
deleted_by_sender TINYINT(1) DEFAULT 0,
|
||
deleted_by_receiver TINYINT(1) DEFAULT 0,
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (id),
|
||
KEY from_id (from_id),
|
||
KEY to_id (to_id)
|
||
) $charset;";
|
||
|
||
$sql_reactions = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}forum_reactions (
|
||
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
user_id BIGINT UNSIGNED NOT NULL,
|
||
object_id BIGINT UNSIGNED NOT NULL,
|
||
object_type VARCHAR(20) NOT NULL DEFAULT 'post',
|
||
reaction VARCHAR(10) NOT NULL,
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY unique_reaction (user_id, object_id, object_type),
|
||
KEY object_id (object_id)
|
||
) $charset;";
|
||
|
||
$sql_remember = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}forum_remember_tokens (
|
||
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
user_id BIGINT UNSIGNED NOT NULL,
|
||
token VARCHAR(64) NOT NULL,
|
||
expires_at DATETIME NOT NULL,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY token (token),
|
||
KEY user_id (user_id)
|
||
) $charset;";
|
||
|
||
require_once ABSPATH . 'wp-admin/includes/upgrade.php';
|
||
dbDelta( $sql_users );
|
||
dbDelta( $sql_cats );
|
||
dbDelta( $sql_threads );
|
||
dbDelta( $sql_posts );
|
||
dbDelta( $sql_likes );
|
||
dbDelta( $sql_tags );
|
||
dbDelta( $sql_thread_tags );
|
||
dbDelta( $sql_messages );
|
||
dbDelta( $sql_reactions );
|
||
dbDelta( $sql_remember );
|
||
|
||
// Live upgrades — add new columns to existing installs
|
||
self::maybe_add_column("{$wpdb->prefix}forum_users", 'signature', "ALTER TABLE {$wpdb->prefix}forum_users ADD COLUMN signature TEXT DEFAULT '' AFTER bio");
|
||
self::maybe_add_column("{$wpdb->prefix}forum_users", 'ban_reason', "ALTER TABLE {$wpdb->prefix}forum_users ADD COLUMN ban_reason TEXT DEFAULT '' AFTER role");
|
||
self::maybe_add_column("{$wpdb->prefix}forum_categories", 'parent_id', "ALTER TABLE {$wpdb->prefix}forum_categories ADD COLUMN parent_id BIGINT UNSIGNED DEFAULT 0 AFTER id");
|
||
self::maybe_add_column("{$wpdb->prefix}forum_categories", 'min_role', "ALTER TABLE {$wpdb->prefix}forum_categories ADD COLUMN min_role VARCHAR(20) DEFAULT 'member' AFTER post_count");
|
||
self::maybe_add_column("{$wpdb->prefix}forum_categories", 'guest_visible', "ALTER TABLE {$wpdb->prefix}forum_categories ADD COLUMN guest_visible TINYINT(1) DEFAULT 1 AFTER min_role");
|
||
self::maybe_add_column("{$wpdb->prefix}forum_users", 'reset_token', "ALTER TABLE {$wpdb->prefix}forum_users ADD COLUMN reset_token VARCHAR(64) DEFAULT NULL");
|
||
self::maybe_add_column("{$wpdb->prefix}forum_users", 'reset_token_expires', "ALTER TABLE {$wpdb->prefix}forum_users ADD COLUMN reset_token_expires DATETIME DEFAULT NULL");
|
||
// Soft-Delete
|
||
self::maybe_add_column("{$wpdb->prefix}forum_threads", 'deleted_at', "ALTER TABLE {$wpdb->prefix}forum_threads ADD COLUMN deleted_at DATETIME DEFAULT NULL");
|
||
self::maybe_add_column("{$wpdb->prefix}forum_posts", 'deleted_at', "ALTER TABLE {$wpdb->prefix}forum_posts ADD COLUMN deleted_at DATETIME DEFAULT NULL");
|
||
// Profil-Sichtbarkeit
|
||
self::maybe_add_column("{$wpdb->prefix}forum_users", 'profile_public', "ALTER TABLE {$wpdb->prefix}forum_users ADD COLUMN profile_public TINYINT(1) DEFAULT 1");
|
||
// Zeitlich begrenzte Sperren
|
||
self::maybe_add_column("{$wpdb->prefix}forum_users", 'ban_until', "ALTER TABLE {$wpdb->prefix}forum_users ADD COLUMN ban_until DATETIME DEFAULT NULL");
|
||
self::maybe_add_column("{$wpdb->prefix}forum_users", 'pre_ban_role', "ALTER TABLE {$wpdb->prefix}forum_users ADD COLUMN pre_ban_role VARCHAR(20) DEFAULT 'member'");
|
||
// Profilbanner
|
||
self::maybe_add_column("{$wpdb->prefix}forum_users", 'banner_url', "ALTER TABLE {$wpdb->prefix}forum_users ADD COLUMN banner_url VARCHAR(255) DEFAULT ''");
|
||
// Thread-Abonnements
|
||
$sql_subscriptions = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}forum_subscriptions (
|
||
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
user_id BIGINT UNSIGNED NOT NULL,
|
||
thread_id BIGINT UNSIGNED NOT NULL,
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY user_thread (user_id, thread_id),
|
||
KEY thread_id (thread_id)
|
||
) $charset;";
|
||
dbDelta( $sql_subscriptions );
|
||
$sql_notifications = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}forum_notifications (
|
||
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
user_id BIGINT UNSIGNED NOT NULL,
|
||
type VARCHAR(30) NOT NULL DEFAULT 'reply',
|
||
object_id BIGINT UNSIGNED NOT NULL,
|
||
actor_id BIGINT UNSIGNED NOT NULL,
|
||
is_read TINYINT(1) DEFAULT 0,
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (id),
|
||
KEY user_id (user_id),
|
||
KEY is_read (is_read)
|
||
) $charset;";
|
||
|
||
// Ensure reports + notifications tables exist on existing installs
|
||
dbDelta( $sql_notifications );
|
||
|
||
// Einladungs-Tabelle
|
||
$sql_invites = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}forum_invites (
|
||
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
code VARCHAR(64) NOT NULL UNIQUE,
|
||
created_by BIGINT UNSIGNED NOT NULL,
|
||
used_by BIGINT UNSIGNED DEFAULT NULL,
|
||
max_uses SMALLINT UNSIGNED DEFAULT 1,
|
||
use_count SMALLINT UNSIGNED DEFAULT 0,
|
||
note VARCHAR(255) DEFAULT '',
|
||
expires_at DATETIME DEFAULT NULL,
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY code (code),
|
||
KEY created_by (created_by)
|
||
) $charset;";
|
||
dbDelta( $sql_invites );
|
||
|
||
// Benutzerdefinierte Profilfelder — Meta-Tabelle
|
||
$sql_user_meta = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}forum_user_meta (
|
||
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
user_id BIGINT UNSIGNED NOT NULL,
|
||
meta_key VARCHAR(60) NOT NULL,
|
||
meta_value TEXT DEFAULT '',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY user_key (user_id, meta_key),
|
||
KEY user_id (user_id)
|
||
) $charset;";
|
||
dbDelta( $sql_user_meta );
|
||
|
||
// Umfragen (Polls)
|
||
$sql_polls = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}forum_polls (
|
||
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
thread_id BIGINT UNSIGNED NOT NULL,
|
||
question VARCHAR(255) NOT NULL DEFAULT '',
|
||
options TEXT NOT NULL DEFAULT '',
|
||
multi TINYINT(1) DEFAULT 0,
|
||
ends_at DATETIME DEFAULT NULL,
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY thread_id (thread_id)
|
||
) $charset;";
|
||
dbDelta( $sql_polls );
|
||
|
||
$sql_poll_votes = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}forum_poll_votes (
|
||
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
poll_id BIGINT UNSIGNED NOT NULL,
|
||
user_id BIGINT UNSIGNED NOT NULL,
|
||
option_idx TINYINT UNSIGNED NOT NULL,
|
||
voted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY poll_user_option (poll_id, user_id, option_idx),
|
||
KEY poll_id (poll_id)
|
||
) $charset;";
|
||
dbDelta( $sql_poll_votes );
|
||
|
||
// ── Thread-Präfixe ────────────────────────────────────────────────────
|
||
$sql_prefixes = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}forum_prefixes (
|
||
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
label VARCHAR(60) NOT NULL,
|
||
color VARCHAR(30) DEFAULT '#ffffff',
|
||
bg_color VARCHAR(30) DEFAULT '#475569',
|
||
sort_order INT DEFAULT 0,
|
||
PRIMARY KEY (id)
|
||
) $charset;";
|
||
dbDelta( $sql_prefixes );
|
||
|
||
// ── Lesezeichen ───────────────────────────────────────────────────────
|
||
$sql_bookmarks = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}forum_bookmarks (
|
||
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
user_id BIGINT UNSIGNED NOT NULL,
|
||
thread_id BIGINT UNSIGNED NOT NULL,
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY user_thread (user_id, thread_id)
|
||
) $charset;";
|
||
dbDelta( $sql_bookmarks );
|
||
|
||
// ── Ignore-Liste ──────────────────────────────────────────────────────
|
||
$sql_ignore = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}forum_ignore_list (
|
||
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
user_id BIGINT UNSIGNED NOT NULL,
|
||
ignored_id BIGINT UNSIGNED NOT NULL,
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY user_ignored (user_id, ignored_id),
|
||
KEY ignored_id (ignored_id)
|
||
) $charset;";
|
||
dbDelta( $sql_ignore );
|
||
|
||
// ── prefix_id zu threads ──────────────────────────────────────────────
|
||
self::maybe_add_column( "{$wpdb->prefix}forum_threads", 'prefix_id',
|
||
"ALTER TABLE {$wpdb->prefix}forum_threads ADD COLUMN prefix_id BIGINT UNSIGNED DEFAULT NULL" );
|
||
|
||
$count = $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->prefix}forum_categories");
|
||
if ( (int)$count === 0 ) {
|
||
$wpdb->insert("{$wpdb->prefix}forum_categories", ['parent_id'=>0,'name'=>'Allgemein', 'slug'=>'allgemein', 'description'=>'Allgemeine Diskussionen','icon'=>'fas fa-home', 'sort_order'=>1]);
|
||
$p1 = $wpdb->insert_id;
|
||
$wpdb->insert("{$wpdb->prefix}forum_categories", ['parent_id'=>0,'name'=>'Ankündigungen', 'slug'=>'ankuendigungen','description'=>'Wichtige Neuigkeiten', 'icon'=>'fas fa-bullhorn', 'sort_order'=>2,'min_role'=>'moderator']);
|
||
$p2 = $wpdb->insert_id;
|
||
$wpdb->insert("{$wpdb->prefix}forum_categories", ['parent_id'=>0,'name'=>'Support', 'slug'=>'support', 'description'=>'Hilfe & Fragen', 'icon'=>'fas fa-life-ring','sort_order'=>3]);
|
||
$p3 = $wpdb->insert_id;
|
||
$wpdb->insert("{$wpdb->prefix}forum_categories", ['parent_id'=>$p1,'name'=>'Introductions', 'slug'=>'introductions', 'description'=>'Stell dich vor!', 'icon'=>'fas fa-user', 'sort_order'=>1]);
|
||
$wpdb->insert("{$wpdb->prefix}forum_categories", ['parent_id'=>$p1,'name'=>'Off-Topic', 'slug'=>'off-topic', 'description'=>'Alles außerhalb des Themas', 'icon'=>'fas fa-coffee', 'sort_order'=>2]);
|
||
$wpdb->insert("{$wpdb->prefix}forum_categories", ['parent_id'=>$p3,'name'=>'Bug Reports', 'slug'=>'bug-reports', 'description'=>'Fehler melden', 'icon'=>'fas fa-bug', 'sort_order'=>1]);
|
||
$wpdb->insert("{$wpdb->prefix}forum_categories", ['parent_id'=>$p3,'name'=>'Feature Requests','slug'=>'feature-requests','description'=>'Neue Funktionen vorschlagen','icon'=>'fas fa-lightbulb','sort_order'=>2]);
|
||
}
|
||
}
|
||
|
||
private static function maybe_add_column( $table, $column, $sql ) {
|
||
global $wpdb;
|
||
$cols = $wpdb->get_col("DESCRIBE {$table}");
|
||
if ( ! in_array($column, $cols) ) {
|
||
$wpdb->query($sql);
|
||
}
|
||
}
|
||
|
||
// ── Rollen — delegiert an WBF_Roles ──────────────────────────────────────
|
||
|
||
public static function role_level( $role ) { return WBF_Roles::level($role); }
|
||
public static function all_roles() { return WBF_Roles::labels(); }
|
||
public static function can( $user, $action ){ return WBF_Roles::can($user, $action); }
|
||
public static function can_post_in( $user, $cat ) { return WBF_Roles::can_post_in($user, $cat); }
|
||
|
||
// ── Users ─────────────────────────────────────────────────────────────────
|
||
|
||
public static function get_user( $id ) {
|
||
global $wpdb;
|
||
return $wpdb->get_row( $wpdb->prepare("SELECT * FROM {$wpdb->prefix}forum_users WHERE id=%d", $id) );
|
||
}
|
||
|
||
public static function get_user_by( $field, $value ) {
|
||
global $wpdb;
|
||
$field = sanitize_key($field);
|
||
// Benutzername & E-Mail: Groß-/Kleinschreibung ignorieren (LOWER)
|
||
if ( in_array($field, ['username', 'email']) ) {
|
||
return $wpdb->get_row( $wpdb->prepare(
|
||
"SELECT * FROM {$wpdb->prefix}forum_users WHERE LOWER($field)=LOWER(%s)",
|
||
$value
|
||
) );
|
||
}
|
||
return $wpdb->get_row( $wpdb->prepare("SELECT * FROM {$wpdb->prefix}forum_users WHERE $field=%s", $value) );
|
||
}
|
||
|
||
public static function create_user( $data ) {
|
||
global $wpdb;
|
||
$wpdb->insert("{$wpdb->prefix}forum_users", $data);
|
||
return $wpdb->insert_id;
|
||
}
|
||
|
||
public static function update_user( $id, $data ) {
|
||
global $wpdb;
|
||
$wpdb->update("{$wpdb->prefix}forum_users", $data, ['id' => $id]);
|
||
|
||
// --- Discord-Rollen-Sync nach Rollenänderung ---
|
||
if (isset($data['role'])) {
|
||
// Discord-User-ID holen
|
||
$discord_user_id = $wpdb->get_var($wpdb->prepare(
|
||
"SELECT meta_value FROM {$wpdb->prefix}forum_user_meta WHERE user_id = %d AND meta_key = 'discord_user_id'",
|
||
$id
|
||
));
|
||
if ($discord_user_id) {
|
||
// Einstellungen laden
|
||
$s = function_exists('wbf_get_settings') ? wbf_get_settings() : [];
|
||
$token = trim($s['discord_bot_token'] ?? '');
|
||
$guild = trim($s['discord_guild_id'] ?? '');
|
||
$role_map = json_decode($s['discord_role_map'] ?? '{}', true) ?: [];
|
||
if ($token && $guild && !empty($role_map)) {
|
||
// Ziel-Discord-Rolle anhand Mapping finden
|
||
$target_discord_role = null;
|
||
foreach ($role_map as $dc_role_id => $forum_role) {
|
||
if ($forum_role === $data['role']) {
|
||
$target_discord_role = (string)$dc_role_id;
|
||
break;
|
||
}
|
||
}
|
||
if ($target_discord_role) {
|
||
// Aktuelle Rollen des Users abrufen
|
||
$res = wp_remote_get("https://discord.com/api/v10/guilds/{$guild}/members/{$discord_user_id}", [
|
||
'timeout' => 6,
|
||
'headers' => ['Authorization' => 'Bot ' . $token],
|
||
]);
|
||
if (!is_wp_error($res) && wp_remote_retrieve_response_code($res) === 200) {
|
||
$member = json_decode(wp_remote_retrieve_body($res), true);
|
||
$user_roles = $member['roles'] ?? [];
|
||
// Alle gemappten Discord-Rollen entfernen, außer Zielrolle
|
||
$remove_roles = [];
|
||
foreach ($role_map as $dc_role_id => $forum_role) {
|
||
if ((string)$dc_role_id !== $target_discord_role && in_array((string)$dc_role_id, $user_roles, true)) {
|
||
$remove_roles[] = (string)$dc_role_id;
|
||
}
|
||
}
|
||
// Zielrolle hinzufügen, falls nicht vorhanden
|
||
if (!in_array($target_discord_role, $user_roles, true)) {
|
||
$user_roles[] = $target_discord_role;
|
||
}
|
||
// Entfernte Rollen rausnehmen
|
||
$user_roles = array_values(array_diff($user_roles, $remove_roles));
|
||
// PATCH an Discord senden
|
||
$body = json_encode(['roles' => array_values($user_roles)]);
|
||
wp_remote_request("https://discord.com/api/v10/guilds/{$guild}/members/{$discord_user_id}", [
|
||
'method' => 'PATCH',
|
||
'timeout' => 6,
|
||
'headers' => [
|
||
'Authorization' => 'Bot ' . $token,
|
||
'Content-Type' => 'application/json',
|
||
],
|
||
'body' => $body,
|
||
]);
|
||
}
|
||
}
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
public static function get_all_users( $limit = 100, $offset = 0 ) {
|
||
global $wpdb;
|
||
return $wpdb->get_results( $wpdb->prepare(
|
||
"SELECT * FROM {$wpdb->prefix}forum_users ORDER BY registered DESC LIMIT %d OFFSET %d",
|
||
$limit, $offset
|
||
));
|
||
}
|
||
|
||
// ── Categories ────────────────────────────────────────────────────────────
|
||
|
||
public static function get_categories_tree() {
|
||
global $wpdb;
|
||
$all = $wpdb->get_results(
|
||
"SELECT c.*,
|
||
(SELECT t.title FROM {$wpdb->prefix}forum_threads t WHERE t.category_id=c.id ORDER BY t.last_reply_at DESC LIMIT 1) as last_thread_title,
|
||
(SELECT t.id FROM {$wpdb->prefix}forum_threads t WHERE t.category_id=c.id ORDER BY t.last_reply_at DESC LIMIT 1) as last_thread_id,
|
||
(SELECT u.display_name FROM {$wpdb->prefix}forum_threads t
|
||
JOIN {$wpdb->prefix}forum_users u ON u.id=t.user_id
|
||
WHERE t.category_id=c.id ORDER BY t.last_reply_at DESC LIMIT 1) as last_post_author
|
||
FROM {$wpdb->prefix}forum_categories c
|
||
ORDER BY c.parent_id ASC, c.sort_order ASC"
|
||
);
|
||
$by_id = [];
|
||
foreach ($all as $cat) { $cat->children = []; $by_id[$cat->id] = $cat; }
|
||
$tree = [];
|
||
foreach ($by_id as $id => $cat) {
|
||
if ((int)$cat->parent_id === 0) $tree[] = &$by_id[$id];
|
||
elseif (isset($by_id[$cat->parent_id])) $by_id[$cat->parent_id]->children[] = &$by_id[$id];
|
||
}
|
||
return $tree;
|
||
}
|
||
|
||
public static function get_categories_flat() {
|
||
global $wpdb;
|
||
return $wpdb->get_results("SELECT * FROM {$wpdb->prefix}forum_categories ORDER BY parent_id ASC, sort_order ASC");
|
||
}
|
||
|
||
public static function get_category( $id_or_slug ) {
|
||
global $wpdb;
|
||
if (is_numeric($id_or_slug)) return $wpdb->get_row($wpdb->prepare("SELECT * FROM {$wpdb->prefix}forum_categories WHERE id=%d", $id_or_slug));
|
||
return $wpdb->get_row($wpdb->prepare("SELECT * FROM {$wpdb->prefix}forum_categories WHERE slug=%s", $id_or_slug));
|
||
}
|
||
|
||
public static function get_category_breadcrumb( $cat ) {
|
||
$path = [$cat]; $max = 5;
|
||
while ((int)$cat->parent_id > 0 && $max--) {
|
||
$cat = self::get_category((int)$cat->parent_id);
|
||
if (!$cat) break;
|
||
array_unshift($path, $cat);
|
||
}
|
||
return $path;
|
||
}
|
||
|
||
public static function get_child_categories( $parent_id ) {
|
||
global $wpdb;
|
||
return $wpdb->get_results($wpdb->prepare(
|
||
"SELECT * FROM {$wpdb->prefix}forum_categories WHERE parent_id=%d ORDER BY sort_order ASC", $parent_id
|
||
));
|
||
}
|
||
|
||
// ── Threads ───────────────────────────────────────────────────────────────
|
||
|
||
public static function get_threads( $category_id, $page = 1, $per_page = 20, $include_archived = false ) {
|
||
global $wpdb;
|
||
$offset = ($page - 1) * $per_page;
|
||
$status_sql = $include_archived ? '' : "AND t.status != 'archived'";
|
||
return $wpdb->get_results($wpdb->prepare(
|
||
"SELECT t.*, u.display_name, u.avatar_url, u.username, u.role as author_role,
|
||
p.label as prefix_label, p.color as prefix_color, p.bg_color as prefix_bg
|
||
FROM {$wpdb->prefix}forum_threads t
|
||
JOIN {$wpdb->prefix}forum_users u ON u.id = t.user_id
|
||
LEFT JOIN {$wpdb->prefix}forum_prefixes p ON p.id = t.prefix_id
|
||
WHERE t.category_id = %d AND t.deleted_at IS NULL $status_sql
|
||
ORDER BY t.pinned DESC, t.last_reply_at DESC
|
||
LIMIT %d OFFSET %d",
|
||
$category_id, $per_page, $offset
|
||
));
|
||
}
|
||
|
||
public static function get_archived_threads( $category_id = 0, $page = 1, $per_page = 20 ) {
|
||
global $wpdb;
|
||
$offset = ($page - 1) * $per_page;
|
||
$cat_sql = $category_id ? $wpdb->prepare('AND t.category_id = %d', $category_id) : '';
|
||
return $wpdb->get_results($wpdb->prepare(
|
||
"SELECT t.*, u.display_name, u.avatar_url, u.username, u.role as author_role,
|
||
c.name as cat_name, c.slug as cat_slug
|
||
FROM {$wpdb->prefix}forum_threads t
|
||
JOIN {$wpdb->prefix}forum_users u ON u.id = t.user_id
|
||
JOIN {$wpdb->prefix}forum_categories c ON c.id = t.category_id
|
||
WHERE t.status = 'archived' $cat_sql
|
||
ORDER BY t.last_reply_at DESC
|
||
LIMIT %d OFFSET %d",
|
||
$per_page, $offset
|
||
));
|
||
}
|
||
|
||
public static function count_archived_threads( $category_id = 0 ) {
|
||
global $wpdb;
|
||
if ( $category_id ) {
|
||
return (int)$wpdb->get_var($wpdb->prepare(
|
||
"SELECT COUNT(*) FROM {$wpdb->prefix}forum_threads WHERE status='archived' AND category_id=%d", $category_id
|
||
));
|
||
}
|
||
return (int)$wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->prefix}forum_threads WHERE status='archived'");
|
||
}
|
||
|
||
public static function count_threads( $category_id ) {
|
||
global $wpdb;
|
||
return (int)$wpdb->get_var($wpdb->prepare(
|
||
"SELECT COUNT(*) FROM {$wpdb->prefix}forum_threads WHERE category_id=%d AND status != 'archived' AND deleted_at IS NULL",
|
||
$category_id
|
||
));
|
||
}
|
||
|
||
public static function move_thread( $thread_id, $new_category_id ) {
|
||
global $wpdb;
|
||
$thread = self::get_thread($thread_id);
|
||
if ( ! $thread ) return false;
|
||
$old_cat = (int) $thread->category_id;
|
||
$new_cat = (int) $new_category_id;
|
||
if ( $old_cat === $new_cat ) return false;
|
||
|
||
$wpdb->update( "{$wpdb->prefix}forum_threads", ['category_id' => $new_cat], ['id' => $thread_id] );
|
||
// Adjust thread counts (don't count archived)
|
||
if ( $thread->status !== 'archived' ) {
|
||
$wpdb->query($wpdb->prepare(
|
||
"UPDATE {$wpdb->prefix}forum_categories SET thread_count=GREATEST(thread_count-1,0) WHERE id=%d", $old_cat
|
||
));
|
||
$wpdb->query($wpdb->prepare(
|
||
"UPDATE {$wpdb->prefix}forum_categories SET thread_count=thread_count+1 WHERE id=%d", $new_cat
|
||
));
|
||
}
|
||
// Move post_count contribution too
|
||
$post_count = (int)$wpdb->get_var($wpdb->prepare(
|
||
"SELECT COUNT(*) FROM {$wpdb->prefix}forum_posts WHERE thread_id=%d AND deleted_at IS NULL", $thread_id
|
||
));
|
||
if ( $post_count > 0 ) {
|
||
$wpdb->query($wpdb->prepare(
|
||
"UPDATE {$wpdb->prefix}forum_categories SET post_count=GREATEST(post_count-%d,0) WHERE id=%d", $post_count, $old_cat
|
||
));
|
||
$wpdb->query($wpdb->prepare(
|
||
"UPDATE {$wpdb->prefix}forum_categories SET post_count=post_count+%d WHERE id=%d", $post_count, $new_cat
|
||
));
|
||
}
|
||
return true;
|
||
}
|
||
|
||
public static function get_thread( $id ) {
|
||
global $wpdb;
|
||
return $wpdb->get_row($wpdb->prepare(
|
||
"SELECT t.*, u.display_name, u.avatar_url, u.username, u.signature,
|
||
u.post_count as author_posts, u.registered as author_registered, u.role as author_role,
|
||
p.label as prefix_label, p.color as prefix_color, p.bg_color as prefix_bg
|
||
FROM {$wpdb->prefix}forum_threads t
|
||
JOIN {$wpdb->prefix}forum_users u ON u.id = t.user_id
|
||
LEFT JOIN {$wpdb->prefix}forum_prefixes p ON p.id = t.prefix_id
|
||
WHERE t.id = %d AND t.deleted_at IS NULL", $id
|
||
));
|
||
}
|
||
|
||
public static function create_thread( $data ) {
|
||
global $wpdb;
|
||
$wpdb->insert("{$wpdb->prefix}forum_threads", $data);
|
||
$id = $wpdb->insert_id;
|
||
$wpdb->query($wpdb->prepare("UPDATE {$wpdb->prefix}forum_categories SET thread_count=thread_count+1 WHERE id=%d", $data['category_id']));
|
||
$wpdb->query($wpdb->prepare("UPDATE {$wpdb->prefix}forum_users SET post_count=post_count+1 WHERE id=%d", $data['user_id']));
|
||
return $id;
|
||
}
|
||
|
||
public static function delete_thread( $id ) {
|
||
global $wpdb;
|
||
$thread = self::get_thread($id);
|
||
if (!$thread) return;
|
||
// Clean up tag associations and decrement use_counts
|
||
$tag_ids = $wpdb->get_col( $wpdb->prepare(
|
||
"SELECT tag_id FROM {$wpdb->prefix}forum_thread_tags WHERE thread_id=%d", $id
|
||
) );
|
||
$wpdb->delete( "{$wpdb->prefix}forum_thread_tags", ['thread_id' => $id] );
|
||
if ( $tag_ids ) {
|
||
foreach ( $tag_ids as $tid ) {
|
||
$wpdb->query( $wpdb->prepare(
|
||
"UPDATE {$wpdb->prefix}forum_tags SET use_count=GREATEST(use_count-1,0) WHERE id=%d", (int)$tid
|
||
) );
|
||
}
|
||
}
|
||
// Posts zählen, User-IDs sammeln
|
||
$posts = $wpdb->get_results($wpdb->prepare("SELECT user_id FROM {$wpdb->prefix}forum_posts WHERE thread_id=%d", $id));
|
||
$post_count = count($posts);
|
||
$user_post_counts = [];
|
||
foreach ($posts as $p) {
|
||
$uid = (int)$p->user_id;
|
||
if (!isset($user_post_counts[$uid])) $user_post_counts[$uid] = 0;
|
||
$user_post_counts[$uid]++;
|
||
}
|
||
// Posts löschen
|
||
$wpdb->delete("{$wpdb->prefix}forum_posts", ['thread_id' => $id]);
|
||
$wpdb->delete("{$wpdb->prefix}forum_threads", ['id' => $id]);
|
||
// Zähler anpassen
|
||
if ( $thread->status !== 'archived' ) {
|
||
$wpdb->query($wpdb->prepare("UPDATE {$wpdb->prefix}forum_categories SET thread_count=GREATEST(thread_count-1,0), post_count=GREATEST(post_count-%d,0) WHERE id=%d", $post_count, $thread->category_id));
|
||
}
|
||
foreach ($user_post_counts as $uid => $cnt) {
|
||
$wpdb->query($wpdb->prepare("UPDATE {$wpdb->prefix}forum_users SET post_count=GREATEST(post_count-%d,0) WHERE id=%d", $cnt, $uid));
|
||
}
|
||
}
|
||
|
||
public static function update_thread( $id, $data ) {
|
||
global $wpdb;
|
||
$wpdb->update("{$wpdb->prefix}forum_threads", $data, ['id' => $id]);
|
||
}
|
||
|
||
// ── Posts ─────────────────────────────────────────────────────────────────
|
||
|
||
public static function get_posts( $thread_id, $page = 1, $per_page = 15 ) {
|
||
global $wpdb;
|
||
$offset = ($page - 1) * $per_page;
|
||
return $wpdb->get_results($wpdb->prepare(
|
||
"SELECT p.*, u.display_name, u.avatar_url, u.username, u.signature,
|
||
u.post_count as author_posts, u.role as author_role, u.registered as author_registered
|
||
FROM {$wpdb->prefix}forum_posts p
|
||
JOIN {$wpdb->prefix}forum_users u ON u.id = p.user_id
|
||
WHERE p.thread_id = %d AND p.deleted_at IS NULL
|
||
ORDER BY p.created_at ASC
|
||
LIMIT %d OFFSET %d",
|
||
$thread_id, $per_page, $offset
|
||
));
|
||
}
|
||
|
||
public static function count_posts( $thread_id ) {
|
||
global $wpdb;
|
||
return (int)$wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM {$wpdb->prefix}forum_posts WHERE thread_id=%d AND deleted_at IS NULL", $thread_id));
|
||
}
|
||
|
||
public static function create_post( $data ) {
|
||
global $wpdb;
|
||
$wpdb->insert("{$wpdb->prefix}forum_posts", $data);
|
||
$id = $wpdb->insert_id;
|
||
$wpdb->query($wpdb->prepare("UPDATE {$wpdb->prefix}forum_threads SET reply_count=reply_count+1, last_reply_at=NOW() WHERE id=%d", $data['thread_id']));
|
||
$thread = $wpdb->get_row($wpdb->prepare("SELECT category_id FROM {$wpdb->prefix}forum_threads WHERE id=%d", $data['thread_id']));
|
||
if ($thread) $wpdb->query($wpdb->prepare("UPDATE {$wpdb->prefix}forum_categories SET post_count=post_count+1 WHERE id=%d", $thread->category_id));
|
||
$wpdb->query($wpdb->prepare("UPDATE {$wpdb->prefix}forum_users SET post_count=post_count+1 WHERE id=%d", $data['user_id']));
|
||
return $id;
|
||
}
|
||
|
||
public static function delete_post( $id ) {
|
||
global $wpdb;
|
||
$post = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$wpdb->prefix}forum_posts WHERE id=%d", $id));
|
||
if (!$post) return;
|
||
$wpdb->delete("{$wpdb->prefix}forum_posts", ['id' => $id]);
|
||
$wpdb->query($wpdb->prepare("UPDATE {$wpdb->prefix}forum_threads SET reply_count=GREATEST(reply_count-1,0) WHERE id=%d", $post->thread_id));
|
||
$wpdb->query($wpdb->prepare("UPDATE {$wpdb->prefix}forum_users SET post_count=GREATEST(post_count-1,0) WHERE id=%d", $post->user_id));
|
||
}
|
||
|
||
// ── Likes ─────────────────────────────────────────────────────────────────
|
||
|
||
public static function has_liked( $user_id, $object_id, $type ) {
|
||
global $wpdb;
|
||
return (bool)$wpdb->get_var($wpdb->prepare(
|
||
"SELECT id FROM {$wpdb->prefix}forum_likes WHERE user_id=%d AND object_id=%d AND object_type=%s",
|
||
$user_id, $object_id, $type
|
||
));
|
||
}
|
||
|
||
public static function toggle_like( $user_id, $object_id, $type ) {
|
||
global $wpdb;
|
||
$map = ['thread'=>"{$wpdb->prefix}forum_threads",'post'=>"{$wpdb->prefix}forum_posts"];
|
||
if (self::has_liked($user_id, $object_id, $type)) {
|
||
$wpdb->delete("{$wpdb->prefix}forum_likes", ['user_id'=>$user_id,'object_id'=>$object_id,'object_type'=>$type]);
|
||
if (isset($map[$type])) $wpdb->query($wpdb->prepare("UPDATE {$map[$type]} SET like_count=GREATEST(like_count-1,0) WHERE id=%d",$object_id));
|
||
return 'unliked';
|
||
} else {
|
||
$wpdb->insert("{$wpdb->prefix}forum_likes", ['user_id'=>$user_id,'object_id'=>$object_id,'object_type'=>$type]);
|
||
if (isset($map[$type])) $wpdb->query($wpdb->prepare("UPDATE {$map[$type]} SET like_count=like_count+1 WHERE id=%d",$object_id));
|
||
return 'liked';
|
||
}
|
||
}
|
||
|
||
public static function get_like_count( $object_id, $type ) {
|
||
global $wpdb;
|
||
$map = ['thread'=>"{$wpdb->prefix}forum_threads",'post'=>"{$wpdb->prefix}forum_posts"];
|
||
if (!isset($map[$type])) return 0;
|
||
return (int)$wpdb->get_var($wpdb->prepare("SELECT like_count FROM {$map[$type]} WHERE id=%d",$object_id));
|
||
}
|
||
|
||
// ── Stats ─────────────────────────────────────────────────────────────────
|
||
|
||
public static function get_recent_threads( $limit = 5 ) {
|
||
global $wpdb;
|
||
return $wpdb->get_results($wpdb->prepare(
|
||
"SELECT t.id, t.title, t.created_at, u.display_name, c.name as cat_name, c.slug as cat_slug
|
||
FROM {$wpdb->prefix}forum_threads t
|
||
JOIN {$wpdb->prefix}forum_users u ON u.id = t.user_id
|
||
JOIN {$wpdb->prefix}forum_categories c ON c.id = t.category_id
|
||
WHERE t.status != 'archived' AND t.deleted_at IS NULL
|
||
ORDER BY t.created_at DESC LIMIT %d", $limit
|
||
));
|
||
}
|
||
|
||
public static function get_stats() {
|
||
global $wpdb;
|
||
return [
|
||
'threads' => $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->prefix}forum_threads WHERE status != 'archived' AND deleted_at IS NULL"),
|
||
'posts' => $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->prefix}forum_posts WHERE deleted_at IS NULL"),
|
||
'members' => $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->prefix}forum_users"),
|
||
'newest' => $wpdb->get_var("SELECT display_name FROM {$wpdb->prefix}forum_users ORDER BY registered DESC LIMIT 1"),
|
||
];
|
||
}
|
||
|
||
// ── Reports ───────────────────────────────────────────────────────────────
|
||
|
||
public static function create_report( $data ) {
|
||
global $wpdb;
|
||
$wpdb->insert("{$wpdb->prefix}forum_reports", $data);
|
||
return $wpdb->insert_id;
|
||
}
|
||
|
||
public static function has_reported( $reporter_id, $object_id, $type = 'post' ) {
|
||
global $wpdb;
|
||
return (bool)$wpdb->get_var($wpdb->prepare(
|
||
"SELECT id FROM {$wpdb->prefix}forum_reports WHERE reporter_id=%d AND object_id=%d AND object_type=%s AND status='open'",
|
||
$reporter_id, $object_id, $type
|
||
));
|
||
}
|
||
|
||
public static function get_reports( $status = 'open', $limit = 50 ) {
|
||
global $wpdb;
|
||
$sql = "SELECT r.*,
|
||
rep.display_name AS reporter_name, rep.username AS reporter_username,
|
||
p.content AS post_content, p.thread_id AS thread_id,
|
||
t.title AS thread_title
|
||
FROM {$wpdb->prefix}forum_reports r
|
||
LEFT JOIN {$wpdb->prefix}forum_users rep ON rep.id = r.reporter_id
|
||
LEFT JOIN {$wpdb->prefix}forum_posts p ON p.id = r.object_id AND r.object_type = 'post'
|
||
LEFT JOIN {$wpdb->prefix}forum_threads t ON t.id = p.thread_id";
|
||
if ( $status !== 'all' ) {
|
||
$sql .= $wpdb->prepare( " WHERE r.status = %s", $status );
|
||
}
|
||
$sql .= $wpdb->prepare( " ORDER BY r.created_at DESC LIMIT %d", $limit );
|
||
return $wpdb->get_results( $sql );
|
||
}
|
||
|
||
public static function update_report( $id, $status ) {
|
||
global $wpdb;
|
||
$wpdb->update("{$wpdb->prefix}forum_reports", ['status' => $status], ['id' => $id]);
|
||
}
|
||
|
||
public static function get_user_posts( $user_id, $limit = 30 ) {
|
||
global $wpdb;
|
||
// UNION: Thread-Erstbeiträge (forum_threads) + Antworten (forum_posts)
|
||
return $wpdb->get_results( $wpdb->prepare(
|
||
"SELECT 'thread' AS entry_type,
|
||
t.id AS id, t.content, t.created_at, t.like_count,
|
||
t.id AS thread_id, t.title AS thread_title,
|
||
c.name AS cat_name
|
||
FROM {$wpdb->prefix}forum_threads t
|
||
JOIN {$wpdb->prefix}forum_categories c ON c.id = t.category_id
|
||
WHERE t.user_id = %d
|
||
UNION ALL
|
||
SELECT 'post' AS entry_type,
|
||
p.id AS id, p.content, p.created_at, p.like_count,
|
||
t.id AS thread_id, t.title AS thread_title,
|
||
c.name AS cat_name
|
||
FROM {$wpdb->prefix}forum_posts p
|
||
JOIN {$wpdb->prefix}forum_threads t ON t.id = p.thread_id
|
||
JOIN {$wpdb->prefix}forum_categories c ON c.id = t.category_id
|
||
WHERE p.user_id = %d
|
||
ORDER BY created_at DESC
|
||
LIMIT %d",
|
||
$user_id, $user_id, $limit
|
||
) );
|
||
}
|
||
|
||
// ── Thread-Teilnehmer (für Benachrichtigungen) ───────────────────────────
|
||
|
||
public static function get_thread_participants( $thread_id ) {
|
||
global $wpdb;
|
||
// Thread-Ersteller + alle die geantwortet haben (ohne Duplikate)
|
||
return $wpdb->get_col( $wpdb->prepare(
|
||
"SELECT DISTINCT user_id FROM (
|
||
SELECT user_id FROM {$wpdb->prefix}forum_threads WHERE id = %d
|
||
UNION
|
||
SELECT user_id FROM {$wpdb->prefix}forum_posts WHERE thread_id = %d
|
||
) AS participants",
|
||
$thread_id, $thread_id
|
||
) );
|
||
}
|
||
|
||
// ── Suche ─────────────────────────────────────────────────────────────────
|
||
|
||
public static function search( $query, $limit = 30, $user = null ) {
|
||
global $wpdb;
|
||
$like = '%' . $wpdb->esc_like( $query ) . '%';
|
||
|
||
// Kategorie-Sichtbarkeit: Gäste und Member dürfen keine privaten Kategorien sehen
|
||
$user_level = $user ? WBF_Roles::level( $user->role ) : -99;
|
||
if ( $user_level >= 50 ) {
|
||
// Moderatoren+ sehen alles (inkl. soft-deleted ist extra)
|
||
$cat_filter = '';
|
||
} elseif ( $user ) {
|
||
// Eingeloggte Member/VIP: nur guest_visible oder eigene Rolle reicht
|
||
$cat_filter = "AND c.guest_visible = 1 AND (c.min_role IS NULL OR c.min_role IN ('member','vip'))";
|
||
} else {
|
||
// Gäste: nur komplett öffentliche Kategorien
|
||
$cat_filter = "AND c.guest_visible = 1 AND (c.min_role IS NULL OR c.min_role = 'member')";
|
||
}
|
||
|
||
return $wpdb->get_results( $wpdb->prepare(
|
||
"SELECT 'thread' AS result_type,
|
||
t.id, t.title, t.content, t.created_at, t.reply_count,
|
||
u.display_name, u.avatar_url, u.role AS author_role,
|
||
c.name AS cat_name, c.slug AS cat_slug
|
||
FROM {$wpdb->prefix}forum_threads t
|
||
JOIN {$wpdb->prefix}forum_users u ON u.id = t.user_id
|
||
JOIN {$wpdb->prefix}forum_categories c ON c.id = t.category_id
|
||
WHERE (t.title LIKE %s OR t.content LIKE %s)
|
||
AND t.status != 'archived' AND t.deleted_at IS NULL
|
||
$cat_filter
|
||
UNION ALL
|
||
SELECT 'post' AS result_type,
|
||
p.id, t.title, p.content, p.created_at, 0 AS reply_count,
|
||
u.display_name, u.avatar_url, u.role AS author_role,
|
||
c.name AS cat_name, c.slug AS cat_slug
|
||
FROM {$wpdb->prefix}forum_posts p
|
||
JOIN {$wpdb->prefix}forum_threads t ON t.id = p.thread_id
|
||
JOIN {$wpdb->prefix}forum_users u ON u.id = p.user_id
|
||
JOIN {$wpdb->prefix}forum_categories c ON c.id = t.category_id
|
||
WHERE p.content LIKE %s
|
||
AND p.deleted_at IS NULL AND t.status != 'archived' AND t.deleted_at IS NULL
|
||
$cat_filter
|
||
ORDER BY created_at DESC
|
||
LIMIT %d",
|
||
$like, $like, $like, $limit
|
||
) );
|
||
}
|
||
|
||
// ── Benachrichtigungen ────────────────────────────────────────────────────
|
||
|
||
public static function create_notification( $user_id, $type, $object_id, $actor_id ) {
|
||
global $wpdb;
|
||
// Keine doppelten ungelesenen Benachrichtigungen
|
||
$exists = $wpdb->get_var( $wpdb->prepare(
|
||
"SELECT id FROM {$wpdb->prefix}forum_notifications
|
||
WHERE user_id=%d AND type=%s AND object_id=%d AND actor_id=%d AND is_read=0",
|
||
$user_id, $type, $object_id, $actor_id
|
||
) );
|
||
if ( $exists ) return;
|
||
// Nicht sich selbst benachrichtigen
|
||
if ( (int)$user_id === (int)$actor_id ) return;
|
||
$wpdb->insert( "{$wpdb->prefix}forum_notifications", [
|
||
'user_id' => $user_id,
|
||
'type' => $type,
|
||
'object_id' => $object_id,
|
||
'actor_id' => $actor_id,
|
||
] );
|
||
// MC Bridge: Ingame-Benachrichtigung auslösen wenn Spieler verknüpft ist
|
||
do_action( 'wbf_notification_created', $user_id, $type, $object_id, $actor_id );
|
||
}
|
||
|
||
public static function get_notifications( $user_id, $limit = 20 ) {
|
||
global $wpdb;
|
||
return $wpdb->get_results( $wpdb->prepare(
|
||
"SELECT n.*,
|
||
u.display_name AS actor_name, u.avatar_url AS actor_avatar,
|
||
t.title AS thread_title, t.id AS thread_id
|
||
FROM {$wpdb->prefix}forum_notifications n
|
||
JOIN {$wpdb->prefix}forum_users u ON u.id = n.actor_id
|
||
LEFT JOIN {$wpdb->prefix}forum_threads t ON t.id = n.object_id
|
||
WHERE n.user_id = %d
|
||
ORDER BY n.created_at DESC
|
||
LIMIT %d",
|
||
$user_id, $limit
|
||
) );
|
||
}
|
||
|
||
public static function count_unread_notifications( $user_id ) {
|
||
global $wpdb;
|
||
$table = "{$wpdb->prefix}forum_notifications";
|
||
if ( $wpdb->get_var("SHOW TABLES LIKE '$table'") !== $table ) return 0;
|
||
return (int) $wpdb->get_var( $wpdb->prepare(
|
||
"SELECT COUNT(*) FROM {$wpdb->prefix}forum_notifications WHERE user_id=%d AND is_read=0",
|
||
$user_id
|
||
) );
|
||
}
|
||
|
||
public static function mark_notifications_read( $user_id ) {
|
||
global $wpdb;
|
||
$wpdb->update(
|
||
"{$wpdb->prefix}forum_notifications",
|
||
['is_read' => 1],
|
||
['user_id' => $user_id, 'is_read' => 0]
|
||
);
|
||
}
|
||
|
||
public static function count_open_reports() {
|
||
global $wpdb;
|
||
// Tabelle existiert evtl. noch nicht auf bestehenden Installs → erst prüfen
|
||
$table = "{$wpdb->prefix}forum_reports";
|
||
if ( $wpdb->get_var("SHOW TABLES LIKE '$table'") !== $table ) {
|
||
return 0;
|
||
}
|
||
return (int)$wpdb->get_var("SELECT COUNT(*) FROM $table WHERE status='open'");
|
||
}
|
||
|
||
// ── Tags ──────────────────────────────────────────────────────────────────
|
||
|
||
/**
|
||
* Tags für einen Thread speichern.
|
||
* $raw_tags = komma- oder leerzeichen-getrennte Zeichenkette, z.B. "php wordpress #cms"
|
||
*/
|
||
public static function sync_thread_tags( $thread_id, $raw_tags ) {
|
||
global $wpdb;
|
||
$thread_id = (int) $thread_id;
|
||
|
||
// Bestehende Verknüpfungen löschen
|
||
$old_ids = $wpdb->get_col( $wpdb->prepare(
|
||
"SELECT tag_id FROM {$wpdb->prefix}forum_thread_tags WHERE thread_id=%d", $thread_id
|
||
) );
|
||
$wpdb->delete( "{$wpdb->prefix}forum_thread_tags", ['thread_id' => $thread_id] );
|
||
// use_count für entfernte Tags dekrementieren
|
||
if ( $old_ids ) {
|
||
$placeholders = implode(',', array_fill(0, count($old_ids), '%d'));
|
||
$wpdb->query( $wpdb->prepare(
|
||
"UPDATE {$wpdb->prefix}forum_tags SET use_count = GREATEST(use_count-1,0) WHERE id IN ($placeholders)",
|
||
...$old_ids
|
||
) );
|
||
}
|
||
|
||
// Tags parsen
|
||
$names = self::parse_tag_string( $raw_tags );
|
||
if ( empty($names) ) return;
|
||
|
||
foreach ( $names as $name ) {
|
||
$slug = sanitize_title( $name );
|
||
if ( ! $slug ) continue;
|
||
|
||
// Upsert Tag
|
||
$tag = $wpdb->get_row( $wpdb->prepare(
|
||
"SELECT id FROM {$wpdb->prefix}forum_tags WHERE slug=%s", $slug
|
||
) );
|
||
if ( $tag ) {
|
||
$tag_id = (int) $tag->id;
|
||
$wpdb->query( $wpdb->prepare(
|
||
"UPDATE {$wpdb->prefix}forum_tags SET use_count=use_count+1 WHERE id=%d", $tag_id
|
||
) );
|
||
} else {
|
||
$wpdb->insert( "{$wpdb->prefix}forum_tags", [
|
||
'name' => $name,
|
||
'slug' => $slug,
|
||
'use_count' => 1,
|
||
] );
|
||
$tag_id = $wpdb->insert_id;
|
||
}
|
||
|
||
// Pivot
|
||
$wpdb->replace( "{$wpdb->prefix}forum_thread_tags", [
|
||
'thread_id' => $thread_id,
|
||
'tag_id' => $tag_id,
|
||
] );
|
||
}
|
||
}
|
||
|
||
public static function parse_tag_string( $raw ) {
|
||
// Strip # prefix, split by comma / space / semicolon
|
||
$raw = strip_tags( $raw );
|
||
$raw = str_replace('#', '', $raw);
|
||
$parts = preg_split('/[\s,;]+/', $raw, -1, PREG_SPLIT_NO_EMPTY);
|
||
$names = [];
|
||
foreach ( $parts as $p ) {
|
||
$p = mb_strtolower( trim($p) );
|
||
if ( mb_strlen($p) >= 2 && mb_strlen($p) <= 30 ) {
|
||
$names[] = $p;
|
||
}
|
||
}
|
||
return array_unique( array_slice($names, 0, 10) ); // max 10 Tags pro Thread
|
||
}
|
||
|
||
/** Tags eines Threads laden */
|
||
public static function get_thread_tags( $thread_id ) {
|
||
global $wpdb;
|
||
return $wpdb->get_results( $wpdb->prepare(
|
||
"SELECT t.* FROM {$wpdb->prefix}forum_tags t
|
||
INNER JOIN {$wpdb->prefix}forum_thread_tags tt ON tt.tag_id = t.id
|
||
WHERE tt.thread_id = %d
|
||
ORDER BY t.name ASC",
|
||
$thread_id
|
||
) );
|
||
}
|
||
|
||
/** Threads nach Tag-Slug laden */
|
||
public static function get_threads_by_tag( $slug, $page = 1, $per_page = 20 ) {
|
||
global $wpdb;
|
||
$offset = ($page - 1) * $per_page;
|
||
return $wpdb->get_results( $wpdb->prepare(
|
||
"SELECT t.*, u.display_name, u.avatar_url, u.role AS author_role,
|
||
c.name AS cat_name, c.slug AS cat_slug
|
||
FROM {$wpdb->prefix}forum_threads t
|
||
INNER JOIN {$wpdb->prefix}forum_thread_tags tt ON tt.thread_id = t.id
|
||
INNER JOIN {$wpdb->prefix}forum_tags tg ON tg.id = tt.tag_id
|
||
JOIN {$wpdb->prefix}forum_users u ON u.id = t.user_id
|
||
JOIN {$wpdb->prefix}forum_categories c ON c.id = t.category_id
|
||
WHERE tg.slug = %s AND t.status != 'archived'
|
||
ORDER BY t.last_reply_at DESC
|
||
LIMIT %d OFFSET %d",
|
||
$slug, $per_page, $offset
|
||
) );
|
||
}
|
||
|
||
public static function count_threads_by_tag( $slug ) {
|
||
global $wpdb;
|
||
return (int) $wpdb->get_var( $wpdb->prepare(
|
||
"SELECT COUNT(*) FROM {$wpdb->prefix}forum_threads t
|
||
INNER JOIN {$wpdb->prefix}forum_thread_tags tt ON tt.thread_id = t.id
|
||
INNER JOIN {$wpdb->prefix}forum_tags tg ON tg.id = tt.tag_id
|
||
WHERE tg.slug = %s AND t.status != 'archived'",
|
||
$slug
|
||
) );
|
||
}
|
||
|
||
/** Tag-Objekt per Slug */
|
||
public static function get_tag( $slug ) {
|
||
global $wpdb;
|
||
return $wpdb->get_row( $wpdb->prepare(
|
||
"SELECT * FROM {$wpdb->prefix}forum_tags WHERE slug=%s", $slug
|
||
) );
|
||
}
|
||
|
||
/** Top-Tags nach Nutzungshäufigkeit */
|
||
public static function get_popular_tags( $limit = 30 ) {
|
||
global $wpdb;
|
||
return $wpdb->get_results( $wpdb->prepare(
|
||
"SELECT * FROM {$wpdb->prefix}forum_tags WHERE use_count > 0 ORDER BY use_count DESC LIMIT %d",
|
||
$limit
|
||
) );
|
||
}
|
||
|
||
/** Autocomplete: Tags die mit $q beginnen */
|
||
public static function suggest_tags( $q, $limit = 8 ) {
|
||
global $wpdb;
|
||
$like = $wpdb->esc_like( strtolower($q) ) . '%';
|
||
return $wpdb->get_results( $wpdb->prepare(
|
||
"SELECT name, slug, use_count FROM {$wpdb->prefix}forum_tags WHERE slug LIKE %s ORDER BY use_count DESC LIMIT %d",
|
||
$like, $limit
|
||
) );
|
||
}
|
||
|
||
|
||
// ── Online-Status ──────────────────────────────────────────────────────────
|
||
|
||
/** Letztes Aktivitätsdatum des Users aktualisieren */
|
||
public static function touch_last_active( $user_id ) {
|
||
global $wpdb;
|
||
$wpdb->query( $wpdb->prepare(
|
||
"UPDATE {$wpdb->prefix}forum_users SET last_active=NOW() WHERE id=%d", (int)$user_id
|
||
) );
|
||
}
|
||
|
||
/** User die in den letzten $minutes Minuten aktiv waren */
|
||
public static function get_online_users( $minutes = 15 ) {
|
||
global $wpdb;
|
||
return $wpdb->get_results( $wpdb->prepare(
|
||
"SELECT id, username, display_name, avatar_url, role
|
||
FROM {$wpdb->prefix}forum_users
|
||
WHERE last_active >= DATE_SUB(NOW(), INTERVAL %d MINUTE)
|
||
ORDER BY last_active DESC
|
||
LIMIT 50",
|
||
$minutes
|
||
) );
|
||
}
|
||
|
||
public static function is_online( $user_id, $minutes = 15 ) {
|
||
global $wpdb;
|
||
return (bool)$wpdb->get_var( $wpdb->prepare(
|
||
"SELECT id FROM {$wpdb->prefix}forum_users
|
||
WHERE id=%d AND last_active >= DATE_SUB(NOW(), INTERVAL %d MINUTE)",
|
||
$user_id, $minutes
|
||
) );
|
||
}
|
||
|
||
// ── Reaktionen ────────────────────────────────────────────────────────────
|
||
|
||
/** Erlaubte Reaktionen aus den Einstellungen holen */
|
||
public static function get_allowed_reactions() {
|
||
$saved = get_option('wbf_reactions', null);
|
||
if ( $saved !== null && is_array($saved) && count($saved) > 0 ) return $saved;
|
||
return ['👍','❤️','😂','😮','😢','😡']; // Defaults
|
||
}
|
||
|
||
public static function set_reaction( $user_id, $object_id, $object_type, $reaction ) {
|
||
global $wpdb;
|
||
$allowed = self::get_allowed_reactions();
|
||
if ( ! in_array($reaction, $allowed, true) ) return false;
|
||
|
||
$existing = $wpdb->get_row( $wpdb->prepare(
|
||
"SELECT reaction FROM {$wpdb->prefix}forum_reactions
|
||
WHERE user_id=%d AND object_id=%d AND object_type=%s",
|
||
$user_id, $object_id, $object_type
|
||
) );
|
||
|
||
if ( $existing && $existing->reaction === $reaction ) {
|
||
// Same reaction → remove (toggle off)
|
||
$wpdb->delete( "{$wpdb->prefix}forum_reactions", [
|
||
'user_id' => $user_id, 'object_id' => $object_id, 'object_type' => $object_type
|
||
] );
|
||
return 'removed';
|
||
}
|
||
// Insert or replace
|
||
$wpdb->replace( "{$wpdb->prefix}forum_reactions", [
|
||
'user_id' => $user_id,
|
||
'object_id' => $object_id,
|
||
'object_type' => $object_type,
|
||
'reaction' => $reaction,
|
||
] );
|
||
return 'added';
|
||
}
|
||
|
||
/** Reaktionen für ein Objekt — [emoji => count] + user's own reaction */
|
||
public static function get_reactions( $object_id, $object_type, $user_id = 0 ) {
|
||
global $wpdb;
|
||
$rows = $wpdb->get_results( $wpdb->prepare(
|
||
"SELECT reaction, COUNT(*) as cnt
|
||
FROM {$wpdb->prefix}forum_reactions
|
||
WHERE object_id=%d AND object_type=%s
|
||
GROUP BY reaction",
|
||
$object_id, $object_type
|
||
) );
|
||
$counts = [];
|
||
foreach ( $rows as $r ) $counts[$r->reaction] = (int)$r->cnt;
|
||
|
||
$my = '';
|
||
if ( $user_id ) {
|
||
$row = $wpdb->get_row( $wpdb->prepare(
|
||
"SELECT reaction FROM {$wpdb->prefix}forum_reactions
|
||
WHERE user_id=%d AND object_id=%d AND object_type=%s",
|
||
$user_id, $object_id, $object_type
|
||
) );
|
||
if ($row) $my = $row->reaction;
|
||
}
|
||
return ['counts' => $counts, 'mine' => $my];
|
||
}
|
||
|
||
// ── Private Nachrichten ───────────────────────────────────────────────────
|
||
|
||
public static function send_message( $from_id, $to_id, $content ) {
|
||
global $wpdb;
|
||
$wpdb->insert( "{$wpdb->prefix}forum_messages", [
|
||
'from_id' => (int)$from_id,
|
||
'to_id' => (int)$to_id,
|
||
'content' => mb_substr(strip_tags($content), 0, 2000),
|
||
'is_read' => 0,
|
||
] );
|
||
return $wpdb->insert_id;
|
||
}
|
||
|
||
/** Alle Konversationspartner des Users */
|
||
public static function get_inbox( $user_id ) {
|
||
global $wpdb;
|
||
return $wpdb->get_results( $wpdb->prepare(
|
||
"SELECT
|
||
partner_id,
|
||
MAX(last_msg_id) AS last_msg_id,
|
||
MAX(last_time) AS last_time,
|
||
SUM(unread_cnt) AS unread_cnt,
|
||
partner_name, partner_avatar, partner_role
|
||
FROM (
|
||
SELECT
|
||
m.from_id AS partner_id,
|
||
MAX(m.id) AS last_msg_id,
|
||
MAX(m.created_at) AS last_time,
|
||
SUM(CASE WHEN m.is_read=0 AND m.to_id=%d THEN 1 ELSE 0 END) AS unread_cnt,
|
||
u.display_name AS partner_name,
|
||
u.avatar_url AS partner_avatar,
|
||
u.role AS partner_role
|
||
FROM {$wpdb->prefix}forum_messages m
|
||
JOIN {$wpdb->prefix}forum_users u ON u.id = m.from_id
|
||
WHERE m.to_id=%d AND m.deleted_by_receiver=0
|
||
GROUP BY m.from_id
|
||
|
||
UNION ALL
|
||
|
||
SELECT
|
||
m.to_id AS partner_id,
|
||
MAX(m.id) AS last_msg_id,
|
||
MAX(m.created_at) AS last_time,
|
||
0 AS unread_cnt,
|
||
u.display_name AS partner_name,
|
||
u.avatar_url AS partner_avatar,
|
||
u.role AS partner_role
|
||
FROM {$wpdb->prefix}forum_messages m
|
||
JOIN {$wpdb->prefix}forum_users u ON u.id = m.to_id
|
||
WHERE m.from_id=%d AND m.deleted_by_sender=0
|
||
GROUP BY m.to_id
|
||
) sub
|
||
GROUP BY partner_id, partner_name, partner_avatar, partner_role
|
||
ORDER BY last_time DESC",
|
||
$user_id, $user_id, $user_id
|
||
) );
|
||
}
|
||
|
||
/** Nachrichten einer Konversation zwischen zwei Usern */
|
||
public static function get_conversation( $user_id, $partner_id, $limit = 50, $offset = 0 ) {
|
||
global $wpdb;
|
||
// Neueste $limit Nachrichten ab $offset holen, dann aufsteigend sortieren
|
||
return $wpdb->get_results( $wpdb->prepare(
|
||
"SELECT * FROM (
|
||
SELECT m.*, u.display_name AS sender_name, u.avatar_url AS sender_avatar
|
||
FROM {$wpdb->prefix}forum_messages m
|
||
JOIN {$wpdb->prefix}forum_users u ON u.id = m.from_id
|
||
WHERE ( (m.from_id=%d AND m.to_id=%d AND m.deleted_by_sender=0)
|
||
OR (m.from_id=%d AND m.to_id=%d AND m.deleted_by_receiver=0) )
|
||
ORDER BY m.created_at DESC
|
||
LIMIT %d OFFSET %d
|
||
) sub ORDER BY sub.created_at ASC",
|
||
$user_id, $partner_id, $partner_id, $user_id, $limit, $offset
|
||
) );
|
||
}
|
||
|
||
public static function count_conversation( $user_id, $partner_id ) {
|
||
global $wpdb;
|
||
return (int) $wpdb->get_var( $wpdb->prepare(
|
||
"SELECT COUNT(*) FROM {$wpdb->prefix}forum_messages m
|
||
WHERE ( (m.from_id=%d AND m.to_id=%d AND m.deleted_by_sender=0)
|
||
OR (m.from_id=%d AND m.to_id=%d AND m.deleted_by_receiver=0) )",
|
||
$user_id, $partner_id, $partner_id, $user_id
|
||
) );
|
||
}
|
||
|
||
public static function mark_messages_read( $user_id, $partner_id ) {
|
||
global $wpdb;
|
||
$wpdb->query( $wpdb->prepare(
|
||
"UPDATE {$wpdb->prefix}forum_messages SET is_read=1
|
||
WHERE to_id=%d AND from_id=%d AND is_read=0",
|
||
$user_id, $partner_id
|
||
) );
|
||
}
|
||
|
||
public static function count_unread_messages( $user_id ) {
|
||
global $wpdb;
|
||
$table = "{$wpdb->prefix}forum_messages";
|
||
if ( $wpdb->get_var("SHOW TABLES LIKE '$table'") !== $table ) return 0;
|
||
return (int)$wpdb->get_var( $wpdb->prepare(
|
||
"SELECT COUNT(*) FROM {$wpdb->prefix}forum_messages WHERE to_id=%d AND is_read=0 AND deleted_by_receiver=0",
|
||
$user_id
|
||
) );
|
||
}
|
||
|
||
// ── Remember-Me ───────────────────────────────────────────────────────────
|
||
|
||
public static function create_remember_token( $user_id ) {
|
||
global $wpdb;
|
||
$token = bin2hex( random_bytes(32) );
|
||
$token_hash = hash('sha256', $token);
|
||
$expires = date('Y-m-d H:i:s', strtotime('+30 days'));
|
||
// Delete existing tokens for this user first
|
||
$wpdb->delete( "{$wpdb->prefix}forum_remember_tokens", ['user_id' => $user_id] );
|
||
$wpdb->insert( "{$wpdb->prefix}forum_remember_tokens", [
|
||
'user_id' => $user_id,
|
||
'token' => $token_hash,
|
||
'expires_at' => $expires,
|
||
] );
|
||
return $token;
|
||
}
|
||
|
||
public static function verify_remember_token( $token ) {
|
||
global $wpdb;
|
||
$table = "{$wpdb->prefix}forum_remember_tokens";
|
||
if ( $wpdb->get_var("SHOW TABLES LIKE '$table'") !== $table ) return null;
|
||
$token_hash = hash('sha256', sanitize_text_field($token));
|
||
return $wpdb->get_row( $wpdb->prepare(
|
||
"SELECT user_id FROM {$wpdb->prefix}forum_remember_tokens
|
||
WHERE token=%s AND expires_at > NOW()",
|
||
$token_hash
|
||
) );
|
||
}
|
||
|
||
public static function delete_remember_token( $user_id ) {
|
||
global $wpdb;
|
||
$wpdb->delete( "{$wpdb->prefix}forum_remember_tokens", ['user_id' => (int)$user_id] );
|
||
}
|
||
|
||
// ── @Erwähnungen ──────────────────────────────────────────────────────────
|
||
|
||
/** Extrahiert @usernames und gibt User-Objekte zurück */
|
||
public static function extract_mentions( $content ) {
|
||
preg_match_all( '/@([a-zA-Z0-9_]{3,60})/', $content, $m );
|
||
$users = [];
|
||
foreach ( array_unique($m[1]) as $username ) {
|
||
$user = self::get_user_by('username', $username);
|
||
if ($user) $users[] = $user;
|
||
}
|
||
return $users;
|
||
}
|
||
|
||
|
||
|
||
// ── Passwort-Reset ────────────────────────────────────────────────────────
|
||
|
||
public static function create_reset_token( $user_id ) {
|
||
global $wpdb;
|
||
$token = bin2hex( random_bytes(32) );
|
||
$hash = hash( 'sha256', $token );
|
||
// Altes Token dieses Users zurücksetzen bevor ein neues gesetzt wird
|
||
$wpdb->query( $wpdb->prepare(
|
||
"UPDATE {$wpdb->prefix}forum_users
|
||
SET reset_token=NULL, reset_token_expires=NULL
|
||
WHERE id=%d",
|
||
(int) $user_id
|
||
) );
|
||
$wpdb->query( $wpdb->prepare(
|
||
"UPDATE {$wpdb->prefix}forum_users
|
||
SET reset_token=%s, reset_token_expires=DATE_ADD(NOW(), INTERVAL 1 HOUR)
|
||
WHERE id=%d",
|
||
$hash, (int) $user_id
|
||
) );
|
||
return $token; // Klartext-Token → per E-Mail senden
|
||
}
|
||
|
||
public static function verify_reset_token( $token ) {
|
||
global $wpdb;
|
||
$hash = hash( 'sha256', $token );
|
||
return $wpdb->get_row( $wpdb->prepare(
|
||
"SELECT * FROM {$wpdb->prefix}forum_users
|
||
WHERE reset_token=%s AND reset_token_expires > NOW()",
|
||
$hash
|
||
) );
|
||
}
|
||
|
||
public static function use_reset_token( $token, $new_password ) {
|
||
global $wpdb;
|
||
$user = self::verify_reset_token( $token );
|
||
if ( ! $user ) return false;
|
||
$wpdb->update(
|
||
"{$wpdb->prefix}forum_users",
|
||
[
|
||
'password' => password_hash( $new_password, PASSWORD_DEFAULT ),
|
||
'reset_token' => null,
|
||
'reset_token_expires' => null,
|
||
],
|
||
['id' => $user->id]
|
||
);
|
||
return true;
|
||
}
|
||
|
||
|
||
|
||
// ── Einladungen ───────────────────────────────────────────────────────────
|
||
|
||
public static function create_invite( $created_by, $max_uses = 1, $note = '', $expires_at = null ) {
|
||
global $wpdb;
|
||
$code = strtoupper( substr( bin2hex( random_bytes(6) ), 0, 10 ) );
|
||
$wpdb->insert( "{$wpdb->prefix}forum_invites", [
|
||
'code' => $code,
|
||
'created_by' => (int) $created_by,
|
||
'max_uses' => (int) $max_uses,
|
||
'note' => sanitize_text_field( $note ),
|
||
'expires_at' => $expires_at,
|
||
] );
|
||
return $code;
|
||
}
|
||
|
||
public static function get_invite( $code ) {
|
||
global $wpdb;
|
||
return $wpdb->get_row( $wpdb->prepare(
|
||
"SELECT * FROM {$wpdb->prefix}forum_invites WHERE code = %s",
|
||
strtoupper( trim($code) )
|
||
) );
|
||
}
|
||
|
||
public static function verify_invite( $code ) {
|
||
$inv = self::get_invite( $code );
|
||
if ( ! $inv ) return false;
|
||
if ( $inv->use_count >= $inv->max_uses ) return false;
|
||
if ( $inv->expires_at && strtotime($inv->expires_at) < time() ) return false;
|
||
return $inv;
|
||
}
|
||
|
||
public static function use_invite( $code, $user_id ) {
|
||
global $wpdb;
|
||
$inv = self::verify_invite( $code );
|
||
if ( ! $inv ) return false;
|
||
$wpdb->query( $wpdb->prepare(
|
||
"UPDATE {$wpdb->prefix}forum_invites
|
||
SET use_count = use_count + 1, used_by = %d
|
||
WHERE code = %s",
|
||
(int) $user_id, strtoupper($code)
|
||
) );
|
||
return true;
|
||
}
|
||
|
||
public static function get_all_invites( $limit = 100 ) {
|
||
global $wpdb;
|
||
return $wpdb->get_results( $wpdb->prepare(
|
||
"SELECT i.*, u.display_name AS creator_name,
|
||
uu.display_name AS used_name
|
||
FROM {$wpdb->prefix}forum_invites i
|
||
LEFT JOIN {$wpdb->prefix}forum_users u ON u.id = i.created_by
|
||
LEFT JOIN {$wpdb->prefix}forum_users uu ON uu.id = i.used_by
|
||
ORDER BY i.created_at DESC
|
||
LIMIT %d",
|
||
$limit
|
||
) );
|
||
}
|
||
|
||
public static function delete_invite( $id ) {
|
||
global $wpdb;
|
||
$wpdb->delete( "{$wpdb->prefix}forum_invites", ['id' => (int)$id] );
|
||
}
|
||
|
||
|
||
|
||
// ── Thread-Abonnements ────────────────────────────────────────────────────
|
||
|
||
public static function subscribe( $user_id, $thread_id ) {
|
||
global $wpdb;
|
||
$wpdb->replace("{$wpdb->prefix}forum_subscriptions", [
|
||
'user_id' => (int)$user_id,
|
||
'thread_id' => (int)$thread_id,
|
||
]);
|
||
}
|
||
|
||
public static function unsubscribe( $user_id, $thread_id ) {
|
||
global $wpdb;
|
||
$wpdb->delete("{$wpdb->prefix}forum_subscriptions", [
|
||
'user_id' => (int)$user_id,
|
||
'thread_id' => (int)$thread_id,
|
||
]);
|
||
}
|
||
|
||
public static function is_subscribed( $user_id, $thread_id ) {
|
||
global $wpdb;
|
||
return (bool)$wpdb->get_var($wpdb->prepare(
|
||
"SELECT id FROM {$wpdb->prefix}forum_subscriptions WHERE user_id=%d AND thread_id=%d",
|
||
(int)$user_id, (int)$thread_id
|
||
));
|
||
}
|
||
|
||
public static function get_thread_subscribers( $thread_id ) {
|
||
global $wpdb;
|
||
return $wpdb->get_results($wpdb->prepare(
|
||
"SELECT u.id, u.email, u.display_name
|
||
FROM {$wpdb->prefix}forum_subscriptions s
|
||
JOIN {$wpdb->prefix}forum_users u ON u.id = s.user_id
|
||
WHERE s.thread_id = %d",
|
||
(int)$thread_id
|
||
));
|
||
}
|
||
|
||
// ── Soft-Delete ───────────────────────────────────────────────────────────
|
||
|
||
public static function soft_delete_thread( $thread_id ) {
|
||
global $wpdb;
|
||
$wpdb->update(
|
||
"{$wpdb->prefix}forum_threads",
|
||
['deleted_at' => current_time('mysql')],
|
||
['id' => (int)$thread_id]
|
||
);
|
||
}
|
||
|
||
public static function soft_delete_post( $post_id ) {
|
||
global $wpdb;
|
||
// Soft-Delete setzen
|
||
$wpdb->update(
|
||
"{$wpdb->prefix}forum_posts",
|
||
['deleted_at' => current_time('mysql')],
|
||
['id' => (int)$post_id]
|
||
);
|
||
// Zähler anpassen
|
||
$post = $wpdb->get_row($wpdb->prepare("SELECT thread_id, user_id FROM {$wpdb->prefix}forum_posts WHERE id=%d", $post_id));
|
||
if ($post) {
|
||
// Thread reply_count -1
|
||
$wpdb->query($wpdb->prepare("UPDATE {$wpdb->prefix}forum_threads SET reply_count=GREATEST(reply_count-1,0) WHERE id=%d", $post->thread_id));
|
||
// User post_count -1
|
||
$wpdb->query($wpdb->prepare("UPDATE {$wpdb->prefix}forum_users SET post_count=GREATEST(post_count-1,0) WHERE id=%d", $post->user_id));
|
||
// Kategorie post_count -1
|
||
$cat_id = $wpdb->get_var($wpdb->prepare("SELECT category_id FROM {$wpdb->prefix}forum_threads WHERE id=%d", $post->thread_id));
|
||
if ($cat_id) {
|
||
$wpdb->query($wpdb->prepare("UPDATE {$wpdb->prefix}forum_categories SET post_count=GREATEST(post_count-1,0) WHERE id=%d", $cat_id));
|
||
}
|
||
}
|
||
}
|
||
|
||
public static function restore_thread( $thread_id ) {
|
||
global $wpdb;
|
||
$wpdb->update("{$wpdb->prefix}forum_threads", ['deleted_at'=>null], ['id'=>(int)$thread_id]);
|
||
}
|
||
|
||
public static function restore_post( $post_id ) {
|
||
global $wpdb;
|
||
$wpdb->update("{$wpdb->prefix}forum_posts", ['deleted_at'=>null], ['id'=>(int)$post_id]);
|
||
}
|
||
|
||
public static function get_deleted_content( $limit = 50 ) {
|
||
global $wpdb;
|
||
$threads = $wpdb->get_results($wpdb->prepare(
|
||
"SELECT 'thread' as type, t.id, t.title as content_preview, t.deleted_at,
|
||
u.display_name, c.name as cat_name
|
||
FROM {$wpdb->prefix}forum_threads t
|
||
JOIN {$wpdb->prefix}forum_users u ON u.id=t.user_id
|
||
LEFT JOIN {$wpdb->prefix}forum_categories c ON c.id=t.category_id
|
||
WHERE t.deleted_at IS NOT NULL
|
||
ORDER BY t.deleted_at DESC LIMIT %d", $limit
|
||
));
|
||
$posts = $wpdb->get_results($wpdb->prepare(
|
||
"SELECT 'post' as type, p.id, LEFT(p.content,80) as content_preview, p.deleted_at,
|
||
u.display_name, t.title as cat_name
|
||
FROM {$wpdb->prefix}forum_posts p
|
||
JOIN {$wpdb->prefix}forum_users u ON u.id=p.user_id
|
||
LEFT JOIN {$wpdb->prefix}forum_threads t ON t.id=p.thread_id
|
||
WHERE p.deleted_at IS NOT NULL
|
||
ORDER BY p.deleted_at DESC LIMIT %d", $limit
|
||
));
|
||
return array_merge($threads, $posts);
|
||
}
|
||
|
||
// ── Nutzungs-Statistiken ──────────────────────────────────────────────────
|
||
|
||
public static function get_activity_stats( $days = 30 ) {
|
||
global $wpdb;
|
||
$since = date('Y-m-d', strtotime("-{$days} days"));
|
||
|
||
$posts_per_day = $wpdb->get_results($wpdb->prepare(
|
||
"SELECT DATE(created_at) as day, COUNT(*) as count
|
||
FROM {$wpdb->prefix}forum_posts
|
||
WHERE created_at >= %s AND deleted_at IS NULL
|
||
GROUP BY DATE(created_at) ORDER BY day ASC",
|
||
$since
|
||
));
|
||
$threads_per_day = $wpdb->get_results($wpdb->prepare(
|
||
"SELECT DATE(created_at) as day, COUNT(*) as count
|
||
FROM {$wpdb->prefix}forum_threads
|
||
WHERE created_at >= %s AND deleted_at IS NULL
|
||
GROUP BY DATE(created_at) ORDER BY day ASC",
|
||
$since
|
||
));
|
||
$registrations = $wpdb->get_results($wpdb->prepare(
|
||
"SELECT DATE(registered) as day, COUNT(*) as count
|
||
FROM {$wpdb->prefix}forum_users
|
||
WHERE registered >= %s
|
||
GROUP BY DATE(registered) ORDER BY day ASC",
|
||
$since
|
||
));
|
||
$top_posters = $wpdb->get_results($wpdb->prepare(
|
||
"SELECT u.display_name, u.role, COUNT(p.id) as post_count
|
||
FROM {$wpdb->prefix}forum_posts p
|
||
JOIN {$wpdb->prefix}forum_users u ON u.id=p.user_id
|
||
WHERE p.created_at >= %s AND p.deleted_at IS NULL
|
||
GROUP BY u.id ORDER BY post_count DESC LIMIT 10",
|
||
$since
|
||
));
|
||
$active_hours = $wpdb->get_results($wpdb->prepare(
|
||
"SELECT HOUR(created_at) as hour, COUNT(*) as count
|
||
FROM {$wpdb->prefix}forum_posts
|
||
WHERE created_at >= %s AND deleted_at IS NULL
|
||
GROUP BY HOUR(created_at) ORDER BY hour ASC",
|
||
$since
|
||
));
|
||
|
||
return compact('posts_per_day','threads_per_day','registrations','top_posters','active_hours');
|
||
}
|
||
|
||
// ── Benutzerdefinierte Profilfelder ───────────────────────────────────────
|
||
|
||
public static function get_profile_field_defs() {
|
||
$fields = get_option( 'wbf_profile_fields', [] );
|
||
return is_array( $fields ) ? $fields : [];
|
||
}
|
||
|
||
public static function save_profile_field_defs( $fields ) {
|
||
update_option( 'wbf_profile_fields', $fields );
|
||
}
|
||
|
||
public static function get_profile_field_categories() {
|
||
$cats = get_option( 'wbf_profile_field_cats', null );
|
||
if ( $cats === null ) {
|
||
// Default-Kategorien beim ersten Aufruf
|
||
$defaults = [
|
||
[ 'id' => 'cat_allgemein', 'name' => 'Allgemein', 'icon' => '👤' ],
|
||
[ 'id' => 'cat_kontakt', 'name' => 'Kontakt', 'icon' => '✉️' ],
|
||
[ 'id' => 'cat_social', 'name' => 'Social Media', 'icon' => '🌐' ],
|
||
];
|
||
update_option( 'wbf_profile_field_cats', $defaults );
|
||
return $defaults;
|
||
}
|
||
return is_array( $cats ) ? $cats : [];
|
||
}
|
||
|
||
public static function save_profile_field_categories( $cats ) {
|
||
update_option( 'wbf_profile_field_cats', $cats );
|
||
}
|
||
|
||
public static function get_user_meta( $user_id ) {
|
||
global $wpdb;
|
||
$rows = $wpdb->get_results( $wpdb->prepare(
|
||
"SELECT meta_key, meta_value FROM {$wpdb->prefix}forum_user_meta WHERE user_id = %d",
|
||
(int) $user_id
|
||
) );
|
||
$out = [];
|
||
foreach ( $rows as $r ) $out[ $r->meta_key ] = $r->meta_value;
|
||
return $out;
|
||
}
|
||
|
||
/**
|
||
* Gibt einen einzelnen Meta-Wert zurück (oder leeren String wenn nicht vorhanden).
|
||
*/
|
||
public static function get_user_meta_single( $user_id, $key ) {
|
||
global $wpdb;
|
||
$value = $wpdb->get_var( $wpdb->prepare(
|
||
"SELECT meta_value FROM {$wpdb->prefix}forum_user_meta WHERE user_id = %d AND meta_key = %s LIMIT 1",
|
||
(int) $user_id, $key
|
||
) );
|
||
return $value !== null ? $value : '';
|
||
}
|
||
|
||
public static function set_user_meta( $user_id, $key, $value ) {
|
||
global $wpdb;
|
||
$wpdb->replace(
|
||
"{$wpdb->prefix}forum_user_meta",
|
||
[ 'user_id' => (int) $user_id, 'meta_key' => $key, 'meta_value' => $value ],
|
||
[ '%d', '%s', '%s' ]
|
||
);
|
||
}
|
||
|
||
public static function delete_user_meta_all( $user_id ) {
|
||
global $wpdb;
|
||
$wpdb->delete( "{$wpdb->prefix}forum_user_meta", [ 'user_id' => (int) $user_id ] );
|
||
}
|
||
|
||
// ── Zeitlich begrenzte Sperren ────────────────────────────────────────────
|
||
|
||
/**
|
||
* Setzt eine zeitlich begrenzte Sperre für einen User.
|
||
* Speichert die vorherige Rolle in pre_ban_role.
|
||
*
|
||
* @param int $user_id
|
||
* @param string $until MySQL DATETIME z.B. '2025-12-31 23:59:00'
|
||
* @param string $reason Sperrgrund
|
||
*/
|
||
public static function temp_ban( $user_id, $until, $reason = '' ) {
|
||
global $wpdb;
|
||
$user = self::get_user( (int) $user_id );
|
||
if ( ! $user || $user->role === 'superadmin' ) return false;
|
||
|
||
$wpdb->update(
|
||
"{$wpdb->prefix}forum_users",
|
||
[
|
||
'pre_ban_role' => $user->role !== 'banned' ? $user->role : ( $user->pre_ban_role ?: 'member' ),
|
||
'role' => 'banned',
|
||
'ban_reason' => $reason,
|
||
'ban_until' => $until,
|
||
],
|
||
[ 'id' => (int) $user_id ],
|
||
[ '%s', '%s', '%s', '%s' ],
|
||
[ '%d' ]
|
||
);
|
||
return true;
|
||
}
|
||
|
||
/**
|
||
* Hebt abgelaufene Sperren auf — läuft per WP-Cron täglich.
|
||
* Gibt Anzahl entsperrter User zurück.
|
||
*/
|
||
public static function check_expired_bans() {
|
||
global $wpdb;
|
||
$expired = $wpdb->get_results(
|
||
"SELECT id, pre_ban_role FROM {$wpdb->prefix}forum_users
|
||
WHERE role = 'banned'
|
||
AND ban_until IS NOT NULL
|
||
AND ban_until <= NOW()"
|
||
);
|
||
$count = 0;
|
||
foreach ( $expired as $u ) {
|
||
$restore = ! empty( $u->pre_ban_role ) ? $u->pre_ban_role : 'member';
|
||
$wpdb->update(
|
||
"{$wpdb->prefix}forum_users",
|
||
[ 'role' => $restore, 'ban_reason' => '', 'ban_until' => null, 'pre_ban_role' => '' ],
|
||
[ 'id' => (int) $u->id ],
|
||
[ '%s', '%s', null, '%s' ],
|
||
[ '%d' ]
|
||
);
|
||
$count++;
|
||
}
|
||
return $count;
|
||
}
|
||
|
||
// ── Umfragen (Polls) ──────────────────────────────────────────────────────
|
||
|
||
public static function create_poll( $thread_id, $question, $options, $multi = false, $ends_at = null ) {
|
||
global $wpdb;
|
||
$wpdb->insert( "{$wpdb->prefix}forum_polls", [
|
||
'thread_id' => (int) $thread_id,
|
||
'question' => sanitize_text_field( $question ),
|
||
'options' => wp_json_encode( array_values( $options ) ),
|
||
'multi' => $multi ? 1 : 0,
|
||
'ends_at' => $ends_at,
|
||
]);
|
||
return $wpdb->insert_id;
|
||
}
|
||
|
||
public static function get_poll( $thread_id ) {
|
||
global $wpdb;
|
||
$row = $wpdb->get_row( $wpdb->prepare(
|
||
"SELECT * FROM {$wpdb->prefix}forum_polls WHERE thread_id = %d", (int) $thread_id
|
||
) );
|
||
if ( ! $row ) return null;
|
||
$row->options = json_decode( $row->options, true ) ?: [];
|
||
return $row;
|
||
}
|
||
|
||
public static function get_poll_results( $poll_id ) {
|
||
global $wpdb;
|
||
$rows = $wpdb->get_results( $wpdb->prepare(
|
||
"SELECT option_idx, COUNT(*) AS votes FROM {$wpdb->prefix}forum_poll_votes
|
||
WHERE poll_id = %d GROUP BY option_idx", (int) $poll_id
|
||
) );
|
||
$out = [];
|
||
foreach ( $rows as $r ) $out[(int)$r->option_idx] = (int)$r->votes;
|
||
return $out;
|
||
}
|
||
|
||
public static function get_user_votes( $poll_id, $user_id ) {
|
||
global $wpdb;
|
||
return array_map( fn($r) => (int)$r->option_idx,
|
||
$wpdb->get_results( $wpdb->prepare(
|
||
"SELECT option_idx FROM {$wpdb->prefix}forum_poll_votes WHERE poll_id=%d AND user_id=%d",
|
||
(int) $poll_id, (int) $user_id
|
||
) )
|
||
);
|
||
}
|
||
|
||
public static function vote_poll( $poll_id, $user_id, $option_idxs ) {
|
||
global $wpdb;
|
||
$poll = $wpdb->get_row( $wpdb->prepare(
|
||
"SELECT ends_at, multi FROM {$wpdb->prefix}forum_polls WHERE id=%d", (int) $poll_id
|
||
) );
|
||
if ( ! $poll ) return false;
|
||
if ( $poll->ends_at && strtotime( $poll->ends_at ) < time() ) return false;
|
||
if ( ! empty( self::get_user_votes( $poll_id, $user_id ) ) ) return false;
|
||
if ( ! $poll->multi ) $option_idxs = [ (int)$option_idxs[0] ];
|
||
foreach ( $option_idxs as $idx ) {
|
||
$wpdb->insert( "{$wpdb->prefix}forum_poll_votes", [
|
||
'poll_id' => (int)$poll_id, 'user_id' => (int)$user_id, 'option_idx' => (int)$idx,
|
||
]);
|
||
}
|
||
return true;
|
||
}
|
||
|
||
public static function delete_poll( $thread_id ) {
|
||
global $wpdb;
|
||
$poll = self::get_poll( $thread_id );
|
||
if ( ! $poll ) return;
|
||
$wpdb->delete( "{$wpdb->prefix}forum_poll_votes", [ 'poll_id' => $poll->id ] );
|
||
$wpdb->delete( "{$wpdb->prefix}forum_polls", [ 'id' => $poll->id ] );
|
||
}
|
||
|
||
// ── Thread-Präfixe ────────────────────────────────────────────────────────
|
||
|
||
public static function get_prefixes() {
|
||
global $wpdb;
|
||
return $wpdb->get_results(
|
||
"SELECT * FROM {$wpdb->prefix}forum_prefixes ORDER BY sort_order ASC, id ASC"
|
||
);
|
||
}
|
||
|
||
public static function get_prefix( $id ) {
|
||
global $wpdb;
|
||
return $wpdb->get_row( $wpdb->prepare(
|
||
"SELECT * FROM {$wpdb->prefix}forum_prefixes WHERE id=%d", (int)$id
|
||
));
|
||
}
|
||
|
||
public static function create_prefix( $data ) {
|
||
global $wpdb;
|
||
$wpdb->insert( "{$wpdb->prefix}forum_prefixes", $data );
|
||
return $wpdb->insert_id;
|
||
}
|
||
|
||
public static function update_prefix( $id, $data ) {
|
||
global $wpdb;
|
||
$wpdb->update( "{$wpdb->prefix}forum_prefixes", $data, ['id' => (int)$id] );
|
||
}
|
||
|
||
public static function delete_prefix( $id ) {
|
||
global $wpdb;
|
||
// Präfix bei betroffenen Threads entfernen
|
||
$wpdb->update( "{$wpdb->prefix}forum_threads", ['prefix_id' => null], ['prefix_id' => (int)$id] );
|
||
$wpdb->delete( "{$wpdb->prefix}forum_prefixes", ['id' => (int)$id] );
|
||
}
|
||
|
||
// ── Lesezeichen ───────────────────────────────────────────────────────────
|
||
|
||
public static function is_bookmarked( $user_id, $thread_id ) {
|
||
global $wpdb;
|
||
return (bool)$wpdb->get_var( $wpdb->prepare(
|
||
"SELECT id FROM {$wpdb->prefix}forum_bookmarks WHERE user_id=%d AND thread_id=%d",
|
||
(int)$user_id, (int)$thread_id
|
||
));
|
||
}
|
||
|
||
public static function toggle_bookmark( $user_id, $thread_id ) {
|
||
global $wpdb;
|
||
if ( self::is_bookmarked( $user_id, $thread_id ) ) {
|
||
$wpdb->delete( "{$wpdb->prefix}forum_bookmarks", [
|
||
'user_id' => (int)$user_id,
|
||
'thread_id' => (int)$thread_id,
|
||
]);
|
||
return false; // removed
|
||
}
|
||
$wpdb->insert( "{$wpdb->prefix}forum_bookmarks", [
|
||
'user_id' => (int)$user_id,
|
||
'thread_id' => (int)$thread_id,
|
||
]);
|
||
return true; // added
|
||
}
|
||
|
||
public static function get_user_bookmarks( $user_id, $limit = 50 ) {
|
||
global $wpdb;
|
||
return $wpdb->get_results( $wpdb->prepare(
|
||
"SELECT t.id, t.title, t.reply_count, t.views, t.created_at, t.last_reply_at,
|
||
t.prefix_id, t.status, t.pinned,
|
||
u.display_name, u.avatar_url, u.role as author_role,
|
||
c.name as cat_name, c.slug as cat_slug,
|
||
b.created_at as bookmarked_at
|
||
FROM {$wpdb->prefix}forum_bookmarks b
|
||
JOIN {$wpdb->prefix}forum_threads t ON t.id = b.thread_id
|
||
JOIN {$wpdb->prefix}forum_users u ON u.id = t.user_id
|
||
JOIN {$wpdb->prefix}forum_categories c ON c.id = t.category_id
|
||
WHERE b.user_id = %d AND t.deleted_at IS NULL
|
||
ORDER BY b.created_at DESC
|
||
LIMIT %d",
|
||
(int)$user_id, $limit
|
||
));
|
||
}
|
||
|
||
// ── Ignore-Liste ──────────────────────────────────────────────────────────
|
||
|
||
public static function toggle_ignore( $user_id, $ignored_id ) {
|
||
global $wpdb;
|
||
$user_id = (int) $user_id;
|
||
$ignored_id = (int) $ignored_id;
|
||
if ( self::is_ignored( $user_id, $ignored_id ) ) {
|
||
$wpdb->delete( "{$wpdb->prefix}forum_ignore_list", [
|
||
'user_id' => $user_id,
|
||
'ignored_id' => $ignored_id,
|
||
] );
|
||
return false;
|
||
}
|
||
$wpdb->replace( "{$wpdb->prefix}forum_ignore_list", [
|
||
'user_id' => $user_id,
|
||
'ignored_id' => $ignored_id,
|
||
] );
|
||
return true;
|
||
}
|
||
|
||
public static function is_ignored( $user_id, $ignored_id ) {
|
||
global $wpdb;
|
||
$table = "{$wpdb->prefix}forum_ignore_list";
|
||
if ( $wpdb->get_var( "SHOW TABLES LIKE '$table'" ) !== $table ) return false;
|
||
return (bool) $wpdb->get_var( $wpdb->prepare(
|
||
"SELECT id FROM {$wpdb->prefix}forum_ignore_list WHERE user_id=%d AND ignored_id=%d",
|
||
(int) $user_id, (int) $ignored_id
|
||
) );
|
||
}
|
||
|
||
/** Gibt alle ignorierten User-IDs als int-Array zurück */
|
||
public static function get_ignored_ids( $user_id ) {
|
||
global $wpdb;
|
||
$table = "{$wpdb->prefix}forum_ignore_list";
|
||
if ( $wpdb->get_var( "SHOW TABLES LIKE '$table'" ) !== $table ) return [];
|
||
$ids = $wpdb->get_col( $wpdb->prepare(
|
||
"SELECT ignored_id FROM {$wpdb->prefix}forum_ignore_list WHERE user_id=%d",
|
||
(int) $user_id
|
||
) );
|
||
return array_map( 'intval', $ids );
|
||
}
|
||
|
||
/** Vollständige Ignore-Liste mit User-Daten */
|
||
public static function get_ignore_list( $user_id ) {
|
||
global $wpdb;
|
||
$table = "{$wpdb->prefix}forum_ignore_list";
|
||
if ( $wpdb->get_var( "SHOW TABLES LIKE '$table'" ) !== $table ) return [];
|
||
return $wpdb->get_results( $wpdb->prepare(
|
||
"SELECT u.id, u.username, u.display_name, u.avatar_url, u.role,
|
||
il.created_at AS ignored_since
|
||
FROM {$wpdb->prefix}forum_ignore_list il
|
||
JOIN {$wpdb->prefix}forum_users u ON u.id = il.ignored_id
|
||
WHERE il.user_id = %d
|
||
ORDER BY il.created_at DESC",
|
||
(int) $user_id
|
||
) );
|
||
}
|
||
|
||
// ── DSGVO Art. 17: Konto vollständig löschen ──────────────────────────────
|
||
|
||
public static function delete_user_gdpr( $user_id ) {
|
||
global $wpdb;
|
||
$user_id = (int) $user_id;
|
||
$user = self::get_user( $user_id );
|
||
if ( ! $user ) return false;
|
||
if ( $user->role === 'superadmin' ) return false;
|
||
|
||
$wpdb->delete( "{$wpdb->prefix}forum_messages", [ 'from_id' => $user_id ] );
|
||
$wpdb->delete( "{$wpdb->prefix}forum_messages", [ 'to_id' => $user_id ] );
|
||
$wpdb->delete( "{$wpdb->prefix}forum_remember_tokens", [ 'user_id' => $user_id ] );
|
||
$wpdb->delete( "{$wpdb->prefix}forum_notifications", [ 'user_id' => $user_id ] );
|
||
$wpdb->delete( "{$wpdb->prefix}forum_notifications", [ 'actor_id' => $user_id ] );
|
||
$wpdb->delete( "{$wpdb->prefix}forum_subscriptions", [ 'user_id' => $user_id ] );
|
||
|
||
$table_bm = "{$wpdb->prefix}forum_bookmarks";
|
||
if ( $wpdb->get_var( "SHOW TABLES LIKE '$table_bm'" ) === $table_bm ) {
|
||
$wpdb->delete( $table_bm, [ 'user_id' => $user_id ] );
|
||
}
|
||
$wpdb->delete( "{$wpdb->prefix}forum_likes", [ 'user_id' => $user_id ] );
|
||
$wpdb->delete( "{$wpdb->prefix}forum_reactions", [ 'user_id' => $user_id ] );
|
||
$wpdb->delete( "{$wpdb->prefix}forum_reports", [ 'reporter_id' => $user_id ] );
|
||
|
||
$table_pv = "{$wpdb->prefix}forum_poll_votes";
|
||
if ( $wpdb->get_var( "SHOW TABLES LIKE '$table_pv'" ) === $table_pv ) {
|
||
$wpdb->delete( $table_pv, [ 'user_id' => $user_id ] );
|
||
}
|
||
|
||
// Ignore-Liste beidseitig bereinigen
|
||
$table_il = "{$wpdb->prefix}forum_ignore_list";
|
||
if ( $wpdb->get_var( "SHOW TABLES LIKE '$table_il'" ) === $table_il ) {
|
||
$wpdb->delete( $table_il, [ 'user_id' => $user_id ] );
|
||
$wpdb->delete( $table_il, [ 'ignored_id' => $user_id ] );
|
||
}
|
||
|
||
delete_transient( 'wbf_flood_' . $user_id );
|
||
delete_transient( 'wbf_flood_ts_' . $user_id );
|
||
|
||
self::delete_user_meta_all( $user_id );
|
||
|
||
$anon_hash = substr( hash( 'sha256', $user_id . wp_salt() . microtime( true ) ), 0, 12 );
|
||
$wpdb->update(
|
||
"{$wpdb->prefix}forum_users",
|
||
[
|
||
'username' => 'deleted_' . $anon_hash,
|
||
'email' => 'deleted_' . $anon_hash . '@deleted.invalid',
|
||
'password' => '',
|
||
'display_name' => 'Gelöschter Nutzer',
|
||
'avatar_url' => '',
|
||
'bio' => '',
|
||
'signature' => '',
|
||
'ban_reason' => '',
|
||
'reset_token' => null,
|
||
'reset_token_expires' => null,
|
||
'pre_ban_role' => '',
|
||
'ban_until' => null,
|
||
'role' => 'banned',
|
||
],
|
||
[ 'id' => $user_id ]
|
||
);
|
||
return true;
|
||
}
|
||
|
||
// ── Wortfilter ────────────────────────────────────────────────────────────
|
||
|
||
public static function get_word_filter() {
|
||
$raw = get_option( 'wbf_word_filter', '' );
|
||
if ( empty( $raw ) ) return [];
|
||
return array_values( array_filter( array_map( 'trim', explode( "\n", $raw ) ) ) );
|
||
}
|
||
|
||
public static function apply_word_filter( $text ) {
|
||
$words = self::get_word_filter();
|
||
if ( empty( $words ) ) return $text;
|
||
foreach ( $words as $word ) {
|
||
if ( empty($word) ) continue;
|
||
$replacement = str_repeat( '*', mb_strlen($word) );
|
||
$text = preg_replace( '/\b' . preg_quote($word, '/') . '\b/iu', $replacement, $text );
|
||
}
|
||
return $text;
|
||
}
|
||
|
||
// ── Flood Control ─────────────────────────────────────────────────────────
|
||
|
||
public static function check_flood( $user_id ) {
|
||
$user_id = (int) $user_id;
|
||
if ( $user_id <= 0 ) return true; // kein eingeloggter User — kein Flood-Check
|
||
$interval = (int)( wbf_get_settings()['flood_interval'] ?? 0 );
|
||
if ( $interval <= 0 ) return true; // deaktiviert
|
||
$key = 'wbf_flood_' . (int)$user_id;
|
||
$ts_key = 'wbf_flood_ts_' . (int)$user_id;
|
||
$last = get_transient( $key );
|
||
if ( $last !== false ) {
|
||
return false; // noch gesperrt
|
||
}
|
||
set_transient( $key, 1, $interval );
|
||
set_transient( $ts_key, time(), $interval + 5 );
|
||
return true;
|
||
}
|
||
|
||
public static function flood_remaining( $user_id ) {
|
||
$interval = (int)( wbf_get_settings()['flood_interval'] ?? 0 );
|
||
if ( $interval <= 0 ) return 0;
|
||
$ts_key = 'wbf_flood_ts_' . (int)$user_id;
|
||
$sent = get_transient( $ts_key );
|
||
if ( $sent === false ) return 0;
|
||
$remaining = $interval - ( time() - (int)$sent );
|
||
return max( 0, $remaining );
|
||
}
|
||
|
||
} |