[{"_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","7037b4412b49b7328160fa57e59f17dfe8779fc7","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","max123","name","screen","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","aaad78d6-3725-11f0-b398-9e149126039e","createdAt",["D",1747929536528],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","b20cb430-de7d-11ef-b8a9-410e9ba48836","pinnedFileId","pinPreview","screenshotVersion","likeCount","41349328-9491-495d-8f1c-617e62a9ac05","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_93":94,"_127":128},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoiZGVmYXVsdCIsImlpZCI6Imluc18yTm10cGhQUklwU0tqZHNiOHNnNGd2ZjJURFUiLCJyaWQiOiJ1c2VyXzJzSmxoQW1GMDdvejNJRkR0dVZkbWZWQjg0RyJ9","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",19,"aaadad6a-3725-11f0-b398-9e149126039e","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},"d4deaf4c-37fe-11f0-814d-9e149126039e","queries.ts","fileId","d4deafe2-37fe-11f0-814d-9e149126039e","parentId","741398a1-fb59-4e89-b344-50a41cff962b","branchId","branchVersion",4,"latest",true,["D",1748022808104],"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport type { Site, Court, Game, Player, PointLog, CreateGameRequest, AddPointRequest } from \"../../shared/types.ts\";\n\n// Site and Court queries\nexport async function getAllSites(): Promise {\n const sites = await sqlite.execute(\"SELECT * FROM sites ORDER BY name\");\n const courts = await sqlite.execute(\"SELECT * FROM courts ORDER BY site_id, number\");\n \n return sites.map(site => ({\n id: site.id,\n name: site.name,\n courts: courts\n .filter(court => court.site_id === site.id)\n .map(court => ({\n id: court.id,\n number: court.number,\n siteId: court.site_id,\n isActive: Boolean(court.is_active)\n }))\n }));\n}\n\nexport async function getCourtById(courtId: string): Promise {\n const result = await sqlite.execute(\n \"SELECT * FROM courts WHERE id = ?\",\n [courtId]\n );\n \n if (result.length === 0) return null;\n \n const court = result[0];\n return {\n id: court.id,\n number: court.number,\n siteId: court.site_id,\n isActive: Boolean(court.is_active)\n };\n}\n\n// Player queries\nexport async function createOrGetPlayer(name: string): Promise {\n // Check if player exists\n const existing = await sqlite.execute(\n \"SELECT * FROM players WHERE name = ?\",\n [name]\n );\n \n if (existing.length > 0) {\n return {\n id: existing[0].id,\n name: existing[0].name,\n team: 'team1' // Will be set by caller\n };\n }\n \n // Create new player\n const playerId = `player-${Date.now()}-${Math.random().toString(36).substr(2, 9)}`;\n await sqlite.execute(\n \"INSERT INTO players (id, name) VALUES (?, ?)\",\n [playerId, name]\n );\n \n return {\n id: playerId,\n name,\n team: 'team1' // Will be set by caller\n };\n}\n\nexport async function getPlayerById(playerId: string): Promise {\n const result = await sqlite.execute(\n \"SELECT * FROM players WHERE id = ?\",\n [playerId]\n );\n \n if (result.length === 0) return null;\n \n return {\n id: result[0].id,\n name: result[0].name,\n team: 'team1' // Will be set by caller\n };\n}\n\n// Game queries\nexport async function createGame(request: CreateGameRequest): Promise {\n const gameId = `game-${Date.now()}-${Math.random().toString(36).substr(2, 9)}`;\n \n // Create or get players\n const team1Player1 = await createOrGetPlayer(request.team1Player1);\n const team1Player2 = request.team1Player2 ? await createOrGetPlayer(request.team1Player2) : null;\n const team2Player1 = await createOrGetPlayer(request.team2Player1);\n const team2Player2 = request.team2Player2 ? await createOrGetPlayer(request.team2Player2) : null;\n \n // Insert game\n await sqlite.execute(`\n INSERT INTO games (\n id, court_id, site_id, game_type, game_format,\n team1_player1_id, team1_player2_id, team2_player1_id, team2_player2_id,\n current_server, server_number\n ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\n `, [\n gameId, request.courtId, request.siteId, request.gameType, request.gameFormat,\n team1Player1.id, team1Player2?.id || null, team2Player1.id, team2Player2?.id || null,\n 'team1', 1\n ]);\n \n return await getGameById(gameId) as Game;\n}\n\nexport async function getGameById(gameId: string): Promise {\n const result = await sqlite.execute(`\n SELECT g.*, \n p1.name as team1_player1_name,\n p2.name as team1_player2_name,\n p3.name as team2_player1_name,\n p4.name as team2_player2_name\n FROM games g\n LEFT JOIN players p1 ON g.team1_player1_id = p1.id\n LEFT JOIN players p2 ON g.team1_player2_id = p2.id\n LEFT JOIN players p3 ON g.team2_player1_id = p3.id\n LEFT JOIN players p4 ON g.team2_player2_id = p4.id\n WHERE g.id = ?\n `, [gameId]);\n \n if (result.length === 0) return null;\n \n const game = result[0];\n \n return {\n id: game.id,\n courtId: game.court_id,\n siteId: game.site_id,\n gameType: game.game_type,\n gameFormat: game.game_format,\n team1: {\n player1: { id: game.team1_player1_id, name: game.team1_player1_name, team: 'team1' },\n player2: game.team1_player2_id ? { id: game.team1_player2_id, name: game.team1_player2_name, team: 'team1' } : undefined,\n score: game.team1_score\n },\n team2: {\n player1: { id: game.team2_player1_id, name: game.team2_player1_name, team: 'team2' },\n player2: game.team2_player2_id ? { id: game.team2_player2_id, name: game.team2_player2_name, team: 'team2' } : undefined,\n score: game.team2_score\n },\n currentServer: game.current_server,\n serverNumber: game.server_number,\n isActive: Boolean(game.is_active),\n isComplete: Boolean(game.is_complete),\n startTime: game.start_time,\n endTime: game.end_time,\n pointHistory: []\n };\n}\n\nexport async function getAllActiveGames(): Promise {\n const result = await sqlite.execute(`\n SELECT g.*, \n p1.name as team1_player1_name,\n p2.name as team1_player2_name,\n p3.name as team2_player1_name,\n p4.name as team2_player2_name\n FROM games g\n LEFT JOIN players p1 ON g.team1_player1_id = p1.id\n LEFT JOIN players p2 ON g.team1_player2_id = p2.id\n LEFT JOIN players p3 ON g.team2_player1_id = p3.id\n LEFT JOIN players p4 ON g.team2_player2_id = p4.id\n WHERE g.is_active = true\n ORDER BY g.start_time DESC\n `);\n \n return result.map(game => ({\n id: game.id,\n courtId: game.court_id,\n siteId: game.site_id,\n gameType: game.game_type,\n gameFormat: game.game_format,\n team1: {\n player1: { id: game.team1_player1_id, name: game.team1_player1_name, team: 'team1' },\n player2: game.team1_player2_id ? { id: game.team1_player2_id, name: game.team1_player2_name, team: 'team1' } : undefined,\n score: game.team1_score\n },\n team2: {\n player1: { id: game.team2_player1_id, name: game.team2_player1_name, team: 'team2' },\n player2: game.team2_player2_id ? { id: game.team2_player2_id, name: game.team2_player2_name, team: 'team2' } : undefined,\n score: game.team2_score\n },\n currentServer: game.current_server,\n serverNumber: game.server_number,\n isActive: Boolean(game.is_active),\n isComplete: Boolean(game.is_complete),\n startTime: game.start_time,\n endTime: game.end_time,\n pointHistory: []\n }));\n}\n\nexport async function getGamesByCourt(courtId: string): Promise {\n const result = await sqlite.execute(`\n SELECT g.*, \n p1.name as team1_player1_name,\n p2.name as team1_player2_name,\n p3.name as team2_player1_name,\n p4.name as team2_player2_name\n FROM games g\n LEFT JOIN players p1 ON g.team1_player1_id = p1.id\n LEFT JOIN players p2 ON g.team1_player2_id = p2.id\n LEFT JOIN players p3 ON g.team2_player1_id = p3.id\n LEFT JOIN players p4 ON g.team2_player2_id = p4.id\n WHERE g.court_id = ? AND g.is_active = true\n ORDER BY g.start_time DESC\n `, [courtId]);\n \n return result.map(game => ({\n id: game.id,\n courtId: game.court_id,\n siteId: game.site_id,\n gameType: game.game_type,\n gameFormat: game.game_format,\n team1: {\n player1: { id: game.team1_player1_id, name: game.team1_player1_name, team: 'team1' },\n player2: game.team1_player2_id ? { id: game.team1_player2_id, name: game.team1_player2_name, team: 'team1' } : undefined,\n score: game.team1_score\n },\n team2: {\n player1: { id: game.team2_player1_id, name: game.team2_player1_name, team: 'team2' },\n player2: game.team2_player2_id ? { id: game.team2_player2_id, name: game.team2_player2_name, team: 'team2' } : undefined,\n score: game.team2_score\n },\n currentServer: game.current_server,\n serverNumber: game.server_number,\n isActive: Boolean(game.is_active),\n isComplete: Boolean(game.is_complete),\n startTime: game.start_time,\n endTime: game.end_time,\n pointHistory: []\n }));\n}\n\n// Point logging\nexport async function addPoint(request: AddPointRequest): Promise {\n const game = await getGameById(request.gameId);\n if (!game) throw new Error(\"Game not found\");\n \n const pointId = `point-${Date.now()}-${Math.random().toString(36).substr(2, 9)}`;\n \n // Calculate new scores\n let newTeam1Score = game.team1.score;\n let newTeam2Score = game.team2.score;\n \n if (request.scoringTeam === 'team1') {\n newTeam1Score++;\n } else {\n newTeam2Score++;\n }\n \n // Determine next server based on pickleball rules\n let nextServer = game.currentServer;\n let nextServerNumber = game.serverNumber;\n \n if (request.scoringTeam !== game.currentServer) {\n // Side out - switch serving team\n nextServer = request.scoringTeam;\n nextServerNumber = 1;\n } else {\n // Same team scored, rotate server if doubles\n if (game.gameType === 'doubles') {\n nextServerNumber = nextServerNumber === 1 ? 2 : 1;\n }\n }\n \n // Check if game is complete\n const targetScore = game.gameFormat === 'standard' ? 11 : \n game.gameFormat === 'tournament15' ? 15 : 21;\n const isComplete = (newTeam1Score >= targetScore || newTeam2Score >= targetScore) &&\n Math.abs(newTeam1Score - newTeam2Score) >= 2;\n \n // Insert point log\n await sqlite.execute(`\n INSERT INTO point_logs (\n id, game_id, scoring_team, team1_score, team2_score,\n server_team, server_number, point_type, description\n ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)\n `, [\n pointId, request.gameId, request.scoringTeam, newTeam1Score, newTeam2Score,\n nextServer, nextServerNumber, request.pointType, request.description || null\n ]);\n \n // Update game\n await sqlite.execute(`\n UPDATE games SET \n team1_score = ?, team2_score = ?, current_server = ?, server_number = ?,\n is_complete = ?, end_time = ?\n WHERE id = ?\n `, [\n newTeam1Score, newTeam2Score, nextServer, nextServerNumber,\n isComplete, isComplete ? new Date().toISOString() : null, request.gameId\n ]);\n \n // Return point log\n const pointResult = await sqlite.execute(\n \"SELECT * FROM point_logs WHERE id = ?\",\n [pointId]\n );\n \n const point = pointResult[0];\n return {\n id: point.id,\n gameId: point.game_id,\n timestamp: point.timestamp,\n scoringTeam: point.scoring_team,\n team1Score: point.team1_score,\n team2Score: point.team2_score,\n serverTeam: point.server_team,\n serverNumber: point.server_number,\n pointType: point.point_type,\n description: point.description\n };\n}\n\nexport async function getPointHistory(gameId: string): Promise {\n const result = await sqlite.execute(\n \"SELECT * FROM point_logs WHERE game_id = ? ORDER BY timestamp\",\n [gameId]\n );\n \n return result.map(point => ({\n id: point.id,\n gameId: point.game_id,\n timestamp: point.timestamp,\n scoringTeam: point.scoring_team,\n team1Score: point.team1_score,\n team2Score: point.team2_score,\n serverTeam: point.server_team,\n serverNumber: point.server_number,\n pointType: point.point_type,\n description: point.description\n }));\n}\n\nexport async function completeGame(gameId: string): Promise {\n await sqlite.execute(`\n UPDATE games SET \n is_active = false, is_complete = true, end_time = ?\n WHERE id = ?\n `, [new Date().toISOString(), gameId]);\n}","deleted","label",[167,168,149],"backend","database","permalink","https://www.val.town/x/max123/screen/code/backend/database/queries.ts","extension","actionData","errors"]