<?php

namespace App\Services;

use App\Models\AcademicYearModel;
use App\Models\ClassroomModel;
use App\Models\GraduationSettingModel;
use App\Models\StudentGraduationModel;
use App\Models\StudentAcademicHistoryModel;
use App\Models\StudentModel;
use CodeIgniter\Files\File;
use CodeIgniter\I18n\Time;
use CodeIgniter\Session\Session;

class GraduationService
{
    protected $academicYearModel;
    protected $graduationSettingModel;
    protected $studentGraduationModel;
    protected $studentModel;
    protected $studentAcademicHistoryModel;
    protected $classroomModel;
    protected $session;

    public function __construct()
    {
        $this->academicYearModel       = new AcademicYearModel();
        $this->graduationSettingModel  = new GraduationSettingModel();
        $this->studentGraduationModel  = new StudentGraduationModel();
        $this->studentModel            = new StudentModel();
        $this->studentAcademicHistoryModel = new StudentAcademicHistoryModel();
        $this->classroomModel          = new ClassroomModel();
        $this->session                 = session();
    }

    public function getPublicGraduationStatus(?int $academicYearId = null): array
    {
        $activeYear = $academicYearId ?
            $this->academicYearModel->find($academicYearId) :
            $this->academicYearModel->getActive();

        if (!$activeYear) {
            return ['is_active' => false, 'is_open' => false, 'countdown_seconds' => -1, 'message' => 'Fitur kelulusan belum dikonfigurasi.'];
        }

        $settings = $this->graduationSettingModel->getSettingsByYear($activeYear['id']);
        if (!$settings || !$settings['is_active']) {
            return ['is_active' => false, 'is_open' => false, 'countdown_seconds' => -1, 'message' => 'Fitur belum aktif.'];
        }

        $announcementTime = Time::parse($settings['announcement_datetime']);
        $currentTime      = Time::now();
        $isOpen           = $currentTime->isAfter($announcementTime);
        $countdownSeconds = $isOpen ? 0 : $announcementTime->getTimestamp() - $currentTime->getTimestamp();

        return [
            'is_active' => true,
            'is_open' => $isOpen,
            'countdown_seconds' => $countdownSeconds,
            'message' => $settings['custom_message'] ?? '',
            'settings' => $settings,
            'academic_year' => $activeYear
        ];
    }

    public function getFinalYearStudents(int $academicYearId, int $finalGradeId): array
    {
        $finalClassrooms = $this->classroomModel
            ->where('academic_year_id', $academicYearId)
            ->where('grade_id', $finalGradeId)
            ->where('deleted_at', null)
            ->findAll();

        if (empty($finalClassrooms)) return [];

        $classroomIds = array_column($finalClassrooms, 'id');

        // Gunakan db_connect() langsung untuk menghindari konflik
        // antara ->from() dan $table yang sudah di-set di model
        $db = db_connect();
        $placeholders = implode(', ', array_fill(0, count($classroomIds), '?'));

        $sql = "SELECT
                    sah.student_id,
                    students.nis,
                    students.nisn,
                    students.full_name,
                    students.gender,
                    classrooms.name AS classroom_name
                FROM student_academic_histories sah
                INNER JOIN students   ON students.id   = sah.student_id
                INNER JOIN classrooms ON classrooms.id = sah.classroom_id
                WHERE sah.classroom_id IN ({$placeholders})
                  AND students.deleted_at IS NULL
                  AND students.status IN ('Aktif', 'Alumni')
                ORDER BY classrooms.name ASC, students.full_name ASC";

        $students = $db->query($sql, $classroomIds)->getResultArray();

        if (empty($students)) return [];

        $studentIds = array_column($students, 'student_id');
        $existingGraduations = $this->studentGraduationModel
            ->whereIn('student_id', $studentIds)
            ->where('academic_year_id', $academicYearId)
            ->findAll();

        $graduationMap = [];
        foreach ($existingGraduations as $grad) {
            $graduationMap[$grad['student_id']] = $grad;
        }

        foreach ($students as &$student) {
            $student['graduation_status'] = $graduationMap[$student['student_id']]['kelulusan_status'] ?? 'BELUM_DITENTUKAN';
        }

        return $students;
    }

    public function checkStudentGraduation(string $nisn, int $academicYearId): ?array
    {
        $student = $this->studentModel->where('nisn', $nisn)->first();
        if (!$student) return null;

        $settings   = $this->graduationSettingModel->getSettingsByYear($academicYearId);
        $allowDownload = $settings['allow_skl_download'] ?? 0;

        $graduation = $this->studentGraduationModel->getStatus($student['id'], $academicYearId);
        if (!$graduation) {
            return [
                'student_id'       => $student['id'],
                'full_name'        => $student['full_name'],
                'birth_place'      => $student['birth_place'],
                'birth_date'       => $student['birth_date'],
                'kelulusan_status' => 'BELUM_DITENTUKAN',
                'skl_file_path'    => null,
                'allow_skl_download' => $allowDownload,
            ];
        }

        return array_merge($student, $graduation, ['allow_skl_download' => $allowDownload]);
    }

