<?php

namespace App\Controllers\Admin;

use App\Controllers\BaseController;
use App\Models\AttendanceModel;
use App\Models\StudentModel;
use App\Models\ClassroomModel;
use App\Models\GradeModel;
use App\Models\AcademicPeriodModel;
use App\Models\TeachingAssignmentModel;
use App\Services\WhatsappService;

class Attendance extends BaseController
{
    protected $attendanceModel;
    protected $studentModel;
    protected $classroomModel;
    protected $gradeModel;
    protected $periodModel;
    protected $assignmentModel;

    public function __construct()
    {
        $this->attendanceModel = new AttendanceModel();
        $this->studentModel    = new StudentModel();
        $this->classroomModel  = new ClassroomModel();
        $this->gradeModel      = new GradeModel();
        $this->periodModel     = new AcademicPeriodModel();
        $this->assignmentModel = new TeachingAssignmentModel();
    }

    private function checkLogin()
    {
        if (!session()->get('logged_in')) {
            return redirect()->to('/login');
        }
        return null;
    }

    private function isBkOrAdmin()
    {
        $role = session()->get('user_role');
        
        // Admin bisa akses semua
        if (in_array($role, ['superadmin', 'admin'])) {
            return true;
        }
        
        // Cek apakah user adalah guru BK
        if ($role == 'guru') {
            $userId = session()->get('user_id');
            
            // Cari teacher_id dari user_id
            $teacher = db_connect()->table('teachers')
                        ->select('id')
                        ->where('user_id', $userId)
                        ->get()
                        ->getRowArray();
            
            if ($teacher) {
                $activePeriod = $this->periodModel->getActive();
                if ($activePeriod) {
                    // Cek apakah guru ini punya assignment sebagai BK di periode aktif
                    $bkAssignment = db_connect()->table('bk_assignments')
                                    ->where('teacher_id', $teacher['id'])
                                    ->where('academic_period_id', $activePeriod['id'])
                                    ->get()
                                    ->getRowArray();
                    
                    if ($bkAssignment) {
                        return true;
                    }
                }
            }
        }
        
        return false;
    }

    /**
     * Mendapatkan daftar ID kelas yang boleh diakses oleh user saat ini.
     * Mengembalikan null jika bebas (Admin/Superadmin).
     */
    private function getAllowedClassroomIds(): ?array
    {
        $role = session()->get('user_role');
        
        if (in_array($role, ['superadmin', 'admin'])) {
            return null; // Bebas akses semua kelas
        }
        
        if ($role == 'guru') {
            $userId = session()->get('user_id');
            $db = \Config\Database::connect();
            
            $teacher = $db->table('teachers')
                          ->select('id')
                          ->where('user_id', $userId)
                          ->get()
                          ->getRowArray();
                          
            if ($teacher) {
                $activePeriod = $this->periodModel->getActive();
                if ($activePeriod) {
                    $assignments = $db->table('bk_assignments')
                                      ->select('classroom_id')
                                      ->where('teacher_id', $teacher['id'])
                                      ->where('academic_period_id', $activePeriod['id'])
                                      ->get()
                                      ->getResultArray();
                                      
                    return array_column($assignments, 'classroom_id');
                }
            }
        }
        
        return []; // Default tidak boleh akses kelas mana pun
    }

    public function index()
    {
        if ($redirect = $this->checkLogin()) return $redirect;
        
        if (!$this->isBkOrAdmin()) {
            return redirect()->to('/dashboard')->with('error', 'Akses ditolak! Hanya Guru BK dan Admin.');
        }

        $activePeriod = $this->periodModel->getActive();
        if (!$activePeriod) {
            return redirect()->to('/dashboard')->with('error', 'Belum ada periode aktif!');
        }

        $data = [
            'title'        => 'Absensi Siswa',
            'activePeriod' => $activePeriod,
            'grades'       => $this->gradeModel->orderBy('order', 'ASC')->findAll(),
        ];

        return view('attendance/index', $data);
    }

    // AJAX: Ambil kelas berdasarkan tingkat
    public function getClassrooms()
    {
        if ($redirect = $this->checkLogin()) return $redirect;

        $gradeId = $this->request->getGet('grade_id');
        $periodId = $this->request->getGet('period_id');

        if (!$gradeId) {
            return $this->response->setJSON([]);
        }

        $classroomsQuery = $this->classroomModel
            ->select('classrooms.id, classrooms.name, grades.name as grade_name')
            ->join('grades', 'grades.id = classrooms.grade_id')
            ->where('classrooms.grade_id', $gradeId)
            ->where('classrooms.deleted_at', null);

        $allowedIds = $this->getAllowedClassroomIds();
        if ($allowedIds !== null) {
            if (empty($allowedIds)) {
                return $this->response->setJSON([]);
            }
            $classroomsQuery->whereIn('classrooms.id', $allowedIds);
        }

        $classrooms = $classroomsQuery->orderBy('classrooms.name', 'ASC')->findAll();

        return $this->response->setJSON($classrooms);
    }

    // AJAX: Ambil daftar siswa di kelas + status absensi hari ini
    public function getStudents()
    {
        if ($redirect = $this->checkLogin()) return $redirect;

        $classroomId = $this->request->getGet('classroom_id');
        $periodId    = $this->request->getGet('period_id');
        $date        = $this->request->getGet('date');

        if (!$classroomId || !$periodId || !$date) {
            return $this->response->setJSON([
                'success' => false,
                'students' => []
            ]);
        }

        // Validasi akses kelas
        $allowedIds = $this->getAllowedClassroomIds();
        if ($allowedIds !== null && !in_array($classroomId, $allowedIds)) {
            return $this->response->setJSON([
                'success' => false,
                'message' => 'Akses ditolak! Anda tidak mengampu kelas ini.',
                'students' => []
            ]);
        }

        // Ambil semua siswa aktif di kelas ini
        $db = \Config\Database::connect();
        $students = $db->table('student_academic_histories as sah')
            ->select('students.id, students.nis, students.nisn, students.full_name, students.gender')
            ->join('students', 'students.id = sah.student_id')
            ->where('sah.classroom_id', $classroomId)
            ->where('sah.academic_period_id', $periodId)
            ->where('students.deleted_at', null)
            ->where('students.status', 'Aktif')
            ->orderBy('students.full_name', 'ASC')
            ->get()
            ->getResultArray();

        // Ambil absensi yang sudah ada untuk tanggal ini
        $attendances = $this->attendanceModel->getByClassroomAndDate($classroomId, $periodId, $date);
        
        // Map attendance by student_id
        $attendanceMap = [];
        foreach ($attendances as $att) {
            $attendanceMap[$att['student_id']] = $att['status'];
        }

        // Gabungkan
        foreach ($students as &$student) {
            $student['attendance_status'] = $attendanceMap[$student['id']] ?? null;
        }

        return $this->response->setJSON([
            'success' => true,
            'students' => $students,
            'date' => $date
        ]);
    }

