[{"_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","569c2cab8ded44a221d3e33107e42257e157d49b","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","join","name","concept","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","93ec7e24-32ef-11f0-84b3-569c3dd06744","createdAt",["D",1747466500878],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","2c50f100-32ef-11f0-b519-ff88f6bc2fe6","pinnedFileId","pinPreview","screenshotVersion","likeCount","337daaab-8c3d-4b0e-843a-d7fe5ed4748b","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_93":94,"_127":128},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoiZGVmYXVsdCIsImlpZCI6Imluc18yTm10cGhQUklwU0tqZHNiOHNnNGd2ZjJURFUiLCJyaWQiOiJ1c2VyXzJ4RElTYm91QXRtUWhBeEdMM1RYYUVmMVVwYSJ9","org",{"_93":94,"_131":128,"_12":111},"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",16,"93ecd2fc-32ef-11f0-84b3-569c3dd06744","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},"ecc938ac-32ef-11f0-ba12-569c3dd06744","queries.ts","fileId","ecc93942-32ef-11f0-ba12-569c3dd06744","parentId","43614158-dfce-40dc-999e-2eb1ae24d1e0","branchId","branchVersion",4,"latest",true,["D",1747466649959],"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport { Tables } from \"./migrations\";\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 = 'break_and_run' | 'rack_runout' | 'foul' | 'scratch_on_break' | 'money_ball_miss' | '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(`SELECT * FROM ${Tables.PLAYERS} ORDER BY last_name, first_name`);\n return result.rows as Player[];\n}\n\nexport async function getPlayerById(id: number): Promise {\n const result = await sqlite.execute(`SELECT * FROM ${Tables.PLAYERS} WHERE id = ?`, [id]);\n return result.rows.length > 0 ? result.rows[0] as Player : null;\n}\n\nexport async function createPlayer(player: Omit): 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 [player.first_name, player.last_name, player.nickname, player.fargo_id, player.email]\n );\n return result.rows[0].id;\n}\n\nexport async function updatePlayer(id: number, player: Partial>): 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(`SELECT * FROM ${Tables.TOURNAMENTS} ORDER BY start_date DESC`);\n return result.rows as Tournament[];\n}\n\nexport async function getTournamentById(id: number): Promise {\n const result = await sqlite.execute(`SELECT * FROM ${Tables.TOURNAMENTS} WHERE id = ?`, [id]);\n return result.rows.length > 0 ? result.rows[0] as Tournament : null;\n}\n\nexport async function createTournament(tournament: Omit): 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 [tournament.name, tournament.location, tournament.start_date, tournament.end_date, tournament.director_id, tournament.status]\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(`SELECT * FROM ${Tables.MATCHES} WHERE id = ?`, [id]);\n return result.rows.length > 0 ? result.rows[0] as Match : null;\n}\n\nexport async function createMatch(match: Omit): 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, match.player_a_id, match.player_b_id, match.race_to,\n match.player_a_score, match.player_b_score, match.game_type, match.table_size,\n match.status, match.winner_id\n ]\n );\n return result.rows[0].id;\n}\n\nexport async function updateMatchScore(id: number, playerAScore: number, playerBScore: number, winnerId?: number): 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(rack: Omit): 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(`DELETE FROM ${Tables.RACKS} WHERE id = ?`, [id]);\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(event: Omit): 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",[167,168,149],"backend","database","permalink","https://www.val.town/x/join/concept/code/backend/database/queries.ts","extension","actionData","errors"]