<?php

namespace App\Http\Controllers;

use App\Models\Dapur;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Font;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use App\Models\MasterData;
use App\Models\HargaPasar;
use App\Models\Suplier;
use App\Models\HargaReal;
use App\Models\BankMenu;
use App\Models\PurchaseOrder;
use App\Models\BarangOperasional;
use App\Models\User;
use Illuminate\Http\Request;

class DashboardController extends Controller
{
    /**
     * Entry point SPA — mengirim semua data yang diperlukan ke frontend
     */
    public function index(Request $request)
    {
        $user = $request->user();
        $dapurList = Dapur::where('status', 'Aktif')->get();

        return view('dashboard', [
            'user' => $user,
            'dapurList' => $dapurList,
        ]);
    }

    /**
     * Batched initial data — menggabungkan 8+ API calls menjadi 1 request.
     * Mengurangi HTTP round-trips dan mempercepat initial load secara drastis.
     */
    public function initialData(Request $request)
    {
        $user = $request->user();
        $dapurId = $request->query('dapur_id', $user->dapur_id);
        $role = $request->query('role', $user->role);

        $filterDapur = function ($query) use ($dapurId) {
            if ($dapurId && $dapurId !== 'ALL') {
                $query->where('dapur_id', $dapurId);
            }
        };

        // === Master Data (global, no dapur filter) ===
        $masterData = MasterData::select(['id_bahan','nama_bahan','bdd','energi','protein','lemak','karbohidrat','serat','abu','air','kalsium','fosfor','besi','natrium','kalium','tembaga','seng','retinol','b_karoten','karoten_total','thiamin','riboflavin','niasin','vit_c'])
            ->orderBy('nama_bahan')->get()->map(fn($i) => [
                'ID_Bahan' => $i->id_bahan, 'Nama_Bahan' => $i->nama_bahan, 'BDD' => $i->bdd,
                'Energi' => $i->energi, 'Protein' => $i->protein, 'Lemak' => $i->lemak,
                'Karbohidrat' => $i->karbohidrat, 'Serat' => $i->serat, 'Abu' => $i->abu,
                'Air' => $i->air, 'Kalsium' => $i->kalsium, 'Fosfor' => $i->fosfor,
                'Besi' => $i->besi, 'Natrium' => $i->natrium, 'Kalium' => $i->kalium,
                'Tembaga' => $i->tembaga, 'Seng' => $i->seng, 'Retinol' => $i->retinol,
                'B_Karoten' => $i->b_karoten, 'Karoten_Total' => $i->karoten_total,
                'Thiamin' => $i->thiamin, 'Riboflavin' => $i->riboflavin,
                'Niasin' => $i->niasin, 'Vit_C' => $i->vit_c,
            ]);

        // === Harga Pasar ===
        $hargaPasar = HargaPasar::when($dapurId !== 'ALL', fn($q) => $q->where('dapur_id', $dapurId))
            ->select(['id_harga','dapur_id','nama_pasar','id_bahan_master','satuan','harga','last_update','hitung_gizi','kategori'])
            ->orderBy('nama_pasar')->get()->map(fn($i) => [
                'ID_Harga' => $i->id_harga, 'Dapur_ID' => $i->dapur_id,
                'Nama_Pasar' => $i->nama_pasar, 'ID_Bahan_Master' => $i->id_bahan_master,
                'Satuan' => $i->satuan, 'Harga' => $i->harga,
                'Hitung_Gizi' => $i->hitung_gizi, 'Kategori' => $i->kategori,
                'Last_Update' => $i->last_update?->toISOString(),
            ]);

        // === Suplier ===
        $suplier = Suplier::when($dapurId !== 'ALL', fn($q) => $q->where('dapur_id', $dapurId))
            ->orderBy('nama_suplier')->get()->map(fn($i) => [
                'ID_Suplier' => $i->id_suplier, 'Dapur_ID' => $i->dapur_id,
                'Nama_Suplier' => $i->nama_suplier, 'PIC_Suplier' => $i->pic_suplier,
                'Kontak' => $i->kontak, 'Alamat' => $i->alamat,
                'Status' => $i->status, 'Last_Update' => $i->last_update,
            ]);

        // === Harga Real ===
        $hargaReal = HargaReal::when($dapurId !== 'ALL', fn($q) => $q->where('dapur_id', $dapurId))
            ->orderBy('nama_real')->get()->map(fn($i) => [
                'ID_Harga_Real' => $i->id_harga_real, 'Dapur_ID' => $i->dapur_id,
                'ID_Bahan_Master' => $i->id_bahan_master, 'Nama_Real' => $i->nama_real,
                'Harga_Nota' => $i->harga_nota, 'Satuan' => $i->satuan,
                'ID_Suplier' => $i->id_suplier, 'Last_Update' => $i->last_update,
            ]);

        // === Bank Menu ===
        $bankMenu = BankMenu::when($dapurId !== 'ALL', fn($q) => $q->where('dapur_id', $dapurId))
            ->orderBy('nama_menu')->get()->map(fn($i) => [
                'ID_Menu' => $i->id_menu, 'Dapur_ID' => $i->dapur_id,
                'Nama_Menu' => $i->nama_menu, 'Data_JSON' => $i->data_json,
            ]);

        // === Purchase Orders ===
        $po = PurchaseOrder::when($dapurId !== 'ALL', fn($q) => $q->where('dapur_id', $dapurId))
            ->orderByDesc('tanggal_po')->get()->map(fn($i) => [
                'ID_PO' => $i->id_po, 'Dapur_ID' => $i->dapur_id,
                'Tanggal_PO' => $i->tanggal_po, 'ID_Suplier' => $i->id_suplier,
                'ID_Menu' => $i->id_menu, 'Total_Biaya' => $i->total_biaya,
                'Status' => $i->status, 'Detail_Item' => $i->detail_item,
                'Parent_ID' => $i->parent_id, 'Tipe_PO' => $i->tipe_po,
                'Kategori_Susulan' => $i->kategori_susulan, 'Alasan_Susulan' => $i->alasan_susulan,
            ]);

        // === Barang Operasional ===
        $operasional = BarangOperasional::when($dapurId !== 'ALL', fn($q) => $q->where('dapur_id', $dapurId))
            ->orderByDesc('tanggal')->get()->map(fn($i) => [
                'ID_Operasional' => $i->id_operasional, 'Dapur_ID' => $i->dapur_id,
                'Nama_Barang' => $i->nama_barang, 'Jumlah' => $i->jumlah,
                'Satuan' => $i->satuan, 'Harga_Satuan' => $i->harga_satuan,
                'Total' => $i->total, 'Tanggal' => $i->tanggal,
                'Keterangan' => $i->keterangan,
            ]);

        // === Response ===
        $response = [
            'success' => true,
            'master_data' => $masterData,
            'harga_pasar' => $hargaPasar,
            'suplier' => $suplier,
            'harga_real' => $hargaReal,
            'bank_menu' => $bankMenu,
            'po' => $po,
            'operasional' => $operasional,
        ];

        // Users only for Super Admin
        if ($role === 'Super Admin') {
            $response['users'] = User::all()->map(fn($u) => [
                'ID' => $u->id, 'Username' => $u->username, 'Nama' => $u->nama,
                'Role' => $u->role, 'Dapur_ID' => $u->dapur_id,
                'Yayasan' => $u->yayasan,
                'Logo_Yayasan' => $u->logo_yayasan ? '/storage/' . $u->logo_yayasan : null,
            ]);
        }

        return response()->json($response);
    }

