[{"_1":2,"_171":-5,"_172":-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","68b182907d5a55cb4248aa216de7d21a5da70c0d","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,"_150":151,"_168":169,"_170":-5},"handle","chriseidhof","name","fnifs-projekte","path","workshopDatabase.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":116,"_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,"_149":20},"id","210bcbb2-57e3-11f0-a4aa-f69ea79377d9","createdAt",["D",1751529347206],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","2c69940e-198d-46d6-824e-0d8c150b63e9","pinnedFileId","4092326a-57e7-11f0-980d-f69ea79377d9","pinPreview",true,"pinMigrated","screenshotVersion","likeCount","917c516d-7013-4878-8604-9bac537fcbe3","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_94":95,"_131":132},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvb2F1dGhfZ2l0aHViL2ltZ18yckc5VFZOUXljZTRkWjJ6T2lPUVRseEtYa3YifQ","org",{"_94":95,"_135":132,"_12":112},"avatar","branch",{"_138":139,"_96":44,"_102":140,"_141":103,"_142":-5,"_143":-5,"_144":-5,"_145":-5,"_146":-5,"_147":97,"_148":95},"version",23,"210c5578-57e3-11f0-a4aa-f69ea79377d9","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_102":152,"_96":99,"_153":154,"_155":-5,"_156":140,"_157":139,"_158":116,"_104":159,"_138":160,"_161":162,"_163":164,"_165":20,"_166":-5,"_98":167},"481d6f4e-57e8-11f0-b6bb-f69ea79377d9","fileId","fa4c38b0-57e5-11f0-bc46-f69ea79377d9","parentId","branchId","branchVersion","latest",["D",1751531560146],2,"fileType","script","content","import { sqlite } from \"https://esm.town/v/std/sqlite\";\nimport type { Workshop, Kid, Preference, Assignment } from \"https://esm.town/v/chriseidhof/workshopTypes\";\n\nexport async function initDatabase() {\n // Create workshops table\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS workshops (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n name TEXT NOT NULL,\n description TEXT NOT NULL,\n age_groups TEXT NOT NULL,\n capacity INTEGER NOT NULL,\n day TEXT NOT NULL CHECK (day IN ('monday', 'tuesday')),\n time_slot INTEGER NOT NULL CHECK (time_slot IN (1, 2, 3)),\n created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,\n UNIQUE(day, time_slot, name)\n )\n `);\n\n // Create kids table\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS kids (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n name TEXT NOT NULL,\n grade TEXT NOT NULL,\n created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP\n )\n `);\n\n // Create preferences table\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS preferences (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n kid_id INTEGER NOT NULL,\n workshop_id INTEGER NOT NULL,\n rank INTEGER NOT NULL CHECK (rank IN (1, 2, 3)),\n day TEXT NOT NULL CHECK (day IN ('monday', 'tuesday')),\n time_slot INTEGER NOT NULL CHECK (time_slot IN (1, 2, 3)),\n created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,\n FOREIGN KEY (kid_id) REFERENCES kids(id) ON DELETE CASCADE,\n FOREIGN KEY (workshop_id) REFERENCES workshops(id) ON DELETE CASCADE,\n UNIQUE(kid_id, day, time_slot, rank)\n )\n `);\n\n // Create assignments table\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS assignments (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n kid_id INTEGER NOT NULL,\n workshop_id INTEGER NOT NULL,\n day TEXT NOT NULL CHECK (day IN ('monday', 'tuesday')),\n time_slot INTEGER NOT NULL CHECK (time_slot IN (1, 2, 3)),\n created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,\n FOREIGN KEY (kid_id) REFERENCES kids(id) ON DELETE CASCADE,\n FOREIGN KEY (workshop_id) REFERENCES workshops(id) ON DELETE CASCADE,\n UNIQUE(kid_id, day, time_slot)\n )\n `);\n\n // Create indexes for better performance\n await sqlite.execute(`CREATE INDEX IF NOT EXISTS idx_workshops_day_time ON workshops(day, time_slot)`);\n await sqlite.execute(`CREATE INDEX IF NOT EXISTS idx_preferences_kid ON preferences(kid_id)`);\n await sqlite.execute(`CREATE INDEX IF NOT EXISTS idx_assignments_workshop ON assignments(workshop_id)`);\n}\n\nexport async function resetDatabase() {\n await sqlite.execute(`DROP TABLE IF EXISTS assignments`);\n await sqlite.execute(`DROP TABLE IF EXISTS preferences`);\n await sqlite.execute(`DROP TABLE IF EXISTS workshops`);\n await sqlite.execute(`DROP TABLE IF EXISTS kids`);\n await initDatabase();\n}\n\n// Workshop database operations\nexport async function createWorkshop(workshop: Omit): Promise {\n const result = await sqlite.execute({\n sql: `INSERT INTO workshops (name, description, age_groups, capacity, day, time_slot) \n VALUES (?, ?, ?, ?, ?, ?)`,\n args: [workshop.name, workshop.description, workshop.age_groups, workshop.capacity, workshop.day, workshop.time_slot]\n });\n return result.lastInsertRowid as number;\n}\n\nexport async function getWorkshops(): Promise {\n const result = await sqlite.execute(`SELECT * FROM workshops ORDER BY day, time_slot, name`);\n return result.rows as Workshop[];\n}\n\nexport async function getWorkshopById(id: number): Promise {\n const result = await sqlite.execute({\n sql: `SELECT * FROM workshops WHERE id = ?`,\n args: [id]\n });\n return result.rows[0] as Workshop || null;\n}\n\nexport async function updateWorkshop(id: number, workshop: Omit): Promise {\n await sqlite.execute({\n sql: `UPDATE workshops SET name = ?, description = ?, age_groups = ?, capacity = ?, day = ?, time_slot = ? WHERE id = ?`,\n args: [workshop.name, workshop.description, workshop.age_groups, workshop.capacity, workshop.day, workshop.time_slot, id]\n });\n}\n\nexport async function deleteWorkshop(id: number): Promise {\n await sqlite.execute({\n sql: `DELETE FROM workshops WHERE id = ?`,\n args: [id]\n });\n}\n\n// Kid database operations\nexport async function createKid(kid: Omit): Promise {\n const result = await sqlite.execute({\n sql: `INSERT INTO kids (name, grade) VALUES (?, ?)`,\n args: [kid.name, kid.grade]\n });\n return result.lastInsertRowid as number;\n}\n\nexport async function getKids(): Promise {\n const result = await sqlite.execute(`SELECT * FROM kids ORDER BY grade, name`);\n return result.rows as Kid[];\n}\n\nexport async function getKidById(id: number): Promise {\n const result = await sqlite.execute({\n sql: `SELECT * FROM kids WHERE id = ?`,\n args: [id]\n });\n return result.rows[0] as Kid || null;\n}\n\nexport async function updateKid(id: number, kid: Omit): Promise {\n await sqlite.execute({\n sql: `UPDATE kids SET name = ?, grade = ? WHERE id = ?`,\n args: [kid.name, kid.grade, id]\n });\n}\n\nexport async function deleteKid(id: number): Promise {\n await sqlite.execute({\n sql: `DELETE FROM kids WHERE id = ?`,\n args: [id]\n });\n}\n\nexport async function bulkCreateKids(kids: Omit[]): Promise {\n const ids: number[] = [];\n for (const kid of kids) {\n const id = await createKid(kid);\n ids.push(id);\n }\n return ids;\n}\n\n// Preference database operations\nexport async function createPreference(preference: Omit): Promise {\n const result = await sqlite.execute({\n sql: `INSERT INTO preferences (kid_id, workshop_id, rank, day, time_slot) VALUES (?, ?, ?, ?, ?)`,\n args: [preference.kid_id, preference.workshop_id, preference.rank, preference.day, preference.time_slot]\n });\n return result.lastInsertRowid as number;\n}\n\nexport async function getPreferencesByKid(kidId: number): Promise {\n const result = await sqlite.execute({\n sql: `SELECT * FROM preferences WHERE kid_id = ? ORDER BY day, time_slot, rank`,\n args: [kidId]\n });\n return result.rows as Preference[];\n}\n\nexport async function deletePreference(id: number): Promise {\n await sqlite.execute({\n sql: `DELETE FROM preferences WHERE id = ?`,\n args: [id]\n });\n}\n\n// Assignment database operations\nexport async function createAssignment(assignment: Omit): Promise {\n const result = await sqlite.execute({\n sql: `INSERT INTO assignments (kid_id, workshop_id, day, time_slot) VALUES (?, ?, ?, ?)`,\n args: [assignment.kid_id, assignment.workshop_id, assignment.day, assignment.time_slot]\n });\n return result.lastInsertRowid as number;\n}\n\nexport async function getAssignmentsByWorkshop(workshopId: number): Promise {\n const result = await sqlite.execute({\n sql: `SELECT * FROM assignments WHERE workshop_id = ?`,\n args: [workshopId]\n });\n return result.rows as Assignment[];\n}\n\nexport async function getAssignmentsByKid(kidId: number): Promise {\n const result = await sqlite.execute({\n sql: `SELECT * FROM assignments WHERE kid_id = ?`,\n args: [kidId]\n });\n return result.rows as Assignment[];\n}\n\n// Dashboard statistics\nexport async function getDashboardStats() {\n const workshopsResult = await sqlite.execute(`SELECT COUNT(*) as count FROM workshops`);\n const kidsResult = await sqlite.execute(`SELECT COUNT(*) as count FROM kids`);\n const preferencesResult = await sqlite.execute(`SELECT COUNT(*) as count FROM preferences`);\n const assignmentsResult = await sqlite.execute(`SELECT COUNT(*) as count FROM assignments`);\n \n const mondayWorkshops = await sqlite.execute(`SELECT * FROM workshops WHERE day = 'monday' ORDER BY time_slot, name`);\n const tuesdayWorkshops = await sqlite.execute(`SELECT * FROM workshops WHERE day = 'tuesday' ORDER BY time_slot, name`);\n\n return {\n total_workshops: workshopsResult.rows[0].count,\n total_kids: kidsResult.rows[0].count,\n total_preferences: preferencesResult.rows[0].count,\n total_assignments: assignmentsResult.rows[0].count,\n workshops_by_day: {\n monday: mondayWorkshops.rows as Workshop[],\n tuesday: tuesdayWorkshops.rows as Workshop[]\n }\n };\n}\n\nexport { sqlite };","deleted","label",[99],"permalink","https://www.val.town/x/chriseidhof/fnifs-projekte/code/workshopDatabase.ts","extension","actionData","errors"]