<?php

namespace App\Services;

use App\Contracts\AuditLogServiceInterface;
use App\Models\User;
use Illuminate\Http\UploadedFile;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\Hash;

class PersonnelImportService
{
    public function __construct(
        protected AuditLogServiceInterface $auditLogService
    ) {}

    public function importFromFile(UploadedFile $file, int $defaultRoleId, ?int $defaultShiftId = null, bool $autoGraduateClass12 = true): array
    {
        $filePath = $file->getRealPath();
        $extension = strtolower($file->getClientOriginalExtension());

        if ($extension === 'csv') {
            $reader = new \PhpOffice\PhpSpreadsheet\Reader\Csv();
        } else {
            $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
        }

        $spreadsheet = $reader->load($filePath);
        $sheetData = $spreadsheet->getActiveSheet()->toArray();

        if (empty($sheetData) || count($sheetData) < 2) {
            return [
                'success' => false,
                'imported_count' => 0,
                'skipped_count' => 0,
                'updated_count' => 0,
                'graduated_count' => 0,
                'message' => 'File kosong atau tidak memiliki data baris.',
            ];
        }

        // 1. Smart Header Auto-Detection (Pindai baris 0 - 10 untuk menemukan baris header Dapodik)
        $headerRowIndex = $this->detectHeaderRowIndex($sheetData);
        if ($headerRowIndex === -1) {
            return [
                'success' => false,
                'imported_count' => 0,
                'skipped_count' => 0,
                'updated_count' => 0,
                'graduated_count' => 0,
                'message' => 'Gagal mendeteksi baris header Dapodik. Pastikan file memuat kolom "Nama" dan "NISN/NIP".',
            ];
        }

        $rawHeaders = $sheetData[$headerRowIndex];
        $headers = array_map(fn($h) => strtolower(trim((string)$h)), $rawHeaders);

        // 2. Pemetaan Kolom Fleksibel (Dapodik Candidates)
        $nameIndex = $this->findColumnIndex($headers, ['nama', 'name', 'nama lengkap', 'nama peserta didik', 'nama ptk', 'nama siswa']);
        $nipNisIndex = $this->findColumnIndex($headers, ['nisn', 'nis', 'nipd', 'nip', 'nik', 'nip_nis', 'nip/nis', 'nomor induk', 'no induk']);
        $classIndex = $this->findColumnIndex($headers, ['rombel', 'rombongan belajar', 'rombel saat ini', 'kelas', 'tingkat', 'class_name']);
        $phoneIndex = $this->findColumnIndex($headers, ['no hp', 'nomor hp', 'hp', 'telepon', 'no telepon', 'hp orang tua', 'no hp wali', 'phone_number']);
        $emailIndex = $this->findColumnIndex($headers, ['email', 'alamat email', 'e-mail', 'email peserta didik', 'email pribadi']);

        if ($nameIndex === -1) {
            return [
                'success' => false,
                'imported_count' => 0,
                'skipped_count' => 0,
                'updated_count' => 0,
                'graduated_count' => 0,
                'message' => 'Kolom "Nama" tidak ditemukan pada baris header Dapodik.',
            ];
        }

        $createdCount = 0;
        $updatedCount = 0;
        $skippedCount = 0;
        $importedNipNisList = [];

        // Loop data baris setelah header
        for ($i = $headerRowIndex + 1; $i < count($sheetData); $i++) {
            $row = $sheetData[$i];
            
            $name = isset($row[$nameIndex]) ? trim((string)$row[$nameIndex]) : '';
            if (empty($name)) {
                continue;
            }

            $nipNis = ($nipNisIndex !== -1 && isset($row[$nipNisIndex])) ? trim((string)$row[$nipNisIndex]) : null;
            $className = ($classIndex !== -1 && isset($row[$classIndex])) ? trim((string)$row[$classIndex]) : null;
            $phone = ($phoneIndex !== -1 && isset($row[$phoneIndex])) ? trim((string)$row[$phoneIndex]) : null;

            if (!empty($nipNis)) {
                $importedNipNisList[] = $nipNis;
            }

            // Email Extraction / Smart Generation
            $email = ($emailIndex !== -1 && isset($row[$emailIndex])) ? trim((string)$row[$emailIndex]) : '';
            
            if (empty($email) || !filter_var($email, FILTER_VALIDATE_EMAIL)) {
                // Generate Email Sekolah Otomatis jika kosong
                $slugName = \Illuminate\Support\Str::slug($name, '.');
                $identifier = !empty($nipNis) ? preg_replace('/[^0-9]/', '', $nipNis) : $slugName;
                $email = "{$identifier}@smkn1gunungagung.sch.id";
            }

            // PILIHAN 2: UPSERT MODE (Pencocokan NISN/NIP, Email, atau Nama termasuk Soft Deleted)
            $existingUser = null;
            if (!empty($nipNis)) {
                $existingUser = User::withTrashed()->where('nip_nis', $nipNis)->first();
            }
            if (!$existingUser && !empty($email)) {
                $existingUser = User::withTrashed()->where('email', $email)->first();
            }
            if (!$existingUser && !empty($name)) {
                $existingUser = User::withTrashed()->where('name', $name)->where('role_id', $defaultRoleId)->first();
            }

            if ($existingUser) {
                // Jika user sebelumnya terhapus (soft deleted), pulihkan (restore) terlebih dahulu
                if ($existingUser->trashed()) {
                    $existingUser->restore();
                }

                // Perbarui data siswa/personil eksisting (misal naik kelas, ganti No HP, atau ganti nama)
                $updateData = [
                    'name' => $name,
                    'role_id' => $defaultRoleId,
                    'is_active' => true,
                    'student_status' => 'aktif',
                ];

                if (!empty($className)) {
                    $updateData['class_name'] = $className;
                }
                if (!empty($phone)) {
                    $updateData['phone_number'] = $phone;
                }
                if (!empty($nipNis)) {
                    $updateData['nip_nis'] = $nipNis;
                }
                if ($defaultShiftId) {
                    $updateData['shift_id'] = $defaultShiftId;
                }

                $existingUser->update($updateData);
                $updatedCount++;
            } else {
                // Pastikan Email Benar-Benar Unik (termasuk cek Soft Deleted) Mencegah UniqueConstraintViolationException
                $originalEmail = $email;
                $counter = 1;
                while (User::withTrashed()->where('email', $email)->exists()) {
                    $parts = explode('@', $originalEmail);
                    $email = $parts[0] . '.' . $counter . '@' . ($parts[1] ?? 'smkn1gunungagung.sch.id');
                    $counter++;
                }

                // Buat data personil/siswa pindahan baru
                User::create([
                    'role_id' => $defaultRoleId,
                    'shift_id' => $defaultShiftId,
                    'name' => $name,
                    'nip_nis' => $nipNis,
                    'class_name' => $className,
                    'student_status' => 'aktif',
                    'phone_number' => $phone,
                    'email' => $email,
                    'password' => Hash::make('password123'),
                    'force_password_change' => true,
                    'is_active' => true,
                ]);

                $createdCount++;
            }
        }

        // STRATEGI 3: Otomatisasi Penandaan Alumni untuk Siswa Kelas XII yang sudah lulus
        $graduatedCount = 0;
        if ($autoGraduateClass12 && !empty($importedNipNisList)) {
            // Cari siswa aktif berkategori Kelas XII / 12 yang TIDAK ADA lagi di file Dapodik baru
            $graduatedStudents = User::whereHas('role', fn($q) => $q->where('name', 'siswa'))
                ->where('is_active', true)
                ->where(function ($q) {
                    $q->where('class_name', 'LIKE', 'XII%')
                      ->orWhere('class_name', 'LIKE', '12%');
                })
                ->whereNotIn('nip_nis', $importedNipNisList)
                ->get();

            foreach ($graduatedStudents as $grad) {
                $grad->update([
                    'student_status' => 'lulus',
                    'is_active' => false,
                ]);
                $graduatedCount++;
            }
        }

        $user = Auth::user();
        $this->auditLogService->log(
            $user,
            'PERSONNEL_IMPORTED',
            "Berhasil memproses file Dapodik: {$createdCount} siswa baru, {$updatedCount} siswa diperbarui kelasnya, {$graduatedCount} siswa Kelas XII lama ditandai sebagai Alumni/Lulus"
        );

        $msg = "Berhasil memproses file Dapodik: {$createdCount} siswa baru ditambahkan, {$updatedCount} data siswa diperbarui (update kelas/HP)";
        if ($graduatedCount > 0) {
            $msg .= ", serta {$graduatedCount} siswa Kelas XII lama otomatis ditandai sebagai Alumni/Lulus.";
        } else {
            $msg .= ".";
        }

        return [
            'success' => true,
            'imported_count' => $createdCount,
            'updated_count' => $updatedCount,
            'skipped_count' => $skippedCount,
            'graduated_count' => $graduatedCount,
            'message' => $msg,
        ];
    }

    private function detectHeaderRowIndex(array $sheetData): int
    {
        $maxScan = min(10, count($sheetData));
        $keywords = ['nama', 'nisn', 'nip', 'rombel', 'kelas', 'nama lengkap', 'nama peserta didik'];

        for ($i = 0; $i < $maxScan; $i++) {
            $rowStr = strtolower(implode(' ', array_map(fn($v) => (string)$v, $sheetData[$i])));
            foreach ($keywords as $kw) {
                if (str_contains($rowStr, $kw)) {
                    return $i;
                }
            }
        }

        return -1;
    }

    private function findColumnIndex(array $headers, array $candidates): int
    {
        foreach ($headers as $index => $header) {
            foreach ($candidates as $cand) {
                if (str_contains($header, $cand)) {
                    return $index;
                }
            }
        }
        return -1;
    }
}
