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

<?php

declare(strict_types=1);

namespace App\Http\Controllers\Backend;

use App\Http\Controllers\Controller;
use App\Models\{
    Admin, Cabang, Region, Kategori, BudgetControlLog,
    FormUpCountry, FormEntertaimentPresentation, FormMeetingSeminar, 
    FormVehicleRunningCost, FormOthers, UserHasCabang
};
use App\Helpers\{FormHelper, BudgetHelper, PeriodeHelper};
use Illuminate\Support\Facades\{DB, Schema};
use Illuminate\Support\Carbon;
use Illuminate\Http\Request;
use Illuminate\Database\Eloquent\Builder;

class ReportController extends Controller
{
    private function getAuthorizedUserIds(): ?array
    {
        $user = auth()->user();
        $role = $user->getRoleNames()[0] ?? null;
        $userData = $user->getMyCabangAndRegionId();
        
        return match ($role) {
            'Admin Region', 'Marketing Operational Manager Region' => 
                ($rid = $userData['region']) && $rid !== '-' 
                    ? UserHasCabang::whereIn('cabang_id', Cabang::where('region_id', $rid)->pluck('id'))->pluck('user_id')->toArray() 
                    : [],
            
            'Area Manager Cabang' => 
                ($cid = $userData['cabang']) && $cid !== '-' 
                    ? UserHasCabang::where('cabang_id', $cid)->pluck('user_id')->toArray() 
                    : [],
            
            'Medical Representatif' => [$user->id],
            
            default => null,
        };
    }

    private function getFilteredForms(string $modelClass, array $withRelations = [])
    {
        $defaultRelations = ['user.region.cabang.region', 'user.cabang.cabang.region', 'rejector'];
        $query = $modelClass::with(array_merge($defaultRelations, $withRelations));

        $userIds = $this->getAuthorizedUserIds();
        if ($userIds !== null) {
            $query->whereIn('user_id', $userIds);
        }

        $this->applyStandardFilters($query, request()->all());
        return $query->orderBy('tanggal', 'desc')->get();
    }

    /**
     * MODIFIKASI: Filter diubah dari rentang tanggal harian (start/end date) 
     * menjadi Rentang Periode Bulan Akuntansi (Bulan berjalan tgl 11 s/d Bulan Depan tgl 13)
     */
    private function applyStandardFilters(Builder $query, array $params): void
    {
        if (!empty($params['region'])) {
            $query->whereHas('user.region.cabang.region', fn($q) => $q->where('code', $params['region']));
        }

        if (!empty($params['cabang'])) {
            $query->whereHas('user.cabang.cabang', fn($q) => $q->where('code', $params['cabang']));
        }

        if (!empty($params['status'])) {
            $query->where('status', $params['status']);
        }

        // --- PENYEMPURNAAN LOGIKA FILTER PERIODE BULAN CUT-OFF (11 s/d 13 Bulan Berikut) ---
        if (!empty($params['month']) && !empty($params['year'])) {
            $month = strtolower($params['month']);
            $year = (int) $params['year'];

            [$startDate, $endDate] = PeriodeHelper::getPeriodeRange($month, $year);

            // Semua pencarian report kategori mengacu pada tanggal final approval/closing dalam rentang periode ini
            $query->whereBetween('final_approved_at', [$startDate, $endDate]);
        }
    }

    public function upcountry()
    {
        $params = request()->all();
        return view('backend.pages.report.upcountry', [
            'pageInfo' => ['title' => 'Reports Form Up Country'],
            'forms' => $this->getFilteredForms(FormUpCountry::class, ['rayon']),
            'cabang' => Cabang::all(),
            'regions' => Region::all(),
            'year' => $params['year'] ?? Carbon::now()->year,
            'month' => $params['month'] ?? Carbon::now()->format('F'),
        ]);
    }

    public function vehicle()
    {
        $params = request()->all();
        return view('backend.pages.report.vehicle', [
            'pageInfo' => ['title' => 'Reports Form Vehicle Running Cost'],
            'forms' => $this->getFilteredForms(FormVehicleRunningCost::class),
            'cabang' => Cabang::all(),
            'regions' => Region::all(),
            'year' => $params['year'] ?? Carbon::now()->year,
            'month' => $params['month'] ?? Carbon::now()->format('F'),
        ]);
    }

    public function entertainment()
    {
        $params = request()->all();
        return view('backend.pages.report.entertainment', [
            'pageInfo' => ['title' => 'Reports Form Entertainment & Presentation'],
            'forms' => $this->getFilteredForms(FormEntertaimentPresentation::class, ['attachments']),
            'cabang' => Cabang::all(),
            'regions' => Region::all(),
            'year' => $params['year'] ?? Carbon::now()->year,
            'month' => $params['month'] ?? Carbon::now()->format('F'),
        ]);
    }

