<?php

namespace App\Controllers\Admin;

use App\Controllers\BaseController;
use App\Models\StudentGradeModel;
use App\Models\LegerImportModel;
use App\Models\StudentModel;
use App\Models\ClassroomModel;
use App\Models\GradeModel;
use App\Models\AcademicPeriodModel;
use App\Models\AcademicYearModel;
use App\Models\SchoolSubjectModel;

class LegerImport extends BaseController
{
    protected $studentGradeModel;
    protected $legerImportModel;
    protected $studentModel;
    protected $classroomModel;
    protected $gradeModel;
    protected $periodModel;
    protected $yearModel;
    protected $schoolSubjectModel;

    public function __construct()
    {
        $this->studentGradeModel  = new StudentGradeModel();
        $this->legerImportModel   = new LegerImportModel();
        $this->studentModel       = new StudentModel();
        $this->classroomModel     = new ClassroomModel();
        $this->gradeModel         = new GradeModel();
        $this->periodModel        = new AcademicPeriodModel();
        $this->yearModel          = new AcademicYearModel();
        $this->schoolSubjectModel = new SchoolSubjectModel();
    }

    private function checkLogin()
    {
        if (!session()->get('logged_in')) return redirect()->to('/login');
        if (!in_array(session()->get('user_role'), ['superadmin','admin','operator'])) {
            return redirect()->to('/dashboard')->with('error', 'Akses ditolak!');
        }
        return null;
    }

    // ── INDEX — riwayat import ────────────────────────────────
    public function index()
    {
        if ($r = $this->checkLogin()) return $r;

        $data = [
            'title'   => 'Import Leger Nilai',
            'imports' => $this->legerImportModel->getAllWithRelations(),
        ];

        return view('leger_import/index', $data);
    }

    // ── UPLOAD — parse + preview ──────────────────────────────
    public function upload()
    {
        if ($r = $this->checkLogin()) return $r;

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

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

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

        // Cari school_subject untuk setiap kode mapel dari leger
        $subjectLookup = $this->buildSubjectLookup($parsed['subject_codes']);

        // Auto-match kelas berdasarkan nama dari file
        $classrooms        = $this->classroomModel->getAllWithRelations();
        $suggestedClassroom = null;
        $rawClassName       = $parsed['meta']['class_name'] ?? '';
        foreach ($classrooms as $c) {
            if (strtolower(trim($c['name'])) === strtolower(trim($rawClassName))) {
                $suggestedClassroom = $c;
                break;
            }
        }

        // Auto-match periode berdasarkan semester + tahun ajaran dari file
        $allPeriods      = $this->periodModel->getAllWithYear();
        $suggestedPeriod = null;
        $rawSemester     = $parsed['meta']['semester'] ?? '';
        $rawYear         = $parsed['meta']['year'] ?? '';
        foreach ($allPeriods as $p) {
            if (
                strtolower($p['semester'])  === strtolower($rawSemester) &&
                strtolower($p['year_name']) === strtolower($rawYear)
            ) {
                $suggestedPeriod = $p;
                break;
            }
        }

        // Ambil semua tahun ajaran untuk dropdown filter
        $allYears = $this->yearModel->orderBy('name','DESC')->findAll();

        $data = [
            'title'              => 'Preview Import Leger',
            'parsed'             => $parsed,
            'subjectLookup'      => $subjectLookup,   // ['QH'=>['id'=>5,'name'=>'...'], ...]
            'classrooms'         => $classrooms,
            'allPeriods'         => $allPeriods,
            'allYears'           => $allYears,
            'suggestedClassroom' => $suggestedClassroom,
            'suggestedPeriod'    => $suggestedPeriod,
            'fileName'           => $file->getClientName(),
        ];

        return view('leger_import/preview', $data);
    }

