<?php

namespace App\Http\Controllers;

use App\Models\DataSiswa;
use App\Models\RiwayatAntropometri;
use App\Models\LogRiwayat;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Font;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

class DataSiswaController extends Controller
{
    private function getDapurId(Request $request): string
    {
        $user = $request->user();
        if (in_array($user->role, ['Super Admin', 'Admin Data'])) {
            return $request->query('dapur_id', $user->dapur_id);
        }
        return $user->dapur_id;
    }

    /**
     * Tambah/Edit penerima manfaat (siswa, guru, staf)
     */
    public function store(Request $request)
    {
        $request->validate(['nama_penerima' => 'required|string']);
        $dapurId = $this->getDapurId($request);
        $id = $request->input('id');

        // Auto-resolve kode_jk dari jenis_kelamin
        $jk = $request->jenis_kelamin;
        $kodeJk = DataSiswa::resolveKodeJk($jk);

        // Resolve jabatan_kode
        $jabatanKode = (int)($request->jabatan_kode ?? DataSiswa::JABATAN_SISWA);
        $jabatan = $request->jabatan ?: 'Siswa';

        // Kelas dikosongkan untuk guru/staf
        $kelas = $jabatanKode === DataSiswa::JABATAN_SISWA
            ? $request->kelas
            : null;

        if ($id) {
            $penerima = DataSiswa::findOrFail($id);
            $penerima->update([
                'nisn' => $request->nisn,
                'nama_penerima' => $request->nama_penerima,
                'tanggal_lahir' => $request->tanggal_lahir,
                'jenis_kelamin' => $jk,
                'kode_jk' => $kodeJk,
                'nama_orangtua' => $request->nama_orangtua,
                'jabatan_kode' => $jabatanKode,
                'jabatan' => $jabatan,
                'kelas' => $kelas,
                'tinggi_badan' => $request->tinggi_badan ?? $penerima->tinggi_badan,
                'berat_badan' => $request->berat_badan ?? $penerima->berat_badan,
                'alergi' => $request->alergi,
                'no_hp' => $request->no_hp,
                'status' => $request->status ?: 'Aktif',
            ]);
        } else {
            $penerima = DataSiswa::create([
                'id_sekolah' => $request->id_sekolah,
                'dapur_id' => $dapurId,
                'nisn' => $request->nisn,
                'nama_penerima' => $request->nama_penerima,
                'tanggal_lahir' => $request->tanggal_lahir,
                'jenis_kelamin' => $jk,
                'kode_jk' => $kodeJk,
                'nama_orangtua' => $request->nama_orangtua,
                'jabatan_kode' => $jabatanKode,
                'jabatan' => $jabatan,
                'kelas' => $kelas,
                'tinggi_badan' => $request->tinggi_badan ?? 0,
                'berat_badan' => $request->berat_badan ?? 0,
                'alergi' => $request->alergi ?: '-',
                'no_hp' => $request->no_hp,
                'status' => $request->status ?: 'Aktif',
            ]);
        }

        // Auto Log Antropometri (hanya untuk siswa — jabatan_kode = 1)
        if ($jabatanKode === DataSiswa::JABATAN_SISWA) {
            $tb = $request->tinggi_badan ?? 0;
            $bb = $request->berat_badan ?? 0;
            if ($tb > 0 || $bb > 0) {
                $entityId = $id ?: $penerima->id_siswa;
                $last = RiwayatAntropometri::where('entity_id', $entityId)
                    ->where('entity_type', 'siswa')
                    ->orderByDesc('tanggal_ukur')->orderByDesc('id')->first();

                if (!$last || $last->tinggi_badan != $tb || $last->berat_badan != $bb) {
                    RiwayatAntropometri::create([
                        'dapur_id' => $dapurId,
                        'entity_type' => 'siswa',
                        'entity_id' => $entityId,
                        'tinggi_badan' => $tb,
                        'berat_badan' => $bb,
                        'tanggal_ukur' => now()->toDateString(),
                    ]);
                }
            }
        }

        return response()->json(['success' => true]);
    }

    public function destroy(string $id)
    {
        $penerima = DataSiswa::findOrFail($id);

        // Hapus riwayat antropometri jika siswa
        if ($penerima->jabatan_kode === DataSiswa::JABATAN_SISWA) {
            RiwayatAntropometri::where('entity_type', 'siswa')->where('entity_id', $id)->delete();
        }

        $penerima->delete();
        return response()->json(['success' => true]);
    }

