<?php
/**
 * Attendance Management Class
 * Handles all attendance-related operations
 */
class Attendance extends Database
{
    /**
     * Mark attendance for a single student
     */
    public function markAttendance($student_id, $class_id, $section_id, $attendance_date, $status, $remarks, $marked_by)
    {
        try {
            // Check if attendance already exists
            $check_sql = "SELECT id FROM student_attendance 
                         WHERE student_id = :student_id AND attendance_date = :date";
            $this->query($check_sql);
            $this->bind(':student_id', $student_id);
            $this->bind(':date', $attendance_date);
            $existing = $this->fetchSingle();

            if ($existing) {
                // Update existing record
                $sql = "UPDATE student_attendance SET 
                       status = :status,
                       remarks = :remarks,
                       marked_by = :marked_by,
                       updated_at = NOW()
                       WHERE id = :id";

                $this->query($sql);
                $this->bind(':id', $existing->id);
            } else {
                // Insert new record
                $sql = "INSERT INTO student_attendance 
                       (student_id, class_id, section_id, attendance_date, status, remarks, marked_by)
                       VALUES (:student_id, :class_id, :section_id, :date, :status, :remarks, :marked_by)";

                $this->query($sql);
                $this->bind(':student_id', $student_id);
                $this->bind(':class_id', $class_id);
                $this->bind(':section_id', $section_id);
                $this->bind(':date', $attendance_date);
            }

            $this->bind(':status', $status);
            $this->bind(':remarks', $remarks);
            $this->bind(':marked_by', $marked_by);

            if ($this->execute()) {
                // Update class summary
                $this->updateClassSummary($class_id, $section_id, $attendance_date);

                // Update student statistics
                $date_parts = explode('-', $attendance_date);
                $this->updateStudentStatistics($student_id, $class_id, $section_id, $date_parts[1], $date_parts[0]);

                return ['success' => true, 'message' => 'Attendance marked successfully'];
            }
            return ['success' => false, 'message' => 'Failed to mark attendance'];
        } catch (Exception $e) {
            return ['success' => false, 'message' => $e->getMessage()];
        }
    }

    /**
     * Mark bulk attendance for a class
     */
    public function markBulkAttendance($class_id, $section_id, $attendance_date, $attendance_data, $marked_by)
    {
        try {
            $success_count = 0;
            $total = count($attendance_data);

            foreach ($attendance_data as $data) {
                $result = $this->markAttendance(
                    $data['student_id'],
                    $class_id,
                    $section_id,
                    $attendance_date,
                    $data['status'],
                    $data['remarks'] ?? '',
                    $marked_by
                );

                if ($result['success']) {
                    $success_count++;
                }
            }

            // Log bulk operation
            $log_sql = "INSERT INTO bulk_attendance_logs 
                       (class_id, section_id, attendance_date, total_students, marked_by)
                       VALUES (:class_id, :section_id, :date, :total, :marked_by)";
            $this->query($log_sql);
            $this->bind(':class_id', $class_id);
            $this->bind(':section_id', $section_id);
            $this->bind(':date', $attendance_date);
            $this->bind(':total', $total);
            $this->bind(':marked_by', $marked_by);
            $this->execute();

            return [
                'success' => true,
                'message' => "Attendance marked for {$success_count} out of {$total} students",
                'marked' => $success_count,
                'total' => $total
            ];
        } catch (Exception $e) {
            return ['success' => false, 'message' => $e->getMessage()];
        }
    }

    /**
     * Get attendance for a class on a specific date
     */
    public function getClassAttendance($class_id, $section_id, $attendance_date)
    {
        $sql = "SELECT sa.*, st.name as student_name, st.registerNO, st.studentID as studentID
                FROM student st
                LEFT JOIN student_attendance sa ON st.studentID = sa.student_id 
                    AND sa.attendance_date = :date
                WHERE st.classesID = :class_id 
                AND st.sectionid = :section_id 
                AND st.status = 'on'
                ORDER BY st.name";

        $this->query($sql);
        $this->bind(':class_id', $class_id);
        $this->bind(':section_id', $section_id);
        $this->bind(':date', $attendance_date);

        return $this->fetchMultiple();
    }