    // ── CONFIRM — simpan ke DB ────────────────────────────────
    public function confirm()
    {
        if ($r = $this->checkLogin()) return $r;

        // Naikkan time limit untuk proses import
        set_time_limit(120);

        $classroomId = $this->request->getPost('classroom_id');
        $periodId    = $this->request->getPost('period_id');
        $parsedJson  = $this->request->getPost('parsed_data');
        $fileName    = $this->request->getPost('file_name');
        $overwrite   = $this->request->getPost('overwrite') === '1';

        if (!$classroomId || !$periodId || !$parsedJson) {
            return redirect()->to('/leger-import')->with('error', 'Data tidak lengkap!');
        }

        $parsed    = json_decode($parsedJson, true);
        $classroom = $this->classroomModel->find($classroomId);
        // Gunakan query dengan JOIN agar 'year_name' tersedia
        $period    = $this->periodModel->select('academic_periods.*, academic_years.name as year_name')
                         ->join('academic_years', 'academic_years.id = academic_periods.academic_year_id')
                         ->find($periodId);

        if (!$classroom || !$period) {
            return redirect()->to('/leger-import')->with('error', 'Kelas atau periode tidak valid!');
        }

        if (!is_period_active($periodId)) {
            return redirect()->to('/leger-import')->with('error', 'Periode akademik untuk data ini telah ditutup! Data tidak dapat di-import.');
        }

        // Cek apakah sudah ada data
        $existingCount = $this->studentGradeModel
            ->where('classroom_id', $classroomId)
            ->where('academic_period_id', $periodId)
            ->countAllResults();

        if ($existingCount > 0 && !$overwrite) {
            return redirect()->to('/leger-import')
                ->with('error', 'Data nilai untuk kelas ini sudah ada! Upload ulang dan centang "Timpa data lama".');
        }

        if ($existingCount > 0 && $overwrite) {
            $this->studentGradeModel->deleteByClassroomAndPeriod($classroomId, $periodId);
        }

        // Bangun lookup school_subject berdasarkan kode
        $subjectLookup = $this->buildSubjectLookup($parsed['subject_codes']);

        // Ambil SEMUA siswa sekaligus (1 query), bukan per baris
        $allNis  = array_filter(array_column($parsed['rows'], 'nis'));
        $allNisn = array_filter(array_column($parsed['rows'], 'nisn'));

        $studentsByNis  = [];
        $studentsByNisn = [];

        if (!empty($allNis)) {
            $found = $this->studentModel->whereIn('nis', array_values($allNis))->findAll();
            foreach ($found as $s) { $studentsByNis[$s['nis']] = $s; }
        }
        if (!empty($allNisn)) {
            $found = $this->studentModel->whereIn('nisn', array_values($allNisn))->findAll();
            foreach ($found as $s) { $studentsByNisn[$s['nisn']] = $s; }
        }

        $batchCode       = 'LEGER-' . strtoupper($classroom['name']) . '-' . date('YmdHis');
        $successStudents = 0;
        $skippedStudents = [];
        $bulkRows        = []; // semua nilai dikumpulkan dulu

        foreach ($parsed['rows'] as $row) {
            // Cocokkan siswa: NIS dulu, fallback NISN
            $student = $studentsByNis[(string)($row['nis'] ?? '')] ?? null;
            if (!$student) {
                $student = $studentsByNisn[(string)($row['nisn'] ?? '')] ?? null;
            }

            if (!$student) {
                $skippedStudents[] = ($row['nama'] ?? '-') . ' (NIS: ' . ($row['nis'] ?? '-') . ')';
                continue;
            }

            foreach ($parsed['subject_codes'] as $code) {
                $score   = $row['scores'][$code] ?? null;
                $subject = $subjectLookup[$code] ?? null;
                if ($score === null || !$subject) continue;

                $bulkRows[] = [
                    'student_id'         => $student['id'],
                    'classroom_id'       => $classroomId,
                    'academic_period_id' => $periodId,
                    'school_subject_id'  => $subject['id'],
                    'score'              => $score,
                    'import_batch'       => $batchCode,
                ];
            }

            $successStudents++;
        }

        // Simpan semua nilai dalam 1 query bulk INSERT
        if (!empty($bulkRows)) {
            $this->studentGradeModel->bulkSave($bulkRows);
        }

        // Log import
        $this->legerImportModel->insert([
            'batch_code'         => $batchCode,
            'classroom_id'       => $classroomId,
            'academic_period_id' => $periodId,
            'file_name'          => $fileName,
            'class_name_raw'     => $parsed['meta']['class_name'] ?? '',
            'semester_raw'       => $parsed['meta']['semester']   ?? '',
            'year_raw'           => $parsed['meta']['year']        ?? '',
            'total_students'     => $successStudents,
            'total_subjects'     => count($parsed['subject_codes']),
            'imported_by'        => session()->get('user_id'),
        ]);

        $msg = "Berhasil import: {$successStudents} siswa, " . count($parsed['subject_codes']) . " mapel.";
        if (!empty($skippedStudents)) {
            session()->setFlashdata('import_warnings', $skippedStudents);
            $msg .= ' ' . count($skippedStudents) . ' siswa tidak ditemukan.';
        }

        log_activity('import', 'leger_nilai', null, "Melakukan import leger nilai kelas " . ($classroom ? ($classroom['name'] ?? '') : '') . " periode " . ($period ? (($period['semester'] ?? '') . ' ' . ($period['year_name'] ?? '')) : '') . " sebanyak {$successStudents} records");

        return redirect()->to('/leger-import')->with('success', $msg);
    }

