feat: Add new user stats page. Fixes #1523

2 files changed, 552 insertions, 1 deletions

diff --git a/packages/trpc/routers/users.test.ts b/packages/trpc/routers/users.test.ts
index 3fd939b1..2f75c8ce 100644
--- a/packages/trpc/routers/users.test.ts
+++ b/packages/trpc/routers/users.test.ts
@@ -1,5 +1,8 @@
 import { assert, beforeEach, describe, expect, test } from "vitest";
 
+import { assets, AssetTypes, bookmarks } from "@karakeep/db/schema";
+import { BookmarkTypes } from "@karakeep/shared/types/bookmarks";
+
 import type { CustomTestContext } from "../testUtils";
 import { defaultBeforeEach, getApiCaller } from "../testUtils";
 
@@ -131,4 +134,343 @@ describe("User Routes", () => {
       /No settings provided/,
     );
   });
+
+  test<CustomTestContext>("user stats - empty user", async ({
+    db,
+    unauthedAPICaller,
+  }) => {
+    const user = await unauthedAPICaller.users.create({
+      name: "Test User",
+      email: "stats@test.com",
+      password: "pass1234",
+      confirmPassword: "pass1234",
+    });
+    const caller = getApiCaller(db, user.id);
+
+    const stats = await caller.users.stats();
+
+    // All stats should be zero for a new user
+    expect(stats.numBookmarks).toBe(0);
+    expect(stats.numFavorites).toBe(0);
+    expect(stats.numArchived).toBe(0);
+    expect(stats.numTags).toBe(0);
+    expect(stats.numLists).toBe(0);
+    expect(stats.numHighlights).toBe(0);
+    expect(stats.bookmarksByType).toEqual({ link: 0, text: 0, asset: 0 });
+    expect(stats.topDomains).toEqual([]);
+    expect(stats.totalAssetSize).toBe(0);
+    expect(stats.assetsByType).toEqual([]);
+    expect(stats.tagUsage).toEqual([]);
+    expect(stats.bookmarkingActivity.thisWeek).toBe(0);
+    expect(stats.bookmarkingActivity.thisMonth).toBe(0);
+    expect(stats.bookmarkingActivity.thisYear).toBe(0);
+    expect(stats.bookmarkingActivity.byHour).toHaveLength(24);
+    expect(stats.bookmarkingActivity.byDayOfWeek).toHaveLength(7);
+
+    // All hours and days should have 0 count
+    stats.bookmarkingActivity.byHour.forEach((hour, index) => {
+      expect(hour.hour).toBe(index);
+      expect(hour.count).toBe(0);
+    });
+    stats.bookmarkingActivity.byDayOfWeek.forEach((day, index) => {
+      expect(day.day).toBe(index);
+      expect(day.count).toBe(0);
+    });
+  });
+
+  test<CustomTestContext>("user stats - with data", async ({
+    db,
+    unauthedAPICaller,
+  }) => {
+    const user = await unauthedAPICaller.users.create({
+      name: "Test User",
+      email: "statsdata@test.com",
+      password: "pass1234",
+      confirmPassword: "pass1234",
+    });
+    const caller = getApiCaller(db, user.id);
+
+    // Create test bookmarks
+    const bookmark1 = await caller.bookmarks.createBookmark({
+      url: "https://example.com/page1",
+      type: BookmarkTypes.LINK,
+    });
+
+    const bookmark2 = await caller.bookmarks.createBookmark({
+      url: "https://google.com/search",
+      type: BookmarkTypes.LINK,
+    });
+
+    await caller.bookmarks.createBookmark({
+      text: "Test note content",
+      type: BookmarkTypes.TEXT,
+    });
+
+    // Create tags
+    const tag1 = await caller.tags.create({ name: "tech" });
+    const tag2 = await caller.tags.create({ name: "work" });
+
+    // Create lists
+    await caller.lists.create({
+      name: "Test List",
+      icon: "📚",
+      type: "manual",
+    });
+
+    // Archive one bookmark
+    await caller.bookmarks.updateBookmark({
+      bookmarkId: bookmark1.id,
+      archived: true,
+    });
+
+    // Favorite one bookmark
+    await caller.bookmarks.updateBookmark({
+      bookmarkId: bookmark2.id,
+      favourited: true,
+    });
+
+    // Add tags to bookmarks
+    await caller.bookmarks.updateTags({
+      bookmarkId: bookmark1.id,
+      attach: [{ tagId: tag1.id }],
+      detach: [],
+    });
+
+    await caller.bookmarks.updateTags({
+      bookmarkId: bookmark2.id,
+      attach: [{ tagId: tag1.id }, { tagId: tag2.id }],
+      detach: [],
+    });
+
+    // Create highlights
+    await caller.highlights.create({
+      bookmarkId: bookmark1.id,
+      startOffset: 0,
+      endOffset: 10,
+      text: "highlighted text",
+      note: "test note",
+    });
+
+    // Insert test assets directly into DB
+    await db.insert(assets).values([
+      {
+        id: "asset1",
+        assetType: AssetTypes.LINK_SCREENSHOT,
+        size: 1024,
+        contentType: "image/png",
+        bookmarkId: bookmark1.id,
+        userId: user.id,
+      },
+      {
+        id: "asset2",
+        assetType: AssetTypes.LINK_BANNER_IMAGE,
+        size: 2048,
+        contentType: "image/jpeg",
+        bookmarkId: bookmark2.id,
+        userId: user.id,
+      },
+    ]);
+
+    const stats = await caller.users.stats();
+
+    // Verify basic counts
+    expect(stats.numBookmarks).toBe(3);
+    expect(stats.numFavorites).toBe(1);
+    expect(stats.numArchived).toBe(1);
+    expect(stats.numTags).toBe(2);
+    expect(stats.numLists).toBe(1);
+    expect(stats.numHighlights).toBe(1);
+
+    // Verify bookmark types
+    expect(stats.bookmarksByType.link).toBe(2);
+    expect(stats.bookmarksByType.text).toBe(1);
+    expect(stats.bookmarksByType.asset).toBe(0);
+
+    // Verify top domains
+    expect(stats.topDomains).toHaveLength(2);
+    expect(
+      stats.topDomains.find((d) => d.domain === "example.com"),
+    ).toBeTruthy();
+    expect(
+      stats.topDomains.find((d) => d.domain === "google.com"),
+    ).toBeTruthy();
+
+    // Verify asset stats
+    expect(stats.totalAssetSize).toBe(3072); // 1024 + 2048
+    expect(stats.assetsByType).toHaveLength(2);
+
+    const screenshotAsset = stats.assetsByType.find(
+      (a) => a.type === AssetTypes.LINK_SCREENSHOT,
+    );
+    expect(screenshotAsset?.count).toBe(1);
+    expect(screenshotAsset?.totalSize).toBe(1024);
+
+    const bannerAsset = stats.assetsByType.find(
+      (a) => a.type === AssetTypes.LINK_BANNER_IMAGE,
+    );
+    expect(bannerAsset?.count).toBe(1);
+    expect(bannerAsset?.totalSize).toBe(2048);
+
+    // Verify tag usage
+    expect(stats.tagUsage).toHaveLength(2);
+    const techTag = stats.tagUsage.find((t) => t.name === "tech");
+    const workTag = stats.tagUsage.find((t) => t.name === "work");
+    expect(techTag?.count).toBe(2); // Used in 2 bookmarks
+    expect(workTag?.count).toBe(1); // Used in 1 bookmark
+
+    // Verify activity stats (should be > 0 since we just created bookmarks)
+    expect(stats.bookmarkingActivity.thisWeek).toBe(3);
+    expect(stats.bookmarkingActivity.thisMonth).toBe(3);
+    expect(stats.bookmarkingActivity.thisYear).toBe(3);
+
+    // Verify hour/day arrays are properly structured
+    expect(stats.bookmarkingActivity.byHour).toHaveLength(24);
+    expect(stats.bookmarkingActivity.byDayOfWeek).toHaveLength(7);
+  });
+
+  test<CustomTestContext>("user stats - privacy isolation", async ({
+    db,
+    unauthedAPICaller,
+  }) => {
+    // Create two users
+    const user1 = await unauthedAPICaller.users.create({
+      name: "User 1",
+      email: "user1@test.com",
+      password: "pass1234",
+      confirmPassword: "pass1234",
+    });
+
+    const user2 = await unauthedAPICaller.users.create({
+      name: "User 2",
+      email: "user2@test.com",
+      password: "pass1234",
+      confirmPassword: "pass1234",
+    });
+
+    const caller1 = getApiCaller(db, user1.id);
+    const caller2 = getApiCaller(db, user2.id);
+
+    // User 1 creates some bookmarks
+    const bookmark1 = await caller1.bookmarks.createBookmark({
+      url: "https://user1.com",
+      type: BookmarkTypes.LINK,
+    });
+
+    const tag1 = await caller1.tags.create({ name: "user1tag" });
+
+    // Attach tag to bookmark
+    await caller1.bookmarks.updateTags({
+      bookmarkId: bookmark1.id,
+      attach: [{ tagId: tag1.id }],
+      detach: [],
+    });
+
+    // User 2 creates different bookmarks
+    const bookmark2 = await caller2.bookmarks.createBookmark({
+      url: "https://user2.com",
+      type: BookmarkTypes.LINK,
+    });
+
+    const tag2 = await caller2.tags.create({ name: "user2tag" });
+
+    // Attach tag to bookmark
+    await caller2.bookmarks.updateTags({
+      bookmarkId: bookmark2.id,
+      attach: [{ tagId: tag2.id }],
+      detach: [],
+    });
+
+    // Get stats for both users
+    const stats1 = await caller1.users.stats();
+    const stats2 = await caller2.users.stats();
+
+    // Each user should only see their own data
+    expect(stats1.numBookmarks).toBe(1);
+    expect(stats1.numTags).toBe(1);
+    expect(stats1.topDomains[0]?.domain).toBe("user1.com");
+    expect(stats1.tagUsage[0]?.name).toBe("user1tag");
+
+    expect(stats2.numBookmarks).toBe(1);
+    expect(stats2.numTags).toBe(1);
+    expect(stats2.topDomains[0]?.domain).toBe("user2.com");
+    expect(stats2.tagUsage[0]?.name).toBe("user2tag");
+
+    // Users should not see each other's data
+    expect(stats1.topDomains.find((d) => d.domain === "user2.com")).toBeFalsy();
+    expect(stats2.topDomains.find((d) => d.domain === "user1.com")).toBeFalsy();
+  });
+
+  test<CustomTestContext>("user stats - activity time patterns", async ({
+    db,
+    unauthedAPICaller,
+  }) => {
+    const user = await unauthedAPICaller.users.create({
+      name: "Test User",
+      email: "timepatterns@test.com",
+      password: "pass1234",
+      confirmPassword: "pass1234",
+    });
+    const caller = getApiCaller(db, user.id);
+
+    // Create bookmarks with specific timestamps
+    const now = new Date();
+    const oneDayAgo = new Date(now.getTime() - 24 * 60 * 60 * 1000);
+    const oneWeekAgo = new Date(now.getTime() - 7 * 24 * 60 * 60 * 1000);
+    const oneMonthAgo = new Date(now.getTime() - 30 * 24 * 60 * 60 * 1000);
+
+    // Insert bookmarks directly with specific timestamps
+    await db
+      .insert(bookmarks)
+      .values([
+        {
+          userId: user.id,
+          type: BookmarkTypes.LINK,
+          createdAt: now,
+          archived: false,
+          favourited: false,
+        },
+        {
+          userId: user.id,
+          type: BookmarkTypes.LINK,
+          createdAt: oneDayAgo,
+          archived: false,
+          favourited: false,
+        },
+        {
+          userId: user.id,
+          type: BookmarkTypes.LINK,
+          createdAt: oneWeekAgo,
+          archived: false,
+          favourited: false,
+        },
+        {
+          userId: user.id,
+          type: BookmarkTypes.LINK,
+          createdAt: oneMonthAgo,
+          archived: false,
+          favourited: false,
+        },
+      ])
+      .returning();
+
+    const stats = await caller.users.stats();
+
+    // Verify activity counts based on time periods
+    expect(stats.bookmarkingActivity.thisWeek).toBeGreaterThanOrEqual(2); // now + oneDayAgo
+    expect(stats.bookmarkingActivity.thisMonth).toBeGreaterThanOrEqual(3); // now + oneDayAgo + oneWeekAgo
+    expect(stats.bookmarkingActivity.thisYear).toBe(4); // All bookmarks
+
+    // Verify that hour and day arrays have proper structure
+    expect(
+      stats.bookmarkingActivity.byHour.every(
+        (h) => typeof h.hour === "number" && h.hour >= 0 && h.hour <= 23,
+      ),
+    ).toBe(true);
+
+    expect(
+      stats.bookmarkingActivity.byDayOfWeek.every(
+        (d) => typeof d.day === "number" && d.day >= 0 && d.day <= 6,
+      ),
+    ).toBe(true);
+  });
 });