    // AJAX: Simpan absensi (auto-save)
    public function save()
    {
        if ($redirect = $this->checkLogin()) return $redirect;

        $studentId = $this->request->getPost('student_id');
        $classroomId = $this->request->getPost('classroom_id');
        $periodId = $this->request->getPost('period_id');
        $date = $this->request->getPost('date');
        $status = $this->request->getPost('status'); // 'Sakit', 'Izin', 'Alpha', atau null (Hadir)
        $recordedBy = session()->get('user_id');

        if (!$studentId || !$classroomId || !$periodId || !$date) {
            return $this->response->setJSON([
                'success' => false,
                'message' => 'Data tidak lengkap!'
            ]);
        }

        // Validasi akses kelas
        $allowedIds = $this->getAllowedClassroomIds();
        if ($allowedIds !== null && !in_array($classroomId, $allowedIds)) {
            return $this->response->setJSON([
                'success' => false,
                'message' => 'Akses ditolak! Anda tidak mengampu kelas ini.'
            ]);
        }

        // Jika status kosong atau 'Hadir', maka null (hapus record)
        if ($status === 'Hadir' || $status === '') {
            $status = null;
        }

        $result = $this->attendanceModel->saveAttendance(
            $studentId, $classroomId, $periodId, $date, $status, $recordedBy
        );

        return $this->response->setJSON([
            'success' => true,
            'message' => 'Berhasil disimpan',
            'status' => $status
        ]);
    }

    // Rekap Bulanan
    public function rekapBulanan()
    {
        if ($redirect = $this->checkLogin()) return $redirect;
        
        if (!$this->isBkOrAdmin()) {
            return redirect()->to('/dashboard')->with('error', 'Akses ditolak!');
        }

        $activePeriod = $this->periodModel->getActive();
        if (!$activePeriod) {
            return redirect()->to('/dashboard')->with('error', 'Belum ada periode aktif!');
        }

        $allowedIds = $this->getAllowedClassroomIds();
        if ($allowedIds !== null) {
            $classrooms = [];
            if (!empty($allowedIds)) {
                $classrooms = $this->classroomModel
                    ->select('classrooms.*, grades.name as grade_name, majors.name as major_name')
                    ->join('grades', 'grades.id = classrooms.grade_id')
                    ->join('majors', 'majors.id = classrooms.major_id', 'left')
                    ->where('classrooms.deleted_at', null)
                    ->whereIn('classrooms.id', $allowedIds)
                    ->orderBy('grades.order', 'ASC')
                    ->orderBy('classrooms.name', 'ASC')
                    ->findAll();
            }
        } else {
            $classrooms = $this->classroomModel->getAllWithRelations();
        }

        $data = [
            'title'        => 'Rekap Absensi Bulanan',
            'activePeriod' => $activePeriod,
            'grades'       => $this->gradeModel->orderBy('order', 'ASC')->findAll(),
            'classrooms'   => $classrooms,
            'months'       => [
                1 => 'Januari', 2 => 'Februari', 3 => 'Maret', 4 => 'April',
                5 => 'Mei', 6 => 'Juni', 7 => 'Juli', 8 => 'Agustus',
                9 => 'September', 10 => 'Oktober', 11 => 'November', 12 => 'Desember'
            ],
            'currentYear'  => date('Y')
        ];

        return view('attendance/rekap_bulanan', $data);
    }

