Something went wrong. Try again.
This repository has no description
Something went wrong. Try again.
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237import * as fs from "fs";
import { createClient } from "@libsql/client";
const db = createClient({ url: process.env.TURSO_DATABASE_URL!, authToken: process.env.TURSO_AUTH_TOKEN!,});
function esc(val: unknown): string { if (val === null || val === undefined) return ""; const s = String(val); if (s.includes(",") || s.includes("\n") || s.includes('"') || s.includes("\r")) { return `"${s.replace(/"/g, '""')}"`; } return s;}
function mean(nums: number[]): number { return nums.reduce((a, b) => a + b, 0) / nums.length;}
async function main() { // ── 1. Students ────────────────────────────────────────────────────────── const { rows: students } = await db.execute( `SELECT id, email, name, student_id, age, jlpt_level, japanese_learning_duration, previous_japanese_score, media_consumption, motivation, study_group, consent_given FROM user WHERE role = 'student' ORDER BY email`, ); const studentMap = new Map(students.map((s: any) => [s.id, s]));
// ── 2. Forms ───────────────────────────────────────────────────────────── const { rows: forms } = await db.execute("SELECT id, title, type FROM forms"); const tamForm = forms.find((f: any) => f.type === "questionnaire" && (f.title as string).toLowerCase().includes("tam")); const preForm = forms.find((f: any) => f.type === "pre_test"); const postForm = forms.find((f: any) => f.type === "post_test"); const delayedForm = forms.find((f: any) => f.type === "delayed_test"); const feedbackForm = forms.find((f: any) => f.type === "questionnaire" && (f.title as string).toLowerCase().includes("feedback")); const tamForm2 = forms.find((f: any) => f.type === "tam" && f.id !== tamForm?.id);
// ── 3. Questions per form ──────────────────────────────────────────────── async function getQuestions(formId: string) { if (!formId) return []; const { rows } = await db.execute({ sql: "SELECT id, order_index, question_text, type FROM questions WHERE form_id = ? ORDER BY order_index", args: [formId], }); return rows as { id: string; order_index: number; question_text: string; type: string }[]; }
const tamQs = await getQuestions(tamForm?.id); const preQs = await getQuestions(preForm?.id); const postQs = await getQuestions(postForm?.id); const delayedQs = await getQuestions(delayedForm?.id);
// TAM: first 5 = PU, next 5 = PEoU const tamPuIds = tamQs.slice(0, 5).map((q) => q.id); const tamPeuIds = tamQs.slice(5, 10).map((q) => q.id);
// ── 4. Responses ───────────────────────────────────────────────────────── async function getResponses(formId: string) { if (!formId) return []; const { rows } = await db.execute({ sql: "SELECT user_id, answers, time_spent_seconds FROM form_responses WHERE form_id = ?", args: [formId], }); return rows as { user_id: string; answers: string; time_spent_seconds: number }[]; }
const tamResponses = await getResponses(tamForm?.id); const preResponses = await getResponses(preForm?.id); const postResponses = await getResponses(postForm?.id); const delayedResponses = await getResponses(delayedForm?.id); const feedbackResponses = await getResponses(feedbackForm?.id); const tam2Responses = await getResponses(tamForm2?.id);
function responseMap(responses: { user_id: string; answers: string }[]) { const m = new Map<string, Record<string, string>>(); for (const r of responses) { m.set(r.user_id, JSON.parse(r.answers)); } return m; }
const tamAns = responseMap(tamResponses); const preAns = responseMap(preResponses); const postAns = responseMap(postResponses); const delayedAns = responseMap(delayedResponses); const feedbackAns = responseMap(feedbackResponses); const tam2Ans = responseMap(tam2Responses);
// ── 5. Learner maps → scores ───────────────────────────────────────────── const { rows: lmRows } = await db.execute( `SELECT lm.user_id, lm.assignment_id, lm.attempt, lm.status, d.score, d.id AS diagnosis_id FROM learner_maps lm LEFT JOIN diagnoses d ON d.learner_map_id = lm.id ORDER BY lm.user_id, lm.assignment_id, lm.attempt`, ); const lmByUser = new Map<string, any[]>(); for (const r of lmRows as any[]) { if (!lmByUser.has(r.user_id)) lmByUser.set(r.user_id, []); lmByUser.get(r.user_id)!.push(r); }
// ── 6. Feedback questions (for denormalization) ──────────────────────── const fbQs = feedbackForm ? await getQuestions(feedbackForm.id) : [];
// ── 7. Build rows ──────────────────────────────────────────────────────── type Row = Record<string, unknown>; const rows: Row[] = [];
for (const s of students as any[]) { const r: Row = { email: s.email, name: s.name, studentId: s.student_id, age: s.age, jlptLevel: s.jlpt_level, studyDurationMonths: s.japanese_learning_duration, prevScore: s.previous_japanese_score, mediaHoursWeek: s.media_consumption, motivation: s.motivation, studyGroup: s.study_group, consentGiven: s.consent_given, };
// TAM const ta = tamAns.get(s.id); if (ta) { const puVals = tamPuIds.map((id) => parseInt(ta[id])).filter((v) => !isNaN(v)); const peuVals = tamPeuIds.map((id) => parseInt(ta[id])).filter((v) => !isNaN(v)); r.tamPuMean = puVals.length ? mean(puVals) : null; r.tamPeouMean = peuVals.length ? mean(peuVals) : null;
// Individual items for (let i = 0; i < tamPuIds.length; i++) { r[`tamPu${i + 1}`] = parseInt(ta[tamPuIds[i]]) || null; } for (let i = 0; i < tamPeuIds.length; i++) { r[`tamPeou${i + 1}`] = parseInt(ta[tamPeuIds[i]]) || null; } }
// TAM2 (if exists) const ta2 = tam2Ans.get(s.id); if (ta2) { const tam2AllQs = await getQuestions(tamForm2?.id); const tam2PuIds = tam2AllQs.slice(0, 5).map((q) => q.id); const tam2PeuIds = tam2AllQs.slice(5, 10).map((q) => q.id); const puVals2 = tam2PuIds.map((id) => parseInt(ta2[id])).filter((v) => !isNaN(v)); const peuVals2 = tam2PeuIds.map((id) => parseInt(ta2[id])).filter((v) => !isNaN(v)); r.tam2PuMean = puVals2.length ? mean(puVals2) : null; r.tam2PeouMean = peuVals2.length ? mean(peuVals2) : null; }
// Pre-test const pra = preAns.get(s.id); if (pra && preQs.length) { const correct = preQs .map((q) => parseInt(pra[q.id])) .filter((v) => !isNaN(v)); r.preTestCorrect = correct.filter((v) => v === 1).length; r.preTestTotal = preQs.length; r.preTestScore = r.preTestTotal ? (r.preTestCorrect as number) / (r.preTestTotal as number) : null; }
// Post-test const poa = postAns.get(s.id); if (poa && postQs.length) { const correct = postQs .map((q) => parseInt(poa[q.id])) .filter((v) => !isNaN(v)); r.postTestCorrect = correct.filter((v) => v === 1).length; r.postTestTotal = postQs.length; r.postTestScore = r.postTestTotal ? (r.postTestCorrect as number) / (r.postTestTotal as number) : null; }
// Delayed post-test const dla = delayedAns.get(s.id); if (dla && delayedQs.length) { const correct = delayedQs .map((q) => parseInt(dla[q.id])) .filter((v) => !isNaN(v)); r.delayedCorrect = correct.filter((v) => v === 1).length; r.delayedTotal = delayedQs.length; r.delayedScore = r.delayedTotal ? (r.delayedCorrect as number) / (r.delayedTotal as number) : null; }
// Feedback (denormalized) const fb = feedbackAns.get(s.id); for (let i = 0; i < fbQs.length; i++) { r[`fb${i + 1}`] = fb ? (fb[fbQs[i].id] ?? null) : null; }
// Learner map scores const lms = lmByUser.get(s.id) || []; const bestAttempts = new Map<string, any>(); for (const lm of lms) { if (lm.status === "graded" && lm.score !== null) { const existing = bestAttempts.get(lm.assignment_id); if (!existing || lm.score > existing.score) { bestAttempts.set(lm.assignment_id, lm); } } } r.learnerMapCount = lms.length; r.learnerMapSubmitted = lms.filter((lm: any) => lm.status !== "draft").length; if (bestAttempts.size > 0) { const scores = [...bestAttempts.values()].map((lm: any) => lm.score); r.learnerMapAvgScore = mean(scores); r.learnerMapMaxScore = Math.max(...scores); }
rows.push(r); }
// ── 8. Write CSV ──────────────────────────────────────────────────────── const headerSet = new Set<string>(); for (const r of rows) Object.keys(r).forEach((k) => headerSet.add(k)); const headers = [...headerSet]; const lines = rows.map((r) => headers.map((h) => esc(r[h])).join(",")); const csv = [headers.join(","), ...lines].join("\n");
fs.mkdirSync("exports", { recursive: true }); fs.writeFileSync("exports/aggregated.csv", csv, "utf-8");
console.log(` exports/aggregated.csv (${rows.length} rows, ${headers.length} columns)`);}
main().catch((err) => { console.error("Export failed:", err); process.exit(1);});