feat: 2025 wrapped (#2322)

* feat: 2025 wrapped

* don't add wrapped for new users

3 files changed, 382 insertions, 1 deletions

diff --git a/packages/shared/types/users.ts b/packages/shared/types/users.ts
index 3ba56583..35db0e98 100644
--- a/packages/shared/types/users.ts
+++ b/packages/shared/types/users.ts
@@ -114,6 +114,73 @@ export const zUserStatsResponseSchema = z.object({
   ),
 });
 
+export const zWrappedStatsResponseSchema = z.object({
+  year: z.number(),
+  totalBookmarks: z.number(),
+  totalFavorites: z.number(),
+  totalArchived: z.number(),
+  totalHighlights: z.number(),
+  totalTags: z.number(),
+  totalLists: z.number(),
+
+  firstBookmark: z
+    .object({
+      id: z.string(),
+      title: z.string().nullable(),
+      createdAt: z.date(),
+      type: z.enum(["link", "text", "asset"]),
+    })
+    .nullable(),
+
+  mostActiveDay: z
+    .object({
+      date: z.string(),
+      count: z.number(),
+    })
+    .nullable(),
+
+  topDomains: z
+    .array(
+      z.object({
+        domain: z.string(),
+        count: z.number(),
+      }),
+    )
+    .max(5),
+
+  topTags: z
+    .array(
+      z.object({
+        name: z.string(),
+        count: z.number(),
+      }),
+    )
+    .max(5),
+
+  bookmarksByType: z.object({
+    link: z.number(),
+    text: z.number(),
+    asset: z.number(),
+  }),
+
+  bookmarksBySource: z.array(
+    z.object({
+      source: zBookmarkSourceSchema.nullable(),
+      count: z.number(),
+    }),
+  ),
+
+  monthlyActivity: z.array(
+    z.object({
+      month: z.number(),
+      count: z.number(),
+    }),
+  ),
+
+  peakHour: z.number(),
+  peakDayOfWeek: z.number(),
+});
+
 export const zReaderFontFamilySchema = z.enum(["serif", "sans", "mono"]);
 export type ZReaderFontFamily = z.infer<typeof zReaderFontFamilySchema>;
 
diff --git a/packages/trpc/models/users.ts b/packages/trpc/models/users.ts
index d8a84ffa..b1719200 100644
--- a/packages/trpc/models/users.ts
+++ b/packages/trpc/models/users.ts
@@ -1,6 +1,6 @@
 import { randomBytes } from "crypto";
 import { TRPCError } from "@trpc/server";
-import { and, count, desc, eq, gte, sql } from "drizzle-orm";
+import { and, count, desc, eq, gte, lte, sql } from "drizzle-orm";
 import invariant from "tiny-invariant";
 import { z } from "zod";
 
@@ -27,6 +27,7 @@ import {
   zUserSettingsSchema,
   zUserStatsResponseSchema,
   zWhoAmIResponseSchema,
+  zWrappedStatsResponseSchema,
 } from "@karakeep/shared/types/users";
 
 import { AuthedContext, Context } from "..";
@@ -870,6 +871,308 @@ export class User {
     };
   }
 
