expense/app/Helpers/BudgetHelper.php

<?php

namespace App\Helpers;

use App\Models\Kategori;
use App\Models\BudgetControl;
use App\Models\FormUpCountry;
use App\Models\FormMeetingSeminar;
use App\Models\FormOthers;
use App\Models\FormEntertaimentPresentation;
use App\Models\FormVehicleRunningCost;
use App\Models\UserHasCabang;
use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

class BudgetHelper
{
    /**
     * Cache memori sementara untuk fungsi jurnal agar loading halaman riwayat tidak berat.
     */
    private static $jurnalCache = [];

    /**
     * Daftar status yang akan memotong (menge-hold) sisa budget secara real-time.
     */
    const HOLD_STATUSES = [
        'Approved 1', 
        'Approved 2', 
        'Waiting Approval', 
        'Validated', 
        'Approved', 
        'Closed', 
        'Completed'
    ];

    public static function getBulkAvailableBudget($budgetControls)
    {
        if ($budgetControls->isEmpty()) return [];

        $cabangIds = $budgetControls->pluck('cabang_id')->unique()->toArray();
        
        // 1. Mapping User per Cabang
        $branchUsers = UserHasCabang::whereIn('cabang_id', $cabangIds)
            ->get()
            ->groupBy('cabang_id')
            ->map(fn($item) => $item->pluck('user_id')->toArray());

        $allUserIds = $branchUsers->flatten()->unique()->toArray();
        $resultsMap = [];

        if (empty($allUserIds)) return [];

        $formModels = [
            'tanggal' => [
                FormUpCountry::class,
                FormVehicleRunningCost::class,
                FormEntertaimentPresentation::class,
                FormOthers::class
            ],
            'created_at' => [
                FormMeetingSeminar::class
            ]
        ];

        // 2. Tarik data pengeluaran sekaligus dari semua tabel form
        foreach ($formModels as $dateColumn => $models) {
            foreach ($models as $model) {
                $query = $model::whereIn('user_id', $allUserIds)
                    ->whereIn('status', self::HOLD_STATUSES)
                    ->select(
                        'user_id',
                        DB::raw("MONTH($dateColumn) as month"),
                        DB::raw("YEAR($dateColumn) as year"),
                        DB::raw("SUM(approved_total) as total")
                    )
                    ->groupBy('user_id', 'month', 'year')
                    ->get();

                // 3. Masukkan ke Map berdasarkan Cabang_Bulan_Tahun
                foreach ($query as $row) {
                    foreach ($branchUsers as $cabangId => $userIds) {
                        if (in_array($row->user_id, $userIds)) {
                            $monthName = strtolower(date('F', mktime(0, 0, 0, $row->month, 10)));
                            $key = "{$cabangId}_{$monthName}_{$row->year}";
                            
                            $resultsMap[$key] = ($resultsMap[$key] ?? 0) + $row->total;
                        }
                    }
                }
            }
        }

        return $resultsMap;
    }

    /**
     * Menghitung sisa budget (Pagu - Total Hold/Terpakai).
     * Tetap menggunakan kolom tanggal/created_at karena pengajuan yang baru 
     * dibuat (Approved 1) belum memiliki final_approved_at.
     */
    public static function getAvailableBudget($cabang_id, $periode_month, $periode_year)
    {
        $periode_month = strtolower($periode_month);
        $periode_year = (int) $periode_year;

        $totalPagu = BudgetControl::where('cabang_id', $cabang_id)
            ->where('periode_month', $periode_month)
            ->where('periode_year', $periode_year)
            ->sum('amount');

        if ($totalPagu <= 0) return 0;

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

        if (empty($userIds)) return $totalPagu;

        $usedBudget = 0;
        $monthNumeric = date('m', strtotime($periode_month));

        $formModels = [
            FormUpCountry::class,
            FormVehicleRunningCost::class,
            FormEntertaimentPresentation::class,
            FormMeetingSeminar::class,
            FormOthers::class
        ];

        foreach ($formModels as $model) {
            $query = $model::whereIn('user_id', $userIds)
                ->whereIn('status', self::HOLD_STATUSES);

            if ($model === FormMeetingSeminar::class) {
                $query->whereMonth('created_at', $monthNumeric)
                      ->whereYear('created_at', $periode_year);
            } else {
                $query->whereMonth('tanggal', $monthNumeric)
                      ->whereYear('tanggal', $periode_year);
            }

            $usedBudget += $query->sum('approved_total');
        }

        return $totalPagu - $usedBudget;
    }

