<?php

namespace App\Models;

use CodeIgniter\Model;

class DashboardModel extends Model
{
    protected $table = 'students';
    protected $primaryKey = 'id';

    /**
     * Get active academic period
     */
    public function getActivePeriod()
    {
        $db = \Config\Database::connect();
        return $db->table('academic_periods')
            ->select('academic_periods.*, academic_years.name as year_name')
            ->join('academic_years', 'academic_years.id = academic_periods.academic_year_id')
            ->where('academic_periods.is_active', 1)
            ->where('academic_periods.deleted_at', null)
            ->get()
            ->getRowArray();
    }

    /**
     * Get gender stats for active period
     */
    public function getGenderStats()
    {
        $db = \Config\Database::connect();
        $activePeriod = $this->getActivePeriod();
        
        if (!$activePeriod) {
            return ['L' => 0, 'P' => 0];
        }
        
        $result = $db->table('student_academic_histories')
            ->select('students.gender, COUNT(*) as total')
            ->join('students', 'students.id = student_academic_histories.student_id')
            ->where('student_academic_histories.academic_period_id', $activePeriod['id'])
            ->where('student_academic_histories.status', 'Aktif')
            ->where('students.deleted_at', null)
            ->groupBy('students.gender')
            ->get()
            ->getResultArray();
        
        $stats = ['L' => 0, 'P' => 0];
        foreach ($result as $row) {
            $stats[$row['gender']] = $row['total'];
        }
        
        return $stats;
    }

    /**
     * Get total active students
     */
    public function getTotalActiveStudents()
    {
        $db = \Config\Database::connect();
        $activePeriod = $this->getActivePeriod();
        
        if (!$activePeriod) {
            return 0;
        }
        
        return $db->table('student_academic_histories')
            ->where('academic_period_id', $activePeriod['id'])
            ->where('status', 'Aktif')
            ->countAllResults();
    }

    /**
     * Get class summary (rekap per kelas)
     */
    public function getClassSummary()
    {
        $db = \Config\Database::connect();
        $activePeriod = $this->getActivePeriod();
        
        if (!$activePeriod) {
            return [];
        }
        
        return $db->table('student_academic_histories')
            ->select('
                classrooms.id as classroom_id,
                classrooms.name as class_name,
                classrooms.capacity,
                grades.name as grade_name,
                COUNT(CASE WHEN students.gender = "L" THEN 1 END) as male_count,
                COUNT(CASE WHEN students.gender = "P" THEN 1 END) as female_count,
                COUNT(*) as total
            ')
            ->join('classrooms', 'classrooms.id = student_academic_histories.classroom_id')
            ->join('grades', 'grades.id = classrooms.grade_id')
            ->join('students', 'students.id = student_academic_histories.student_id')
            ->where('student_academic_histories.academic_period_id', $activePeriod['id'])
            ->where('student_academic_histories.status', 'Aktif')
            ->where('students.deleted_at', null)
            ->groupBy('classrooms.id')
            ->orderBy('grades.order', 'ASC')
            ->orderBy('classrooms.name', 'ASC')
            ->get()
            ->getResultArray();
    }
}