"use strict";
const { Router } = require("express");
const { query } = require("../db");
const router = Router();

router.get("/dashboard/summary", async (_req, res) => {
  const [[{ totalStudents }]] = [await query("SELECT COUNT(*) AS totalStudents FROM students")];
  const [[{ totalClasses }]] = [await query("SELECT COUNT(*) AS totalClasses FROM classes")];
  const [[{ totalExams }]] = [await query("SELECT COUNT(*) AS totalExams FROM exams")];
  const [[{ totalQuestions }]] = [await query("SELECT COUNT(*) AS totalQuestions FROM questions")];
  const inAWeek = new Date(Date.now() + 7 * 24 * 60 * 60 * 1000).toISOString().slice(0, 19).replace("T", " ");
  const now = new Date().toISOString().slice(0, 19).replace("T", " ");
  const [[{ examsThisWeek }]] = [await query("SELECT COUNT(*) AS examsThisWeek FROM exams WHERE scheduled_at >= ? AND scheduled_at <= ?", [now, inAWeek])];
  const [[{ pendingInvoices }]] = [await query("SELECT COUNT(*) AS pendingInvoices FROM invoices WHERE status != 'paid'")];
  const [[{ totalRevenue }]] = [await query("SELECT COALESCE(SUM(amount_paid), 0) AS totalRevenue FROM invoices")];
  const [[{ averageScore }]] = [await query("SELECT COALESCE(AVG(percentage), 0) AS averageScore FROM exam_results")];
  res.json({
    totalStudents, totalClasses, totalExams, totalQuestions, examsThisWeek, pendingInvoices,
    totalRevenue: Math.round((totalRevenue || 0) * 100) / 100,
    averageScore: Math.round((averageScore || 0) * 100) / 100,
  });
});

router.get("/dashboard/activity", async (_req, res) => {
  const rows = await query("SELECT * FROM activity_log ORDER BY created_at DESC LIMIT 20");
  res.json(rows.map(r => ({ id: String(r.id), type: r.type, message: r.message, timestamp: new Date(r.created_at).toISOString() })));
});

router.get("/dashboard/top-students", async (_req, res) => {
  const results = await query("SELECT * FROM exam_results");
  const students = await query("SELECT s.*, c.name AS class_name FROM students s LEFT JOIN classes c ON c.id = s.class_id");
  const agg = new Map();
  for (const r of results) {
    const cur = agg.get(r.student_id) || { score: 0, total: 0 };
    cur.score += r.score;
    cur.total += r.total_marks;
    agg.set(r.student_id, cur);
  }
  const list = students.map(s => {
    const a = agg.get(s.id);
    const avg = a && a.total > 0 ? (a.score / a.total) * 100 : 0;
    return { studentId: s.id, studentName: `${s.first_name} ${s.last_name}`, className: s.class_name || "", average: Math.round(avg * 100) / 100 };
  }).filter(x => x.average > 0).sort((a, b) => b.average - a.average).slice(0, 5);
  res.json(list);
});

module.exports = router;