    // ── VIEW — tampilkan leger per kelas + periode ────────────
    public function view()
    {
        if ($r = $this->checkLogin()) return $r;

        $activePeriod = $this->periodModel->getActive();

        // Filter: Tahun Ajaran → Semester → Kelas
        $yearId      = $this->request->getGet('year_id');
        $periodId    = $this->request->getGet('period_id');
        $classroomId = $this->request->getGet('classroom_id');

        // Daftar untuk dropdown
        $allYears     = $this->yearModel->orderBy('name','DESC')->findAll();
        $allPeriods   = $yearId
            ? $this->periodModel->getByYear($yearId)
            : [];
        $allClassrooms = $periodId
            ? $this->getClassroomsWithLeger($periodId)
            : [];

        $legerData = null;
        $classroom = null;
        $period    = null;

        if ($classroomId && $periodId) {
            $classroom = $this->classroomModel->find($classroomId);
            $period    = $this->periodModel->find($periodId);
            $legerData = $this->studentGradeModel->getLegerByClassroom($classroomId, $periodId);
        }

        $data = [
            'title'              => 'Data Leger Nilai',
            'allYears'           => $allYears,
            'allPeriods'         => $allPeriods,
            'allClassrooms'      => $allClassrooms,
            'activePeriod'       => $activePeriod,
            'selectedYearId'     => $yearId,
            'selectedPeriodId'   => $periodId,
            'selectedClassroomId'=> $classroomId,
            'classroom'          => $classroom,
            'period'             => $period,
            'legerData'          => $legerData,
        ];

        return view('leger_import/view', $data);
    }

    // AJAX — ambil periode berdasarkan tahun ajaran
    public function getPeriods()
    {
        if ($r = $this->checkLogin()) return $r;

        $yearId  = $this->request->getGet('year_id');
        $periods = $yearId ? $this->periodModel->getByYear($yearId) : [];

        return $this->response->setJSON($periods);
    }

    // AJAX — ambil kelas yang memiliki data leger pada periode tertentu
    public function getClassrooms()
    {
        if ($r = $this->checkLogin()) return $r;

        $periodId  = $this->request->getGet('period_id');
        $classrooms = $periodId ? $this->getClassroomsWithLeger($periodId) : [];

        return $this->response->setJSON($classrooms);
    }

