"use strict";
const { Router } = require("express");
const { query, insert, update } = require("../db");
const { getSession } = require("../auth");
const router = Router();

function calcGrade(total) {
  if (total >= 90) return { grade: "A+", remark: "Outstanding" };
  if (total >= 80) return { grade: "A",  remark: "Excellent" };
  if (total >= 70) return { grade: "B",  remark: "Very Good" };
  if (total >= 60) return { grade: "C",  remark: "Good" };
  if (total >= 50) return { grade: "D",  remark: "Pass" };
  return { grade: "F", remark: "Fail" };
}

router.get("/gradebook", async (req, res) => {
  const session = getSession(req);
  if (!session) return res.status(401).json({ error: "Login required" });
  const { classId, subjectId, term, academicYear } = req.query;
  if (!classId || !subjectId) return res.status(400).json({ error: "classId and subjectId required" });
  let sql = "SELECT * FROM gradebook WHERE class_id=? AND subject_id=?";
  const params = [classId, subjectId];
  if (term) { sql += " AND term=?"; params.push(term); }
  if (academicYear) { sql += " AND academic_year=?"; params.push(academicYear); }
  const entries = await query(sql, params);
  const students = await query("SELECT * FROM students WHERE class_id=?", [classId]);
  const [subject] = await query("SELECT * FROM subjects WHERE id=?", [subjectId]);
  const [klass] = await query("SELECT * FROM classes WHERE id=?", [classId]);
  const entryMap = new Map(entries.map(e => [e.student_id, e]));
  const rows = students.map(s => {
    const e = entryMap.get(s.id);
    return {
      studentId: s.id, studentName: `${s.first_name} ${s.last_name}`, studentCode: s.student_id,
      gradebookId: e?.id ?? null, caScore: e?.ca_score ?? null, examScore: e?.exam_score ?? null,
      totalScore: e?.total_score ?? null, grade: e?.grade ?? "", remark: e?.remark ?? "",
    };
  });
  res.json({ rows, className: klass?.name ?? "", subjectName: subject?.name ?? "" });
});

router.get("/gradebook/student/:studentId", async (req, res) => {
  const session = getSession(req);
  if (!session) return res.status(401).json({ error: "Login required" });
  const { term, academicYear } = req.query;
  let sql = "SELECT g.*, s.name as subject_name FROM gradebook g LEFT JOIN subjects s ON g.subject_id=s.id WHERE g.student_id=?";
  const params = [req.params.studentId];
  if (term) { sql += " AND g.term=?"; params.push(term); }
  if (academicYear) { sql += " AND g.academic_year=?"; params.push(academicYear); }
  const rows = await query(sql, params);
  res.json(rows.map(r => ({
    gradebookId: r.id, subjectId: r.subject_id, subjectName: r.subject_name,
    caScore: r.ca_score, examScore: r.exam_score, totalScore: r.total_score,
    grade: r.grade, remark: r.remark, term: r.term, academicYear: r.academic_year,
  })));
});

router.post("/gradebook/bulk", async (req, res) => {
  const session = getSession(req);
  if (!session || (session.role !== "admin" && session.role !== "teacher")) return res.status(403).json({ error: "Staff only" });
  const { classId, subjectId, term, academicYear, records } = req.body;
  if (!classId || !subjectId || !term || !Array.isArray(records)) return res.status(400).json({ error: "Required fields missing" });
  for (const r of records) {
    const total = (r.caScore || 0) + (r.examScore || 0);
    const { grade, remark } = calcGrade(total);
    const [existing] = await query("SELECT id FROM gradebook WHERE student_id=? AND subject_id=? AND class_id=? AND term=? AND academic_year=?",
      [r.studentId, subjectId, classId, term, academicYear || ""]);
    if (existing) {
      await update("UPDATE gradebook SET ca_score=?, exam_score=?, total_score=?, grade=?, remark=?, recorded_by=? WHERE id=?",
        [r.caScore || 0, r.examScore || 0, total, grade, remark, session.displayName, existing.id]);
    } else {
      await insert("INSERT INTO gradebook (student_id, class_id, subject_id, term, academic_year, ca_score, exam_score, total_score, grade, remark, recorded_by) VALUES (?,?,?,?,?,?,?,?,?,?,?)",
        [r.studentId, classId, subjectId, term, academicYear || "", r.caScore || 0, r.examScore || 0, total, grade, remark, session.displayName]);
    }
  }
  res.json({ saved: records.length });
});

module.exports = router;
