feat: badges table

2 files changed, 13 insertions, 5 deletions

diff --git a/database.sql b/database.sql
index e995320..36beec8 100644
--- a/database.sql
+++ b/database.sql
@@ -81,13 +81,19 @@ CREATE TABLE IF NOT EXISTS reports (
     response_message TEXT
 );
 
+CREATE TABLE IF NOT EXISTS badges (
+    id CHAR(32) NOT NULL PRIMARY KEY DEFAULT REPLACE(UUID(),'-',''),
+    uploaded_by CHAR(32) REFERENCES users(id),
+    created_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP
+);
+
 CREATE TABLE IF NOT EXISTS roles (
     id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
     priority INTEGER NOT NULL DEFAULT 0,
     name TEXT NOT NULL,
     foreground_color TEXT NOT NULL DEFAULT '000,000,000',
     background_color TEXT NOT NULL DEFAULT 'solid:255,255,255',
-    badge_id INTEGER NOT NULL DEFAULT 0,
+    badge_id CHAR(32) REFERENCES badges(id),
 
     -- permissions
     permission_upload BOOLEAN NOT NULL DEFAULT true,
@@ -153,6 +159,8 @@ END$$
 DELIMITER ;
 
 -- NULLIFY EMOTE AUTHORS ON USER DELETION
+DROP TRIGGER IF EXISTS user_deletion;
+
 DELIMITER $$
 CREATE TRIGGER IF NOT EXISTS user_deletion
 BEFORE DELETE ON users
@@ -161,6 +169,7 @@ BEGIN
     UPDATE emotes SET uploaded_by = NULL WHERE uploaded_by = OLD.id;
     UPDATE emote_set_contents SET added_by = NULL WHERE added_by = OLD.id;
     UPDATE reports SET resolved_by = NULL WHERE resolved_by = OLD.id;
+    UPDATE badges SET uploaded_by = NULL WHERE uploaded_by = OLD.id;
 END$$
 DELIMITER ;
 
diff --git a/public/users.php b/public/users.php
index 1d803e7..3320451 100644
--- a/public/users.php
+++ b/public/users.php
@@ -344,7 +344,6 @@ if ($is_json) {
                     <!-- Role -->
                     <?php
                     if ($role) {
-                        $badge_path = sprintf("/%s/userdata/badges/%s/3x.webp", INSTANCE_STATIC_FOLDER, $role["badge_id"] ?? "");
                         $bg_color_split = explode(":", $role["background_color"]);
                         $bg_color = match ($bg_color_split[0]) {
                             "solid" => sprintf("background: rgba(%s);", $bg_color_split[1]),
@@ -353,11 +352,11 @@ if ($is_json) {
                             default => ""
                         };
 
-                        if (is_file($_SERVER["DOCUMENT_ROOT"] . $badge_path)): ?>
+                        if ($role["badge_id"]): ?>
                             <div class="box row small-gap items-center" style="<?php echo $bg_color; ?>">
                                 <div>
-                                    <img src="<?php echo $badge_path ?>" alt="<?php echo $role["name"] ?>" width="54"
-                                        height="54">
+                                    <img src="/static/userdata/badges/<?php echo $role["badge_id"] ?>/3x.webp"
+                                        alt="<?php echo $role["name"] ?>" width="54" height="54">
                                 </div>
                                 <div class="column">
                                     <p><?php echo $role["name"] ?></p>