// backend/models/studentModel.js
const db = require("../config/db");
const { v4: uuidv4 } = require("uuid");

const Student = {
  /**
   * Creates a new student for a specific institution.
   * @param {number} institution_id The ID of the institution.
   * @param {object} studentData The student's details.
   * @param {string} studentData.name
   * @param {number} studentData.grade_id
   * @param {string} studentData.mobile_number
   */
  create: async (institution_id, { name, grade_id, mobile_number }) => {
    const qr_id = uuidv4();
    const sql =
      'INSERT INTO students (institution_id, name, grade_id, mobile_number, qr_id, status) VALUES (?, ?, ?, ?, ?, "active")';
    const [result] = await db.query(sql, [
      institution_id,
      name,
      grade_id,
      mobile_number,
      qr_id,
    ]);
    return {
      id: result.insertId,
      institution_id,
      name,
      grade_id,
      mobile_number,
      qr_id,
    };
  },

  /**
   * Finds all active students for a specific institution.
   * @param {number} institution_id The ID of the institution.
   */
  findAll: async (institution_id) => {
    const sql = `
      SELECT s.id, s.name, s.mobile_number, s.qr_id, s.grade_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'
      ORDER BY s.name;
    `;
    const [rows] = await db.query(sql, [institution_id]);
    return rows;
  },

  /**
   * Finds a student by ID, scoped to an institution.
   * @param {number} institution_id The ID of the institution.
   * @param {number} id The ID of the student.
   */
  findById: async (institution_id, id) => {
    const sql = `
      SELECT s.id, s.name, s.mobile_number, s.qr_id, s.grade_id, g.name AS grade_name
      FROM students s
      LEFT JOIN grades g ON s.grade_id = g.id
      WHERE s.id = ? AND s.institution_id = ?;
    `;
    const [rows] = await db.query(sql, [id, institution_id]);
    return rows[0];
  },

  /**
   * Finds a student by QR ID, scoped to an institution.
   * This is a critical security update to prevent cross-institution scanning.
   * @param {number} institution_id The ID of the institution performing the scan.
   * @param {string} qr_id The QR ID of the student.
   */
  findByQrId: async (institution_id, qr_id) => {
    const sql = `
      SELECT s.id, s.name, s.status,s.mobile_number, s.grade_id, g.name AS grade_name
      FROM students s
      JOIN grades g ON s.grade_id = g.id
      WHERE s.qr_id = ? AND s.institution_id = ?;
    `;
    const [rows] = await db.query(sql, [qr_id, institution_id]);
    return rows[0];
  },

  /**
   * Updates a student's details, scoped to an institution.
   * @param {number} institution_id The ID of the institution.
   * @param {number} id The ID of the student to update.
   * @param {object} studentData The new student data.
   */
  update: async (institution_id, id, { name, grade_id, mobile_number }) => {
    const sql =
      "UPDATE students SET name = ?, grade_id = ?, mobile_number = ? WHERE id = ? AND institution_id = ?";
    const [result] = await db.query(sql, [
      name,
      grade_id,
      mobile_number,
      id,
      institution_id,
    ]);
    return result.affectedRows > 0;
  },

  /**
   * Soft deletes (or restores) a student, scoped to an institution.
   * @param {number} institution_id The ID of the institution.
   * @param {number} id The ID of the student.
   * @param {'active' | 'inactive'} status The new status.
   */
  setStatus: async (institution_id, id, status) => {
    const [result] = await db.query(
      "UPDATE students SET status = ? WHERE id = ? AND institution_id = ?",
      [status, id, institution_id]
    );
    return result.affectedRows > 0;
  },
  countAll: async (institution_id) => {
    const [rows] = await db.query(
      "SELECT COUNT(*) as count FROM students WHERE institution_id = ? AND status = 'active'",
      [institution_id]
    );
    return rows[0].count;
  },
  countPlatformTotal: async () => {
    const [rows] = await db.query(
      "SELECT COUNT(*) as count FROM students WHERE status = 'active'"
    );
    return rows[0].count;
  },
};

module.exports = Student;
