<?php

namespace App\Services;

use App\Models\NilaiUjian;
use App\Models\Ujian;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Color;
use Illuminate\Http\Response;

class ExcelExportService
{
    private const COLOR_HEADER     = '1E3A5F';
    private const COLOR_SUBHEAD    = '2E6DA4';
    private const COLOR_ALT        = 'F7FAFD';
    private const COLOR_KATROL_BG  = 'E8F5E9';
    private const COLOR_KATROL_ALT = 'D4EDDA';
    private const COLOR_KATROL_FG  = '1A6B3C';
    private const COLOR_BORDER     = 'CBD5E0';

    /**
     * Export hasil katrol nilai ke file Excel.
     *
     * Alur:
     * 1. Build Spreadsheet di memory
     * 2. Save ke temp file di disk (menghindari ob_start corruption)
     * 3. Baca file sebagai binary string
     * 4. Hapus temp file
     * 5. Return sebagai Response binary
     *
     * Menggunakan Illuminate\Http\Response (bukan StreamedResponse)
     * agar tidak ada middleware Laravel yang menyentuh isi binary.
     */
    public function exportKatrolNilai(Ujian $ujian): Response
    {
        $ujian->loadMissing(['mataPelajaran', 'kelas', 'tahunAjaran']);

        $nilaiList = NilaiUjian::where('ujian_id', $ujian->id)
            ->with('siswa')
            ->orderBy('siswa_id')
            ->get();

        $spreadsheet = new Spreadsheet();
        $sheet       = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Hasil Katrol Nilai');

        $row = 1;
        $row = $this->writeTitle($sheet, $row);
        $row = $this->writeInfoUjian($sheet, $ujian, $row);
        $row++;                                         // baris kosong
        $row = $this->writeTableHeader($sheet, $row);
        $dataStartRow = $row;
        $row = $this->writeTableData($sheet, $nilaiList, $row);
        $this->writeSummaryRow($sheet, $nilaiList->count(), $dataStartRow, $row);
        $this->setColumnWidths($sheet);
        $sheet->freezePane('A' . $dataStartRow);

        // Tulis ke temp file — cara paling aman, tidak ada output buffering
        $tempFile = tempnam(sys_get_temp_dir(), 'xlsx_katrol_');
        (new Xlsx($spreadsheet))->save($tempFile);

        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);

        // Baca binary dari disk lalu hapus temp file
        $content = file_get_contents($tempFile);
        unlink($tempFile);

        $namaFile = 'Katrol_Nilai_'
            . preg_replace('/[^A-Za-z0-9_\-]/', '_', $ujian->nama)
            . '_' . now()->format('Ymd_His')
            . '.xlsx';