    /**
     * Import massal penerima manfaat dari Excel (client-side parsed JSON)
     */
    public function import(Request $request)
    {
        $dapurId = $this->getDapurId($request);
        $idSekolah = $request->input('id_sekolah');
        $rows = $request->input('data', []);

        if (empty($rows)) {
            return response()->json(['success' => false, 'message' => 'Data kosong']);
        }

        $count = 0;
        DB::beginTransaction();
        try {
            foreach ($rows as $r) {
                if (empty($r['nama_penerima'])) continue;

                // Resolve jenis kelamin — bisa terima kode (392/64) atau huruf (L/P) atau teks
                $jk = $r['jenis_kelamin'] ?? $r['kode_jk'] ?? 'L';
                if (is_numeric($jk)) {
                    $jenisKelamin = DataSiswa::resolveJkFromKode($jk);
                    $kodeJk = (int)$jk;
                } else {
                    $jkUpper = strtoupper(trim($jk));
                    if (str_contains(strtolower($jk), 'laki')) $jkUpper = 'L';
                    elseif (str_contains(strtolower($jk), 'perempuan')) $jkUpper = 'P';
                    $jenisKelamin = $jkUpper === 'L' || $jkUpper === 'P' ? $jkUpper : 'L';
                    $kodeJk = DataSiswa::resolveKodeJk($jenisKelamin);
                }

                // Resolve jabatan_kode — bisa terima kode (1/2/3) atau teks
                $jabatanInput = $r['jabatan_kode'] ?? $r['jabatan'] ?? '1';
                if (is_numeric($jabatanInput) && in_array((int)$jabatanInput, [1, 2, 3])) {
                    $jabatanKode = (int)$jabatanInput;
                    $jabatan = $r['jabatan'] ?? ($jabatanKode === 1 ? 'Siswa' : ($jabatanKode === 2 ? 'Guru' : 'Staf'));
                } else {
                    $jabatan = $jabatanInput;
                    $jabatanKode = DataSiswa::resolveJabatanKode($jabatan);
                }

                // Resolve status — bisa terima true/false atau Aktif/Nonaktif
                $statusInput = $r['status'] ?? 'true';
                if ($statusInput === 'true' || $statusInput === true || $statusInput === '1') {
                    $status = 'Aktif';
                } elseif ($statusInput === 'false' || $statusInput === false || $statusInput === '0') {
                    $status = 'Nonaktif';
                } else {
                    $status = (strtolower($statusInput) === 'aktif' || strtolower($statusInput) === 'active') ? 'Aktif' : 'Nonaktif';
                }

                $kelas = $jabatanKode === DataSiswa::JABATAN_SISWA ? ($r['kelas'] ?? '') : null;

                $penerima = DataSiswa::create([
                    'id_sekolah' => $idSekolah,
                    'dapur_id' => $dapurId,
                    'nisn' => $r['nisn'] ?? '',
                    'nama_penerima' => $r['nama_penerima'],
                    'tanggal_lahir' => $r['tanggal_lahir'] ?? '',
                    'jenis_kelamin' => $jenisKelamin,
                    'kode_jk' => $kodeJk,
                    'nama_orangtua' => $r['nama_orangtua'] ?? '',
                    'jabatan_kode' => $jabatanKode,
                    'jabatan' => $jabatan,
                    'kelas' => $kelas,
                    'tinggi_badan' => $r['tinggi_badan'] ?? 0,
                    'berat_badan' => $r['berat_badan'] ?? 0,
                    'alergi' => $r['alergi'] ?? '-',
                    'no_hp' => $r['no_hp'] ?? '',
                    'status' => $status,
                ]);

                // Auto-log antropometri untuk siswa
                if ($jabatanKode === DataSiswa::JABATAN_SISWA) {
                    if (($r['tinggi_badan'] ?? 0) > 0 || ($r['berat_badan'] ?? 0) > 0) {
                        RiwayatAntropometri::create([
                            'dapur_id' => $dapurId,
                            'entity_type' => 'siswa',
                            'entity_id' => $penerima->id_siswa,
                            'tinggi_badan' => $r['tinggi_badan'] ?? 0,
                            'berat_badan' => $r['berat_badan'] ?? 0,
                            'tanggal_ukur' => now()->toDateString(),
                        ]);
                    }
                }

                $count++;
            }
            DB::commit();
            LogRiwayat::catat('Data Penerima Manfaat', 'Import', "Mengimpor {$count} data penerima manfaat");
            return response()->json(['success' => true, 'count' => $count]);
        } catch (\Exception $e) {
            DB::rollBack();
            return response()->json(['success' => false, 'message' => $e->getMessage()]);
        }
    }

