[{"_1":2,"_175":-5,"_176":-5},"loaderData",{"_3":4,"_92":93},"root",{"_5":6,"_35":36,"_65":66},"ctx",{"_7":8,"_23":-5,"_24":25,"_26":27,"_28":20,"_29":20,"_30":20,"_31":32,"_33":20,"_34":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","isTeamOrgMember","ENV",{"_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":64},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","VAL_RUNTIME_NAME","prod","RENDER_GIT_COMMIT","c2ca74b25698bc62f1250d595e4f42af9437a7d5","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",{"_67":68},"__internal_clerk_state",{"_69":70,"_71":46,"_72":73,"_74":73,"_75":20,"_76":73,"_77":73,"_78":73,"_79":73,"_80":81,"_90":20,"_91":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",{"_82":20,"_19":20,"_83":73,"_84":85,"_86":73,"_87":46,"_88":20,"_89":73},"isSignedIn","proxyUrl","reason","session-token-and-uat-missing","message","publishableKey","isSatellite","domain","__telemetryDisabled","__telemetryDebug","routes/embed.x.$handle.$name.$",{"_94":95,"_96":97,"_98":99,"_100":101,"_150":151,"_172":173,"_174":-5},"handle","Zoemedia","name","TOS","path","backend/database/messages.ts","project",{"_102":103,"_96":97,"_104":105,"_106":-5,"_107":20,"_108":109,"_110":-5,"_111":-5,"_12":112,"_113":114,"_115":116,"_117":116,"_118":-5,"_119":32,"_14":120,"_121":122,"_123":122,"_124":125,"_126":125,"_127":125,"_128":122,"_129":122,"_23":130,"_133":134,"_136":137,"_149":20},"id","415ae048-325d-11f0-a41d-569c3dd06744","createdAt",["D",1747403655828],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","e0e0bf30-247e-11f0-9d73-cd8227da5cf6","pinnedFileId","67c216de-325d-11f0-a529-569c3dd06744","pinPreview",true,"pinMigrated","screenshotVersion","likeCount","b2a21fd4-ab59-49d8-875e-54408060892a","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_94":95,"_131":132},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoiZGVmYXVsdCIsImlpZCI6Imluc18yTm10cGhQUklwU0tqZHNiOHNnNGd2ZjJURFUiLCJyaWQiOiJ1c2VyXzJ3Tk9tUnpQcTRjejVsVzFsdlV1VlRrU0JPNiJ9","org",{"_94":95,"_135":132,"_12":112},"avatar","branch",{"_138":139,"_96":44,"_102":140,"_141":103,"_142":-5,"_143":-5,"_144":-5,"_145":-5,"_146":-5,"_147":97,"_148":95},"version",22,"415b32b4-325d-11f0-a41d-569c3dd06744","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_102":152,"_96":153,"_154":155,"_156":157,"_158":140,"_159":160,"_161":116,"_104":162,"_138":32,"_163":164,"_165":166,"_167":20,"_168":-5,"_98":169},"51c15514-325f-11f0-aaeb-569c3dd06744","messages.ts","fileId","51c155b4-325f-11f0-aaeb-569c3dd06744","parentId","08a0b4a1-f6b8-46b4-9a73-a244d91bcba4","branchId","branchVersion",17,"latest",["D",1747404542334],"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport { Message, MessageWithUser } from \"../../shared/types.ts\";\nimport { getPublicUserData } from \"./users.ts\";\n\n// Create a new message\nexport async function createMessage(\n senderId: number,\n content: string,\n receiverId: number | null = null // null for public chat\n): Promise {\n const now = new Date().toISOString();\n \n const result = await sqlite.execute(\n `INSERT INTO messages (sender_id, receiver_id, content, created_at, is_read)\n VALUES (?, ?, ?, ?, ?)\n RETURNING *`,\n [senderId, receiverId, content, now, receiverId === null] // Public messages are always \"read\"\n );\n \n return result.rows[0] as Message;\n}\n\n// Get a message by ID\nexport async function getMessageById(messageId: number): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM messages WHERE id = ?`,\n [messageId]\n );\n \n if (result.rows.length === 0) {\n return null;\n }\n \n return result.rows[0] as Message;\n}\n\n// Get a message with user data\nexport async function getMessageWithUserData(\n messageId: number\n): Promise {\n const message = await getMessageById(messageId);\n \n if (!message) {\n return null;\n }\n \n // Get sender data\n const sender = await getPublicUserData(message.sender_id);\n \n if (!sender) {\n return null;\n }\n \n return {\n ...message,\n sender\n };\n}\n\n// Get public chat messages\nexport async function getPublicChatMessages(\n limit: number = 50,\n before?: string // timestamp to get messages before\n): Promise {\n let query = `\n SELECT * FROM messages\n WHERE receiver_id IS NULL\n `;\n \n const params: any[] = [];\n \n if (before) {\n query += ` AND created_at < ?`;\n params.push(before);\n }\n \n query += `\n ORDER BY created_at DESC\n LIMIT ?\n `;\n \n params.push(limit);\n \n const result = await sqlite.execute(query, params);\n \n const messages = result.rows as Message[];\n \n // Enhance messages with user data\n const messagesWithUserData = await Promise.all(\n messages.map(message => getMessageWithUserData(message.id))\n );\n \n // Filter out any null results and reverse to get chronological order\n return messagesWithUserData\n .filter(message => message !== null) as MessageWithUser[];\n}\n\n// Get private chat messages between two users\nexport async function getPrivateChatMessages(\n user1Id: number,\n user2Id: number,\n limit: number = 50,\n before?: string // timestamp to get messages before\n): Promise {\n let query = `\n SELECT * FROM messages\n WHERE (sender_id = ? AND receiver_id = ?) OR (sender_id = ? AND receiver_id = ?)\n `;\n \n const params: any[] = [user1Id, user2Id, user2Id, user1Id];\n \n if (before) {\n query += ` AND created_at < ?`;\n params.push(before);\n }\n \n query += `\n ORDER BY created_at DESC\n LIMIT ?\n `;\n \n params.push(limit);\n \n const result = await sqlite.execute(query, params);\n \n const messages = result.rows as Message[];\n \n // Enhance messages with user data\n const messagesWithUserData = await Promise.all(\n messages.map(message => getMessageWithUserData(message.id))\n );\n \n // Filter out any null results\n return messagesWithUserData\n .filter(message => message !== null) as MessageWithUser[];\n}\n\n// Mark messages as read\nexport async function markMessagesAsRead(\n receiverId: number,\n senderId: number\n): Promise {\n const result = await sqlite.execute(\n `UPDATE messages\n SET is_read = 1\n WHERE receiver_id = ? AND sender_id = ? AND is_read = 0`,\n [receiverId, senderId]\n );\n \n return result.rowsAffected > 0;\n}\n\n// Get unread message count for a user\nexport async function getUnreadMessageCount(userId: number): Promise {\n const result = await sqlite.execute(\n `SELECT COUNT(*) as count\n FROM messages\n WHERE receiver_id = ? AND is_read = 0`,\n [userId]\n );\n \n return result.rows[0].count;\n}\n\n// Get chat partners for a user (people they've exchanged messages with)\nexport async function getChatPartners(userId: number): Promise<{\n userId: number;\n username: string;\n profilePicture: string;\n lastMessage: string;\n lastMessageTime: string;\n unreadCount: number;\n}[]> {\n // This query gets the most recent message between the user and each chat partner\n const result = await sqlite.execute(`\n WITH chat_partners AS (\n SELECT \n CASE \n WHEN sender_id = ? THEN receiver_id\n ELSE sender_id\n END as partner_id\n FROM messages\n WHERE (sender_id = ? OR receiver_id = ?) AND receiver_id IS NOT NULL\n GROUP BY partner_id\n ),\n last_messages AS (\n SELECT \n cp.partner_id,\n m.content,\n m.created_at,\n ROW_NUMBER() OVER (PARTITION BY cp.partner_id ORDER BY m.created_at DESC) as rn\n FROM chat_partners cp\n JOIN messages m ON (m.sender_id = ? AND m.receiver_id = cp.partner_id) OR (m.sender_id = cp.partner_id AND m.receiver_id = ?)\n ),\n unread_counts AS (\n SELECT \n sender_id,\n COUNT(*) as unread_count\n FROM messages\n WHERE receiver_id = ? AND is_read = 0\n GROUP BY sender_id\n )\n SELECT \n u.id as userId,\n u.username,\n u.profile_picture as profilePicture,\n lm.content as lastMessage,\n lm.created_at as lastMessageTime,\n COALESCE(uc.unread_count, 0) as unreadCount\n FROM chat_partners cp\n JOIN users u ON cp.partner_id = u.id\n JOIN last_messages lm ON cp.partner_id = lm.partner_id AND lm.rn = 1\n LEFT JOIN unread_counts uc ON cp.partner_id = uc.sender_id\n ORDER BY lm.created_at DESC\n `, [userId, userId, userId, userId, userId, userId]);\n \n return result.rows as {\n userId: number;\n username: string;\n profilePicture: string;\n lastMessage: string;\n lastMessageTime: string;\n unreadCount: number;\n }[];\n}","deleted","label",[170,171,153],"backend","database","permalink","https://www.val.town/x/Zoemedia/TOS/code/backend/database/messages.ts","extension","actionData","errors"]