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

function gradeFor(pct) {
  if (pct >= 80) return { grade: "A", remark: "Excellent" };
  if (pct >= 70) return { grade: "B", remark: "Very Good" };
  if (pct >= 60) return { grade: "C", remark: "Good" };
  if (pct >= 50) return { grade: "D", remark: "Average" };
  if (pct >= 40) return { grade: "E", remark: "Below Average" };
  return { grade: "F", remark: "Fail" };
}

router.get("/results", async (req, res) => {
  const { examId, studentId } = req.query;
  let sql = "SELECT r.*, e.title AS exam_title, e.subject_id, s.first_name, s.last_name FROM exam_results r LEFT JOIN exams e ON e.id = r.exam_id LEFT JOIN students s ON s.id = r.student_id";
  const params = []; const where = [];
  if (examId) { where.push("r.exam_id = ?"); params.push(parseInt(examId)); }
  if (studentId) { where.push("r.student_id = ?"); params.push(parseInt(studentId)); }
  if (where.length) sql += " WHERE " + where.join(" AND ");
  sql += " ORDER BY r.submitted_at DESC";
  const rows = await query(sql, params);
  const subjects = await query("SELECT id, name FROM subjects");
  const sMap = new Map(subjects.map(s => [s.id, s.name]));
  res.json(rows.map(r => ({
    id: r.id,
    examId: r.exam_id,
    examTitle: r.exam_title,
    subjectName: sMap.get(r.subject_id) ?? null,
    studentId: r.student_id,
    studentName: r.first_name && r.last_name ? `${r.first_name} ${r.last_name}` : null,
    score: r.score,
    totalMarks: r.total_marks,
    percentage: Math.round(r.percentage * 100) / 100,
    grade: r.grade,
    remark: r.remark,
    submittedAt: r.submitted_at ? new Date(r.submitted_at).toISOString() : null,
  })));
});

router.get("/report-card/:studentId", async (req, res) => {
  const studentId = parseInt(req.params.studentId);
  if (!studentId) return res.status(400).json({ error: "Invalid studentId" });
  const { term } = req.query;

  const [school] = await query("SELECT * FROM school_settings LIMIT 1");
  const [student] = await query("SELECT s.*, c.name AS class_name FROM students s LEFT JOIN classes c ON c.id = s.class_id WHERE s.id = ?", [studentId]);
  if (!student || !school) return res.status(404).json({ error: "Not found" });

  const termName = term || school.term_name;
  const sessionName = school.session_name;

  const allResults = await query("SELECT * FROM exam_results WHERE student_id = ?", [studentId]);
  const exams = await query("SELECT * FROM exams");
  const subjects = await query("SELECT * FROM subjects");
  const eMap = new Map(exams.map(e => [e.id, e]));
  const sMap = new Map(subjects.map(s => [s.id, s]));

  const bySubject = new Map();
  for (const r of allResults) {
    const exam = eMap.get(r.exam_id);
    if (!exam) continue;
    const subj = sMap.get(exam.subject_id);
    if (!subj) continue;
    const cur = bySubject.get(subj.id) || { name: subj.name, score: 0, total: 0 };
    cur.score += r.score;
    cur.total += r.total_marks;
    bySubject.set(subj.id, cur);
  }

  const subjectsRC = Array.from(bySubject.values()).map(s => {
    const pct = s.total > 0 ? (s.score / s.total) * 100 : 0;
    const band = gradeFor(pct);
    return { subjectName: s.name, score: s.score, totalMarks: s.total, grade: band.grade, remark: band.remark };
  });

  const totalScore = subjectsRC.reduce((a, b) => a + b.score, 0);
  const totalMax = subjectsRC.reduce((a, b) => a + b.totalMarks, 0);
  const average = totalMax > 0 ? Math.round(((totalScore / totalMax) * 100) * 100) / 100 : 0;
  const overall = gradeFor(average);

  const classmates = await query("SELECT id FROM students WHERE class_id = ?", [student.class_id]);
  const allResultsForClass = await query("SELECT * FROM exam_results");
  const avgByStudent = classmates.map(cm => {
    const rs = allResultsForClass.filter(r => r.student_id === cm.id);
    const sc = rs.reduce((a, b) => a + b.score, 0);
    const tt = rs.reduce((a, b) => a + b.total_marks, 0);
    return { id: cm.id, avg: tt > 0 ? sc / tt : 0 };
  }).sort((a, b) => b.avg - a.avg);

  const position = Math.max(1, avgByStudent.findIndex(x => x.id === studentId) + 1);
  const classSize = classmates.length;
  const teacherComment = average >= 70 ? "An outstanding performance — keep it up." : average >= 50 ? "A satisfactory performance with room to improve." : "Needs significant improvement next term.";
  const principalComment = average >= 70 ? "Commendable. The school is proud of you." : "Work harder and consult your teachers for support.";

  res.json({
    student: {
      id: student.id, studentId: student.student_id, firstName: student.first_name, lastName: student.last_name,
      className: student.class_name, photoUrl: student.photo_url, gender: student.gender,
      admissionDate: student.admission_date ? new Date(student.admission_date).toISOString() : null,
    },
    school: {
      id: school.id, name: school.name, motto: school.motto, address: school.address, phone: school.phone,
      email: school.email, logoUrl: school.logo_url, primaryColor: school.primary_color,
      currency: school.currency, gradingScale: school.grading_scale, termName: school.term_name,
      sessionName: school.session_name, principalName: school.principal_name,
    },
    term: termName, session: sessionName,
    subjects: subjectsRC, totalScore, totalMax, average,
    overallGrade: overall.grade, position, classSize,
    teacherComment, principalComment,
    nextTermBegins: school.next_term_begins,
    barcode: `${student.student_id}|${termName}|${sessionName}`,
  });
});

module.exports = router;
