[{"_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","e3ca0a77ab07ff2ce3097208ffd6aa0e378d3c47","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,"_147":148,"_170":171,"_172":-5},"handle","Remna","name","Remnachat","path","backend/database/queries.ts","project",{"_101":102,"_95":96,"_103":104,"_105":-5,"_106":20,"_107":108,"_109":110,"_111":-5,"_12":112,"_113":-5,"_114":20,"_115":-5,"_116":32,"_14":117,"_118":119,"_120":119,"_121":122,"_123":122,"_124":122,"_125":119,"_126":119,"_23":127,"_130":131,"_133":134,"_146":20},"id","36374624-3fba-11f0-a2df-76b3cceeab13","createdAt",["D",1748872945452],"deletedAt","legacyEnvVars","privacy","public","description","This is my trial application","imageUrl","eaae0c60-3fb9-11f0-ad5b-814c56c8abb0","pinnedFileId","pinPreview","screenshotVersion","likeCount","68c28e20-3e36-4e5c-9d56-670c73be044e","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_93":94,"_128":129},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvb2F1dGhfZ2l0aHViL2ltZ18yeHhIU3FucHFXZmdDM1lPbmRpanM4VDhlSGIifQ","org",{"_93":94,"_132":129},"avatar","branch",{"_135":136,"_95":43,"_101":137,"_138":102,"_139":-5,"_140":-5,"_141":-5,"_142":-5,"_143":-5,"_144":96,"_145":94},"version",22,"3637e372-3fba-11f0-a2df-76b3cceeab13","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_101":149,"_95":150,"_151":152,"_153":154,"_155":137,"_156":157,"_158":159,"_103":160,"_135":32,"_161":162,"_163":164,"_165":20,"_166":-5,"_97":167},"3ff747d0-3fbb-11f0-9ea7-76b3cceeab13","queries.ts","fileId","3ff74898-3fbb-11f0-9ea7-76b3cceeab13","parentId","9ef53471-47b3-4d24-b436-2233fda281a8","branchId","branchVersion",4,"latest",true,["D",1748873391303],"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport type { User, Contact, Chat, Message, Group, ChatRoomPost, ChatRoomReply, UserSettings } from \"../../shared/types.ts\";\n\n// User queries\nexport async function createUser(user: Omit & { passwordHash: string }) {\n const id = crypto.randomUUID();\n const createdAt = new Date().toISOString();\n \n await sqlite.execute(`\n INSERT INTO users (id, username, email, phone, password_hash, avatar, status, created_at)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?)\n `, [id, user.username, user.email, user.phone || null, user.passwordHash, user.avatar || null, user.status, createdAt]);\n \n return { ...user, id, createdAt };\n}\n\nexport async function getUserByEmail(email: string): Promise {\n const result = await sqlite.execute(`\n SELECT id, username, email, phone, avatar, status, last_seen, created_at\n FROM users WHERE email = ?\n `, [email]);\n \n return result.length > 0 ? {\n id: result[0].id,\n username: result[0].username,\n email: result[0].email,\n phone: result[0].phone,\n avatar: result[0].avatar,\n status: result[0].status,\n lastSeen: result[0].last_seen,\n createdAt: result[0].created_at\n } : null;\n}\n\nexport async function getUserByUsername(username: string): Promise {\n const result = await sqlite.execute(`\n SELECT id, username, email, phone, avatar, status, last_seen, created_at\n FROM users WHERE username = ?\n `, [username]);\n \n return result.length > 0 ? {\n id: result[0].id,\n username: result[0].username,\n email: result[0].email,\n phone: result[0].phone,\n avatar: result[0].avatar,\n status: result[0].status,\n lastSeen: result[0].last_seen,\n createdAt: result[0].created_at\n } : null;\n}\n\nexport async function getUserById(id: string): Promise {\n const result = await sqlite.execute(`\n SELECT id, username, email, phone, avatar, status, last_seen, created_at\n FROM users WHERE id = ?\n `, [id]);\n \n return result.length > 0 ? {\n id: result[0].id,\n username: result[0].username,\n email: result[0].email,\n phone: result[0].phone,\n avatar: result[0].avatar,\n status: result[0].status,\n lastSeen: result[0].last_seen,\n createdAt: result[0].created_at\n } : null;\n}\n\nexport async function getUserPasswordHash(email: string): Promise {\n const result = await sqlite.execute(`\n SELECT password_hash FROM users WHERE email = ?\n `, [email]);\n \n return result.length > 0 ? result[0].password_hash : null;\n}\n\nexport async function updateUserStatus(userId: string, status: 'online' | 'offline' | 'away') {\n await sqlite.execute(`\n UPDATE users SET status = ?, last_seen = CURRENT_TIMESTAMP WHERE id = ?\n `, [status, userId]);\n}\n\n// Contact queries\nexport async function addContact(userId: string, contactId: string, contactName?: string): Promise {\n const id = crypto.randomUUID();\n const addedAt = new Date().toISOString();\n \n await sqlite.execute(`\n INSERT INTO contacts (id, user_id, contact_id, contact_name, added_at)\n VALUES (?, ?, ?, ?, ?)\n `, [id, userId, contactId, contactName || null, addedAt]);\n \n const contact = await getUserById(contactId);\n return {\n id,\n userId,\n contactId,\n contactName,\n addedAt,\n contact: contact!\n };\n}\n\nexport async function getUserContacts(userId: string): Promise {\n const result = await sqlite.execute(`\n SELECT c.*, u.username, u.email, u.phone, u.avatar, u.status, u.last_seen, u.created_at\n FROM contacts c\n JOIN users u ON c.contact_id = u.id\n WHERE c.user_id = ?\n ORDER BY u.username\n `, [userId]);\n \n return result.map(row => ({\n id: row.id,\n userId: row.user_id,\n contactId: row.contact_id,\n contactName: row.contact_name,\n addedAt: row.added_at,\n contact: {\n id: row.contact_id,\n username: row.username,\n email: row.email,\n phone: row.phone,\n avatar: row.avatar,\n status: row.status,\n lastSeen: row.last_seen,\n createdAt: row.created_at\n }\n }));\n}\n\n// Chat queries\nexport async function createChat(type: 'individual' | 'group', createdBy: string, name?: string): Promise {\n const id = crypto.randomUUID();\n const timestamp = new Date().toISOString();\n \n await sqlite.execute(`\n INSERT INTO chats (id, type, name, created_by, created_at, updated_at)\n VALUES (?, ?, ?, ?, ?, ?)\n `, [id, type, name || null, createdBy, timestamp, timestamp]);\n \n return id;\n}\n\nexport async function addChatParticipant(chatId: string, userId: string, isAdmin = false) {\n const id = crypto.randomUUID();\n await sqlite.execute(`\n INSERT INTO chat_participants (id, chat_id, user_id, is_admin)\n VALUES (?, ?, ?, ?)\n `, [id, chatId, userId, isAdmin]);\n}\n\nexport async function getUserChats(userId: string): Promise {\n const result = await sqlite.execute(`\n SELECT DISTINCT c.*, \n m.content as last_message_content,\n m.timestamp as last_message_time,\n m.sender_id as last_message_sender,\n u.username as last_message_sender_name\n FROM chats c\n JOIN chat_participants cp ON c.id = cp.chat_id\n LEFT JOIN messages m ON c.id = m.chat_id\n LEFT JOIN users u ON m.sender_id = u.id\n WHERE cp.user_id = ?\n AND (m.timestamp IS NULL OR m.timestamp = (\n SELECT MAX(timestamp) FROM messages WHERE chat_id = c.id\n ))\n ORDER BY m.timestamp DESC, c.created_at DESC\n `, [userId]);\n \n const chats: Chat[] = [];\n for (const row of result) {\n const participants = await sqlite.execute(`\n SELECT user_id FROM chat_participants WHERE chat_id = ?\n `, [row.id]);\n \n const unreadCount = await sqlite.execute(`\n SELECT COUNT(*) as count FROM messages m\n WHERE m.chat_id = ? AND m.sender_id != ?\n AND NOT EXISTS (\n SELECT 1 FROM message_read_status mrs \n WHERE mrs.message_id = m.id AND mrs.user_id = ?\n )\n `, [row.id, userId, userId]);\n \n chats.push({\n id: row.id,\n type: row.type,\n participants: participants.map(p => p.user_id),\n name: row.name,\n avatar: row.avatar,\n lastMessage: row.last_message_content ? {\n id: '',\n chatId: row.id,\n senderId: row.last_message_sender,\n content: row.last_message_content,\n type: 'text',\n timestamp: row.last_message_time,\n sender: { username: row.last_message_sender_name } as User\n } : undefined,\n unreadCount: unreadCount[0]?.count || 0,\n createdAt: row.created_at,\n updatedAt: row.updated_at\n });\n }\n \n return chats;\n}\n\n// Message queries\nexport async function sendMessage(chatId: string, senderId: string, content: string, type = 'text', replyTo?: string): Promise {\n const id = crypto.randomUUID();\n const timestamp = new Date().toISOString();\n \n await sqlite.execute(`\n INSERT INTO messages (id, chat_id, sender_id, content, type, reply_to, timestamp)\n VALUES (?, ?, ?, ?, ?, ?, ?)\n `, [id, chatId, senderId, content, type, replyTo || null, timestamp]);\n \n // Update chat timestamp\n await sqlite.execute(`\n UPDATE chats SET updated_at = ? WHERE id = ?\n `, [timestamp, chatId]);\n \n const sender = await getUserById(senderId);\n return {\n id,\n chatId,\n senderId,\n content,\n type: type as any,\n replyTo,\n timestamp,\n sender: sender!\n };\n}\n\nexport async function getChatMessages(chatId: string, limit = 50, offset = 0): Promise {\n const result = await sqlite.execute(`\n SELECT m.*, u.username, u.avatar\n FROM messages m\n JOIN users u ON m.sender_id = u.id\n WHERE m.chat_id = ?\n ORDER BY m.timestamp DESC\n LIMIT ? OFFSET ?\n `, [chatId, limit, offset]);\n \n return result.map(row => ({\n id: row.id,\n chatId: row.chat_id,\n senderId: row.sender_id,\n content: row.content,\n type: row.type,\n replyTo: row.reply_to,\n timestamp: row.timestamp,\n edited: row.edited,\n editedAt: row.edited_at,\n sender: {\n id: row.sender_id,\n username: row.username,\n avatar: row.avatar\n } as User\n })).reverse();\n}\n\n// Chat room queries\nexport async function createChatRoomPost(userId: string, title: string, content: string, type: string, tags: string[]): Promise {\n const id = crypto.randomUUID();\n const timestamp = new Date().toISOString();\n \n await sqlite.execute(`\n INSERT INTO chatroom_posts (id, user_id, title, content, type, tags, timestamp)\n VALUES (?, ?, ?, ?, ?, ?, ?)\n `, [id, userId, title, content, type, JSON.stringify(tags), timestamp]);\n \n const user = await getUserById(userId);\n return {\n id,\n userId,\n title,\n content,\n type: type as any,\n tags,\n timestamp,\n user: user!,\n replies: [],\n upvotes: 0,\n downvotes: 0\n };\n}\n\nexport async function getChatRoomPosts(limit = 20, offset = 0): Promise {\n const result = await sqlite.execute(`\n SELECT p.*, u.username, u.avatar,\n COALESCE(p.upvotes, 0) as upvotes,\n COALESCE(p.downvotes, 0) as downvotes\n FROM chatroom_posts p\n JOIN users u ON p.user_id = u.id\n ORDER BY p.timestamp DESC\n LIMIT ? OFFSET ?\n `, [limit, offset]);\n \n const posts: ChatRoomPost[] = [];\n for (const row of result) {\n const replies = await getChatRoomReplies(row.id);\n posts.push({\n id: row.id,\n userId: row.user_id,\n title: row.title,\n content: row.content,\n type: row.type,\n tags: JSON.parse(row.tags || '[]'),\n timestamp: row.timestamp,\n edited: row.edited,\n editedAt: row.edited_at,\n user: {\n id: row.user_id,\n username: row.username,\n avatar: row.avatar\n } as User,\n replies,\n upvotes: row.upvotes,\n downvotes: row.downvotes\n });\n }\n \n return posts;\n}\n\nexport async function getChatRoomReplies(postId: string): Promise {\n const result = await sqlite.execute(`\n SELECT r.*, u.username, u.avatar\n FROM chatroom_replies r\n JOIN users u ON r.user_id = u.id\n WHERE r.post_id = ?\n ORDER BY r.timestamp ASC\n `, [postId]);\n \n return result.map(row => ({\n id: row.id,\n postId: row.post_id,\n userId: row.user_id,\n content: row.content,\n timestamp: row.timestamp,\n edited: row.edited,\n editedAt: row.edited_at,\n user: {\n id: row.user_id,\n username: row.username,\n avatar: row.avatar\n } as User,\n upvotes: row.upvotes || 0,\n downvotes: row.downvotes || 0\n }));\n}\n\nexport async function createChatRoomReply(postId: string, userId: string, content: string): Promise {\n const id = crypto.randomUUID();\n const timestamp = new Date().toISOString();\n \n await sqlite.execute(`\n INSERT INTO chatroom_replies (id, post_id, user_id, content, timestamp)\n VALUES (?, ?, ?, ?, ?)\n `, [id, postId, userId, content, timestamp]);\n \n const user = await getUserById(userId);\n return {\n id,\n postId,\n userId,\n content,\n timestamp,\n user: user!,\n upvotes: 0,\n downvotes: 0\n };\n}\n\n// Settings queries\nexport async function getUserSettings(userId: string): Promise {\n const result = await sqlite.execute(`\n SELECT * FROM user_settings WHERE user_id = ?\n `, [userId]);\n \n if (result.length === 0) {\n // Create default settings\n const id = crypto.randomUUID();\n const defaultSettings = {\n id,\n userId,\n theme: 'light' as const,\n notifications: {\n messages: true,\n groups: true,\n chatroom: true,\n sound: true\n },\n privacy: {\n lastSeen: 'everyone' as const,\n profilePhoto: 'everyone' as const,\n status: 'everyone' as const\n },\n chatSettings: {\n enterToSend: true,\n fontSize: 'medium' as const,\n messagePreview: true\n }\n };\n \n await sqlite.execute(`\n INSERT INTO user_settings (id, user_id, theme, notifications, privacy, chat_settings)\n VALUES (?, ?, ?, ?, ?, ?)\n `, [\n id, userId, defaultSettings.theme,\n JSON.stringify(defaultSettings.notifications),\n JSON.stringify(defaultSettings.privacy),\n JSON.stringify(defaultSettings.chatSettings)\n ]);\n \n return defaultSettings;\n }\n \n const row = result[0];\n return {\n id: row.id,\n userId: row.user_id,\n theme: row.theme,\n notifications: JSON.parse(row.notifications),\n privacy: JSON.parse(row.privacy),\n chatSettings: JSON.parse(row.chat_settings)\n };\n}\n\nexport async function updateUserSettings(userId: string, settings: Partial>) {\n const updates: string[] = [];\n const values: any[] = [];\n \n if (settings.theme) {\n updates.push('theme = ?');\n values.push(settings.theme);\n }\n if (settings.notifications) {\n updates.push('notifications = ?');\n values.push(JSON.stringify(settings.notifications));\n }\n if (settings.privacy) {\n updates.push('privacy = ?');\n values.push(JSON.stringify(settings.privacy));\n }\n if (settings.chatSettings) {\n updates.push('chat_settings = ?');\n values.push(JSON.stringify(settings.chatSettings));\n }\n \n if (updates.length > 0) {\n values.push(userId);\n await sqlite.execute(`\n UPDATE user_settings SET ${updates.join(', ')} WHERE user_id = ?\n `, values);\n }\n}","deleted","label",[168,169,150],"backend","database","permalink","https://www.val.town/x/Remna/Remnachat/code/backend/database/queries.ts","extension","actionData","errors"]