feat: tags

7 files changed, 137 insertions, 15 deletions

diff --git a/database.sql b/database.sql
index 6ce0c96..5f3bfbc 100644
--- a/database.sql
+++ b/database.sql
@@ -30,6 +30,17 @@ CREATE TABLE IF NOT EXISTS emotes (
     visibility INTEGER NOT NULL
 );
 
+CREATE TABLE IF NOT EXISTS tags (
+    id CHAR(32) NOT NULL PRIMARY KEY DEFAULT REPLACE(UUID(),'-',''),
+    code TEXT NOT NULL UNIQUE
+);
+
+CREATE TABLE IF NOT EXISTS tag_assigns (
+    id CHAR(32) NOT NULL PRIMARY KEY DEFAULT REPLACE(UUID(),'-',''),
+    tag_id CHAR(32) NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
+    emote_id CHAR(32) NOT NULL REFERENCES emote(id) ON DELETE CASCADE
+);
+
 CREATE TABLE IF NOT EXISTS emote_sets (
     id CHAR(32) NOT NULL PRIMARY KEY DEFAULT REPLACE(UUID(),'-',''),
     owner_id CHAR(32) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
diff --git a/public/emotes/index.php b/public/emotes/index.php
index afa24ad..78828e1 100644
--- a/public/emotes/index.php
+++ b/public/emotes/index.php
@@ -36,18 +36,23 @@ function display_list_emotes(PDO &$db, string $search, string $sort_by, int $pag
     FROM emotes e
     LEFT JOIN user_preferences up ON up.id = e.uploaded_by
     LEFT JOIN ratings AS r ON r.emote_id = e.id
-    WHERE e.code LIKE ? AND e.visibility = 1
+    LEFT JOIN tag_assigns ta ON ta.emote_id = e.id
+    LEFT JOIN tags t ON t.id = ta.tag_id
+    WHERE (t.code = ? OR e.code LIKE ?) AND e.visibility = 1
     GROUP BY 
     e.id, e.code, e.created_at
     ORDER BY $sort
     LIMIT ? OFFSET ?
     ");
 
+    $sql_search = "%$search%";
+
     $stmt->bindParam(1, $current_user_id, PDO::PARAM_STR);
     $stmt->bindParam(2, $user_id, PDO::PARAM_INT);
     $stmt->bindParam(3, $search, PDO::PARAM_STR);
-    $stmt->bindParam(4, $limit, PDO::PARAM_INT);
-    $stmt->bindParam(5, $offset, PDO::PARAM_INT);
+    $stmt->bindParam(4, $sql_search, PDO::PARAM_STR);
+    $stmt->bindParam(5, $limit, PDO::PARAM_INT);
+    $stmt->bindParam(6, $offset, PDO::PARAM_INT);
 
     $stmt->execute();
 
@@ -78,7 +83,8 @@ function display_list_emotes(PDO &$db, string $search, string $sort_by, int $pag
             $row["is_in_user_set"],
             $row["rating"],
             $row["visibility"],
-            $row["source"]
+            $row["source"],
+            []
         ));
     }
 
