expense/app/Http/Controllers/Backend/BudgetControlController.php

<?php

declare(strict_types=1);

namespace App\Http\Controllers\Backend;

use App\Helpers\BudgetHelper;
use App\Http\Controllers\Controller;
use App\Models\BudgetControl;
use App\Models\BudgetControlLog;
use App\Models\Admin;
use Illuminate\Http\Request;
use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

use Maatwebsite\Excel\Facades\Excel;
use Maatwebsite\Excel\Concerns\FromArray;
use Maatwebsite\Excel\Concerns\WithHeadings;
use App\Helpers\PeriodeHelper;

class BudgetControlController extends Controller
{
    /**
     * Halaman Utama Budget Control (Termasuk Data Grafik)
     */
    public function index(Request $request)
{
    $this->checkAuthorization(auth()->user(), ['budget_control.view']);
    
    $user = auth()->user();
    $role = $user->getRoleNames()->first();

    // --- 1. IDENTIFIKASI WILAYAH USER (Sama seperti Dashboard) ---
    // Mengambil semua cabang yang di-assign ke user ini
    $userCabangIds = \App\Models\UserHasCabang::where('user_id', $user->id)
        ->pluck('cabang_id')
        ->toArray();

    // Mengambil semua ID Region dari cabang-cabang tersebut
    $userRegionIds = \App\Models\Cabang::whereIn('id', $userCabangIds)
        ->pluck('region_id')
        ->unique()
        ->toArray();

    // --- 2. KOLEKSI DROPDOWN FILTER BERDASARKAN ROLE ---
    if (in_array($role, ['Admin Region', 'Marketing Operational Manager Region'])) {
        // Dropdown Region HANYA muncul region miliknya (Menghilangkan Blank)
        $regions = \App\Models\Region::whereIn('id', $userRegionIds)->get();
        // Dropdown Cabang HANYA muncul cabang-cabang di bawah region tersebut
        $cabangOptions = \App\Models\Cabang::whereIn('region_id', $userRegionIds)->get();
        
    } elseif (in_array($role, ['Area Manager Cabang', 'Medical Representatif'])) {
        $regions = \App\Models\Region::whereIn('id', $userRegionIds)->get();
        $cabangOptions = \App\Models\Cabang::whereIn('id', $userCabangIds)->get();
        
    } else {
        // Superadmin & Accounting: Muncul Semua
        $regions = \App\Models\Region::all();
        $cabangOptions = \App\Models\Cabang::when($request->region_id, function($q) use ($request) {
            return $q->where('region_id', $request->region_id);
        })->get();
    }

    // --- 3. QUERY UTAMA DENGAN PROTEKSI DATA ---
    $query = \App\Models\BudgetControl::with(['receiver', 'sender', 'cabang.region']);

    // Jika bukan Superadmin/Accounting, kunci kueri ke wilayahnya saja
    if (!in_array($role, ['Superadmin', 'Super Admin', 'Accounting', 'accounting'])) {
        $query->whereHas('cabang', function($q) use ($userRegionIds) {
            $q->whereIn('region_id', $userRegionIds);
        });
    }

    // --- 4. FILTER DARI INPUT USER ---
    if ($request->filled('region_id')) {
        $query->whereHas('cabang', fn($q) => $q->where('region_id', $request->region_id));
    }
    if ($request->filled('cabang_id')) {
        $query->where('cabang_id', $request->cabang_id);
    }
    if ($request->filled('month')) {
        $query->where('periode_month', strtolower($request->month));
    }
    if ($request->filled('year')) {
        $query->where('periode_year', $request->year);
    }

    $data = $query->latest()->paginate(50)->withQueryString();

    // --- 5. OPTIMASI PERHITUNGAN REALISASI ---
    $expenseMap = \App\Helpers\BudgetHelper::getBulkAvailableBudget($data);

    $data->getCollection()->transform(function ($b) use ($expenseMap) {
        $key = $b->cabang_id . '_' . strtolower($b->periode_month) . '_' . $b->periode_year;
        $b->availableBudget = isset($expenseMap[$key]) ? ($b->amount - $expenseMap[$key]) : $b->amount;
        return $b;
    });

    return view('backend.pages.budget_control.index', compact('data', 'regions', 'cabangOptions'));
}
    /**
     * Menampilkan Log Riwayat Budget (Disempurnakan dengan Total, Realisasi, DR, CR)
     */
    /**
     * Menampilkan Log Riwayat Budget (Menampilkan Budget & Realisasi Cash BCA)
     */
   /**
     * Menampilkan Log Riwayat Budget dengan Integrasi Tabel Transaksi
     */
    public function log(Request $request)
{
    $this->checkAuthorization(auth()->user(), ['budget_control.view']);
    
    // Data untuk Dropdown Filter
    $regions = \App\Models\Region::all();
    $cabangOptions = \App\Models\Cabang::when($request->region_id, function($q) use ($request) {
        return $q->where('region_id', $request->region_id);
    })->get();

    // Query Utama dengan Filter Dinamis
    $query = \App\Models\BudgetControlLog::with(['receiver', 'sender', 'cabang.region']);

    if ($request->filled('region_id')) {
        $query->whereHas('cabang', fn($q) => $q->where('region_id', $request->region_id));
    }
    if ($request->filled('cabang_id')) {
        $query->where('cabang_id', $request->cabang_id);
    }
    if ($request->filled('month')) {
        $query->where('periode_month', strtolower($request->month));
    }
    if ($request->filled('year')) {
        $query->where('periode_year', $request->year);
    }

    $data = $query->orderBy('created_at', 'desc')->paginate(50)->withQueryString();

    // Sinkronisasi Kalkulasi Realisasi
    $lookups = $data->map(fn($l) => [
        'cabang_id' => $l->cabang_id,
        'month'     => $l->periode_month,
        'year'      => $l->periode_year
    ])->unique()->toArray();

    $debetMap = \App\Helpers\BudgetHelper::getBulkKalkulasiJurnal($lookups);

    $data->getCollection()->transform(function ($log) use ($debetMap) {
        $key = $log->cabang_id . '_' . strtolower($log->periode_month) . '_' . $log->periode_year;
        
        $log->total_budget = (float) $log->amount;
        $log->total_credit = (float) $log->amount; // Pagu/Transfer
        $log->total_debet  = (float) ($debetMap[$key] ?? 0); // Pengeluaran riil
        $log->realisasi    = $log->total_credit - $log->total_debet; // Sisa Saldo
        
        return $log;
    });

    // Menyiapkan Data Grafik (Top 10 hasil filter)
    $chartData = [
        'labels' => $data->getCollection()->take(10)->map(fn($l) => $l->cabang->name)->toArray(),
        'budget' => $data->getCollection()->take(10)->pluck('total_budget')->toArray(),
        'realisasi' => $data->getCollection()->take(10)->pluck('realisasi')->toArray(),
    ];

    return view('backend.pages.budget_control.log', compact('data', 'regions', 'cabangOptions', 'chartData'));
}

private function prepareLogChartData($collection)
{
    // Mengelompokkan data berdasarkan periode untuk grafik
    $grouped = $collection->groupBy(function($item) {
        return strtoupper($item->periode_month) . ' ' . $item->periode_year;
    })->take(6); // Ambil 6 periode terbaru yang ada di list

    return [
        'labels' => $grouped->keys()->toArray(),
        'budget' => $grouped->map(fn($group) => $group->sum('total_budget'))->values()->toArray(),
        'realisasi' => $grouped->map(fn($group) => $group->sum('realisasi'))->values()->toArray(),
    ];
}