    /**
     * Get student attendance history
     */
    public function getStudentAttendance($student_id, $start_date = null, $end_date = null)
    {
        $sql = "SELECT * FROM student_attendance 
                WHERE student_id = :student_id";

        if ($start_date && $end_date) {
            $sql .= " AND attendance_date BETWEEN :start_date AND :end_date";
        }

        $sql .= " ORDER BY attendance_date DESC";

        $this->query($sql);
        $this->bind(':student_id', $student_id);

        if ($start_date && $end_date) {
            $this->bind(':start_date', $start_date);
            $this->bind(':end_date', $end_date);
        }

        return $this->fetchMultiple();
    }

    /**
     * Get student attendance statistics
     */
    public function getStudentStats($student_id, $month = null, $year = null)
    {
        if ($month && $year) {
            $sql = "SELECT * FROM attendance_statistics 
                   WHERE student_id = :student_id 
                   AND month = :month AND year = :year";

            $this->query($sql);
            $this->bind(':student_id', $student_id);
            $this->bind(':month', $month);
            $this->bind(':year', $year);

            return $this->fetchSingle();
        } else {
            // Get overall statistics
            $sql = "SELECT 
                   COUNT(*) as total_days,
                   SUM(CASE WHEN status = 'present' THEN 1 ELSE 0 END) as present_days,
                   SUM(CASE WHEN status = 'absent' THEN 1 ELSE 0 END) as absent_days,
                   SUM(CASE WHEN status = 'late' THEN 1 ELSE 0 END) as late_days,
                   SUM(CASE WHEN status = 'excused' THEN 1 ELSE 0 END) as excused_days
                   FROM student_attendance 
                   WHERE student_id = :student_id";

            $this->query($sql);
            $this->bind(':student_id', $student_id);
            $stats = $this->fetchSingle();

            if ($stats && $stats->total_days > 0) {
                $stats->attendance_percentage = round(($stats->present_days / $stats->total_days) * 100, 2);
            } else {
                $stats->attendance_percentage = 0;
            }

            return $stats;
        }
    }

    /**
     * Update class attendance summary
     */
    public function updateClassSummary($class_id, $section_id, $attendance_date)
    {
        // Get total students
        $total_sql = "SELECT COUNT(*) as total FROM student 
                     WHERE classesID = :class_id AND sectionid = :section_id AND status = 'on'";
        $this->query($total_sql);
        $this->bind(':class_id', $class_id);
        $this->bind(':section_id', $section_id);
        $total_result = $this->fetchSingle();
        $total_students = $total_result->total ?? 0;

        // Get attendance counts
        $count_sql = "SELECT 
                     SUM(CASE WHEN status = 'present' THEN 1 ELSE 0 END) as present,
                     SUM(CASE WHEN status = 'absent' THEN 1 ELSE 0 END) as absent,
                     SUM(CASE WHEN status = 'late' THEN 1 ELSE 0 END) as late,
                     SUM(CASE WHEN status = 'excused' THEN 1 ELSE 0 END) as excused
                     FROM student_attendance 
                     WHERE class_id = :class_id 
                     AND section_id = :section_id 
                     AND attendance_date = :date";

        $this->query($count_sql);
        $this->bind(':class_id', $class_id);
        $this->bind(':section_id', $section_id);
        $this->bind(':date', $attendance_date);
        $counts = $this->fetchSingle();

        $present = $counts->present ?? 0;
        $absent = $counts->absent ?? 0;
        $late = $counts->late ?? 0;
        $excused = $counts->excused ?? 0;
        $percentage = $total_students > 0 ? round(($present / $total_students) * 100, 2) : 0;

        // Check if summary exists
        $check_sql = "SELECT id FROM class_attendance_summary 
                     WHERE class_id = :class_id 
                     AND section_id = :section_id 
                     AND attendance_date = :date";
        $this->query($check_sql);
        $this->bind(':class_id', $class_id);
        $this->bind(':section_id', $section_id);
        $this->bind(':date', $attendance_date);
        $existing = $this->fetchSingle();

        if ($existing) {
            // Update
            $update_sql = "UPDATE class_attendance_summary SET 
                          present_count = :present,
                          absent_count = :absent,
                          late_count = :late,
                          excused_count = :excused,
                          attendance_percentage = :percentage
                          WHERE id = :id";
            $this->query($update_sql);
            $this->bind(':id', $existing->id);
        } else {
            // Insert
            $insert_sql = "INSERT INTO class_attendance_summary 
                          (class_id, section_id, attendance_date, total_students, 
                           present_count, absent_count, late_count, excused_count, attendance_percentage)
                          VALUES (:class_id, :section_id, :date, :total, :present, :absent, :late, :excused, :percentage)";
            $this->query($insert_sql);
            $this->bind(':class_id', $class_id);
            $this->bind(':section_id', $section_id);
            $this->bind(':date', $attendance_date);
            $this->bind(':total', $total_students);
        }

        $this->bind(':present', $present);
        $this->bind(':absent', $absent);
        $this->bind(':late', $late);
        $this->bind(':excused', $excused);
        $this->bind(':percentage', $percentage);

        return $this->execute();
    }