    // AJAX: Get rekap bulanan data
    public function getRekapBulanan()
    {
        if ($redirect = $this->checkLogin()) return $redirect;

        $classroomId = $this->request->getGet('classroom_id');
        $periodId    = $this->request->getGet('period_id');
        $month       = $this->request->getGet('month');
        $year        = $this->request->getGet('year');

        if (!$classroomId || !$periodId || !$month || !$year) {
            return $this->response->setJSON([]);
        }

        // Validasi akses kelas
        $allowedIds = $this->getAllowedClassroomIds();
        if ($allowedIds !== null && !in_array($classroomId, $allowedIds)) {
            return $this->response->setJSON([]);
        }

        // Ambil periode untuk hitung hari aktif
        $period = $this->periodModel->find($periodId);
        
        $startDate = "$year-$month-01";
        $endDate = date('Y-m-t', strtotime($startDate));
        
        $periodeStart = $period['start_date'];
        $periodeEnd = $period['end_date'];
        
        if ($startDate < $periodeStart) $startDate = $periodeStart;
        if ($endDate > $periodeEnd) $endDate = $periodeEnd;
        
        $totalHariAktif = $this->attendanceModel->countActiveDays($startDate, $endDate);
        
        $db = \Config\Database::connect();

        // Fetch academic calendar holidays
        $holidayMap = [];
        $holidays = $db->table('calendar_events')
            ->select('calendar_events.start_date, calendar_events.end_date, calendar_events.title')
            ->join('calendar_categories', 'calendar_categories.id = calendar_events.category_id')
            ->where('calendar_categories.is_libur', 1)
            ->where('calendar_events.deleted_at', null)
            ->where('calendar_events.start_date <=', $endDate)
            ->where('calendar_events.end_date >=', $startDate)
            ->get()
            ->getResultArray();

        foreach ($holidays as $h) {
            $start = strtotime($h['start_date']);
            $end = strtotime($h['end_date']);
            for ($curr = $start; $curr <= $end; $curr = strtotime('+1 day', $curr)) {
                $dateStr = date('Y-m-d', $curr);
                if ($dateStr >= $startDate && $dateStr <= $endDate) {
                    $holidayMap[$dateStr] = $h['title'];
                }
            }
        }

        $days = [];
        $daysInMonth = (int)date('t', strtotime($startDate));
        for ($d = 1; $d <= $daysInMonth; $d++) {
            $currentDateString = sprintf('%s-%s-%02d', $year, $month, $d);
            $dayOfWeek = date('N', strtotime($currentDateString));
            $isSunday = ($dayOfWeek == 7);
            $isHoliday = isset($holidayMap[$currentDateString]);
            $holidayTitle = $holidayMap[$currentDateString] ?? null;
            
            $days[] = [
                'day' => $d,
                'is_off' => ($isSunday || $isHoliday),
                'holiday_title' => $holidayTitle,
            ];
        }
        
        // Ambil semua siswa di kelas
        $allStudents = $db->table('student_academic_histories as sah')
            ->select('students.id, students.nis, students.full_name, students.gender')
            ->join('students', 'students.id = sah.student_id')
            ->where('sah.classroom_id', $classroomId)
            ->where('sah.academic_period_id', $periodId)
            ->where('students.deleted_at', null)
            ->where('students.status', 'Aktif')
            ->orderBy('students.full_name', 'ASC')
            ->get()
            ->getResultArray();
        
        $attendancesRaw = $db->table('attendances')
            ->select('student_id, date, status')
            ->where('classroom_id', $classroomId)
            ->where('academic_period_id', $periodId)
            ->where('date >=', $startDate)
            ->where('date <=', $endDate)
            ->get()
            ->getResultArray();

        $attMap = [];
        $attDailyMap = [];
        foreach ($attendancesRaw as $a) {
            $sId = $a['student_id'];
            $status = $a['status'];
            $day = (int)date('j', strtotime($a['date']));
            
            if (!isset($attMap[$sId])) {
                $attMap[$sId] = ['sakit' => 0, 'izin' => 0, 'alpha' => 0];
            }
            if ($status === 'Sakit') $attMap[$sId]['sakit']++;
            elseif ($status === 'Izin') $attMap[$sId]['izin']++;
            elseif ($status === 'Alpha') $attMap[$sId]['alpha']++;

            $attDailyMap[$sId][$day] = $status;
        }
        
        $result = [];
        foreach ($allStudents as $student) {
            $sId = $student['id'];
            $data = $attMap[$sId] ?? [
                'sakit' => 0,
                'izin' => 0,
                'alpha' => 0
            ];
            
            $totalTidakHadir = $data['sakit'] + $data['izin'] + $data['alpha'];
            $hadir = $totalHariAktif - $totalTidakHadir;
            if ($hadir < 0) $hadir = 0;

            $studentDailyStatuses = [];
            for ($d = 1; $d <= $daysInMonth; $d++) {
                $status = $attDailyMap[$sId][$d] ?? null;
                if ($status === 'Sakit') {
                    $studentDailyStatuses[$d] = 'S';
                } elseif ($status === 'Izin') {
                    $studentDailyStatuses[$d] = 'I';
                } elseif ($status === 'Alpha') {
                    $studentDailyStatuses[$d] = 'A';
                } else {
                    $studentDailyStatuses[$d] = '.';
                }
            }
            
            $result[] = [
                'nis' => $student['nis'],
                'full_name' => $student['full_name'],
                'gender' => $student['gender'],
                'sakit' => (int)$data['sakit'],
                'izin' => (int)$data['izin'],
                'alpha' => (int)$data['alpha'],
                'hadir' => $hadir,
                'total_hari' => $totalHariAktif,
                'statuses' => $studentDailyStatuses
            ];
        }
        
        return $this->response->setJSON([
            'success' => true,
            'students' => $result,
            'days' => $days,
            'total_hari_aktif' => $totalHariAktif,
            'bulan' => $month,
            'tahun' => $year
        ]);
    }

    // Rekap Semester
    public function rekapSemester()
    {
        if ($redirect = $this->checkLogin()) return $redirect;
        
        if (!$this->isBkOrAdmin()) {
            return redirect()->to('/dashboard')->with('error', 'Akses ditolak!');
        }

        $activePeriod = $this->periodModel->getActive();
        if (!$activePeriod) {
            return redirect()->to('/dashboard')->with('error', 'Belum ada periode aktif!');
        }

        $allowedIds = $this->getAllowedClassroomIds();
        if ($allowedIds !== null) {
            $classrooms = [];
            if (!empty($allowedIds)) {
                $classrooms = $this->classroomModel
                    ->select('classrooms.*, grades.name as grade_name, majors.name as major_name')
                    ->join('grades', 'grades.id = classrooms.grade_id')
                    ->join('majors', 'majors.id = classrooms.major_id', 'left')
                    ->where('classrooms.deleted_at', null)
                    ->whereIn('classrooms.id', $allowedIds)
                    ->orderBy('grades.order', 'ASC')
                    ->orderBy('classrooms.name', 'ASC')
                    ->findAll();
            }
        } else {
            $classrooms = $this->classroomModel->getAllWithRelations();
        }

        $data = [
            'title'        => 'Rekap Absensi Semester',
            'activePeriod' => $activePeriod,
            'grades'       => $this->gradeModel->orderBy('order', 'ASC')->findAll(),
            'classrooms'   => $classrooms,
            'periods'      => $this->periodModel->getAllWithYear(),
        ];

        return view('attendance/rekap_semester', $data);
    }

