<?php

namespace App\Services;

use App\Models\Guru;
use App\Models\Siswa;
use App\Models\User;
use App\Models\Kelas;
use Illuminate\Http\UploadedFile;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Hash;
use PhpOffice\PhpSpreadsheet\IOFactory;

class ExcelImportService
{
    /**
     * Import data guru dari file Excel.
     * Template kolom: NIP, Nama, Email, Jenis Kelamin (L/P), No HP, Alamat
     */
    public function importGuru(UploadedFile $file): array
    {
        $spreadsheet = IOFactory::load($file->getPathname());
        $sheet       = $spreadsheet->getActiveSheet();
        $rows        = $sheet->toArray();

        // Skip header row
        array_shift($rows);

        $berhasil = 0;
        $gagal    = [];

        DB::beginTransaction();
        try {
            foreach ($rows as $index => $row) {
                $rowNum = $index + 2;

                // Skip baris kosong
                if (empty(array_filter($row))) continue;

                $nip           = trim($row[0] ?? '');
                $nama          = trim($row[1] ?? '');
                $email         = trim($row[2] ?? '');
                $jenisKelamin  = strtoupper(trim($row[3] ?? 'L'));
                $noHp          = trim($row[4] ?? '');
                $alamat        = trim($row[5] ?? '');

                // Validasi wajib
                if (empty($nama) || empty($email)) {
                    $gagal[] = "Baris {$rowNum}: Nama dan Email wajib diisi.";
                    continue;
                }

                // Cek email duplikat
                if (User::where('email', $email)->exists()) {
                    $gagal[] = "Baris {$rowNum}: Email {$email} sudah digunakan.";
                    continue;
                }

                // Buat user
                $user = User::create([
                    'name'     => $nama,
                    'email'    => $email,
                    'password' => Hash::make('guru123'), // password default
                    'role'     => 'guru',
                    'is_aktif' => true,
                ]);

                // Buat profil guru
                Guru::create([
                    'user_id'      => $user->id,
                    'nip'          => $nip ?: null,
                    'nama'         => $nama,
                    'jenis_kelamin'=> in_array($jenisKelamin, ['L', 'P']) ? $jenisKelamin : 'L',
                    'no_hp'        => $noHp ?: null,
                    'alamat'       => $alamat ?: null,
                ]);

                $berhasil++;
            }

            DB::commit();
        } catch (\Exception $e) {
            DB::rollBack();
            throw $e;
        }

        return [
            'berhasil' => $berhasil,
            'gagal'    => $gagal,
            'total'    => $berhasil + count($gagal),
        ];
    }

    /**
     * Import data siswa dari file Excel.
     * Template kolom: NIS, NISN, Nama, Email, Jenis Kelamin (L/P), Kelas ID, No HP, Alamat
     */
    public function importSiswa(UploadedFile $file, int $kelasId): array
    {
        $kelas = Kelas::findOrFail($kelasId);

        $spreadsheet = IOFactory::load($file->getPathname());
        $sheet       = $spreadsheet->getActiveSheet();
        $rows        = $sheet->toArray();

        array_shift($rows);

        $berhasil = 0;
        $gagal    = [];

        DB::beginTransaction();
        try {
            foreach ($rows as $index => $row) {
                $rowNum = $index + 2;

                if (empty(array_filter($row))) continue;

                $nis          = trim($row[0] ?? '');
                $nisn         = trim($row[1] ?? '');
                $nama         = trim($row[2] ?? '');
                $email        = trim($row[3] ?? '');
                $jenisKelamin = strtoupper(trim($row[4] ?? 'L'));
                $noHp         = trim($row[5] ?? '');
                $alamat       = trim($row[6] ?? '');

                if (empty($nama) || empty($email)) {
                    $gagal[] = "Baris {$rowNum}: Nama dan Email wajib diisi.";
                    continue;
                }

                if (User::where('email', $email)->exists()) {
                    $gagal[] = "Baris {$rowNum}: Email {$email} sudah digunakan.";
                    continue;
                }

                $user = User::create([
                    'name'     => $nama,
                    'email'    => $email,
                    'password' => Hash::make('siswa123'), // password default
                    'role'     => 'siswa',
                    'is_aktif' => true,
                ]);

                Siswa::create([
                    'user_id'      => $user->id,
                    'kelas_id'     => $kelas->id,
                    'nis'          => $nis ?: null,
                    'nisn'         => $nisn ?: null,
                    'nama'         => $nama,
                    'jenis_kelamin'=> in_array($jenisKelamin, ['L', 'P']) ? $jenisKelamin : 'L',
                    'no_hp'        => $noHp ?: null,
                    'alamat'       => $alamat ?: null,
                ]);

                $berhasil++;
            }

            // Update jumlah siswa di kelas
            $kelas->update(['jumlah_siswa' => Siswa::where('kelas_id', $kelas->id)->count()]);

            DB::commit();
        } catch (\Exception $e) {
            DB::rollBack();
            throw $e;
        }

        return [
            'berhasil' => $berhasil,
            'gagal'    => $gagal,
            'total'    => $berhasil + count($gagal),
        ];
    }