diff --git a/packages/trpc/routers/users.ts b/packages/trpc/routers/users.ts
index 33aac2b7..ea5e6944 100644
--- a/packages/trpc/routers/users.ts
+++ b/packages/trpc/routers/users.ts
@@ -1,14 +1,17 @@
 import { TRPCError } from "@trpc/server";
-import { and, count, eq } from "drizzle-orm";
+import { and, count, desc, eq, gte, sql } from "drizzle-orm";
 import invariant from "tiny-invariant";
 import { z } from "zod";
 
 import { SqliteError } from "@karakeep/db";
 import {
+  assets,
+  bookmarkLinks,
   bookmarkLists,
   bookmarks,
   bookmarkTags,
   highlights,
+  tagsOnBookmarks,
   users,
   userSettings,
 } from "@karakeep/db/schema";
@@ -223,6 +226,11 @@ export const usersAppRouter = router({
   stats: authedProcedure
     .output(zUserStatsResponseSchema)
     .query(async ({ ctx }) => {
+      const now = new Date();
+      const weekAgo = new Date(now.getTime() - 7 * 24 * 60 * 60 * 1000);
+      const monthAgo = new Date(now.getTime() - 30 * 24 * 60 * 60 * 1000);
+      const yearAgo = new Date(now.getTime() - 365 * 24 * 60 * 60 * 1000);
+
       const [
         [{ numBookmarks }],
         [{ numFavorites }],
@@ -230,7 +238,18 @@ export const usersAppRouter = router({
         [{ numTags }],
         [{ numLists }],
         [{ numHighlights }],
+        bookmarksByType,
+        topDomains,
+        [{ totalAssetSize }],
+        assetsByType,
+        [{ thisWeek }],
+        [{ thisMonth }],
+        [{ thisYear }],
+        bookmarkingByHour,
+        bookmarkingByDay,
+        tagUsage,
       ] = await Promise.all([
+        // Basic counts
         ctx.db
           .select({ numBookmarks: count() })
           .from(bookmarks)
@@ -265,7 +284,185 @@ export const usersAppRouter = router({
           .select({ numHighlights: count() })
           .from(highlights)
           .where(eq(highlights.userId, ctx.user.id)),
+
+        // Bookmarks by type
+        ctx.db
+          .select({
+            type: bookmarks.type,
+            count: count(),
+          })
+          .from(bookmarks)
+          .where(eq(bookmarks.userId, ctx.user.id))
+          .groupBy(bookmarks.type),
+
+        // Top domains
+        ctx.db
+          .select({
+            domain: sql<string>`CASE 
+              WHEN ${bookmarkLinks.url} LIKE 'https://%' THEN 
+                CASE 
+                  WHEN INSTR(SUBSTR(${bookmarkLinks.url}, 9), '/') > 0 THEN
+                    SUBSTR(${bookmarkLinks.url}, 9, INSTR(SUBSTR(${bookmarkLinks.url}, 9), '/') - 1)
+                  ELSE
+                    SUBSTR(${bookmarkLinks.url}, 9)
+                END
+              WHEN ${bookmarkLinks.url} LIKE 'http://%' THEN 
+                CASE 
+                  WHEN INSTR(SUBSTR(${bookmarkLinks.url}, 8), '/') > 0 THEN
+                    SUBSTR(${bookmarkLinks.url}, 8, INSTR(SUBSTR(${bookmarkLinks.url}, 8), '/') - 1)
+                  ELSE
+                    SUBSTR(${bookmarkLinks.url}, 8)
+                END
+              ELSE 
+                CASE 
+                  WHEN INSTR(${bookmarkLinks.url}, '/') > 0 THEN
+                    SUBSTR(${bookmarkLinks.url}, 1, INSTR(${bookmarkLinks.url}, '/') - 1)
+                  ELSE
+                    ${bookmarkLinks.url}
+                END
+            END`,
+            count: count(),
+          })
+          .from(bookmarkLinks)
+          .innerJoin(bookmarks, eq(bookmarks.id, bookmarkLinks.id))
+          .where(eq(bookmarks.userId, ctx.user.id))
+          .groupBy(
+            sql`CASE 
+            WHEN ${bookmarkLinks.url} LIKE 'https://%' THEN 
+              CASE 
+                WHEN INSTR(SUBSTR(${bookmarkLinks.url}, 9), '/') > 0 THEN
+                  SUBSTR(${bookmarkLinks.url}, 9, INSTR(SUBSTR(${bookmarkLinks.url}, 9), '/') - 1)
+                ELSE
+                  SUBSTR(${bookmarkLinks.url}, 9)
+              END
+            WHEN ${bookmarkLinks.url} LIKE 'http://%' THEN 
+              CASE 
+                WHEN INSTR(SUBSTR(${bookmarkLinks.url}, 8), '/') > 0 THEN
+                  SUBSTR(${bookmarkLinks.url}, 8, INSTR(SUBSTR(${bookmarkLinks.url}, 8), '/') - 1)
+                ELSE
+                  SUBSTR(${bookmarkLinks.url}, 8)
+              END
+            ELSE 
+              CASE 
+                WHEN INSTR(${bookmarkLinks.url}, '/') > 0 THEN
+                  SUBSTR(${bookmarkLinks.url}, 1, INSTR(${bookmarkLinks.url}, '/') - 1)
+                ELSE
+                  ${bookmarkLinks.url}
+              END
+          END`,
+          )
+          .orderBy(desc(count()))
+          .limit(10),
+
+        // Total asset size
+        ctx.db
+          .select({
+            totalAssetSize: sql<number>`COALESCE(SUM(${assets.size}), 0)`,
+          })
+          .from(assets)
+          .where(eq(assets.userId, ctx.user.id)),
+
+        // Assets by type
+        ctx.db
+          .select({
+            type: assets.assetType,
+            count: count(),
+            totalSize: sql<number>`COALESCE(SUM(${assets.size}), 0)`,
+          })
+          .from(assets)
+          .where(eq(assets.userId, ctx.user.id))
+          .groupBy(assets.assetType),
+
+        // Activity stats
+        ctx.db
+          .select({ thisWeek: count() })
+          .from(bookmarks)
+          .where(
+            and(
+              eq(bookmarks.userId, ctx.user.id),
+              gte(bookmarks.createdAt, weekAgo),
+            ),
+          ),
+        ctx.db
+          .select({ thisMonth: count() })
+          .from(bookmarks)
+          .where(
+            and(
+              eq(bookmarks.userId, ctx.user.id),
+              gte(bookmarks.createdAt, monthAgo),
+            ),
+          ),
+        ctx.db
+          .select({ thisYear: count() })
+          .from(bookmarks)
+          .where(
+            and(
+              eq(bookmarks.userId, ctx.user.id),
+              gte(bookmarks.createdAt, yearAgo),
+            ),
+          ),
+
+        // Bookmarking by hour (UTC time)
+        ctx.db
+          .select({
+            hour: sql<number>`CAST(strftime('%H', datetime(${bookmarks.createdAt} / 1000, 'unixepoch')) AS INTEGER)`,
+            count: count(),
+          })
+          .from(bookmarks)
+          .where(eq(bookmarks.userId, ctx.user.id))
+          .groupBy(
+            sql`strftime('%H', datetime(${bookmarks.createdAt} / 1000, 'unixepoch'))`,
+          ),
+
+        // Bookmarking by day of week (UTC time)
+        ctx.db
+          .select({
+            day: sql<number>`CAST(strftime('%w', datetime(${bookmarks.createdAt} / 1000, 'unixepoch')) AS INTEGER)`,
+            count: count(),
+          })
+          .from(bookmarks)
+          .where(eq(bookmarks.userId, ctx.user.id))
+          .groupBy(
+            sql`strftime('%w', datetime(${bookmarks.createdAt} / 1000, 'unixepoch'))`,
+          ),
+
+        // Tag usage
+        ctx.db
+          .select({
+            name: bookmarkTags.name,
+            count: count(),
+          })
+          .from(bookmarkTags)
+          .innerJoin(
+            tagsOnBookmarks,
+            eq(tagsOnBookmarks.tagId, bookmarkTags.id),
+          )
+          .where(eq(bookmarkTags.userId, ctx.user.id))
+          .groupBy(bookmarkTags.name)
+          .orderBy(desc(count()))
+          .limit(10),
       ]);
+
+      // Process bookmarks by type
+      const bookmarkTypeMap = { link: 0, text: 0, asset: 0 };
+      bookmarksByType.forEach((item) => {
+        if (item.type in bookmarkTypeMap) {
+          bookmarkTypeMap[item.type as keyof typeof bookmarkTypeMap] =
+            item.count;
+        }
+      });
+
+      // Fill missing hours and days with 0
+      const hourlyActivity = Array.from({ length: 24 }, (_, i) => ({
+        hour: i,
+        count: bookmarkingByHour.find((item) => item.hour === i)?.count || 0,
+      }));
+
+      const dailyActivity = Array.from({ length: 7 }, (_, i) => ({
+        day: i,
+        count: bookmarkingByDay.find((item) => item.day === i)?.count || 0,
+      }));
+
       return {
         numBookmarks,
         numFavorites,
@@ -273,6 +470,18 @@ export const usersAppRouter = router({
         numTags,
         numLists,
         numHighlights,
+        bookmarksByType: bookmarkTypeMap,
+        topDomains: topDomains.filter((d) => d.domain && d.domain.length > 0),
+        totalAssetSize: totalAssetSize || 0,
+        assetsByType,
+        bookmarkingActivity: {
+          thisWeek: thisWeek || 0,
+          thisMonth: thisMonth || 0,
+          thisYear: thisYear || 0,
+          byHour: hourlyActivity,
+          byDayOfWeek: dailyActivity,
+        },
+        tagUsage,
       };
     }),
   settings: authedProcedure