<?php

namespace App\Controllers\Admin;

use App\Controllers\BaseController;
use App\Models\StudentModel;
use App\Models\StudentAcademicHistoryModel;
use App\Models\AcademicPeriodModel;
use App\Models\ClassroomModel;
use App\Models\GradeModel;
use App\Services\StorageService;

class Student extends BaseController
{
    protected $model;
    protected $historyModel;
    protected $periodModel;
    protected $classroomModel;
    protected $gradeModel;

    public function __construct()
    {
        $this->model          = new StudentModel();
        $this->historyModel   = new StudentAcademicHistoryModel();
        $this->periodModel    = new AcademicPeriodModel();
        $this->classroomModel = new ClassroomModel();
        $this->gradeModel     = new GradeModel();
    }

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

    public function index()
    {
        if ($redirect = $this->checkLogin()) return $redirect;
 
        $data = [
            'title'        => 'Data Siswa',
            'activePeriod' => $this->periodModel->getActive(),
            'grades'       => $this->gradeModel->orderBy('order', 'ASC')->findAll(),
            'classrooms'   => $this->classroomModel->getAllWithRelations(),
        ];
 
        return view('student/index', $data);
    }

    public function create()
    {
        if ($redirect = $this->checkLogin()) return $redirect;

        $entryYear    = date('Y');
        $suggestNis   = $this->model->getSuggestNis($entryYear);
        $lastNis      = $this->model->getLastNis($entryYear);

        $data = [
            'title'       => 'Tambah Siswa',
            'suggestNis'  => $suggestNis,
            'lastNis'     => $lastNis ? $lastNis['nis'] : '-',
            'classrooms'  => $this->classroomModel->getAllWithRelations(),
            'activePeriod'=> $this->periodModel->getActive(),
        ];

        return view('student/create', $data);
    }

    public function store()
    {
        if ($redirect = $this->checkLogin()) return $redirect;

        $rules = [
            'nis'       => 'required|max_length[20]|is_unique[students.nis]',
            'full_name' => 'required|min_length[3]|max_length[100]',
            'gender'    => 'required|in_list[L,P]',
            'entry_year'=> 'required',
        ];

        if (!$this->validate($rules)) {
            return redirect()->back()
                ->with('errors', $this->validator->getErrors())
                ->withInput();
        }

        $studentId = $this->model->insert([
            'nis'                       => $this->request->getPost('nis'),
            'nisn'                      => $this->request->getPost('nisn'),
            'nik'                       => $this->request->getPost('nik'),
            'kip_no'                    => $this->request->getPost('kip_no'),
            'full_name'                 => $this->request->getPost('full_name'),
            'gender'                    => $this->request->getPost('gender'),
            'birth_place'               => $this->request->getPost('birth_place'),
            'birth_date'                => $this->request->getPost('birth_date'),
            'religion'                  => $this->request->getPost('religion'),
            'entry_year'                => $this->request->getPost('entry_year'),
            'entry_date'                => $this->request->getPost('entry_date'),
            'origin_school'             => $this->request->getPost('origin_school'),
            'graduation_certificate_no' => $this->request->getPost('graduation_certificate_no'),
            'status'                    => 'Aktif',
            'address'                   => $this->request->getPost('address'),
            'rt'                        => $this->request->getPost('rt'),
            'rw'                        => $this->request->getPost('rw'),
            'village'                   => $this->request->getPost('village'),
            'district'                  => $this->request->getPost('district'),
            'city'                      => $this->request->getPost('city'),
            'province'                  => $this->request->getPost('province'),
            'postal_code'               => $this->request->getPost('postal_code'),
            'phone'                     => $this->request->getPost('phone'),
            'family_card_no'            => $this->request->getPost('family_card_no'),
            'family_head_name'          => $this->request->getPost('family_head_name'),
            'father_name'               => $this->request->getPost('father_name'),
            'father_nik'                => $this->request->getPost('father_nik'),
            'father_birth_place'        => $this->request->getPost('father_birth_place'),
            'father_birth'              => $this->request->getPost('father_birth') ?: null,
            'father_edu'                => $this->request->getPost('father_edu'),
            'father_job'                => $this->request->getPost('father_job'),
            'father_phone'              => $this->request->getPost('father_phone'),
            'mother_name'               => $this->request->getPost('mother_name'),
            'mother_nik'                => $this->request->getPost('mother_nik'),
            'mother_birth_place'        => $this->request->getPost('mother_birth_place'),
            'mother_birth'              => $this->request->getPost('mother_birth') ?: null,
            'mother_edu'                => $this->request->getPost('mother_edu'),
            'mother_job'                => $this->request->getPost('mother_job'),
            'mother_phone'              => $this->request->getPost('mother_phone'),
        ]);

        // Assign ke kelas jika ada periode aktif
        $classroomId  = $this->request->getPost('classroom_id');
        $activePeriod = $this->periodModel->getActive();

        if ($studentId && $classroomId && $activePeriod) {
            $this->historyModel->insert([
                'student_id'         => $studentId,
                'academic_period_id' => $activePeriod['id'],
                'classroom_id'       => $classroomId,
                'status'             => 'Aktif',
            ]);
        }
        
        if ($studentId) {
            log_activity('create', 'siswa', $studentId, "Menambahkan siswa baru: " . $this->request->getPost('full_name') . " (NIS: " . $this->request->getPost('nis') . ")");
            
            // Buat akun login siswa secara otomatis
            $username = $this->request->getPost('nisn') ?: $this->request->getPost('nis');
            if ($username) {
                $userModel = new \App\Models\UserModel();
                $birthDate = $this->request->getPost('birth_date');
                $defaultPassword = !empty($birthDate) ? date('dmY', strtotime($birthDate)) : 'siswa123';
                
                $userModel->insert([
                    'name'              => $this->request->getPost('full_name'),
                    'username'          => $username,
                    'email'             => $this->request->getPost('nisn') ? $this->request->getPost('nisn') . '@simak.id' : $username . '@simak.id',
                    'password'          => password_hash($defaultPassword, PASSWORD_DEFAULT),
                    'role'              => 'siswa',
                    'is_active'         => 1,
                    'student_id'        => $studentId,
                    'is_password_reset' => 1,
                ]);
            }
        }

        return redirect()->to('/student')
            ->with('success', 'Data siswa berhasil ditambahkan!');
    }

