<?php

namespace App\Models;

use CodeIgniter\Model;

class StudentGraduationModel extends Model
{
    protected $table            = 'student_graduations';
    protected $primaryKey       = 'id';
    protected $useAutoIncrement = true;
    protected $returnType       = 'array';
    protected $useSoftDeletes   = true;
    protected $allowedFields    = [
        'student_id', 'academic_year_id', 'kelulusan_status',
        'decided_at', 'decided_by', 'skl_file_path',
        'skl_uploaded_at'
    ];

    protected $useTimestamps = true;
    protected $createdField  = 'created_at';
    protected $updatedField  = 'updated_at';

    /**
     * Get graduation status for a student in a specific academic year.
     *
     * @param int $studentId
     * @param int $academicYearId
     * @return array|null
     */
    public function getStatus(int $studentId, int $academicYearId)
    {
        return $this->where('student_id', $studentId)
                      ->where('academic_year_id', $academicYearId)
                      ->first();
    }

    /**
     * Get all graduation records for a student.
     *
     * @param int $studentId
     * @return array
     */
    public function getByStudent(int $studentId)
    {
        return $this->where('student_id', $studentId)->findAll();
    }

    /**
     * Get all student graduations for a specific academic year.
     *
     * @param int $academicYearId
     * @return array
     */
    public function getByAcademicYear(int $academicYearId)
    {
        return $this->where('academic_year_id', $academicYearId)->findAll();
    }

    /**
     * Get student graduation status and student basic info for a specific academic year.
     *
     * @param int $academicYearId
     * @return array
     */
    public function getWithStudentInfo(int $academicYearId)
    {
        return $this->select('student_graduations.*, students.nis, students.nisn, students.full_name, students.gender')
                      ->join('students', 'students.id = student_graduations.student_id')
                      ->where('student_graduations.academic_year_id', $academicYearId)
                      ->where('students.deleted_at', null)
                      ->orderBy('students.full_name', 'ASC')
                      ->findAll();
    }

    /**
     * Bulk insert or update graduation records.
     * Useful for processing upload results.
     *
     * @param array $data Array of records to insert/update.
     * @return bool
     */
    public function bulkSave(array $data): bool
    {
        if (empty($data)) {
            return true;
        }

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

        $placeholders = [];
        $binds        = [];

        foreach ($data as $row) {
            $placeholders[] = '(?, ?, ?, ?, ?, ?, ?, ?)'; // student_id, ac_yr_id, status, decided_at, decided_by, skl_path, created_at, updated_at
            $binds[] = $row['student_id'];
            $binds[] = $row['academic_year_id'];
            $binds[] = $row['kelulusan_status'];
            $binds[] = $row['decided_at'];
            $binds[] = $row['decided_by'];
            $binds[] = $row['skl_file_path'] ?? null;
            $binds[] = $now; // created_at
            $binds[] = $now; // updated_at
        }

        $sql = "INSERT INTO student_graduations
                    (student_id, academic_year_id, kelulusan_status, decided_at, decided_by, skl_file_path, created_at, updated_at)
               VALUES " . implode(', ', $placeholders) . "
               ON DUPLICATE KEY UPDATE
                    kelulusan_status = VALUES(kelulusan_status),
                    decided_at       = VALUES(decided_at),
                    decided_by       = VALUES(decided_by),
                    skl_file_path    = VALUES(skl_file_path),
                    skl_uploaded_at  = VALUES(skl_uploaded_at),
                    updated_at       = VALUES(updated_at)";

        return $db->query($sql, $binds);
    }

    /**
     * Remove all graduation records for a given student in a specific academic year.
     *
     * @param int $studentId
     * @param int $academicYearId
     * @return bool
     */
    public function removeRecords(int $studentId, int $academicYearId): bool
    {
        return $this->where('student_id', $studentId)
                      ->where('academic_year_id', $academicYearId)
                      ->delete();
    }
}
