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

const Attendance = {
  /**
   * Logs a new attendance record for a student.
   * @param {number} institution_id The ID of the institution.
   * @param {object} logData The attendance details.
   * @param {number} logData.student_id
   * @param {'entry' | 'exit'} logData.type
   */
  log: async (institution_id, { student_id, type }) => {
    const sql =
      "INSERT INTO attendance (institution_id, student_id, type) VALUES (?, ?, ?)";
    const [result] = await db.query(sql, [institution_id, student_id, type]);
    return { id: result.insertId, institution_id, student_id, type };
  },

  /**
   * Gets attendance history for a specific date and institution.
   * @param {number} institution_id The ID of the institution.
   * @param {string} date Date in 'YYYY-MM-DD' format.
   */
  getHistoryByDate: async (institution_id, date) => {
    const sql = `
      SELECT a.id, a.timestamp, a.type, a.sms_sent, s.name as student_name, g.name AS grade_name
      FROM attendance a
      JOIN students s ON a.student_id = s.id
      JOIN grades g ON s.grade_id = g.id
      WHERE a.institution_id = ? AND DATE(a.timestamp) = ?
      ORDER BY a.timestamp DESC;
    `;
    const [rows] = await db.query(sql, [institution_id, date]);
    return rows;
  },

  /**
   * Updates the SMS status for an attendance record, scoped to an institution.
   * @param {number} institution_id The ID of the institution.
   * @param {number} attendanceId The ID of the attendance record.
   * @param {'pending' | 'success' | 'failed'} status The new SMS status.
   */
  updateSmsStatus: async (institution_id, attendanceId, status) => {
    const sql =
      "UPDATE attendance SET sms_sent = ? WHERE id = ? AND institution_id = ?";
    const [result] = await db.query(sql, [
      status,
      attendanceId,
      institution_id,
    ]);
    return result.affectedRows > 0;
  },
  getTodaysUniqueCount: async (institution_id) => {
    const [rows] = await db.query(
      "SELECT COUNT(DISTINCT student_id) as count FROM attendance WHERE institution_id = ? AND DATE(timestamp) = CURDATE()",
      [institution_id]
    );
    return rows[0].count;
  },

  getRecentLogs: async (institution_id, limit) => {
    const sql = `
        SELECT a.id, a.timestamp, a.type, s.name AS student_name, g.name AS grade_name
        FROM attendance a
        JOIN students s ON a.student_id = s.id
        JOIN grades g ON s.grade_id = g.id
        WHERE a.institution_id = ?
        ORDER BY a.timestamp DESC
        LIMIT ?;
      `;
    const [rows] = await db.query(sql, [institution_id, limit]);
    return rows;
  },
};

module.exports = Attendance;