    public function show($id)
    {
        if ($redirect = $this->checkLogin()) return $redirect;

        $student = $this->model->find($id);
        if (!$student) {
            return redirect()->to('/student')
                ->with('error', 'Data siswa tidak ditemukan!');
        }

        $data = [
            'title'    => 'Detail Siswa',
            'student'  => $student,
            'histories'=> $this->historyModel->getByStudent($id),
        ];

        return view('student/show', $data);
    }

    public function edit($id)
    {
        if ($redirect = $this->checkLogin()) return $redirect;

        $student = $this->model->find($id);
        if (!$student) {
            return redirect()->to('/student')
                ->with('error', 'Data siswa tidak ditemukan!');
        }

        $data = [
            'title'      => 'Edit Siswa',
            'student'    => $student,
            'classrooms' => $this->classroomModel->getAllWithRelations(),
        ];

        return view('student/edit', $data);
    }

    public function update($id)
    {
        if ($redirect = $this->checkLogin()) return $redirect;

        $rules = [
            'nis'       => "required|max_length[20]|is_unique[students.nis,id,{$id}]",
            'full_name' => 'required|min_length[3]|max_length[100]',
            'gender'    => 'required|in_list[L,P]',
        ];

        if (!$this->validate($rules)) {
            return redirect()->back()
                ->with('errors', $this->validator->getErrors())
                ->withInput();
        }

        $status = $this->request->getPost('status');

        $this->model->update($id, [
            'nis'                       => $this->request->getPost('nis'),
            'nisn'                      => $this->request->getPost('nisn'),
            'nik'                       => $this->request->getPost('nik'),
            'kip_no'                    => $this->request->getPost('kip_no'),
            'full_name'                 => $this->request->getPost('full_name'),
            'gender'                    => $this->request->getPost('gender'),
            'birth_place'               => $this->request->getPost('birth_place'),
            'birth_date'                => $this->request->getPost('birth_date'),
            'religion'                  => $this->request->getPost('religion'),
            'entry_year'                => $this->request->getPost('entry_year'),
            'entry_date'                => $this->request->getPost('entry_date'),
            'origin_school'             => $this->request->getPost('origin_school'),
            'graduation_certificate_no' => $this->request->getPost('graduation_certificate_no'),
            'status'                    => $status,
            'exit_date'                 => $this->request->getPost('exit_date') ?: null,
            'exit_to'                   => $this->request->getPost('exit_to') ?: null,
            'exit_reason'               => $this->request->getPost('exit_reason') ?: null,
            'address'                   => $this->request->getPost('address'),
            'rt'                        => $this->request->getPost('rt'),
            'rw'                        => $this->request->getPost('rw'),
            'village'                   => $this->request->getPost('village'),
            'district'                  => $this->request->getPost('district'),
            'city'                      => $this->request->getPost('city'),
            'province'                  => $this->request->getPost('province'),
            'postal_code'               => $this->request->getPost('postal_code'),
            'phone'                     => $this->request->getPost('phone'),
            'family_card_no'            => $this->request->getPost('family_card_no'),
            'family_head_name'          => $this->request->getPost('family_head_name'),
            'father_name'               => $this->request->getPost('father_name'),
            'father_nik'                => $this->request->getPost('father_nik'),
            'father_birth_place'        => $this->request->getPost('father_birth_place'),
            'father_birth'              => $this->request->getPost('father_birth') ?: null,
            'father_edu'                => $this->request->getPost('father_edu'),
            'father_job'                => $this->request->getPost('father_job'),
            'father_phone'              => $this->request->getPost('father_phone'),
            'mother_name'               => $this->request->getPost('mother_name'),
            'mother_nik'                => $this->request->getPost('mother_nik'),
            'mother_birth_place'        => $this->request->getPost('mother_birth_place'),
            'mother_birth'              => $this->request->getPost('mother_birth') ?: null,
            'mother_edu'                => $this->request->getPost('mother_edu'),
            'mother_job'                => $this->request->getPost('mother_job'),
            'mother_phone'              => $this->request->getPost('mother_phone'),
        ]);

        log_activity('update', 'siswa', $id, "Mengubah data siswa: " . $this->request->getPost('full_name') . " (NIS: " . $this->request->getPost('nis') . ")");

        // Sinkronisasi data akun login siswa
        $username = $this->request->getPost('nisn') ?: $this->request->getPost('nis');
        if ($username) {
            $userModel = new \App\Models\UserModel();
            $studentUser = $userModel->where('student_id', $id)->first();
            if ($studentUser) {
                $userModel->update($studentUser['id'], [
                    'name'      => $this->request->getPost('full_name'),
                    'username'  => $username,
                    'is_active' => ($status === 'Aktif' ? 1 : 0),
                ]);
            } else {
                $birthDate = $this->request->getPost('birth_date');
                $defaultPassword = !empty($birthDate) ? date('dmY', strtotime($birthDate)) : 'siswa123';
                $userModel->insert([
                    'name'              => $this->request->getPost('full_name'),
                    'username'          => $username,
                    'email'             => $this->request->getPost('nisn') ? $this->request->getPost('nisn') . '@simak.id' : $username . '@simak.id',
                    'password'          => password_hash($defaultPassword, PASSWORD_DEFAULT),
                    'role'              => 'siswa',
                    'is_active'         => ($status === 'Aktif' ? 1 : 0),
                    'student_id'        => $id,
                    'is_password_reset' => 1,
                ]);
            }
        }

        // Sinkronisasi status di tabel student_academic_histories untuk periode akademik aktif
        $activePeriod = $this->periodModel->getActive();
        if ($activePeriod) {
            $historyStatus = 'Aktif';
            if ($status === 'Mutasi') {
                $historyStatus = 'Mutasi';
            } elseif ($status === 'Drop Out') {
                $historyStatus = 'Drop Out';
            } elseif ($status === 'Alumni') {
                $historyStatus = 'Lulus';
            }
            
            $db = \Config\Database::connect();
            $db->table('student_academic_histories')
                ->where('student_id', $id)
                ->where('academic_period_id', $activePeriod['id'])
                ->update(['status' => $historyStatus, 'updated_at' => date('Y-m-d H:i:s')]);
        }

        return redirect()->to('/student/show/' . $id)
            ->with('success', 'Data siswa berhasil diupdate!');
    }

