[{"_1":2,"_174":-5,"_175":-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","8abab1f3bacc65052d6313ca736f41d897b49791","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,"_148":149,"_171":172,"_173":-5},"handle","Deep_25","name","untitled-9443","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":-5,"_114":20,"_115":-5,"_116":-5,"_117":32,"_14":118,"_119":120,"_121":120,"_122":123,"_124":123,"_125":123,"_126":120,"_127":120,"_23":128,"_131":132,"_134":135,"_147":20},"id","7f64a828-3f10-11f0-b71c-76b3cceeab13","createdAt",["D",1748800053779],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","61373500-3f10-11f0-ad5b-814c56c8abb0","pinnedFileId","pinPreview","pinMigrated","screenshotVersion","likeCount","ab5b8c69-9eae-4a47-9ca0-07f4c9e773ac","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_94":95,"_129":130},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvb2F1dGhfZ2l0aHViL2ltZ18yeHV0ck1YVzhIT2NwYU9RMmtoY1lmclBqcVEifQ","org",{"_94":95,"_133":130,"_12":112},"avatar","branch",{"_136":137,"_96":44,"_102":138,"_139":103,"_140":-5,"_141":-5,"_142":-5,"_143":-5,"_144":-5,"_145":97,"_146":95},"version",19,"7f64f4a4-3f10-11f0-b71c-76b3cceeab13","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_102":150,"_96":151,"_152":153,"_154":155,"_156":138,"_157":158,"_159":160,"_104":161,"_136":32,"_162":163,"_164":165,"_166":20,"_167":-5,"_98":168},"9fa6e74e-3f11-11f0-9762-76b3cceeab13","queries.ts","fileId","9fa6e898-3f11-11f0-9762-76b3cceeab13","parentId","fd5aaf18-ee6f-4d2a-b227-9852974f1eb0","branchId","branchVersion",6,"latest",true,["D",1748800537392],"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport type { Student, Teacher, Class, Grade, Attendance, DashboardStats } from \"../../shared/types.ts\";\n\n// Student queries\nexport async function getAllStudents(): Promise {\n const result = await sqlite.execute(\"SELECT * FROM students ORDER BY lastName, firstName\");\n return result.rows as Student[];\n}\n\nexport async function getStudentById(id: number): Promise {\n const result = await sqlite.execute(\"SELECT * FROM students WHERE id = ?\", [id]);\n return result.rows[0] as Student || null;\n}\n\nexport async function createStudent(student: Omit): Promise {\n const result = await sqlite.execute(`\n INSERT INTO students (firstName, lastName, email, dateOfBirth, grade, parentContact, address, status)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?)\n `, [\n student.firstName,\n student.lastName,\n student.email,\n student.dateOfBirth,\n student.grade,\n student.parentContact,\n student.address,\n student.status || 'active'\n ]);\n \n return await getStudentById(result.lastInsertRowid as number) as Student;\n}\n\nexport async function updateStudent(id: number, student: Partial): Promise {\n const fields = Object.keys(student).filter(key => key !== 'id');\n const values = fields.map(key => student[key as keyof Student]);\n const setClause = fields.map(field => `${field} = ?`).join(', ');\n \n await sqlite.execute(`\n UPDATE students SET ${setClause}, updatedAt = datetime('now') WHERE id = ?\n `, [...values, id]);\n \n return await getStudentById(id);\n}\n\nexport async function deleteStudent(id: number): Promise {\n const result = await sqlite.execute(\"DELETE FROM students WHERE id = ?\", [id]);\n return result.changes > 0;\n}\n\n// Teacher queries\nexport async function getAllTeachers(): Promise {\n const result = await sqlite.execute(\"SELECT * FROM teachers ORDER BY lastName, firstName\");\n return result.rows as Teacher[];\n}\n\nexport async function getTeacherById(id: number): Promise {\n const result = await sqlite.execute(\"SELECT * FROM teachers WHERE id = ?\", [id]);\n return result.rows[0] as Teacher || null;\n}\n\nexport async function createTeacher(teacher: Omit): Promise {\n const result = await sqlite.execute(`\n INSERT INTO teachers (firstName, lastName, email, phone, subject, department, status)\n VALUES (?, ?, ?, ?, ?, ?, ?)\n `, [\n teacher.firstName,\n teacher.lastName,\n teacher.email,\n teacher.phone,\n teacher.subject,\n teacher.department,\n teacher.status || 'active'\n ]);\n \n return await getTeacherById(result.lastInsertRowid as number) as Teacher;\n}\n\nexport async function updateTeacher(id: number, teacher: Partial): Promise {\n const fields = Object.keys(teacher).filter(key => key !== 'id');\n const values = fields.map(key => teacher[key as keyof Teacher]);\n const setClause = fields.map(field => `${field} = ?`).join(', ');\n \n await sqlite.execute(`\n UPDATE teachers SET ${setClause}, updatedAt = datetime('now') WHERE id = ?\n `, [...values, id]);\n \n return await getTeacherById(id);\n}\n\nexport async function deleteTeacher(id: number): Promise {\n const result = await sqlite.execute(\"DELETE FROM teachers WHERE id = ?\", [id]);\n return result.changes > 0;\n}\n\n// Class queries\nexport async function getAllClasses(): Promise {\n const result = await sqlite.execute(`\n SELECT c.*, t.firstName || ' ' || t.lastName as teacherName,\n COUNT(DISTINCT g.studentId) as enrolledCount\n FROM classes c\n LEFT JOIN teachers t ON c.teacherId = t.id\n LEFT JOIN grades g ON c.id = g.classId\n GROUP BY c.id\n ORDER BY c.name\n `);\n return result.rows as Class[];\n}\n\nexport async function getClassById(id: number): Promise {\n const result = await sqlite.execute(`\n SELECT c.*, t.firstName || ' ' || t.lastName as teacherName,\n COUNT(DISTINCT g.studentId) as enrolledCount\n FROM classes c\n LEFT JOIN teachers t ON c.teacherId = t.id\n LEFT JOIN grades g ON c.id = g.classId\n WHERE c.id = ?\n GROUP BY c.id\n `, [id]);\n return result.rows[0] as Class || null;\n}\n\nexport async function createClass(classData: Omit): Promise {\n const result = await sqlite.execute(`\n INSERT INTO classes (name, subject, teacherId, grade, schedule, room, capacity)\n VALUES (?, ?, ?, ?, ?, ?, ?)\n `, [\n classData.name,\n classData.subject,\n classData.teacherId,\n classData.grade,\n classData.schedule,\n classData.room,\n classData.capacity\n ]);\n \n return await getClassById(result.lastInsertRowid as number) as Class;\n}\n\nexport async function updateClass(id: number, classData: Partial): Promise {\n const fields = Object.keys(classData).filter(key => !['id', 'teacherName', 'enrolledCount'].includes(key));\n const values = fields.map(key => classData[key as keyof Class]);\n const setClause = fields.map(field => `${field} = ?`).join(', ');\n \n await sqlite.execute(`\n UPDATE classes SET ${setClause}, updatedAt = datetime('now') WHERE id = ?\n `, [...values, id]);\n \n return await getClassById(id);\n}\n\nexport async function deleteClass(id: number): Promise {\n const result = await sqlite.execute(\"DELETE FROM classes WHERE id = ?\", [id]);\n return result.changes > 0;\n}\n\n// Grade queries\nexport async function getAllGrades(): Promise {\n const result = await sqlite.execute(`\n SELECT g.*, s.firstName || ' ' || s.lastName as studentName,\n c.name as className\n FROM grades g\n LEFT JOIN students s ON g.studentId = s.id\n LEFT JOIN classes c ON g.classId = c.id\n ORDER BY g.date DESC\n `);\n return result.rows as Grade[];\n}\n\nexport async function getGradesByStudent(studentId: number): Promise {\n const result = await sqlite.execute(`\n SELECT g.*, c.name as className\n FROM grades g\n LEFT JOIN classes c ON g.classId = c.id\n WHERE g.studentId = ?\n ORDER BY g.date DESC\n `, [studentId]);\n return result.rows as Grade[];\n}\n\nexport async function getGradesByClass(classId: number): Promise {\n const result = await sqlite.execute(`\n SELECT g.*, s.firstName || ' ' || s.lastName as studentName\n FROM grades g\n LEFT JOIN students s ON g.studentId = s.id\n WHERE g.classId = ?\n ORDER BY g.date DESC\n `, [classId]);\n return result.rows as Grade[];\n}\n\nexport async function createGrade(grade: Omit): Promise {\n const result = await sqlite.execute(`\n INSERT INTO grades (studentId, classId, assignment, score, maxScore, type)\n VALUES (?, ?, ?, ?, ?, ?)\n `, [\n grade.studentId,\n grade.classId,\n grade.assignment,\n grade.score,\n grade.maxScore,\n grade.type\n ]);\n \n const newGrade = await sqlite.execute(`\n SELECT g.*, s.firstName || ' ' || s.lastName as studentName,\n c.name as className\n FROM grades g\n LEFT JOIN students s ON g.studentId = s.id\n LEFT JOIN classes c ON g.classId = c.id\n WHERE g.id = ?\n `, [result.lastInsertRowid]);\n \n return newGrade.rows[0] as Grade;\n}\n\n// Attendance queries\nexport async function getAllAttendance(): Promise {\n const result = await sqlite.execute(`\n SELECT a.*, s.firstName || ' ' || s.lastName as studentName,\n c.name as className\n FROM attendance a\n LEFT JOIN students s ON a.studentId = s.id\n LEFT JOIN classes c ON a.classId = c.id\n ORDER BY a.date DESC\n `);\n return result.rows as Attendance[];\n}\n\nexport async function getAttendanceByDate(date: string): Promise {\n const result = await sqlite.execute(`\n SELECT a.*, s.firstName || ' ' || s.lastName as studentName,\n c.name as className\n FROM attendance a\n LEFT JOIN students s ON a.studentId = s.id\n LEFT JOIN classes c ON a.classId = c.id\n WHERE a.date = ?\n ORDER BY s.lastName, s.firstName\n `, [date]);\n return result.rows as Attendance[];\n}\n\nexport async function markAttendance(attendance: Omit): Promise {\n await sqlite.execute(`\n INSERT OR REPLACE INTO attendance (studentId, classId, date, status, notes)\n VALUES (?, ?, ?, ?, ?)\n `, [\n attendance.studentId,\n attendance.classId,\n attendance.date,\n attendance.status,\n attendance.notes || null\n ]);\n \n const result = await sqlite.execute(`\n SELECT a.*, s.firstName || ' ' || s.lastName as studentName,\n c.name as className\n FROM attendance a\n LEFT JOIN students s ON a.studentId = s.id\n LEFT JOIN classes c ON a.classId = c.id\n WHERE a.studentId = ? AND a.classId = ? AND a.date = ?\n `, [attendance.studentId, attendance.classId, attendance.date]);\n \n return result.rows[0] as Attendance;\n}\n\n// Dashboard queries\nexport async function getDashboardStats(): Promise {\n const [studentsResult, teachersResult, classesResult, attendanceResult, gradesResult] = await Promise.all([\n sqlite.execute(\"SELECT COUNT(*) as count FROM students WHERE status = 'active'\"),\n sqlite.execute(\"SELECT COUNT(*) as count FROM teachers WHERE status = 'active'\"),\n sqlite.execute(\"SELECT COUNT(*) as count FROM classes\"),\n sqlite.execute(`\n SELECT \n COUNT(CASE WHEN status = 'present' THEN 1 END) as present,\n COUNT(*) as total\n FROM attendance \n WHERE date >= date('now', '-7 days')\n `),\n sqlite.execute(\"SELECT AVG(score * 100.0 / maxScore) as average FROM grades\")\n ]);\n\n const attendanceData = attendanceResult.rows[0] as any;\n const attendanceRate = attendanceData.total > 0 ? (attendanceData.present / attendanceData.total) * 100 : 0;\n \n const averageGrade = gradesResult.rows[0]?.average || 0;\n\n // Get recent activities\n const activitiesResult = await sqlite.execute(`\n SELECT type, description, timestamp FROM activities \n ORDER BY timestamp DESC \n LIMIT 10\n `);\n\n return {\n totalStudents: studentsResult.rows[0].count,\n totalTeachers: teachersResult.rows[0].count,\n totalClasses: classesResult.rows[0].count,\n attendanceRate: Math.round(attendanceRate * 100) / 100,\n averageGrade: Math.round(averageGrade * 100) / 100,\n recentActivities: activitiesResult.rows.map((row, index) => ({\n id: index + 1,\n type: row.type,\n description: row.description,\n timestamp: row.timestamp\n }))\n };\n}\n\n// Activity logging\nexport async function logActivity(type: string, description: string): Promise {\n await sqlite.execute(`\n INSERT INTO activities (type, description) VALUES (?, ?)\n `, [type, description]);\n}","deleted","label",[169,170,151],"backend","database","permalink","https://www.val.town/x/Deep_25/untitled-9443/code/backend/database/queries.ts","extension","actionData","errors"]