feat: show user badges in /emotes and /system/emotes

2 files changed, 34 insertions, 6 deletions

diff --git a/public/emotes/index.php b/public/emotes/index.php
index fb09b10..1225bdd 100644
--- a/public/emotes/index.php
+++ b/public/emotes/index.php
@@ -329,11 +329,14 @@ if (CLIENT_REQUIRES_JSON) {
                                     $username = ANONYMOUS_DEFAULT_NAME;
                                     $link = "#";
                                     $show_private_badge = false;
+                                    $badge = null;
 
                                     if ($emote->get_uploaded_by()) {
-                                        $stmt = $db->prepare("SELECT u.username, up.private_profile
+                                        $stmt = $db->prepare("SELECT u.username, up.private_profile, r.name AS role_name, r.badge_id AS role_badge_id
                                             FROM users u
                                             INNER JOIN user_preferences up ON up.id = u.id
+                                            LEFT JOIN role_assigns ra ON ra.user_id = u.id
+                                            LEFT JOIN roles r ON r.id = ra.role_id
                                             WHERE u.id = ?
                                         ");
                                         $stmt->execute([$emote->get_uploaded_by()]);
@@ -343,6 +346,7 @@ if (CLIENT_REQUIRES_JSON) {
 
                                             $username = $row["username"];
                                             $link = "/users.php?id=" . $emote->get_uploaded_by();
+                                            $badge = ["role_name" => $row["role_name"], "role_badge_id" => $row["role_badge_id"]];
                                         }
                                     }
 
@@ -354,20 +358,32 @@ if (CLIENT_REQUIRES_JSON) {
                                         echo " <img src='/static/img/icons/eye.png' alt='(Private)' title='You are the only one who sees this' />";
                                     }
 
+                                    if ($badge && $badge["role_badge_id"]) {
+                                        echo ' <img src="/static/userdata/badges/' . $badge["role_badge_id"] . '/1x.webp" alt="## ' . $badge["role_name"] . '" title="' . $badge["role_name"] . '" />';
+                                    }
+
                                     echo ', <span title="';
                                     echo date("M d, Y H:i:s", $emote->get_created_at());
                                     echo ' UTC">about ' . format_timestamp(time() - $emote->get_created_at()) . " ago</span>";
                                     ?></td>
                                 </tr>
                                 <?php
-                                $stmt = $db->prepare("SELECT u.id, u.username, a.created_at FROM users u
+                                $stmt = $db->prepare("SELECT u.id, u.username, a.created_at, r.name AS role_name, r.badge_id AS role_badge_id FROM users u
                                     INNER JOIN mod_actions a ON a.emote_id = ?
+                                    LEFT JOIN role_assigns ra ON ra.user_id = u.id
+                                    LEFT JOIN roles r ON r.id = ra.role_id
                                     WHERE u.id = a.user_id");
                                 $stmt->execute([$emote->get_id()]);
 
                                 if ($row = $stmt->fetch()) {
                                     echo '<tr><th>Approver</th><td>';
-                                    echo '<a href="/users.php?id=' . $row["id"] . '" target="_blank">' . $row["username"] . '</a>, <span title="';
+                                    echo '<a href="/users.php?id=' . $row["id"] . '" target="_blank">' . $row["username"] . '</a>';
+
+                                    if ($row["role_badge_id"]) {
+                                        echo ' <img src="/static/userdata/badges/' . $row["role_badge_id"] . '/1x.webp" alt="## ' . $row["role_name"] . '" title="' . $row["role_name"] . '" />';
+                                    }
+
+                                    echo ', <span title="';
                                     echo date("M d, Y H:i:s", strtotime($row["created_at"])) . ' UTC">';
                                     echo format_timestamp(strtotime($row["created_at"]) - $emote->get_created_at()) . ' after upload';
                                     echo '</span></td></tr>';
diff --git a/public/system/emotes/index.php b/public/system/emotes/index.php
index c80641c..3fc0b14 100644
--- a/public/system/emotes/index.php
+++ b/public/system/emotes/index.php
@@ -20,10 +20,14 @@ $current_user_id = $_SESSION["user_id"] ?? "";
 $db = new PDO(DB_URL, DB_USER, DB_PASS);
 $emote_results = $db->prepare("SELECT e.*,
 CASE WHEN up.private_profile = FALSE OR up.id = ? THEN e.uploaded_by ELSE NULL END AS uploaded_by,
-CASE WHEN up.private_profile = FALSE OR up.id = ? THEN u.username ELSE NULL END AS uploader_name
+CASE WHEN up.private_profile = FALSE OR up.id = ? THEN u.username ELSE NULL END AS uploader_name,
+r.name AS role_name,
+r.badge_id AS role_badge_id
 FROM emotes e
 LEFT JOIN users u ON u.id = e.uploaded_by
 LEFT JOIN user_preferences up ON up.id = u.id
+LEFT JOIN role_assigns ra ON ra.user_id = u.id
+LEFT JOIN roles r ON r.id = ra.role_id
 WHERE e.visibility = 2
 ORDER BY e.created_at DESC
 LIMIT 25
@@ -37,10 +41,14 @@ $emote = $emote_results[0] ?? null;
 if (isset($_GET["id"])) {
     $stmt = $db->prepare("SELECT e.*,
         CASE WHEN up.private_profile = FALSE OR up.id = ? THEN e.uploaded_by ELSE NULL END AS uploaded_by,
-        CASE WHEN up.private_profile = FALSE OR up.id = ? THEN u.username ELSE NULL END AS uploader_name
+        CASE WHEN up.private_profile = FALSE OR up.id = ? THEN u.username ELSE NULL END AS uploader_name,
+        r.name AS role_name,
+        r.badge_id AS role_badge_id
         FROM emotes e
-        LEFT JOIN user_preferences up ON up.id = u.id
         LEFT JOIN users u ON u.id = e.uploaded_by
+        LEFT JOIN user_preferences up ON up.id = u.id
+        LEFT JOIN role_assigns ra ON ra.user_id = u.id
+        LEFT JOIN roles r ON r.id = ra.role_id
         WHERE e.visibility = 2 AND e.id = ?
         LIMIT 1");
 
@@ -136,6 +144,10 @@ if (isset($_GET["id"])) {
                                     echo $username;
                                     echo "</a>";
 
+                                    if ($emote["role_badge_id"]) {
+                                        echo ' <img src="/static/userdata/badges/' . $emote["role_badge_id"] . '/1x.webp" alt="## ' . $emote["role_name"] . '" title="' . $emote["role_name"] . '" />';
+                                    }
+
                                     echo ', <span title="';
                                     echo date("M d, Y H:i:s", strtotime($emote["created_at"]));
                                     echo ' UTC">about ' . format_timestamp(time() - strtotime($emote["created_at"])) . " ago</span>";