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

2 files changed, 63 insertions, 0 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])
+}