<?php

namespace App\Services;

use App\Models\NilaiUjian;
use App\Models\Ujian;
use Barryvdh\DomPDF\Facade\Pdf;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use Symfony\Component\HttpFoundation\StreamedResponse;

class LaporanService
{
    /**
     * Sanitize nama file — hapus karakter yang tidak valid
     */
    private function sanitizeFilename(string $name): string
    {
        // Ganti / \ : * ? " < > | dan spasi dengan underscore
        return preg_replace('/[\/\\\\\:\*\?\"\<\>\|\s]+/', '_', $name);
    }

    /**
     * Export laporan nilai ke Excel
     */
    public function exportNilaiExcel(Ujian $ujian): StreamedResponse
    {
        $nilaiList = NilaiUjian::where('ujian_id', $ujian->id)
            ->with('siswa.kelas')
            ->orderBy('siswa_id')
            ->get();

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

        $headers = ['No', 'NIS', 'Nama Siswa', 'Kelas', 'Nilai PG', 'Nilai BS', 'Nilai Essay', 'Nilai Murni', 'Nilai Final'];
        foreach ($headers as $col => $header) {
            $cell = chr(65 + $col) . '1';
            $sheet->setCellValue($cell, $header);
        }

        $sheet->getStyle('A1:I1')->applyFromArray([
            'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
            'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => '2563EB']],
            'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
        ]);

        foreach ($nilaiList as $i => $nilai) {
            $row = $i + 2;
            $sheet->setCellValue("A{$row}", $i + 1);
            $sheet->setCellValue("B{$row}", $nilai->siswa->nis ?? '-');
            $sheet->setCellValue("C{$row}", $nilai->siswa->nama);
            $sheet->setCellValue("D{$row}", $nilai->siswa->kelas->nama ?? '-');
            $sheet->setCellValue("E{$row}", $nilai->nilai_pg ?? '-');
            $sheet->setCellValue("F{$row}", $nilai->nilai_bs ?? '-');
            $sheet->setCellValue("G{$row}", $nilai->nilai_essay ?? '-');
            $sheet->setCellValue("H{$row}", $nilai->nilai_murni ?? '-');
            $sheet->setCellValue("I{$row}", $nilai->nilai_final ?? '-');
        }

        foreach (range('A', 'I') as $col) {
            $sheet->getColumnDimension($col)->setAutoSize(true);
        }

        // ✅ fix: sanitize nama file
        $filename = 'Nilai_' . $this->sanitizeFilename($ujian->nama) . '_' . date('YmdHis') . '.xlsx';
        $writer   = new Xlsx($spreadsheet);

        return response()->streamDownload(function () use ($writer) {
            $writer->save('php://output');
        }, $filename, [
            'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        ]);
    }

    /**
     * Export laporan nilai ke PDF
     */
    public function exportNilaiPdf(Ujian $ujian): \Illuminate\Http\Response
    {
        $nilaiList = NilaiUjian::where('ujian_id', $ujian->id)
            ->with('siswa.kelas')
            ->orderBy('siswa_id')
            ->get();

        $pdf = Pdf::loadView('laporan.nilai', [
            'ujian'     => $ujian->load(['mataPelajaran', 'kelas', 'tahunAjaran']),
            'nilaiList' => $nilaiList,
        ])->setPaper('a4', 'landscape');

        // ✅ fix: sanitize nama file
        $filename = 'Nilai_' . $this->sanitizeFilename($ujian->nama) . '_' . date('YmdHis') . '.pdf';

        return $pdf->download($filename);
    }

    /**
     * Generate template Excel untuk import nilai PG & BS
     */
    public function templateImportNilai(Ujian $ujian): StreamedResponse
    {
        $siswaList = \App\Models\Siswa::where('kelas_id', $ujian->kelas_id)
            ->orderBy('nama')
            ->get();

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

        // ✅ Header sesuai format export Google Form
        $headers = ['Score', 'Nama Peserta UM', 'NISN', 'Kelas'];
        foreach ($headers as $col => $header) {
            $sheet->setCellValue(chr(65 + $col) . '1', $header);
        }

        $sheet->getStyle('A1:D1')->applyFromArray([
            'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
            'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => '10B981']],
            'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
        ]);

        // ✅ Isi data siswa: Score kosong, Nama, NISN, Kelas
        foreach ($siswaList as $i => $siswa) {
            $row = $i + 2;
            $sheet->setCellValue("A{$row}", '');              // Score — diisi admin
            $sheet->setCellValue("B{$row}", $siswa->nama);
            $sheet->setCellValue("C{$row}", $siswa->nisn ?? '');
            $sheet->setCellValue("D{$row}", $siswa->kelas->nama ?? '');
        }

        foreach (range('A', 'D') as $col) {
            $sheet->getColumnDimension($col)->setAutoSize(true);
        }

        $filename = 'Template_Nilai_' . $this->sanitizeFilename($ujian->nama) . '.xlsx';
        $writer   = new Xlsx($spreadsheet);

        return response()->streamDownload(function () use ($writer) {
            $writer->save('php://output');
        }, $filename, [
            'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        ]);
    }

    /**
     * Generate template Excel untuk import Guru
     */
    public function templateImportGuru(): StreamedResponse
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Template Guru');

        $headers = ['NIP', 'Nama Lengkap', 'Email', 'Jenis Kelamin (L/P)', 'No HP', 'Alamat'];
        foreach ($headers as $col => $header) {
            $sheet->setCellValue(chr(65 + $col) . '1', $header);
        }

        $sheet->getStyle('A1:F1')->applyFromArray([
            'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
            'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => '2563EB']],
        ]);

        $sheet->setCellValue('A2', '198501012010011001');
        $sheet->setCellValue('B2', 'Ahmad Fauzi, S.Pd');
        $sheet->setCellValue('C2', 'ahmad.fauzi@sekolah.com');
        $sheet->setCellValue('D2', 'L');
        $sheet->setCellValue('E2', '081234567890');
        $sheet->setCellValue('F2', 'Jl. Contoh No. 1');

        foreach (range('A', 'F') as $col) {
            $sheet->getColumnDimension($col)->setAutoSize(true);
        }

        $writer = new Xlsx($spreadsheet);
        return response()->streamDownload(function () use ($writer) {
            $writer->save('php://output');
        }, 'Template_Import_Guru.xlsx', [
            'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        ]);
    }

    /**
     * Generate template Excel untuk import Siswa
     */
    public function templateImportSiswa(): StreamedResponse
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Template Siswa');

        $headers = ['NIS', 'NISN', 'Nama Lengkap', 'Email', 'Jenis Kelamin (L/P)', 'No HP', 'Alamat'];
        foreach ($headers as $col => $header) {
            $sheet->setCellValue(chr(65 + $col) . '1', $header);
        }

        $sheet->getStyle('A1:G1')->applyFromArray([
            'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
            'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => '2563EB']],
        ]);

        $sheet->setCellValue('A2', '2024001');
        $sheet->setCellValue('B2', '1234567890');
        $sheet->setCellValue('C2', 'Siti Aminah');
        $sheet->setCellValue('D2', 'siti.aminah@email.com');
        $sheet->setCellValue('E2', 'P');
        $sheet->setCellValue('F2', '081234567891');
        $sheet->setCellValue('G2', 'Jl. Contoh No. 2');

        foreach (range('A', 'G') as $col) {
            $sheet->getColumnDimension($col)->setAutoSize(true);
        }

        $writer = new Xlsx($spreadsheet);
        return response()->streamDownload(function () use ($writer) {
            $writer->save('php://output');
        }, 'Template_Import_Siswa.xlsx', [
            'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        ]);
    }
}