    /**
     * Download Template untuk Bulk Upload
     */
    public function downloadTemplate()
    {
        $fileName = 'Template_Bulk_Upload_Budget.csv';
        
        $headers = [
            "Content-type"        => "text/csv",
            "Content-Disposition" => "attachment; filename=$fileName",
            "Pragma"              => "no-cache",
            "Cache-Control"       => "must-revalidate, post-check=0, pre-check=0",
            "Expires"             => "0"
        ];

        $columns = ['Kode Cabang', 'Bulan', 'Nominal Budget', 'Keterangan'];
        
        $callback = function() use($columns) {
            $file = fopen('php://output', 'w');
            
            // Baris 1: Header
            fputcsv($file, $columns);
            
            // Baris 2 & 3: Contoh data agar user paham
            fputcsv($file, ['JKT-01', 'January', '15000000', 'Budget Ops JKT-01']);
            fputcsv($file, ['BDG-01', 'January', '12500000', 'Budget Ops BDG-01']);
            
            fclose($file);
        };

        return response()->stream($callback, 200, $headers);
    }

    public static function generateRefNumber($cabang)
{
    // Mengambil 3 huruf kode cabang (Pastikan Anda menggunakan kolom yang tepat, misalnya $cabang->code atau $cabang->name)
    $kodeCabang = strtoupper(substr($cabang->code ?? $cabang->name, 0, 3)); 
    
    // Format ym (Contoh: 2605 untuk Mei 2026)
    $ym = date('ym'); 
    
    $prefix = "JRN-{$kodeCabang}-{$ym}";

    // Menghitung urutan transaksi untuk cabang dan bulan ini
    $count = \Illuminate\Support\Facades\DB::table('transactions')
                ->where('cabang_id', $cabang->id)
                ->whereRaw("DATE_FORMAT(created_at, '%y%m') = ?", [$ym])
                ->count();
                
    $sequence = str_pad((string)($count + 1), 3, '0', STR_PAD_LEFT);

    // Hasil akhir: JRN-PDG-2605-001
    return "{$prefix}-{$sequence}"; 
}
    /**
     * Form Create Budget
     */
    public function create()
    {
        $this->checkAuthorization(auth()->user(), ['budget_control.create']);

        $user = auth()->user();
        $role = $user->getRoleNames()[0];

        if (in_array($role, ['Admin Region', 'Marketing Operational Manager Region'])) {
            $region_id = $user->getMyCabangAndRegionId()['region'];
            $users = $region_id 
                ? Admin::where('id', '!=', $user->id)->whereHas('cabang', fn($q) => $q->where('region_id', $region_id))->get()
                : Admin::getUserByRoleName('Area Manager Cabang');
        } elseif ($role == 'Area Manager Cabang') {
            $cabang_id = $user->getMyCabangAndRegionId()['cabang'];
            $users = $cabang_id 
                ? Admin::where('id', '!=', $user->id)->whereHas('cabang', fn($q) => $q->where('id', $cabang_id))->get()
                : collect();
        } else {
            $users = Admin::getUserByRoleName('Area Manager Cabang');
        }

        return view('backend.pages.budget_control.create', ['users' => $users]);
    }
    /**
     * Menampilkan Halaman Filter Generate Report Jurnal
     */
    public function jurnal()
    {
        $user = auth()->user();
        $role = $user->getRoleNames()[0] ?? null;
        $userData = $user->getMyCabangAndRegionId();
        
        $regions = Region::all();
        $cabangs = Cabang::all();

        // Filter data berdasarkan hak akses (Role)
        if (in_array($role, ['Admin Region', 'Marketing Operational Manager Region'])) {
            $regions = Region::where('id', $userData['region'])->get();
            $cabangs = Cabang::where('region_id', $userData['region'])->get();
        } elseif ($role == 'Area Manager Cabang') {
            $cabangs = Cabang::where('id', $userData['cabang'])->get();
            $regions = Region::where('id', $cabangs->first()->region_id ?? 0)->get();
        }

        return view('backend.pages.report.jurnal', [
            'pageInfo' => ['title' => 'Generate Reports For Jurnal'],
            'cabang' => $cabangs,
            'regions' => $regions
        ]);
    }