    // ── PRINT ─────────────────────────────────────────────────
    public function print()
    {
        if ($r = $this->checkLogin()) return $r;

        $classroomId = $this->request->getGet('classroom_id');
        $periodId    = $this->request->getGet('period_id');

        if (!$classroomId || !$periodId) {
            return redirect()->to('/leger-import/view')->with('error', 'Pilih kelas dan periode!');
        }

        $classroom   = $this->classroomModel->find($classroomId);
        $grade       = $this->gradeModel->find($classroom['grade_id'] ?? 0);
        $period      = $this->periodModel->find($periodId);
        $legerData   = $this->studentGradeModel->getLegerByClassroom($classroomId, $periodId);

        $db = \Config\Database::connect();
        $academicYear = $period
            ? $db->table('academic_years')->where('id', $period['academic_year_id'])->get()->getRowArray()
            : null;

        $schoolModel = new \App\Models\SchoolSettingModel();
        $school      = $schoolModel->getSettings();

        // Ambil data Wali Kelas untuk TTD
        $waliKelas = $db->table('homeroom_assignments')
            ->select('teachers.name as teacher_name, teachers.nip as teacher_nip')
            ->join('teachers', 'teachers.id = homeroom_assignments.teacher_id')
            ->where('homeroom_assignments.classroom_id', $classroomId)
            ->where('homeroom_assignments.academic_period_id', $periodId)
            ->get()->getRowArray();

        $data = [
            'title'        => 'Leger Nilai',
            'school'       => $school,
            'classroom'    => $classroom,
            'grade'        => $grade,
            'period'       => $period,
            'academicYear' => $academicYear,
            'legerData'    => $legerData,
            'waliKelas'    => $waliKelas,
            'printDate'    => date('d F Y'),
        ];

        return view('leger_import/print', $data);
    }

    // ── DELETE — hapus data leger 1 batch ─────────────────────
    public function delete($importId)
    {
        if ($r = $this->checkLogin()) return $r;

        $import = $this->legerImportModel->find($importId);
        if (!$import) {
            return redirect()->to('/leger-import')->with('error', 'Data tidak ditemukan!');
        }

        if (!is_period_active($import['academic_period_id'])) {
            return redirect()->to('/leger-import')->with('error', 'Periode akademik untuk data ini telah ditutup! Data tidak dapat dihapus.');
        }

        log_activity('delete', 'leger_nilai', $importId, "Menghapus data leger nilai kelas ID {$import['classroom_id']} periode ID {$import['academic_period_id']}");

        $this->studentGradeModel->deleteByClassroomAndPeriod(
            $import['classroom_id'],
            $import['academic_period_id']
        );
        $this->legerImportModel->delete($importId);

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

    // ── PRIVATE: parse file Excel leger ──────────────────────
    private function parseLegerFile($filePath)
    {
        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile($filePath);
        $reader->setReadDataOnly(true);
        $spreadsheet = $reader->load($filePath);
        $ws          = $spreadsheet->getActiveSheet();

        // Meta dari header file
        // Row 2: Col B = nama kelas, Col E = semester
        // Row 3: Col B = madrasah,   Col E = tahun ajaran
        $meta = [
            'class_name' => trim((string)($ws->getCell('B2')->getValue() ?? '')),
            'semester'   => trim((string)($ws->getCell('E2')->getValue() ?? '')),
            'school'     => trim((string)($ws->getCell('B3')->getValue() ?? '')),
            'year'       => trim((string)($ws->getCell('E3')->getValue() ?? '')),
        ];

        // Parse kolom mapel dari row 6 (header) dan row 7 (sub-header)
        // Kolom 1-5: No, NIS, NISN, Nama, JK — skip
        // Kolom terakhir biasanya 'Jumlah' — skip
        $maxColIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString(
            $ws->getHighestColumn()
        );

        $subjectMap   = []; // [col_index => kode_mapel]
        $subjectCodes = []; // urut sesuai kolom

        // Helper closure: konversi angka kolom ke koordinat sel (misal col=6,row=6 → "F6")
        $cellAddr = function(int $col, int $row): string {
            return \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col) . $row;
        };

        for ($col = 6; $col <= $maxColIndex; $col++) {
            $r6 = $ws->getCell($cellAddr($col, 6))->getValue();
            $r7 = $ws->getCell($cellAddr($col, 7))->getValue();

            // Skip kolom 'Jumlah'
            if (!empty($r6) && strtolower(trim($r6)) === 'jumlah') continue;

            // Row 7 ada isi → pakai row 7 (sub-header: QH, AA, FIK, SKI, BAJ, TAQ)
            // Row 7 kosong  → pakai row 6 (mapel tunggal: MTK, IPA, dll)
            $rawCode = $r7 ?? $r6;
            if (empty($rawCode)) continue;

            $code = strtoupper(trim((string)$rawCode));

            // Skip nama group (PAI / MULOK) — kolom merged, sub-kode ada di row 7
            if (in_array($code, ['PAI', 'MULOK'])) continue;

            $subjectMap[$col] = $code;
            $subjectCodes[]   = $code;
        }

        // Parse baris data mulai row 8
        $rows = [];
        for ($row = 8; $row <= $ws->getHighestRow(); $row++) {
            $nis = $ws->getCell($cellAddr(2, $row))->getValue();
            if (empty($nis)) break; // baris kosong = selesai

            $scores = [];
            foreach ($subjectMap as $col => $code) {
                $val           = $ws->getCell($cellAddr($col, $row))->getValue();
                $scores[$code] = is_numeric($val) ? (float)$val : null;
            }

            $rows[] = [
                'no'    => $ws->getCell($cellAddr(1, $row))->getValue(),
                'nis'   => (string)$nis,
                'nisn'  => (string)($ws->getCell($cellAddr(3, $row))->getValue() ?? ''),
                'nama'  => $ws->getCell($cellAddr(4, $row))->getValue(),
                'jk'    => $ws->getCell($cellAddr(5, $row))->getValue(),
                'scores'=> $scores,
            ];
        }

        return [
            'meta'          => $meta,
            'subject_codes' => $subjectCodes,
            'rows'          => $rows,
        ];
    }

