// Analyse pédagogique : statistiques AGRÉGÉES et anonymisées (seuil de 3 étudiants minimum).
import { NextResponse } from "next/server";
import { apiError } from "@/lib/api.ts";
import { requireRole } from "@/lib/auth/session.ts";
import { all } from "@/lib/db/index.ts";

const MIN_STUDENTS = 3;

export async function GET(req: Request) {
  try {
    await requireRole("instructor");
    const course = new URL(req.url).searchParams.get("course")?.toUpperCase() ?? "IMM1003";

    const conceptStats = all<{ id: number; name: string; week: number | null; students: number; avg_score: number | null }>(
      `SELECT c.id, c.name, c.week, COUNT(DISTINCT m.user_id) as students, AVG(m.score) as avg_score
       FROM concepts c LEFT JOIN mastery m ON m.concept_id = c.id
       WHERE c.course_code = ? GROUP BY c.id ORDER BY c.week`,
      course
    ).map((r) => ({
      ...r,
      avg_score: r.students >= MIN_STUDENTS ? r.avg_score : null,
      masked: r.students > 0 && r.students < MIN_STUDENTS,
    }));

    const hardestQuestions = all(
      `SELECT q.id, q.question, q.difficulty, c.name as concept, COUNT(qa.id) as attempts, ROUND(AVG(qa.correct)*100) as success_pct
       FROM quiz_answers qa JOIN quiz_questions q ON q.id = qa.question_id LEFT JOIN concepts c ON c.id = q.concept_id
       WHERE q.course_code = ? GROUP BY q.id HAVING attempts >= ? ORDER BY success_pct LIMIT 15`,
      course, MIN_STUDENTS
    );

    const commonErrors = all(
      `SELECT c.name as concept, COUNT(*) as n FROM error_notebook e
       LEFT JOIN concepts c ON c.id = e.concept_id
       WHERE e.course_code = ? GROUP BY e.concept_id HAVING COUNT(DISTINCT e.user_id) >= ? ORDER BY n DESC LIMIT 12`,
      course, MIN_STUDENTS
    );

    const examStats = all(
      `SELECT me.title, COUNT(ea.id) as attempts, ROUND(AVG(ea.score * 100.0 / NULLIF(ea.total,0)),1) as avg_pct
       FROM mock_exams me LEFT JOIN exam_attempts ea ON ea.exam_id = me.id AND ea.finished_at IS NOT NULL
       WHERE me.course_code = ? GROUP BY me.id`,
      course
    ).map((r) => ({ ...r, avg_pct: (r.attempts as number) >= MIN_STUDENTS ? r.avg_pct : null }));

    const flagged = all(
      `SELECT rf.id, rf.reason, rf.created_at, rf.resolved, m.content as message_preview
       FROM report_flags rf JOIN messages m ON m.id = rf.message_id
       ORDER BY rf.id DESC LIMIT 30`
    ).map((r) => ({ ...r, message_preview: String(r.message_preview).slice(0, 300) }));

    const feedback = all(
      `SELECT date(m.created_at) as day,
              SUM(CASE WHEN m.feedback = 1 THEN 1 ELSE 0 END) as up,
              SUM(CASE WHEN m.feedback = -1 THEN 1 ELSE 0 END) as down
       FROM messages m JOIN conversations cv ON cv.id = m.conversation_id
       WHERE m.role='assistant' AND cv.course_code = ? AND m.created_at >= datetime('now','-30 days')
       GROUP BY day ORDER BY day`,
      course
    );

    return NextResponse.json({ course, minStudents: MIN_STUDENTS, conceptStats, hardestQuestions, commonErrors, examStats, flagged, feedback });
  } catch (e) {
    return apiError(e);
  }
}