    /**
     * Proses Menghitung Data Jurnal (Debet, Kredit, & Realisasi)
     */
    public function generateJurnal()
    {
        $params = request()->all();
        $accounting_period = $params['accounting_period'] ?? '-';
        
        // 1. Ambil kategori yang valid (Wajib punya Account Number & tidak di-exclude)
        $excluded = ['Cash in Bank BCA', 'Donasi/Gift', 'PPn Masukan', 'Vehicle Maintenance', 'Bahan Promosi', 'Sponsorship'];
        $kategoris = Kategori::whereNotIn('name', $excluded)
            ->whereNotNull('account_number')
            ->where('account_number', '!=', '')
            ->where('account_number', '!=', '0')
            ->get();

        $kategori_bca = Kategori::where('name', 'Cash in Bank BCA')->first();
        $cabang = Cabang::where('code', $params['cabang'])->firstOrFail();
        $year = $params['year'];
        $month = $params['month'];

        // 2. Ambil Range Tanggal Cut-off (Misal: 11 Maret - 13 April)
        [$startDate, $endDate] = PeriodeHelper::getPeriodeRange($month, $year);

        // 3. KREDIT: Total Petty Cash yang diterima cabang (Pagu)
        $pettyCashAmount = BudgetControl::where('cabang_id', $cabang->id)
            ->where('periode_year', $year)
            ->where('periode_month', $month)
            ->sum('amount');

        // Log budget untuk display di view
        $budget = BudgetControlLog::where('cabang_id', $cabang->id)
            ->where('periode_year', $year)
            ->where('periode_month', $month)
            ->orderByDesc('created_at')->first();

        $userIds = UserHasCabang::where('cabang_id', $cabang->id)->pluck('user_id');

        // Mapping Model & Alias
       $formModels = [
            'Up Country'           => FormUpCountry::class,
            'Vehicle Running Cost' => FormVehicleRunningCost::class,
            'Entertainment'        => FormEntertaimentPresentation::class,
            'Entertaiment'         => FormEntertaimentPresentation::class,
            'Presentation'         => FormEntertaimentPresentation::class,
            'Meeting / Seminar'    => FormMeetingSeminar::class,
        ];

        $nominals = [];
        foreach ($kategoris as $kategori) {
    $sum = 0;
    $katName = trim($kategori->name);

    if (isset($formModels[$katName])) {
        $model = $formModels[$katName];
        $sum = $model::whereIn('user_id', $userIds)
            ->where('expense_number', 'LIKE', "%-{$cabang->code}-%") // Filter Cabang
            ->where('kategori_id', $kategori->id) // Filter Kategori WAJIB
            ->whereIn('status', ['Closed', 'Final Approved'])
            ->whereBetween('final_approved_at', [$startDate, $endDate])
            ->sum('approved_total');
    } else {
        $sum = FormOthers::whereIn('user_id', $userIds)
            ->where('expense_number', 'LIKE', "%-{$cabang->code}-%") // Filter Cabang
            ->where('kategori_id', $kategori->id) // Filter Kategori WAJIB
            ->whereIn('status', ['Closed', 'Final Approved'])
            ->whereBetween('final_approved_at', [$startDate, $endDate])
            ->sum('approved_total');
    }
    $nominals[$kategori->id] = $sum;
}
        $totalDebet = array_sum($nominals); // Total Pengeluaran riil

        return view('backend.pages.report.generate_jurnal', [
            'pageInfo'          => ['title' => 'Generate Reports For Jurnal'],
            'kategori'          => $kategoris,
            'cabang'            => $cabang,
            'year'              => $year,
            'month'             => $month,
            'kategori_bca'      => $kategori_bca,
            'nominals'          => $nominals,
            'budget'            => $budget,
            'totalKategori'     => $totalDebet,          // Total Debet Jurnal
            'pettyCashAmount'   => $pettyCashAmount,    // Total Kredit Jurnal
            'cashInBca'         => $pettyCashAmount - $totalDebet, // Sisa Uang BCA
            'accounting_period' => $accounting_period,
        ]);
    }

