<?php

namespace App\Http\Controllers;

use App\Models\TahunAjaran;
use App\Models\Siswa;
use App\Models\SiswaKelas;
use App\Models\Kelas;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Spatie\SimpleExcel\SimpleExcelWriter;
use Spatie\SimpleExcel\SimpleExcelReader;

class KenaikanKelasController extends Controller
{
    public function __construct()
    {
        $this->middleware('auth');
    }

    /**
     * Main page: list students from source year with rollover preview.
     */
    public function index(Request $request)
    {
        // Target = active year
        $targetYear = TahunAjaran::aktif()->first();
        if (!$targetYear) {
            return back()->with('error', 'Tidak ada tahun ajaran aktif. Silakan aktifkan tahun ajaran terlebih dahulu.');
        }

        // Source = exactly one year before the active one
        $parts = explode('/', $targetYear->tahun);
        if (count($parts) === 2) {
            $prevYearStart = intval($parts[0]) - 1;
            $prevYearEnd = intval($parts[1]) - 1;
            $expectedSourceTahun = $prevYearStart . '/' . $prevYearEnd;
            $sourceYear = TahunAjaran::where('tahun', $expectedSourceTahun)->first();
        } else {
            $sourceYear = TahunAjaran::where('id', '!=', $targetYear->id)
                ->orderBy('tahun', 'desc')
                ->first();
        }

        if (!$sourceYear) {
            return back()->with('error', 'Tidak ada tahun ajaran sumber (satu tahun sebelumnya). Pastikan tahun ajaran sebelumnya sudah dibuat.');
        }

        // Build query: students that have siswa_kelas in source year
        $query = SiswaKelas::withoutGlobalScope('active')->with(['siswa.riwayatStatus', 'kelas'])
            ->where('siswa_kelas.tahun_ajaran_id', $sourceYear->id)
            ->whereHas('siswa', fn($q) => $q->whereIn('siswa.status', ['aktif', 'tidak_aktif', 'lulus']));

        // Filter: Jenjang
        if ($request->filled('jenjang')) {
            $query->whereHas('kelas', fn($q) => $q->where('jenjang', $request->jenjang));
        }
        // Filter: Tingkat
        if ($request->filled('tingkat')) {
            $query->whereHas('kelas', fn($q) => $q->where('tingkat', $request->tingkat));
        }
        // Filter: Search NIS/Nama
        if ($request->filled('q')) {
            $search = $request->q;
            $query->whereHas('siswa', function ($q) use ($search) {
                $q->where('nis', 'like', "%{$search}%")
                  ->orWhere('nama_lengkap', 'like', "%{$search}%");
            });
        }

        // Get IDs already registered in target year
        $registeredSiswaIds = SiswaKelas::withoutGlobalScope('active')
            ->where('siswa_kelas.tahun_ajaran_id', $targetYear->id)
            ->pluck('siswa_id')
            ->toArray();

        // Filter: Status rollover
        if ($request->filled('status_rollover')) {
            if ($request->status_rollover === 'sudah') {
                $query->where(function($q) use ($registeredSiswaIds) {
                    $q->whereIn('siswa_id', $registeredSiswaIds)
                      ->orWhereHas('siswa.riwayatStatus', fn($sq) => $sq->where('keterangan', 'Lulus'));
                });
            } elseif ($request->status_rollover === 'belum') {
                $query->whereNotIn('siswa_id', $registeredSiswaIds)
                      ->whereDoesntHave('siswa.riwayatStatus', fn($sq) => $sq->where('keterangan', 'Lulus'));
            }
        }

        // Order by kelas
        $query->join('kelas', 'siswa_kelas.kelas_id', '=', 'kelas.id')
              ->orderBy('kelas.jenjang')
              ->orderBy('kelas.tingkat')
              ->orderBy('kelas.paralel')
              ->select('siswa_kelas.*');

        $siswaKelasPaginated = $query->paginate(25)->withQueryString();

        // Map into structured data for the view
        $students = $siswaKelasPaginated->through(function ($sk) use ($registeredSiswaIds, $targetYear) {
            $siswa = $sk->siswa;
            $kelasAsal = $sk->kelas;
            $isLulus = $siswa->riwayatStatus->contains('keterangan', 'Lulus');
            $sudahTerdaftar = in_array($sk->siswa_id, $registeredSiswaIds) || $isLulus;

            // Determine target class
            $kelasTujuan = null;
            if ($sudahTerdaftar && !$isLulus) {
                // Already registered — show the actual target class
                $targetSK = SiswaKelas::withoutGlobalScopes()
                    ->where('siswa_id', $sk->siswa_id)
                    ->where('tahun_ajaran_id', $targetYear->id)
                    ->first();
                $kelasTujuan = $targetSK ? Kelas::find($targetSK->kelas_id) : null;
            }

            return [
                'siswa' => $siswa,
                'kelas_asal' => $kelasAsal,
                'kelas_tujuan' => $kelasTujuan,
                'sudah_terdaftar' => $sudahTerdaftar,
                'is_lulus' => $isLulus,
            ];
        });

        // Stats — gunakan withoutGlobalScope agar siswa_kelas sumber yang sudah
        // berstatus 'nonaktif' setelah proses rollover tetap ikut dihitung.
        $totalSiswaSumber = SiswaKelas::withoutGlobalScope('active')
            ->where('siswa_kelas.tahun_ajaran_id', $sourceYear->id)
            ->whereHas('siswa', fn($q) => $q->whereIn('siswa.status', ['aktif', 'tidak_aktif', 'lulus']))
            ->count();
            
        $totalSudahTerdaftar = SiswaKelas::withoutGlobalScope('active')
            ->where('siswa_kelas.tahun_ajaran_id', $sourceYear->id)
            ->where(function($q) use ($registeredSiswaIds) {
                $q->whereIn('siswa_id', $registeredSiswaIds)
                  ->orWhereHas('siswa.riwayatStatus', fn($sq) => $sq->where('keterangan', 'Lulus'));
            })
            ->whereHas('siswa', fn($q) => $q->whereIn('siswa.status', ['aktif', 'tidak_aktif', 'lulus']))
            ->count();

        // Tingkat list for filter dropdown
        $tingkatList = Kelas::orderBy('tingkat')->pluck('tingkat')->unique()->filter()->values();

        return view('kenaikan-kelas.index', compact(
            'students', 'sourceYear', 'targetYear',
            'totalSiswaSumber', 'totalSudahTerdaftar', 'tingkatList'
        ));
    }