    public function delete($id)
    {
        if ($redirect = $this->checkLogin()) return $redirect;

        $student = $this->model->find($id);
        $studentName = $student ? $student['full_name'] : 'Tidak diketahui';
        $studentNis = $student ? $student['nis'] : '-';

        $this->model->delete($id);

        log_activity('delete', 'siswa', $id, "Menghapus siswa: {$studentName} (NIS: {$studentNis})");

        return redirect()->to('/student')
            ->with('success', 'Data siswa berhasil dihapus!');
    }

    public function byClass()
    {
        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,
                'students' => [],
            ]);
        }
 
        $db = \Config\Database::connect();
 
        $students = $db->table('student_academic_histories as sah')
            ->select('
                students.id,
                students.nis,
                students.nisn,
                students.full_name,
                students.gender,
                students.birth_place,
                students.birth_date,
                students.photo
            ')
            ->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();
 
        // Pemetaan URL foto dinamis menggunakan StorageService
        foreach ($students as &$s) {
            $s['photo_url'] = StorageService::getUrl($s['photo'], 'students');
        }

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

    public function getSuggestNis()
    {
        if ($redirect = $this->checkLogin()) return $redirect;

        $year       = $this->request->getGet('year') ?? date('Y');
        $suggest    = $this->model->getSuggestNis($year);
        $last       = $this->model->getLastNis($year);

        return $this->response->setJSON([
            'suggest' => $suggest,
            'last'    => $last ? $last['nis'] : '-',
        ]);
    }

    public function importExcel()
    {
        if ($redirect = $this->checkLogin()) return $redirect;

        $data = [
            'title' => 'Import Excel Siswa',
            'activePeriod' => $this->periodModel->getActive(),
            'classrooms'   => $this->classroomModel->getAllWithRelations(),
        ];

        return view('student/import', $data);
    }

    public function importProcess()
    {
        if ($redirect = $this->checkLogin()) return $redirect;

        $file = $this->request->getFile('excel_file');

        if (!$file || !$file->isValid()) {
            return redirect()->back()->with('error', 'File tidak valid!');
        }

        $ext = strtolower($file->getClientExtension());
        if (!in_array($ext, ['xlsx', 'xls'])) {
            return redirect()->back()->with('error', 'File harus berformat .xlsx atau .xls!');
        }

        try {
            $reader      = \PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile($file->getTempName());
            $spreadsheet = $reader->load($file->getTempName());
            $sheet       = $spreadsheet->getActiveSheet();
            $rows        = $sheet->toArray();

            // Hapus header row
            array_shift($rows); // baris 1: group header
            array_shift($rows); // baris 2: nama kolom

            $success = 0;
            $errors  = [];
            $classroomId  = $this->request->getPost('classroom_id');
            $activePeriod = $this->periodModel->getActive();

            foreach ($rows as $i => $row) {
                $rowNum = $i + 2;

                // Skip baris kosong
                if (empty($row[0]) && empty($row[2])) continue;

                $nis          = trim($row[0]  ?? '');
                $nisn         = trim($row[1]  ?? '');
                $nik          = trim($row[2]  ?? '');
                $kipNo        = trim($row[3]  ?? '');
                $fullName     = trim($row[4]  ?? '');
                $gender       = strtoupper(trim($row[5] ?? ''));
                $religion     = trim($row[6]  ?? 'Islam');
                $birthPlace   = trim($row[7]  ?? '');
                $birthDate    = trim($row[8]  ?? '');
                $phone        = trim($row[9]  ?? '');
                $entryYear    = trim($row[10] ?? date('Y'));
                $entryDate    = trim($row[11] ?? '');
                $originSchool = trim($row[12] ?? '');
                $gradCertNo   = trim($row[13] ?? '');
                $status       = trim($row[14] ?? 'Aktif');
                $address      = trim($row[15] ?? '');
                $rt           = trim($row[16] ?? '');
                $rw           = trim($row[17] ?? '');
                $village      = trim($row[18] ?? '');
                $district     = trim($row[19] ?? '');
                $city         = trim($row[20] ?? '');
                $province     = trim($row[21] ?? '');
                $postalCode   = trim($row[22] ?? '');
                $familyCardNo = trim($row[23] ?? '');
                $familyHead   = trim($row[24] ?? '');
                
                $fatherName       = trim($row[25] ?? '');
                $fatherNik        = trim($row[26] ?? '');
                $fatherBirthPlace = trim($row[27] ?? '');
                $fatherBirth      = trim($row[28] ?? '');
                $fatherEdu        = trim($row[29] ?? '');
                $fatherJob        = trim($row[30] ?? '');
                $fatherPhone      = trim($row[31] ?? '');
                
                $motherName       = trim($row[32] ?? '');
                $motherNik        = trim($row[33] ?? '');
                $motherBirthPlace = trim($row[34] ?? '');
                $motherBirth      = trim($row[35] ?? '');
                $motherEdu        = trim($row[36] ?? '');
                $motherJob        = trim($row[37] ?? '');
                $motherPhone      = trim($row[38] ?? '');

                // Validasi wajib
                if (empty($nis)) {
                    $errors[] = "Baris {$rowNum}: NIS kosong";
                    continue;
                }
                if (empty($fullName)) {
                    $errors[] = "Baris {$rowNum}: Nama kosong";
                    continue;
                }
                if (!in_array($gender, ['L', 'P'])) {
                    $errors[] = "Baris {$rowNum}: Jenis kelamin harus L atau P";
                    continue;
                }

                // Cek NIS duplikat
                $existing = $this->model->where('nis', $nis)->withDeleted()->first();
                if ($existing) {
                    $errors[] = "Baris {$rowNum}: NIS {$nis} sudah ada";
                    continue;
                }

                // Format tanggal lahir
                if (!empty($birthDate)) {
                    try {
                        if (is_numeric($birthDate)) {
                            $birthDate = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($birthDate)->format('Y-m-d');
                        } else {
                            $birthDate = date('Y-m-d', strtotime($birthDate));
                        }
                    } catch (\Exception $e) {
                        $birthDate = null;
                    }
                } else {
                    $birthDate = null;
                }

                // Format tanggal lahir ayah
                if (!empty($fatherBirth)) {
                    try {
                        if (is_numeric($fatherBirth)) {
                            $fatherBirth = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($fatherBirth)->format('Y-m-d');
                        } else {
                            $fatherBirth = date('Y-m-d', strtotime($fatherBirth));
                        }
                    } catch (\Exception $e) {
                        $fatherBirth = null;
                    }
                } else {
                    $fatherBirth = null;
                }

                // Format tanggal lahir ibu
                if (!empty($motherBirth)) {
                    try {
                        if (is_numeric($motherBirth)) {
                            $motherBirth = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($motherBirth)->format('Y-m-d');
                        } else {
                            $motherBirth = date('Y-m-d', strtotime($motherBirth));
                        }
                    } catch (\Exception $e) {
                        $motherBirth = null;
                    }
                } else {
                    $motherBirth = null;
                }

                // Simpan siswa
                $studentId = $this->model->insert([
                    'nis'                       => $nis,
                    'nisn'                      => $nisn ?: null,
                    'nik'                       => $nik ?: null,
                    'kip_no'                    => $kipNo ?: null,
                    'full_name'                 => $fullName,
                    'gender'                    => $gender,
                    'religion'                  => in_array($religion, ['Islam','Kristen','Katolik','Hindu','Buddha','Konghucu']) ? $religion : 'Islam',
                    'birth_place'               => $birthPlace ?: null,
                    'birth_date'                => $birthDate ?: null,
                    'phone'                     => $phone ?: null,
                    'entry_year'                => $entryYear ?: date('Y'),
                    'entry_date'                => $entryDate ?: null,
                    'origin_school'             => $originSchool ?: null,
                    'graduation_certificate_no' => $gradCertNo ?: null,
                    'status'                    => in_array($status, ['Aktif','Alumni','Mutasi','Drop Out']) ? $status : 'Aktif',
                    'address'                   => $address ?: null,
                    'rt'                        => $rt ?: null,
                    'rw'                        => $rw ?: null,
                    'village'                   => $village ?: null,
                    'district'                  => $district ?: null,
                    'city'                      => $city ?: null,
                    'province'                  => $province ?: null,
                    'postal_code'               => $postalCode ?: null,
                    'family_card_no'            => $familyCardNo ?: null,
                    'family_head_name'          => $familyHead ?: null,
                    'father_name'               => $fatherName ?: null,
                    'father_nik'                => $fatherNik ?: null,
                    'father_birth_place'        => $fatherBirthPlace ?: null,
                    'father_birth'              => $fatherBirth ?: null,
                    'father_edu'                => $fatherEdu ?: null,
                    'father_job'                => $fatherJob ?: null,
                    'father_phone'              => $fatherPhone ?: null,
                    'mother_name'               => $motherName ?: null,
                    'mother_nik'                => $motherNik ?: null,
                    'mother_birth_place'        => $motherBirthPlace ?: null,
                    'mother_birth'              => $motherBirth ?: null,
                    'mother_edu'                => $motherEdu ?: null,
                    'mother_job'                => $motherJob ?: null,
                    'mother_phone'              => $motherPhone ?: null,
                ]);

                // Assign ke kelas jika ada
                if ($studentId && $classroomId && $activePeriod) {
                    $this->historyModel->insert([
                        'student_id'         => $studentId,
                        'academic_period_id' => $activePeriod['id'],
                        'classroom_id'       => $classroomId,
                        'status'             => 'Aktif',
                    ]);
                }

                // Buat akun login siswa secara otomatis jika belum ada
                $username = $nisn ?: $nis;
                if ($studentId && $username) {
                    $userModel = new \App\Models\UserModel();
                    $existingUser = $userModel->where('username', $username)->first();
                    if (!$existingUser) {
                        $defaultPassword = !empty($birthDate) ? date('dmY', strtotime($birthDate)) : 'siswa123';
                        $userModel->insert([
                            'name'              => $fullName,
                            'username'          => $username,
                            'email'             => $nisn ? $nisn . '@simak.id' : $username . '@simak.id',
                            'password'          => password_hash($defaultPassword, PASSWORD_DEFAULT),
                            'role'              => 'siswa',
                            'is_active'         => 1,
                            'student_id'        => $studentId,
                            'is_password_reset' => 1,
                        ]);
                    }
                }

                $success++;
            }

            log_activity('import', 'siswa', null, "Melakukan import data siswa sebanyak {$success} records");

            $message = "Berhasil import {$success} siswa.";
            if (!empty($errors)) {
                session()->setFlashdata('import_errors', $errors);
            }

            return redirect()->to('/student/import')
                ->with('success', $message);

        } catch (\Exception $e) {
            return redirect()->back()
                ->with('error', 'Gagal membaca file: ' . $e->getMessage());
        }
    }

    public function downloadTemplate()
    {
        if ($redirect = $this->checkLogin()) return $redirect;

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

        // Group headers (baris 1)
        $groups = [
            ['label' => 'IDENTITAS SISWA',  'color' => '0D6EFD', 'start' => 1,  'end' => 10],
            ['label' => 'DATA AKADEMIK',     'color' => '198754', 'start' => 11, 'end' => 15],
            ['label' => 'ALAMAT',            'color' => '6F42C1', 'start' => 16, 'end' => 23],
            ['label' => 'KARTU KELUARGA',    'color' => '20B2AA', 'start' => 24, 'end' => 25],
            ['label' => 'DATA AYAH',         'color' => 'FD7E14', 'start' => 26, 'end' => 32],
            ['label' => 'DATA IBU',          'color' => 'DC3545', 'start' => 33, 'end' => 39],
        ];

        foreach ($groups as $g) {
            $startCell = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($g['start']) . '1';
            $endCell   = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($g['end']) . '1';
            $sheet->mergeCells($startCell . ':' . $endCell);
            $sheet->setCellValue($startCell, $g['label']);
            $sheet->getStyle($startCell)->applyFromArray([
                'font'      => ['bold' => true, 'color' => ['rgb' => 'FFFFFF'], 'size' => 10],
                'fill'      => ['fillType' => 'solid', 'startColor' => ['rgb' => $g['color']]],
                'alignment' => ['horizontal' => 'center', 'vertical' => 'center'],
            ]);
        }

        // Column headers (baris 2)
        $columns = [
            // Identitas
            ['NIS *',            14], ['NISN',             14], ['NIK',              18], ['No. KIP',          18],
            ['Nama Lengkap *',   28], ['Jenis Kelamin *',  12], ['Agama',            15],
            ['Tempat Lahir',     18], ['Tanggal Lahir',    18], ['No. HP Siswa',     16],
            // Akademik
            ['Tahun Masuk *',   12], ['Tanggal Masuk',    16], ['Asal Sekolah',     28], ['No. Seri Ijazah',  20], ['Status', 13],
            // Alamat
            ['Alamat',          35], ['RT',  7], ['RW', 7], ['Desa/Kelurahan', 18],
            ['Kecamatan',       18], ['Kota/Kabupaten', 18], ['Provinsi', 18], ['Kode Pos', 10],
            // Kartu Keluarga
            ['No. KK',          18], ['Nama Kepala Keluarga', 28],
            // Ayah
            ['Nama Ayah',       24], ['NIK Ayah',         18], ['Tempat Lahir Ayah', 18], ['Tgl Lahir Ayah',  16],
            ['Pendidikan Ayah', 14], ['Pekerjaan Ayah',   18], ['No. HP Ayah',     16],
            // Ibu
            ['Nama Ibu',        24], ['NIK Ibu',          18], ['Tempat Lahir Ibu', 18], ['Tgl Lahir Ibu',   16],
            ['Pendidikan Ibu',  14], ['Pekerjaan Ibu',    18], ['No. HP Ibu',      16],
        ];

        foreach ($columns as $i => [$label, $width]) {
            $col  = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($i + 1);
            $cell = $col . '2';
            $sheet->setCellValue($cell, $label);
            $sheet->getStyle($cell)->applyFromArray([
                'font'      => ['bold' => true, 'size' => 9],
                'fill'      => ['fillType' => 'solid', 'startColor' => ['rgb' => 'F0F4FF']],
                'alignment' => ['horizontal' => 'center', 'vertical' => 'center', 'wrapText' => true],
                'borders'   => ['allBorders' => ['borderStyle' => 'thin', 'color' => ['rgb' => 'CCCCCC']]],
            ]);
            $sheet->getColumnDimension($col)->setWidth($width);
        }

        $sheet->getRowDimension(1)->setRowHeight(22);
        $sheet->getRowDimension(2)->setRowHeight(32);

        // Contoh data (baris 3)
        $example = [
            '250001', '1234567890', '3372012345670001', 'KIP-12345',
            'Ahmad Fauzi', 'L', 'Islam', 'Surakarta', '2012-05-15', '085712345678',
            '2025', '2025-07-14', 'SDN 1 Surakarta', 'Ijazah-12345', 'Aktif',
            'Jl. Merdeka No. 1', '001', '002', 'Sriwedari', 'Laweyan', 'Surakarta', 'Jawa Tengah', '57141',
            '3372012345670002', 'Ahmad Fauzi',
            'Budi Santoso', '3372019876540001', 'Surakarta', '1985-03-20', 'S1', 'Wiraswasta', '081234567890',
            'Sri Wahyuni', '3372019876540002', 'Wonogiri', '1988-07-10', 'S1', 'Ibu Rumah Tangga', '081298765432',
        ];

        foreach ($example as $i => $val) {
            $col  = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($i + 1);
            $sheet->setCellValue($col . '3', $val);
            $sheet->getStyle($col . '3')->applyFromArray([
                'font'  => ['size' => 9, 'color' => ['rgb' => '888888'], 'italic' => true],
                'fill'  => ['fillType' => 'solid', 'startColor' => ['rgb' => 'FAFAFA']],
                'borders' => ['allBorders' => ['borderStyle' => 'thin', 'color' => ['rgb' => 'CCCCCC']]],
            ]);
        }

        $sheet->freezePane('A3');

        // Sheet referensi
        $ref = $spreadsheet->createSheet();
        $ref->setTitle('Referensi');
        $refData = [
            ['Jenis Kelamin', 'L / P'],
            ['Agama', 'Islam, Kristen, Katolik, Hindu, Buddha, Konghucu'],
            ['Status', 'Aktif, Alumni, Mutasi, Drop Out'],
            ['Pendidikan', 'SD, SMP, SMA, D3, S1, S2, S3'],
            ['Format Tanggal', 'YYYY-MM-DD (contoh: 2012-05-15)'],
        ];
        $ref->setCellValue('A1', 'Field');
        $ref->setCellValue('B1', 'Nilai yang Valid');
        $ref->getStyle('A1:B1')->applyFromArray([
            'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
            'fill' => ['fillType' => 'solid', 'startColor' => ['rgb' => '0D6EFD']],
        ]);
        foreach ($refData as $r => $row) {
            $ref->setCellValue('A' . ($r + 2), $row[0]);
            $ref->setCellValue('B' . ($r + 2), $row[1]);
        }
        $ref->getColumnDimension('A')->setWidth(20);
        $ref->getColumnDimension('B')->setWidth(55);

        $spreadsheet->setActiveSheetIndex(0);

        $filename = 'template_import_siswa.xlsx';
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $filename . '"');
        header('Cache-Control: max-age=0');

        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;
    }


    // ── DATA ALUMNI & NON-AKTIF ───────────────────────────────
 
    public function alumni()
    {
        if ($redirect = $this->checkLogin()) return $redirect;
 
        $db = \Config\Database::connect();
 
        $students = $db->table('students')
            ->select('
                students.id,
                students.nis,
                students.nisn,
                students.full_name,
                students.gender,
                students.birth_place,
                students.birth_date,
                students.status,
                students.entry_year,
                students.exit_date,
                students.exit_to,
                students.exit_reason
            ')
            ->whereIn('students.status', ['Alumni', 'Mutasi', 'Drop Out'])
            ->where('students.deleted_at', null)
            ->orderBy('students.status', 'ASC')
            ->orderBy('students.full_name', 'ASC')
            ->get()
            ->getResultArray();
 
        $data = [
            'title'    => 'Data Alumni & Non-Aktif',
            'students' => $students,
        ];
 
        return view('student/alumni', $data);
    }
 
    // AJAX — aktifkan kembali siswa
    public function reactivate($id)
    {
        if ($redirect = $this->checkLogin()) return $redirect;
 
        $student = $this->model->find($id);
        if (!$student) {
            return $this->response->setJSON([
                'success' => false,
                'message' => 'Siswa tidak ditemukan!',
            ]);
        }
 
        // Aktifkan siswa
        $this->model->update($id, ['status' => 'Aktif']);

        log_activity('update', 'siswa', $id, "Mengaktifkan kembali siswa: " . $student['full_name'] . " (NIS: " . $student['nis'] . ")");
 
        // Cek history terakhir siswa
        $activePeriod  = $this->periodModel->getActive();
        $lastHistory   = $this->historyModel
            ->where('student_id', $id)
            ->orderBy('id', 'DESC')
            ->first();
 
        $kelasInfo = '-';
 
        if ($lastHistory && $activePeriod) {
            if ($lastHistory['academic_period_id'] == $activePeriod['id']) {
                // History terakhir = periode aktif → sudah punya kelas
                $classroom  = $this->classroomModel->find($lastHistory['classroom_id']);
                $kelasInfo  = $classroom ? $classroom['name'] : '-';
            }
            // Kalau periode berbeda → biarkan, assign manual lewat Assign Kelas
        }
 
        return $this->response->setJSON([
            'success'   => true,
            'message'   => $student['full_name'] . ' berhasil diaktifkan kembali.',
            'has_class' => ($lastHistory && $activePeriod && $lastHistory['academic_period_id'] == $activePeriod['id']),
            'kelas'     => $kelasInfo,
        ]);
    }

    // AJAX — proses siswa keluar (mutasi / drop out / alumni)
    public function exitProcess($id)
    {
        if ($redirect = $this->checkLogin()) return $redirect;

        $student = $this->model->find($id);
        if (!$student) {
            return $this->response->setJSON([
                'success'    => false,
                'message'    => 'Data siswa tidak ditemukan!',
                'csrf_token' => csrf_hash(),
            ]);
        }

        $rules = [
            'status'      => 'required|in_list[Mutasi,Drop Out,Alumni]',
            'exit_date'   => 'required|valid_date',
            'exit_to'     => 'permit_empty|max_length[150]',
            'exit_reason' => 'permit_empty',
        ];

        if (!$this->validate($rules)) {
            return $this->response->setJSON([
                'success'    => false,
                'message'    => implode(', ', $this->validator->getErrors()),
                'csrf_token' => csrf_hash(),
            ]);
        }

        $status = $this->request->getPost('status');
        $exitTo = $this->request->getPost('exit_to') ?: null;

        // Custom validation: Sekolah Tujuan is required if status is Mutasi
        if ($status === 'Mutasi' && empty($exitTo)) {
            return $this->response->setJSON([
                'success'    => false,
                'message'    => 'Sekolah tujuan mutasi wajib diisi!',
                'csrf_token' => csrf_hash(),
            ]);
        }

        $this->model->update($id, [
            'status'      => $status,
            'exit_date'   => $this->request->getPost('exit_date'),
            'exit_to'     => $exitTo,
            'exit_reason' => $this->request->getPost('exit_reason') ?: null,
        ]);

        log_activity('update', 'siswa', $id, "Memproses keluar siswa ({$status}): " . $student['full_name'] . " (NIS: " . $student['nis'] . ")");

        return $this->response->setJSON([
            'success'    => true,
            'message'    => $student['full_name'] . ' berhasil diproses keluar (' . $status . ').',
            'csrf_token' => csrf_hash(),
        ]);
    }

    // AJAX — Reset password akun siswa ke default (tanggal lahir DDMMYYYY)
    public function resetPassword($id)
    {
        if ($redirect = $this->checkLogin()) return $redirect;

        $student = $this->model->find($id);
        if (!$student) {
            return $this->response->setJSON([
                'success' => false,
                'message' => 'Siswa tidak ditemukan.'
            ]);
        }

        $userModel = new \App\Models\UserModel();
        $user = $userModel->where('student_id', $id)->first();

        $birthDate = $student['birth_date'];
        $defaultPassword = !empty($birthDate) ? date('dmY', strtotime($birthDate)) : 'siswa123';

        if ($user) {
            $userModel->update($user['id'], [
                'password'          => password_hash($defaultPassword, PASSWORD_DEFAULT),
                'is_password_reset' => 1,
            ]);
            
            return $this->response->setJSON([
                'success' => true,
                'message' => 'Password siswa ' . $student['full_name'] . ' berhasil direset ke password bawaan (' . $defaultPassword . ').'
            ]);
        }

        // Jika user akun belum ada, buat baru
        $username = $student['nisn'] ?: $student['nis'];
        if ($username) {
            $userModel->insert([
                'name'              => $student['full_name'],
                'username'          => $username,
                'email'             => $student['nisn'] ? $student['nisn'] . '@simak.id' : $username . '@simak.id',
                'password'          => password_hash($defaultPassword, PASSWORD_DEFAULT),
                'role'              => 'siswa',
                'is_active'         => 1,
                'student_id'        => $id,
                'is_password_reset' => 1,
            ]);
            return $this->response->setJSON([
                'success' => true,
                'message' => 'Akun baru berhasil dibuat untuk siswa ' . $student['full_name'] . ' dengan password bawaan (' . $defaultPassword . ').'
            ]);
        }

        return $this->response->setJSON([
            'success' => false,
            'message' => 'Siswa tidak memiliki NISN atau NIS untuk dijadikan username!'
        ]);
    }

    // Pembuatan akun massal untuk siswa lama yang belum mempunyai akun login
    public function generateMissingAccounts()
    {
        if ($redirect = $this->checkLogin()) return $redirect;

        $db = \Config\Database::connect();
        // Cari siswa yang belum memiliki record di users
        $students = $db->table('students s')
            ->select('s.id, s.full_name, s.nis, s.nisn, s.birth_date')
            ->join('users u', 'u.student_id = s.id', 'left')
            ->where('u.id', null)
            ->where('s.deleted_at', null)
            ->get()->getResultArray();

        if (empty($students)) {
            return redirect()->to('/student')->with('info', 'Semua siswa aktif saat ini sudah memiliki akun login.');
        }

        $userModel = new \App\Models\UserModel();
        $count = 0;

        foreach ($students as $student) {
            $username = $student['nisn'] ?: $student['nis'];
            if ($username) {
                // Pastikan username belum dipakai oleh user lain di database
                $exists = $userModel->where('username', $username)->first();
                if (!$exists) {
                    $birthDate = $student['birth_date'];
                    $defaultPassword = !empty($birthDate) ? date('dmY', strtotime($birthDate)) : 'siswa123';
                    
                    $userModel->insert([
                        'name'              => $student['full_name'],
                        'username'          => $username,
                        'email'             => $student['nisn'] ? $student['nisn'] . '@simak.id' : $username . '@simak.id',
                        'password'          => password_hash($defaultPassword, PASSWORD_DEFAULT),
                        'role'              => 'siswa',
                        'is_active'         => 1,
                        'student_id'        => $student['id'],
                        'is_password_reset' => 1,
                    ]);
                    $count++;
                }
            }
        }

        log_activity('create', 'user_management', null, "Generate massal akun login siswa sebanyak {$count} akun");

        return redirect()->to('/student')->with('success', "Berhasil membuat {$count} akun login baru untuk siswa yang belum memiliki akun.");
    }

    // ── MONITORING KELENGKAPAN DATA SISWA ─────────────────────────────
    public function completeness()
    {
        if ($redirect = $this->checkLogin()) return $redirect;

        $db = \Config\Database::connect();
        
        // Ambil periode akademik aktif sebagai default
        $activePeriod = $this->periodModel->getActive();
        $selectedPeriodId = $this->request->getGet('academic_period_id') ?: ($activePeriod ? $activePeriod['id'] : null);
        $selectedClassroomId = $this->request->getGet('classroom_id');

        // Load data dropdown filter
        $periods = $this->periodModel->getAllWithYear();
        $classrooms = $this->classroomModel->getAllWithRelations();

        // Query data siswa aktif di periode terpilih
        $builder = $db->table('students s')
            ->select('s.*, c.name as classroom_name, g.name as grade_name, g.order as grade_order')
            ->join('student_academic_histories sah', 'sah.student_id = s.id')
            ->join('classrooms c', 'c.id = sah.classroom_id')
            ->join('grades g', 'g.id = c.grade_id')
            ->where('s.deleted_at', null)
            ->where('s.status', 'Aktif');

        if ($selectedPeriodId) {
            $builder->where('sah.academic_period_id', $selectedPeriodId);
        }

        if (!empty($selectedClassroomId)) {
            $builder->where('sah.classroom_id', $selectedClassroomId);
        }

        // Urutkan berdasarkan Tingkat (grade_order) -> Nama Kelas -> Nama Siswa
        $builder->orderBy('g.order', 'ASC')
                ->orderBy('c.name', 'ASC')
                ->orderBy('s.full_name', 'ASC');

        $students = $builder->get()->getResultArray();

        $data = [
            'title'               => 'Monitoring Kelengkapan Data Siswa',
            'students'            => $students,
            'periods'             => $periods,
            'classrooms'          => $classrooms,
            'selectedPeriodId'    => $selectedPeriodId,
            'selectedClassroomId' => $selectedClassroomId,
        ];

        return view('student/completeness', $data);
    }
}