spb/immbot-ai

1// Analyse pédagogique : statistiques AGRÉGÉES et anonymisées (seuil de 3 étudiants minimum).2import { NextResponse } from "next/server";3import { apiError } from "@/lib/api.ts";4import { requireRole } from "@/lib/auth/session.ts";5import { all } from "@/lib/db/index.ts";67const MIN_STUDENTS = 3;89export async function GET(req: Request) {10  try {11    await requireRole("instructor");12    const course = new URL(req.url).searchParams.get("course")?.toUpperCase() ?? "IMM1003";1314    const conceptStats = all<{ id: number; name: string; week: number | null; students: number; avg_score: number | null }>(15      `SELECT c.id, c.name, c.week, COUNT(DISTINCT m.user_id) as students, AVG(m.score) as avg_score16       FROM concepts c LEFT JOIN mastery m ON m.concept_id = c.id17       WHERE c.course_code = ? GROUP BY c.id ORDER BY c.week`,18      course19    ).map((r) => ({20      ...r,21      avg_score: r.students >= MIN_STUDENTS ? r.avg_score : null,22      masked: r.students > 0 && r.students < MIN_STUDENTS,23    }));2425    const hardestQuestions = all(26      `SELECT q.id, q.question, q.difficulty, c.name as concept, COUNT(qa.id) as attempts, ROUND(AVG(qa.correct)*100) as success_pct27       FROM quiz_answers qa JOIN quiz_questions q ON q.id = qa.question_id LEFT JOIN concepts c ON c.id = q.concept_id28       WHERE q.course_code = ? GROUP BY q.id HAVING attempts >= ? ORDER BY success_pct LIMIT 15`,29      course, MIN_STUDENTS30    );3132    const commonErrors = all(33      `SELECT c.name as concept, COUNT(*) as n FROM error_notebook e34       LEFT JOIN concepts c ON c.id = e.concept_id35       WHERE e.course_code = ? GROUP BY e.concept_id HAVING COUNT(DISTINCT e.user_id) >= ? ORDER BY n DESC LIMIT 12`,36      course, MIN_STUDENTS37    );3839    const examStats = all(40      `SELECT me.title, COUNT(ea.id) as attempts, ROUND(AVG(ea.score * 100.0 / NULLIF(ea.total,0)),1) as avg_pct41       FROM mock_exams me LEFT JOIN exam_attempts ea ON ea.exam_id = me.id AND ea.finished_at IS NOT NULL42       WHERE me.course_code = ? GROUP BY me.id`,43      course44    ).map((r) => ({ ...r, avg_pct: (r.attempts as number) >= MIN_STUDENTS ? r.avg_pct : null }));4546    const flagged = all(47      `SELECT rf.id, rf.reason, rf.created_at, rf.resolved, m.content as message_preview48       FROM report_flags rf JOIN messages m ON m.id = rf.message_id49       ORDER BY rf.id DESC LIMIT 30`50    ).map((r) => ({ ...r, message_preview: String(r.message_preview).slice(0, 300) }));5152    const feedback = all(53      `SELECT date(m.created_at) as day,54              SUM(CASE WHEN m.feedback = 1 THEN 1 ELSE 0 END) as up,55              SUM(CASE WHEN m.feedback = -1 THEN 1 ELSE 0 END) as down56       FROM messages m JOIN conversations cv ON cv.id = m.conversation_id57       WHERE m.role='assistant' AND cv.course_code = ? AND m.created_at >= datetime('now','-30 days')58       GROUP BY day ORDER BY day`,59      course60    );6162    return NextResponse.json({ course, minStudents: MIN_STUDENTS, conceptStats, hardestQuestions, commonErrors, examStats, flagged, feedback });63  } catch (e) {64    return apiError(e);65  }66}67