    /**
     * Bangun lookup: ['QH' => ['id'=>5,'name'=>'Al-Qur\'an Hadis'], ...]
     * dari tabel school_subjects berdasarkan kode
     */
    private function buildSubjectLookup(array $codes)
    {
        if (empty($codes)) return [];

        $subjects = $this->schoolSubjectModel
            ->whereIn('code', $codes)
            ->findAll();

        $lookup = [];
        foreach ($subjects as $s) {
            $lookup[strtoupper($s['code'])] = [
                'id'   => $s['id'],
                'name' => $s['name'],
                'code' => $s['code'],
            ];
        }

        return $lookup;
    }

    /**
     * Ambil kelas yang memiliki data leger pada periode tertentu
     */
    private function getClassroomsWithLeger($periodId)
    {
        $db = \Config\Database::connect();
        return $db->table('leger_imports')
            ->select('classrooms.id, classrooms.name, grades.name as grade_name')
            ->join('classrooms', 'classrooms.id = leger_imports.classroom_id')
            ->join('grades',     'grades.id = classrooms.grade_id')
            ->where('leger_imports.academic_period_id', $periodId)
            ->orderBy('grades.order', 'ASC')
            ->orderBy('classrooms.name', 'ASC')
            ->get()->getResultArray();
    }

    // Export Leger to Excel
    public function exportExcel()
    {
        if ($r = $this->checkLogin()) return $r;

        $classroomId = $this->request->getGet('classroom_id');
        $periodId    = $this->request->getGet('period_id');

        if (!$classroomId || !$periodId) {
            return redirect()->to('/leger-import/view')->with('error', 'Pilih kelas dan periode!');
        }

        $classroom   = $this->classroomModel->find($classroomId);
        $grade       = $this->gradeModel->find($classroom['grade_id'] ?? 0);
        $period      = $this->periodModel->find($periodId);
        $legerData   = $this->studentGradeModel->getLegerByClassroom($classroomId, $periodId);

        $db = \Config\Database::connect();
        $academicYear = $period
            ? $db->table('academic_years')->where('id', $period['academic_year_id'])->get()->getRowArray()
            : null;

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

        // Title
        $sheet->setCellValue('A1', 'LEGER NILAI SISWA');
        $sheet->setCellValue('A2', "Kelas: {$classroom['name']} | Semester: " . ($period['semester'] ?? '') . " | Tahun Ajaran: " . ($academicYear['name'] ?? ''));
        $sheet->getStyle('A1:A2')->getFont()->setBold(true);

        // Header Table
        // No, NIS, NISN, Nama, JK, [MAPEL CODES], Total, Rata-rata
        $headers = ['No', 'NIS', 'NISN', 'Nama Lengkap', 'JK'];
        $subjectCodes = $legerData['subject_codes'] ?? [];
        foreach ($subjectCodes as $sub) {
            $headers[] = $sub['code'];
        }
        $headers[] = 'Jumlah';
        $headers[] = 'Rata-rata';

        foreach ($headers as $i => $h) {
            $col = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($i + 1);
            $sheet->setCellValue($col . '4', $h);
            $sheet->getStyle($col . '4')->applyFromArray([
                'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
                'fill' => ['fillType' => 'solid', 'startColor' => ['rgb' => '4F46E5']],
                'alignment' => ['horizontal' => 'center', 'vertical' => 'center'],
                'borders' => ['allBorders' => ['borderStyle' => 'thin', 'color' => ['rgb' => 'CCCCCC']]]
            ]);
        }

        $rowNum = 5;
        $students = $legerData['students'] ?? [];
        foreach ($students as $idx => $s) {
            $sheet->setCellValue('A' . $rowNum, $idx + 1);
            $sheet->setCellValue('B' . $rowNum, $s['student']['nis']);
            $sheet->setCellValue('C' . $rowNum, $s['student']['nisn']);
            $sheet->setCellValue('D' . $rowNum, $s['student']['full_name']);
            $sheet->setCellValue('E' . $rowNum, $s['student']['gender']);

            $colIdx = 6;
            foreach ($subjectCodes as $sub) {
                $score = $s['grades'][$sub['id']] ?? '';
                $col = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($colIdx);
                $sheet->setCellValue($col . $rowNum, $score);
                $colIdx++;
            }

            // Total
            $colTotal = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($colIdx);
            $sheet->setCellValue($colTotal . $rowNum, $s['total']);
            $colIdx++;

            // Rata-rata
            $colAvg = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($colIdx);
            $avg = count($subjectCodes) > 0 ? round($s['total'] / count($subjectCodes), 2) : 0;
            $sheet->setCellValue($colAvg . $rowNum, $avg);

            // Style cells
            $maxColIdx = 5 + count($subjectCodes) + 2;
            for ($c = 1; $c <= $maxColIdx; $c++) {
                $col = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($c);
                $hAlign = ($c === 4) ? 'left' : 'center';
                $sheet->getStyle($col . $rowNum)->applyFromArray([
                    'alignment' => ['horizontal' => $hAlign, 'vertical' => 'center'],
                    'borders' => ['allBorders' => ['borderStyle' => 'thin', 'color' => ['rgb' => 'EAEAEA']]]
                ]);
            }

            $rowNum++;
        }

        // Auto width for columns
        $maxColIdx = 5 + count($subjectCodes) + 2;
        for ($c = 1; $c <= $maxColIdx; $c++) {
            $col = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($c);
            $sheet->getColumnDimension($col)->setAutoSize(true);
        }

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="leger_nilai_' . strtolower(str_replace(' ', '_', $classroom['name'])) . '_' . $periodId . '.xlsx"');
        header('Cache-Control: max-age=0');
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;
    }
}