    public function processGraduationDecision(int $studentId, string $status, string $decidedAt, int $academicYearId, int $decidedByUserId): bool
    {
        $student = $this->studentModel->find($studentId);
        if (!$student) return false;

        $existingGraduation = $this->studentGraduationModel->getStatus($studentId, $academicYearId);
        $data = [
            'student_id' => $studentId,
            'academic_year_id' => $academicYearId,
            'kelulusan_status' => $status,
            'decided_at' => $decidedAt,
            'decided_by' => $decidedByUserId,
        ];

        if ($existingGraduation) {
            $this->studentGraduationModel->update($existingGraduation['id'], $data);
        } else {
            $this->studentGraduationModel->insert($data);
        }

        if ($status === 'LULUS') {
            $this->studentModel->update($studentId, ['status' => 'Alumni', 'exit_date' => $decidedAt, 'graduated_academic_year_id' => $academicYearId]);
        } elseif ($status === 'TIDAK_LULUS') {
            if ($student['status'] === 'Alumni' && $student['graduated_academic_year_id'] == $academicYearId) {
                $this->studentModel->update($studentId, ['status' => 'Aktif', 'exit_date' => null, 'graduated_academic_year_id' => null]);
            }
        }
        return true;
    }

    /**
     * Proses kelulusan massal dengan batch query — jauh lebih cepat dari loop individual.
     * N×3 queries → 3 queries total.
     */
    public function processBulkGraduationDecision(array $studentIds, string $status, string $decidedAt, int $academicYearId, int $decidedByUserId): int
    {
        if (empty($studentIds)) return 0;

        $db  = db_connect();
        $now = date('Y-m-d H:i:s');

        // 1. Cari record yang sudah ada sekaligus (1 query)
        $existingRecords = $this->studentGraduationModel
            ->whereIn('student_id', $studentIds)
            ->where('academic_year_id', $academicYearId)
            ->findAll();

        $existingMap = []; // student_id => graduation_id
        foreach ($existingRecords as $rec) {
            $existingMap[(int)$rec['student_id']] = (int)$rec['id'];
        }

        $toInsert    = [];
        $toUpdateIds = [];

        foreach ($studentIds as $sid) {
            $sid = (int)$sid;
            if (isset($existingMap[$sid])) {
                $toUpdateIds[] = $existingMap[$sid];
            } else {
                $toInsert[] = [
                    'student_id'       => $sid,
                    'academic_year_id' => $academicYearId,
                    'kelulusan_status' => $status,
                    'decided_at'       => $decidedAt,
                    'decided_by'       => $decidedByUserId,
                    'created_at'       => $now,
                    'updated_at'       => $now,
                ];
            }
        }

        // 2. Batch INSERT record baru (1 query)
        if (!empty($toInsert)) {
            $db->table('student_graduations')->insertBatch($toInsert);
        }

        // 3. Batch UPDATE record yang sudah ada (1 query)
        if (!empty($toUpdateIds)) {
            $db->table('student_graduations')
                ->whereIn('id', $toUpdateIds)
                ->update([
                    'kelulusan_status' => $status,
                    'decided_at'       => $decidedAt,
                    'decided_by'       => $decidedByUserId,
                    'updated_at'       => $now,
                ]);
        }

        // 4. Update tabel students (1 query)
        if ($status === 'LULUS') {
            $db->table('students')
                ->whereIn('id', $studentIds)
                ->update([
                    'status'                     => 'Alumni',
                    'exit_date'                  => $decidedAt,
                    'graduated_academic_year_id' => $academicYearId,
                    'updated_at'                 => $now,
                ]);
        } elseif ($status === 'TIDAK_LULUS') {
            // Hanya reset siswa yang status Alumni dari tahun ajaran ini
            $db->table('students')
                ->whereIn('id', $studentIds)
                ->where('status', 'Alumni')
                ->where('graduated_academic_year_id', $academicYearId)
                ->update([
                    'status'                     => 'Aktif',
                    'exit_date'                  => null,
                    'graduated_academic_year_id' => null,
                    'updated_at'                 => $now,
                ]);
        }

        return count($studentIds);
    }