   /**
     * Memvalidasi apakah tanggal expense yang diinput user berada dalam 
     * rentang periode aktif (Tanggal 11 s/d 10).
     */
    public static function checkExpenseInputWindow($tanggal_expense)
    {
        $inputDate = \Carbon\Carbon::parse($tanggal_expense)->startOfDay();
        $today = \Carbon\Carbon::now();
        
        $startDay = 11;
        $endDay = 10;
        
        // Menentukan jendela periode yang sedang aktif hari ini
        if ($today->day >= $startDay) {
            // Jika hari ini tgl 11 atau lebih: Jendela aktif adalah Tgl 11 bulan ini s/d Tgl 10 bulan depan
            $periodStart = \Carbon\Carbon::create($today->year, $today->month, $startDay)->startOfDay();
            $periodEnd = \Carbon\Carbon::create($today->year, $today->month, $endDay)->addMonth()->endOfDay();
        } else {
            // Jika hari ini tgl 1-10: Jendela aktif adalah Tgl 11 bulan lalu s/d Tgl 10 bulan ini
            $periodStart = \Carbon\Carbon::create($today->year, $today->month, $startDay)->subMonth()->startOfDay();
            $periodEnd = \Carbon\Carbon::create($today->year, $today->month, $endDay)->endOfDay();
        }

        // Jika tanggal form yang diinput user berada di luar jendela aktif
        if ($inputDate->lt($periodStart) || $inputDate->gt($periodEnd)) {
            return [
                'is_valid' => false,
                'message'  => "Gagal! Batas input expense untuk periode aktif saat ini adalah {$periodStart->format('d M Y')} s/d {$periodEnd->format('d M Y')}. Tanggal {$inputDate->format('d M Y')} sudah ditutup atau belum dibuka."
            ];
        }

        return ['is_valid' => true];
    }
    
    /**
     * Mengambil nominal per tipe form. 
     */
    public static function getNominalByFormType($userIds, $type, $type_id, $month, $year)
    {
        $monthNumeric = date('m', strtotime($month));
        $used = 0;

        if ($type == 'Up Country') {
            $used = FormUpCountry::whereIn('user_id', $userIds)
                ->whereMonth('tanggal', $monthNumeric)->whereYear('tanggal', $year)
                ->whereIn('status', self::HOLD_STATUSES)->sum('approved_total');

        } elseif ($type == 'Vehicle Running Cost') {
            $used = FormVehicleRunningCost::whereIn('user_id', $userIds)
                ->whereMonth('tanggal', $monthNumeric)->whereYear('tanggal', $year)
                ->whereIn('status', self::HOLD_STATUSES)->sum('approved_total');

        } elseif ($type == 'Entertainment') {
            $used = FormEntertaimentPresentation::whereIn('user_id', $userIds)
                ->whereMonth('tanggal', $monthNumeric)->whereYear('tanggal', $year)
                ->whereIn('status', self::HOLD_STATUSES)->sum('approved_total');

        } elseif ($type == 'Meeting / Seminar') {
            $used = FormMeetingSeminar::whereIn('user_id', $userIds)
                ->whereMonth('created_at', $monthNumeric)->whereYear('created_at', $year)
                ->whereIn('status', self::HOLD_STATUSES)->sum('approved_total');

        } else {
            $used = FormOthers::whereIn('user_id', $userIds)
                ->where('kategori_id', $type_id)
                ->whereMonth('tanggal', $monthNumeric)->whereYear('tanggal', $year)
                ->whereIn('status', self::HOLD_STATUSES)->sum('approved_total');
        }

        return $used;
    }

    /**
     * Mengambil tanggal closing.
     */
    public static function getClosingDate($cabang_id)
    {
        return BudgetControl::where('cabang_id', $cabang_id)
            ->orderBy('created_at', 'desc')
            ->value('closing_at');
    }

    /**
     * KALKULASI JURNAL AKUNTANSI 
     * Berlaku seragam menggunakan kolom final_approved_at untuk semua form
     * Cut-off Date: Tgl 11 s/d Tgl 13 Bulan Berikutnya
     */
    /**
     * KALKULASI JURNAL AKUNTANSI (Log & Report)
     * Sinkron dengan 12 Kategori Utama & Akun 800xxx
     */
    // public static function getKalkulasiJurnal($cabang_id, $periode_month, $periode_year)
    // {
    //     $periode_month = strtolower($periode_month);
    //     $periode_year = (int) $periode_year;
        
