// backend/models/gradeModel.js
const db = require("../config/db");

const Grade = {
  /**
   * Creates a new grade for a specific institution.
   * @param {number} institution_id The ID of the institution.
   * @param {string} name The name of the grade (e.g., "10-A").
   */
  create: async (institution_id, name) => {
    const [result] = await db.query(
      'INSERT INTO grades (institution_id, name, status) VALUES (?, ?, "active")',
      [institution_id, name]
    );
    return { id: result.insertId, institution_id, name };
  },

  /**
   * Gets all grades for a specific institution.
   * @param {number} institution_id The ID of the institution.
   * @param {boolean} [includeInactive=false] Whether to include inactive grades.
   */
  getAll: async (institution_id, includeInactive = false) => {
    const orderByClause = "ORDER BY CAST(name AS UNSIGNED) ASC, name ASC";
    let sql = `SELECT * FROM grades WHERE institution_id = ? AND status = 'active' ${orderByClause}`;
    const params = [institution_id];

    if (includeInactive) {
      sql = `SELECT * FROM grades WHERE institution_id = ? ${orderByClause}`;
    }

    const [rows] = await db.query(sql, params);
    return rows;
  },

  /**
   * Finds a single grade by its ID, scoped to an institution.
   * @param {number} institution_id The ID of the institution.
   * @param {number} id The ID of the grade.
   */
  findById: async (institution_id, id) => {
    const [rows] = await db.query(
      "SELECT * FROM grades WHERE id = ? AND institution_id = ?",
      [id, institution_id]
    );
    return rows[0];
  },

  /**
   * Updates a grade's name, scoped to an institution.
   * @param {number} institution_id The ID of the institution.
   * @param {number} id The ID of the grade to update.
   * @param {string} name The new name for the grade.
   */
  update: async (institution_id, id, name) => {
    const [result] = await db.query(
      "UPDATE grades SET name = ? WHERE id = ? AND institution_id = ?",
      [name, id, institution_id]
    );
    return result.affectedRows > 0;
  },

  /**
   * Soft deletes (or restores) a grade by setting its status, scoped to an institution.
   * @param {number} institution_id The ID of the institution.
   * @param {number} id The ID of the grade.
   * @param {'active' | 'inactive'} status The new status.
   */
  setStatus: async (institution_id, id, status) => {
    const [result] = await db.query(
      "UPDATE grades SET status = ? WHERE id = ? AND institution_id = ?",
      [status, id, institution_id]
    );
    return result.affectedRows > 0;
  },
};

module.exports = Grade;
