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

router.get("/promotions/preview", async (req, res) => {
  const session = getSession(req);
  if (!session || session.role !== "admin") return res.status(403).json({ error: "Admin only" });
  const { fromClassId, academicYear } = req.query;
  if (!fromClassId) return res.status(400).json({ error: "fromClassId required" });
  const students = await query("SELECT * FROM students WHERE class_id=?", [fromClassId]);
  if (!students.length) return res.json([]);
  const rows = await Promise.all(students.map(async s => {
    let sql = "SELECT total_score FROM gradebook WHERE student_id=? AND class_id=?";
    const params = [s.id, fromClassId];
    if (academicYear) { sql += " AND academic_year=?"; params.push(academicYear); }
    const grades = await query(sql, params);
    const scores = grades.map(g => parseFloat(g.total_score || 0));
    const avg = scores.length ? Math.round(scores.reduce((a, b) => a + b, 0) / scores.length) : null;
    const failing = scores.filter(s => s < 50).length;
    return {
      studentId: s.id, studentName: `${s.first_name} ${s.last_name}`, studentCode: s.student_id,
      photo: s.photo_url, subjectCount: scores.length, averageScore: avg, failingSubjects: failing,
      suggestedAction: avg === null ? "pending" : avg >= 50 ? "promote" : "holdback",
    };
  }));
  res.json(rows);
});

router.get("/promotions/history", async (req, res) => {
  const session = getSession(req);
  if (!session || session.role !== "admin") return res.status(403).json({ error: "Admin only" });
  const { academicYear } = req.query;
  let sql = `SELECT p.*, CONCAT(s.first_name,' ',s.last_name) as student_name,
    fc.name as from_class_name, tc.name as to_class_name
    FROM promotions p
    LEFT JOIN students s ON p.student_id=s.id
    LEFT JOIN classes fc ON p.from_class_id=fc.id
    LEFT JOIN classes tc ON p.to_class_id=tc.id`;
  const params = [];
  if (academicYear) { sql += " WHERE p.academic_year=?"; params.push(academicYear); }
  sql += " ORDER BY p.processed_at DESC";
  const rows = await query(sql, params);
  res.json(rows);
});

router.post("/promotions/bulk", async (req, res) => {
  const session = getSession(req);
  if (!session || session.role !== "admin") return res.status(403).json({ error: "Admin only" });
  const { fromClassId, toClassId, academicYear, decisions } = req.body;
  if (!fromClassId || !Array.isArray(decisions)) return res.status(400).json({ error: "fromClassId and decisions required" });
  let promoted = 0, heldBack = 0;
  for (const d of decisions) {
    const isPromote = d.action === "promote" || d.action === "graduate";
    await insert("INSERT INTO promotions (student_id, from_class_id, to_class_id, academic_year, action, reason, promoted, processed_by) VALUES (?,?,?,?,?,?,?,?)",
      [d.studentId, fromClassId, isPromote && toClassId ? toClassId : null, academicYear, d.action, d.reason || "", isPromote ? 1 : 0, session.displayName]);
    if (isPromote && toClassId) {
      await update("UPDATE students SET class_id=? WHERE id=?", [toClassId, d.studentId]);
      promoted++;
    } else { heldBack++; }
  }
  res.json({ promoted, heldBack, total: decisions.length });
});

module.exports = router;