    //     $cacheKey = "{$cabang_id}_{$periode_month}_{$periode_year}";
    //     if (isset(self::$jurnalCache[$cacheKey])) return self::$jurnalCache[$cacheKey];

    //     $userIds = UserHasCabang::where('cabang_id', $cabang_id)->pluck('user_id')->toArray();
    //     $totalDebet = 0; 

    //     if (!empty($userIds)) {
    //         // Ambil Range Tanggal (Contoh: 11 Maret - 13 April)
    //         $startDay = env('STARTING_DATE', 11);
    //         $closeDay = env('CLOSING_DATE', 13);
    //         $monthNumeric = date('m', strtotime($periode_month));

    //         $startDate = Carbon::create($periode_year, $monthNumeric, $startDay)->startOfDay();
    //         $endDate = Carbon::create($periode_year, $monthNumeric, $closeDay)->addMonth()->endOfDay();

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

    //         // Ambil kategori yang punya account_number dan tidak dikecualikan
    //         $kategoris = \App\Models\Kategori::whereNotIn('name', ['Cash in Bank BCA', 'Donasi/Gift', 'PPn Masukan', 'Sponsorship'])
    //             ->whereNotNull('account_number')
    //             ->where('account_number', '!=', '')
    //             ->get();

    //         foreach ($kategoris as $kat) {
    //             $katName = trim($kat->name);
                
    //             if (isset($formModels[$katName])) {
    //                 $model = $formModels[$katName];
    //                 $query = $model::whereIn('user_id', $userIds)
    //                     ->whereIn('status', ['Closed', 'Final Approved'])
    //                     ->whereBetween('final_approved_at', [$startDate, $endDate]);

    //                 // PENTING: Filter berdasarkan kategori_id agar data tidak tertukar di tabel yang sama
    //                 if (\Illuminate\Support\Facades\Schema::hasColumn((new $model)->getTable(), 'kategori_id')) {
    //                     $query->where('kategori_id', $kat->id);
    //                 }
    //                 $totalDebet += $query->sum('approved_total');
    //             } else {
    //                 // Masuk ke Form Others
    //                 $totalDebet += FormOthers::whereIn('user_id', $userIds)
    //                     ->where('kategori_id', $kat->id)
    //                     ->whereIn('status', ['Closed', 'Final Approved'])
    //                     ->whereBetween('final_approved_at', [$startDate, $endDate])
    //                     ->sum('approved_total');
    //             }
    //         }
    //     }

    //     self::$jurnalCache[$cacheKey] = $totalDebet;
    //     return $totalDebet;
    // }

   public static function getBulkKalkulasiJurnal(array $lookups)
{
    if (empty($lookups)) return [];

    $resultsMap = [];
    $startDay = 11; // Batas awal periode (Contoh: tgl 11)
    $closeDay = 13; // Batas akhir approval (Contoh: tgl 13 bulan depan)

    foreach ($lookups as $item) {
        $cabId = $item['cabang_id'];
        $monthNumeric = date('m', strtotime($item['month']));
        $year = (int) $item['year'];
        $key = $cabId . '_' . strtolower($item['month']) . '_' . $year;

        // Penentuan Rentang Tanggal Cut-off Akuntansi
        $startDate = \Illuminate\Support\Carbon::create($year, $monthNumeric, $startDay)->startOfDay();
        $endDate = $startDate->copy()->addMonth()->day($closeDay)->endOfDay();

        $userIds = \App\Models\UserHasCabang::where('cabang_id', $cabId)->pluck('user_id')->toArray();

        if (empty($userIds)) {
            $resultsMap[$key] = 0;
            continue;
        }

        // Menghitung Total Pengeluaran Riil (Status: Closed / Final Approved)
        $tables = ['form_up_country', 'form_vehicle_running_cost', 'form_entertainment_presentation', 'form_meeting_seminar', 'form_others'];
        $totalExpense = 0;

        foreach ($tables as $table) {
            $totalExpense += \Illuminate\Support\Facades\DB::table($table)
                ->whereIn('user_id', $userIds)
                ->whereIn('status', ['Closed', 'Final Approved'])
                ->whereBetween('final_approved_at', [$startDate, $endDate])
                ->sum('approved_total');
        }

        $resultsMap[$key] = $totalExpense;
    }

    return $resultsMap;
}
}