[{"_1":2,"_174":-5,"_175":-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","5c349ef9e7d7333839a0634dd567aae2d49623a6","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,"_148":149,"_171":172,"_173":-5},"handle","jibolad1","name","LearnEasy","path","backend/database/queries.ts","project",{"_102":103,"_96":97,"_104":105,"_106":-5,"_107":20,"_108":109,"_110":-5,"_111":-5,"_12":112,"_113":-5,"_114":20,"_115":-5,"_116":-5,"_117":32,"_14":118,"_119":120,"_121":120,"_122":123,"_124":123,"_125":123,"_126":120,"_127":120,"_23":128,"_131":132,"_134":135,"_147":20},"id","9a6bb110-3fbb-11f0-976c-76b3cceeab13","createdAt",["D",1748873543065],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","dfc821a0-3fb9-11f0-b6e9-ab0b2f88b858","pinnedFileId","pinPreview","pinMigrated","screenshotVersion","likeCount","e1b77b5f-4bdc-4e86-aa64-92dd8eff8f3e","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_94":95,"_129":130},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoiZGVmYXVsdCIsImlpZCI6Imluc18yTm10cGhQUklwU0tqZHNiOHNnNGd2ZjJURFUiLCJyaWQiOiJ1c2VyXzJ4eEhMek1DdnNLV2dZUVloVktrTngyNjZlSiJ9","org",{"_94":95,"_133":130,"_12":112},"avatar","branch",{"_136":137,"_96":44,"_102":138,"_139":103,"_140":-5,"_141":-5,"_142":-5,"_143":-5,"_144":-5,"_145":97,"_146":95},"version",19,"9a6c253c-3fbb-11f0-976c-76b3cceeab13","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_102":150,"_96":151,"_152":153,"_154":155,"_156":138,"_157":158,"_159":160,"_104":161,"_136":32,"_162":163,"_164":165,"_166":20,"_167":-5,"_98":168},"a137ffca-3fbc-11f0-a441-76b3cceeab13","queries.ts","fileId","a1380074-3fbc-11f0-a441-76b3cceeab13","parentId","968aa553-0a1e-48c5-8d79-8bd375816673","branchId","branchVersion",4,"latest",true,["D",1748873983963],"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport type { User, Skill, Post, PostResponse, ChatMessage, TutoringSession } from \"../../shared/types.ts\";\n\n// User queries\nexport async function createUser(userData: {\n name: string;\n email: string;\n password_hash: string;\n school: string;\n grade_level: string;\n bio?: string;\n}): Promise {\n const result = await sqlite.execute(\n `INSERT INTO users (name, email, password_hash, school, grade_level, bio) \n VALUES (?, ?, ?, ?, ?, ?)`,\n [userData.name, userData.email, userData.password_hash, userData.school, userData.grade_level, userData.bio || null]\n );\n return result.lastInsertRowId as number;\n}\n\nexport async function getUserByEmail(email: string): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM users WHERE email = ?`,\n [email]\n );\n return result.rows[0] as User || null;\n}\n\nexport async function getUserById(id: number): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM users WHERE id = ?`,\n [id]\n );\n return result.rows[0] as User || null;\n}\n\nexport async function updateUserReputation(userId: number, newRating: number): Promise {\n // Get current stats\n const user = await getUserById(userId);\n if (!user) return;\n\n const newTotal = user.total_sessions + 1;\n const newAverage = ((user.reputation_stars * user.total_sessions) + newRating) / newTotal;\n\n await sqlite.execute(\n `UPDATE users SET reputation_stars = ?, total_sessions = ? WHERE id = ?`,\n [newAverage, newTotal, userId]\n );\n}\n\n// Skill queries\nexport async function getUserSkills(userId: number): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM skills WHERE user_id = ? ORDER BY created_at DESC`,\n [userId]\n );\n return result.rows as Skill[];\n}\n\nexport async function addUserSkill(skillData: {\n user_id: number;\n subject: string;\n mastery_level: string;\n verification_method?: string;\n}): Promise {\n const result = await sqlite.execute(\n `INSERT OR REPLACE INTO skills (user_id, subject, mastery_level, verification_method) \n VALUES (?, ?, ?, ?)`,\n [skillData.user_id, skillData.subject, skillData.mastery_level, skillData.verification_method || null]\n );\n return result.lastInsertRowId as number;\n}\n\nexport async function findTutorsBySubject(subject: string, excludeUserId?: number): Promise<(User & { mastery_level: string })[]> {\n const query = excludeUserId \n ? `SELECT u.*, s.mastery_level FROM users u \n JOIN skills s ON u.id = s.user_id \n WHERE s.subject = ? AND u.id != ? \n ORDER BY u.reputation_stars DESC, s.mastery_level DESC`\n : `SELECT u.*, s.mastery_level FROM users u \n JOIN skills s ON u.id = s.user_id \n WHERE s.subject = ? \n ORDER BY u.reputation_stars DESC, s.mastery_level DESC`;\n \n const params = excludeUserId ? [subject, excludeUserId] : [subject];\n const result = await sqlite.execute(query, params);\n return result.rows as (User & { mastery_level: string })[];\n}\n\n// Post queries\nexport async function createPost(postData: {\n user_id: number;\n subject: string;\n description: string;\n urgency: string;\n location_preference: string;\n}): Promise {\n const result = await sqlite.execute(\n `INSERT INTO posts (user_id, subject, description, urgency, location_preference) \n VALUES (?, ?, ?, ?, ?)`,\n [postData.user_id, postData.subject, postData.description, postData.urgency, postData.location_preference]\n );\n return result.lastInsertRowId as number;\n}\n\nexport async function getPosts(school?: string, limit: number = 50): Promise {\n let query = `\n SELECT p.*, u.name as user_name, u.school as user_school, u.grade_level as user_grade,\n COUNT(pr.id) as responses_count\n FROM posts p \n JOIN users u ON p.user_id = u.id \n LEFT JOIN post_responses pr ON p.id = pr.post_id\n WHERE p.status = 'Open'\n `;\n \n const params: any[] = [];\n if (school) {\n query += ` AND u.school = ?`;\n params.push(school);\n }\n \n query += ` GROUP BY p.id ORDER BY p.created_at DESC LIMIT ?`;\n params.push(limit);\n\n const result = await sqlite.execute(query, params);\n return result.rows as Post[];\n}\n\nexport async function getPostById(id: number): Promise {\n const result = await sqlite.execute(\n `SELECT p.*, u.name as user_name, u.school as user_school, u.grade_level as user_grade,\n COUNT(pr.id) as responses_count\n FROM posts p \n JOIN users u ON p.user_id = u.id \n LEFT JOIN post_responses pr ON p.id = pr.post_id\n WHERE p.id = ?\n GROUP BY p.id`,\n [id]\n );\n return result.rows[0] as Post || null;\n}\n\nexport async function addPostResponse(responseData: {\n post_id: number;\n tutor_id: number;\n message: string;\n}): Promise {\n const result = await sqlite.execute(\n `INSERT INTO post_responses (post_id, tutor_id, message) \n VALUES (?, ?, ?)`,\n [responseData.post_id, responseData.tutor_id, responseData.message]\n );\n return result.lastInsertRowId as number;\n}\n\nexport async function getPostResponses(postId: number): Promise {\n const result = await sqlite.execute(\n `SELECT pr.*, u.name as tutor_name, u.reputation_stars as tutor_reputation\n FROM post_responses pr\n JOIN users u ON pr.tutor_id = u.id\n WHERE pr.post_id = ?\n ORDER BY pr.created_at DESC`,\n [postId]\n );\n return result.rows as PostResponse[];\n}\n\n// Chat queries\nexport async function sendMessage(messageData: {\n sender_id: number;\n receiver_id: number;\n message: string;\n}): Promise {\n const result = await sqlite.execute(\n `INSERT INTO chat_messages (sender_id, receiver_id, message) \n VALUES (?, ?, ?)`,\n [messageData.sender_id, messageData.receiver_id, messageData.message]\n );\n return result.lastInsertRowId as number;\n}\n\nexport async function getChatMessages(userId1: number, userId2: number, limit: number = 50): Promise {\n const result = await sqlite.execute(\n `SELECT cm.*, u.name as sender_name\n FROM chat_messages cm\n JOIN users u ON cm.sender_id = u.id\n WHERE (cm.sender_id = ? AND cm.receiver_id = ?) \n OR (cm.sender_id = ? AND cm.receiver_id = ?)\n ORDER BY cm.created_at ASC\n LIMIT ?`,\n [userId1, userId2, userId2, userId1, limit]\n );\n return result.rows as ChatMessage[];\n}\n\nexport async function markMessagesAsRead(senderId: number, receiverId: number): Promise {\n await sqlite.execute(\n `UPDATE chat_messages SET read = TRUE \n WHERE sender_id = ? AND receiver_id = ? AND read = FALSE`,\n [senderId, receiverId]\n );\n}\n\nexport async function getChatContacts(userId: number): Promise<{ user_id: number; name: string; last_message: string; unread_count: number }[]> {\n const result = await sqlite.execute(\n `SELECT DISTINCT \n CASE WHEN cm.sender_id = ? THEN cm.receiver_id ELSE cm.sender_id END as user_id,\n u.name,\n (SELECT message FROM chat_messages cm2 \n WHERE (cm2.sender_id = ? AND cm2.receiver_id = user_id) \n OR (cm2.sender_id = user_id AND cm2.receiver_id = ?)\n ORDER BY cm2.created_at DESC LIMIT 1) as last_message,\n (SELECT COUNT(*) FROM chat_messages cm3 \n WHERE cm3.sender_id = user_id AND cm3.receiver_id = ? AND cm3.read = FALSE) as unread_count\n FROM chat_messages cm\n JOIN users u ON u.id = CASE WHEN cm.sender_id = ? THEN cm.receiver_id ELSE cm.sender_id END\n WHERE cm.sender_id = ? OR cm.receiver_id = ?\n ORDER BY (SELECT MAX(created_at) FROM chat_messages cm4 \n WHERE (cm4.sender_id = ? AND cm4.receiver_id = user_id) \n OR (cm4.sender_id = user_id AND cm4.receiver_id = ?)) DESC`,\n [userId, userId, userId, userId, userId, userId, userId, userId, userId]\n );\n return result.rows as { user_id: number; name: string; last_message: string; unread_count: number }[];\n}\n\n// Session management\nexport async function createSession(sessionId: string, userId: number, expiresAt: Date): Promise {\n await sqlite.execute(\n `INSERT INTO user_sessions (id, user_id, expires_at) VALUES (?, ?, ?)`,\n [sessionId, userId, expiresAt.toISOString()]\n );\n}\n\nexport async function getSessionUser(sessionId: string): Promise {\n const result = await sqlite.execute(\n `SELECT u.* FROM users u\n JOIN user_sessions s ON u.id = s.user_id\n WHERE s.id = ? AND s.expires_at > datetime('now')`,\n [sessionId]\n );\n return result.rows[0] as User || null;\n}\n\nexport async function deleteSession(sessionId: string): Promise {\n await sqlite.execute(`DELETE FROM user_sessions WHERE id = ?`, [sessionId]);\n}\n\n// Schools\nexport async function getSchools(): Promise<{ id: number; name: string; location: string; type: string }[]> {\n const result = await sqlite.execute(`SELECT * FROM schools ORDER BY name`);\n return result.rows as { id: number; name: string; location: string; type: string }[];\n}","deleted","label",[169,170,151],"backend","database","permalink","https://www.val.town/x/jibolad1/LearnEasy/code/backend/database/queries.ts","extension","actionData","errors"]