    public function meeting()
    {
        $params = request()->all();
        return view('backend.pages.report.meeting', [
            'pageInfo' => ['title' => 'Reports Form Meeting & Seminar'],
            'forms' => $this->getFilteredForms(FormMeetingSeminar::class),
            'cabang' => Cabang::all(),
            'regions' => Region::all(),
            'year' => $params['year'] ?? Carbon::now()->year,
            'month' => $params['month'] ?? Carbon::now()->format('F'),
        ]);
    }

    public function others()
    {
        $params = request()->all();
        return view('backend.pages.report.others', [
            'pageInfo' => ['title' => 'Reports Form Others'],
            'forms' => $this->getFilteredForms(FormOthers::class, ['kategori']),
            'cabang' => Cabang::all(),
            'regions' => Region::all(),
            'year' => $params['year'] ?? Carbon::now()->year,
            'month' => $params['month'] ?? Carbon::now()->format('F'),
        ]);
    }

    public function all()
    {
        $params = request()->all();
        $userFilter = $this->getAuthorizedUserIds();
        $availableBudget = 0;

        $selectedMonth = strtolower($params['month'] ?? Carbon::now()->format('F'));
        $selectedYear = (int) ($params['year'] ?? Carbon::now()->year);

        if (!empty($params['cabang'])) {
            $cabang = Cabang::where('code', $params['cabang'])->first();
            if ($cabang) {
                $availableBudget = BudgetHelper::getAvailableBudget($cabang->id, $selectedMonth, $selectedYear);
                $uIds = UserHasCabang::where('cabang_id', $cabang->id)->pluck('user_id')->toArray();
                $userFilter = $userFilter === null ? $uIds : array_values(array_intersect($userFilter, $uIds));
            }
        }

        // Sinkronisasi logika rentang tanggal 11 s/d 13 bulan depan untuk database konsolidasi (All Forms)
        [$start, $end] = PeriodeHelper::getPeriodeRange($selectedMonth, $selectedYear);

        $tables = [
            'form_others' => 'bukti_total',
            'form_up_country' => 'NULL',
            'form_vehicle_running_cost' => 'bukti_total',
            'form_meeting_seminar' => 'NULL',
            'form_entertainment_presentation' => 'bukti_total'
        ];

        $formsQuery = null;

        foreach ($tables as $table => $mainProofCol) {
            $query = DB::table($table)->selectRaw("
                id, 
                expense_number, 
                '$table' as type, 
                user_id, 
                total, 
                approved_total, 
                account_number, 
                status, 
                final_approved_at as report_date, 
                created_at,
                $mainProofCol as main_proof,
                rejected_by,
                rejected_at,
                remarks
            ")
                ->when($userFilter, fn($q) => $q->whereIn('user_id', $userFilter))
                ->when(!empty($params['status']), fn($q) => $q->where('status', $params['status']))
                // Kunci dengan rentang tanggal closing resmi 11 s/d 13 bulan berikutnya
                ->whereBetween('final_approved_at', [$start, $end])
                ->when(Schema::hasColumn($table, 'deleted_at'), fn($q) => $q->whereNull('deleted_at'));

            $formsQuery = $formsQuery ? $formsQuery->unionAll($query) : $query;
        }

        $forms = collect();
        if ($formsQuery) {
            $forms = DB::query()->fromSub($formsQuery, 'forms')
                ->orderByDesc('report_date')
                ->paginate(100)->appends($params);

            $items = collect($forms->items());
            $formIds = $items->pluck('id');
            
            $submitterIds = $items->pluck('user_id')->filter()->unique();
            $rejectorIds = $items->pluck('rejected_by')->filter()->unique();
            $allAdminIds = $submitterIds->merge($rejectorIds)->unique();

            $admins = Admin::with(['region.cabang.region', 'cabang.cabang.region'])
                ->whereIn('id', $allAdminIds)
                ->get()
                ->keyBy('id');

            $attachments = \App\Models\AttachmentForm::whereIn('form_id', $formIds)
                ->get()
                ->groupBy(fn($q) => $q->table_name . '_' . $q->form_id);

            $forms->getCollection()->transform(function ($item) use ($admins, $attachments) {
                $item->user = $admins->get($item->user_id);
                $item->rejector = $admins->get($item->rejected_by);
                $item->type_label = ucwords(str_replace(['form_', '_'], ['', ' '], (string)$item->type));
                
                $key = $item->type . '_' . $item->id;
                $item->extra_attachments = $attachments->get($key) ?? collect();
                
                return $item;
            });
        }

        return view('backend.pages.report.all', [
            'pageInfo' => ['title' => 'Reports For All Forms'],
            'forms' => $forms,
            'cabangs' => Cabang::all(),
            'regions' => Region::all(),
            'availableBudget' => $availableBudget,
            'year' => $selectedYear,
            'month' => $selectedMonth,
        ]);
    }

    public function jurnal()
    {
        $user = auth()->user();
        $role = $user->getRoleNames()[0] ?? null;
        $userData = $user->getMyCabangAndRegionId();
        
        $regions = Region::all();
        $cabangs = Cabang::all();

        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
        ]);
    }

    public function generateJurnal(Request $request)
    {
        $request->validate([
            'cabang' => 'required',
            'year'   => 'required',
            'month'  => 'required',
        ]);

        $params = $request->all();
        $accounting_period = $params['accounting_period'] ?? '-';
        
        $excluded = ['Cash in Bank BCA', 'Donasi/Gift', 'PPn Masukan', 'Vehicle Maintenance', 'Bahan Promosi', 'Sponsorship'];
        $kategoris = \App\Models\Kategori::whereNotIn('name', $excluded)->get();
        $kategori_bca = \App\Models\Kategori::where('name', 'Cash in Bank BCA')->first();
        
        $cabang = \App\Models\Cabang::where('code', $params['cabang'])->firstOrFail();
        $year = (int) $params['year'];
        $month = strtolower($params['month']);

        // Memanggil rentang baru 11 s/d 13 bulan depan untuk pembuatan Jurnal Akurat
        [$startDate, $endDate] = PeriodeHelper::getPeriodeRange($month, $year);

        $pettyCashAmount = \App\Models\BudgetControl::where('cabang_id', $cabang->id)
            ->where('periode_year', $year)
            ->where('periode_month', $month)
            ->sum('amount');

        $budget = \App\Models\BudgetControlLog::where('cabang_id', $cabang->id)
            ->where('periode_year', $year)
            ->where('periode_month', $month)
            ->orderByDesc('created_at')->first();

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

        $formModels = [
            'up country'           => \App\Models\FormUpCountry::class,
            'vehicle running cost' => \App\Models\FormVehicleRunningCost::class,
            'entertainment'        => \App\Models\FormEntertaimentPresentation::class,
            'presentation'         => \App\Models\FormEntertaimentPresentation::class,
            'sponsorship'          => \App\Models\FormEntertaimentPresentation::class,
            'meeting / seminar'    => \App\Models\FormMeetingSeminar::class,
        ];

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

            if (isset($formModels[$katKey])) {
                $model = $formModels[$katKey];
                
                $query = $model::whereIn('user_id', $userIds)
                    ->whereBetween('final_approved_at', [$startDate, $endDate])
                    ->where('status', 'Closed');

                if ($model === \App\Models\FormEntertaimentPresentation::class) {
                    $query->where('kategori_id', $kategori->id)
                          ->where(function($q) use ($katKey) {
                              $q->where('jenis', 'LIKE', '%' . $katKey . '%');
                          });
                } else {
                    if (\Illuminate\Support\Facades\Schema::hasColumn((new $model)->getTable(), 'kategori_id')) {
                        $query->where('kategori_id', $kategori->id);
                    }
                }
                    
                $sum = $query->sum('approved_total');
            } else {
                $sum = \App\Models\FormOthers::whereIn('user_id', $userIds)
                    ->where('kategori_id', $kategori->id)
                    ->whereBetween('final_approved_at', [$startDate, $endDate])
                    ->where('status', 'Closed')
                    ->sum('approved_total');
            }
            $nominals[$kategori->id] = $sum;
        }

        $totalDebet = array_sum($nominals);

        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,
            'pettyCashAmount'   => $pettyCashAmount,
            'cashInBca'         => $pettyCashAmount - $totalDebet,
            'accounting_period' => $accounting_period,
        ]);
    }

    public function reportRejected(Request $request)
    {
        $models = [
            \App\Models\FormUpCountry::class,
            \App\Models\FormEntertaimentPresentation::class,
            \App\Models\FormVehicleRunningCost::class,
            \App\Models\FormMeetingSeminar::class,
            \App\Models\FormOthers::class
        ];

        $allRejected = collect();

        foreach ($models as $model) {
            $data = $model::with(['user', 'cabang', 'rejector'])
                ->where('status', 'Rejected')
                ->when($request->start_date, function ($query) use ($request) {
                    return $query->whereBetween('rejected_at', [$request->start_date, $request->end_date]);
                })
                ->get();

            $allRejected = $allRejected->concat($data);
        }

        $allRejected = $allRejected->sortByDesc('rejected_at');

        return view('backend.report.report_rejected', [
            'data' => $allRejected,
            'start_date' => $request->start_date,
            'end_date' => $request->end_date,
            'year' => $request->year ?? Carbon::now()->year,
            'month' => $request->month ?? Carbon::now()->format('F'),
        ]);
    }
}