[{"_1":2,"_172":-5,"_173":-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,"_146":147,"_169":170,"_171":-5},"handle","ikeali","name","my-first-val","path","backend/database/queries.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,"_145":20},"id","33614708-3f9f-11f0-9765-76b3cceeab13","createdAt",["D",1748861344283],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","1ef380b0-3f9f-11f0-ad5b-814c56c8abb0","pinnedFileId","pinPreview","screenshotVersion","likeCount","175dd161-d97e-4dae-8110-5f4423725a9e","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_93":94,"_127":128},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvb2F1dGhfZ2l0aHViL2ltZ18yeHd1OENvNzliczFIeTFodFNiYms3ejB3dTQifQ","org",{"_93":94,"_131":128},"avatar","branch",{"_134":135,"_95":43,"_101":136,"_137":102,"_138":-5,"_139":-5,"_140":-5,"_141":-5,"_142":-5,"_143":96,"_144":94},"version",26,"336188a8-3f9f-11f0-9765-76b3cceeab13","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_101":148,"_95":149,"_150":151,"_152":153,"_154":136,"_155":156,"_157":158,"_103":159,"_134":32,"_160":161,"_162":163,"_164":20,"_165":-5,"_97":166},"0c0a2ee8-3fa1-11f0-a06e-76b3cceeab13","queries.ts","fileId","0c0a2f7e-3fa1-11f0-a06e-76b3cceeab13","parentId","341387e4-1e9b-4d54-8afe-afd0b26e7b0e","branchId","branchVersion",10,"latest",true,["D",1748862137271],"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport type { User, Poll, PollOption, Vote, CreatePollRequest } from \"../../shared/types.ts\";\n\n// User queries\nexport async function createUser(username: string, email: string, passwordHash: string): Promise {\n const result = await sqlite.execute(\n `INSERT INTO users_v1 (username, email, password_hash) VALUES (?, ?, ?) RETURNING *`,\n [username, email, passwordHash]\n );\n return result.rows[0] as User;\n}\n\nexport async function getUserByUsername(username: string): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM users_v1 WHERE username = ?`,\n [username]\n );\n return result.rows[0] as User || null;\n}\n\nexport async function getUserByEmail(email: string): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM users_v1 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_v1 WHERE id = ?`,\n [id]\n );\n return result.rows[0] as User || null;\n}\n\n// Session queries\nexport async function createSession(sessionId: string, userId: number, expiresAt: Date): Promise {\n await sqlite.execute(\n `INSERT INTO sessions_v1 (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_v1 u\n JOIN sessions_v1 s ON u.id = s.user_id\n WHERE s.id = ? AND s.expires_at > CURRENT_TIMESTAMP\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 sessions_v1 WHERE id = ?`, [sessionId]);\n}\n\n// Poll queries\nexport async function createPoll(poll: CreatePollRequest, creatorId: number): Promise {\n // Create the poll\n const pollResult = await sqlite.execute(`\n INSERT INTO polls_v1 (title, description, creator_id, start_date, end_date)\n VALUES (?, ?, ?, ?, ?)\n RETURNING *\n `, [poll.title, poll.description, creatorId, poll.start_date, poll.end_date]);\n \n const newPoll = pollResult.rows[0] as Poll;\n \n // Create poll options\n for (const optionText of poll.options) {\n await sqlite.execute(`\n INSERT INTO poll_options_v1 (poll_id, option_text)\n VALUES (?, ?)\n `, [newPoll.id, optionText]);\n }\n \n return getPollById(newPoll.id);\n}\n\nexport async function getPollById(id: number, userId?: number): Promise {\n const pollResult = await sqlite.execute(`\n SELECT p.*, u.username as creator_username\n FROM polls_v1 p\n JOIN users_v1 u ON p.creator_id = u.id\n WHERE p.id = ?\n `, [id]);\n \n if (pollResult.rows.length === 0) return null;\n \n const poll = pollResult.rows[0] as Poll;\n \n // Get poll options\n const optionsResult = await sqlite.execute(`\n SELECT * FROM poll_options_v1 WHERE poll_id = ? ORDER BY id\n `, [id]);\n \n poll.options = optionsResult.rows as PollOption[];\n \n // Check if user has voted (if userId provided)\n if (userId) {\n const voteResult = await sqlite.execute(`\n SELECT 1 FROM votes_v1 WHERE poll_id = ? AND user_id = ?\n `, [id, userId]);\n poll.user_has_voted = voteResult.rows.length > 0;\n }\n \n // Calculate total votes\n poll.total_votes = poll.options.reduce((sum, option) => sum + option.vote_count, 0);\n \n return poll;\n}\n\nexport async function getAllPolls(userId?: number): Promise {\n const pollsResult = await sqlite.execute(`\n SELECT p.*, u.username as creator_username\n FROM polls_v1 p\n JOIN users_v1 u ON p.creator_id = u.id\n ORDER BY p.created_at DESC\n `);\n \n const polls: Poll[] = [];\n \n for (const pollRow of pollsResult.rows) {\n const poll = pollRow as Poll;\n \n // Get options for each poll\n const optionsResult = await sqlite.execute(`\n SELECT * FROM poll_options_v1 WHERE poll_id = ? ORDER BY id\n `, [poll.id]);\n \n poll.options = optionsResult.rows as PollOption[];\n \n // Check if user has voted\n if (userId) {\n const voteResult = await sqlite.execute(`\n SELECT 1 FROM votes_v1 WHERE poll_id = ? AND user_id = ?\n `, [poll.id, userId]);\n poll.user_has_voted = voteResult.rows.length > 0;\n }\n \n // Calculate total votes\n poll.total_votes = poll.options.reduce((sum, option) => sum + option.vote_count, 0);\n \n polls.push(poll);\n }\n \n return polls;\n}\n\nexport async function updatePoll(id: number, updates: Partial): Promise {\n const setClause = Object.keys(updates)\n .filter(key => key !== 'id' && key !== 'options')\n .map(key => `${key} = ?`)\n .join(', ');\n \n if (!setClause) return getPollById(id);\n \n const values = Object.keys(updates)\n .filter(key => key !== 'id' && key !== 'options')\n .map(key => updates[key as keyof Poll]);\n \n await sqlite.execute(`\n UPDATE polls_v1 SET ${setClause} WHERE id = ?\n `, [...values, id]);\n \n return getPollById(id);\n}\n\nexport async function deletePoll(id: number): Promise {\n const result = await sqlite.execute(`DELETE FROM polls_v1 WHERE id = ?`, [id]);\n return result.changes > 0;\n}\n\n// Vote queries\nexport async function castVote(pollId: number, userId: number, optionId: number): Promise {\n try {\n // Check if user has already voted\n const existingVote = await sqlite.execute(`\n SELECT 1 FROM votes_v1 WHERE poll_id = ? AND user_id = ?\n `, [pollId, userId]);\n \n if (existingVote.rows.length > 0) {\n throw new Error(\"User has already voted on this poll\");\n }\n \n // Insert the vote\n const voteResult = await sqlite.execute(`\n INSERT INTO votes_v1 (poll_id, user_id, option_id)\n VALUES (?, ?, ?)\n RETURNING *\n `, [pollId, userId, optionId]);\n \n // Update vote count\n await sqlite.execute(`\n UPDATE poll_options_v1 \n SET vote_count = vote_count + 1 \n WHERE id = ?\n `, [optionId]);\n \n return voteResult.rows[0] as Vote;\n } catch (error) {\n console.error(\"Error casting vote:\", error);\n return null;\n }\n}\n\nexport async function getUserVote(pollId: number, userId: number): Promise {\n const result = await sqlite.execute(`\n SELECT * FROM votes_v1 WHERE poll_id = ? AND user_id = ?\n `, [pollId, userId]);\n \n return result.rows[0] as Vote || null;\n}\n\nexport async function getPollResults(pollId: number): Promise<{ poll: Poll; total_votes: number } | null> {\n const poll = await getPollById(pollId);\n if (!poll) return null;\n \n const total_votes = poll.options.reduce((sum, option) => sum + option.vote_count, 0);\n \n return { poll, total_votes };\n}","deleted","label",[167,168,149],"backend","database","permalink","https://www.val.town/x/ikeali/my-first-val/code/backend/database/queries.ts","extension","actionData","errors"]