    /**
     * Simpan Budget Baru & Log
     */
    public function store(Request $request)
    {
        $this->checkAuthorization(auth()->user(), ['budget_control.create']);
        $validated = $request->validate([
            'receiver_id' => 'required|exists:admins,id',
            'periode_month' => 'required|string',
            'periode_year' => 'required|numeric',
            'amount' => 'required|numeric',
            'remarks' => 'nullable|string',
        ]);

        return DB::transaction(function () use ($validated) {
            $receiver = Admin::find($validated['receiver_id']);
            $cabang_id = $receiver->getMyCabangAndRegionId()['cabang'];
            
            $existing = BudgetControl::where('cabang_id', $cabang_id)
                ->where('periode_month', $validated['periode_month'])
                ->where('periode_year', $validated['periode_year'])
                ->first();

            if ($existing && strtolower($existing->status) == 'assigned') {
                return back()->with('error', 'Gagal: Budget periode ini sudah Assigned.');
            }
            
            $budgetData = [
                'sender_id' => auth()->user()->id,
                'receiver_id' => $validated['receiver_id'],
                'amount' => $validated['amount'],
                'remarks' => $validated['remarks'],
                'status' => 'Assigned',
            ];

            BudgetControl::updateOrCreate(
                ['cabang_id' => $cabang_id, 'periode_month' => $validated['periode_month'], 'periode_year' => $validated['periode_year']],
                $budgetData
            );

            BudgetControlLog::create(array_merge(['cabang_id' => $cabang_id], $validated, $budgetData));

            return redirect()->route('budget_control')->with('success', 'Budget Assigned successfully');
        });
    }