    /**
     * Import nilai PG+BS dari Excel hasil Google Form.
     * Format kolom: Score | Nama Peserta UM | NISN | Kelas
     * Score = total nilai PG+BS (tanpa essay), disimpan ke nilai_pg.
     */
    public function importNilaiPgBs(UploadedFile $file, int $ujianId): array
    {
        $spreadsheet = IOFactory::load($file->getPathname());
        $sheet       = $spreadsheet->getActiveSheet();
        $rows        = $sheet->toArray();

        // Validasi header
        $header = array_map(fn($h) => strtolower(trim((string)$h)), $rows[0] ?? []);
        if (!isset($header[0]) || !str_contains($header[0], 'score')) {
            return [
                'berhasil' => 0,
                'gagal'    => ['Format file tidak sesuai. Kolom pertama harus "Score" (sesuai export Google Form).'],
                'total'    => 0,
            ];
        }

        array_shift($rows); // skip header

        $berhasil = 0;
        $gagal    = [];

        DB::beginTransaction();
        try {
            foreach ($rows as $index => $row) {
                $rowNum = $index + 2;

                if (empty(array_filter(array_map('strval', $row)))) continue;

                $score = trim((string)($row[0] ?? ''));
                $nama  = trim((string)($row[1] ?? ''));
                $nisn  = trim((string)($row[2] ?? ''));

                if (empty($nisn)) {
                    $gagal[] = "Baris {$rowNum}: NISN kosong ({$nama}).";
                    continue;
                }

                if ($score !== '' && !is_numeric($score)) {
                    $gagal[] = "Baris {$rowNum}: Score '{$score}' bukan angka ({$nama}).";
                    continue;
                }

                $siswa = Siswa::where('nisn', $nisn)->first();
                if (!$siswa) {
                    $gagal[] = "Baris {$rowNum}: NISN {$nisn} ({$nama}) tidak ditemukan.";
                    continue;
                }

                $nilaiPgBs = $score !== '' ? round(floatval($score), 2) : 0;

                $nilai = \App\Models\NilaiUjian::firstOrNew([
                    'ujian_id' => $ujianId,
                    'siswa_id' => $siswa->id,
                ]);

                // Score dari Google Form = nilai PG+BS (murni tanpa essay)
                $nilai->nilai_pg     = $nilaiPgBs;
                $nilai->nilai_bs     = 0;
                $nilai->is_uploaded  = true;
                // nilai_murni = pg+bs saja, essay akan ditambahkan guru nanti
                $nilai->nilai_murni  = $nilaiPgBs + ($nilai->nilai_essay ?? 0);
                $nilai->save();

                $berhasil++;
            }

            DB::commit();
        } catch (\Exception $e) {
            DB::rollBack();
            throw $e;
        }

        return ['berhasil' => $berhasil, 'gagal' => $gagal, 'total' => $berhasil + count($gagal)];
    }
}