    /**
     * Update student monthly statistics
     */
    public function updateStudentStatistics($student_id, $class_id, $section_id, $month, $year)
    {
        // Get attendance counts for the month
        $sql = "SELECT 
               COUNT(*) as total_days,
               SUM(CASE WHEN status = 'present' THEN 1 ELSE 0 END) as present,
               SUM(CASE WHEN status = 'absent' THEN 1 ELSE 0 END) as absent,
               SUM(CASE WHEN status = 'late' THEN 1 ELSE 0 END) as late,
               SUM(CASE WHEN status = 'excused' THEN 1 ELSE 0 END) as excused
               FROM student_attendance 
               WHERE student_id = :student_id 
               AND MONTH(attendance_date) = :month 
               AND YEAR(attendance_date) = :year";

        $this->query($sql);
        $this->bind(':student_id', $student_id);
        $this->bind(':month', $month);
        $this->bind(':year', $year);
        $stats = $this->fetchSingle();

        $percentage = $stats->total_days > 0 ? round(($stats->present / $stats->total_days) * 100, 2) : 0;

        // Check if stats exist
        $check_sql = "SELECT id FROM attendance_statistics 
                     WHERE student_id = :student_id AND month = :month AND year = :year";
        $this->query($check_sql);
        $this->bind(':student_id', $student_id);
        $this->bind(':month', $month);
        $this->bind(':year', $year);
        $existing = $this->fetchSingle();

        if ($existing) {
            // Update
            $update_sql = "UPDATE attendance_statistics SET 
                          total_days = :total,
                          present_days = :present,
                          absent_days = :absent,
                          late_days = :late,
                          excused_days = :excused,
                          attendance_percentage = :percentage
                          WHERE id = :id";
            $this->query($update_sql);
            $this->bind(':id', $existing->id);
        } else {
            // Insert
            $insert_sql = "INSERT INTO attendance_statistics 
                          (student_id, class_id, section_id, month, year, total_days, 
                           present_days, absent_days, late_days, excused_days, attendance_percentage)
                          VALUES (:student_id, :class_id, :section_id, :month, :year, :total, 
                                  :present, :absent, :late, :excused, :percentage)";
            $this->query($insert_sql);
            $this->bind(':student_id', $student_id);
            $this->bind(':class_id', $class_id);
            $this->bind(':section_id', $section_id);
            $this->bind(':month', $month);
            $this->bind(':year', $year);
        }

        $this->bind(':total', $stats->total_days);
        $this->bind(':present', $stats->present);
        $this->bind(':absent', $stats->absent);
        $this->bind(':late', $stats->late);
        $this->bind(':excused', $stats->excused);
        $this->bind(':percentage', $percentage);

        return $this->execute();
    }