    // AJAX: Get rekap semester data
    public function getRekapSemester()
    {
        if ($redirect = $this->checkLogin()) return $redirect;

        $classroomId = $this->request->getGet('classroom_id');
        $periodId    = $this->request->getGet('period_id');

        if (!$classroomId || !$periodId) {
            return $this->response->setJSON([
                'success' => false,
                'message' => 'Data tidak lengkap'
            ]);
        }

        // Validasi akses kelas
        $allowedIds = $this->getAllowedClassroomIds();
        if ($allowedIds !== null && !in_array($classroomId, $allowedIds)) {
            return $this->response->setJSON([
                'success' => false,
                'message' => 'Akses ditolak! Anda tidak mengampu kelas ini.'
            ]);
        }

        try {
            // Ambil data absensi
            $attendances = $this->attendanceModel->getSemesterRecap($classroomId, $periodId);
            
            // Ambil periode untuk hitung hari aktif
            $period = $this->periodModel->find($periodId);
            
            if (!$period) {
                return $this->response->setJSON([
                    'success' => false,
                    'message' => 'Periode tidak ditemukan'
                ]);
            }
            
            // Ambil nama tahun ajaran
            $academicYear = db_connect()->table('academic_years')
                            ->select('name')
                            ->where('id', $period['academic_year_id'])
                            ->get()
                            ->getRowArray();
            
            $yearName = $academicYear['name'] ?? '';
            
            // Hitung total hari aktif dalam periode (exclude weekend)
            $totalHariAktif = $this->attendanceModel->countActiveDays($period['start_date'], $period['end_date']);
            
            // Ambil semua siswa di kelas
            $db = \Config\Database::connect();
            $allStudents = $db->table('student_academic_histories as sah')
                ->select('students.id, students.nis, students.full_name')
                ->join('students', 'students.id = sah.student_id')
                ->where('sah.classroom_id', $classroomId)
                ->where('sah.academic_period_id', $periodId)
                ->where('students.deleted_at', null)
                ->where('students.status', 'Aktif')
                ->orderBy('students.full_name', 'ASC')
                ->get()
                ->getResultArray();
            
            // Gabungkan dengan data absensi
            $attendanceMap = [];
            foreach ($attendances as $att) {
                $attendanceMap[$att['student_id']] = $att;
            }
            
            $result = [];
            foreach ($allStudents as $student) {
                $data = $attendanceMap[$student['id']] ?? [
                    'sakit' => 0,
                    'izin' => 0,
                    'alpha' => 0
                ];
                
                $totalTidakHadir = $data['sakit'] + $data['izin'] + $data['alpha'];
                $hadir = $totalHariAktif - $totalTidakHadir;
                
                $result[] = [
                    'nis' => $student['nis'],
                    'full_name' => $student['full_name'],
                    'sakit' => (int)$data['sakit'],
                    'izin' => (int)$data['izin'],
                    'alpha' => (int)$data['alpha'],
                    'hadir' => $hadir,
                    'total_hari' => $totalHariAktif
                ];
            }
            
            return $this->response->setJSON([
                'success' => true,
                'students' => $result,
                'total_hari_aktif' => $totalHariAktif,
                'periode' => $period['semester'] . ' - ' . $yearName
            ]);
            
        } catch (\Exception $e) {
            log_message('error', 'getRekapSemester error: ' . $e->getMessage());
            return $this->response->setJSON([
                'success' => false,
                'message' => $e->getMessage()
            ]);
        }
    }

