[{"_1":2,"_173":-5,"_174":-5},"loaderData",{"_3":4,"_91":92},"root",{"_5":6,"_34":35,"_64":65},"ctx",{"_7":8,"_23":-5,"_24":25,"_26":27,"_28":20,"_29":20,"_30":20,"_31":32,"_33":20},"auth",{"_9":-5,"_10":-5,"_11":-5,"_12":-5,"_13":-5,"_14":-5,"_15":-5,"_16":-5,"_17":-5,"_18":-5,"_19":20,"_21":22},"sessionClaims","sessionId","sessionStatus","userId","actor","orgId","orgRole","orgSlug","orgPermissions","factorVerificationAge","isAuthenticated",false,"tokenType","session_token","user","preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","notificationCount",0,"flagDenols","ENV",{"_36":37,"_38":39,"_40":41,"_42":43,"_44":45,"_46":47,"_48":49,"_50":51,"_52":53,"_54":55,"_56":57,"_58":59,"_60":61,"_62":63},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","VAL_RUNTIME_NAME","prod","RENDER_GIT_COMMIT","27fe99fbd4c9bcde616903c265d49161492efaf2","RENDER_GIT_BRANCH","main","CLERK_PUBLISHABLE_KEY","pk_live_Y2xlcmsudmFsLnRvd24k","POSTHOG_API_TOKEN","phc_nmoyuAkStEZuUp69rYlg1DZgrIt19Gx0Y4Hwf8tGgJP","POSTHOG_API_HOST","https://wart.val.town","WEB_API_TEMPLATE","https://{val}.web.val.run","APP_URL","https://www.val.town","MODULE_URL","https://esm.town","VANITY_WEB_API_TEMPLATE","https://{label}.val.run/","DENOLS_URL","https://lsp.val.town","NODE_ENV","production","API_URL","https://api.val.town","clerkState",{"_66":67},"__internal_clerk_state",{"_68":69,"_70":45,"_71":72,"_73":72,"_74":20,"_75":72,"_76":72,"_77":72,"_78":72,"_79":80,"_89":20,"_90":20},"__clerk_ssr_state",{"_21":22,"_9":-5,"_10":-5,"_11":-5,"_12":-5,"_13":-5,"_14":-5,"_15":-5,"_16":-5,"_17":-5,"_18":-5,"_19":20},"__publishableKey","__proxyUrl","","__domain","__isSatellite","__signInUrl","__signUpUrl","__afterSignInUrl","__afterSignUpUrl","__clerk_debug",{"_81":20,"_19":20,"_82":72,"_83":84,"_85":72,"_86":45,"_87":20,"_88":72},"isSignedIn","proxyUrl","reason","session-token-and-uat-missing","message","publishableKey","isSatellite","domain","__telemetryDisabled","__telemetryDebug","routes/embed.x.$handle.$name.$",{"_93":94,"_95":96,"_97":98,"_99":100,"_150":151,"_170":171,"_172":-5},"handle","charmaine","name","charmaineValSearch","path","db.ts","project",{"_101":102,"_95":96,"_103":104,"_105":-5,"_106":20,"_107":108,"_109":-5,"_110":-5,"_12":111,"_112":-5,"_113":20,"_114":-5,"_115":32,"_14":116,"_117":118,"_119":118,"_120":121,"_122":121,"_123":121,"_124":118,"_125":118,"_23":126,"_129":130,"_132":133,"_149":20},"id","5bf2225e-3b09-11f0-8b52-9e149126039e","createdAt",["D",1748357183177],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","c156eb0e-8b9d-4104-a055-502e9a0eb441","pinnedFileId","pinPreview","screenshotVersion","likeCount","2019e55b-c541-4c51-bef0-008eef10ef88","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_93":94,"_127":128},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvdXBsb2FkZWQvaW1nXzJ4cFpFME5pSXNZamJNd296dlZSYVJ0OVF1RSJ9","org",{"_93":94,"_131":128},"avatar","branch",{"_134":135,"_95":43,"_101":136,"_137":102,"_138":139,"_140":141,"_142":108,"_143":144,"_145":146,"_147":96,"_148":94},"version",60,"5bf37776-3b09-11f0-8b52-9e149126039e","projectId","forkedBranchId","cdebffc6-0105-11f0-90bd-569c3dd06744","forkedBranchVersion",207,"forkedProjectPrivacy","forkedProjectId","cdebb08e-0105-11f0-90bd-569c3dd06744","forkedProjectOrgId","c1e7642a-74cc-4d69-8652-20abbdeaa94e","projectName","userHandle","isOwned","file",{"_101":152,"_95":98,"_153":154,"_155":-5,"_156":136,"_157":158,"_159":160,"_103":161,"_134":162,"_163":164,"_165":166,"_167":20,"_168":-5,"_97":169},"1afacbce-3d85-11f0-8bc4-76b3cceeab13","fileId","8d74a481-9458-480f-a7b0-b195dbdd8063","parentId","branchId","branchVersion",51,"latest",true,["D",1748630234091],7,"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport { z } from \"npm:zod\";\nimport {\n ProjectSearchResult,\n UserSearchResult,\n EnhancedSearchResult as TypesEnhancedSearchResult,\n SearchResponse as TypesSearchResponse\n} from \"./types.tsx\";\n\n/**\n * Wraps an async function to measure its execution time\n */\nexport async function withTiming(\n fn: () => Promise,\n): Promise<[T, number]> {\n const start = performance.now();\n const result = await fn();\n const end = performance.now();\n const duration = Math.round(end - start);\n\n return [result, duration];\n}\n\n// In-memory data structure for typeahead\nexport const typeaheadStrings: string[] = [];\n\n// Load all usernames and project paths into memory for fast typeahead\nexport async function loadTypeaheadDataIntoMemory(): Promise {\n console.log(\"Loading typeahead data into memory...\");\n\n // Clear existing data\n typeaheadStrings.length = 0;\n\n // Load unique usernames\n const usersResult = await sqlite.execute(\n `SELECT DISTINCT username\n FROM ${tablePrefix}_projects\n ORDER BY username`\n );\n\n for (const row of usersResult.rows) {\n if (row.username) typeaheadStrings.push(String(row.username));\n }\n\n // Load projects with username prefix\n const projectsResult = await sqlite.execute(\n `SELECT username, name\n FROM ${tablePrefix}_projects\n ORDER BY updated_at DESC`\n );\n\n for (const row of projectsResult.rows) {\n typeaheadStrings.push(`${row.username}/${row.name}`);\n }\n\n console.log(`Loaded ${typeaheadStrings.length} typeahead strings into memory`);\n}\n\n/**\n * Search typeahead data by prefix\n */\nexport function searchTypeahead(query: string, limit: number = 10): string[] {\n const lowerQuery = query.toLowerCase();\n const results: string[] = [];\n\n for (const str of typeaheadStrings) {\n if (str.toLowerCase().includes(lowerQuery)) {\n results.push(str);\n if (results.length >= limit) break;\n }\n }\n\n return results;\n}\n\n/**\n * Get activity data for the last 6 months\n */\nexport async function getActivityHeatmap(): Promise<{\n date: string;\n count: number;\n}[]> {\n const [result] = await withTiming(async () => {\n // Calculate date for 6 months ago\n const now = new Date();\n const sixMonthsAgo = new Date(now);\n sixMonthsAgo.setMonth(now.getMonth() - 6);\n\n // Format dates for SQL\n const sixMonthsAgoStr = sixMonthsAgo.toISOString();\n\n // Get file activity data grouped by day\n const activityResult = await sqlite.execute(\n `SELECT\n strftime('%Y-%m-%d', updated_at) as date,\n COUNT(*) as count\n FROM ${tablePrefix}_files\n WHERE updated_at >= ?\n GROUP BY date\n ORDER BY date ASC`,\n [sixMonthsAgoStr]\n );\n\n return activityResult.rows.map(row => ({\n date: String(row.date),\n count: Number(row.count)\n }));\n });\n\n return result;\n}\n\n/**\n * Get top contributors based on recency\n */\nexport async function getTopContributors(limit: number = 10): Promise<{\n username: string;\n projectCount: number;\n fileCount: number;\n url: string;\n profile_image_url: string | null;\n updated_at: string;\n}[]> {\n const result = await sqlite.execute(\n `SELECT\n p.username,\n COUNT(DISTINCT p.id) as project_count,\n COUNT(DISTINCT f.id) as file_count,\n 'https://val.town/u/' || p.username as url,\n u.profile_image_url,\n MAX(p.updated_at) as updated_at\n FROM ${tablePrefix}_projects p\n LEFT JOIN ${tablePrefix}_users u ON p.username = u.username\n LEFT JOIN ${tablePrefix}_files f ON p.id = f.project_id\n GROUP BY p.username\n ORDER BY updated_at DESC, project_count DESC\n LIMIT ?`,\n [limit]\n );\n\n return result.rows.map(row => ({\n username: String(row.username),\n projectCount: Number(row.project_count),\n fileCount: Number(row.file_count),\n url: String(row.url),\n profile_image_url: row.profile_image_url ? String(row.profile_image_url) : null,\n updated_at: String(row.updated_at)\n }));\n}\n\n/**\n * Get most active projects based on recency and file count\n */\nexport async function getMostActiveProjects(limit: number = 10): Promise<{\n name: string;\n username: string;\n fileCount: number;\n description: string | null;\n url: string;\n image_url: string | null;\n updated_at: string;\n}[]> {\n const result = await sqlite.execute(\n `SELECT\n p.name,\n p.username,\n COUNT(f.id) as file_count,\n p.description,\n p.url,\n p.image_url,\n p.updated_at\n FROM ${tablePrefix}_projects p\n LEFT JOIN ${tablePrefix}_files f ON p.id = f.project_id\n GROUP BY p.id\n ORDER BY p.updated_at DESC, file_count DESC\n LIMIT ?`,\n [limit]\n );\n\n return result.rows.map(row => ({\n name: String(row.name),\n username: String(row.username),\n fileCount: Number(row.file_count),\n description: row.description ? String(row.description) : null,\n url: String(row.url),\n image_url: row.image_url ? String(row.image_url) : null,\n updated_at: String(row.updated_at)\n }));\n}\n\n/**\n * Get database statistics for the homepage\n */\nexport async function getSearchStats(): Promise<{\n totalProjects: number;\n totalFiles: number;\n projectsLastDay: number;\n projectsLastWeek: number;\n projectsLastMonth: number;\n activityHeatmap: { date: string; count: number }[];\n topContributors: {\n username: string;\n projectCount: number;\n fileCount: number;\n url: string;\n profile_image_url: string | null;\n updated_at: string;\n }[];\n mostActiveProjects: {\n name: string;\n username: string;\n fileCount: number;\n description: string | null;\n url: string;\n image_url: string | null;\n updated_at: string;\n }[];\n executionTimeMs?: number;\n queryTimings?: Record;\n}> {\n const [results, totalTime] = await withTiming(async () => {\n // Get current time and dates for 1 day, 1 week, and 1 month ago\n const now = new Date();\n const oneDayAgo = new Date(now);\n oneDayAgo.setDate(now.getDate() - 1);\n\n const oneWeekAgo = new Date(now);\n oneWeekAgo.setDate(now.getDate() - 7);\n\n const oneMonthAgo = new Date(now);\n oneMonthAgo.setMonth(now.getMonth() - 1);\n\n // Format dates for SQL\n const oneDayAgoStr = oneDayAgo.toISOString();\n const oneWeekAgoStr = oneWeekAgo.toISOString();\n const oneMonthAgoStr = oneMonthAgo.toISOString();\n\n // Collect timings for all operations\n const queryTimings: Record = {};\n\n // Execute all queries in parallel for better performance\n const [\n [totalProjectsResult, totalProjectsTime],\n [totalFilesResult, totalFilesTime],\n [recentProjectsResult, recentProjectsTime],\n [activityHeatmap, heatmapTime],\n [topContributors, contributorsTime],\n [mostActiveProjects, projectsTime]\n ] = await Promise.all([\n // Get total counts\n withTiming(() => sqlite.execute(\n `SELECT COUNT(*) as count FROM ${tablePrefix}_projects`\n )),\n\n withTiming(() => sqlite.execute(\n `SELECT COUNT(*) as count FROM ${tablePrefix}_files`\n )),\n\n // Get counts for recent projects\n withTiming(() => sqlite.execute(\n `SELECT\n SUM(CASE WHEN updated_at >= ? THEN 1 ELSE 0 END) as last_day,\n SUM(CASE WHEN updated_at >= ? THEN 1 ELSE 0 END) as last_week,\n SUM(CASE WHEN updated_at >= ? THEN 1 ELSE 0 END) as last_month\n FROM ${tablePrefix}_projects`,\n [oneDayAgoStr, oneWeekAgoStr, oneMonthAgoStr]\n )),\n\n // Get activity heatmap data\n withTiming(() => getActivityHeatmap()),\n\n // Get top contributors\n withTiming(() => getTopContributors(10)),\n\n // Get most active projects\n withTiming(() => getMostActiveProjects(10))\n ]);\n\n // Store all timings\n queryTimings.totalProjects = totalProjectsTime;\n queryTimings.totalFiles = totalFilesTime;\n queryTimings.recentProjects = recentProjectsTime;\n queryTimings.activityHeatmap = heatmapTime;\n queryTimings.topContributors = contributorsTime;\n queryTimings.mostActiveProjects = projectsTime;\n\n return {\n totalProjects: Number(totalProjectsResult.rows[0]?.count || 0),\n totalFiles: Number(totalFilesResult.rows[0]?.count || 0),\n projectsLastDay: Number(recentProjectsResult.rows[0]?.last_day || 0),\n projectsLastWeek: Number(recentProjectsResult.rows[0]?.last_week || 0),\n projectsLastMonth: Number(recentProjectsResult.rows[0]?.last_month || 0),\n activityHeatmap,\n topContributors,\n mostActiveProjects,\n queryTimings\n };\n });\n\n return {\n ...results,\n executionTimeMs: totalTime\n };\n}\n\n// Original example code\nconst tablePrefix = \"vt_project_search_v5\";\n\n// Create tables for project file content search\nawait sqlite.batch([\n // Users table\n `CREATE TABLE IF NOT EXISTS ${tablePrefix}_users (\n id TEXT PRIMARY KEY,\n username TEXT,\n bio TEXT,\n profile_image_url TEXT,\n url TEXT NOT NULL,\n updated_at TIMESTAMP NOT NULL\n )`,\n\n // Projects table\n `CREATE TABLE IF NOT EXISTS ${tablePrefix}_projects (\n id TEXT PRIMARY KEY,\n url TEXT NOT NULL,\n name TEXT NOT NULL,\n username TEXT NOT NULL,\n updated_at TIMESTAMP NOT NULL,\n forked_branch_id TEXT,\n description TEXT,\n image_url TEXT,\n user_id TEXT,\n FOREIGN KEY (user_id) REFERENCES ${tablePrefix}_users(id)\n )`,\n\n // Files table with foreign key to projects\n `CREATE TABLE IF NOT EXISTS ${tablePrefix}_files (\n id TEXT PRIMARY KEY,\n url TEXT NOT NULL,\n project_id TEXT NOT NULL,\n name TEXT NOT NULL,\n content TEXT,\n updated_at TIMESTAMP NOT NULL,\n FOREIGN KEY (project_id) REFERENCES ${tablePrefix}_projects(id)\n )`,\n\n // Create index for faster text searches\n `CREATE INDEX IF NOT EXISTS idx_${tablePrefix}_files_content ON ${tablePrefix}_files(content)`,\n\n // Create index for project lookups\n `CREATE INDEX IF NOT EXISTS idx_${tablePrefix}_files_project_id ON ${tablePrefix}_files(project_id)`,\n\n // Create index for username lookups\n `CREATE INDEX IF NOT EXISTS idx_${tablePrefix}_users_username ON ${tablePrefix}_users(username)`,\n]);\n\n// Define types with Zod schemas\nconst UserSchema = z.object({\n id: z.string(),\n username: z.string().nullable(),\n bio: z.string().nullable(),\n profile_image_url: z.string().nullable(),\n url: z.string(),\n updated_at: z.string().datetime(),\n});\n\nconst ProjectSchema = z.object({\n id: z.string(),\n url: z.string(),\n name: z.string(),\n username: z.string(),\n description: z.string().nullable(),\n image_url: z.string().nullable(),\n forked_branch_id: z.string().nullable(),\n updated_at: z.string().datetime(),\n user_id: z.string().nullable(),\n});\n\nconst FileSchema = z.object({\n id: z.string(),\n url: z.string(),\n project_id: z.string(),\n name: z.string(),\n content: z.string().nullable(),\n updated_at: z.string().datetime(),\n});\n\nconst ProjectWithFilesSchema = ProjectSchema.extend({\n files: z.array(FileSchema).optional(),\n});\n\n// TypeScript interfaces derived from Zod schemas\ntype User = z.infer;\ntype Project = z.infer;\ntype File = z.infer;\ntype ProjectWithFiles = z.infer;\n\n// Search result type\ntype FileSearchResult = File & {\n project_name: string;\n username: string;\n project_url: string;\n};\n\n// Helper functions for project and file management\n\n/**\n * Get a user by ID\n */\nexport async function getUser(userId: string): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${tablePrefix}_users WHERE id = ?`,\n [userId],\n );\n\n if (result.rows.length === 0) return null;\n\n try {\n return UserSchema.parse(result.rows[0]);\n } catch (error) {\n console.error(\"Invalid user data:\", error);\n return null;\n }\n}\n\n/**\n * Insert or update a user if it's newer than existing data\n */\nexport async function upsertUser(user: User): Promise {\n // Check if user exists and compare timestamps\n const existingUser = await getUser(user.id);\n\n if (!existingUser) {\n // Insert new user\n await sqlite.execute(\n `INSERT INTO ${tablePrefix}_users (id, username, bio, profile_image_url, url, updated_at)\n VALUES (?, ?, ?, ?, ?, ?)`,\n [\n user.id,\n user.username,\n user.bio,\n user.profile_image_url,\n user.url,\n user.updated_at,\n ],\n );\n return true; // Inserted\n } else if (user.updated_at > existingUser.updated_at) {\n // Update existing user if newer\n await sqlite.execute(\n `UPDATE ${tablePrefix}_users\n SET username = ?, bio = ?, profile_image_url = ?, url = ?, updated_at = ?\n WHERE id = ?`,\n [\n user.username,\n user.bio,\n user.profile_image_url,\n user.url,\n user.updated_at,\n user.id,\n ],\n );\n return true; // Updated\n }\n\n return false; // Not updated (existing data is newer)\n}\n\n/**\n * Get a project by ID\n */\nasync function getProject(projectId: string): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${tablePrefix}_projects WHERE id = ?`,\n [projectId],\n );\n\n if (result.rows.length === 0) return null;\n\n try {\n return ProjectSchema.parse(result.rows[0]);\n } catch (error) {\n console.error(\"Invalid project data:\", error);\n return null;\n }\n}\n\n/**\n * Insert or update a project if it's newer than existing data\n */\nexport async function upsertProject(project: Project): Promise {\n // Convert Date object to string if needed\n const updatedAt = project.updated_at;\n\n // Check if project exists and compare timestamps\n const existingProject = await getProject(project.id);\n\n if (!existingProject) {\n // Insert new project\n await sqlite.execute(\n `INSERT INTO ${tablePrefix}_projects (id, url, name, username, updated_at, forked_branch_id, description, image_url, user_id)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,\n [\n project.id,\n project.url,\n project.name,\n project.username,\n updatedAt,\n project.forked_branch_id,\n project.description,\n project.image_url,\n project.user_id\n ],\n );\n return true; // Inserted\n } else if (updatedAt > existingProject.updated_at) {\n // Update existing project if newer\n await sqlite.execute(\n `UPDATE ${tablePrefix}_projects\n SET url = ?, name = ?, username = ?, updated_at = ?, forked_branch_id = ?, description = ?, image_url = ?, user_id = ?\n WHERE id = ?`,\n [\n project.url,\n project.name,\n project.username,\n updatedAt,\n project.forked_branch_id,\n project.description,\n project.image_url,\n project.user_id,\n project.id\n ],\n );\n return true; // Updated\n }\n\n return false; // Not updated (existing data is newer)\n}\n\n/**\n * Get a file by ID\n */\nasync function getFile(fileId: string): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${tablePrefix}_files WHERE id = ?`,\n [fileId],\n );\n\n if (result.rows.length === 0) return null;\n\n try {\n return FileSchema.parse(result.rows[0]);\n } catch (error) {\n console.error(\"Invalid file data:\", error);\n return null;\n }\n}\n\n/**\n * Insert or update a file if it's newer than existing data\n */\nexport async function upsertFile(file: File): Promise {\n // Convert Date object to string if needed\n\n // Check if file exists and compare timestamps\n const existingFile = await getFile(file.id);\n\n if (!existingFile) {\n // Insert new file\n await sqlite.execute(\n `INSERT INTO ${tablePrefix}_files (id, url, project_id, name, content, updated_at)\n VALUES (?, ?, ?, ?, ?, ?)`,\n [\n file.id,\n file.url,\n file.project_id,\n file.name,\n file.content,\n file.updated_at,\n ],\n );\n return true; // Inserted\n } else if (file.updated_at > existingFile.updated_at) {\n // Update existing file if newer\n await sqlite.execute(\n `UPDATE ${tablePrefix}_files SET url = ?, project_id = ?, name = ?, content = ?, updated_at = ?\n WHERE id = ?`,\n [\n file.url,\n file.project_id,\n file.name,\n file.content,\n file.updated_at,\n file.id,\n ],\n );\n return true; // Updated\n }\n\n return false; // Not updated (existing data is newer)\n}\n\n/**\n * Enhanced search result with code context for internal use\n */\ntype EnhancedSearchResult = TypesEnhancedSearchResult;\n\n/**\n * Extract matched lines with context around matches\n */\nfunction extractMatchedLines(\n content: string,\n searchText: string,\n contextLines: number = 2,\n): EnhancedSearchResult[\"matchedLines\"] {\n if (!content) return [];\n\n const lines = content.split(\"\\n\");\n const matchedLineIndices: number[] = [];\n\n // Find all matching lines\n for (let i = 0; i < lines.length; i++) {\n if (lines[i].toLowerCase().includes(searchText.toLowerCase())) {\n matchedLineIndices.push(i);\n }\n }\n\n // Create groups of matched lines with context\n const result: {\n lineNumber: number;\n lineContent: string;\n isMatch: boolean;\n }[] = [];\n const processedLines = new Set();\n\n for (const matchIndex of matchedLineIndices) {\n // Calculate range for context\n const startLine = Math.max(0, matchIndex - contextLines);\n const endLine = Math.min(lines.length - 1, matchIndex + contextLines);\n\n // Add lines with context\n for (let i = startLine; i <= endLine; i++) {\n if (!processedLines.has(i)) {\n result.push({\n lineNumber: i + 1, // 1-based line number for display\n lineContent: lines[i],\n isMatch: matchedLineIndices.includes(i),\n });\n processedLines.add(i);\n }\n }\n\n // Add a separator between non-contiguous blocks\n if (\n result.length > 0 && result[result.length - 1].lineNumber !== endLine + 1\n ) {\n result.push({\n lineNumber: -1, // Sentinel value for separator\n lineContent: \"...\",\n isMatch: false,\n });\n }\n }\n\n return result;\n}\n\n/**\n * Search for files containing specific text\n */\nexport async function searchFileContent(\n searchText: string,\n): Promise {\n const searchPattern = `%${searchText}%`;\n\n const result = await sqlite.execute(\n `SELECT f.*, p.name as project_name, p.url as project_url, p.username, p.description\n FROM ${tablePrefix}_files f\n JOIN ${tablePrefix}_projects p ON f.project_id = p.id\n WHERE f.content LIKE ?\n OR p.username LIKE ?\n OR p.name LIKE ?\n OR f.name LIKE ?\n OR p.description LIKE ?\n ORDER BY f.updated_at DESC`,\n [searchPattern, searchPattern, searchPattern, searchPattern, searchPattern],\n );\n\n const FileSearchResultSchema = FileSchema.extend({\n project_name: z.string(),\n project_url: z.string(),\n username: z.string(),\n description: z.string().nullable(),\n });\n\n try {\n return z.array(FileSearchResultSchema).parse(result.rows);\n } catch (error) {\n console.error(\"Invalid search results:\", error);\n return [];\n }\n}\n\n/**\n * Search response with pagination and timing info\n */\nexport type SearchResponse = {\n results: EnhancedSearchResult[];\n totalResults: number;\n page: number;\n pageSize: number;\n totalPages: number;\n executionTimeMs: number;\n};\n\n/**\n * Count total file search results\n */\nexport async function countFileSearchResults(searchText: string): Promise {\n const searchPattern = `%${searchText}%`;\n\n const result = await sqlite.execute(\n `SELECT COUNT(*) as total\n FROM ${tablePrefix}_files f\n JOIN ${tablePrefix}_projects p ON f.project_id = p.id\n WHERE f.content LIKE ?\n OR f.name LIKE ?`,\n [searchPattern, searchPattern],\n );\n\n return Number(result.rows[0]?.total) || 0;\n}\n\n/**\n * Count total project search results\n */\nexport async function countProjectSearchResults(searchText: string): Promise {\n const searchPattern = `%${searchText}%`;\n\n const result = await sqlite.execute(\n `SELECT COUNT(DISTINCT p.id) as total\n FROM ${tablePrefix}_projects p\n WHERE p.name LIKE ?\n OR p.description LIKE ?`,\n [searchPattern, searchPattern],\n );\n\n return Number(result.rows[0]?.total) || 0;\n}\n\n/**\n * Count total user search results\n */\nexport async function countUserSearchResults(searchText: string): Promise {\n const searchPattern = `%${searchText}%`;\n\n const result = await sqlite.execute(\n `SELECT COUNT(*) as total\n FROM ${tablePrefix}_users u\n WHERE u.username LIKE ?\n OR u.bio LIKE ?`,\n [searchPattern, searchPattern],\n );\n\n return Number(result.rows[0]?.total) || 0;\n}\n\n/**\n * Search for projects matching the query\n */\nexport async function searchProjects(\n searchText: string,\n page: number = 1,\n pageSize: number = 10\n): Promise {\n const searchPattern = `%${searchText}%`;\n const offset = (page - 1) * pageSize;\n\n const result = await sqlite.execute(\n `SELECT p.*,\n (SELECT COUNT(*) FROM ${tablePrefix}_files f WHERE f.project_id = p.id AND\n (f.content LIKE ? OR f.name LIKE ?)) as matchCount\n FROM ${tablePrefix}_projects p\n WHERE p.name LIKE ?\n OR p.description LIKE ?\n ORDER BY p.updated_at DESC\n LIMIT ? OFFSET ?`,\n [searchPattern, searchPattern, searchPattern, searchPattern, pageSize, offset],\n );\n\n const ProjectSearchResultSchema = ProjectSchema.extend({\n matchCount: z.number(),\n });\n\n try {\n return z.array(ProjectSearchResultSchema).parse(result.rows);\n } catch (error) {\n console.error(\"Invalid project search results:\", error);\n return [];\n }\n}\n\n/**\n * Search for users matching the query\n */\nexport async function searchUsers(\n searchText: string,\n page: number = 1,\n pageSize: number = 10\n): Promise {\n const searchPattern = `%${searchText}%`;\n const offset = (page - 1) * pageSize;\n\n const result = await sqlite.execute(\n `SELECT u.id, u.username, u.bio, u.profile_image_url, u.url, u.updated_at,\n (SELECT COUNT(*) FROM ${tablePrefix}_projects p\n WHERE p.user_id = u.id) as matchCount\n FROM ${tablePrefix}_users u\n WHERE u.username LIKE ?\n OR u.bio LIKE ?\n ORDER BY u.updated_at DESC\n LIMIT ? OFFSET ?`,\n [searchPattern, searchPattern, pageSize, offset],\n );\n\n const UserSearchResultSchema = z.object({\n id: z.string(),\n username: z.string().nullable(),\n bio: z.string().nullable(),\n profile_image_url: z.string().nullable(),\n url: z.string(),\n updated_at: z.string().datetime(),\n matchCount: z.number(),\n });\n\n try {\n return z.array(UserSearchResultSchema).parse(result.rows);\n } catch (error) {\n console.error(\"Invalid user search results:\", error);\n return [];\n }\n}\n\n/**\n * Enhanced search that returns matched content with context and pagination\n */\nexport async function searchFileContentWithContext(\n searchText: string,\n contextLines: number = 2,\n page: number = 1,\n pageSize: number = 10,\n resultType: \"files\" | \"projects\" | \"users\" = \"files\",\n): Promise {\n if (!searchText.trim()) {\n return {\n fileResults: [],\n projectResults: [],\n userResults: [],\n docResults: [],\n totalFileResults: 0,\n totalProjectResults: 0,\n totalUserResults: 0,\n totalDocResults: 0,\n page,\n pageSize,\n totalPages: 0,\n executionTimeMs: 0,\n resultType,\n };\n }\n\n const [results, totalExecutionTime] = await withTiming(async () => {\n // Calculate pagination parameters\n const offset = (page - 1) * pageSize;\n\n // Start all database queries in parallel\n // 1. Launch count queries\n const countsPromise = withTiming(() => Promise.all([\n countFileSearchResults(searchText),\n countProjectSearchResults(searchText),\n countUserSearchResults(searchText),\n ]));\n\n // 2. Start preparing search pattern\n const searchPattern = `%${searchText}%`;\n\n // 3. Start all data fetching queries in parallel, regardless of result type\n // This way we don't wait for counts to finish before starting data queries\n const fileResultsPromise = withTiming(async () => {\n // Always launch the files query for the active type or for samples\n const result = await sqlite.execute(\n `SELECT f.*, p.name as project_name, p.url as project_url, p.username, p.description, p.image_url\n FROM ${tablePrefix}_files f\n JOIN ${tablePrefix}_projects p ON f.project_id = p.id\n WHERE f.content LIKE ?\n OR f.name LIKE ?\n ORDER BY f.updated_at DESC\n LIMIT ? OFFSET ?`,\n [searchPattern, searchPattern, resultType === \"files\" ? pageSize : 5, resultType === \"files\" ? offset : 0],\n );\n\n const FileSearchResultSchema = FileSchema.extend({\n project_name: z.string(),\n project_url: z.string(),\n username: z.string(),\n description: z.string().nullable(),\n image_url: z.string().nullable(),\n });\n\n try {\n const rawFileResults = z.array(FileSearchResultSchema).parse(result.rows);\n\n return rawFileResults.map((result) => {\n const matchedLines = extractMatchedLines(\n result.content || \"\",\n searchText,\n contextLines,\n );\n const totalMatches = matchedLines.filter((line) => line.isMatch).length;\n\n // If we have a match but no matched lines (username/project match only),\n // show the first few lines of the file\n if (totalMatches === 0 && result.content) {\n const firstLines = result.content.split(\"\\n\").slice(0, 5).map((lineContent, i) => ({\n lineNumber: i + 1,\n lineContent,\n isMatch: false\n }));\n\n return {\n ...result,\n matchedLines: firstLines,\n totalMatches: 0,\n };\n }\n\n return {\n ...result,\n matchedLines,\n totalMatches,\n };\n });\n } catch (error) {\n console.error(\"Invalid file search results:\", error);\n return [];\n }\n });\n\n // Launch projects query\n const projectResultsPromise = withTiming(() => searchProjects(\n searchText,\n resultType === \"projects\" ? page : 1,\n resultType === \"projects\" ? pageSize : 5\n ));\n\n // Launch users query\n const userResultsPromise = withTiming(() => searchUsers(\n searchText,\n resultType === \"users\" ? page : 1,\n resultType === \"users\" ? pageSize : 5\n ));\n\n // Wait for all database operations to complete in parallel\n const [\n [countsResult, countsTime],\n [rawFileResults, fileTime],\n [projectResults, projectTime],\n [userResults, userTime]\n ] = await Promise.all([\n countsPromise,\n fileResultsPromise,\n projectResultsPromise,\n userResultsPromise\n ]);\n\n const [totalFileResults, totalProjectResults, totalUserResults] = countsResult;\n\n // Determine result type if not specified (use the one with most results)\n let effectiveResultType = resultType;\n if (!resultType) {\n if (totalFileResults >= totalProjectResults && totalFileResults >= totalUserResults) {\n effectiveResultType = \"files\";\n } else if (totalProjectResults >= totalFileResults && totalProjectResults >= totalUserResults) {\n effectiveResultType = \"projects\";\n } else {\n effectiveResultType = \"users\";\n }\n }\n\n // Calculate total pages based on the active result type\n let totalResults: number;\n switch (effectiveResultType) {\n case \"files\":\n totalResults = totalFileResults;\n break;\n case \"projects\":\n totalResults = totalProjectResults;\n break;\n case \"users\":\n totalResults = totalUserResults;\n break;\n }\n\n const totalPages = Math.ceil(totalResults / pageSize);\n\n // Filter the results to use based on result type\n // We already have all the data, just need to use the right ones\n let fileResults: EnhancedSearchResult[] = [];\n if (effectiveResultType === \"files\" || totalFileResults > 0) {\n if (effectiveResultType === \"files\") {\n // Use all results for active type\n fileResults = rawFileResults;\n } else if (totalFileResults > 0) {\n // Use limited sample for other types\n fileResults = rawFileResults.slice(0, 5);\n }\n }\n\n console.log(`Operation timings:\n - Counts: ${countsTime}ms\n - Files: ${fileTime}ms\n - Projects: ${projectTime}ms\n - Users: ${userTime}ms\n - Total: ${countsTime + fileTime + projectTime + userTime}ms (parallel execution)\n `);\n\n return {\n fileResults,\n projectResults,\n userResults,\n docResults: [],\n totalFileResults,\n totalProjectResults,\n totalUserResults,\n totalDocResults: 0,\n page,\n pageSize,\n totalPages,\n resultType: effectiveResultType,\n };\n });\n\n return {\n ...results,\n executionTimeMs: totalExecutionTime,\n };\n}\n\n/**\n * Get most recently edited vals (files) within a time period for newsletter curation\n */\nexport async function getMostEditedVals(\n daysBack: number = 30, \n limit: number = 50\n): Promise<{\n id: string;\n name: string;\n url: string;\n content: string | null;\n updated_at: string;\n project_name: string;\n username: string;\n project_url: string;\n project_description: string | null;\n user_profile_image_url: string | null;\n update_count: number;\n}[]> {\n // Calculate the date threshold\n const dateThreshold = new Date();\n dateThreshold.setDate(dateThreshold.getDate() - daysBack);\n const thresholdStr = dateThreshold.toISOString();\n\n const result = await sqlite.execute(\n `SELECT \n f.id,\n f.name,\n f.url,\n f.content,\n f.updated_at,\n p.name as project_name,\n p.username,\n p.url as project_url,\n p.description as project_description,\n u.profile_image_url as user_profile_image_url,\n -- Use updated_at as a proxy for edit frequency\n 1 as update_count\n FROM ${tablePrefix}_files f\n JOIN ${tablePrefix}_projects p ON f.project_id = p.id\n LEFT JOIN ${tablePrefix}_users u ON p.username = u.username\n WHERE f.updated_at >= ?\n ORDER BY f.updated_at DESC\n LIMIT ?`,\n [thresholdStr, limit]\n );\n\n return result.rows.map(row => ({\n id: String(row.id),\n name: String(row.name),\n url: String(row.url),\n content: row.content ? String(row.content) : null,\n updated_at: String(row.updated_at),\n project_name: String(row.project_name),\n username: String(row.username),\n project_url: String(row.project_url),\n project_description: row.project_description ? String(row.project_description) : null,\n user_profile_image_url: row.user_profile_image_url ? String(row.user_profile_image_url) : null,\n update_count: Number(row.update_count)\n }));\n}\n\n/**\n * Get most active projects in a time period for newsletter curation\n * Excludes @vtTestLocal user\n * Activity is measured by number of distinct edit sessions, not total file changes\n */\nexport async function getMostActiveProjectsForNewsletter(\n daysBack: number = 30,\n limit: number = 50\n): Promise<{\n project_id: string;\n name: string;\n username: string;\n url: string;\n description: string | null;\n image_url: string | null;\n user_profile_image_url: string | null;\n edit_sessions: number;\n file_count: number;\n recent_files: string[];\n latest_update: string;\n}[]> {\n const dateThreshold = new Date();\n dateThreshold.setDate(dateThreshold.getDate() - daysBack);\n const thresholdStr = dateThreshold.toISOString();\n\n const result = await sqlite.execute(\n `SELECT \n p.id as project_id,\n p.name,\n p.username,\n p.url,\n p.description,\n p.image_url,\n u.profile_image_url as user_profile_image_url,\n COUNT(DISTINCT strftime('%Y-%m-%d %H:%M', f.updated_at)) as edit_sessions,\n COUNT(f.id) as file_count,\n GROUP_CONCAT(f.name, ', ') as recent_files,\n MAX(f.updated_at) as latest_update\n FROM ${tablePrefix}_projects p\n LEFT JOIN ${tablePrefix}_users u ON p.username = u.username\n JOIN ${tablePrefix}_files f ON p.id = f.project_id\n WHERE f.updated_at >= ? \n AND p.username != 'vtTestLocal'\n GROUP BY p.id\n ORDER BY edit_sessions DESC, latest_update DESC\n LIMIT ?`,\n [thresholdStr, limit]\n );\n\n return result.rows.map(row => ({\n project_id: String(row.project_id),\n name: String(row.name),\n username: String(row.username),\n url: String(row.url),\n description: row.description ? String(row.description) : null,\n image_url: row.image_url ? String(row.image_url) : null,\n user_profile_image_url: row.user_profile_image_url ? String(row.user_profile_image_url) : null,\n edit_sessions: Number(row.edit_sessions),\n file_count: Number(row.file_count),\n recent_files: row.recent_files ? String(row.recent_files).split(', ') : [],\n latest_update: String(row.latest_update)\n }));\n}\n","deleted","label",[98],"permalink","https://www.val.town/x/charmaine/charmaineValSearch/code/db.ts","extension","actionData","errors"]