    /**
     * Proses Upload Bukti Transfer (Disempurnakan untuk dukung PDF & Gambar)
     */
    public function uploadProof(Request $request)
    {
        // PERBAIKAN: Ganti 'image' menjadi 'file', karena aturan 'image' akan menolak PDF
        $request->validate([
            'budget_id' => 'required|exists:budget_control,id',
            'transfer_proof' => 'required|file|mimes:jpeg,png,jpg,pdf|max:5120', // Maks 5MB
        ], [
            'transfer_proof.mimes' => 'Format file harus berupa JPG, JPEG, PNG, atau PDF.',
            'transfer_proof.max' => 'Ukuran file maksimal 5MB.'
        ]);

        $budget = BudgetControl::find($request->budget_id);
        
        if ($request->hasFile('transfer_proof')) {
            $file = $request->file('transfer_proof');
            $filename = time() . '_' . str_replace(' ', '_', $file->getClientOriginalName());
            
            // Simpan file
            $file->move(public_path('uploads/transfer_proofs'), $filename);
            
            $budget->update([
                'transfer_proof' => 'uploads/transfer_proofs/' . $filename,
                'transfer_status' => 'Waiting Approval'
            ]);

            return back()->with('success', 'Bukti transfer berhasil diunggah.');
        }

        return back()->with('error', 'File gagal diunggah.');
    }

    /**
     * Konfirmasi Accounting (Validated)
     */
    public function approveTransfer($id)
    {
        BudgetControl::where('id', $id)->update([
            'transfer_status' => 'Validated',
            'updated_at' => now()
        ]);
        return back()->with('success', 'Transfer divalidasi.');
    }

    /**
     * Penolakan Accounting (Rejected)
     */
    public function rejectTransfer($id)
    {
        BudgetControl::where('id', $id)->update([
            'transfer_status' => 'Rejected',
            'updated_at' => now()
        ]);
        return back()->with('error', 'Transfer ditolak.');
    }

