From 47624547f8cb352426d597537c11e7a4550aa91e Mon Sep 17 00:00:00 2001 From: Mohamed Bassem Date: Sun, 6 Jul 2025 14:31:58 +0000 Subject: feat: Add new user stats page. Fixes #1523 --- apps/web/app/settings/layout.tsx | 6 + apps/web/app/settings/stats/page.tsx | 495 ++++++++++++++++++++++++++ apps/web/lib/i18n/locales/en/translation.json | 3 + packages/shared/types/users.ts | 46 +++ packages/trpc/routers/users.test.ts | 342 ++++++++++++++++++ packages/trpc/routers/users.ts | 211 ++++++++++- 6 files changed, 1102 insertions(+), 1 deletion(-) create mode 100644 apps/web/app/settings/stats/page.tsx diff --git a/apps/web/app/settings/layout.tsx b/apps/web/app/settings/layout.tsx index 1f7c5c12..4ff3719f 100644 --- a/apps/web/app/settings/layout.tsx +++ b/apps/web/app/settings/layout.tsx @@ -6,6 +6,7 @@ import { api } from "@/server/api/client"; import { TFunction } from "i18next"; import { ArrowLeft, + BarChart3, Download, GitBranch, Image, @@ -34,6 +35,11 @@ const settingsSidebarItems = ( icon: , path: "/settings/info", }, + { + name: "Usage Statistics", + icon: , + path: "/settings/stats", + }, { name: t("settings.ai.ai_settings"), icon: , diff --git a/apps/web/app/settings/stats/page.tsx b/apps/web/app/settings/stats/page.tsx new file mode 100644 index 00000000..15608a34 --- /dev/null +++ b/apps/web/app/settings/stats/page.tsx @@ -0,0 +1,495 @@ +"use client"; + +import { useMemo } from "react"; +import { Badge } from "@/components/ui/badge"; +import { Card, CardContent, CardHeader, CardTitle } from "@/components/ui/card"; +import { Progress } from "@/components/ui/progress"; +import { Skeleton } from "@/components/ui/skeleton"; +import { api } from "@/lib/trpc"; +import { + Archive, + BarChart3, + BookOpen, + Clock, + Database, + FileText, + Globe, + Hash, + Heart, + Highlighter, + Image, + Link, + List, + TrendingUp, +} from "lucide-react"; + +function formatBytes(bytes: number): string { + if (bytes === 0) return "0 Bytes"; + const k = 1024; + const sizes = ["Bytes", "KB", "MB", "GB"]; + const i = Math.floor(Math.log(bytes) / Math.log(k)); + return parseFloat((bytes / Math.pow(k, i)).toFixed(2)) + " " + sizes[i]; +} + +function formatNumber(num: number): string { + if (num >= 1000000) { + return (num / 1000000).toFixed(1) + "M"; + } + if (num >= 1000) { + return (num / 1000).toFixed(1) + "K"; + } + return num.toString(); +} + +const dayNames = ["Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"]; +const hourLabels = Array.from({ length: 24 }, (_, i) => + i === 0 ? "12 AM" : i < 12 ? `${i} AM` : i === 12 ? "12 PM" : `${i - 12} PM`, +); + +function SimpleBarChart({ + data, + maxValue, + labels, +}: { + data: number[]; + maxValue: number; + labels: string[]; +}) { + return ( +
+ {data.map((value, index) => ( +
+
+ {labels[index]} +
+
+
0 ? (value / maxValue) * 100 : 0}%`, + }} + /> +
+
+ {value} +
+
+ ))} +
+ ); +} + +function StatCard({ + title, + value, + icon, + description, +}: { + title: string; + value: string | number; + icon: React.ReactNode; + description?: string; +}) { + return ( + + + {title} + {icon} + + +
{value}
+ {description && ( +

{description}

+ )} + + + ); +} + +export default function StatsPage() { + const { data: stats, isLoading } = api.users.stats.useQuery(); + + const maxHourlyActivity = useMemo(() => { + if (!stats) return 0; + return Math.max( + ...stats.bookmarkingActivity.byHour.map( + (h: { hour: number; count: number }) => h.count, + ), + ); + }, [stats]); + + const maxDailyActivity = useMemo(() => { + if (!stats) return 0; + return Math.max( + ...stats.bookmarkingActivity.byDayOfWeek.map( + (d: { day: number; count: number }) => d.count, + ), + ); + }, [stats]); + + if (isLoading) { + return ( +
+
+

Usage Statistics

+

+ Insights into your bookmarking habits and collection +

+
+ +
+ {Array.from({ length: 8 }).map((_, i) => ( + + + + + + + + + + ))} +
+
+ ); + } + + if (!stats) { + return ( +
+

Failed to load statistics

+
+ ); + } + + return ( +
+
+

Usage Statistics

+

+ Insights into your bookmarking habits and collection +

+
+ + {/* Overview Stats */} +
+ } + description="All saved items" + /> + } + description="Starred bookmarks" + /> + } + description="Archived items" + /> + } + description="Unique tags created" + /> + } + description="Bookmark collections" + /> + } + description="Text highlights" + /> + } + description="Total asset storage" + /> + } + description="Bookmarks added" + /> +
+ +
+ {/* Bookmark Types */} + + + + + Bookmark Types + + + +
+
+
+ + Links +
+ + {stats.bookmarksByType.link} + +
+ 0 + ? (stats.bookmarksByType.link / stats.numBookmarks) * 100 + : 0 + } + className="h-2" + /> +
+
+
+
+ + Text Notes +
+ + {stats.bookmarksByType.text} + +
+ 0 + ? (stats.bookmarksByType.text / stats.numBookmarks) * 100 + : 0 + } + className="h-2" + /> +
+
+
+
+ + Assets +
+ + {stats.bookmarksByType.asset} + +
+ 0 + ? (stats.bookmarksByType.asset / stats.numBookmarks) * 100 + : 0 + } + className="h-2" + /> +
+ + + + {/* Recent Activity */} + + + + + Recent Activity + + + +
+
+
+ {stats.bookmarkingActivity.thisWeek} +
+
This Week
+
+
+
+ {stats.bookmarkingActivity.thisMonth} +
+
This Month
+
+
+
+ {stats.bookmarkingActivity.thisYear} +
+
This Year
+
+
+ + + + {/* Top Domains */} + + + + + Top Domains + + + + {stats.topDomains.length > 0 ? ( +
+ {stats.topDomains + .slice(0, 8) + .map( + ( + domain: { domain: string; count: number }, + index: number, + ) => ( +
+
+
+ {index + 1} +
+ + {domain.domain} + +
+ {domain.count} +
+ ), + )} +
+ ) : ( +

No domains found

+ )} + + + + {/* Top Tags */} + + + + + Most Used Tags + + + + {stats.tagUsage.length > 0 ? ( +
+ {stats.tagUsage + .slice(0, 8) + .map( + (tag: { name: string; count: number }, index: number) => ( +
+
+
+ {index + 1} +
+ + {tag.name} + +
+ {tag.count} +
+ ), + )} +
+ ) : ( +

No tags found

+ )} + + +
+ + {/* Activity Patterns */} +
+ {/* Hourly Activity */} + + + + + Activity by Hour + + + + h.count, + )} + maxValue={maxHourlyActivity} + labels={hourLabels} + /> + + + + {/* Daily Activity */} + + + + + Activity by Day + + + + d.count, + )} + maxValue={maxDailyActivity} + labels={dayNames} + /> + + +
+ + {/* Asset Storage */} + {stats.assetsByType.length > 0 && ( + + + + + Storage Breakdown + + + +
+ {stats.assetsByType.map( + (asset: { type: string; count: number; totalSize: number }) => ( +
+
+ + {asset.type.replace(/([A-Z])/g, " $1").trim()} + + {asset.count} +
+
+ {formatBytes(asset.totalSize)} +
+ 0 + ? (asset.totalSize / stats.totalAssetSize) * 100 + : 0 + } + className="h-2" + /> +
+ ), + )} +
+ + + )} +
+ ); +} diff --git a/apps/web/lib/i18n/locales/en/translation.json b/apps/web/lib/i18n/locales/en/translation.json index d33e8521..727a7538 100644 --- a/apps/web/lib/i18n/locales/en/translation.json +++ b/apps/web/lib/i18n/locales/en/translation.json @@ -115,6 +115,9 @@ } } }, + "stats": { + "usage_statistics": "Usage Statistics" + }, "ai": { "ai_settings": "AI Settings", "tagging_rules": "Tagging Rules", diff --git a/packages/shared/types/users.ts b/packages/shared/types/users.ts index 0abe13b1..65e73ff3 100644 --- a/packages/shared/types/users.ts +++ b/packages/shared/types/users.ts @@ -38,6 +38,52 @@ export const zUserStatsResponseSchema = z.object({ numTags: z.number(), numLists: z.number(), numHighlights: z.number(), + bookmarksByType: z.object({ + link: z.number(), + text: z.number(), + asset: z.number(), + }), + topDomains: z + .array( + z.object({ + domain: z.string(), + count: z.number(), + }), + ) + .max(10), + totalAssetSize: z.number(), + assetsByType: z.array( + z.object({ + type: z.string(), + count: z.number(), + totalSize: z.number(), + }), + ), + bookmarkingActivity: z.object({ + thisWeek: z.number(), + thisMonth: z.number(), + thisYear: z.number(), + byHour: z.array( + z.object({ + hour: z.number(), + count: z.number(), + }), + ), + byDayOfWeek: z.array( + z.object({ + day: z.number(), + count: z.number(), + }), + ), + }), + tagUsage: z + .array( + z.object({ + name: z.string(), + count: z.number(), + }), + ) + .max(10), }); export const zUserSettingsSchema = z.object({ 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("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("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("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("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`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`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`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`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`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 -- cgit v1.2.3-70-g09d2