    public function uploadSklZip($zipFile, int $academicYearId, int $userId): array
    {
        $report = [
            'success_count' => 0,
            'failed_count'  => 0,
            'failed_items'  => [], // ['nisn' => '...', 'reason' => '...']
        ];

        // Direktori tujuan di writable/uploads/skl/{academicYearId}/
        $targetDir = WRITEPATH . 'uploads/skl/' . $academicYearId . DIRECTORY_SEPARATOR;
        if (!is_dir($targetDir)) {
            mkdir($targetDir, 0755, true);
        }

        // Buka ZIP dari path temp upload
        $zip = new \ZipArchive();
        $openResult = $zip->open($zipFile->getTempName());
        if ($openResult !== true) {
            $report['failed_count']++;
            $report['failed_items'][] = ['nisn' => '-', 'reason' => 'File ZIP tidak valid atau rusak. Kode: ' . $openResult];
            return $report;
        }

        $now = date('Y-m-d H:i:s');

        for ($i = 0; $i < $zip->numFiles; $i++) {
            $stat     = $zip->statIndex($i);
            $filename = basename($stat['name']); // ambil nama file saja (abaikan subfolder dalam ZIP)
            $ext      = strtolower(pathinfo($filename, PATHINFO_EXTENSION));

            // Hanya proses file PDF
            if ($ext !== 'pdf') continue;

            // NISN = nama file tanpa ekstensi
            $nisn = trim(pathinfo($filename, PATHINFO_FILENAME));

            if (empty($nisn)) continue;

            // Cari siswa berdasarkan NISN
            $student = $this->studentModel->where('nisn', $nisn)->where('deleted_at', null)->first();
            if (!$student) {
                $report['failed_count']++;
                $report['failed_items'][] = [
                    'nisn'   => $nisn,
                    'reason' => 'NISN tidak ditemukan di database.',
                ];
                continue;
            }

            // Simpan file ke disk
            $destPath    = $targetDir . $nisn . '.pdf';
            $fileContent = $zip->getFromIndex($i);

            if ($fileContent === false || file_put_contents($destPath, $fileContent) === false) {
                $report['failed_count']++;
                $report['failed_items'][] = [
                    'nisn'   => $nisn,
                    'reason' => 'Gagal menyimpan file ke server.',
                ];
                continue;
            }

            // Path relatif yang disimpan di DB (relatif terhadap WRITEPATH)
            $relativePath = 'uploads/skl/' . $academicYearId . '/' . $nisn . '.pdf';

            // Upsert student_graduations
            $existing = $this->studentGraduationModel->getStatus($student['id'], $academicYearId);
            if ($existing) {
                $this->studentGraduationModel->update($existing['id'], [
                    'skl_file_path'   => $relativePath,
                    'skl_uploaded_at' => $now,
                ]);
            } else {
                $this->studentGraduationModel->insert([
                    'student_id'       => $student['id'],
                    'academic_year_id' => $academicYearId,
                    'kelulusan_status' => 'BELUM_DITENTUKAN',
                    'skl_file_path'    => $relativePath,
                    'skl_uploaded_at'  => $now,
                    'decided_by'       => $userId,
                ]);
            }

            $report['success_count']++;
        }

        $zip->close();

        return $report;
    }

    /**
     * Mendapatkan daftar siswa lengkap dengan status upload SKL untuk tabel detail
     */
    public function getSklStudentList(int $academicYearId, int $finalGradeId): array
    {
        $students = $this->getFinalYearStudents($academicYearId, $finalGradeId);
        if (empty($students)) return [];

        $studentIds = array_column($students, 'student_id');

        // Ambil data SKL dari student_graduations dalam satu query
        $db = db_connect();
        $placeholders = implode(', ', array_fill(0, count($studentIds), '?'));
        $sql = "SELECT student_id, kelulusan_status, skl_file_path, skl_uploaded_at
                FROM student_graduations
                WHERE academic_year_id = ?
                  AND student_id IN ({$placeholders})
                  AND deleted_at IS NULL";
        $params = array_merge([$academicYearId], $studentIds);
        $sklRecords = $db->query($sql, $params)->getResultArray();

        $sklMap = [];
        foreach ($sklRecords as $rec) {
            $sklMap[$rec['student_id']] = $rec;
        }

        // Gabungkan data SKL ke tiap siswa
        foreach ($students as &$student) {
            $skl = $sklMap[$student['student_id']] ?? null;
            $student['skl_file_path']   = $skl['skl_file_path'] ?? null;
            $student['skl_uploaded_at'] = $skl['skl_uploaded_at'] ?? null;
            $student['graduation_status'] = $student['graduation_status'] ?? 'BELUM_DITENTUKAN';
        }

        return $students;
    }

    
    /**
     * Mendapatkan jumlah file SKL untuk suatu tahun
     */
    public function getSklCountByYear(int $academicYearId): int
    {
        return (int) db_connect()->table('student_graduations')
            ->where('academic_year_id', $academicYearId)
            ->where('skl_file_path IS NOT NULL', null, false)
            ->where('deleted_at IS NULL', null, false)
            ->countAllResults();
    }

