upd: big changes in database.sql + .webp is now default image format

10 files changed, 59 insertions, 84 deletions

diff --git a/public/account/delete.php b/public/account/delete.php
index ecfcc80..4459edb 100644
--- a/public/account/delete.php
+++ b/public/account/delete.php
@@ -12,15 +12,7 @@ if (!isset($_SESSION["user_id"])) {
 $id = $_SESSION["user_id"];
 
 $db = new PDO(DB_URL, DB_USER, DB_PASS);
-
-$stmt = $db->prepare("UPDATE emotes SET uploaded_by = NULL WHERE uploaded_by = ?");
-$stmt->execute([$id]);
-
-$stmt = $db->prepare("DELETE FROM connections WHERE user_id = ?");
-$stmt->execute([$id]);
-
-$stmt = $db->prepare("DELETE FROM users WHERE id = ?");
-$stmt->execute([$id]);
+$db->prepare("DELETE FROM users WHERE id = ?")->execute([$id]);
 
 session_unset();
 session_destroy();
diff --git a/public/account/login/twitch.php b/public/account/login/twitch.php
index dfd319f..f322f42 100644
--- a/public/account/login/twitch.php
+++ b/public/account/login/twitch.php
@@ -69,9 +69,9 @@ if (empty($twitch_user["data"])) {
 $twitch_user = $twitch_user["data"][0];
 
 // saving it
-$_SESSION["twitch_access_token"] = $response["access_token"];
-$_SESSION["twitch_refresh_token"] = $response["refresh_token"];
-$_SESSION["twitch_expires_on"] = time() + intval($response["expires_in"]);
+$twitch_access_token = $response["access_token"];
+$twitch_refresh_token = $response["refresh_token"];
+$twitch_expires_on = time() + intval($response["expires_in"]);
 
 $db = new PDO(DB_URL, DB_USER, DB_PASS);
 
@@ -102,21 +102,21 @@ if ($row = $stmt->fetch()) {
 } else {
     $user_secret_key = generate_random_string(32);
     $user_name = $twitch_user["login"];
+    $user_id = bin2hex(random_bytes(16));
 
-    $stmt = $db->prepare("INSERT INTO users(username, secret_key) VALUES (?, ?)");
-    if (!$stmt->execute([$user_name, $user_secret_key])) {
+
+    $stmt = $db->prepare("INSERT INTO users(id, username, secret_key) VALUES (?, ?, ?)");
+    if (!$stmt->execute([$user_id, $user_name, $user_secret_key])) {
         $db = null;
         echo "Failed to create a user";
         exit;
     }
 
-    $user_id = $db->lastInsertId();
-
     $stmt = $db->prepare("INSERT INTO connections(user_id, alias_id, platform, data) VALUES (?, ?, 'twitch', ?)");
     $stmt->execute([
         $user_id,
         $twitch_user["id"],
-        $_SESSION["twitch_access_token"] . ":" . $_SESSION["twitch_refresh_token"] . ":" . $_SESSION["twitch_expires_on"]
+        sprintf("%s:%s:%s", $twitch_access_token, $twitch_refresh_token, $twitch_expires_on)
     ]);
 }
 
diff --git a/public/emotes/index.php b/public/emotes/index.php
index 3491ab8..2c2cff7 100644
--- a/public/emotes/index.php
+++ b/public/emotes/index.php
@@ -61,7 +61,7 @@ function display_list_emotes(PDO &$db, string $search, string $sort_by, int $pag
         array_push($emotes, new Emote(
             $row["id"],
             $row["code"],
-            $row["ext"],
+            "webp",
             intval(strtotime($row["created_at"])),
             $uploader,
             $row["is_in_user_set"],
@@ -73,7 +73,7 @@ function display_list_emotes(PDO &$db, string $search, string $sort_by, int $pag
     return $emotes;
 }
 
-function display_emote(PDO &$db, int $id)
+function display_emote(PDO &$db, string $id)
 {
     $stmt = $db->prepare("SELECT e.*, COALESCE(COUNT(r.rate), 0) as total_rating,
     COALESCE(ROUND(AVG(r.rate), 2), 0) AS average_rating
@@ -89,7 +89,7 @@ function display_emote(PDO &$db, int $id)
             $emote = new Emote(
                 $row["id"],
                 $row["code"],
-                $row["ext"],
+                "webp",
                 intval(strtotime($row["created_at"])),
                 $row["uploaded_by"],
                 false,
@@ -130,14 +130,14 @@ $total_pages = 0;
 $search = "%" . ($_GET["q"] ?? "") . "%";
 $sort_by = $_GET["sort_by"] ?? "";
 
-if ($id == "" || !is_numeric($id)) {
+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_pages = ceil($total_emotes / $limit);
 } else {
-    $emote = display_emote($db, intval($id));
+    $emote = display_emote($db, $id);
 }
 
 if (CLIENT_REQUIRES_JSON) {
@@ -180,14 +180,14 @@ if (CLIENT_REQUIRES_JSON) {
                             <?php echo $emote != null ? "Emote - " . $emote->get_code() : "$total_emotes Emotes - Page $page/$total_pages" ?>
                         </div>
                         <?php
-                        if (empty($emotes)) { ?>
+                        if ($emote != null) { ?>
                             <div class="box content">
                                 <div class="emote-showcase">
-                                    <img src="/static/userdata/emotes/<?php echo $emote->get_id() . '/' . '1x.' . $emote->get_ext() ?>"
+                                    <img src="/static/userdata/emotes/<?php echo $emote->get_id() ?>/1x.webp"
                                         alt="<?php echo $emote->get_code() ?>">
-                                    <img src="/static/userdata/emotes/<?php echo $emote->get_id() . '/' . '2x.' . $emote->get_ext() ?>"
+                                    <img src="/static/userdata/emotes/<?php echo $emote->get_id() ?>/2x.webp"
                                         alt="<?php echo $emote->get_code() ?>">
-                                    <img src="/static/userdata/emotes/<?php echo $emote->get_id() . '/' . '3x.' . $emote->get_ext() ?>"
+                                    <img src="/static/userdata/emotes/<?php echo $emote->get_id() ?>/3x.webp"
                                         alt="<?php echo $emote->get_code() ?>">
                                 </div>
                             </div>
@@ -200,14 +200,14 @@ if (CLIENT_REQUIRES_JSON) {
                                     $added = false;
 
                                     if (isset($_SESSION["user_emote_set_id"])) {
-                                        $stmt = $db->prepare("SELECT id, name FROM emote_set_contents WHERE emote_set_id = ? AND emote_id = ?");
+                                        $stmt = $db->prepare("SELECT id, code FROM emote_set_contents WHERE emote_set_id = ? AND emote_id = ?");
                                         $stmt->execute([$_SESSION["user_emote_set_id"], $emote->get_id()]);
 
                                         $added = false;
 
                                         if ($row = $stmt->fetch()) {
                                             $added = true;
-                                            $emote_current_name = $row["name"] ?? $emote->get_code();
+                                            $emote_current_name = $row["code"] ?? $emote->get_code();
                                         }
                                     }
 
@@ -419,7 +419,7 @@ if (CLIENT_REQUIRES_JSON) {
                                         echo '<img src="/static/img/icons/yes.png" class="emote-check" />';
                                     }
 
-                                    echo '<img src="/static/userdata/emotes/' . $e->get_id() . '/2x.' . $e->get_ext() . '" alt="' . $e->get_code() . '"/>';
+                                    echo '<img src="/static/userdata/emotes/' . $e->get_id() . '/2x.webp" alt="' . $e->get_code() . '"/>';
                                     echo '<h1>' . $e->get_code() . '</h1>';
                                     echo '<p>' . ($e->get_uploaded_by() == null ? (ANONYMOUS_DEFAULT_NAME . "*") : $e->get_uploaded_by()["username"]) . '</p>';
                                     echo '</a>';
diff --git a/public/emotes/rate.php b/public/emotes/rate.php
index 2f63d7c..1e8eb67 100644
--- a/public/emotes/rate.php
+++ b/public/emotes/rate.php
@@ -18,7 +18,7 @@ if (isset($_SESSION["user_role"]) && !$_SESSION["user_role"]["permission_rate"])
     exit;
 }
 
-$id = intval(str_safe($_POST["id"] ?? "0", 10));
+$id = str_safe($_POST["id"] ?? "0", 32);
 $rate = intval(str_safe($_POST["rate"] ?? "0", 2));
 
 if ($id == 0 || $rate == 0) {
diff --git a/public/emotes/setmanip.php b/public/emotes/setmanip.php
index 8b43b54..8b0f085 100644
--- a/public/emotes/setmanip.php
+++ b/public/emotes/setmanip.php
@@ -109,7 +109,7 @@ switch ($action) {
             $value = null;
         }
 
-        $stmt = $db->prepare("UPDATE emote_set_contents SET name = ? WHERE emote_set_id = ? AND emote_id = ?");
+        $stmt = $db->prepare("UPDATE emote_set_contents SET code = ? WHERE emote_set_id = ? AND emote_id = ?");
         $stmt->execute([$value, $emote_set_id, $emote_id]);
 
         $db = null;
diff --git a/public/emotes/upload.php b/public/emotes/upload.php
index 137e29b..89abf44 100644
--- a/public/emotes/upload.php
+++ b/public/emotes/upload.php
@@ -225,6 +225,11 @@ if (is_null(list($mime, $ext) = get_mime_and_ext($image["tmp_name"]))) {
     exit;
 }
 
+$notes = str_safe($_POST["notes"] ?? "", EMOTE_COMMENT_MAX_LENGTH);
+if (empty($notes)) {
+    $notes = null;
+}
+
 $visibility = clamp(intval($_POST["visibility"], EMOTE_VISIBILITY_DEFAULT), 0, 2);
 
 if (MOD_EMOTES_APPROVE && $visibility == 1 && EMOTE_VISIBILITY_DEFAULT != 1) {
@@ -234,21 +239,9 @@ if (MOD_EMOTES_APPROVE && $visibility == 1 && EMOTE_VISIBILITY_DEFAULT != 1) {
 // creating a new emote record
 $db = new PDO(DB_URL, DB_USER, DB_PASS);
 
-$stmt = $db->prepare("INSERT INTO emotes(code, mime, ext, uploaded_by, visibility) VALUES (?, ?, ?, ?, ?)");
-$stmt->execute([$code, $mime, $ext, $uploaded_by, $visibility]);
-
-$id = $db->lastInsertId();
-
-if ($id == 0) {
-    $db = null;
-    http_response_code(500);
-    echo json_encode([
-        "status_code" => 500,
-        "message" => "Failed to create an emote record",
-        "data" => null
-    ]);
-    exit;
-}
+$id = bin2hex(random_bytes(16));
+$stmt = $db->prepare("INSERT INTO emotes(id, code, notes, uploaded_by, visibility) VALUES (?, ?, ?, ?, ?)");
+$stmt->execute([$id, $code, $notes, $uploaded_by, $visibility]);
 
 $path = "../static/userdata/emotes/$id";
 
diff --git a/public/emotesets.php b/public/emotesets.php
index 61a8312..5405f52 100644
--- a/public/emotesets.php
+++ b/public/emotesets.php
@@ -27,11 +27,11 @@ if ($id == "global") {
         $stmt = $db->prepare("SELECT 
         e.*, 
         CASE 
-            WHEN esc.name IS NOT NULL THEN esc.name 
+            WHEN esc.code IS NOT NULL THEN esc.code 
             ELSE e.code
         END AS code,
         CASE 
-            WHEN esc.name IS NOT NULL THEN e.code 
+            WHEN esc.code IS NOT NULL THEN e.code 
             ELSE NULL 
         END AS original_code
         FROM emotes e
@@ -63,11 +63,11 @@ if ($id == "global") {
     foreach ($emote_sets as &$e) {
         $stmt = $db->prepare("SELECT e.*, 
         CASE 
-            WHEN esc.name IS NOT NULL THEN esc.name 
+            WHEN esc.code IS NOT NULL THEN esc.code 
             ELSE e.code
         END AS code,
         CASE 
-            WHEN esc.name IS NOT NULL THEN e.code 
+            WHEN esc.code IS NOT NULL THEN e.code 
             ELSE NULL 
         END AS original_code
         FROM emotes e
@@ -101,11 +101,11 @@ if ($id == "global") {
 
         $stmt = $db->prepare("SELECT e.*, 
         CASE 
-            WHEN esc.name IS NOT NULL THEN esc.name 
+            WHEN esc.code IS NOT NULL THEN esc.code 
             ELSE e.code
         END AS code,
         CASE 
-            WHEN esc.name IS NOT NULL THEN e.code 
+            WHEN esc.code IS NOT NULL THEN e.code 
             ELSE NULL 
         END AS original_code
         FROM emotes e
@@ -124,7 +124,6 @@ if ($id == "global") {
         }
     }
 } else {
-    $id = intval($id);
     $stmt = $db->prepare("SELECT * FROM emote_sets WHERE id = ?");
     $stmt->execute([$id]);
 
@@ -133,11 +132,11 @@ if ($id == "global") {
 
         $stmt = $db->prepare("SELECT e.*, 
         CASE 
-            WHEN esc.name IS NOT NULL THEN esc.name 
+            WHEN esc.code IS NOT NULL THEN esc.code 
             ELSE e.code
         END AS code,
         CASE 
-            WHEN esc.name IS NOT NULL THEN e.code 
+            WHEN esc.code IS NOT NULL THEN e.code 
             ELSE NULL 
         END AS original_code
         FROM emotes e
@@ -213,17 +212,13 @@ if (CLIENT_REQUIRES_JSON) {
                                         <div>
                                             <?php
                                             echo '<p>' . $set_row["name"] . '</p>';
-
-                                            if ($set_row["size"]) {
-                                                echo '<p class="circled black">' . $set_row["size"] . '</p>';
-                                            }
                                             ?>
                                         </div>
 
                                         <div>
                                             <?php
                                             foreach ($set_row["emotes"] as $emm) {
-                                                echo '<img src="/static/userdata/emotes/' . $emm["id"] . '/1x.' . $emm["ext"] . '">';
+                                                echo '<img src="/static/userdata/emotes/' . $emm["id"] . '/1x.webp">';
                                             }
                                             ?>
                                         </div>
@@ -243,7 +238,7 @@ if (CLIENT_REQUIRES_JSON) {
                             } else if (!empty($emote_set)) {
                                 foreach ($emote_set["emotes"] as $emote_row) {
                                     echo '<a class="box emote" href="/emotes?id=' . $emote_row["id"] . '">';
-                                    echo '<img src="/static/userdata/emotes/' . $emote_row["id"] . '/2x.' . $emote_row["ext"] . '" alt="' . $emote_row["code"] . '"/>';
+                                    echo '<img src="/static/userdata/emotes/' . $emote_row["id"] . '/2x.webp" alt="' . $emote_row["code"] . '"/>';
                                     echo '<h1>' . $emote_row["code"] . '</h1>';
                                     echo '<p>' . ($emote_row["uploaded_by"] == null ? (ANONYMOUS_DEFAULT_NAME . "*") : $emote_row["uploaded_by"]["username"]) . '</p>';
                                     echo '</a>';
diff --git a/public/static/img/defaults/profile_picture.png b/public/static/img/defaults/profile_picture.png
new file mode 100644
index 0000000..caaab1a
--- /dev/null
+++ b/public/static/img/defaults/profile_picture.png
diff --git a/public/system/emotes/index.php b/public/system/emotes/index.php
index 713957e..98d56b8 100644
--- a/public/system/emotes/index.php
+++ b/public/system/emotes/index.php
@@ -60,7 +60,7 @@ if ($emote_id > 0) {
                         <?php
                         foreach ($emote_results as $row) {
                             echo '<a href="/system/emotes?id=' . $row["id"] . '">';
-                            echo '<img src="/static/userdata/emotes/' . $row["id"] . '/1x.' . $row["ext"] . '">';
+                            echo '<img src="/static/userdata/emotes/' . $row["id"] . '/1x.webp">';
                             echo '<b>' . $row["code"] . '</b>';
                             echo '<span style="font-size:10px;"> by ';
 
@@ -86,11 +86,11 @@ if ($emote_id > 0) {
                             <div class="box navtab">Emote - <?php echo $emote["code"] ?></div>
                             <div class="box content">
                                 <div class="emote-showcase">
-                                    <img src="/static/userdata/emotes/<?php echo $emote["id"] . '/' . '1x.' . $emote["ext"] ?>"
+                                    <img src="/static/userdata/emotes/<?php echo $emote["id"] ?>/1x.webp"
                                         alt="<?php echo $emote["id"] ?>">
-                                    <img src="/static/userdata/emotes/<?php echo $emote["id"] . '/' . '2x.' . $emote["ext"] ?>"
+                                    <img src="/static/userdata/emotes/<?php echo $emote["id"] ?>/2x.webp"
                                         alt="<?php echo $emote["id"] ?>">
-                                    <img src="/static/userdata/emotes/<?php echo $emote["id"] . '/' . '3x.' . $emote["ext"] ?>"
+                                    <img src="/static/userdata/emotes/<?php echo $emote["id"] ?>/3x.webp"
                                         alt="<?php echo $emote["id"] ?>">
                                 </div>
                             </div>
diff --git a/public/users.php b/public/users.php
index baebcb4..1fbc6de 100644
--- a/public/users.php
+++ b/public/users.php
@@ -124,13 +124,13 @@ $stmt = null;
 
 if ($id != "") {
     $stmt = $db->prepare("SELECT * FROM users WHERE id = ?");
-    $stmt->execute([intval($id)]);
+    $stmt->execute([$id]);
 } else if ($alias_id != "") {
     $stmt = $db->prepare("SELECT u.* FROM users u
     INNER JOIN connections co ON (co.alias_id = ? AND co.platform = 'twitch')
     WHERE co.user_id = u.id
     ");
-    $stmt->execute([intval($alias_id)]);
+    $stmt->execute([$alias_id]);
 }
 
 $user = null;
@@ -155,19 +155,19 @@ $stmt->execute([$user->id()]);
 
 while ($row = $stmt->fetch()) {
     // getting more info about set
-    $set_stmt = $db->prepare("SELECT id, name, size FROM emote_sets WHERE id = ?");
+    $set_stmt = $db->prepare("SELECT id, name FROM emote_sets WHERE id = ?");
     $set_stmt->execute([$row["emote_set_id"]]);
     $set = $set_stmt->fetch();
 
     // getting info about emote set content
     $em_stmt = $db->prepare(
-        "SELECT e.id, e.mime, e.ext, e.created_at, e.uploaded_by, 
+        "SELECT e.id, e.created_at, e.uploaded_by, 
         CASE 
-            WHEN esc.name IS NOT NULL THEN esc.name 
+            WHEN esc.code IS NOT NULL THEN esc.code 
             ELSE e.code
         END AS code,
         CASE 
-            WHEN esc.name IS NOT NULL THEN e.code 
+            WHEN esc.code IS NOT NULL THEN e.code 
             ELSE NULL 
         END AS original_code
         FROM emotes e
@@ -190,7 +190,6 @@ while ($row = $stmt->fetch()) {
     $emote_set = [
         "id" => $set["id"],
         "name" => $set["name"],
-        "size" => $set["size"],
         "emotes" => $emote_set_emotes
     ];
 
@@ -285,7 +284,7 @@ if ($is_json) {
         "status_code" => 200,
         "message" => null,
         "data" => [
-            "id" => intval($user->id()),
+            "id" => $user->id(),
             "username" => $user->username(),
             "joined_at" => $user->joined_at(),
             "last_active_at" => $user->last_active_at(),
@@ -378,7 +377,7 @@ if ($is_json) {
                             }
                             ?>
                             <?php
-                            $stmt = $db->prepare("SELECT code, ext FROM emotes WHERE id = ?");
+                            $stmt = $db->prepare("SELECT code FROM emotes WHERE id = ?");
                             $stmt->execute([$fav_emote]);
 
                             if ($row = $stmt->fetch()) {
@@ -386,7 +385,7 @@ if ($is_json) {
                                 echo '<th><img src="/static/img/icons/heart.png"> Favorite emote</th>';
                                 echo '<td>';
                                 echo "<a href=\"/emotes?id=$fav_emote\">";
-                                echo $row["code"] . ' <img src="/static/userdata/emotes/' . $fav_emote . '/1x.' . $row["ext"] . '" width="16" height="16">';
+                                echo $row["code"] . ' <img src="/static/userdata/emotes/' . $fav_emote . '/1x.webp" width="16" height="16">';
                                 echo '</a></td></tr>';
                             }
                             ?>
@@ -398,7 +397,7 @@ if ($is_json) {
                         <a href="/message/send.php?user=<?php echo $user->id() ?>">Send a message</a>
                         <?php
                         if (isset($_SESSION["user_role"]) && $_SESSION["user_role"]["permission_report"]) {
-                            echo '<a href="/report?user_id=<?php echo $user->id() ?>">Report user</a>';
+                            echo '<a href="/report?user_id=' . $user->id() . '">Report user</a>';
                         }
                         ?>
                     </section>
@@ -415,14 +414,10 @@ if ($is_json) {
                             <?php
                             if (!empty($emote_sets)) {
                                 foreach ($emote_sets as $set_row) { ?>
-                                    <a href="/emotesets?id=<?php echo $set_row["id"] ?>" class="box">
+                                    <a href="/emotesets.php?id=<?php echo $set_row["id"] ?>" class="box">
                                         <div>
                                             <?php
                                             echo '<p>' . $set_row["name"] . '</p>';
-
-                                            if ($set_row["size"]) {
-                                                echo '<p class="circled black">' . $set_row["size"] . '</p>';
-                                            }
                                             ?>
                                         </div>
 
@@ -430,7 +425,7 @@ if ($is_json) {
                                             <?php
                                             for ($i = 0; $i < clamp(count($set_row["emotes"]), 0, 5); $i++) {
                                                 $e = &$set_row["emotes"][$i];
-                                                echo '<img src="/static/userdata/emotes/' . $e["id"] . '/1x.' . $e["ext"] . '">';
+                                                echo '<img src="/static/userdata/emotes/' . $e["id"] . '/1x.webp">';
                                             }
                                             ?>
                                         </div>
@@ -453,7 +448,7 @@ if ($is_json) {
                                 if (!empty($active_emote_set["emotes"])) {
                                     foreach ($active_emote_set["emotes"] as $emote_row) {
                                         echo '<a class="box emote" href="/emotes?id=' . $emote_row["id"] . '">';
-                                        echo '<img src="/static/userdata/emotes/' . $emote_row["id"] . '/2x.' . $emote_row["ext"] . '" alt="' . $emote_row["code"] . '"/>';
+                                        echo '<img src="/static/userdata/emotes/' . $emote_row["id"] . '/2x.webp" alt="' . $emote_row["code"] . '"/>';
                                         echo '<h1>' . $emote_row["code"] . '</h1>';
                                         echo '<p>' . ($emote_row["uploaded_by"] == null ? (ANONYMOUS_DEFAULT_NAME . "*") : $emote_row["uploaded_by"]["username"]) . '</p>';
                                         echo '</a>';
@@ -480,7 +475,7 @@ if ($is_json) {
                                 <?php
                                 foreach ($uploaded_emotes as $emote_row) {
                                     echo '<a class="box emote" href="/emotes?id=' . $emote_row["id"] . '">';
-                                    echo '<img src="/static/userdata/emotes/' . $emote_row["id"] . '/2x.' . $emote_row["ext"] . '" alt="' . $emote_row["code"] . '"/>';
+                                    echo '<img src="/static/userdata/emotes/' . $emote_row["id"] . '/2x.webp" alt="' . $emote_row["code"] . '"/>';
                                     echo '<h1>' . $emote_row["code"] . '</h1>';
                                     echo '</a>';
                                 }