[{"_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","703ea01c1077155a6b2e005f3a31351a5b041073","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,"_170":171,"_172":-5},"handle","stevekrouse","name","rate-connections","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","812ba8fa-5423-11f0-be45-f69ea79377d9","createdAt",["D",1751117191617],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","a0bf3b31-15a5-4d5c-880e-4b1e22c9bc18","pinnedFileId","pinPreview","screenshotVersion","likeCount","e81e7335-6d4c-4764-a902-d0962bbfa9f7","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_93":94,"_127":128},"profileImageUrl","https://images.clerk.dev/uploaded/img_2PqHa2Gsy93xQrjh2w78Xu0cChW.jpeg","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",80,"812c5778-5423-11f0-be45-f69ea79377d9","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":160,"_161":162,"_163":164,"_165":20,"_166":-5,"_97":167},"36f35fd6-542c-11f0-a602-f69ea79377d9","queries.ts","fileId","9898ae10-5424-11f0-b2b5-f69ea79377d9","parentId","284a92b9-076e-46ad-8b25-a750683fd6ac","branchId","branchVersion",79,"latest",true,["D",1751120932556],8,"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport { PUZZLES_TABLE, VOTES_TABLE, AI_SEEDS_TABLE } from \"./migrations.ts\";\nimport type { Puzzle, Vote, AISeed, PuzzleWithStats } from \"../../shared/utils.ts\";\nimport { calculateOverallAverage } from \"../../shared/utils.ts\";\n\n// Create a new puzzle\nexport async function createPuzzle(date: string, categories: string[], categoryMembers: string[][]): Promise {\n const result = await sqlite.execute(\n `INSERT INTO ${PUZZLES_TABLE} (date, categories, category_members) VALUES (?, ?, ?) RETURNING id`,\n [date, JSON.stringify(categories), JSON.stringify(categoryMembers)]\n );\n return result.rows[0].id as number;\n}\n\n// Get puzzle by date\nexport async function getPuzzleByDate(date: string): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${PUZZLES_TABLE} WHERE date = ?`,\n [date]\n );\n \n if (result.rows.length === 0) return null;\n \n const row = result.rows[0];\n return {\n id: row.id as number,\n date: row.date as string,\n categories: JSON.parse(row.categories as string),\n category_members: JSON.parse(row.category_members as string),\n overall_average: row.overall_average as number,\n total_votes: row.total_votes as number,\n created_at: row.created_at as string,\n };\n}\n\n// Get puzzle by ID\nexport async function getPuzzleById(id: number): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${PUZZLES_TABLE} WHERE id = ?`,\n [id]\n );\n \n if (result.rows.length === 0) return null;\n \n const row = result.rows[0];\n return {\n id: row.id as number,\n date: row.date as string,\n categories: JSON.parse(row.categories as string),\n category_members: JSON.parse(row.category_members as string),\n overall_average: row.overall_average as number,\n total_votes: row.total_votes as number,\n created_at: row.created_at as string,\n };\n}\n\n// Get all puzzles ordered by date (newest first)\nexport async function getAllPuzzles(): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${PUZZLES_TABLE} ORDER BY date DESC`\n );\n \n return result.rows.map(row => ({\n id: row.id as number,\n date: row.date as string,\n categories: JSON.parse(row.categories as string),\n category_members: JSON.parse(row.category_members as string),\n overall_average: row.overall_average as number,\n total_votes: row.total_votes as number,\n created_at: row.created_at as string,\n }));\n}\n\n// Get puzzle with detailed stats including category averages\nexport async function getPuzzleWithStats(date: string, ipHash?: string): Promise {\n const puzzle = await getPuzzleByDate(date);\n if (!puzzle) return null;\n\n // Get category averages and vote counts\n const categoryStats = await sqlite.execute(`\n SELECT \n category_index,\n AVG(rating) as average,\n COUNT(*) as vote_count\n FROM ${VOTES_TABLE} \n WHERE puzzle_id = ? \n GROUP BY category_index\n ORDER BY category_index\n `, [puzzle.id]);\n\n // Initialize arrays for all 4 categories\n const category_averages = [0, 0, 0, 0];\n const category_vote_counts = [0, 0, 0, 0];\n\n // Fill in actual data\n for (const row of categoryStats.rows) {\n const index = row.category_index as number;\n category_averages[index] = Math.round((row.average as number) * 100) / 100;\n category_vote_counts[index] = row.vote_count as number;\n }\n\n // Get AI seed data if no votes exist for a category\n const aiSeed = await getAISeed(puzzle.id);\n if (aiSeed) {\n for (let i = 0; i < 4; i++) {\n if (category_vote_counts[i] === 0) {\n category_averages[i] = aiSeed.category_ratings[i];\n // Don't count AI votes in vote count\n }\n }\n }\n\n let user_votes: number[] | undefined;\n if (ipHash) {\n const userVoteResult = await sqlite.execute(`\n SELECT category_index, rating \n FROM ${VOTES_TABLE} \n WHERE puzzle_id = ? AND ip_hash = ?\n ORDER BY category_index\n `, [puzzle.id, ipHash]);\n\n if (userVoteResult.rows.length > 0) {\n user_votes = [0, 0, 0, 0];\n for (const row of userVoteResult.rows) {\n user_votes[row.category_index as number] = row.rating as number;\n }\n }\n }\n\n return {\n ...puzzle,\n category_averages,\n category_vote_counts,\n user_votes,\n };\n}\n\n// Submit a vote for a category\nexport async function submitVote(\n puzzleId: number, \n categoryIndex: number, \n rating: number, \n ipHash: string\n): Promise<{ success: boolean; error?: string }> {\n try {\n // Check if user already voted for this category\n const existingVote = await sqlite.execute(\n `SELECT id FROM ${VOTES_TABLE} WHERE puzzle_id = ? AND category_index = ? AND ip_hash = ?`,\n [puzzleId, categoryIndex, ipHash]\n );\n\n if (existingVote.rows.length > 0) {\n return { success: false, error: \"You have already voted for this category\" };\n }\n\n // Insert the vote\n await sqlite.execute(\n `INSERT INTO ${VOTES_TABLE} (puzzle_id, category_index, rating, ip_hash) VALUES (?, ?, ?, ?)`,\n [puzzleId, categoryIndex, rating, ipHash]\n );\n\n // Recalculate puzzle averages\n await recalculatePuzzleAverages(puzzleId);\n\n return { success: true };\n } catch (error) {\n console.error(\"Error submitting vote:\", error);\n return { success: false, error: \"Failed to submit vote. Please try again.\" };\n }\n}\n\n// Check what categories a user has voted on for a puzzle\nexport async function getUserVotes(puzzleId: number, ipHash: string): Promise {\n const result = await sqlite.execute(\n `SELECT category_index, rating FROM ${VOTES_TABLE} WHERE puzzle_id = ? AND ip_hash = ? ORDER BY category_index`,\n [puzzleId, ipHash]\n );\n\n const votes = [0, 0, 0, 0]; // 0 means no vote\n for (const row of result.rows) {\n votes[row.category_index as number] = row.rating as number;\n }\n return votes;\n}\n\n// Store AI seed ratings for a puzzle\nexport async function storeAISeed(puzzleId: number, categoryRatings: number[]): Promise {\n await sqlite.execute(\n `INSERT OR REPLACE INTO ${AI_SEEDS_TABLE} (puzzle_id, category_ratings) VALUES (?, ?)`,\n [puzzleId, JSON.stringify(categoryRatings)]\n );\n}\n\n// Get AI seed ratings for a puzzle\nexport async function getAISeed(puzzleId: number): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${AI_SEEDS_TABLE} WHERE puzzle_id = ?`,\n [puzzleId]\n );\n\n if (result.rows.length === 0) return null;\n\n const row = result.rows[0];\n return {\n id: row.id as number,\n puzzle_id: row.puzzle_id as number,\n category_ratings: JSON.parse(row.category_ratings as string),\n created_at: row.created_at as string,\n };\n}\n\n// Get leaderboard (puzzles ranked by overall average, newest ties first)\nexport async function getLeaderboard(): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${PUZZLES_TABLE} \n WHERE total_votes > 0 \n ORDER BY overall_average DESC, date DESC`\n );\n\n return result.rows.map(row => ({\n id: row.id as number,\n date: row.date as string,\n categories: JSON.parse(row.categories as string),\n category_members: JSON.parse(row.category_members as string),\n overall_average: row.overall_average as number,\n total_votes: row.total_votes as number,\n created_at: row.created_at as string,\n }));\n}\n\n// Get worst leaderboard (puzzles ranked by lowest overall average, newest ties first)\nexport async function getWorstLeaderboard(): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${PUZZLES_TABLE} \n WHERE total_votes > 0 \n ORDER BY overall_average ASC, date DESC`\n );\n\n return result.rows.map(row => ({\n id: row.id as number,\n date: row.date as string,\n categories: JSON.parse(row.categories as string),\n category_members: JSON.parse(row.category_members as string),\n overall_average: row.overall_average as number,\n total_votes: row.total_votes as number,\n created_at: row.created_at as string,\n }));\n}\n\n// Recalculate puzzle averages after new votes\nasync function recalculatePuzzleAverages(puzzleId: number): Promise {\n // Get category averages\n const categoryStats = await sqlite.execute(`\n SELECT \n category_index,\n AVG(rating) as average,\n COUNT(*) as vote_count\n FROM ${VOTES_TABLE} \n WHERE puzzle_id = ? \n GROUP BY category_index\n `, [puzzleId]);\n\n const categoryAverages = [0, 0, 0, 0];\n\n // Fill in vote-based averages\n for (const row of categoryStats.rows) {\n const index = row.category_index as number;\n categoryAverages[index] = row.average as number;\n }\n\n // For categories with no votes, use AI seed\n const aiSeed = await getAISeed(puzzleId);\n if (aiSeed) {\n for (let i = 0; i < 4; i++) {\n if (categoryAverages[i] === 0) {\n categoryAverages[i] = aiSeed.category_ratings[i];\n }\n }\n }\n\n // Count unique users who have voted (not individual category votes)\n const uniqueVotersResult = await sqlite.execute(`\n SELECT COUNT(DISTINCT ip_hash) as unique_voters\n FROM ${VOTES_TABLE} \n WHERE puzzle_id = ?\n `, [puzzleId]);\n\n const totalVotes = uniqueVotersResult.rows[0].unique_voters as number;\n const overallAverage = calculateOverallAverage(categoryAverages);\n\n // Update puzzle record\n await sqlite.execute(\n `UPDATE ${PUZZLES_TABLE} SET overall_average = ?, total_votes = ? WHERE id = ?`,\n [overallAverage, totalVotes, puzzleId]\n );\n}","deleted","label",[168,169,149],"backend","database","permalink","https://www.val.town/x/stevekrouse/rate-connections/code/backend/database/queries.ts","extension","actionData","errors"]