<?php

namespace App\Controllers\Kamad;

use App\Controllers\BaseController;
use App\Models\AcademicPeriodModel;

class Dashboard extends BaseController
{
    private function checkAccess()
    {
        if (!session()->get('logged_in')) {
            return redirect()->to('/login');
        }
        $role = session()->get('user_role');
        if ($role !== 'kamad') {
            return redirect()->to('/dashboard')->with('error', 'Akses ditolak.');
        }
        return null;
    }

    public function index()
    {
        if ($redirect = $this->checkAccess()) return $redirect;

        $db = \Config\Database::connect();
        
        // 1. Ambil Periode Aktif
        $periodModel = new AcademicPeriodModel();
        $activePeriod = $periodModel->getActive();
        
        if (!$activePeriod) {
            return view('kamad/dashboard', [
                'title' => 'Dashboard Pengawasan',
                'activePeriod' => null,
            ]);
        }

        $periodId = $activePeriod['id'];
        $academicYearId = $activePeriod['academic_year_id'];
        $today = date('Y-m-d');

        // 2. Metrik Ringkasan (KBM Hari Ini)
        $totalClassrooms = $db->table('classrooms')
            ->where('academic_year_id', $academicYearId)
            ->where('deleted_at', null)
            ->countAllResults();

        $filledClassrooms = $db->table('teaching_journals')
            ->select('COUNT(DISTINCT classroom_id) as count')
            ->where('academic_period_id', $periodId)
            ->where('meeting_date', $today)
            ->where('deleted_at', null)
            ->get()->getRowArray();
        $filledClassroomsCount = $filledClassrooms ? (int)$filledClassrooms['count'] : 0;

        // 3. Ringkasan Kehadiran Siswa Hari Ini
        $totalActiveStudents = $db->table('student_academic_histories')
            ->where('academic_period_id', $periodId)
            ->where('status', 'Aktif')
            ->countAllResults();

        $attendanceCounts = $db->table('attendances')
            ->select('status, COUNT(*) as total')
            ->where('academic_period_id', $periodId)
            ->where('date', $today)
            ->groupBy('status')
            ->get()
            ->getResultArray();
        
        $sakit = 0;
        $izin = 0;
        $alpha = 0;
        foreach ($attendanceCounts as $c) {
            if ($c['status'] === 'Sakit') $sakit = (int)$c['total'];
            if ($c['status'] === 'Izin') $izin = (int)$c['total'];
            if ($c['status'] === 'Alpha') $alpha = (int)$c['total'];
        }
        $hadir = max(0, $totalActiveStudents - ($sakit + $izin + $alpha));

        // 4. Tren Pelanggaran Tata Tertib Mingguan (7 Hari Terakhir)
        $trendViolations = [];
        for ($i = 6; $i >= 0; $i--) {
            $d = date('Y-m-d', strtotime("-$i days"));
            $trendViolations[$d] = 0;
        }
        $dates = array_keys($trendViolations);
        $vData = $db->table('student_violations')
            ->select('date, COUNT(*) as count')
            ->where('academic_period_id', $periodId)
            ->whereIn('date', $dates)
            ->groupBy('date')
            ->get()
            ->getResultArray();
        foreach ($vData as $row) {
            $trendViolations[$row['date']] = (int)$row['count'];
        }

        // 5. Distribusi Kategori Pengaduan Siswa
        $complaintCats = [
            'pribadi' => 0,
            'akademik' => 0,
            'sarpras' => 0,
            'bullying' => 0,
            'karir' => 0,
            'lainnya' => 0
        ];
        $tickets = $db->table('counseling_tickets')
            ->select('category, COUNT(*) as total')
            ->where('academic_period_id', $periodId)
            ->groupBy('category')
            ->get()
            ->getResultArray();
        foreach ($tickets as $t) {
            if (isset($complaintCats[$t['category']])) {
                $complaintCats[$t['category']] = (int)$t['total'];
            }
        }

        // 6. Rata-rata Nilai Akademik Per Kelas
        $academicStats = $db->table('student_grades sg')
            ->select('c.name as class_name, AVG(sg.score) as avg_score')
            ->join('classrooms c', 'c.id = sg.classroom_id')
            ->where('sg.academic_period_id', $periodId)
            ->groupBy('c.id')
            ->orderBy('c.name', 'ASC')
            ->get()
            ->getResultArray();

        // 7. Rata-rata Nilai Tahfidz Per Kelas
        $tahfidzStats = $db->table('tahfidz_setoran ts')
            ->select('c.name as class_name, AVG(ts.nilai) as avg_score')
            ->join('student_academic_histories sah', 'sah.student_id = ts.student_id')
            ->join('classrooms c', 'c.id = sah.classroom_id')
            ->where('sah.academic_period_id', $periodId)
            ->groupBy('c.id')
            ->orderBy('c.name', 'ASC')
            ->get()
            ->getResultArray();

        // 8. Log 5 Penugasan Dinas Luar & Surat Tugas Terakhir
        $latestLetters = $db->table('letter_logs')
            ->select('letter_type, letter_number, student_name, officer_name, letter_date, purpose, task_description, created_at')
            ->orderBy('id', 'DESC')
            ->limit(5)
            ->get()
            ->getResultArray();

        // Metrik Ringkas untuk Card Header
        $totalViolations = $db->table('student_violations')
            ->where('academic_period_id', $periodId)
            ->countAllResults();

        $activeTicketsCount = $db->table('counseling_tickets')
            ->where('academic_period_id', $periodId)
            ->where('status', 'open')
            ->countAllResults();

        return view('kamad/dashboard', [
            'title'                 => 'Dashboard Pengawasan Kamad',
            'activePeriod'          => $activePeriod,
            
            // KBM rate
            'totalClassrooms'       => $totalClassrooms,
            'filledClassrooms'      => $filledClassroomsCount,
            
            // Attendance stats
            'totalActiveStudents'   => $totalActiveStudents,
            'attendance'            => [
                'hadir' => $hadir,
                'sakit' => $sakit,
                'izin'  => $izin,
                'alpha' => $alpha
            ],

            // Infographics trends
            'trendViolations'       => $trendViolations,
            'complaintCats'         => $complaintCats,
            'academicStats'         => $academicStats,
            'tahfidzStats'          => $tahfidzStats,
            'latestLetters'         => $latestLetters,

            // Header summary numbers
            'totalViolations'       => $totalViolations,
            'activeTicketsCount'    => $activeTicketsCount
        ]);
    }
}
