[{"_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","a31a1550ad486cf340b818a53bc6ddc8c7db0295","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,"_147":148,"_170":171,"_172":-5},"handle","reubenoguta_Valtown","name","Reuben","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":-5,"_116":32,"_14":117,"_118":119,"_120":119,"_121":122,"_123":122,"_124":122,"_125":119,"_126":119,"_23":127,"_130":131,"_133":134,"_146":20},"id","52a66114-3f8d-11f0-9e1b-76b3cceeab13","createdAt",["D",1748853665804],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","e2684160-3f8c-11f0-b6e9-ab0b2f88b858","pinnedFileId","pinPreview","pinMigrated","screenshotVersion","likeCount","ac4fbd36-fbab-4ab3-968f-89f2feacb060","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_93":94,"_128":129},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvb2F1dGhfZ2l0aHViL2ltZ18yeHdlQWp1Y0xGZ2FJbm9XUzl2d2JhMFVFcHkifQ","org",{"_93":94,"_132":129,"_12":111},"avatar","branch",{"_135":136,"_95":43,"_101":137,"_138":102,"_139":-5,"_140":-5,"_141":-5,"_142":-5,"_143":-5,"_144":96,"_145":94},"version",19,"52a6d0ea-3f8d-11f0-9e1b-76b3cceeab13","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_101":149,"_95":150,"_151":152,"_153":154,"_155":137,"_156":157,"_158":159,"_103":160,"_135":32,"_161":162,"_163":164,"_165":20,"_166":-5,"_97":167},"bbd851c4-3f8d-11f0-a7c2-76b3cceeab13","queries.ts","fileId","bbd852a0-3f8d-11f0-a7c2-76b3cceeab13","parentId","0032a71f-ac82-4711-9203-c63ed2dffc2c","branchId","branchVersion",4,"latest",true,["D",1748853842289],"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport type { User, Course, Exam, Question, ExamSubmission, Enrollment } from \"../../shared/types.ts\";\n\n// User queries\nexport async function createUser(email: string, passwordHash: string, name: string, role: 'student' | 'instructor'): Promise {\n const result = await sqlite.execute(\n `INSERT INTO users (email, password_hash, name, role) VALUES (?, ?, ?, ?) RETURNING *`,\n [email, passwordHash, name, role]\n );\n return result.rows[0] as User;\n}\n\nexport async function getUserByEmail(email: string): Promise {\n const result = await sqlite.execute(`SELECT * FROM users WHERE email = ?`, [email]);\n return result.rows[0] as User || null;\n}\n\nexport async function getUserById(id: number): Promise {\n const result = await sqlite.execute(`SELECT * FROM users WHERE id = ?`, [id]);\n return result.rows[0] as User || null;\n}\n\nexport async function getUserPasswordHash(email: string): Promise {\n const result = await sqlite.execute(`SELECT password_hash FROM users WHERE email = ?`, [email]);\n return result.rows[0]?.password_hash || null;\n}\n\n// Session queries\nexport async function createSession(sessionId: string, userId: number, expiresAt: Date): Promise {\n await sqlite.execute(\n `INSERT INTO sessions (id, user_id, expires_at) VALUES (?, ?, ?)`,\n [sessionId, userId, expiresAt.toISOString()]\n );\n}\n\nexport async function getSessionUser(sessionId: string): Promise {\n const result = await sqlite.execute(\n `SELECT u.* FROM users u \n JOIN sessions s ON u.id = s.user_id \n WHERE s.id = ? AND s.expires_at > datetime('now')`,\n [sessionId]\n );\n return result.rows[0] as User || null;\n}\n\nexport async function deleteSession(sessionId: string): Promise {\n await sqlite.execute(`DELETE FROM sessions WHERE id = ?`, [sessionId]);\n}\n\n// Course queries\nexport async function createCourse(title: string, description: string, instructorId: number): Promise {\n const result = await sqlite.execute(\n `INSERT INTO courses (title, description, instructor_id) VALUES (?, ?, ?) RETURNING *`,\n [title, description, instructorId]\n );\n return result.rows[0] as Course;\n}\n\nexport async function getAllCourses(): Promise {\n const result = await sqlite.execute(\n `SELECT c.*, u.name as instructor_name \n FROM courses c \n JOIN users u ON c.instructor_id = u.id \n ORDER BY c.created_at DESC`\n );\n return result.rows as Course[];\n}\n\nexport async function getCourseById(id: number): Promise {\n const result = await sqlite.execute(\n `SELECT c.*, u.name as instructor_name \n FROM courses c \n JOIN users u ON c.instructor_id = u.id \n WHERE c.id = ?`,\n [id]\n );\n return result.rows[0] as Course || null;\n}\n\nexport async function getCoursesForUser(userId: number, role: string): Promise {\n if (role === 'instructor') {\n const result = await sqlite.execute(\n `SELECT c.*, u.name as instructor_name \n FROM courses c \n JOIN users u ON c.instructor_id = u.id \n WHERE c.instructor_id = ? \n ORDER BY c.created_at DESC`,\n [userId]\n );\n return result.rows as Course[];\n } else {\n const result = await sqlite.execute(\n `SELECT c.*, u.name as instructor_name, TRUE as enrolled\n FROM courses c \n JOIN users u ON c.instructor_id = u.id\n JOIN enrollments e ON c.id = e.course_id\n WHERE e.user_id = ? \n ORDER BY c.created_at DESC`,\n [userId]\n );\n return result.rows as Course[];\n }\n}\n\n// Enrollment queries\nexport async function enrollUser(userId: number, courseId: number): Promise {\n await sqlite.execute(\n `INSERT OR IGNORE INTO enrollments (user_id, course_id) VALUES (?, ?)`,\n [userId, courseId]\n );\n}\n\nexport async function isUserEnrolled(userId: number, courseId: number): Promise {\n const result = await sqlite.execute(\n `SELECT 1 FROM enrollments WHERE user_id = ? AND course_id = ?`,\n [userId, courseId]\n );\n return result.rows.length > 0;\n}\n\nexport async function getCourseEnrollments(courseId: number): Promise {\n const result = await sqlite.execute(\n `SELECT u.* FROM users u \n JOIN enrollments e ON u.id = e.user_id \n WHERE e.course_id = ? AND u.role = 'student'`,\n [courseId]\n );\n return result.rows as User[];\n}\n\n// Exam queries\nexport async function createExam(courseId: number, title: string, description: string, durationMinutes: number, dueDate?: string): Promise {\n const result = await sqlite.execute(\n `INSERT INTO exams (course_id, title, description, duration_minutes, due_date) \n VALUES (?, ?, ?, ?, ?) RETURNING *`,\n [courseId, title, description, durationMinutes, dueDate || null]\n );\n return result.rows[0] as Exam;\n}\n\nexport async function getExamById(id: number): Promise {\n const result = await sqlite.execute(`SELECT * FROM exams WHERE id = ?`, [id]);\n return result.rows[0] as Exam || null;\n}\n\nexport async function getCourseExams(courseId: number): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM exams WHERE course_id = ? ORDER BY created_at DESC`,\n [courseId]\n );\n return result.rows as Exam[];\n}\n\nexport async function updateExamTotalPoints(examId: number, totalPoints: number): Promise {\n await sqlite.execute(\n `UPDATE exams SET total_points = ? WHERE id = ?`,\n [totalPoints, examId]\n );\n}\n\nexport async function publishExam(examId: number): Promise {\n await sqlite.execute(\n `UPDATE exams SET is_published = TRUE WHERE id = ?`,\n [examId]\n );\n}\n\n// Question queries\nexport async function createQuestion(examId: number, type: string, questionText: string, points: number, orderIndex: number, options?: string[], correctAnswer?: string): Promise {\n const result = await sqlite.execute(\n `INSERT INTO questions (exam_id, type, question_text, points, order_index, options, correct_answer) \n VALUES (?, ?, ?, ?, ?, ?, ?) RETURNING *`,\n [examId, type, questionText, points, orderIndex, options ? JSON.stringify(options) : null, correctAnswer || null]\n );\n \n const question = result.rows[0] as any;\n if (question.options) {\n question.options = JSON.parse(question.options);\n }\n return question as Question;\n}\n\nexport async function getExamQuestions(examId: number): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM questions WHERE exam_id = ? ORDER BY order_index`,\n [examId]\n );\n \n return result.rows.map(row => {\n const question = row as any;\n if (question.options) {\n question.options = JSON.parse(question.options);\n }\n return question as Question;\n });\n}\n\n// Submission queries\nexport async function createSubmission(examId: number, userId: number, answers: Record, maxScore: number): Promise {\n const result = await sqlite.execute(\n `INSERT INTO exam_submissions (exam_id, user_id, answers, max_score) \n VALUES (?, ?, ?, ?) RETURNING *`,\n [examId, userId, JSON.stringify(answers), maxScore]\n );\n \n const submission = result.rows[0] as any;\n submission.answers = JSON.parse(submission.answers);\n return submission as ExamSubmission;\n}\n\nexport async function getSubmission(examId: number, userId: number): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM exam_submissions WHERE exam_id = ? AND user_id = ?`,\n [examId, userId]\n );\n \n if (result.rows.length === 0) return null;\n \n const submission = result.rows[0] as any;\n submission.answers = JSON.parse(submission.answers);\n return submission as ExamSubmission;\n}\n\nexport async function getExamSubmissions(examId: number): Promise<(ExamSubmission & { user_name: string })[]> {\n const result = await sqlite.execute(\n `SELECT es.*, u.name as user_name \n FROM exam_submissions es \n JOIN users u ON es.user_id = u.id \n WHERE es.exam_id = ? \n ORDER BY es.submitted_at DESC`,\n [examId]\n );\n \n return result.rows.map(row => {\n const submission = row as any;\n submission.answers = JSON.parse(submission.answers);\n return submission;\n });\n}\n\nexport async function updateSubmissionScore(submissionId: number, score: number, gradedBy: number): Promise {\n await sqlite.execute(\n `UPDATE exam_submissions \n SET score = ?, graded_at = datetime('now'), graded_by = ? \n WHERE id = ?`,\n [score, gradedBy, submissionId]\n );\n}","deleted","label",[168,169,150],"backend","database","permalink","https://www.val.town/x/reubenoguta_Valtown/Reuben/code/backend/database/queries.ts","extension","actionData","errors"]