<?php

namespace Database\Seeders;

use App\Models\Role;
use App\Models\User;
use App\Models\WarningLetter;
use Illuminate\Database\Seeder;

class WarningLetterSeeder extends Seeder
{
    public function run(): void
    {
        $admin = User::where('email', 'admin@smkn1gunungagung.sch.id')->first();
        $siswaRoleId = Role::where('name', 'siswa')->value('id');

        if (!$siswaRoleId) {
            return;
        }

        $month = now()->format('Y-m');

        $studentsWithAlpha = User::where('role_id', $siswaRoleId)
            ->where('is_active', true)
            ->where('student_status', 'aktif')
            ->withCount([
                'attendanceRecords as alpha_count' => fn ($q) => $q
                    ->where('status', 'Alpha')
                    ->where('date', 'like', $month . '%'),
            ])
            ->orderByDesc('alpha_count')
            ->take(5)
            ->get();

        if ($studentsWithAlpha->isEmpty()) {
            $studentsWithAlpha = User::where('role_id', $siswaRoleId)
                ->where('is_active', true)
                ->take(3)
                ->get()
                ->map(function ($u) {
                    $u->alpha_count = 4;
                    return $u;
                });
        }

        $letters = [
            ['letter_type' => 'sp1', 'notes' => 'Alpha berulang pada minggu berjalan. Harap segera diperbaiki.'],
            ['letter_type' => 'sp1', 'notes' => 'Ketidakhadiran tanpa keterangan melebihi batas toleransi.'],
            ['letter_type' => 'sp2', 'notes' => 'Masih berulang setelah SP1. Dilanjutkan ke panggilan orang tua bila tidak ada perbaikan.'],
            ['letter_type' => 'panggilan_ortu', 'notes' => 'Orang tua/wali diminta hadir ke sekolah untuk pembinaan.'],
        ];

        foreach ($studentsWithAlpha as $i => $student) {
            if ($i >= count($letters)) {
                break;
            }

            $letter = $letters[$i];
            $alphaCount = max((int) ($student->alpha_count ?? 3), 3);

            $existing = WarningLetter::where('user_id', $student->id)
                ->where('letter_type', $letter['letter_type'])
                ->where('month', $month)
                ->first();

            $letterNumber = $existing?->letter_number;

            if (!$letterNumber) {
                $year = now()->format('Y');
                $typeUpper = strtoupper($letter['letter_type']);
                $prefix = sprintf('SP/%s/%s/', $typeUpper, $year);

                $seqCount = WarningLetter::where('letter_number', 'like', $prefix . '%')->count() + 1;
                do {
                    $letterNumber = sprintf('%s%03d', $prefix, $seqCount);
                    $exists = WarningLetter::where('letter_number', $letterNumber)->exists();
                    if ($exists) {
                        $seqCount++;
                    }
                } while ($exists);
            }

            WarningLetter::updateOrCreate(
                [
                    'user_id' => $student->id,
                    'letter_type' => $letter['letter_type'],
                    'month' => $month,
                ],
                [
                    'letter_number' => $letterNumber,
                    'alpha_count' => $alphaCount,
                    'issued_date' => now()->subDays(2 - min($i, 2))->toDateString(),
                    'notes' => $letter['notes'],
                    'created_by' => $admin?->id,
                ]
            );
        }
    }
}
