This repository has no description
1import { db } from "../../../libs/db";
2import { users as usersTable, takes as takesTable } from "../../../libs/schema";
3import { handleApiError } from "../../../libs/apiError";
4import { eq, count } from "drizzle-orm";
5import { userService } from "../../../libs/cachet";
6
7export type Project = {
8 projectName: string;
9 projectDescription: string;
10 projectCategory:
11 | "hardware"
12 | "hardware_software"
13 | "website"
14 | "app"
15 | "game"
16 | "art_design"
17 | "other";
18 projectBannerUrl: string;
19 /** Total time spent on takes, in seconds */
20 totalTakesTime: number;
21 userId: string;
22 userName?: string;
23 /** Total number of takes */
24 takesCount: number;
25 lastUpdated: Date;
26 createdAt: Date;
27};
28
29// Project cache to reduce database queries
30const projectCache = new Map<
31 string,
32 { data: Project | Project[]; timestamp: number }
33>();
34const PROJECT_CACHE_TTL = 60 * 1000; // 1 minute
35
36export async function projects(url: URL): Promise<Response> {
37 const user = url.searchParams.get("user");
38 try {
39 // Check cache before database query
40 const cacheKey = user || "all_projects";
41 const cached = projectCache.get(cacheKey);
42 if (cached && Date.now() - cached.timestamp < PROJECT_CACHE_TTL) {
43 return new Response(
44 JSON.stringify({
45 projects: cached.data,
46 }),
47 {
48 headers: {
49 "Content-Type": "application/json",
50 },
51 },
52 );
53 }
54
55 // Use a JOIN query to get projects and takes count in a single database operation
56 let projectsWithCounts: {
57 projectName: string;
58 projectDescription: string;
59 projectCategory: string;
60 projectBannerUrl: string;
61 totalTakesTime: number;
62 userId: string;
63 takesCount: number;
64 lastUpdated: string;
65 createdAt: string;
66 }[];
67
68 if (user) {
69 // For a single user, get their project data and takes count directly
70 projectsWithCounts = await db
71 .select({
72 projectName: usersTable.projectName,
73 projectDescription: usersTable.projectDescription,
74 projectCategory: usersTable.projectCategory,
75 projectBannerUrl: usersTable.projectBannerUrl,
76 totalTakesTime: usersTable.totalTakesTime,
77 userId: usersTable.id,
78 takesCount: count(takesTable.id).as("takes_count"),
79 lastUpdated: usersTable.lastTakeUploadDate,
80 createdAt: usersTable.createdAt,
81 })
82 .from(usersTable)
83 .leftJoin(takesTable, eq(usersTable.id, takesTable.userId))
84 .where(eq(usersTable.id, user))
85 .groupBy(usersTable.id);
86 } else {
87 // For all users, get project data and takes count
88 projectsWithCounts = await db
89 .select({
90 projectName: usersTable.projectName,
91 projectDescription: usersTable.projectDescription,
92 projectCategory: usersTable.projectCategory,
93 projectBannerUrl: usersTable.projectBannerUrl,
94 totalTakesTime: usersTable.totalTakesTime,
95 userId: usersTable.id,
96 takesCount: count(takesTable.id).as("takes_count"),
97 lastUpdated: usersTable.lastTakeUploadDate,
98 createdAt: usersTable.createdAt,
99 })
100 .from(usersTable)
101 .leftJoin(takesTable, eq(usersTable.id, takesTable.userId))
102 .groupBy(usersTable.id);
103 }
104
105 if (projectsWithCounts.length === 0) {
106 return new Response(
107 JSON.stringify({
108 projects: [],
109 }),
110 {
111 headers: {
112 "Content-Type": "application/json",
113 },
114 },
115 );
116 }
117
118 // Get unique user IDs
119 const userIds = [
120 ...new Set(projectsWithCounts.map((project) => project.userId)),
121 ];
122
123 // Fetch all user names from shared user service
124 const userNamesPromises = userIds.map((id) =>
125 userService.getUserName(id),
126 );
127 const userNames = await Promise.all(userNamesPromises);
128
129 // Create a map of user names
130 const userNameMap: Record<string, string> = {};
131 userIds.forEach((id, index) => {
132 userNameMap[id] = userNames[index] || "Unknown User";
133 });
134
135 // Add user names to projects and convert lastUpdated to number
136 const projectsWithUserNames = projectsWithCounts.map((project) => ({
137 ...project,
138 userName: userNameMap[project.userId] || "Unknown User",
139 lastUpdated: new Date(project.lastUpdated),
140 createdAt: new Date(project.createdAt),
141 })) as Project[];
142
143 // Store in cache
144 const result = user ? projectsWithUserNames[0] : projectsWithUserNames;
145 projectCache.set(cacheKey, {
146 data: result as Project | Project[],
147 timestamp: Date.now(),
148 });
149
150 return new Response(
151 JSON.stringify({
152 projects: result,
153 }),
154 {
155 headers: {
156 "Content-Type": "application/json",
157 },
158 },
159 );
160 } catch (error) {
161 return handleApiError(error, "projects");
162 }
163}