// backend/controllers/studentController.js

const db = require("../config/db"); // Still needed for complex PDF query
const PDFDocument = require("pdfkit");
const Student = require("../models/studentModel");

const XLSX = require("xlsx");
const { v4: uuidv4 } = require("uuid");

exports.createStudent = async (req, res) => {
  try {
    const { institution_id } = req.user;
    const student = await Student.create(institution_id, req.body);
    res.status(201).json({ message: "Student created successfully", student });
  } catch (error) {
    res
      .status(500)
      .json({ message: "Error creating student", error: error.message });
  }
};

exports.bulkCreateStudents = async (req, res) => {
  const connection = await db.getConnection(); // get a connection for transaction

  try {
    const { institution_id } = req.user;

    if (!req.file) {
      return res.status(400).json({ message: "Excel file is required" });
    }

    // Read Excel
    const workbook = XLSX.read(req.file.buffer, { type: "buffer" });
    const sheet = workbook.Sheets[workbook.SheetNames[0]];
    const rows = XLSX.utils.sheet_to_json(sheet);

    if (!rows.length) {
      return res.status(400).json({ message: "Excel file is empty" });
    }

    // Start transaction
    await connection.beginTransaction();

    // Fetch grades for this institution
    const [grades] = await connection.query(
      "SELECT id, name FROM grades WHERE institution_id = ?",
      [institution_id]
    );

    const gradeMap = {};
    grades.forEach((g) => {
      gradeMap[g.name.toString().trim()] = g.id;
    });

    const inserted = [];
    const skipped = [];

    for (const row of rows) {
      const rawName = row.name?.toString().trim();
      const rawGrade = row.grade?.toString().trim();
      const rawMobile = row.mobile_number?.toString().trim() || null;

      if (!rawName || !rawGrade) {
        skipped.push({
          row,
          reason: "Missing required fields (name or grade)",
        });
        continue;
      }

      const grade_id = gradeMap[rawGrade];

      if (!grade_id) {
        skipped.push({
          row,
          reason: `Grade "${rawGrade}" not found for this institution`,
        });
        continue;
      }

      const qr_id = uuidv4();

      await connection.query(
        `INSERT INTO students
          (institution_id, name, grade_id, mobile_number, qr_id, status)
         VALUES (?, ?, ?, ?, ?, 'active')`,
        [institution_id, rawName, grade_id, rawMobile, qr_id]
      );

      inserted.push(rawName);
    }

    // Commit transaction
    await connection.commit();
    connection.release();

    res.status(201).json({
      message: "Bulk student upload completed",
      insertedCount: inserted.length,
      skippedCount: skipped.length,
      skipped,
    });
  } catch (error) {
    await connection.rollback();
    connection.release();
    console.error(error);
    res
      .status(500)
      .json({ message: "Bulk upload failed", error: error.message });
  }
};

exports.getAllStudents = async (req, res) => {
  try {
    const { institution_id } = req.user;
    const students = await Student.findAll(institution_id);
    res.status(200).json(students);
  } catch (error) {
    res
      .status(500)
      .json({ message: "Error fetching students", error: error.message });
  }
};

exports.getStudentById = async (req, res) => {
  try {
    const { institution_id } = req.user;
    const student = await Student.findById(institution_id, req.params.id);
    if (student) {
      res.status(200).json(student);
    } else {
      res.status(404).json({ message: "Student not found" });
    }
  } catch (error) {
    res
      .status(500)
      .json({ message: "Error fetching student", error: error.message });
  }
};

exports.updateStudent = async (req, res) => {
  try {
    const { institution_id } = req.user;
    const { id } = req.params;
    const success = await Student.update(institution_id, id, req.body);
    if (success) {
      res.status(200).json({ message: "Student updated successfully" });
    } else {
      res.status(404).json({ message: "Student not found or no changes made" });
    }
  } catch (error) {
    res
      .status(500)
      .json({ message: "Error updating student", error: error.message });
  }
};

exports.deleteStudent = async (req, res) => {
  try {
    const { institution_id } = req.user;
    const { id } = req.params;
    const success = await Student.setStatus(institution_id, id, "inactive");
    if (success) {
      res.status(200).json({ message: "Student deactivated successfully" });
    } else {
      res.status(404).json({ message: "Student not found" });
    }
  } catch (error) {
    res
      .status(500)
      .json({ message: "Error deactivating student", error: error.message });
  }
};

exports.searchStudents = async (req, res) => {
  try {
    const { institution_id } = req.user;
    const { query } = req.query;
    if (!query || query.trim().length < 2) return res.status(200).json([]);

    // Scoped search query
    const sql = `
      SELECT s.id, s.name, s.qr_id, g.name AS grade_name
      FROM students s
      LEFT JOIN grades g ON s.grade_id = g.id
      WHERE s.institution_id = ? AND s.status = 'active' AND (s.name LIKE ? OR s.qr_id LIKE ?)
      LIMIT 10;
    `;
    const params = [institution_id, `%${query}%`, `%${query}%`];
    const [students] = await db.query(sql, params);
    res.status(200).json(students);
  } catch (error) {
    res
      .status(500)
      .json({ message: "Error during student search", error: error.message });
  }
};

exports.generateStudentListPdf = async (req, res) => {
  try {
    const { gradeId } = req.query; // Get the optional gradeId from query params

    let query = `
            SELECT s.name, s.mobile_number, g.name AS grade_name
            FROM students s
            LEFT JOIN grades g ON s.grade_id = g.id
            WHERE s.status = 'active'
        `;
    const params = [];

    if (gradeId) {
      query += ` AND s.grade_id = ?`;
      params.push(gradeId);
    }

    query += ` ORDER BY g.name ASC, s.name ASC;`;

    const [students] = await db.query(query, params);

    // --- PDF Generation ---
    const doc = new PDFDocument({ margin: 40, size: "A4" });

    res.setHeader("Content-Type", "application/pdf");
    res.setHeader(
      "Content-Disposition",
      `attachment; filename=student-list_${Date.now()}.pdf`
    );
    doc.pipe(res);

    // Header
    doc
      .fontSize(18)
      .font("Helvetica-Bold")
      .text("Dr. Maths Education - Student List", { align: "center" });
    doc
      .fontSize(10)
      .font("Helvetica")
      .text(`Generated on: ${new Date().toLocaleDateString()}`, {
        align: "center",
      });
    doc.moveDown(2);

    // Table Headers
    const tableTop = doc.y;
    const nameX = 40;
    const gradeX = 250;
    const mobileX = 400;

    doc.fontSize(12).font("Helvetica-Bold");
    doc.text("Student Name", nameX, tableTop);
    doc.text("Grade", gradeX, tableTop);
    doc.text("Mobile Number", mobileX, tableTop);
    doc.moveTo(nameX, doc.y).lineTo(555, doc.y).stroke(); // Underline
    doc.font("Helvetica");

    // Table Rows
    students.forEach((student) => {
      const y = doc.y + 10;
      doc.fontSize(10).text(student.name, nameX, y, { width: 200 });
      doc.text(student.grade_name, gradeX, y, { width: 140 });
      doc.text(student.mobile_number, mobileX, y, { width: 150 });
    });

    doc.end();
  } catch (error) {
    console.error("Error generating student list PDF:", error);
    res
      .status(500)
      .json({ message: "Error generating PDF report", error: error.message });
  }
};