    /**
     * Naik kelas massal — hanya untuk siswa (jabatan_kode = 1)
     */
    public function promote(Request $request, string $idSekolah)
    {
        $siswaList = DataSiswa::where('id_sekolah', $idSekolah)
            ->where('jabatan_kode', DataSiswa::JABATAN_SISWA)
            ->where('status', 'Aktif')->get();

        $updates = 0;
        DB::beginTransaction();
        /** @var \App\Models\DataSiswa $s */
        foreach ($siswaList as $s) {
            $currentKelas = trim($s->kelas);
            if (preg_match('/^(\d+)(.*)$/', $currentKelas, $match)) {
                $tingkat = (int)$match[1];
                if (in_array($tingkat, [6, 9, 12])) {
                    $s->update(['status' => 'Nonaktif']);
                    $updates++;
                } else {
                    $newKelas = ($tingkat + 1) . $match[2];
                    $s->update(['kelas' => $newKelas]);
                    $updates++;
                }
            }
        }
        DB::commit();

        if ($updates > 0) {
            LogRiwayat::catat('Data Penerima Manfaat', 'Naik Kelas', "Menaikkan kelas {$updates} siswa");
        }
        return response()->json(['success' => true, 'count' => $updates]);
    }

    /**
     * Hapus semua penerima manfaat di sekolah tertentu
     */
    public function truncate(string $idSekolah)
    {
        // Hapus riwayat antropometri siswa
        $siswaIds = DataSiswa::where('id_sekolah', $idSekolah)
            ->where('jabatan_kode', DataSiswa::JABATAN_SISWA)
            ->pluck('id_siswa');
        RiwayatAntropometri::where('entity_type', 'siswa')->whereIn('entity_id', $siswaIds)->delete();

        $count = DataSiswa::where('id_sekolah', $idSekolah)->delete();
        LogRiwayat::catat('Data Penerima Manfaat', 'Hapus Semua', "Menghapus {$count} penerima manfaat dari sekolah ID: {$idSekolah}");
        return response()->json(['success' => true, 'message' => "{$count} data penerima manfaat berhasil dihapus."]);
    }

