feat(gateway:listeners): Add emoji and sticker usage tracking

6 files changed, 260 insertions, 3 deletions

diff --git a/packages/gateway/prisma/migrations/20251024070034_add_emoji_sticker_tracking/migration.sql b/packages/gateway/prisma/migrations/20251024070034_add_emoji_sticker_tracking/migration.sql
new file mode 100644
index 0000000..bc59a37
--- /dev/null
+++ b/packages/gateway/prisma/migrations/20251024070034_add_emoji_sticker_tracking/migration.sql
@@ -0,0 +1,37 @@
+-- CreateTable
+CREATE TABLE "EmojiUsage" (
+    "id" TEXT NOT NULL,
+    "guildId" TEXT NOT NULL,
+    "emojiId" TEXT NOT NULL,
+    "emojiName" TEXT NOT NULL,
+    "usageCount" INTEGER NOT NULL DEFAULT 1,
+    "firstUsed" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    "lastUsed" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
+
+    CONSTRAINT "EmojiUsage_pkey" PRIMARY KEY ("id")
+);
+
+-- CreateTable
+CREATE TABLE "StickerUsage" (
+    "id" TEXT NOT NULL,
+    "guildId" TEXT NOT NULL,
+    "stickerId" TEXT NOT NULL,
+    "stickerName" TEXT NOT NULL,
+    "usageCount" INTEGER NOT NULL DEFAULT 1,
+    "firstUsed" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    "lastUsed" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
+
+    CONSTRAINT "StickerUsage_pkey" PRIMARY KEY ("id")
+);
+
+-- CreateIndex
+CREATE INDEX "EmojiUsage_guildId_usageCount_idx" ON "EmojiUsage"("guildId", "usageCount");
+
+-- CreateIndex
+CREATE UNIQUE INDEX "EmojiUsage_guildId_emojiId_key" ON "EmojiUsage"("guildId", "emojiId");
+
+-- CreateIndex
+CREATE INDEX "StickerUsage_guildId_usageCount_idx" ON "StickerUsage"("guildId", "usageCount");
+
+-- CreateIndex
+CREATE UNIQUE INDEX "StickerUsage_guildId_stickerId_key" ON "StickerUsage"("guildId", "stickerId");
diff --git a/packages/gateway/prisma/schema.prisma b/packages/gateway/prisma/schema.prisma
index 922ca6f..3f69cf0 100644
--- a/packages/gateway/prisma/schema.prisma
+++ b/packages/gateway/prisma/schema.prisma
@@ -18,3 +18,29 @@ model UserRole {
   @@unique([userId, guildId, roleId])
   @@index([userId, guildId])
 }