    // Export PDF Rekap Bulanan
    public function exportPdfBulanan()
    {
        if ($redirect = $this->checkLogin()) return $redirect;
        
        if (!$this->isBkOrAdmin()) {
            return redirect()->to('/dashboard')->with('error', 'Akses ditolak!');
        }

        $classroomId = $this->request->getGet('classroom_id');
        $periodId    = $this->request->getGet('period_id');
        $month       = $this->request->getGet('month');
        $year        = $this->request->getGet('year');

        if (!$classroomId || !$periodId || !$month || !$year) {
            return redirect()->back()->with('error', 'Data tidak lengkap!');
        }

        // Validasi akses kelas
        $allowedIds = $this->getAllowedClassroomIds();
        if ($allowedIds !== null && !in_array($classroomId, $allowedIds)) {
            return redirect()->back()->with('error', 'Akses ditolak! Anda tidak mengampu kelas ini.');
        }

        $db = \Config\Database::connect();
        
        // Ambil data kelas
        $classroom = $this->classroomModel->find($classroomId);
        $grade = $this->gradeModel->find($classroom['grade_id']);
        
        // Ambil data Kepala Madrasah
        $settingModel = new \App\Models\SchoolSettingModel();
        $setting = $settingModel->getSettings();
        $kepalaSekolah = [
            'name' => $setting['principal_name'] ?? '',
            'nip' => $setting['principal_nip'] ?? '',
            'city' => $setting['city'] ?? 'Wonogiri'
        ];
        
        // Ambil data Guru BK
        $bkAssignment = $db->table('bk_assignments')
            ->select('teachers.name as teacher_name, teachers.nip as teacher_nip')
            ->join('teachers', 'teachers.id = bk_assignments.teacher_id')
            ->where('bk_assignments.classroom_id', $classroomId)
            ->where('bk_assignments.academic_period_id', $periodId)
            ->get()
            ->getRowArray();
        
        $waliKelas = $bkAssignment ?? [
            'teacher_name' => '_________________________',
            'teacher_nip' => ''
        ];
        
        // Ambil periode
        $period = $this->periodModel->find($periodId);
        
        // Hitung total hari aktif
        $startDate = "$year-$month-01";
        $endDate = date('Y-m-t', strtotime($startDate));
        $periodeStart = $period['start_date'];
        $periodeEnd = $period['end_date'];
        if ($startDate < $periodeStart) $startDate = $periodeStart;
        if ($endDate > $periodeEnd) $endDate = $periodeEnd;
        $totalHariAktif = $this->attendanceModel->countActiveDays($startDate, $endDate);
        
        // Fetch academic calendar holidays
        $holidayMap = [];
        $holidays = $db->table('calendar_events')
            ->select('calendar_events.start_date, calendar_events.end_date, calendar_events.title')
            ->join('calendar_categories', 'calendar_categories.id = calendar_events.category_id')
            ->where('calendar_categories.is_libur', 1)
            ->where('calendar_events.deleted_at', null)
            ->where('calendar_events.start_date <=', $endDate)
            ->where('calendar_events.end_date >=', $startDate)
            ->get()
            ->getResultArray();

        foreach ($holidays as $h) {
            $start = strtotime($h['start_date']);
            $end = strtotime($h['end_date']);
            for ($curr = $start; $curr <= $end; $curr = strtotime('+1 day', $curr)) {
                $dateStr = date('Y-m-d', $curr);
                if ($dateStr >= $startDate && $dateStr <= $endDate) {
                    $holidayMap[$dateStr] = $h['title'];
                }
            }
        }

        $days = [];
        $daysInMonth = (int)date('t', strtotime($startDate));
        for ($d = 1; $d <= $daysInMonth; $d++) {
            $currentDateString = sprintf('%s-%s-%02d', $year, $month, $d);
            $dayOfWeek = date('N', strtotime($currentDateString));
            $isSunday = ($dayOfWeek == 7);
            $isHoliday = isset($holidayMap[$currentDateString]);
            $holidayTitle = $holidayMap[$currentDateString] ?? null;
            
            $days[] = [
                'day' => $d,
                'is_off' => ($isSunday || $isHoliday),
                'holiday_title' => $holidayTitle,
            ];
        }

        // Ambil semua siswa di kelas
        $allStudents = $db->table('student_academic_histories as sah')
            ->select('students.id, students.nis, students.full_name, students.gender')
            ->join('students', 'students.id = sah.student_id')
            ->where('sah.classroom_id', $classroomId)
            ->where('sah.academic_period_id', $periodId)
            ->where('students.deleted_at', null)
            ->where('students.status', 'Aktif')
            ->orderBy('students.full_name', 'ASC')
            ->get()
            ->getResultArray();

        $attendancesRaw = $db->table('attendances')
            ->select('student_id, date, status')
            ->where('classroom_id', $classroomId)
            ->where('academic_period_id', $periodId)
            ->where('date >=', $startDate)
            ->where('date <=', $endDate)
            ->get()
            ->getResultArray();

        $attMap = [];
        $attDailyMap = [];
        foreach ($attendancesRaw as $a) {
            $sId = $a['student_id'];
            $status = $a['status'];
            $day = (int)date('j', strtotime($a['date']));
            
            if (!isset($attMap[$sId])) {
                $attMap[$sId] = ['sakit' => 0, 'izin' => 0, 'alpha' => 0];
            }
            if ($status === 'Sakit') $attMap[$sId]['sakit']++;
            elseif ($status === 'Izin') $attMap[$sId]['izin']++;
            elseif ($status === 'Alpha') $attMap[$sId]['alpha']++;

            $attDailyMap[$sId][$day] = $status;
        }
        
        $dataSiswa = [];
        $grandTotalA = $grandTotalI = $grandTotalS = 0;
        foreach ($allStudents as $student) {
            $sId = $student['id'];
            $data = $attMap[$sId] ?? ['sakit' => 0, 'izin' => 0, 'alpha' => 0];
            $totalA = (int)$data['alpha'];
            $totalI = (int)$data['izin'];
            $totalS = (int)$data['sakit'];
            $grandTotalA += $totalA;
            $grandTotalI += $totalI;
            $grandTotalS += $totalS;

            $studentDailyStatuses = [];
            for ($d = 1; $d <= $daysInMonth; $d++) {
                $status = $attDailyMap[$sId][$d] ?? null;
                if ($status === 'Sakit') {
                    $studentDailyStatuses[$d] = 'S';
                } elseif ($status === 'Izin') {
                    $studentDailyStatuses[$d] = 'I';
                } elseif ($status === 'Alpha') {
                    $studentDailyStatuses[$d] = 'A';
                } else {
                    $studentDailyStatuses[$d] = '.';
                }
            }
            
            $dataSiswa[] = [
                'nis' => $student['nis'],
                'full_name' => $student['full_name'],
                'gender' => $student['gender'],
                'alpha' => $totalA,
                'izin' => $totalI,
                'sakit' => $totalS,
                'total' => $totalA + $totalI + $totalS,
                'statuses' => $studentDailyStatuses
            ];
        }

        // Nama bulan
        $namaBulan = ['Januari', 'Februari', 'Maret', 'April', 'Mei', 'Juni', 'Juli', 'Agustus', 'September', 'Oktober', 'November', 'Desember'];
        
        $data = [
            'judul' => 'REKAP ABSENSI BULANAN',
            'subjudul' => 'Bulan: ' . $namaBulan[(int)$month - 1] . ' ' . $year,
            'kelas' => ['name' => $classroom['name'], 'level' => $grade['name']],
            'dataSiswa' => $dataSiswa,
            'days' => $days,
            'grandTotalA' => $grandTotalA,
            'grandTotalI' => $grandTotalI,
            'grandTotalS' => $grandTotalS,
            'totalSiswa' => count($dataSiswa),
            'kepalaSekolah' => $kepalaSekolah,
            'waliKelas' => $waliKelas,
            'totalHariAktif' => $totalHariAktif,
            'schoolSettings' => $setting,
            'month' => $month,
            'year' => $year,
        ];

        return view('attendance/export_pdf_bulanan', $data);
    }