    /**
     * Export penerima manfaat ke Excel (.xlsx) — styled dengan PhpSpreadsheet
     * type: 'bgn' = format kode, 'laporan' = format lengkap terbaca
     */
    public function export(Request $request, string $idSekolah)
    {
        $type = $request->query('type', 'laporan'); // 'bgn' or 'laporan'

        // Get school info
        $sekolah = \App\Models\DataSekolah::where('id_sekolah', $idSekolah)->first();
        $namaSekolah = $sekolah ? $sekolah->nama_sekolah : 'Sekolah';
        $alamatSekolah = $sekolah ? ($sekolah->alamat ?? '') : '';

        // Get data
        $data = DataSiswa::where('id_sekolah', $idSekolah)->orderBy('jabatan_kode')->orderBy('nama_penerima')->get();

        if ($data->isEmpty()) {
            return response()->json(['success' => false, 'message' => 'Tidak ada data untuk diexport'], 404);
        }

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // =============================================
        // COLORS
        // =============================================
        $colorHeaderBg = '1F4E79';     // Dark blue
        $colorHeaderFont = 'FFFFFF';   // White
        $colorSubHeaderBg = '2E75B6';  // Medium blue
        $colorRowEven = 'F2F7FB';      // Light blue tint
        $colorRowOdd = 'FFFFFF';       // White
        $colorBorder = 'BDD7EE';       // Light blue border
        $colorAccent = 'D6E4F0';       // Accent blue

        if ($type === 'bgn') {
            // =========================================
            // EXPORT BGN
            // =========================================
            $sheet->setTitle('Data BGN');

            // Title row
            $sheet->mergeCells('A1:G1');
            $sheet->setCellValue('A1', "DATA BGN — {$namaSekolah}");
            $sheet->getStyle('A1')->applyFromArray([
                'font' => ['bold' => true, 'size' => 14, 'color' => ['rgb' => $colorHeaderFont], 'name' => 'Calibri'],
                'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => $colorHeaderBg]],
                'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER, 'vertical' => Alignment::VERTICAL_CENTER],
            ]);
            $sheet->getRowDimension(1)->setRowHeight(36);

            // Subtitle
            $sheet->mergeCells('A2:G2');
            $sheet->setCellValue('A2', "Diekspor: " . date('d-m-Y H:i') . " | Total: {$data->count()} penerima");
            $sheet->getStyle('A2')->applyFromArray([
                'font' => ['italic' => true, 'size' => 9, 'color' => ['rgb' => $colorHeaderFont], 'name' => 'Calibri'],
                'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => $colorSubHeaderBg]],
                'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER, 'vertical' => Alignment::VERTICAL_CENTER],
            ]);
            $sheet->getRowDimension(2)->setRowHeight(22);

            // Headers
            $headers = ['NISN/NIK', 'Nama Penerima', 'Tanggal Lahir', 'Jenis Kelamin', 'Nama Orang Tua/Wali', 'Posisi', 'Status'];
            $colLetters = ['A', 'B', 'C', 'D', 'E', 'F', 'G'];
            $colWidths = [22, 30, 16, 16, 28, 10, 10];
            $lastCol = 'G';
            $headerRow = 3;

            foreach ($headers as $i => $h) {
                $sheet->setCellValue($colLetters[$i] . $headerRow, $h);
            }

            // Header style
            $sheet->getStyle("A{$headerRow}:{$lastCol}{$headerRow}")->applyFromArray([
                'font' => ['bold' => true, 'color' => ['rgb' => $colorHeaderFont], 'size' => 10, 'name' => 'Calibri'],
                'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => $colorHeaderBg]],
                'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER, 'vertical' => Alignment::VERTICAL_CENTER, 'wrapText' => true],
                'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => '1A3A5C']]],
            ]);
            $sheet->getRowDimension($headerRow)->setRowHeight(28);

            // Data rows
            $row = $headerRow + 1;
            foreach ($data as $idx => $item) {
                $jkKode = $item->jenis_kelamin === 'L' ? 392 : 64;
                $posisiKode = $item->jabatan_kode ?? 1;
                $statusVal = $item->status === 'Aktif' ? 'true' : 'false';
                $tglLahir = $item->tanggal_lahir ? date('d-m-Y', strtotime($item->tanggal_lahir)) : '';

                $sheet->setCellValue("A{$row}", $item->nisn ?? '');
                $sheet->setCellValue("B{$row}", $item->nama_penerima ?? '');
                $sheet->setCellValue("C{$row}", $tglLahir);
                $sheet->setCellValue("D{$row}", $jkKode);
                $sheet->setCellValue("E{$row}", $item->nama_orangtua ?? '');
                $sheet->setCellValue("F{$row}", $posisiKode);
                $sheet->setCellValue("G{$row}", $statusVal);

                // Alternating row color
                $bgColor = ($idx % 2 === 0) ? $colorRowEven : $colorRowOdd;
                $sheet->getStyle("A{$row}:{$lastCol}{$row}")->applyFromArray([
                    'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => $bgColor]],
                    'font' => ['size' => 10, 'name' => 'Calibri'],
                    'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_HAIR, 'color' => ['rgb' => $colorBorder]]],
                    'alignment' => ['vertical' => Alignment::VERTICAL_CENTER],
                ]);

                // Center align code columns
                $sheet->getStyle("D{$row}")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
                $sheet->getStyle("F{$row}")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
                $sheet->getStyle("G{$row}")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

                $row++;
            }

            // Column widths
            foreach ($colWidths as $i => $w) {
                $sheet->getColumnDimension($colLetters[$i])->setWidth($w);
            }

            // Auto filter
            $sheet->setAutoFilter("A{$headerRow}:{$lastCol}" . ($row - 1));
            $sheet->freezePane("A" . ($headerRow + 1));

            // Keterangan kode di bawah data
            $footerRow = $row + 1;
            $sheet->mergeCells("A{$footerRow}:{$lastCol}{$footerRow}");
            $sheet->setCellValue("A{$footerRow}", "KETERANGAN KODE:");
            $sheet->getStyle("A{$footerRow}")->applyFromArray([
                'font' => ['bold' => true, 'size' => 9, 'color' => ['rgb' => '1F4E79']],
                'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => $colorAccent]],
            ]);

            $notes = [
                "Jenis Kelamin: 392 = Laki-laki, 64 = Perempuan",
                "Posisi: 1 = Siswa, 2 = Guru/Kepala Sekolah, 3 = Staf Lainnya",
                "Status: true = Aktif, false = Nonaktif",
            ];
            foreach ($notes as $note) {
                $footerRow++;
                $sheet->mergeCells("A{$footerRow}:{$lastCol}{$footerRow}");
                $sheet->setCellValue("A{$footerRow}", "  • {$note}");
                $sheet->getStyle("A{$footerRow}")->applyFromArray([
                    'font' => ['size' => 9, 'color' => ['rgb' => '4472C4'], 'italic' => true],
                    'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => $colorAccent]],
                ]);
            }

            $filename = "Export_BGN_{$namaSekolah}_" . date('Y-m-d') . ".xlsx";

        } else {
            // =========================================
            // EXPORT LAPORAN
            // =========================================
            $sheet->setTitle('Laporan');

            // Title row
            $sheet->mergeCells('A1:L1');
            $sheet->setCellValue('A1', "LAPORAN DATA PENERIMA MANFAAT — {$namaSekolah}");
            $sheet->getStyle('A1')->applyFromArray([
                'font' => ['bold' => true, 'size' => 14, 'color' => ['rgb' => $colorHeaderFont], 'name' => 'Calibri'],
                'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => $colorHeaderBg]],
                'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER, 'vertical' => Alignment::VERTICAL_CENTER],
            ]);
            $sheet->getRowDimension(1)->setRowHeight(36);

            // Subtitle
            $sheet->mergeCells('A2:L2');
            $subtitle = $alamatSekolah
                ? "Alamat: {$alamatSekolah} | Diekspor: " . date('d-m-Y H:i') . " | Total: {$data->count()} penerima"
                : "Diekspor: " . date('d-m-Y H:i') . " | Total: {$data->count()} penerima";
            $sheet->setCellValue('A2', $subtitle);
            $sheet->getStyle('A2')->applyFromArray([
                'font' => ['italic' => true, 'size' => 9, 'color' => ['rgb' => $colorHeaderFont], 'name' => 'Calibri'],
                'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => $colorSubHeaderBg]],
                'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER, 'vertical' => Alignment::VERTICAL_CENTER],
            ]);
            $sheet->getRowDimension(2)->setRowHeight(22);

            // Headers
            $headers = ['No', 'NISN/NIK', 'Nama Penerima', 'Tanggal Lahir', 'Jenis Kelamin', 'Nama Orang Tua/Wali', 'Posisi', 'Status', 'TB (cm)', 'BB (kg)', 'Kelas', 'Alergi'];
            $colLetters = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L'];
            $colWidths = [6, 22, 30, 16, 16, 28, 20, 12, 10, 10, 10, 16];
            $lastCol = 'L';
            $headerRow = 3;

            foreach ($headers as $i => $h) {
                $sheet->setCellValue($colLetters[$i] . $headerRow, $h);
            }

            // Header style
            $sheet->getStyle("A{$headerRow}:{$lastCol}{$headerRow}")->applyFromArray([
                'font' => ['bold' => true, 'color' => ['rgb' => $colorHeaderFont], 'size' => 10, 'name' => 'Calibri'],
                'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => $colorHeaderBg]],
                'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER, 'vertical' => Alignment::VERTICAL_CENTER, 'wrapText' => true],
                'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => '1A3A5C']]],
            ]);
            $sheet->getRowDimension($headerRow)->setRowHeight(28);

            // Data rows
            $row = $headerRow + 1;
            foreach ($data as $idx => $item) {
                $jkLabel = $item->jenis_kelamin === 'L' ? 'Laki-Laki' : 'Perempuan';
                $posisiLabel = $item->jabatan ?: ($item->jabatan_kode === 1 ? 'Siswa' : ($item->jabatan_kode === 2 ? 'Guru' : 'Staf'));
                $statusLabel = $item->status === 'Aktif' ? 'Aktif' : 'Nonaktif';
                $tglLahir = $item->tanggal_lahir ? date('d-m-Y', strtotime($item->tanggal_lahir)) : '';

                $sheet->setCellValue("A{$row}", $idx + 1);
                $sheet->setCellValue("B{$row}", $item->nisn ?? '');
                $sheet->setCellValue("C{$row}", $item->nama_penerima ?? '');
                $sheet->setCellValue("D{$row}", $tglLahir);
                $sheet->setCellValue("E{$row}", $jkLabel);
                $sheet->setCellValue("F{$row}", $item->nama_orangtua ?? '');
                $sheet->setCellValue("G{$row}", $posisiLabel);
                $sheet->setCellValue("H{$row}", $statusLabel);
                $sheet->setCellValue("I{$row}", $item->tinggi_badan ?: '');
                $sheet->setCellValue("J{$row}", $item->berat_badan ?: '');
                $sheet->setCellValue("K{$row}", $item->kelas ?? '');
                $sheet->setCellValue("L{$row}", $item->alergi ?? '-');

                // Alternating row color
                $bgColor = ($idx % 2 === 0) ? $colorRowEven : $colorRowOdd;
                $sheet->getStyle("A{$row}:{$lastCol}{$row}")->applyFromArray([
                    'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => $bgColor]],
                    'font' => ['size' => 10, 'name' => 'Calibri'],
                    'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_HAIR, 'color' => ['rgb' => $colorBorder]]],
                    'alignment' => ['vertical' => Alignment::VERTICAL_CENTER],
                ]);

                // Center align specific columns
                foreach (['A', 'E', 'H', 'I', 'J', 'K'] as $c) {
                    $sheet->getStyle("{$c}{$row}")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
                }

                // Status color coding
                if ($statusLabel === 'Nonaktif') {
                    $sheet->getStyle("H{$row}")->applyFromArray([
                        'font' => ['color' => ['rgb' => 'DC2626'], 'bold' => true],
                    ]);
                } else {
                    $sheet->getStyle("H{$row}")->applyFromArray([
                        'font' => ['color' => ['rgb' => '059669'], 'bold' => true],
                    ]);
                }

                $row++;
            }

            // Column widths
            foreach ($colWidths as $i => $w) {
                $sheet->getColumnDimension($colLetters[$i])->setWidth($w);
            }

            // Auto filter & freeze
            $sheet->setAutoFilter("A{$headerRow}:{$lastCol}" . ($row - 1));
            $sheet->freezePane("A" . ($headerRow + 1));

            // Summary footer
            $footerRow = $row + 1;
            $siswaCount = $data->where('jabatan_kode', 1)->count();
            $guruCount = $data->where('jabatan_kode', 2)->count();
            $stafCount = $data->where('jabatan_kode', 3)->count();
            $aktifCount = $data->where('status', 'Aktif')->count();
            $lkCount = $data->where('jenis_kelamin', 'L')->count();
            $prCount = $data->where('jenis_kelamin', 'P')->count();

            $sheet->mergeCells("A{$footerRow}:{$lastCol}{$footerRow}");
            $sheet->setCellValue("A{$footerRow}", "RINGKASAN DATA:");
            $sheet->getStyle("A{$footerRow}")->applyFromArray([
                'font' => ['bold' => true, 'size' => 9, 'color' => ['rgb' => '1F4E79']],
                'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => $colorAccent]],
            ]);

            $summaries = [
                "Total: {$data->count()} penerima | Aktif: {$aktifCount} | Nonaktif: " . ($data->count() - $aktifCount),
                "Siswa: {$siswaCount} | Guru/Kepsek: {$guruCount} | Staf: {$stafCount}",
                "Laki-laki: {$lkCount} | Perempuan: {$prCount}",
            ];
            foreach ($summaries as $summary) {
                $footerRow++;
                $sheet->mergeCells("A{$footerRow}:{$lastCol}{$footerRow}");
                $sheet->setCellValue("A{$footerRow}", "  • {$summary}");
                $sheet->getStyle("A{$footerRow}")->applyFromArray([
                    'font' => ['size' => 9, 'color' => ['rgb' => '4472C4'], 'italic' => true],
                    'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => $colorAccent]],
                ]);
            }

            $filename = "Laporan_Penerima_{$namaSekolah}_" . date('Y-m-d') . ".xlsx";
        }

        // =============================================
        // OUTPUT
        // =============================================
        $safeFilename = preg_replace('/[^a-zA-Z0-9_\-\.]/', '_', $filename);
        $temp = tempnam(sys_get_temp_dir(), 'export');

        $writer = new Xlsx($spreadsheet);
        $writer->save($temp);

        return response()->download($temp, $safeFilename, [
            'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        ])->deleteFileAfterSend(true);
    }
}