    /**
     * Get dapur settings (jabatan list, etc.)
     */
    public function getDapurSettings(Request $request)
    {
        $dapurId = auth()->user()->dapur_id;
        $dapur = Dapur::find($dapurId);
        $settings = $dapur ? ($dapur->settings ?? []) : [];
        return response()->json(['success' => true, 'settings' => $settings]);
    }

    /**
     * Update dapur settings
     */
    public function updateDapurSettings(Request $request)
    {
        $dapurId = auth()->user()->dapur_id;
        $dapur = Dapur::find($dapurId);
        if (!$dapur) {
            return response()->json(['success' => false, 'message' => 'Dapur tidak ditemukan'], 404);
        }

        $key = $request->input('key');
        $value = $request->input('value');

        $settings = $dapur->settings ?? [];
        $settings[$key] = $value;
        $dapur->settings = $settings;
        $dapur->save();

        return response()->json(['success' => true, 'settings' => $settings]);
    }

    /**
     * Download Excel Template
     */
    public function downloadTemplate($type)
    {
        $validTypes = ['siswa', 'tendik', 'balita', 'ibuhamil', 'ibumenyusui', 'kader'];
        if (!in_array($type, $validTypes)) {
            return response("<div style='font-family:sans-serif; text-align:center; margin-top:50px;'><h2>Error 404</h2><p>Template tidak valid.</p></div>", 404);
        }

        // Auto-generate template untuk penerima manfaat (siswa/tendik)
        if ($type === 'siswa' || $type === 'tendik') {
            return $this->generatePenerimaTemplate();
        }

        // Tipe lain: gunakan file yang sudah diupload
        $path = storage_path('app/public/templates/template_' . $type . '.xlsx');
        if (!file_exists($path)) {
            return response("<div style='font-family:sans-serif; text-align:center; margin-top:50px;'><h2>Template Belum Tersedia</h2><p>File template Excel untuk <b>" . strtoupper($type) . "</b> belum diunggah oleh Super Admin.</p><p>Harap minta Super Admin untuk mengunggahnya terlebih dahulu melalui tombol Upload Template.</p></div>", 404);
        }

        return response()->download($path, 'Template_Import_' . ucfirst($type) . '.xlsx');
    }