    // Export PDF Rekap Semester
    public function exportPdfSemester()
    {
        if ($redirect = $this->checkLogin()) return $redirect;
        
        if (!$this->isBkOrAdmin()) {
            return redirect()->to('/dashboard')->with('error', 'Akses ditolak!');
        }

        $classroomId = $this->request->getGet('classroom_id');
        $periodId    = $this->request->getGet('period_id');

        if (!$classroomId || !$periodId) {
            return redirect()->back()->with('error', 'Data tidak lengkap!');
        }

        // Validasi akses kelas
        $allowedIds = $this->getAllowedClassroomIds();
        if ($allowedIds !== null && !in_array($classroomId, $allowedIds)) {
            return redirect()->back()->with('error', 'Akses ditolak! Anda tidak mengampu kelas ini.');
        }

        $db = \Config\Database::connect();
        
        // Ambil data kelas
        $classroom = $this->classroomModel->find($classroomId);
        $grade = $this->gradeModel->find($classroom['grade_id']);
        
        // Ambil data Kepala Madrasah
        $settingModel = new \App\Models\SchoolSettingModel();
        $setting = $settingModel->getSettings();
        $kepalaSekolah = [
            'name' => $setting['principal_name'] ?? '',
            'nip' => $setting['principal_nip'] ?? '',
            'city' => $setting['city'] ?? 'Wonogiri'
        ];
        
        // Ambil data Guru BK
        $bkAssignment = $db->table('bk_assignments')
            ->select('teachers.name as teacher_name, teachers.nip as teacher_nip')
            ->join('teachers', 'teachers.id = bk_assignments.teacher_id')
            ->where('bk_assignments.classroom_id', $classroomId)
            ->where('bk_assignments.academic_period_id', $periodId)
            ->get()
            ->getRowArray();
        
        $waliKelas = $bkAssignment ?? [
            'teacher_name' => '_________________________',
            'teacher_nip' => ''
        ];
        
        // Ambil data rekap
        $attendances = $this->attendanceModel->getSemesterRecap($classroomId, $periodId);
        
        // Ambil periode
        $period = $this->periodModel->find($periodId);
        
        $academicYear = $db->table('academic_years')
            ->select('name')
            ->where('id', $period['academic_year_id'])
            ->get()
            ->getRowArray();
        
        // Hitung total hari aktif
        $totalHariAktif = $this->attendanceModel->countActiveDays($period['start_date'], $period['end_date']);
        
        // Ambil semua siswa di kelas
        $allStudents = $db->table('student_academic_histories as sah')
            ->select('students.id, students.nis, students.full_name, students.gender')
            ->join('students', 'students.id = sah.student_id')
            ->where('sah.classroom_id', $classroomId)
            ->where('sah.academic_period_id', $periodId)
            ->where('students.deleted_at', null)
            ->where('students.status', 'Aktif')
            ->orderBy('students.full_name', 'ASC')
            ->get()
            ->getResultArray();
        
        // Gabungkan data
        $attendanceMap = [];
        foreach ($attendances as $att) {
            $attendanceMap[$att['student_id']] = $att;
        }
        
        $dataSiswa = [];
        $grandTotalA = $grandTotalI = $grandTotalS = 0;
        foreach ($allStudents as $student) {
            $data = $attendanceMap[$student['id']] ?? ['sakit' => 0, 'izin' => 0, 'alpha' => 0];
            $totalA = (int)$data['alpha'];
            $totalI = (int)$data['izin'];
            $totalS = (int)$data['sakit'];
            $grandTotalA += $totalA;
            $grandTotalI += $totalI;
            $grandTotalS += $totalS;
            
            $dataSiswa[] = [
                'nis' => $student['nis'],
                'full_name' => $student['full_name'],
                'gender' => $student['gender'],
                'alpha' => $totalA,
                'izin' => $totalI,
                'sakit' => $totalS,
                'total' => $totalA + $totalI + $totalS
            ];
        }

        $data = [
            'judul' => 'REKAP ABSENSI SEMESTER',
            'subjudul' => 'Periode: ' . $period['semester'] . ' - ' . ($academicYear['name'] ?? ''),
            'kelas' => ['name' => $classroom['name'], 'level' => $grade['name']],
            'dataSiswa' => $dataSiswa,
            'grandTotalA' => $grandTotalA,
            'grandTotalI' => $grandTotalI,
            'grandTotalS' => $grandTotalS,
            'totalSiswa' => count($dataSiswa),
            'totalHariAktif' => $totalHariAktif,
            'kepalaSekolah' => $kepalaSekolah,
            'waliKelas' => $waliKelas,
        ];

        return view('attendance/export_pdf_semester', $data);
    }

