[{"_1":2,"_180":-5,"_181":-5},"loaderData",{"_3":4,"_93":94},"root",{"_5":6,"_36":37,"_66":67},"ctx",{"_7":8,"_23":-5,"_24":25,"_26":27,"_28":20,"_29":20,"_30":20,"_31":-5,"_32":33,"_34":20,"_35":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","preferencesChatPanelState","notificationCount",0,"flagDenols","isTeamOrgMember","ENV",{"_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,"_64":65},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","VAL_RUNTIME_NAME","prod","RENDER_GIT_COMMIT","6050acf0705e6826bb1b68e27572c5b8979c5bd9","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",{"_68":69},"__internal_clerk_state",{"_70":71,"_72":47,"_73":74,"_75":74,"_76":20,"_77":74,"_78":74,"_79":74,"_80":74,"_81":82,"_91":20,"_92":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",{"_83":20,"_19":20,"_84":74,"_85":86,"_87":74,"_88":47,"_89":20,"_90":74},"isSignedIn","proxyUrl","reason","session-token-and-uat-missing","message","publishableKey","isSatellite","domain","__telemetryDisabled","__telemetryDebug","routes/embed.x.$handle.$name.$",{"_95":96,"_97":98,"_99":100,"_101":102,"_155":156,"_177":178,"_179":-5},"handle","David_Navi","name","Blogged","path","backend/database/queries.ts","project",{"_103":104,"_97":98,"_105":106,"_107":-5,"_108":20,"_109":110,"_111":-5,"_112":-5,"_12":113,"_114":115,"_116":117,"_118":117,"_119":-5,"_120":33,"_14":121,"_122":-5,"_123":-5,"_124":125,"_126":125,"_127":128,"_129":128,"_130":128,"_131":125,"_132":125,"_23":133,"_136":137,"_141":142,"_154":20},"id","34e8a8a0-3fbd-11f0-881d-76b3cceeab13","createdAt",["D",1748874231749],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","685cf750-3fbc-11f0-ad5b-814c56c8abb0","pinnedFileId","0630a7d2-3fbe-11f0-b6c3-76b3cceeab13","pinPreview",true,"pinMigrated","screenshotVersion","likeCount","63d009de-d622-4fdd-996d-1ed23d66d4c1","tursoName","tursoHostname","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_95":96,"_134":135},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoiZGVmYXVsdCIsImlpZCI6Imluc18yTm10cGhQUklwU0tqZHNiOHNnNGd2ZjJURFUiLCJyaWQiOiJ1c2VyXzJ4eEpUUUo4Z1RxcDhEUGJ4RjZZbFYwckVBVSJ9","org",{"_95":96,"_138":135,"_12":113,"_139":140},"avatar","tier","free","branch",{"_143":144,"_97":45,"_103":145,"_146":104,"_147":-5,"_148":-5,"_149":-5,"_150":-5,"_151":-5,"_152":98,"_153":96},"version",18,"34e90250-3fbd-11f0-881d-76b3cceeab13","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_103":157,"_97":158,"_159":160,"_161":162,"_163":145,"_164":165,"_166":117,"_105":167,"_143":33,"_168":169,"_170":171,"_172":20,"_173":-5,"_99":174},"d89ad32e-3fbd-11f0-8972-76b3cceeab13","queries.ts","fileId","d89ad3ec-3fbd-11f0-8972-76b3cceeab13","parentId","73e47dce-1c4a-471a-ae39-fe9e2fd0afe5","branchId","branchVersion",4,"latest",["D",1748874506381],"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport type { User, Post, Comment, PostSummary, PostDetailResponse } from \"../../shared/types.ts\";\n\n// User queries\nexport async function createUser(userData: {\n username: string;\n email: string;\n displayName: string;\n bio?: string;\n}): Promise {\n const result = await sqlite.execute(\n `INSERT INTO users (username, email, display_name, bio) \n VALUES (?, ?, ?, ?) RETURNING *`,\n [userData.username, userData.email, userData.displayName, userData.bio || null]\n );\n \n return mapDbUserToUser(result[0]);\n}\n\nexport async function getUserById(id: number): Promise {\n const result = await sqlite.execute(\"SELECT * FROM users WHERE id = ?\", [id]);\n return result.length > 0 ? mapDbUserToUser(result[0]) : null;\n}\n\nexport async function getUserByUsername(username: string): Promise {\n const result = await sqlite.execute(\"SELECT * FROM users WHERE username = ?\", [username]);\n return result.length > 0 ? mapDbUserToUser(result[0]) : null;\n}\n\n// Post queries\nexport async function createPost(postData: {\n userId: number;\n title: string;\n content: string;\n summary: string;\n}): Promise {\n const result = await sqlite.execute(\n `INSERT INTO posts (user_id, title, content, summary) \n VALUES (?, ?, ?, ?) RETURNING *`,\n [postData.userId, postData.title, postData.content, postData.summary]\n );\n \n return mapDbPostToPost(result[0]);\n}\n\nexport async function getPostById(id: number, userId?: number): Promise {\n const query = `\n SELECT p.*, u.username, u.display_name, u.avatar_url,\n COUNT(l.id) as likes_count,\n COUNT(c.id) as comments_count,\n COUNT(s.id) as shares_count\n ${userId ? ', CASE WHEN ul.id IS NOT NULL THEN 1 ELSE 0 END as is_liked' : ''}\n FROM posts p\n JOIN users u ON p.user_id = u.id\n LEFT JOIN likes l ON p.id = l.post_id\n LEFT JOIN comments c ON p.id = c.post_id\n LEFT JOIN shares s ON p.id = s.post_id\n ${userId ? 'LEFT JOIN likes ul ON p.id = ul.post_id AND ul.user_id = ?' : ''}\n WHERE p.id = ?\n GROUP BY p.id\n `;\n \n const params = userId ? [userId, id] : [id];\n const result = await sqlite.execute(query, params);\n \n if (result.length === 0) return null;\n \n const row = result[0];\n return {\n ...mapDbPostToPost(row),\n likesCount: row.likes_count,\n commentsCount: row.comments_count,\n sharesCount: row.shares_count,\n isLiked: userId ? Boolean(row.is_liked) : undefined,\n author: {\n id: row.user_id,\n username: row.username,\n displayName: row.display_name,\n avatarUrl: row.avatar_url,\n email: '', // Don't expose email in post queries\n bio: '',\n createdAt: ''\n }\n };\n}\n\nexport async function getPostsWithPagination(\n limit: number = 10, \n offset: number = 0,\n userId?: number\n): Promise<{ posts: PostSummary[]; total: number }> {\n // Get total count\n const countResult = await sqlite.execute(\"SELECT COUNT(*) as total FROM posts\");\n const total = countResult[0].total;\n\n // Get posts with author info and counts\n const query = `\n SELECT p.id, p.title, p.summary, p.created_at,\n u.id as author_id, u.username, u.display_name, u.avatar_url,\n COUNT(l.id) as likes_count,\n COUNT(c.id) as comments_count,\n COUNT(s.id) as shares_count\n ${userId ? ', CASE WHEN ul.id IS NOT NULL THEN 1 ELSE 0 END as is_liked' : ''}\n FROM posts p\n JOIN users u ON p.user_id = u.id\n LEFT JOIN likes l ON p.id = l.post_id\n LEFT JOIN comments c ON p.id = c.post_id\n LEFT JOIN shares s ON p.id = s.post_id\n ${userId ? 'LEFT JOIN likes ul ON p.id = ul.post_id AND ul.user_id = ?' : ''}\n GROUP BY p.id\n ORDER BY p.created_at DESC\n LIMIT ? OFFSET ?\n `;\n \n const params = userId ? [userId, limit, offset] : [limit, offset];\n const result = await sqlite.execute(query, params);\n \n const posts: PostSummary[] = result.map(row => ({\n id: row.id,\n title: row.title,\n summary: row.summary,\n createdAt: row.created_at,\n likesCount: row.likes_count,\n commentsCount: row.comments_count,\n sharesCount: row.shares_count,\n author: {\n id: row.author_id,\n username: row.username,\n displayName: row.display_name,\n avatarUrl: row.avatar_url\n }\n }));\n \n return { posts, total };\n}\n\nexport async function getPostWithComments(postId: number, userId?: number): Promise {\n const post = await getPostById(postId, userId);\n if (!post) return null;\n \n const commentsResult = await sqlite.execute(`\n SELECT c.*, u.username, u.display_name, u.avatar_url\n FROM comments c\n JOIN users u ON c.user_id = u.id\n WHERE c.post_id = ?\n ORDER BY c.created_at ASC\n `, [postId]);\n \n const comments: Comment[] = commentsResult.map(row => ({\n id: row.id,\n postId: row.post_id,\n userId: row.user_id,\n content: row.content,\n createdAt: row.created_at,\n author: {\n id: row.user_id,\n username: row.username,\n displayName: row.display_name,\n avatarUrl: row.avatar_url,\n email: '',\n bio: '',\n createdAt: ''\n }\n }));\n \n return { post, comments };\n}\n\n// Like/Unlike functionality\nexport async function toggleLike(postId: number, userId: number): Promise<{ liked: boolean; likesCount: number }> {\n // Check if already liked\n const existingLike = await sqlite.execute(\n \"SELECT id FROM likes WHERE post_id = ? AND user_id = ?\",\n [postId, userId]\n );\n \n if (existingLike.length > 0) {\n // Unlike\n await sqlite.execute(\"DELETE FROM likes WHERE post_id = ? AND user_id = ?\", [postId, userId]);\n } else {\n // Like\n await sqlite.execute(\"INSERT INTO likes (post_id, user_id) VALUES (?, ?)\", [postId, userId]);\n }\n \n // Get updated count\n const countResult = await sqlite.execute(\"SELECT COUNT(*) as count FROM likes WHERE post_id = ?\", [postId]);\n \n return {\n liked: existingLike.length === 0,\n likesCount: countResult[0].count\n };\n}\n\n// Comment functionality\nexport async function addComment(postId: number, userId: number, content: string): Promise {\n const result = await sqlite.execute(\n `INSERT INTO comments (post_id, user_id, content) VALUES (?, ?, ?) RETURNING *`,\n [postId, userId, content]\n );\n \n // Get comment with author info\n const commentWithAuthor = await sqlite.execute(`\n SELECT c.*, u.username, u.display_name, u.avatar_url\n FROM comments c\n JOIN users u ON c.user_id = u.id\n WHERE c.id = ?\n `, [result[0].id]);\n \n const row = commentWithAuthor[0];\n return {\n id: row.id,\n postId: row.post_id,\n userId: row.user_id,\n content: row.content,\n createdAt: row.created_at,\n author: {\n id: row.user_id,\n username: row.username,\n displayName: row.display_name,\n avatarUrl: row.avatar_url,\n email: '',\n bio: '',\n createdAt: ''\n }\n };\n}\n\n// Share functionality\nexport async function sharePost(postId: number, userId: number): Promise {\n await sqlite.execute(\"INSERT INTO shares (post_id, user_id) VALUES (?, ?)\", [postId, userId]);\n}\n\n// Helper functions to map database rows to TypeScript types\nfunction mapDbUserToUser(row: any): User {\n return {\n id: row.id,\n username: row.username,\n email: row.email,\n displayName: row.display_name,\n bio: row.bio,\n avatarUrl: row.avatar_url,\n createdAt: row.created_at\n };\n}\n\nfunction mapDbPostToPost(row: any): Post {\n return {\n id: row.id,\n userId: row.user_id,\n title: row.title,\n content: row.content,\n summary: row.summary,\n createdAt: row.created_at,\n updatedAt: row.updated_at,\n likesCount: 0,\n commentsCount: 0,\n sharesCount: 0\n };\n}","deleted","label",[175,176,158],"backend","database","permalink","https://www.val.town/x/David_Navi/Blogged/code/backend/database/queries.ts","extension","actionData","errors"]