    /**
     * Proses Bulk Upload Budget via Excel/CSV
     */
    public function bulkUpload(Request $request)
    {
        $this->checkAuthorization(auth()->user(), ['budget_control.create']);

        // Tambahkan 'csv' pada mimes
        $request->validate([
            'upload_type'  => 'required|in:region,cabang',
            'periode_year' => 'required|numeric',
            'file_excel'   => 'required|file|mimes:xlsx,xls,csv|max:5120', 
        ], [
            'file_excel.mimes' => 'Format file wajib berupa Microsoft Excel (.xls / .xlsx) atau CSV.'
        ]);

        // 1. Tentukan Cabang ID mana saja yang diizinkan untuk di-upload pada proses ini
        $cabangIds = [];
        if ($request->upload_type === 'region') {
            $request->validate(['region_id' => 'required|exists:region,id']);
            $cabangIds = \App\Models\Cabang::where('region_id', $request->region_id)->pluck('id')->toArray();
        } else {
            $request->validate(['cabang_id' => 'required|array', 'cabang_id.*' => 'exists:cabang,id']);
            $cabangIds = $request->cabang_id;
        }

        try {
            // 2. Baca file Excel menggunakan Maatwebsite (otomatis jadi Array)
            // Parameter pertama new \stdClass() digunakan sebagai dummy import class
            $data = Excel::toArray(new \stdClass(), $request->file('file_excel'));
            $rows = $data[0] ?? [];

            DB::beginTransaction();
            $count = 0;

            // 3. Looping data Excel
            foreach ($rows as $index => $row) {
                // Skip baris pertama (Asumsi baris 1 adalah Header)
                if ($index === 0) continue;

                $cabangCode = $row[0] ?? null;
                $month      = $row[1] ?? null;
                $amount     = (float) ($row[2] ?? 0);
                $remarks    = $row[3] ?? null;

                if (!$cabangCode || !$month || $amount <= 0) continue;

                // Cari cabang berdasarkan Kode Cabang di Excel
                $cabang = \App\Models\Cabang::where('code', $cabangCode)->first();
                
                // Validasi Keamanan: Pastikan cabang ditemukan DAN termasuk dalam scope filter modal
                if (!$cabang || !in_array($cabang->id, $cabangIds)) continue;

                $budgetData = [
                    'sender_id'   => auth()->user()->id,
                    'receiver_id' => $cabang->head_id ?? auth()->user()->id, // Assign ke Head Cabang
                    'amount'      => $amount,
                    'remarks'     => $remarks,
                    'status'      => 'Assigned',
                ];

                // Simpan atau Update Budget
                BudgetControl::updateOrCreate(
                    [
                        'cabang_id'     => $cabang->id, 
                        'periode_month' => $month, 
                        'periode_year'  => $request->periode_year
                    ],
                    $budgetData
                );

                // Catat di Log
                BudgetControlLog::create(array_merge([
                    'cabang_id'     => $cabang->id, 
                    'periode_month' => $month, 
                    'periode_year'  => $request->periode_year
                ], $budgetData));

                $count++;
            }

            DB::commit();
            return back()->with('success', "Bulk upload berhasil! Sebanyak {$count} data budget telah diproses.");

        } catch (\Exception $e) {
            DB::rollBack();
            \Illuminate\Support\Facades\Log::error("Bulk Upload Error: " . $e->getMessage());
            return back()->with('error', 'Gagal memproses file Excel. Pastikan format tabel sudah sesuai.');
        }
    }

    /**
     * Hapus Data Budget
     */
    public function destroy($id)
    {
        $this->checkAuthorization(auth()->user(), ['budget_control.delete']);
        BudgetControl::findOrFail($id)->delete();
        return redirect()->route('budget_control')->with('success', 'Deleted successfully');
    }

    /**
     * PRIVATE METHOD: Logika Grafik Trend Petty Cash vs Realisasi
     */
    private function getTrendChartData(array $cabangIds): array
    {
        $currentYear = \Carbon\Carbon::now()->year;
        $months = ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December'];
        
        $pettyCashData = [];
        $realisasiData = [];

        foreach ($months as $month) {
            $pettyCash = \App\Models\BudgetControl::whereIn('cabang_id', $cabangIds)
                ->where('periode_month', strtolower($month))
                ->where('periode_year', $currentYear)
                ->sum('amount');
                
            // Realisasi Sementara Di-set 0
            $realisasi = 0; 

            $pettyCashData[] = (float) $pettyCash;
            $realisasiData[] = (float) $realisasi;
        }

        return [
            'labels' => $months,
            'datasets' => [
                [
                    'label' => 'Petty Cash (Budget)',
                    'data' => $pettyCashData,
                    'backgroundColor' => 'rgba(54, 162, 235, 0.6)',
                    'borderColor' => 'rgba(54, 162, 235, 1)',
                ],
                [
                    'label' => 'Realisasi',
                    'data' => $realisasiData,
                    'backgroundColor' => 'rgba(255, 99, 132, 0.6)',
                    'borderColor' => 'rgba(255, 99, 132, 1)',
                ]
            ]
        ];
    }
}