@@ -100,6 +106,15 @@ function display_emote(PDO &$db, string $id)
 
     if ($row = $stmt->fetch()) {
         if ($row["id"] != null) {
+            $stmt = $db->prepare("SELECT t.code FROM tags t
+                INNER JOIN tag_assigns ta ON ta.emote_id = ?
+                WHERE t.id = ta.tag_id
+            ");
+            $stmt->execute([$row["id"]]);
+
+            $tags = $stmt->fetchAll(PDO::FETCH_ASSOC);
+            $tags = array_column($tags, "code");
+
             $emote = new Emote(
                 $row["id"],
                 $row["code"],
@@ -109,7 +124,8 @@ function display_emote(PDO &$db, string $id)
                 false,
                 ["total" => $row["total_rating"], "average" => $row["average_rating"]],
                 $row["visibility"],
-                $row["source"]
+                $row["source"],
+                $tags
             );
         }
     }
@@ -142,14 +158,12 @@ $page = max(1, intval($_GET["p"] ?? "1"));
 $limit = 50;
 $total_emotes = 0;
 $total_pages = 0;
-$search = "%" . ($_GET["q"] ?? "") . "%";
+$search = $_GET["q"] ?? "";
 $sort_by = $_GET["sort_by"] ?? "";
 
 if (empty($id)) {
     $emotes = display_list_emotes($db, $search, $sort_by, $page, $limit);
-    $stmt = $db->prepare("SELECT COUNT(*) FROM emotes WHERE code LIKE ? AND visibility = 1");
-    $stmt->execute([$search]);
-    $total_emotes = $stmt->fetch()[0];
+    $total_emotes = count($emotes);
     $total_pages = ceil($total_emotes / $limit);
 } else {
     $emote = display_emote($db, $id);
@@ -224,7 +238,7 @@ if (CLIENT_REQUIRES_JSON) {
                                 }
                                 echo '</div>';
                             } else {
-                                echo "$total_emotes Emotes - Page $page/$total_pages";
+                                echo "Emotes - Page $page/$total_pages";
                             }
                             ?>
                         </div>
@@ -352,6 +366,18 @@ if (CLIENT_REQUIRES_JSON) {
 
                         <section class="box">
                             <table class="vertical">
+                                <?php if (!empty($emote->get_tags())): ?>
+                                    <tr>
+                                        <th>Tags</th>
+                                        <td>
+                                            <?php
+                                            foreach ($emote->get_tags() as $tag) {
+                                                echo "<a href='/emotes/?q=$tag'>$tag</a> ";
+                                            }
+                                            ?>
+                                        </td>
+                                    </tr>
+                                <?php endif; ?>
                                 <tr>
                                     <th>Uploader</th>
                                     <td><?php
diff --git a/public/emotes/upload.php b/public/emotes/upload.php
index a9d416c..e4ff6cc 100644
--- a/public/emotes/upload.php
+++ b/public/emotes/upload.php
@@ -103,10 +103,25 @@ if ($_SERVER['REQUEST_METHOD'] != "POST") {
                                 <label for="notes">Approval notes</label>
                                 <textarea name="notes" id="form-notes"></textarea>
 
-                                <div>
-                                    <label class="inline" for="source">Emote source: </label>
-                                    <input name="source" id="form-source"></input>
-                                </div>
+                                <table class="vertical left font-weight-normal">
+                                    <tr>
+                                        <th>Emote source:</th>
+                                        <td class="flex"><input class="grow" name="source" id="form-source"></input></td>
+                                    </tr>
+                                    <?php if (TAGS_ENABLE && TAGS_MAX_COUNT != 0): ?>
+                                        <tr>
+                                            <th>Tags <span class="font-small" style="cursor: help;" title="<?php
+                                            echo 'Tags are used for fast search. ';
+                                            if (TAGS_MAX_COUNT > 0) {
+                                                echo 'You can use ' . TAGS_MAX_COUNT . ' tags. ';
+                                            }
+                                            echo 'They are space-separated o algo.';
+                                            ?>">[?]</span>:
+                                            </th>
+                                            <td class="flex"><input class="grow" name="tags" id="form-tags"></input></td>
+                                        </tr>
+                                    <?php endif; ?>
+                                </table>
 
                                 <div>
                                     <label for="tos" class="inline">Do you accept <a href="/rules.php" target="_BLANK">the
@@ -391,6 +406,40 @@ if ($is_manual) {
     }
 }
 
+$tags = str_safe($_POST["tags"] ?? "", null);
+
+if (!empty($tags) && TAGS_ENABLE) {
+    $tags = explode(" ", $tags);
+
+    $count = 0;
+
+    foreach ($tags as $tag) {
+        if (TAGS_MAX_COUNT > 0 && $count >= TAGS_MAX_COUNT) {
+            break;
+        }
+
+        if (!preg_match(TAGS_CODE_REGEX, $tag)) {
+            continue;
+        }
+
+        $tag_id = null;
+
+        $stmt = $db->prepare("SELECT id FROM tags WHERE code = ?");
+        $stmt->execute([$tag]);
+
+        if ($row = $stmt->fetch()) {
+            $tag_id = $row["id"];
+        } else {
+            $tag_id = bin2hex(random_bytes(16));
+            $db->prepare("INSERT INTO tags(id, code) VALUES (?, ?)")->execute([$tag_id, $tag]);
+        }
+
+        $db->prepare("INSERT INTO tag_assigns(tag_id, emote_id) VALUES (?, ?)")->execute([$tag_id, $id]);
+
+        $count++;
+    }
+}
+
 if (ACCOUNT_LOG_ACTIONS && $uploaded_by != null) {
     $db->prepare("INSERT INTO actions(user_id, action_type, action_payload) VALUES (?, ?, ?)")
         ->execute([
diff --git a/public/static/style.css b/public/static/style.css
index a53d128..218df1a 100644
--- a/public/static/style.css
+++ b/public/static/style.css
@@ -562,6 +562,11 @@ a.box:hover {
     width: 100%;
 }
 
+.font-weight-normal,
+.font-weight-normal th {
+    font-weight: normal;
+}
+
 /** 
 -------------
    USER
diff --git a/public/system/emotes/index.php b/public/system/emotes/index.php
index 9e88d83..eaabf4d 100644
--- a/public/system/emotes/index.php
+++ b/public/system/emotes/index.php
@@ -147,6 +147,24 @@ if (isset($_GET["id"])) {
                         <!-- Emote information -->
                         <section class="box">
                             <table class="vertical">
+                                <?php
+                                $stmt = $db->prepare("SELECT t.code FROM tags t
+                                    INNER JOIN tag_assigns ta ON ta.emote_id = ?
+                                    WHERE t.id = ta.tag_id
+                                ");
+                                $stmt->execute([$emote["id"]]);
+
+                                $tags = $stmt->fetchAll(PDO::FETCH_ASSOC);
+                                $tags = array_column($tags, "code");
+
+                                if (!empty($tags)) {
+                                    echo '<tr><th>Tags</th><td>';
+                                    foreach ($tags as $tag) {
+                                        echo "<a href='/emotes/?q=$tag'>$tag</a> ";
+                                    }
+                                    echo '</td></tr>';
+                                }
+                                ?>
                                 <tr>
                                     <th>Uploader</th>
                                     <td><?php
diff --git a/src/config.sample.php b/src/config.sample.php
index d6114b5..ed206f6 100644
--- a/src/config.sample.php
+++ b/src/config.sample.php
@@ -31,6 +31,11 @@ define("EMOTE_MAX_SIZE", [128, 128]); // Max size of emote.
 define("EMOTE_NAME_REGEX", "/^[A-Za-z0-9_]+$/"); // RegEx filter for emote names.
 define("EMOTE_STORE_ORIGINAL", true); // Store original uploads of emotes.
 
+// TAGS
+define("TAGS_ENABLE", true); // Allow emote tagging.
+define("TAGS_CODE_REGEX", "/^[A-Za-z0-9_]+$/");
+define("TAGS_MAX_COUNT", 10); // Maximum tags per emote. Set -1 for unlimited amount.
+
 // EMOTESETS
 define("EMOTESET_PUBLIC_LIST", true); // Show emotesets public.
 
diff --git a/src/emote.php b/src/emote.php
index 8e7da38..ce3b930 100644
--- a/src/emote.php
+++ b/src/emote.php
@@ -12,7 +12,9 @@ class Emote
 
     public string|null $source;
 
-    function __construct($id, $code, $ext, $created_at, $uploaded_by, $is_in_user_set, $rating, $visibility, $source)
+    public array $tags;
+
+    function __construct($id, $code, $ext, $created_at, $uploaded_by, $is_in_user_set, $rating, $visibility, $source, $tags)
     {
         $this->id = $id;
         $this->code = $code;
@@ -23,6 +25,7 @@ class Emote
         $this->rating = $rating;
         $this->visibility = $visibility;
         $this->source = $source;
+        $this->tags = $tags;
     }
 
     function get_id()
@@ -69,6 +72,11 @@ class Emote
     {
         return $this->source;
     }
+
+    function get_tags(): array
+    {
+        return $this->tags;
+    }
 }
 
 function html_random_emote(PDO &$db)