[{"_1":2,"_180":-5,"_181":-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","8a08d4a46a197c541e82d5fac862bafdcf40c111","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,"_154":155,"_177":178,"_179":-5},"handle","volt","name","concept","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":114,"_115":116,"_117":-5,"_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,"_153":20},"id","50a38506-de73-11f0-a915-42dde27851f2","createdAt",["D",1766324730365],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","0199b749-1e1c-7399-b093-a7ba99382572","pinnedFileId","019b4128-7a3d-7283-9b6f-a6f01c56afab","pinPreview",true,"pinMigrated","screenshotVersion","likeCount","0199b749-1e1c-7399-b093-a97ac9fff69e","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_94":95,"_131":132},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoiZGVmYXVsdCIsImlpZCI6Imluc18yTm10cGhQUklwU0tqZHNiOHNnNGd2ZjJURFUiLCJyaWQiOiJ1c2VyXzMzZm1qMllGY2JhQmhhWFYwVXVvQ05aT2tJciJ9","org",{"_94":95,"_135":132,"_12":112},"avatar","branch",{"_138":139,"_96":44,"_102":140,"_141":103,"_142":143,"_144":145,"_146":109,"_147":148,"_149":150,"_151":97,"_152":95},"version",10,"50a6c158-de73-11f0-a915-42dde27851f2","projectId","forkedBranchId","93ecd2fc-32ef-11f0-84b3-569c3dd06744","forkedBranchVersion",16,"forkedProjectPrivacy","forkedProjectId","93ec7e24-32ef-11f0-84b3-569c3dd06744","forkedProjectOrgId","337daaab-8c3d-4b0e-843a-d7fe5ed4748b","projectName","userHandle","isOwned","file",{"_102":156,"_96":157,"_158":159,"_160":161,"_162":140,"_163":164,"_165":116,"_104":166,"_138":167,"_168":169,"_170":171,"_172":20,"_173":-5,"_98":174},"034da5f6-de74-11f0-bdbb-42dde27851f2","queries.ts","fileId","019b4128-7a3d-7283-9b6f-8660c5a59971","parentId","019b4128-7a3d-7283-9b6f-89657970de5f","branchId","branchVersion",6,"latest",["D",1766325030140],1,"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport { Tables } from \"./migrations.ts\";\n\n// Types for our database entities\nexport interface User {\n id: number;\n username: string;\n email?: string;\n full_name?: string;\n created_at: number;\n updated_at: number;\n}\n\nexport interface Player {\n id: number;\n first_name: string;\n last_name: string;\n nickname?: string;\n fargo_id?: string;\n email?: string;\n created_at: number;\n updated_at: number;\n}\n\nexport interface Tournament {\n id: number;\n name: string;\n location?: string;\n start_date?: number;\n end_date?: number;\n director_id?: number;\n status: \"pending\" | \"active\" | \"completed\" | \"cancelled\";\n created_at: number;\n updated_at: number;\n}\n\nexport interface Match {\n id: number;\n tournament_id?: number;\n player_a_id: number;\n player_b_id: number;\n race_to: number;\n player_a_score: number;\n player_b_score: number;\n game_type: \"8-ball\" | \"9-ball\" | \"10-ball\";\n table_size: \"7ft\" | \"8ft\" | \"9ft\";\n status: \"pending\" | \"active\" | \"completed\" | \"cancelled\";\n winner_id?: number;\n created_at: number;\n updated_at: number;\n}\n\nexport interface Rack {\n id: number;\n match_id: number;\n rack_number: number;\n winner_id: number;\n created_at: number;\n}\n\nexport type EventType =\n | \"break_and_run\"\n | \"rack_runout\"\n | \"foul\"\n | \"scratch_on_break\"\n | \"money_ball_miss\"\n | \"safety_win\";\n\nexport interface RackEvent {\n id: number;\n rack_id: number;\n event_type: EventType;\n player_id: number;\n created_at: number;\n}\n\n// Player queries\nexport async function getPlayers(): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${Tables.PLAYERS} ORDER BY last_name, first_name`,\n );\n return result.rows as Player[];\n}\n\nexport async function getPlayerById(id: number): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${Tables.PLAYERS} WHERE id = ?`,\n [id],\n );\n return result.rows.length > 0 ? result.rows[0] as Player : null;\n}\n\nexport async function createPlayer(\n player: Omit,\n): Promise {\n const result = await sqlite.execute(\n `INSERT INTO ${Tables.PLAYERS} (first_name, last_name, nickname, fargo_id, email) \n VALUES (?, ?, ?, ?, ?)\n RETURNING id`,\n [\n player.first_name,\n player.last_name,\n player.nickname,\n player.fargo_id,\n player.email,\n ],\n );\n return result.rows[0].id;\n}\n\nexport async function updatePlayer(\n id: number,\n player: Partial>,\n): Promise {\n const fields = Object.keys(player).map((key) => `${key} = ?`).join(\", \");\n const values = Object.values(player);\n\n if (fields.length === 0) return false;\n\n const result = await sqlite.execute(\n `UPDATE ${Tables.PLAYERS} \n SET ${fields}, updated_at = strftime('%s', 'now')\n WHERE id = ?`,\n [...values, id],\n );\n\n return result.rowsAffected > 0;\n}\n\n// Tournament queries\nexport async function getTournaments(): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${Tables.TOURNAMENTS} ORDER BY start_date DESC`,\n );\n return result.rows as Tournament[];\n}\n\nexport async function getTournamentById(\n id: number,\n): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${Tables.TOURNAMENTS} WHERE id = ?`,\n [id],\n );\n return result.rows.length > 0 ? result.rows[0] as Tournament : null;\n}\n\nexport async function createTournament(\n tournament: Omit,\n): Promise {\n const result = await sqlite.execute(\n `INSERT INTO ${Tables.TOURNAMENTS} (name, location, start_date, end_date, director_id, status) \n VALUES (?, ?, ?, ?, ?, ?)\n RETURNING id`,\n [\n tournament.name,\n tournament.location,\n tournament.start_date,\n tournament.end_date,\n tournament.director_id,\n tournament.status,\n ],\n );\n return result.rows[0].id;\n}\n\n// Match queries\nexport async function getMatches(tournamentId?: number): Promise {\n let query = `SELECT * FROM ${Tables.MATCHES}`;\n const params = [];\n\n if (tournamentId) {\n query += ` WHERE tournament_id = ?`;\n params.push(tournamentId);\n }\n\n query += ` ORDER BY created_at DESC`;\n\n const result = await sqlite.execute(query, params);\n return result.rows as Match[];\n}\n\nexport async function getMatchById(id: number): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${Tables.MATCHES} WHERE id = ?`,\n [id],\n );\n return result.rows.length > 0 ? result.rows[0] as Match : null;\n}\n\nexport async function createMatch(\n match: Omit,\n): Promise {\n const result = await sqlite.execute(\n `INSERT INTO ${Tables.MATCHES} \n (tournament_id, player_a_id, player_b_id, race_to, player_a_score, player_b_score, \n game_type, table_size, status, winner_id) \n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\n RETURNING id`,\n [\n match.tournament_id,\n match.player_a_id,\n match.player_b_id,\n match.race_to,\n match.player_a_score,\n match.player_b_score,\n match.game_type,\n match.table_size,\n match.status,\n match.winner_id,\n ],\n );\n return result.rows[0].id;\n}\n\nexport async function updateMatchScore(\n id: number,\n playerAScore: number,\n playerBScore: number,\n winnerId?: number,\n): Promise {\n const status = winnerId ? \"completed\" : \"active\";\n\n const result = await sqlite.execute(\n `UPDATE ${Tables.MATCHES} \n SET player_a_score = ?, player_b_score = ?, winner_id = ?, status = ?, updated_at = strftime('%s', 'now')\n WHERE id = ?`,\n [playerAScore, playerBScore, winnerId, status, id],\n );\n\n return result.rowsAffected > 0;\n}\n\n// Rack queries\nexport async function getRacksByMatchId(matchId: number): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${Tables.RACKS} WHERE match_id = ? ORDER BY rack_number ASC`,\n [matchId],\n );\n return result.rows as Rack[];\n}\n\nexport async function createRack(\n rack: Omit,\n): Promise {\n const result = await sqlite.execute(\n `INSERT INTO ${Tables.RACKS} (match_id, rack_number, winner_id) \n VALUES (?, ?, ?)\n RETURNING id`,\n [rack.match_id, rack.rack_number, rack.winner_id],\n );\n return result.rows[0].id;\n}\n\nexport async function deleteRack(id: number): Promise {\n // First delete any events associated with this rack\n await sqlite.execute(`DELETE FROM ${Tables.EVENTS} WHERE rack_id = ?`, [id]);\n\n // Then delete the rack\n const result = await sqlite.execute(\n `DELETE FROM ${Tables.RACKS} WHERE id = ?`,\n [id],\n );\n return result.rowsAffected > 0;\n}\n\n// Event queries\nexport async function getEventsByRackId(rackId: number): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${Tables.EVENTS} WHERE rack_id = ?`,\n [rackId],\n );\n return result.rows as RackEvent[];\n}\n\nexport async function createEvent(\n event: Omit,\n): Promise {\n const result = await sqlite.execute(\n `INSERT INTO ${Tables.EVENTS} (rack_id, event_type, player_id) \n VALUES (?, ?, ?)\n RETURNING id`,\n [event.rack_id, event.event_type, event.player_id],\n );\n return result.rows[0].id;\n}\n\n// Player statistics\nexport async function getPlayerStats(playerId: number): Promise<{\n matches_played: number;\n matches_won: number;\n total_racks_played: number;\n total_racks_won: number;\n break_and_runs: number;\n rack_runouts: number;\n fouls: number;\n}> {\n // Get matches played and won\n const matchesResult = await sqlite.execute(\n `SELECT \n COUNT(*) as matches_played,\n SUM(CASE WHEN winner_id = ? THEN 1 ELSE 0 END) as matches_won\n FROM ${Tables.MATCHES}\n WHERE player_a_id = ? OR player_b_id = ?`,\n [playerId, playerId, playerId],\n );\n\n // Get racks played and won\n const racksResult = await sqlite.execute(\n `SELECT \n COUNT(*) as total_racks_played,\n SUM(CASE WHEN winner_id = ? THEN 1 ELSE 0 END) as total_racks_won\n FROM ${Tables.RACKS} r\n JOIN ${Tables.MATCHES} m ON r.match_id = m.id\n WHERE m.player_a_id = ? OR m.player_b_id = ?`,\n [playerId, playerId, playerId],\n );\n\n // Get special events\n const eventsResult = await sqlite.execute(\n `SELECT \n event_type,\n COUNT(*) as count\n FROM ${Tables.EVENTS}\n WHERE player_id = ?\n GROUP BY event_type`,\n [playerId],\n );\n\n // Process events into a map\n const eventCounts: Record = {};\n for (const row of eventsResult.rows) {\n eventCounts[row.event_type] = row.count;\n }\n\n return {\n matches_played: matchesResult.rows[0].matches_played || 0,\n matches_won: matchesResult.rows[0].matches_won || 0,\n total_racks_played: racksResult.rows[0].total_racks_played || 0,\n total_racks_won: racksResult.rows[0].total_racks_won || 0,\n break_and_runs: eventCounts[\"break_and_run\"] || 0,\n rack_runouts: eventCounts[\"rack_runout\"] || 0,\n fouls: eventCounts[\"foul\"] || 0,\n };\n}","deleted","label",[175,176,157],"backend","database","permalink","https://www.val.town/x/volt/concept/code/backend/database/queries.ts","extension","actionData","errors"]