        return new Response($content, 200, [
            'Content-Type'              => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            'Content-Disposition'       => 'attachment; filename="' . $namaFile . '"',
            'Content-Length'            => strlen($content),
            'Content-Transfer-Encoding' => 'binary',
            'Cache-Control'             => 'no-cache, no-store, must-revalidate',
            'Pragma'                    => 'no-cache',
            'Expires'                   => '0',
        ]);
    }

    // ─── Helpers ──────────────────────────────────────────────────

    private function writeTitle($sheet, int $row): int
    {
        $sheet->mergeCells("A{$row}:H{$row}");
        $sheet->setCellValue("A{$row}", 'LAPORAN HASIL KATROL NILAI');
        $sheet->getStyle("A{$row}")->applyFromArray([
            'font'      => ['bold' => true, 'size' => 14, 'color' => ['rgb' => 'FFFFFF']],
            'fill'      => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => self::COLOR_HEADER]],
            'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER, 'vertical' => Alignment::VERTICAL_CENTER],
        ]);
        $sheet->getRowDimension($row)->setRowHeight(36);

        return $row + 1;
    }

    private function writeInfoUjian($sheet, Ujian $ujian, int $row): int
    {
        $infos = [
            ['Ujian',          $ujian->nama],
            ['Mata Pelajaran', $ujian->mataPelajaran->nama ?? '-'],
            ['Kelas',          $ujian->kelas->nama ?? '-'],
            ['Tahun Ajaran',   $ujian->tahunAjaran->nama ?? '-'],
            ['Tanggal Export', now()->format('d/m/Y H:i')],
        ];

        foreach ($infos as [$label, $value]) {
            $sheet->setCellValue("A{$row}", $label);
            $sheet->setCellValue("B{$row}", ': ' . $value);
            $sheet->getStyle("A{$row}")->getFont()->setBold(true);
            $sheet->getStyle("A{$row}")->getFont()
                  ->setColor(new Color('FF' . self::COLOR_HEADER));
            $row++;
        }

        return $row;
    }

    private function writeTableHeader($sheet, int $row): int
    {
        $headers = [
            'A' => 'No',
            'B' => 'NIS',
            'C' => 'Nama Siswa',
            'D' => 'Nilai PG+BS',
            'E' => 'Nilai Essay',
            'F' => 'Nilai Murni',
            'G' => 'Nilai Katrol',
            'H' => 'Keterangan',
        ];

        foreach ($headers as $col => $label) {
            $isKatrolCol = in_array($col, ['G', 'H']);
            $sheet->setCellValue($col . $row, $label);
            $sheet->getStyle($col . $row)->applyFromArray([
                'font'      => ['bold' => true, 'size' => 10, 'color' => ['rgb' => 'FFFFFF']],
                'fill'      => [
                    'fillType'   => Fill::FILL_SOLID,
                    'startColor' => ['rgb' => $isKatrolCol ? self::COLOR_KATROL_FG : self::COLOR_SUBHEAD],
                ],
                'alignment' => [
                    'horizontal' => Alignment::HORIZONTAL_CENTER,
                    'vertical'   => Alignment::VERTICAL_CENTER,
                    'wrapText'   => true,
                ],
                'borders'   => [
                    'allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => 'FFFFFF']],
                ],
            ]);
        }

        $sheet->getRowDimension($row)->setRowHeight(28);

        return $row + 1;
    }

    private function writeTableData($sheet, $nilaiList, int $startRow): int
    {
        $row = $startRow;

        foreach ($nilaiList as $idx => $nilai) {
            $isAlt = ($idx % 2 === 1);

            $keterangan = match (true) {
                $nilai->is_katrol       => 'Sudah Dikatrol',
                $nilai->is_essay_filled => 'Essay Terisi',
                $nilai->is_uploaded     => 'PG/BS Terupload',
                default                 => 'Belum Ada Nilai',
            };

            $rowData = [
                'A' => $idx + 1,
                'B' => $nilai->siswa->nis    ?? '-',
                'C' => $nilai->siswa->nama   ?? '-',
                'D' => $nilai->nilai_pg      ?? '-',
                'E' => $nilai->nilai_essay   ?? '-',
                'F' => $nilai->nilai_murni   ?? '-',
                'G' => $nilai->nilai_final   ?? '-',
                'H' => $keterangan,
            ];

            foreach ($rowData as $col => $val) {
                $isKatrolCol = in_array($col, ['G', 'H']);
                $bgColor = $isKatrolCol
                    ? ($isAlt ? self::COLOR_KATROL_ALT : self::COLOR_KATROL_BG)
                    : ($isAlt ? self::COLOR_ALT        : 'FFFFFF');

                $sheet->setCellValue($col . $row, $val);
                $sheet->getStyle($col . $row)->applyFromArray([
                    'fill'      => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => $bgColor]],
                    'alignment' => [
                        'horizontal' => in_array($col, ['A', 'D', 'E', 'F', 'G'])
                            ? Alignment::HORIZONTAL_CENTER
                            : Alignment::HORIZONTAL_LEFT,
                        'vertical'   => Alignment::VERTICAL_CENTER,
                    ],
                    'borders'   => [
                        'allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => self::COLOR_BORDER]],
                    ],
                ]);

                if (in_array($col, ['D', 'E', 'F', 'G']) && is_numeric($val)) {
                    $sheet->getStyle($col . $row)->getNumberFormat()->setFormatCode('0.00');
                }

                if ($col === 'G' && is_numeric($val)) {
                    $sheet->getStyle($col . $row)->getFont()->setBold(true);
                    $sheet->getStyle($col . $row)->getFont()
                          ->setColor(new Color('FF' . self::COLOR_KATROL_FG));
                }
            }

            $sheet->getRowDimension($row)->setRowHeight(22);
            $row++;
        }

        return $row;
    }

    private function writeSummaryRow($sheet, int $count, int $dataStartRow, int $summaryRow): void
    {
        $dataEnd = $summaryRow - 1;

        $sheet->setCellValue("A{$summaryRow}", 'Total Siswa');
        $sheet->setCellValue("B{$summaryRow}", $count);
        $sheet->setCellValue("C{$summaryRow}", 'Rata-rata Nilai Murni');
        $sheet->setCellValue("D{$summaryRow}", "=IFERROR(AVERAGE(F{$dataStartRow}:F{$dataEnd}),\"-\")");
        $sheet->setCellValue("E{$summaryRow}", 'Rata-rata Nilai Katrol');
        $sheet->setCellValue("F{$summaryRow}", "=IFERROR(AVERAGE(G{$dataStartRow}:G{$dataEnd}),\"-\")");

        $sheet->getStyle("A{$summaryRow}:H{$summaryRow}")->applyFromArray([
            'font'    => ['bold' => true],
            'fill'    => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => 'EEF2F7']],
            'borders' => [
                'outline' => ['borderStyle' => Border::BORDER_MEDIUM, 'color' => ['rgb' => self::COLOR_BORDER]],
            ],
        ]);

        foreach (['D', 'F'] as $col) {
            $sheet->getStyle($col . $summaryRow)->getNumberFormat()->setFormatCode('0.00');
        }
    }

    private function setColumnWidths($sheet): void
    {
        $widths = ['A' => 6, 'B' => 16, 'C' => 34, 'D' => 14, 'E' => 14, 'F' => 14, 'G' => 14, 'H' => 18];
        foreach ($widths as $col => $width) {
            $sheet->getColumnDimension($col)->setWidth($width);
        }
    }
}