    /**
     * Get class summary for date range
     */
    public function getClassSummary($class_id, $section_id, $start_date, $end_date)
    {
        $sql = "SELECT * FROM class_attendance_summary 
               WHERE class_id = :class_id 
               AND section_id = :section_id 
               AND attendance_date BETWEEN :start_date AND :end_date
               ORDER BY attendance_date DESC";

        $this->query($sql);
        $this->bind(':class_id', $class_id);
        $this->bind(':section_id', $section_id);
        $this->bind(':start_date', $start_date);
        $this->bind(':end_date', $end_date);

        return $this->fetchMultiple();
    }

    /**
     * Get attendance report for class
     */
    public function getClassReport($class_id, $section_id, $start_date, $end_date)
    {
        $sql = "SELECT st.studentID, st.name, st.registerNO,
               COUNT(sa.id) as total_days,
               SUM(CASE WHEN sa.status = 'present' THEN 1 ELSE 0 END) as present_days,
               SUM(CASE WHEN sa.status = 'absent' THEN 1 ELSE 0 END) as absent_days,
               SUM(CASE WHEN sa.status = 'late' THEN 1 ELSE 0 END) as late_days,
               SUM(CASE WHEN sa.status = 'excused' THEN 1 ELSE 0 END) as excused_days
               FROM student st
               LEFT JOIN student_attendance sa ON st.studentID = sa.student_id 
                   AND sa.attendance_date BETWEEN :start_date AND :end_date
               WHERE st.classesID = :class_id 
               AND st.sectionid = :section_id 
               AND st.status = 'on'
               GROUP BY st.studentID
               ORDER BY st.name";

        $this->query($sql);
        $this->bind(':class_id', $class_id);
        $this->bind(':section_id', $section_id);
        $this->bind(':start_date', $start_date);
        $this->bind(':end_date', $end_date);

        $results = $this->fetchMultiple();

        // Calculate percentage for each student
        foreach ($results as $result) {
            $result->attendance_percentage = $result->total_days > 0 ?
                round(($result->present_days / $result->total_days) * 100, 2) : 0;
        }

        return $results;
    }


    public function getStudentStatistics($student_id)
    {
        // Get overall statistics
        $sql = "SELECT 
               COUNT(*) as total_days,
               SUM(CASE WHEN status = 'present' THEN 1 ELSE 0 END) as present_days,
               SUM(CASE WHEN status = 'absent' THEN 1 ELSE 0 END) as absent_days,
               SUM(CASE WHEN status = 'late' THEN 1 ELSE 0 END) as late_days,
               SUM(CASE WHEN status = 'excused' THEN 1 ELSE 0 END) as excused_days
               FROM student_attendance 
               WHERE student_id = :student_id";

        $this->query($sql);
        $this->bind(':student_id', $student_id);
        $stats = $this->fetchSingle();

        if ($stats && $stats->total_days > 0) {
            $stats->attendance_percentage = round(($stats->present_days / $stats->total_days) * 100, 2);
        } else {
            $stats = new stdClass();
            $stats->total_days = 0;
            $stats->present_days = 0;
            $stats->absent_days = 0;
            $stats->late_days = 0;
            $stats->excused_days = 0;
            $stats->attendance_percentage = 0;
        }

        return $stats;
    }


    public function getStudentAttendanceHistory($student_id, $limit = null)
    {
        $sql = "SELECT * FROM student_attendance 
                WHERE student_id = :student_id
                ORDER BY attendance_date DESC";

        if($limit) {
            $sql .= " LIMIT :limit";
        }

        $this->query($sql);
        $this->bind(':student_id', $student_id);

        if($limit) {
            $this->bind(':limit', $limit);
        }

        return $this->fetchMultiple();
    }
}