/**
 * ==========================================================
 * Smart Attendance System
 * Attendance Repository / Model
 * Compatible with mysql2/promise
 * ==========================================================
 */

const db = require("../../config/db");

class AttendanceModel {

    // ==========================================
    // Find Student By ID
    // ==========================================

    static async getStudentById(studentId) {

        const [rows] = await db.query(

            `SELECT *
             FROM students
             WHERE id=?
             LIMIT 1`,

            [studentId]

        );

        return rows;

    }

    // ==========================================
    // Find Student By NFC UID
    // ==========================================

    static async getStudentByNFC(uid) {

        const [rows] = await db.query(

            `SELECT *
             FROM students
             WHERE nfc_uid=?
             LIMIT 1`,

            [uid]

        );

        return rows;

    }

    // ==========================================
    // Check Today's Attendance
    // ==========================================

    static async checkTodayAttendance(studentId, attendanceDate) {

        const [rows] = await db.query(

            `SELECT *
             FROM attendance
             WHERE student_id=?
             AND attendance_date=?
             LIMIT 1`,

            [

                studentId,

                attendanceDate

            ]

        );

        return rows;

    }

    // ==========================================
    // Mark Attendance
    // ==========================================

    static async markAttendance(data) {

        const [result] = await db.query(

            `INSERT INTO attendance(

                student_id,

                attendance_date,

                check_in,

                face_verified,

                nfc_verified,

                device_id,

                ip_address,

                face_image,

                status,

                remarks

            )

            VALUES(

                ?,?,?,?,?,?,?,?,?,?

            )`,

            [

                data.student_id,

                data.attendance_date,

                data.check_in,

                data.face_verified,

                data.nfc_verified,

                data.device_id,

                data.ip_address,

                data.face_image,

                data.status,

                data.remarks

            ]

        );

        return result;

    }

    // ==========================================
    // Checkout
    // ==========================================

    static async checkout(

        studentId,

        attendanceDate,

        checkoutTime

    ) {

        const [result] = await db.query(

            `UPDATE attendance

             SET check_out=?

             WHERE student_id=?

             AND attendance_date=?`,

            [

                checkoutTime,

                studentId,

                attendanceDate

            ]

        );

        return result;

    }

    // ==========================================
    // Today's Attendance
    // ==========================================

    static async getTodayAttendance(attendanceDate) {

        const [rows] = await db.query(

            `SELECT

                attendance.*,

                students.student_id,

                students.full_name,

                students.department,

                students.class_name,

                students.photo

            FROM attendance

            INNER JOIN students

            ON attendance.student_id=students.id

            WHERE attendance.attendance_date=?

            ORDER BY attendance.check_in ASC`,

            [

                attendanceDate

            ]

        );

        return rows;

    }

    // ==========================================
    // Student History
    // ==========================================

    static async getStudentAttendance(studentId) {

        const [rows] = await db.query(

            `SELECT *

             FROM attendance

             WHERE student_id=?

             ORDER BY attendance_date DESC,

                      check_in DESC`,

            [

                studentId

            ]

        );

        return rows;

    }

    // ==========================================
    // Monthly Attendance
    // ==========================================

    static async getMonthlyAttendance(

        year,

        month

    ) {

        const [rows] = await db.query(

            `SELECT

                attendance.*,

                students.student_id,

                students.full_name,

                students.department,

                students.class_name

            FROM attendance

            INNER JOIN students

            ON attendance.student_id=students.id

            WHERE YEAR(attendance_date)=?

            AND MONTH(attendance_date)=?

            ORDER BY attendance_date DESC,

                     check_in DESC`,

            [

                year,

                month

            ]

        );

        return rows;

    }

    // ==========================================
    // Dashboard Statistics
    // ==========================================

    static async getTodayStatistics(attendanceDate) {

        const [rows] = await db.query(

            `SELECT

                COUNT(*) AS total,

                SUM(status='Present') AS present,

                SUM(status='Late') AS late,

                SUM(status='Absent') AS absent,

                SUM(face_verified=1) AS face_verified,

                SUM(nfc_verified=1) AS nfc_verified,

                SUM(check_out IS NOT NULL) AS checked_out

            FROM attendance

            WHERE attendance_date=?`,

            [

                attendanceDate

            ]

        );

        return rows[0];

    }

}

module.exports = AttendanceModel;