+
+model EmojiUsage {
+  id        String   @id @default(cuid())
+  guildId   String
+  emojiId   String
+  emojiName String
+  usageCount Int     @default(1)
+  firstUsed DateTime @default(now())
+  lastUsed  DateTime @default(now())
+
+  @@unique([guildId, emojiId])
+  @@index([guildId, usageCount])
+}
+
+model StickerUsage {
+  id          String   @id @default(cuid())
+  guildId     String
+  stickerId   String
+  stickerName String
+  usageCount  Int      @default(1)
+  firstUsed   DateTime @default(now())
+  lastUsed    DateTime @default(now())
+
+  @@unique([guildId, stickerId])
+  @@index([guildId, usageCount])
+}
diff --git a/packages/gateway/src/database/emojiUsageTracker.ts b/packages/gateway/src/database/emojiUsageTracker.ts
new file mode 100644
index 0000000..604e8a6
--- /dev/null
+++ b/packages/gateway/src/database/emojiUsageTracker.ts
@@ -0,0 +1,178 @@
+import { Message } from "discord.js";
+import prisma from "./prisma";
+import { logUnexpectedDiscordAPIError } from "../utilities";
+
+export class EmojiUsageTracker {
+  static async trackEmojiUsage(message: Message): Promise<void> {
+    if (!message.guild) return;
+
+    try {
+      const guildId = message.guild.id;
+      const customEmojiRegex = /<a?:(\w+):(\d+)>/g;
+      const matches = message.content.matchAll(customEmojiRegex);
+
+      for (const match of matches) {
+        const emojiName = match[1];
+        const emojiId = match[2];
+        const emoji = message.guild.emojis.cache.get(emojiId);
+
+        if (!emoji) continue;
+
+        await this.recordEmojiUsage(guildId, emojiId, emojiName);
+      }
+
+      if (message.reactions.cache.size > 0)
+        for (const reaction of message.reactions.cache.values()) {
+          const emoji = reaction.emoji;
+
+          if (emoji.id && message.guild.emojis.cache.has(emoji.id))
+            await this.recordEmojiUsage(
+              guildId,
+              emoji.id,
+              emoji.name || "unknown",
+            );
+        }
+    } catch (error) {
+      logUnexpectedDiscordAPIError(error);
+    }
+  }
+
+  static async trackStickerUsage(message: Message): Promise<void> {
+    if (!message.guild) return;
+
+    try {
+      const guildId = message.guild.id;
+
+      for (const sticker of message.stickers.values())
+        if (sticker.guildId === guildId)
+          await this.recordStickerUsage(guildId, sticker.id, sticker.name);
+    } catch (error) {
+      logUnexpectedDiscordAPIError(error);
+    }
+  }
+
+  private static async recordEmojiUsage(
+    guildId: string,
+    emojiId: string,
+    emojiName: string,
+  ): Promise<void> {
+    try {
+      const existing = await prisma.emojiUsage.findUnique({
+        where: {
+          guildId_emojiId: {
+            guildId,
+            emojiId,
+          },
+        },
+      });
+
+      if (existing) {
+        await prisma.emojiUsage.update({
+          where: {
+            guildId_emojiId: {
+              guildId,
+              emojiId,
+            },
+          },
+          data: {
+            usageCount: existing.usageCount + 1,
+            lastUsed: new Date(),
+          },
+        });
+      } else {
+        await prisma.emojiUsage.create({
+          data: {
+            guildId,
+            emojiId,
+            emojiName,
+            usageCount: 1,
+            firstUsed: new Date(),
+            lastUsed: new Date(),
+          },
+        });
+      }
+    } catch (error) {
+      logUnexpectedDiscordAPIError(error);
+    }
+  }
+
+  private static async recordStickerUsage(
+    guildId: string,
+    stickerId: string,
+    stickerName: string,
+  ): Promise<void> {
+    try {
+      const existing = await prisma.stickerUsage.findUnique({
+        where: {
+          guildId_stickerId: {
+            guildId,
+            stickerId,
+          },
+        },
+      });
+
+      if (existing) {
+        await prisma.stickerUsage.update({
+          where: {
+            guildId_stickerId: {
+              guildId,
+              stickerId,
+            },
+          },
+          data: {
+            usageCount: existing.usageCount + 1,
+            lastUsed: new Date(),
+          },
+        });
+      } else {
+        await prisma.stickerUsage.create({
+          data: {
+            guildId,
+            stickerId,
+            stickerName,
+            usageCount: 1,
+            firstUsed: new Date(),
+            lastUsed: new Date(),
+          },
+        });
+      }
+    } catch (error) {
+      logUnexpectedDiscordAPIError(error);
+    }
+  }
+
+  static async getEmojiStats(guildId: string, limit: number = 50) {
+    try {
+      return await prisma.emojiUsage.findMany({
+        where: { guildId },
+        orderBy: { usageCount: "desc" },
+        take: limit,
+      });
+    } catch (error) {
+      logUnexpectedDiscordAPIError(error);
+
+      return [];
+    }
+  }
+
+  static async getStickerStats(guildId: string, limit: number = 50) {
+    try {
+      return await prisma.stickerUsage.findMany({
+        where: { guildId },
+        orderBy: { usageCount: "desc" },
+        take: limit,
+      });
+    } catch (error) {
+      logUnexpectedDiscordAPIError(error);
+
+      return [];
+    }
+  }
+
+  // static async cleanupDeletedAssets(guildId: string): Promise<void> {
+  //   try {
+  //   } catch (error) {
+  //     logUnexpectedDiscordAPIError(error);
+  //   }
+  // }
+}
diff --git a/packages/gateway/src/listeners/emojiUsageTracking.ts b/packages/gateway/src/listeners/emojiUsageTracking.ts
new file mode 100644
index 0000000..a6b01a1
--- /dev/null
+++ b/packages/gateway/src/listeners/emojiUsageTracking.ts
@@ -0,0 +1,16 @@
+import { Client, Events, Message } from "discord.js";
+import { EmojiUsageTracker } from "../database/emojiUsageTracker";
+import { logUnexpectedDiscordAPIError } from "../utilities";
+
+export const handleEmojiUsageTracking = (client: Client) => {
+  client.on(Events.MessageCreate, async (message: Message) => {
+    try {
+      if (message.author.bot || !message.guild) return;
+
+      await EmojiUsageTracker.trackEmojiUsage(message);
+      await EmojiUsageTracker.trackStickerUsage(message);
+    } catch (error) {
+      logUnexpectedDiscordAPIError(error);
+    }
+  });
+};
diff --git a/packages/gateway/src/listeners/index.ts b/packages/gateway/src/listeners/index.ts
index 21da227..15b617b 100644
--- a/packages/gateway/src/listeners/index.ts
+++ b/packages/gateway/src/listeners/index.ts
@@ -9,6 +9,7 @@ import { handleMemberJoin } from "./memberJoin";
 import { handleMemberLeave } from "./memberLeave";
 import { handleTimeoutMirroring } from "./timeoutMirroring";
 import { handleAutoDeletion } from "./autoDeletion";
+import { handleEmojiUsageTracking } from "./emojiUsageTracking";
 // import { handleMediaModeration } from "./mediaModeration";
 
 export const handleListeners = (client: Client) => {
@@ -22,5 +23,6 @@ export const handleListeners = (client: Client) => {
   handleMemberLeave(client);
   handleTimeoutMirroring(client);
   handleAutoDeletion(client);
+  handleEmojiUsageTracking(client);
   // handleMediaModeration(client);
 };
diff --git a/packages/gateway/src/listeners/memberLeave.ts b/packages/gateway/src/listeners/memberLeave.ts
index e348917..3fdef88 100644
--- a/packages/gateway/src/listeners/memberLeave.ts
+++ b/packages/gateway/src/listeners/memberLeave.ts
@@ -1,8 +1,6 @@
-/* eslint-disable @typescript-eslint/no-unused-vars, no-unused-vars */
-
 import { Client, Events, GuildMember } from "discord.js";
 import { RolePersistenceService } from "../database/rolePersistence";
-import { log, LogLevel, logUnexpectedDiscordAPIError } from "../utilities";
+import { logUnexpectedDiscordAPIError } from "../utilities";
 
 export const handleMemberLeave = (client: Client) => {
   client.on(Events.GuildMemberRemove, async (member) => {