    /**
     * Generate template Excel .xlsx untuk import penerima manfaat
     * Menggunakan PhpSpreadsheet untuk format .xlsx yang proper
     */
    private function generatePenerimaTemplate()
    {
        $spreadsheet = new Spreadsheet();
        
        // =============================================
        // SHEET 1: Template Import
        // =============================================
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Template Import');

        // Kolom header (Baris 1) - Nama kolom
        $headers = [
            'A' => 'NISN/NIK',
            'B' => 'Nama Penerima',
            'C' => 'Tanggal Lahir',
            'D' => 'Jenis Kelamin',
            'E' => 'Nama Orang Tua/Wali',
            'F' => 'Posisi',
            'G' => 'Status',
            'H' => 'Tinggi Badan',
            'I' => 'Berat Badan',
            'J' => 'Kelas',
            'K' => 'Alergi',
            'L' => 'No HP',
        ];

        // Deskripsi format (Baris 2) - Keterangan isian
        $descriptions = [
            'A' => 'NISN (10 digit) untuk siswa / NIK (16 digit) untuk umum — maks 20 karakter',
            'B' => 'Nama lengkap penerima',
            'C' => 'Format: DD-MM-YYYY (contoh: 15-03-2015)',
            'D' => 'Isi KODE: 392 = Laki-laki, 64 = Perempuan',
            'E' => 'Nama orang tua/wali (wajib untuk siswa)',
            'F' => 'Isi KODE: 1 = Siswa, 2 = Guru/Kepala Sekolah, 3 = Staf Lainnya',
            'G' => 'Isi: true = Aktif, false = Nonaktif',
            'H' => 'Dalam cm (kosongkan untuk guru/staf)',
            'I' => 'Dalam kg (kosongkan untuk guru/staf)',
            'J' => 'Contoh: 1A, 2B (kosongkan untuk guru/staf)',
            'K' => 'Alergi makanan, isi - jika tidak ada',
            'L' => 'Nomor HP (terutama untuk guru/staf)',
        ];

        // Set header values (Row 1)
        foreach ($headers as $col => $value) {
            $sheet->setCellValue($col . '1', $value);
        }

        // Set description values (Row 2)
        foreach ($descriptions as $col => $value) {
            $sheet->setCellValue($col . '2', $value);
        }

        // ---- STYLING ----
        $lastCol = 'L';
        $headerRange = "A1:{$lastCol}1";
        $descRange = "A2:{$lastCol}2";

        // Style Baris 1: Header (Biru gelap, teks putih, bold)
        $sheet->getStyle($headerRange)->applyFromArray([
            'font' => [
                'bold' => true,
                'color' => ['rgb' => 'FFFFFF'],
                'size' => 11,
                'name' => 'Calibri',
            ],
            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'startColor' => ['rgb' => '1F4E79'],
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER,
                'wrapText' => true,
            ],
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['rgb' => '1A3A5C'],
                ],
            ],
        ]);

        // Style Baris 2: Deskripsi (Biru muda, teks biru tua, italic)
        $sheet->getStyle($descRange)->applyFromArray([
            'font' => [
                'italic' => true,
                'color' => ['rgb' => '1F4E79'],
                'size' => 9,
                'name' => 'Calibri',
            ],
            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'startColor' => ['rgb' => 'BDD7EE'],
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_LEFT,
                'vertical' => Alignment::VERTICAL_CENTER,
                'wrapText' => true,
            ],
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['rgb' => '9DC3E6'],
                ],
            ],
        ]);

        // Row heights
        $sheet->getRowDimension(1)->setRowHeight(30);
        $sheet->getRowDimension(2)->setRowHeight(40);

        // Column widths
        $colWidths = [
            'A' => 22, 'B' => 28, 'C' => 18, 'D' => 16,
            'E' => 25, 'F' => 20, 'G' => 12, 'H' => 14,
            'I' => 14, 'J' => 10, 'K' => 16, 'L' => 18,
        ];
        foreach ($colWidths as $col => $width) {
            $sheet->getColumnDimension($col)->setWidth($width);
        }

        // Add thin borders for 50 empty input rows (rows 3-52)
        $dataRange = "A3:{$lastCol}52";
        $sheet->getStyle($dataRange)->applyFromArray([
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_HAIR,
                    'color' => ['rgb' => 'D9D9D9'],
                ],
            ],
        ]);

        // Freeze panes - freeze top 2 rows so headers stay visible
        $sheet->freezePane('A3');

        // =============================================
        // SHEET 2: Panduan
        // =============================================
        $panduan = $spreadsheet->createSheet();
        $panduan->setTitle('Panduan');

        $panduanData = [
            ['KOLOM', 'KETERANGAN', 'CONTOH'],
            ['NISN/NIK', 'NISN (10 digit) untuk siswa / NIK (16 digit) untuk umum — maks 20 karakter', '0012345678'],
            ['Nama Penerima', 'Nama lengkap penerima manfaat', 'Ahmad Fauzi'],
            ['Tanggal Lahir', 'Format: DD-MM-YYYY', '15-03-2015'],
            ['Jenis Kelamin', 'Isi KODE angka: 392 = Laki-laki, 64 = Perempuan', '392'],
            ['Nama Orang Tua/Wali', 'Nama orang tua / wali (wajib untuk siswa, kosongkan untuk guru/staf)', 'Budi Santoso'],
            ['Posisi', 'Isi KODE angka: 1 = Siswa, 2 = Guru/Kepala Sekolah, 3 = Staf Lainnya', '1'],
            ['Status', 'Isi: true = Aktif, false = Nonaktif', 'true'],
            ['Tinggi Badan', 'Dalam cm (hanya untuk siswa, kosongkan untuk guru/staf)', '120'],
            ['Berat Badan', 'Dalam kg (hanya untuk siswa, kosongkan untuk guru/staf)', '25'],
            ['Kelas', 'Contoh: 1A, 2B, 3C (hanya untuk siswa, kosongkan untuk guru/staf)', '3A'],
            ['Alergi', 'Alergi makanan jika ada, isi - jika tidak ada', '-'],
            ['No HP', 'Nomor HP (terutama untuk guru/staf)', '081234567890'],
        ];

        $colLetters = ['A', 'B', 'C'];
        foreach ($panduanData as $rowIdx => $row) {
            foreach ($row as $colIdx => $value) {
                $panduan->setCellValue($colLetters[$colIdx] . ($rowIdx + 1), $value);
            }
        }

        // Style panduan header
        $panduan->getStyle('A1:C1')->applyFromArray([
            'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF'], 'size' => 11],
            'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => '1F4E79']],
            'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
        ]);

        // Style panduan data rows
        $panduan->getStyle('A2:C' . count($panduanData))->applyFromArray([
            'font' => ['size' => 10],
            'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => 'F2F7FB']],
            'borders' => [
                'allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => 'BDD7EE']],
            ],
            'alignment' => ['wrapText' => true, 'vertical' => Alignment::VERTICAL_CENTER],
        ]);

        // Panduan column widths
        $panduan->getColumnDimension('A')->setWidth(22);
        $panduan->getColumnDimension('B')->setWidth(60);
        $panduan->getColumnDimension('C')->setWidth(20);

        // Set active sheet back to first
        $spreadsheet->setActiveSheetIndex(0);

        // =============================================
        // OUTPUT
        // =============================================
        $filename = 'Template_Import_Penerima_Manfaat.xlsx';
        $temp = tempnam(sys_get_temp_dir(), 'xlsx');

        $writer = new Xlsx($spreadsheet);
        $writer->save($temp);

        return response()->download($temp, $filename, [
            'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        ])->deleteFileAfterSend(true);
    }

    /**
     * Upload Excel Template (Super Admin only)
     */
    public function uploadTemplate(Request $request, $type)
    {
        $validTypes = ['siswa', 'tendik', 'balita', 'ibuhamil', 'ibumenyusui', 'kader'];
        if (!in_array($type, $validTypes)) {
            return response()->json(['success' => false, 'message' => 'Tipe template tidak valid'], 400);
        }

        $request->validate([
            'file' => 'required|file|mimes:xlsx,xls|max:5120',
        ]);

        $file = $request->file('file');
        $fileName = 'template_' . $type . '.xlsx';
        
        // Ensure directory exists
        if (!file_exists(storage_path('app/public/templates'))) {
            mkdir(storage_path('app/public/templates'), 0755, true);
        }

        // Force store di disk public (storage/app/public/templates)
        $result = $file->storeAs('templates', $fileName, 'public');
        if (!$result) {
            return response()->json(['success' => false, 'message' => 'Gagal menyimpan file ke server lokal (Permission Error).'], 500);
        }

        return response()->json(['success' => true, 'message' => 'Template berhasil diunggah']);
    }
}