    // Export Excel Rekap Bulanan
    public function exportExcelMonthly()
    {
        if ($redirect = $this->checkLogin()) return $redirect;
        
        if (!$this->isBkOrAdmin()) {
            return redirect()->to('/dashboard')->with('error', 'Akses ditolak!');
        }

        $classroomId = $this->request->getGet('classroom_id');
        $periodId    = $this->request->getGet('period_id');
        $month       = $this->request->getGet('month');
        $year        = $this->request->getGet('year');

        if (!$classroomId || !$periodId || !$month || !$year) {
            return redirect()->back()->with('error', 'Data tidak lengkap!');
        }

        // Validasi akses kelas
        $allowedIds = $this->getAllowedClassroomIds();
        if ($allowedIds !== null && !in_array($classroomId, $allowedIds)) {
            return redirect()->back()->with('error', 'Akses ditolak! Anda tidak mengampu kelas ini.');
        }

        $db = \Config\Database::connect();
        $classroom = $this->classroomModel->find($classroomId);
        $grade = $this->gradeModel->find($classroom['grade_id']);
        $period = $this->periodModel->find($periodId);
        
        $startDate = "$year-$month-01";
        $endDate = date('Y-m-t', strtotime($startDate));
        $periodeStart = $period['start_date'];
        $periodeEnd = $period['end_date'];
        if ($startDate < $periodeStart) $startDate = $periodeStart;
        if ($endDate > $periodeEnd) $endDate = $periodeEnd;
        $totalHariAktif = $this->attendanceModel->countActiveDays($startDate, $endDate);

        $allStudents = $db->table('student_academic_histories as sah')
            ->select('students.id, students.nis, students.full_name, students.gender')
            ->join('students', 'students.id = sah.student_id')
            ->where('sah.classroom_id', $classroomId)
            ->where('sah.academic_period_id', $periodId)
            ->where('students.deleted_at', null)
            ->where('students.status', 'Aktif')
            ->orderBy('students.full_name', 'ASC')
            ->get()
            ->getResultArray();

        $attendancesRaw = $db->table('attendances')
            ->select('student_id, date, status')
            ->where('classroom_id', $classroomId)
            ->where('academic_period_id', $periodId)
            ->where('date >=', $startDate)
            ->where('date <=', $endDate)
            ->get()->getResultArray();

        $attDailyMap = [];
        $attMap = [];
        foreach ($attendancesRaw as $a) {
            $sId = $a['student_id'];
            $day = (int) date('j', strtotime($a['date']));
            $attDailyMap[$sId][$day] = $a['status'];
            if (!isset($attMap[$sId])) {
                $attMap[$sId] = ['sakit' => 0, 'izin' => 0, 'alpha' => 0];
            }
            if ($a['status'] === 'Sakit') $attMap[$sId]['sakit']++;
            elseif ($a['status'] === 'Izin') $attMap[$sId]['izin']++;
            elseif ($a['status'] === 'Alpha') $attMap[$sId]['alpha']++;
        }

        $daysInMonth = (int) date('t', strtotime($startDate));
        $holidayMap = [];
        $holidays = $db->table('calendar_events')
            ->select('calendar_events.start_date, calendar_events.end_date')
            ->join('calendar_categories', 'calendar_categories.id = calendar_events.category_id')
            ->where('calendar_categories.is_libur', 1)
            ->where('calendar_events.deleted_at', null)
            ->where('calendar_events.start_date <=', $endDate)
            ->where('calendar_events.end_date >=', $startDate)
            ->get()->getResultArray();

        foreach ($holidays as $h) {
            $start = strtotime($h['start_date']);
            $end = strtotime($h['end_date']);
            for ($curr = $start; $curr <= $end; $curr = strtotime('+1 day', $curr)) {
                $dateStr = date('Y-m-d', $curr);
                if ($dateStr >= $startDate && $dateStr <= $endDate) {
                    $holidayMap[$dateStr] = true;
                }
            }
        }

        $namaBulan = ['Januari', 'Februari', 'Maret', 'April', 'Mei', 'Juni', 'Juli', 'Agustus', 'September', 'Oktober', 'November', 'Desember'];
        $bulanText = $namaBulan[(int)$month - 1];

        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Rekap Bulanan');

        $totalCols = 4 + $daysInMonth + 4;
        $lastCol = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($totalCols);

        $sheet->setCellValue('A1', 'REKAP ABSENSI BULANAN');
        $sheet->setCellValue('A2', "Bulan: {$bulanText} {$year} | Kelas: {$classroom['name']} ({$grade['name']})");
        $sheet->mergeCells("A1:{$lastCol}1");
        $sheet->mergeCells("A2:{$lastCol}2");
        $sheet->getStyle('A1:A2')->getFont()->setBold(true);
        $sheet->getStyle('A1')->getFont()->setSize(14);

        $sheet->setCellValue('A4', 'No');
        $sheet->setCellValue('B4', 'NIS');
        $sheet->setCellValue('C4', 'Nama Siswa');
        $sheet->setCellValue('D4', 'L/P');
        $sheet->getColumnDimension('A')->setWidth(5);
        $sheet->getColumnDimension('B')->setWidth(12);
        $sheet->getColumnDimension('C')->setWidth(28);
        $sheet->getColumnDimension('D')->setWidth(5);

        for ($d = 1; $d <= $daysInMonth; $d++) {
            $col = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(4 + $d);
            $dateStr = sprintf('%s-%02d-%02d', $year, $month, $d);
            $isOff = (date('N', strtotime($dateStr)) == 7 || isset($holidayMap[$dateStr]));
            $sheet->setCellValue($col . '4', $d);
            $sheet->getColumnDimension($col)->setWidth(4);
            if ($isOff) {
                $sheet->getStyle($col . '4')->applyFromArray([
                    'fill' => ['fillType' => 'solid', 'startColor' => ['rgb' => 'FFE5E5']],
                ]);
            }
        }

        $colS = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(4 + $daysInMonth + 1);
        $colI = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(4 + $daysInMonth + 2);
        $colA = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(4 + $daysInMonth + 3);
        $colH = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(4 + $daysInMonth + 4);

        $sheet->setCellValue($colS . '4', 'S');
        $sheet->setCellValue($colI . '4', 'I');
        $sheet->setCellValue($colA . '4', 'A');
        $sheet->setCellValue($colH . '4', 'H');
        $sheet->getColumnDimension($colS)->setWidth(5);
        $sheet->getColumnDimension($colI)->setWidth(5);
        $sheet->getColumnDimension($colA)->setWidth(5);
        $sheet->getColumnDimension($colH)->setWidth(5);

        $headerStyle = [
            'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
            'fill' => ['fillType' => 'solid', 'startColor' => ['rgb' => '0D6EFD']],
            'alignment' => ['horizontal' => 'center', 'vertical' => 'center'],
            'borders' => ['allBorders' => ['borderStyle' => 'thin', 'color' => ['rgb' => 'CCCCCC']]],
        ];
        $sheet->getStyle('A4:' . $lastCol . '4')->applyFromArray($headerStyle);

        $rowNum = 5;
        $totalS = $totalI = $totalA = $totalH = 0;
        foreach ($allStudents as $idx => $student) {
            $sId = $student['id'];
            $data = $attMap[$sId] ?? ['sakit' => 0, 'izin' => 0, 'alpha' => 0];
            $sakit = $data['sakit'];
            $izin = $data['izin'];
            $alpha = $data['alpha'];
            $hadir = $totalHariAktif - ($sakit + $izin + $alpha);
            if ($hadir < 0) $hadir = 0;

            $totalS += $sakit;
            $totalI += $izin;
            $totalA += $alpha;
            $totalH += $hadir;

            $sheet->setCellValue('A' . $rowNum, $idx + 1);
            $sheet->setCellValue('B' . $rowNum, $student['nis']);
            $sheet->setCellValue('C' . $rowNum, $student['full_name']);
            $sheet->setCellValue('D' . $rowNum, $student['gender']);

            for ($d = 1; $d <= $daysInMonth; $d++) {
                $col = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(4 + $d);
                $dateStr = sprintf('%s-%02d-%02d', $year, $month, $d);
                $isOff = (date('N', strtotime($dateStr)) == 7 || isset($holidayMap[$dateStr]));

                if ($isOff) {
                    $sheet->getStyle($col . $rowNum)->applyFromArray([
                        'fill' => ['fillType' => 'solid', 'startColor' => ['rgb' => 'FFE5E5']],
                    ]);
                } else {
                    $status = $attDailyMap[$sId][$d] ?? null;
                    $val = '.';
                    if ($status === 'Sakit') $val = 'S';
                    elseif ($status === 'Izin') $val = 'I';
                    elseif ($status === 'Alpha') $val = 'A';
                    $sheet->setCellValue($col . $rowNum, $val);
                }
            }

            $sheet->setCellValue($colS . $rowNum, $sakit);
            $sheet->setCellValue($colI . $rowNum, $izin);
            $sheet->setCellValue($colA . $rowNum, $alpha);
            $sheet->setCellValue($colH . $rowNum, $hadir);

            $cellStyle = [
                'alignment' => ['horizontal' => 'center', 'vertical' => 'center'],
                'borders' => ['allBorders' => ['borderStyle' => 'thin', 'color' => ['rgb' => 'EAEAEA']]],
            ];
            $sheet->getStyle('A' . $rowNum . ':' . $lastCol . $rowNum)->applyFromArray($cellStyle);
            $sheet->getStyle('C' . $rowNum)->getAlignment()->setHorizontal('left');
            $rowNum++;
        }

        $sheet->setCellValue('A' . $rowNum, '');
        $sheet->setCellValue('B' . $rowNum, '');
        $sheet->setCellValue('C' . $rowNum, 'TOTAL');
        $sheet->setCellValue('D' . $rowNum, '');
        $sheet->setCellValue($colS . $rowNum, $totalS);
        $sheet->setCellValue($colI . $rowNum, $totalI);
        $sheet->setCellValue($colA . $rowNum, $totalA);
        $sheet->setCellValue($colH . $rowNum, $totalH);
        $sheet->getStyle('A' . $rowNum . ':' . $lastCol . $rowNum)->applyFromArray([
            'font' => ['bold' => true],
            'fill' => ['fillType' => 'solid', 'startColor' => ['rgb' => 'F2F2F2']],
            'alignment' => ['horizontal' => 'center', 'vertical' => 'center'],
            'borders' => ['allBorders' => ['borderStyle' => 'thin', 'color' => ['rgb' => 'CCCCCC']]],
        ]);

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="rekap_absen_bulanan_' . strtolower(str_replace(' ', '_', $classroom['name'])) . '_' . $month . '_' . $year . '.xlsx"');
        header('Cache-Control: max-age=0');
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;
    }

