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

const uploadDir = path.join(__dirname, "../../uploads");
if (!fs.existsSync(uploadDir)) fs.mkdirSync(uploadDir, { recursive: true });

const storage = multer.diskStorage({
  destination: (_req, _file, cb) => cb(null, uploadDir),
  filename: (_req, file, cb) => cb(null, `${Date.now()}-${file.originalname}`),
});
const upload = multer({ storage, limits: { fileSize: 5 * 1024 * 1024 } });

async function nextStudentId() {
  const yr = new Date().getFullYear();
  const prefix = `SCH/${yr}/`;
  const [row] = await query("SELECT COUNT(*) AS cnt FROM students WHERE student_id LIKE ?", [`${prefix}%`]);
  const next = (row?.cnt || 0) + 1;
  return `${prefix}${String(next).padStart(4, "0")}`;
}

async function withClassName(rows) {
  if (!rows.length) return rows;
  const classes = await query("SELECT id, name FROM classes");
  const map = new Map(classes.map(c => [c.id, c.name]));
  return rows.map(r => ({
    ...r,
    className: map.get(r.class_id) ?? null,
    admissionDate: r.admission_date ? new Date(r.admission_date).toISOString() : null,
    classId: r.class_id,
    studentId: r.student_id,
    firstName: r.first_name,
    lastName: r.last_name,
    dateOfBirth: r.date_of_birth,
    guardianName: r.guardian_name,
    guardianPhone: r.guardian_phone,
    photoUrl: r.photo_url,
  }));
}

router.get("/students", async (req, res) => {
  const { classId, search } = req.query;
  let sql = "SELECT * FROM students";
  const params = [];
  const where = [];
  if (classId) { where.push("class_id = ?"); params.push(parseInt(classId)); }
  if (search) {
    where.push("(first_name LIKE ? OR last_name LIKE ? OR student_id LIKE ?)");
    const s = `%${search}%`;
    params.push(s, s, s);
  }
  if (where.length) sql += " WHERE " + where.join(" AND ");
  sql += " ORDER BY id DESC";
  const rows = await query(sql, params);
  res.json(await withClassName(rows));
});

router.post("/students", async (req, res) => {
  const { firstName, lastName, classId, gender, dateOfBirth, guardianName, guardianPhone, photoUrl, address } = req.body || {};
  if (!firstName || !lastName || !classId || !gender) return res.status(400).json({ error: "firstName, lastName, classId, gender required" });
  const sid = await nextStudentId();
  await query("INSERT INTO students (student_id, first_name, last_name, class_id, gender, date_of_birth, guardian_name, guardian_phone, photo_url, address) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
    [sid, firstName, lastName, classId, gender, dateOfBirth || null, guardianName || null, guardianPhone || null, photoUrl || null, address || null]);
  const [row] = await query("SELECT * FROM students WHERE id = LAST_INSERT_ID()");
  await query("INSERT INTO activity_log (type, message) VALUES (?, ?)", ["student", `Admitted ${firstName} ${lastName} (${sid})`]);
  const [enriched] = await withClassName([row]);
  res.status(201).json(enriched);
});

router.post("/students/bulk", async (req, res) => {
  const { students } = req.body || {};
  if (!Array.isArray(students) || !students.length) return res.status(400).json({ error: "students array required" });
  const created = [];
  for (const s of students) {
    const sid = await nextStudentId();
    await query("INSERT INTO students (student_id, first_name, last_name, class_id, gender, date_of_birth, guardian_name, guardian_phone) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
      [sid, s.firstName, s.lastName, s.classId, s.gender || "Other", s.dateOfBirth || null, s.guardianName || null, s.guardianPhone || null]);
    const [row] = await query("SELECT * FROM students WHERE id = LAST_INSERT_ID()");
    created.push(row);
  }
  await query("INSERT INTO activity_log (type, message) VALUES (?, ?)", ["student", `Bulk imported ${created.length} students`]);
  res.status(201).json({ created: created.length, students: await withClassName(created) });
});

router.post("/students/upload-photo", upload.single("photo"), async (req, res) => {
  if (!req.file) return res.status(400).json({ error: "No file uploaded" });
  const url = `/uploads/${req.file.filename}`;
  res.json({ url });
});

router.get("/students/:id", async (req, res) => {
  const id = parseInt(req.params.id);
  if (!id) return res.status(400).json({ error: "Invalid id" });
  const [row] = await query("SELECT * FROM students WHERE id = ?", [id]);
  if (!row) return res.status(404).json({ error: "Not found" });
  const [enriched] = await withClassName([row]);
  res.json(enriched);
});

router.patch("/students/:id", async (req, res) => {
  const id = parseInt(req.params.id);
  if (!id) return res.status(400).json({ error: "Invalid id" });
  const { firstName, lastName, classId, gender, dateOfBirth, guardianName, guardianPhone, photoUrl, address } = req.body || {};
  const sets = []; const vals = [];
  if (firstName !== undefined) { sets.push("first_name = ?"); vals.push(firstName); }
  if (lastName !== undefined) { sets.push("last_name = ?"); vals.push(lastName); }
  if (classId !== undefined) { sets.push("class_id = ?"); vals.push(classId); }
  if (gender !== undefined) { sets.push("gender = ?"); vals.push(gender); }
  if (dateOfBirth !== undefined) { sets.push("date_of_birth = ?"); vals.push(dateOfBirth); }
  if (guardianName !== undefined) { sets.push("guardian_name = ?"); vals.push(guardianName); }
  if (guardianPhone !== undefined) { sets.push("guardian_phone = ?"); vals.push(guardianPhone); }
  if (photoUrl !== undefined) { sets.push("photo_url = ?"); vals.push(photoUrl); }
  if (address !== undefined) { sets.push("address = ?"); vals.push(address); }
  if (!sets.length) return res.status(400).json({ error: "Nothing to update" });
  vals.push(id);
  await query(`UPDATE students SET ${sets.join(", ")} WHERE id = ?`, vals);
  const [row] = await query("SELECT * FROM students WHERE id = ?", [id]);
  if (!row) return res.status(404).json({ error: "Not found" });
  const [enriched] = await withClassName([row]);
  res.json(enriched);
});

router.delete("/students/:id", async (req, res) => {
  const id = parseInt(req.params.id);
  if (!id) return res.status(400).json({ error: "Invalid id" });
  await query("DELETE FROM students WHERE id = ?", [id]);
  res.status(204).end();
});

module.exports = router;