    /**
     * Process selected rollover actions (deprecated/removed, just redirect back if accessed)
     */
    public function proses(Request $request)
    {
        return redirect()->route('kenaikan-kelas.index')->with('info', 'Gunakan fitur Export dan Import Excel untuk pembagian kelas baru.');
    }

    /**
     * Export students to Excel for rollover editing.
     */
    public function export(Request $request)
    {
        $targetYear = TahunAjaran::aktif()->first();
        $parts = explode('/', $targetYear->tahun);
        if (count($parts) === 2) {
            $prevYearStart = intval($parts[0]) - 1;
            $prevYearEnd = intval($parts[1]) - 1;
            $expectedSourceTahun = $prevYearStart . '/' . $prevYearEnd;
            $sourceYear = TahunAjaran::where('tahun', $expectedSourceTahun)->first();
        } else {
            $sourceYear = TahunAjaran::where('id', '!=', $targetYear->id)
                ->orderBy('tahun', 'desc')
                ->first();
        }

        if (!$sourceYear || !$targetYear) {
            return back()->with('error', 'Data tahun ajaran tidak lengkap atau tahun ajaran sebelumnya tidak ditemukan.');
        }

        $registeredSiswaIds = SiswaKelas::where('siswa_kelas.tahun_ajaran_id', $targetYear->id)
            ->pluck('siswa_id')
            ->toArray();

        $query = SiswaKelas::with(['siswa', 'kelas'])
            ->where('siswa_kelas.tahun_ajaran_id', $sourceYear->id)
            ->whereHas('siswa', fn($q) => $q->where('siswa.status', 'aktif'))
            ->join('kelas', 'siswa_kelas.kelas_id', '=', 'kelas.id')
            ->orderBy('kelas.jenjang')
            ->orderBy('kelas.tingkat')
            ->orderBy('kelas.paralel')
            ->select('siswa_kelas.*');

        $records = $query->get();

        $writer = SimpleExcelWriter::streamDownload('format_pembagian_kelas_' . $targetYear->tahun . '.xlsx');

        foreach ($records as $index => $sk) {
            $siswa = $sk->siswa;
            $kelasAsal = $sk->kelas;
            $sudah = in_array($sk->siswa_id, $registeredSiswaIds);
            
            // Only export students that have not been rolled over yet
            if (!$sudah) {
                $writer->addRow([
                    'No' => $index + 1,
                    'NIS' => $siswa->nis ?? '-',
                    'Nama Siswa' => $siswa->nama_lengkap ?? '-',
                    'Kelas Asal' => $kelasAsal->nama_kelas ?? '-',
                    'Kelas Tujuan' => '', // To be filled by user
                ]);
            }
        }

        return $writer->toBrowser();
    }

        
    /**
     * Import rollover actions from Excel.
     */
    public function import(Request $request)
    {
        $request->validate([
            'file_excel' => 'required|mimes:xlsx,csv|max:5120',
        ]);

        $targetYear = TahunAjaran::aktif()->first();
        if (!$targetYear) {
            return back()->with('error', 'Tidak ada tahun ajaran aktif.');
        }

        $file = $request->file('file_excel');
        $reader = SimpleExcelReader::create($file->getRealPath(), $file->getClientOriginalExtension());

        $processed = 0;
        $failed = [];

        DB::transaction(function () use ($reader, $targetYear, &$processed, &$failed) {
            $reader->getRows()->each(function (array $row) use ($targetYear, &$processed, &$failed) {
                $clean = [];
                foreach ($row as $key => $value) {
                    $clean[strtolower(trim($key))] = trim($value ?? '');
                }

                $nis = $clean['nis'] ?? '';
                $kelasTujuanStr = $clean['kelas tujuan'] ?? '';

                if (empty($nis)) {
                    // Skip empty rows
                    return;
                }

                $siswa = Siswa::where('nis', $nis)->first();
                if (!$siswa) {
                    $failed[] = "NIS {$nis}: Siswa tidak ditemukan.";
                    return;
                }

                // If class is empty, we assume they are graduating or leaving, but for this specific process,
                // we probably just want to skip them or mark them if requested. Let's just skip them if no class is provided.
                if (empty($kelasTujuanStr)) {
                    return;
                }

                if (strtolower(trim($kelasTujuanStr)) === 'lulus') {
                    $oldStatus = $siswa->status;
                    if ($oldStatus !== 'tidak_aktif') {
                        $siswa->update(['status' => 'tidak_aktif']);
                        
                        \App\Models\RiwayatStatusSiswa::create([
                            'siswa_id' => $siswa->id,
                            'tahun_ajaran_id' => $targetYear->id,
                            'status_lama' => $oldStatus,
                            'status_baru' => 'tidak_aktif',
                            'keterangan' => 'Lulus',
                        ]);

                        SiswaKelas::where('siswa_id', $siswa->id)
                            ->where('tahun_ajaran_id', '!=', $targetYear->id)
                            ->update(['status' => 'nonaktif']);
                    }
                    $processed++;
                    return;
                }

                $kelasTujuan = Kelas::aktif()
                    ->whereRaw('LOWER(TRIM(nama_kelas)) = ?', [strtolower(trim($kelasTujuanStr))])
                    ->first();

                if (!$kelasTujuan) {
                    $failed[] = "NIS {$nis}: Kelas tujuan '{$kelasTujuanStr}' tidak ditemukan atau tidak aktif.";
                    return;
                }

                // Skip if already registered
                $already = SiswaKelas::where('siswa_id', $siswa->id)
                    ->where('tahun_ajaran_id', $targetYear->id)
                    ->exists();
                if ($already) {
                    return;
                }

                // Nonaktifkan penempatan kelas di tahun ajaran sebelumnya/lainnya
                SiswaKelas::where('siswa_id', $siswa->id)
                    ->where('tahun_ajaran_id', '!=', $targetYear->id)
                    ->update(['status' => 'nonaktif']);

                SiswaKelas::create([
                    'siswa_id' => $siswa->id,
                    'kelas_id' => $kelasTujuan->id,
                    'tahun_ajaran_id' => $targetYear->id,
                    'status' => 'aktif',
                ]);

                if ($targetYear->status === 'aktif') {
                    app(\App\Services\TagihanService::class)->generateTagihanSiswa($siswa, $kelasTujuan, $targetYear);
                }

                $processed++;
            });
        });

        $msg = "{$processed} siswa berhasil didaftarkan ke kelas baru.";
        if (count($failed) > 0) {
            return redirect()->route('kenaikan-kelas.index')
                ->with('success', $msg)
                ->with('error', 'Beberapa baris gagal diproses: ' . implode(' | ', $failed));
        }

        return redirect()->route('kenaikan-kelas.index')->with('success', $msg);
    }
}
