<?php

namespace App\Http\Controllers\Kabupaten;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;

// Menggunakan pustaka resmi PhpSpreadsheet untuk memanipulasi file XLSX secara presisi
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
use PhpOffice\PhpSpreadsheet\IOFactory;

class ExcelImportController extends Controller
{
    /**
     * Tampilkan formulir unggah berkas excel massal dan riwayat impor.
     */
    public function showForm()
    {
        $riwayatImport = DB::table('usulan')
            ->select('batch_import_id', 'tanggal_usulan', DB::raw('count(*) as jumlah_usulan'))
            ->whereNotNull('batch_import_id')
            ->groupBy('batch_import_id', 'tanggal_usulan')
            ->orderBy('tanggal_usulan', 'DESC')
            ->get();

        return view('kabupaten.usulan.import', compact('riwayatImport'));
    }

    /**
     * Membuat dan mengunduh template XLSX asli dengan dropdown kegiatan dinamis dari database.
     */
    public function unduhTemplate()
    {
        try {
            // 1. Ambil daftar kegiatan aktif dari database MySQL
            $kegiatanAktif = DB::table('kegiatan')
                ->where('is_active', true)
                ->pluck('nama_kegiatan')
                ->toArray();

            if (empty($kegiatanAktif)) {
                $kegiatanAktif = ['Saprodi Padi Unggul', 'Bantuan Pupuk Jagung Hibrida']; // Fallback aman
            }

            // 2. Inisiasi Spreadsheet Baru
            $spreadsheet = new Spreadsheet();
            
            // --- SHEET 1: FORM INPUT UTAMA ---
            $sheetUtama = $spreadsheet->getActiveSheet();
            $sheetUtama->setTitle('Draf Usulan');

            // Set Header Kolom Baru dengan Kecamatan & Desa
            $sheetUtama->setCellValue('A1', 'NAMA_KELOMPOK_TANI');
            $sheetUtama->setCellValue('B1', 'KECAMATAN');
            $sheetUtama->setCellValue('C1', 'DESA');
            $sheetUtama->setCellValue('D1', 'NAMA_PROGRAM_BANTUAN');
            $sheetUtama->setCellValue('E1', 'LUAS_LAHAN_HEKTAR');

            // Format Lebar Kolom agar rapi saat dibuka
            $sheetUtama->getColumnDimension('A')->setWidth(25);
            $sheetUtama->getColumnDimension('B')->setWidth(20);
            $sheetUtama->getColumnDimension('C')->setWidth(20);
            $sheetUtama->getColumnDimension('D')->setWidth(35);
            $sheetUtama->getColumnDimension('E')->setWidth(22);

            // Berikan contoh baris data pertama yang aman (Sesuai wilayah Demak di seeder)
            $sheetUtama->setCellValue('A2', 'Tani Mulyo I');
            $sheetUtama->setCellValue('B2', 'Mranggen');
            $sheetUtama->setCellValue('C2', 'Kembangarum');
            $sheetUtama->setCellValue('D2', $kegiatanAktif[0]);
            $sheetUtama->setCellValue('E2', '1.5');

            // --- SHEET 2: DAFTAR REFERENSI (SUMBER DROPDOWN) ---
            $sheetRef = $spreadsheet->createSheet();
            $sheetRef->setTitle('ReferensiDinas');
            
            // Isi sheet referensi dengan daftar nama kegiatan dinamis dari database
            foreach ($kegiatanAktif as $index => $namaKegiatan) {
                $row = $index + 1;
                $sheetRef->setCellValue("A{$row}", $namaKegiatan);
            }

            // Sembunyikan sheet referensi dengan aman menggunakan string parameter 'hidden'
            $sheetRef->setSheetState('hidden');

            // --- PASANG VALIDASI DROPDOWN PADA KOLOM PROGRAM (KOLOM D) ---
            $totalRowRef = count($kegiatanAktif);
            
            for ($row = 2; $row <= 150; $row++) {
                $cell = $sheetUtama->getCell("D{$row}");
                $validation = $cell->getDataValidation();
                
                $validation->setType(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST);
                $validation->setErrorStyle(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::ERROR_STYLE_STOP);
                $validation->setAllowBlank(false);
                $validation->setShowInputMessage(true);
                $validation->setShowErrorMessage(true);
                $validation->setShowDropDown(true);
                
                // Pesan petunjuk saat sel diklik
                $validation->setPromptTitle('Pilih Program');
                $validation->setPrompt('Pilihlah salah satu program kegiatan resmi dari menu dropdown.');
                
                // Pesan peringatan jika pengguna memaksa mengetik manual secara salah
                $validation->setErrorTitle('Pilihan Salah');
                $validation->setError('Program yang Anda ketik tidak terdaftar di database dinas!');
                
                // Formula merujuk pada sheet referensi tersembunyi
                $validation->setFormula1("ReferensiDinas!\$A\$1:\$A\${$totalRowRef}");
            }

            // 3. Proses Pengaliran Dokumen XLSX ke Browser
            $writer = new Xlsx($spreadsheet);
            
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment; filename="template_usulan_masal_pradipa.xlsx"');
            header('Cache-Control: max-age=0');

            $writer->save('php://output');
            exit;

        } catch (\Exception $e) {
            return back()->withErrors(['error' => 'Gagal membuat template Excel: ' . $e->getMessage()]);
        }
    }