    public function deleteSklMassal(int $academicYearId): bool
    {
        // Ambil semua record yang punya SKL file untuk tahun ajaran ini
        $records = $this->studentGraduationModel
            ->where('academic_year_id', $academicYearId)
            ->where('skl_file_path IS NOT NULL', null, false)
            ->findAll();

        // Hapus file dari disk satu per satu
        foreach ($records as $record) {
            $fullPath = WRITEPATH . $record['skl_file_path'];
            if (file_exists($fullPath)) {
                @unlink($fullPath);
            }
        }

        // Coba hapus folder jika sudah kosong
        $sklDir = WRITEPATH . 'uploads/skl/' . $academicYearId;
        if (is_dir($sklDir) && count(glob($sklDir . '/*')) === 0) {
            @rmdir($sklDir);
        }

        // Reset path di DB — status kelulusan TIDAK diubah (history tetap)
        db_connect()->table('student_graduations')
            ->where('academic_year_id', $academicYearId)
            ->update([
                'skl_file_path'   => null,
                'skl_uploaded_at' => null,
                'updated_at'      => date('Y-m-d H:i:s'),
            ]);

        return true;
    }

    public function generateDownloadToken(int $studentId, int $academicYearId): string
    {
        $token = bin2hex(random_bytes(16));
        $this->session->setTempdata("skl_download_token_{$token}", ['student_id' => $studentId, 'academic_year_id' => $academicYearId], 300);
        return $token;
    }

    public function validateDownloadToken(string $token): ?string
    {
        $tokenData = $this->session->getTempdata("skl_download_token_{$token}");
        if (!$tokenData) return null;
        $this->session->removeTempdata("skl_download_token_{$token}");

        $graduation = $this->studentGraduationModel->getStatus($tokenData['student_id'], $tokenData['academic_year_id']);
        return $graduation['skl_file_path'] ?? null;
    }

    public function generateMathCaptcha(): array
    {
        $num1 = rand(1, 9); $num2 = rand(1, 9);
        $question = "Berapa {$num1} + {$num2}?";
        $answer = $num1 + $num2;
        $captchaKey = 'math_captcha_' . uniqid();
        $this->session->setTempdata($captchaKey, $answer, 300);
        return ['question' => $question, 'key' => $captchaKey];
    }

    public function validateMathCaptcha(string $userAnswer, string $captchaKey): bool
    {
        $correctAnswer = $this->session->getTempdata($captchaKey);
        $this->session->removeTempdata($captchaKey);
        return (string)$correctAnswer === (string)$userAnswer;
    }

    public function getSklSummary(): array
    {
        $activeYear = $this->academicYearModel->getActive();
        if (!$activeYear) return [];

        $settings = $this->graduationSettingModel->getSettingsByYear($activeYear['id']);
        if (!$settings) return ['academic_year' => $activeYear, 'settings' => null];

        $db = db_connect();

        $totalLulus = (int) $db->table('student_graduations')
            ->where('academic_year_id', $activeYear['id'])
            ->where('kelulusan_status', 'LULUS')
            ->where('deleted_at IS NULL', null, false)
            ->countAllResults();

        $totalTidakLulus = (int) $db->table('student_graduations')
            ->where('academic_year_id', $activeYear['id'])
            ->where('kelulusan_status', 'TIDAK_LULUS')
            ->where('deleted_at IS NULL', null, false)
            ->countAllResults();

        $withSkl = (int) $db->table('student_graduations')
            ->where('academic_year_id', $activeYear['id'])
            ->where('skl_file_path IS NOT NULL', null, false)
            ->where('deleted_at IS NULL', null, false)
            ->countAllResults();

        return [
            'academic_year'    => $activeYear,
            'settings'         => $settings,
            'total_lulus'      => $totalLulus,
            'total_tidak_lulus'=> $totalTidakLulus,
            'with_skl'         => $withSkl,
            'without_skl'      => max(0, $totalLulus - $withSkl),
        ];
    }

    /**
     * Mendapatkan semua tahun ajaran
     */
    public function getSklYears(): array
    {
        $db = db_connect();
        $rows = $db->query("
            SELECT id, name FROM academic_years
            WHERE deleted_at IS NULL
            ORDER BY name DESC
        ")->getResultArray();

        return $rows ?: [];
    }
}