+  async hasWrapped(): Promise<boolean> {
+    const [{ numBookmarks }] = await this.ctx.db
+      .select({
+        numBookmarks: count(bookmarks.id),
+      })
+      .from(bookmarks)
+      .where(eq(bookmarks.userId, this.user.id));
+
+    return numBookmarks >= 20;
+  }
+
+  async getWrappedStats(
+    year: number,
+  ): Promise<z.infer<typeof zWrappedStatsResponseSchema>> {
+    const userObj = await this.ctx.db.query.users.findFirst({
+      where: eq(users.id, this.user.id),
+      columns: {
+        timezone: true,
+      },
+    });
+    const userTimezone = userObj?.timezone || "UTC";
+
+    // Define year range for 2025
+    const yearStart = new Date(`${year}-01-01T00:00:00Z`);
+    const yearEnd = new Date(`${year}-12-31T23:59:59Z`);
+
+    const yearFilter = and(
+      eq(bookmarks.userId, this.user.id),
+      gte(bookmarks.createdAt, yearStart),
+      lte(bookmarks.createdAt, yearEnd),
+    );
+
+    const [
+      [{ totalBookmarks }],
+      [{ totalFavorites }],
+      [{ totalArchived }],
+      [{ numTags }],
+      [{ numLists }],
+      [{ numHighlights }],
+      firstBookmarkResult,
+      bookmarksByType,
+      topDomains,
+      topTags,
+      bookmarksBySource,
+      bookmarkTimestamps,
+    ] = await Promise.all([
+      // Total bookmarks in year
+      this.ctx.db
+        .select({ totalBookmarks: count() })
+        .from(bookmarks)
+        .where(yearFilter),
+
+      // Total favorites in year
+      this.ctx.db
+        .select({ totalFavorites: count() })
+        .from(bookmarks)
+        .where(and(yearFilter, eq(bookmarks.favourited, true))),
+
+      // Total archived in year
+      this.ctx.db
+        .select({ totalArchived: count() })
+        .from(bookmarks)
+        .where(and(yearFilter, eq(bookmarks.archived, true))),
+
+      // Total unique tags (created in year)
+      this.ctx.db
+        .select({ numTags: count() })
+        .from(bookmarkTags)
+        .where(
+          and(
+            eq(bookmarkTags.userId, this.user.id),
+            gte(bookmarkTags.createdAt, yearStart),
+            lte(bookmarkTags.createdAt, yearEnd),
+          ),
+        ),
+
+      // Total lists (created in year)
+      this.ctx.db
+        .select({ numLists: count() })
+        .from(bookmarkLists)
+        .where(
+          and(
+            eq(bookmarkLists.userId, this.user.id),
+            gte(bookmarkLists.createdAt, yearStart),
+            lte(bookmarkLists.createdAt, yearEnd),
+          ),
+        ),
+
+      // Total highlights (created in year)
+      this.ctx.db
+        .select({ numHighlights: count() })
+        .from(highlights)
+        .where(
+          and(
+            eq(highlights.userId, this.user.id),
+            gte(highlights.createdAt, yearStart),
+            lte(highlights.createdAt, yearEnd),
+          ),
+        ),
+
+      // First bookmark of the year
+      this.ctx.db
+        .select({
+          id: bookmarks.id,
+          title: bookmarks.title,
+          createdAt: bookmarks.createdAt,
+          type: bookmarks.type,
+        })
+        .from(bookmarks)
+        .where(yearFilter)
+        .orderBy(bookmarks.createdAt)
+        .limit(1),
+
+      // Bookmarks by type
+      this.ctx.db
+        .select({
+          type: bookmarks.type,
+          count: count(),
+        })
+        .from(bookmarks)
+        .where(yearFilter)
+        .groupBy(bookmarks.type),
+
+      // Top 5 domains
+      this.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(yearFilter)
+        .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(5),
+
+      // Top 5 tags (used in bookmarks created this year)
+      this.ctx.db
+        .select({
+          name: bookmarkTags.name,
+          count: count(),
+        })
+        .from(bookmarkTags)
+        .innerJoin(tagsOnBookmarks, eq(tagsOnBookmarks.tagId, bookmarkTags.id))
+        .innerJoin(bookmarks, eq(bookmarks.id, tagsOnBookmarks.bookmarkId))
+        .where(yearFilter)
+        .groupBy(bookmarkTags.name)
+        .orderBy(desc(count()))
+        .limit(5),
+
+      // Bookmarks by source
+      this.ctx.db
+        .select({
+          source: bookmarks.source,
+          count: count(),
+        })
+        .from(bookmarks)
+        .where(yearFilter)
+        .groupBy(bookmarks.source)
+        .orderBy(desc(count())),
+
+      // All bookmark timestamps in the year for activity calculations
+      this.ctx.db
+        .select({
+          createdAt: bookmarks.createdAt,
+        })
+        .from(bookmarks)
+        .where(yearFilter),
+    ]);
+
+    // 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;
+      }
+    });
+
+    // Process timestamps with user timezone for hourly/daily activity
+    const hourCounts = Array.from({ length: 24 }, () => 0);
+    const dayCounts = Array.from({ length: 7 }, () => 0);
+    const monthCounts = Array.from({ length: 12 }, () => 0);
+    const dayCounts_full: Record<string, number> = {};
+
+    bookmarkTimestamps.forEach(({ createdAt }) => {
+      if (createdAt) {
+        const date = new Date(createdAt);
+        const userDate = new Date(
+          date.toLocaleString("en-US", { timeZone: userTimezone }),
+        );
+
+        const hour = userDate.getHours();
+        const day = userDate.getDay();
+        const month = userDate.getMonth();
+        const dateKey = userDate.toISOString().split("T")[0];
+
+        hourCounts[hour]++;
+        dayCounts[day]++;
+        monthCounts[month]++;
+        dayCounts_full[dateKey] = (dayCounts_full[dateKey] || 0) + 1;
+      }
+    });
+
+    // Find peak hour and day
+    const peakHour = hourCounts.indexOf(Math.max(...hourCounts));
+    const peakDayOfWeek = dayCounts.indexOf(Math.max(...dayCounts));
+
+    // Find most active day
+    let mostActiveDay: { date: string; count: number } | null = null;
+    if (Object.keys(dayCounts_full).length > 0) {
+      const sortedDays = Object.entries(dayCounts_full).sort(
+        ([, a], [, b]) => b - a,
+      );
+      mostActiveDay = {
+        date: sortedDays[0][0],
+        count: sortedDays[0][1],
+      };
+    }
+
+    // Monthly activity
+    const monthlyActivity = Array.from({ length: 12 }, (_, i) => ({
+      month: i + 1,
+      count: monthCounts[i],
+    }));
+
+    // First bookmark
+    const firstBookmark =
+      firstBookmarkResult.length > 0
+        ? {
+            id: firstBookmarkResult[0].id,
+            title: firstBookmarkResult[0].title,
+            createdAt: firstBookmarkResult[0].createdAt,
+            type: firstBookmarkResult[0].type,
+          }
+        : null;
+
+    return {
+      year,
+      totalBookmarks: totalBookmarks || 0,
+      totalFavorites: totalFavorites || 0,
+      totalArchived: totalArchived || 0,
+      totalHighlights: numHighlights || 0,
+      totalTags: numTags || 0,
+      totalLists: numLists || 0,
+      firstBookmark,
+      mostActiveDay,
+      topDomains: topDomains.filter((d) => d.domain && d.domain.length > 0),
+      topTags,
+      bookmarksByType: bookmarkTypeMap,
+      bookmarksBySource,
+      monthlyActivity,
+      peakHour,
+      peakDayOfWeek,
+    };
+  }
+
   asWhoAmI(): z.infer<typeof zWhoAmIResponseSchema> {
     return {
       id: this.user.id,
diff --git a/packages/trpc/routers/users.ts b/packages/trpc/routers/users.ts
index dbfbbc3c..71c23a39 100644
--- a/packages/trpc/routers/users.ts
+++ b/packages/trpc/routers/users.ts
@@ -9,6 +9,7 @@ import {
   zUserSettingsSchema,
   zUserStatsResponseSchema,
   zWhoAmIResponseSchema,
+  zWrappedStatsResponseSchema,
 } from "@karakeep/shared/types/users";
 
 import {
@@ -136,6 +137,16 @@ export const usersAppRouter = router({
       const user = await User.fromCtx(ctx);
       return await user.getStats();
     }),
+  wrapped: authedProcedure
+    .output(zWrappedStatsResponseSchema)
+    .query(async ({ ctx }) => {
+      const user = await User.fromCtx(ctx);
+      return await user.getWrappedStats(2025);
+    }),
+  hasWrapped: authedProcedure.output(z.boolean()).query(async ({ ctx }) => {
+    const user = await User.fromCtx(ctx);
+    return await user.hasWrapped();
+  }),
   settings: authedProcedure
     .output(zUserSettingsSchema)
     .query(async ({ ctx }) => {