    /**
     * Memproses berkas XLSX yang diunggah dengan validasi wilayah bertingkat & Dynamic Relation Lookup.
     */
    public function prosesImport(Request $request)
    {
        $request->validate([
            'berkas_import' => 'required|file|mimes:xlsx,xls|max:4096'
        ], [
            'berkas_import.required' => 'Silakan unggah berkas draf usulan XLSX Anda terlebih dahulu!',
            'berkas_import.mimes' => 'Format dokumen wajib berupa file spreadsheet asli berformat .xlsx / .xls!'
        ]);

        try {
            $file = $request->file('berkas_import');
            
            // Load berkas menggunakan IOFactory milik PhpSpreadsheet
            $spreadsheet = IOFactory::load($file->getRealPath());
            $sheet = $spreadsheet->getActiveSheet();
            $highestRow = $sheet->getHighestRow();

            $errorLogs = [];
            $importedCount = 0;
            $batchId = 'BCH-' . date('YmdHis') . '-' . strtoupper(Str::random(5));

            // Pembacaan ID user penguji/pengimpor
            $userId = intval($request->input('simulasi_user_id', 3));
            $user = DB::table('users')->where('id', $userId)->first();
            $regencyId = $user ? $user->regency_id : 1;

            // Tracker memory lokal untuk mendeteksi baris ganda di dalam file Excel itu sendiri
            $excelDuplicateTracker = [];

            DB::beginTransaction();

            // Membaca data mulai dari baris ke-2 (menghindari baris header)
            for ($row = 2; $row <= $highestRow; $row++) {
                $namaPoktan = trim($sheet->getCell("A{$row}")->getValue());
                $namaKecamatan = trim($sheet->getCell("B{$row}")->getValue());
                $namaDesa = trim($sheet->getCell("C{$row}")->getValue());
                $namaKegiatan = trim($sheet->getCell("D{$row}")->getValue());
                $luasLahan = floatval($sheet->getCell("E{$row}")->getValue());

                // Lewati baris jika seluruh sel kosong
                if (empty($namaPoktan) && empty($namaKecamatan) && empty($namaDesa) && empty($namaKegiatan) && $luasLahan == 0) {
                    continue;
                }

                // --- 1. VALIDASI KECAMATAN DI DALAM KABUPATEN LOGIN ---
                $kecamatan = DB::table('districts')
                    ->where('nama_kecamatan', 'LIKE', $namaKecamatan)
                    ->where('regency_id', $regencyId)
                    ->first();

                if (!$kecamatan) {
                    $errorLogs[] = "Baris {$row}: Kecamatan '{$namaKecamatan}' tidak ditemukan atau tidak berada di bawah wewenang wilayah kabupaten Anda!";
                    continue;
                }

                // --- 2. VALIDASI DESA DI DALAM KECAMATAN TERKAIT ---
                $desa = DB::table('villages')
                    ->where('nama_desa', 'LIKE', $namaDesa)
                    ->where('district_id', $kecamatan->id)
                    ->first();

                if (!$desa) {
                    $errorLogs[] = "Baris {$row}: Desa '{$namaDesa}' tidak ditemukan di dalam wilayah Kecamatan '{$namaKecamatan}'!";
                    continue;
                }

                // --- 3. VALIDASI KELOMPOK TANI DI DALAM DESA TERKAIT ---
                $poktan = DB::table('kelompok_tani')
                    ->where('nama_kelompok', 'LIKE', $namaPoktan)
                    ->where('village_id', $desa->id)
                    ->first();

                if (!$poktan) {
                    $errorLogs[] = "Baris {$row}: Kelompok Tani '{$namaPoktan}' tidak terdaftar resmi di Desa {$namaDesa}, Kec. {$namaKecamatan}!";
                    continue;
                }

                // --- 4. VALIDASI PROGRAM KEGIATAN ---
                $kegiatan = DB::table('kegiatan')
                    ->where('nama_kegiatan', $namaKegiatan)
                    ->where('is_active', true)
                    ->first();

                if (!$kegiatan) {
                    $errorLogs[] = "Baris {$row}: Program bantuan '{$namaKegiatan}' tidak terdaftar atau sudah tidak aktif.";
                    continue;
                }

                // === FITUR ANTI-DUPLIKASI BANTUAN ===
                $isAlreadyRegisteredInDb = DB::table('usulan')
                    ->where('kelompok_tani_id', $poktan->id)
                    ->where('kegiatan_id', $kegiatan->id)
                    ->exists();

                if ($isAlreadyRegisteredInDb) {
                    $errorLogs[] = "Baris {$row}: Kelompok Tani '{$poktan->nama_kelompok}' sudah pernah mengajukan usulan program '{$namaKegiatan}' sebelumnya di database!";
                    continue;
                }

                if (isset($excelDuplicateTracker[$poktan->id][$kegiatan->id])) {
                    $errorLogs[] = "Baris {$row}: Penulisan ganda terdeteksi! Kelompok Tani '{$poktan->nama_kelompok}' ditulis berulang kali untuk program '{$namaKegiatan}' di dalam berkas Excel ini (Lihat baris ke-{$excelDuplicateTracker[$poktan->id][$kegiatan->id]} & {$row})!";
                    continue;
                }

                $excelDuplicateTracker[$poktan->id][$kegiatan->id] = $row;

                // --- VALIDASI BATAS MINIMAL LAHAN ---
                if ($luasLahan < 0.1) {
                    $errorLogs[] = "Baris {$row}: Luas lahan '{$luasLahan} Ha' tidak memenuhi standar batas minimal (0.1 Ha).";
                    continue;
                }

                // Simpan Baris Induk Usulan
                $usulanId = DB::table('usulan')->insertGetId([
                    'kegiatan_id' => $kegiatan->id,
                    'kelompok_tani_id' => $poktan->id,
                    'user_id' => $userId,
                    'status' => 'diajukan',
                    'akumulasi_skor' => 0.00, // Default 0, akan diperbarui dinamis setelah item di-insert
                    'batch_import_id' => $batchId,
                    'tanggal_usulan' => now(),
                    'created_at' => now(),
                    'updated_at' => now(),
                ]);

                // --- DYNAMIC LOOKUP BARANG ---
                $barangDef = DB::table('barang_kegiatan')
                    ->where('kegiatan_id', $kegiatan->id)
                    ->first();

                if ($barangDef) {
                    DB::table('usulan_barang')->insert([
                        'usulan_id' => $usulanId,
                        'barang_kegiatan_id' => $barangDef->id,
                        'jumlah_diusulkan' => round($luasLahan * 50),
                        'luas_pemanfaatan' => $luasLahan,
                        'created_at' => now(),
                        'updated_at' => now(),
                    ]);
                }

                // --- DYNAMIC LOOKUP SYARAT ---
                $syaratDefs = DB::table('syarat_kegiatan')
                    ->where('kegiatan_id', $kegiatan->id)
                    ->get();

                foreach ($syaratDefs as $sd) {
                    DB::table('usulan_syarat')->insert([
                        'usulan_id' => $usulanId,
                        'syarat_kegiatan_id' => $sd->id,
                        'file_path' => null,
                        'koordinat_lahan' => null, // Spasial kosong, operator kabupaten akan melukisnya nanti di peta
                        'status_verifikasi' => 'pending',
                        'skor_didapat' => 0.00,
                        'created_at' => now(),
                        'updated_at' => now(),
                    ]);
                }

                $importedCount++;
            }

            if (count($errorLogs) > 0) {
                DB::rollBack();
                return back()->withErrors($errorLogs);
            }

            DB::commit();

            if ($importedCount === 0) {
                return back()->withErrors(['error' => 'Berkas Excel Anda tidak memiliki baris data yang valid untuk diimpor.']);
            }

            return redirect()->route('kabupaten.usulan.import_form')->with('success', "Sukses mengimpor {$importedCount} data usulan baru secara massal! Batch ID Pelacak: {$batchId}");

        } catch (\Exception $e) {
            DB::rollBack();
            return back()->withErrors(['error' => 'Gagal membaca berkas Excel: ' . $e->getMessage()]);
        }
    }

    /**
     * Membatalkan pengimporan draf secara masal jika terjadi kelalaian operator.
     */
    public function rollbackBatch($batchId)
    {
        DB::beginTransaction();
        try {
            $usulanIds = DB::table('usulan')->where('batch_import_id', $batchId)->pluck('id');

            DB::table('usulan_barang')->whereIn('usulan_id', $usulanIds)->delete();
            DB::table('usulan_syarat')->whereIn('usulan_id', $usulanIds)->delete();
            $deletedCount = DB::table('usulan')->where('batch_import_id', $batchId)->delete();

            DB::commit();
            return redirect()->back()->with('success', "Sukses melakukan rollback! Sebanyak {$deletedCount} usulan pada Batch {$batchId} berhasil dihapus dari sistem.");

        } catch (\Exception $e) {
            DB::rollBack();
            return redirect()->back()->withErrors(['error' => 'Gagal melakukan rollback: ' . $e->getMessage()]);
        }
    }
}