[{"_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","1f1a009cadcf7353a37b999b92a7f8af633192ea","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","ikeali","name","untitled-1496","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","67b51c0a-3f9f-11f0-9481-76b3cceeab13","createdAt",["D",1748861432074],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","1ef380b0-3f9f-11f0-ad5b-814c56c8abb0","pinnedFileId","909566ce-3fa0-11f0-9704-76b3cceeab13","pinPreview",true,"pinMigrated","screenshotVersion","likeCount","175dd161-d97e-4dae-8110-5f4423725a9e","tursoName","tursoHostname","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_95":96,"_134":135},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvb2F1dGhfZ2l0aHViL2ltZ18yeHd1OENvNzliczFIeTFodFNiYms3ejB3dTQifQ","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",19,"67b55990-3f9f-11f0-9481-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},"6ccb2a1c-3fa0-11f0-8f25-76b3cceeab13","queries.ts","fileId","6ccb2b02-3fa0-11f0-8f25-76b3cceeab13","parentId","036933b1-cfcd-4ef7-bd2c-07e69fefd672","branchId","branchVersion",5,"latest",["D",1748861870099],"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport { TABLE_NAMES } from \"./migrations.ts\";\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 ${TABLE_NAMES.USERS} (username, email, password_hash) VALUES (?, ?, ?)`,\n [username, email, passwordHash]\n );\n return result.lastInsertRowId;\n}\n\nexport async function getUserByUsername(username: string): Promise {\n const result = await sqlite.execute(\n `SELECT id, username, email, is_admin as isAdmin, created_at as createdAt \n FROM ${TABLE_NAMES.USERS} 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 id, username, email, is_admin as isAdmin, created_at as createdAt \n FROM ${TABLE_NAMES.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 id, username, email, is_admin as isAdmin, created_at as createdAt \n FROM ${TABLE_NAMES.USERS} WHERE id = ?`,\n [id]\n );\n return result.rows[0] as User || null;\n}\n\nexport async function getUserPasswordHash(username: string): Promise {\n const result = await sqlite.execute(\n `SELECT password_hash FROM ${TABLE_NAMES.USERS} WHERE username = ?`,\n [username]\n );\n return result.rows[0]?.password_hash || null;\n}\n\n// Poll queries\nexport async function createPoll(poll: CreatePollRequest, creatorId: number): Promise {\n const result = await sqlite.execute(\n `INSERT INTO ${TABLE_NAMES.POLLS} (title, description, creator_id, expires_at) \n VALUES (?, ?, ?, ?)`,\n [poll.title, poll.description, creatorId, poll.expiresAt || null]\n );\n \n const pollId = result.lastInsertRowId;\n \n // Insert poll options\n for (const optionText of poll.options) {\n await sqlite.execute(\n `INSERT INTO ${TABLE_NAMES.POLL_OPTIONS} (poll_id, text) VALUES (?, ?)`,\n [pollId, optionText]\n );\n }\n \n return pollId;\n}\n\nexport async function getAllPolls(): Promise {\n const result = await sqlite.execute(`\n SELECT \n p.id, p.title, p.description, p.creator_id as creatorId,\n u.username as creatorUsername, p.is_active as isActive,\n p.created_at as createdAt, p.expires_at as expiresAt\n FROM ${TABLE_NAMES.POLLS} p\n JOIN ${TABLE_NAMES.USERS} u ON p.creator_id = u.id\n ORDER BY p.created_at DESC\n `);\n \n const polls = result.rows as Poll[];\n \n // Get options for each poll\n for (const poll of polls) {\n const optionsResult = await sqlite.execute(`\n SELECT \n o.id, o.poll_id as pollId, o.text,\n COUNT(v.id) as voteCount\n FROM ${TABLE_NAMES.POLL_OPTIONS} o\n LEFT JOIN ${TABLE_NAMES.VOTES} v ON o.id = v.option_id\n WHERE o.poll_id = ?\n GROUP BY o.id, o.poll_id, o.text\n ORDER BY o.id\n `, [poll.id]);\n \n poll.options = optionsResult.rows as PollOption[];\n }\n \n return polls;\n}\n\nexport async function getPollById(id: number): Promise {\n const result = await sqlite.execute(`\n SELECT \n p.id, p.title, p.description, p.creator_id as creatorId,\n u.username as creatorUsername, p.is_active as isActive,\n p.created_at as createdAt, p.expires_at as expiresAt\n FROM ${TABLE_NAMES.POLLS} p\n JOIN ${TABLE_NAMES.USERS} u ON p.creator_id = u.id\n WHERE p.id = ?\n `, [id]);\n \n const poll = result.rows[0] as Poll;\n if (!poll) return null;\n \n // Get options with vote counts\n const optionsResult = await sqlite.execute(`\n SELECT \n o.id, o.poll_id as pollId, o.text,\n COUNT(v.id) as voteCount\n FROM ${TABLE_NAMES.POLL_OPTIONS} o\n LEFT JOIN ${TABLE_NAMES.VOTES} v ON o.id = v.option_id\n WHERE o.poll_id = ?\n GROUP BY o.id, o.poll_id, o.text\n ORDER BY o.id\n `, [id]);\n \n poll.options = optionsResult.rows as PollOption[];\n return poll;\n}\n\nexport async function deletePoll(id: number, userId: number): Promise {\n // Check if user owns the poll or is admin\n const result = await sqlite.execute(`\n SELECT p.creator_id, u.is_admin \n FROM ${TABLE_NAMES.POLLS} p, ${TABLE_NAMES.USERS} u \n WHERE p.id = ? AND u.id = ?\n `, [id, userId]);\n \n const row = result.rows[0];\n if (!row || (row.creator_id !== userId && !row.is_admin)) {\n return false;\n }\n \n await sqlite.execute(`DELETE FROM ${TABLE_NAMES.POLLS} WHERE id = ?`, [id]);\n return true;\n}\n\n// Vote queries\nexport async function castVote(pollId: number, optionId: number, userId: number): Promise {\n try {\n // Check if poll is active and not expired\n const pollResult = await sqlite.execute(`\n SELECT is_active, expires_at FROM ${TABLE_NAMES.POLLS} WHERE id = ?\n `, [pollId]);\n \n const poll = pollResult.rows[0];\n if (!poll || !poll.is_active) return false;\n \n if (poll.expires_at && new Date(poll.expires_at) < new Date()) {\n return false;\n }\n \n // Check if option belongs to poll\n const optionResult = await sqlite.execute(`\n SELECT id FROM ${TABLE_NAMES.POLL_OPTIONS} WHERE id = ? AND poll_id = ?\n `, [optionId, pollId]);\n \n if (optionResult.rows.length === 0) return false;\n \n // Insert vote (will fail if user already voted due to UNIQUE constraint)\n await sqlite.execute(`\n INSERT INTO ${TABLE_NAMES.VOTES} (poll_id, option_id, user_id) \n VALUES (?, ?, ?)\n `, [pollId, optionId, userId]);\n \n return true;\n } catch (error) {\n // Handle duplicate vote error\n if (error.message.includes('UNIQUE constraint failed')) {\n return false;\n }\n throw error;\n }\n}\n\nexport async function getUserVoteForPoll(pollId: number, userId: number): Promise {\n const result = await sqlite.execute(`\n SELECT id, poll_id as pollId, option_id as optionId, user_id as userId, created_at as createdAt\n FROM ${TABLE_NAMES.VOTES} \n 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) {\n const poll = await getPollById(pollId);\n if (!poll) return null;\n \n const totalVotes = poll.options.reduce((sum, option) => sum + option.voteCount, 0);\n \n const options = poll.options.map(option => ({\n id: option.id,\n text: option.text,\n voteCount: option.voteCount,\n percentage: totalVotes > 0 ? Math.round((option.voteCount / totalVotes) * 100) : 0\n }));\n \n return {\n poll,\n totalVotes,\n options\n };\n}","deleted","label",[175,176,158],"backend","database","permalink","https://www.val.town/x/ikeali/untitled-1496/code/backend/database/queries.ts","extension","actionData","errors"]