    // Export Excel Rekap Semester
    public function exportExcelSemester()
    {
        if ($redirect = $this->checkLogin()) return $redirect;
        
        if (!$this->isBkOrAdmin()) {
            return redirect()->to('/dashboard')->with('error', 'Akses ditolak!');
        }

        $classroomId = $this->request->getGet('classroom_id');
        $periodId    = $this->request->getGet('period_id');

        if (!$classroomId || !$periodId) {
            return redirect()->back()->with('error', 'Data tidak lengkap!');
        }

        // Validasi akses kelas
        $allowedIds = $this->getAllowedClassroomIds();
        if ($allowedIds !== null && !in_array($classroomId, $allowedIds)) {
            return redirect()->back()->with('error', 'Akses ditolak! Anda tidak mengampu kelas ini.');
        }

        $db = \Config\Database::connect();
        $classroom = $this->classroomModel->find($classroomId);
        $grade = $this->gradeModel->find($classroom['grade_id']);
        $period = $this->periodModel->find($periodId);
        
        $academicYear = $db->table('academic_years')
            ->select('name')
            ->where('id', $period['academic_year_id'])
            ->get()
            ->getRowArray();
        $yearName = $academicYear['name'] ?? '';

        $totalHariAktif = $this->attendanceModel->countActiveDays($period['start_date'], $period['end_date']);

        $allStudents = $db->table('student_academic_histories as sah')
            ->select('students.id, students.nis, students.full_name, students.gender')
            ->join('students', 'students.id = sah.student_id')
            ->where('sah.classroom_id', $classroomId)
            ->where('sah.academic_period_id', $periodId)
            ->where('students.deleted_at', null)
            ->where('students.status', 'Aktif')
            ->orderBy('students.full_name', 'ASC')
            ->get()
            ->getResultArray();

        $attendances = $this->attendanceModel->getSemesterRecap($classroomId, $periodId);
        $attendanceMap = [];
        foreach ($attendances as $att) {
            $attendanceMap[$att['student_id']] = $att;
        }

        // Create spreadsheet
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Rekap Semester');

        // Styles
        $sheet->setCellValue('A1', 'REKAP ABSENSI SEMESTER');
        $sheet->setCellValue('A2', "Periode: {$period['semester']} - {$yearName} | Kelas: {$classroom['name']} ({$grade['name']})");
        $sheet->mergeCells('A1:H1');
        $sheet->mergeCells('A2:H2');
        $sheet->getStyle('A1:A2')->getFont()->setBold(true);
        $sheet->getStyle('A1')->getFont()->setSize(14);

        // Header Table
        $headers = ['No', 'NIS', 'Nama Lengkap', 'JK', 'Sakit (S)', 'Izin (I)', 'Alpha (A)', 'Hadir (H)'];
        $widths = [6, 15, 35, 8, 12, 12, 12, 12];
        foreach ($headers as $i => $h) {
            $col = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($i + 1);
            $sheet->setCellValue($col . '4', $h);
            $sheet->getStyle($col . '4')->applyFromArray([
                'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
                'fill' => ['fillType' => 'solid', 'startColor' => ['rgb' => '198754']],
                'alignment' => ['horizontal' => 'center', 'vertical' => 'center'],
                'borders' => ['allBorders' => ['borderStyle' => 'thin', 'color' => ['rgb' => 'CCCCCC']]]
            ]);
            $sheet->getColumnDimension($col)->setWidth($widths[$i]);
        }

        $rowNum = 5;
        foreach ($allStudents as $idx => $student) {
            $data = $attendanceMap[$student['id']] ?? ['sakit' => 0, 'izin' => 0, 'alpha' => 0];
            $sakit = (int)$data['sakit'];
            $izin = (int)$data['izin'];
            $alpha = (int)$data['alpha'];
            $totalTidakHadir = $sakit + $izin + $alpha;
            $hadir = $totalHariAktif - $totalTidakHadir;

            $sheet->setCellValue('A' . $rowNum, $idx + 1);
            $sheet->setCellValue('B' . $rowNum, $student['nis']);
            $sheet->setCellValue('C' . $rowNum, $student['full_name']);
            $sheet->setCellValue('D' . $rowNum, $student['gender']);
            $sheet->setCellValue('E' . $rowNum, $sakit);
            $sheet->setCellValue('F' . $rowNum, $izin);
            $sheet->setCellValue('G' . $rowNum, $alpha);
            $sheet->setCellValue('H' . $rowNum, $hadir);

            foreach (range(1, 8) as $colIdx) {
                $col = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($colIdx);
                $hAlign = ($colIdx === 3) ? 'left' : 'center';
                $sheet->getStyle($col . $rowNum)->applyFromArray([
                    'alignment' => ['horizontal' => $hAlign, 'vertical' => 'center'],
                    'borders' => ['allBorders' => ['borderStyle' => 'thin', 'color' => ['rgb' => 'EAEAEA']]]
                ]);
            }
            $rowNum++;
        }

        // Output Excel
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="rekap_absen_semester_' . strtolower(str_replace(' ', '_', $classroom['name'])) . '_' . $periodId . '.xlsx"');
        header('Cache-Control: max-age=0');
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;
    }
}