<?php

namespace App\Http\Controllers\Api;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use App\Models\PayrollPeriod;
use App\Models\Employee;
use App\Models\Attendance;      
use App\Models\PayrollDetail;   
use App\Services\AccountingService;
use App\Services\AttendanceService; 
use Carbon\Carbon;

class PayrollController extends Controller
{
    /**
     * Menampilkan daftar semua periode penggajian
     */
    public function getPeriods(Request $request)
    {
        try {
            $query = PayrollPeriod::with('branch');

            if ($request->has('branch_id') && !empty($request->branch_id)) {
                $query->where('branch_id', $request->branch_id);
            }

            $periods = $query->orderBy('year', 'desc')
                             ->orderBy('month', 'desc')
                             ->get();

            return response()->json([
                'success' => true,
                'data' => $periods
            ]);
        } catch (\Exception $e) {
            return response()->json(['success' => false, 'message' => $e->getMessage()], 500);
        }
    }

    /**
     * Membuat periode penggajian baru (Fase Draft)
     */
    public function storePeriod(Request $request)
    {
        $request->validate([
            'period_name' => 'required|string',
            'month'       => 'required|integer|between:1,12',
            'year'        => 'required|integer|digits:4',
            'branch_id'   => 'required|exists:branches,id',
            'start_date'  => 'required|date',
            'end_date'    => 'required|date|after_or_equal:start_date'
        ]);

        try {
            // Validasi duplikasi (kode lama Anda)
            $exists = PayrollPeriod::where('month', $request->month)
                ->where('year', $request->year)
                ->where('branch_id', $request->branch_id)
                ->exists();

            if ($exists) {
                throw new \Exception("Periode penggajian untuk bulan " . $request->month . " tahun " . $request->year . " di cabang tersebut sudah ada.");
            }

            // Tambahkan kolom yang dibutuhkan berdasarkan DB Anda
            $period = PayrollPeriod::create([
                'period_name' => $request->period_name,
                'month'       => $request->month,
                'year'        => $request->year,
                'branch_id'   => $request->branch_id,
                'start_date'  => $request->start_date,
                'end_date'    => $request->end_date,
                'status'      => 'draft',
                'created_by'  => $request->user()->id ?? 1
            ]);

            return response()->json([
                'success' => true,
                'message' => 'Periode penggajian baru berhasil dibuat.',
                'data'    => $period
            ]);
        } catch (\Exception $e) {
            return response()->json(['success' => false, 'message' => $e->getMessage()], 500);
        }
    }

    /**
     * Proses Generate, Hitung Gaji (Pokok + Tunjangan Dinamis), Potong Kasbon, BPJS, dan Posting Jurnal Akrual Otomatis
     */
    public function approvePeriod(Request $request, $periodId)
    {
        $user = $request->user();
        
        DB::beginTransaction();
        try {
            // Mengunci baris data di database untuk mencegah eksekusi ganda (Race Condition)
            $period = PayrollPeriod::lockForUpdate()->findOrFail($periodId);

            if ($period->status !== 'draft') {
                throw new \Exception("Sistem Keamanan: Periode penggajian ini sudah pernah di-approve dan dikunci sebelumnya.");
            }

            // 1. Tarik semua data karyawan aktif beserta komponen gaji, kasbon, posisi, dan relasi tunjangan dinamis (allowances)
            $employeesQuery = Employee::with(['salary', 'activeLoans', 'position', 'allowances.allowanceType'])->where('is_active', true);
            
            if ($period->branch_id) {
                $employeesQuery->where('branch_id', $period->branch_id);
            }
            
            $employees = $employeesQuery->get();

            // 2. Tarik Pemetaan Master Akun (COA) Dinamis
            $akunPiutangKasbon  = AccountingService::getAccountId('employee_receivable_account') ?? 
                                  DB::table('accounts')->where('name', 'like', '%Piutang Karyawan%')->value('id'); 
                                  
            $akunBebanGajiOpex  = AccountingService::getAccountId('expense_salary_account') ?? 
                                  DB::table('accounts')->where('code', '6201')->value('id'); 
                                  
            $akunHutangGajiOpex = AccountingService::getAccountId('payable_salary_account') ?? 
                                  DB::table('accounts')->where('name', 'like', '%Hutang Gaji%')->value('id'); 
                                  
            $akunBebanUpahHpp   = AccountingService::getAccountId('expense_btkl_account') ?? 
                                  DB::table('accounts')->where('code', '5001')->value('id'); 
                                  
            $akunHutangUpahHpp  = AccountingService::getAccountId('payable_btkl_account') ?? 
                                  DB::table('accounts')->where('code', '2110')->value('id'); 
            
            $akunHutangBpjsTk   = AccountingService::getAccountId('payable_bpjs_tk_account') ?? 
                                  DB::table('accounts')->where('name', 'like', '%Hutang BPJS Ketenagakerjaan%')->value('id'); 
            
            $akunHutangBpjsKes  = AccountingService::getAccountId('payable_bpjs_kes_account') ?? 
                                  DB::table('accounts')->where('name', 'like', '%Hutang BPJS Kesehatan%')->value('id');

            // Validasi Keamanan Finansial Ekstra Ketat
            if (!$akunPiutangKasbon || !$akunBebanGajiOpex || !$akunHutangGajiOpex || !$akunBebanUpahHpp || !$akunHutangUpahHpp || !$akunHutangBpjsTk || !$akunHutangBpjsKes) {
                throw new \Exception("Sistem Gagal: Pemetaan Master Akun (COA) untuk Penggajian atau BPJS belum lengkap di Setting Akuntansi. Proses dibatalkan demi keamanan Buku Besar.");
            }

            foreach ($employees as $emp) {
                if (!$emp->salary) continue; 

                // Placeholder Kehadiran Fisik (Nanti disambungkan ke tabel attendances)
                $daysWorked = 26; 

                // ==============================================================
                // A. HITUNG GAJI POKOK
                // ==============================================================
                $baseAmount = 0;
                $descSuffix = '';

                if ($emp->salary->salary_type === 'daily') {
                    $baseAmount = $emp->salary->base_salary * $daysWorked;
                    $descSuffix = " (Rate x " . $daysWorked . " Hari)";
                } else {
                    $baseAmount = $emp->salary->base_salary;
                    $descSuffix = " (Bulanan Flat)";
                }

                $grossSalary = $baseAmount;
                $payrollDetailsData = [];

                // Record Gaji Pokok
                $payrollDetailsData[] = [
                    'type'        => 'earning', 
                    'description' => 'Gaji Pokok / Upah Dasar' . $descSuffix, 
                    'amount'      => $baseAmount, 
                    'created_at'  => Carbon::now()
                ];

                // ==============================================================
                // A.1 HITUNG TUNJANGAN DINAMIS (MASTER ALLOWANCES) DENGAN EFFECTIVE DATING
                // ==============================================================
                $totalDynamicAllowances = 0;
                
                // Siapkan rentang periode gaji sebagai batas utama
                $periodStart = Carbon::parse($period->start_date)->startOfDay();
                $periodEnd   = Carbon::parse($period->end_date)->endOfDay();
                
                foreach ($emp->allowances as $empAllowance) {
                    $allowanceType = $empAllowance->allowanceType;
                    $method        = $allowanceType->calculation_method; 
                    $allowanceDesc = $allowanceType->name; 
                    $amount        = $empAllowance->amount;

                    // --- LOGIKA IRISAN TANGGAL (DATE INTERSECTION) ---
                    $allowanceStart = $empAllowance->start_date ? Carbon::parse($empAllowance->start_date)->startOfDay() : $periodStart->copy();
                    // Jika end_date kosong, dianggap berlaku selamanya (sampai akhir periode ini)
                    $allowanceEnd   = $empAllowance->end_date ? Carbon::parse($empAllowance->end_date)->endOfDay() : $periodEnd->copy();

                    // Hitung titik temu (irisan) antara Periode Gaji dan Masa Aktif Tunjangan
                    $calcStart = $periodStart->copy()->max($allowanceStart);
                    $calcEnd   = $periodEnd->copy()->min($allowanceEnd);

                    // Jika tidak ada irisan (tunjangan mati sebelum periode gaji, atau baru aktif setelah periode gaji selesai) -> lewati
                    if ($calcStart->gt($calcEnd)) {
                        continue;
                    }

                    $calculatedAmount = 0;
                    $queryStartDate   = $calcStart->toDateString();
                    $queryEndDate     = $calcEnd->toDateString();

                    // Indikator visual untuk Finance jika tunjangan terpotong di tengah bulan
                    $isPartialPeriod = ($calcStart->toDateString() !== $periodStart->toDateString() || $calcEnd->toDateString() !== $periodEnd->toDateString());
                    if ($isPartialPeriod) {
                        $allowanceDesc .= " (" . $calcStart->format('d/m') . " - " . $calcEnd->format('d/m') . ")";
                    }

                    if ($method === 'attendance_based') {
                        // Absensi hanya dihitung DALAM rentang irisan waktu tunjangan aktif
                        $actualPresence = \App\Models\Attendance::where('employee_id', $emp->id)
                            ->where('status', 'hadir')
                            ->whereBetween('date', [$queryStartDate, $queryEndDate])
                            ->count();
                        
                        $calculatedAmount = $amount * $actualPresence;

                    } elseif ($method === 'prorata_working_days') {
                        $attendanceService = new \App\Services\AttendanceService();
                        $targetDays = $attendanceService->getWorkingDays($period->year, $period->month, $emp->branch_id);
                        
                        $actualPresence = \App\Models\Attendance::where('employee_id', $emp->id)
                            ->where('status', 'hadir')
                            ->whereBetween('date', [$queryStartDate, $queryEndDate])
                            ->count();
                            
                        $calculatedAmount = ($targetDays > 0) ? ($amount / $targetDays) * $actualPresence : 0;

                    } else {
                        // Metode 'flat'
                        $daysInPeriod = $periodStart->diffInDays($periodEnd) + 1;
                        $daysActive   = $calcStart->diffInDays($calcEnd) + 1;

                        // Jika flat tapi mati/mulai di pertengahan, kita hitung pro-rata harian kalendernya agar tidak double payment
                        if ($isPartialPeriod) {
                            $calculatedAmount = ($amount / $daysInPeriod) * $daysActive;
                        } else {
                            $calculatedAmount = $amount;
                        }
                    }

                    $totalDynamicAllowances += $calculatedAmount;
                    $payrollDetailsData[] = [
                        'type'        => 'earning',
                        'description' => $allowanceDesc,
                        'amount'      => round($calculatedAmount), // Dibulatkan agar tidak ada desimal koma
                        'created_at'  => Carbon::now()
                    ];
                }

                // Total Pendapatan Kotor (Gaji Pokok + Semua Tunjangan)
                $grossSalary += $totalDynamicAllowances;

                // ==============================================================
                // B. BUAT HEADER SLIP GAJI KARYAWAN
                // ==============================================================
                $payrollId = DB::table('payrolls')->insertGetId([
                    'payroll_period_id' => $period->id,
                    'employee_id'       => $emp->id,
                    'branch_id'         => $emp->branch_id,
                    'payslip_number'    => 'PAY-' . $period->year . str_pad($period->month, 2, '0', STR_PAD_LEFT) . '-' . str_pad($emp->id, 4, '0', STR_PAD_LEFT),
                    'total_earnings'    => $grossSalary,
                    'total_deductions'  => 0, 
                    'net_salary'        => 0, 
                    'payment_status'    => 'unpaid',
                    'created_at'        => Carbon::now()
                ]);

                // Insert semua detail earning (Pokok + Dinamis) sekaligus
                foreach ($payrollDetailsData as &$detail) {
                    $detail['payroll_id'] = $payrollId;
                }
                DB::table('payroll_details')->insert($payrollDetailsData);

                // ==============================================================
                // C. OTOMASI POTONGAN KASBON & BPJS (DEDUCTIONS)
                // ==============================================================
                $totalDeduction = 0;

                // C.1 Potongan Cicilan Kasbon
                $totalKasbonDeduction = 0;
                foreach ($emp->activeLoans as $loan) {
                    $deductAmount = min($loan->installment_amount, $loan->remaining_balance);
                    $totalKasbonDeduction += $deductAmount;

                    DB::table('payroll_details')->insert([
                        'payroll_id'  => $payrollId,
                        'type'        => 'deduction',
                        'description' => 'Potongan Cicilan Kasbon (' . $loan->loan_number . ')',
                        'amount'      => round($deductAmount),
                        'created_at'  => Carbon::now()
                    ]);

                    $newBalance = $loan->remaining_balance - $deductAmount;
                    $loan->update([
                        'remaining_balance' => $newBalance,
                        'status'            => $newBalance <= 0 ? 'paid_off' : 'active'
                    ]);
                }
                $totalDeduction += $totalKasbonDeduction;

                // C.2 Potongan BPJS
                $bpjsKesehatanDeduction = 0;
                $bpjsTkDeduction = 0;

                if ($emp->salary->has_bpjs) {
                    $bpjsKesehatanDeduction = $baseAmount * 0.01; // 1% dari Gaji Pokok
                    $bpjsTkDeduction = $baseAmount * 0.02;        // 2% dari Gaji Pokok

                    if ($bpjsKesehatanDeduction > 0) {
                        DB::table('payroll_details')->insert([
                            'payroll_id'  => $payrollId,
                            'type'        => 'deduction',
                            'description' => 'Potongan BPJS Kesehatan (1%)',
                            'amount'      => round($bpjsKesehatanDeduction),
                            'created_at'  => Carbon::now()
                        ]);
                        $totalDeduction += $bpjsKesehatanDeduction;
                    }

                    if ($bpjsTkDeduction > 0) {
                        DB::table('payroll_details')->insert([
                            'payroll_id'  => $payrollId,
                            'type'        => 'deduction',
                            'description' => 'Potongan JHT Ketenagakerjaan (2%)',
                            'amount'      => round($bpjsTkDeduction),
                            'created_at'  => Carbon::now()
                        ]);
                        $totalDeduction += $bpjsTkDeduction;
                    }
                }

                $netSalary = $grossSalary - $totalDeduction;

                // Update nilai final Netto Bersih pada Header Slip Gaji
                DB::table('payrolls')->where('id', $payrollId)->update([
                    'total_deductions' => round($totalDeduction),
                    'net_salary'       => round($netSalary)
                ]);

                // =================================================================
                // D. AUTO-ROUTING JURNAL AKUNTANSI BUKU BESAR 
                // =================================================================
                $isPabrik = ($emp->position && $emp->position->expense_group === 'hpp');
                
                $akunDebitBiaya   = $isPabrik ? $akunBebanUpahHpp : $akunBebanGajiOpex;
                $akunKreditHutang = $isPabrik ? $akunHutangUpahHpp : $akunHutangGajiOpex;
                $descPrefix       = $isPabrik ? "Akrual Upah Pabrik (HPP): " : "Akrual Gaji Karyawan (OPEX): ";

                $journalItems = [
                    ['account_id' => $akunDebitBiaya, 'debit' => round($grossSalary), 'credit' => 0], // DEBIT Beban Gaji Utuh (Gross)
                ];

                if ($totalKasbonDeduction > 0) {
                    $journalItems[] = ['account_id' => $akunPiutangKasbon, 'debit' => 0, 'credit' => round($totalKasbonDeduction)]; // KREDIT Pelunasan Kasbon
                }

                if ($bpjsKesehatanDeduction > 0) {
                    $journalItems[] = ['account_id' => $akunHutangBpjsKes, 'debit' => 0, 'credit' => round($bpjsKesehatanDeduction)]; // KREDIT Hutang BPJS Kesehatan
                }

                if ($bpjsTkDeduction > 0) {
                    $journalItems[] = ['account_id' => $akunHutangBpjsTk, 'debit' => 0, 'credit' => round($bpjsTkDeduction)]; // KREDIT Hutang BPJS TK
                }

                $journalItems[] = ['account_id' => $akunKreditHutang, 'debit' => 0, 'credit' => round($netSalary)]; // KREDIT Hutang Gaji Bersih

                // Kirim transaksi ke Core Accounting Engine Service
                $journal = AccountingService::log([
                    'branch_id'   => $emp->branch_id,
                    'entry_date'  => Carbon::today()->toDateString(),
                    'description' => $descPrefix . $emp->name . " (Periode " . $period->month . "/" . $period->year . ")",
                    'reference'   => 'PAY-' . $period->year . str_pad($period->month, 2, '0', STR_PAD_LEFT) . '-' . str_pad($emp->id, 4, '0', STR_PAD_LEFT),
                    'source_type' => 'PAYROLL_ACCRUAL',
                    'user_id'     => $user->id ?? 1,
                    'items'       => $journalItems
                ]);

                // Tautkan ID Journal Entry ke Slip Gaji
                DB::table('payrolls')->where('id', $payrollId)->update(['journal_entry_id' => $journal->id]);
            }

            // Kunci periode penggajian secara permanen
            $period->update([
                'status'      => 'approved',
                'approved_by' => $user->id ?? 1
            ]);

            DB::commit();
            return response()->json([
                'success' => true, 
                'message' => 'Periode Penggajian berhasil disetujui! Slip Gaji, Potongan Kasbon, Potongan BPJS, dan Jurnal Akrual telah diproses penuh.'
            ]);

        } catch (\Exception $e) {
            DB::rollBack();
            return response()->json(['success' => false, 'message' => $e->getMessage()], 500);
        }
    }

    public function showPeriod($id)
    {
        try {
            $period = PayrollPeriod::with('branch')->findOrFail($id);

            // Menggunakan Query Builder agar join lebih optimal dan aman
            $payrolls = DB::table('payrolls')
                ->join('employees', 'payrolls.employee_id', '=', 'employees.id')
                ->leftJoin('positions', 'employees.position_id', '=', 'positions.id')
                ->where('payrolls.payroll_period_id', $id)
                ->select(
                    'payrolls.*',
                    'employees.employee_code',
                    'employees.name as employee_name',
                    'positions.name as position_name'
                )
                ->orderBy('employees.name', 'asc')
                ->get();

            return response()->json([
                'success' => true,
                'data' => [
                    'period'   => $period,
                    'payrolls' => $payrolls
                ]
            ]);
        } catch (\Exception $e) {
            return response()->json(['success' => false, 'message' => 'Periode tidak ditemukan atau terjadi kesalahan server.'], 404);
        }
    }

    /**
     * Cetak Slip Gaji PDF / HTML Print
     */
    public function printPayslip($id)
    {
        try {
            $payroll = DB::table('payrolls')
                ->join('employees', 'payrolls.employee_id', '=', 'employees.id')
                ->join('payroll_periods', 'payrolls.payroll_period_id', '=', 'payroll_periods.id')
                ->leftJoin('positions', 'employees.position_id', '=', 'positions.id')
                ->leftJoin('branches', 'payrolls.branch_id', '=', 'branches.id')
                ->where('payrolls.id', $id)
                ->select(
                    'payrolls.*',
                    'employees.employee_code',
                    'employees.name as employee_name',
                    'positions.name as position_name',
                    'payroll_periods.month',
                    'payroll_periods.year',
                    'payroll_periods.period_name',
                    'branches.name as branch_name'
                )
                ->first();

            if (!$payroll) {
                return abort(404, 'Slip Gaji tidak ditemukan.');
            }

            $details = DB::table('payroll_details')->where('payroll_id', $id)->get();
            
            $earnings = $details->where('type', 'earning');
            $deductions = $details->where('type', 'deduction');

            // Kita kembalikan ke view blade HTML yang diformat khusus untuk PDF/Print
            return view('payslip', compact('payroll', 'earnings', 'deductions'));

        } catch (\Exception $e) {
            return abort(500, 'Terjadi kesalahan saat memuat dokumen: ' . $e->getMessage());
        }
    }

    /**
     * Export Rekapitulasi Gaji Excel (Dinamis Pivot Kolom)
     */
    public function exportExcel($periodId)
    {
        try {
            $period = PayrollPeriod::with('branch')->findOrFail($periodId);

            // 1. Ambil data Header Slip Gaji
            $payrolls = DB::table('payrolls')
                ->join('employees', 'payrolls.employee_id', '=', 'employees.id')
                ->leftJoin('positions', 'employees.position_id', '=', 'positions.id')
                ->where('payrolls.payroll_period_id', $periodId)
                ->select(
                    'payrolls.id',
                    'payrolls.payslip_number',
                    'payrolls.total_earnings',
                    'payrolls.total_deductions',
                    'payrolls.net_salary',
                    'employees.employee_code',
                    'employees.name as employee_name',
                    'positions.name as position_name'
                )
                ->orderBy('employees.name', 'asc')
                ->get();

            if ($payrolls->isEmpty()) {
                return response()->json(['success' => false, 'message' => 'Tidak ada data slip gaji untuk diekspor.'], 404);
            }

            // 2. Ambil seluruh Detail Gaji (Komponen Pembentuk)
            $payrollIds = $payrolls->pluck('id')->toArray();
            $details = DB::table('payroll_details')->whereIn('payroll_id', $payrollIds)->get();

            // 3. EKSTRAK HEADER DINAMIS (Scanning semua jenis tunjangan & potongan yang ada di periode ini)
            $allowanceHeaders = [];
            $deductionHeaders = [];

            foreach ($details as $d) {
                $descLower = strtolower($d->description);
                // Pisahkan Gaji Pokok dari Tunjangan Lainnya
                if ($d->type === 'earning' && strpos($descLower, 'gaji pokok') === false && strpos($descLower, 'upah dasar') === false) {
                    if (!in_array($d->description, $allowanceHeaders)) {
                        $allowanceHeaders[] = $d->description;
                    }
                } elseif ($d->type === 'deduction') {
                    if (!in_array($d->description, $deductionHeaders)) {
                        $deductionHeaders[] = $d->description;
                    }
                }
            }

            // 4. Mulai Susun Spreadsheet
            $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();
            $sheet->setTitle('Rekapitulasi Gaji');

            // --- SUSUN JUDUL KOLOM EXCEL ---
            $headers = ['NO', 'NO SLIP', 'NIK', 'NAMA KARYAWAN', 'JABATAN', 'GAJI POKOK'];
            
            // Masukkan setiap Tunjangan jadi kolom sendiri
            foreach ($allowanceHeaders as $header) {
                $headers[] = strtoupper($header);
            }
            $headers[] = 'TOTAL BRUTO';

            // Masukkan setiap Potongan jadi kolom sendiri
            foreach ($deductionHeaders as $header) {
                $headers[] = strtoupper($header);
            }
            $headers[] = 'TOTAL POTONGAN';
            $headers[] = 'GAJI BERSIH (NETTO)';

            // Cetak Header ke baris pertama (Row 1)
            $colLetter = 'A';
            foreach ($headers as $headerTitle) {
                $sheet->setCellValue($colLetter . '1', $headerTitle);
                $sheet->getStyle($colLetter . '1')->getFont()->setBold(true);
                $sheet->getColumnDimension($colLetter)->setAutoSize(true);
                $colLetter++;
            }

            // --- ISI DATA MASING-MASING KARYAWAN ---
            $row = 2;
            foreach ($payrolls as $index => $p) {
                $empDetails = $details->where('payroll_id', $p->id);
                
                // Ekstrak Nominal Gaji Pokok
                $gajiPokok = $empDetails->filter(function($d) {
                    $desc = strtolower($d->description);
                    return $d->type === 'earning' && (strpos($desc, 'gaji pokok') !== false || strpos($desc, 'upah dasar') !== false);
                })->sum('amount');

                // Info Dasar
                $sheet->setCellValue('A' . $row, $index + 1);
                $sheet->setCellValue('B' . $row, $p->payslip_number);
                $sheet->setCellValueExplicit('C' . $row, $p->employee_code, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                $sheet->setCellValue('D' . $row, $p->employee_name);
                $sheet->setCellValue('E' . $row, $p->position_name ?? '-');
                $sheet->setCellValue('F' . $row, $gajiPokok);

                $currentColIndex = 7; // Dimulai dari kolom G

                // Mapping Nominal Tunjangan ke Kolomnya masing-masing
                foreach ($allowanceHeaders as $headerName) {
                    $amount = $empDetails->where('type', 'earning')->where('description', $headerName)->sum('amount');
                    $colString = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($currentColIndex);
                    $sheet->setCellValue($colString . $row, $amount);
                    $currentColIndex++;
                }

                // Kolom Total Bruto
                $colString = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($currentColIndex);
                $sheet->setCellValue($colString . $row, $p->total_earnings);
                $sheet->getStyle($colString . $row)->getFont()->setBold(true);
                $currentColIndex++;

                // Mapping Nominal Potongan ke Kolomnya masing-masing
                foreach ($deductionHeaders as $headerName) {
                    $amount = $empDetails->where('type', 'deduction')->where('description', $headerName)->sum('amount');
                    $colString = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($currentColIndex);
                    $sheet->setCellValue($colString . $row, $amount);
                    $currentColIndex++;
                }

                // Kolom Total Potongan
                $colString = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($currentColIndex);
                $sheet->setCellValue($colString . $row, $p->total_deductions);
                $sheet->getStyle($colString . $row)->getFont()->setBold(true);
                $sheet->getStyle($colString . $row)->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);
                $currentColIndex++;

                // Kolom Gaji Bersih Netto
                $colString = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($currentColIndex);
                $sheet->setCellValue($colString . $row, $p->net_salary);
                $sheet->getStyle($colString . $row)->getFont()->setBold(true);
                $sheet->getStyle($colString . $row)->getFont()->getColor()->setARGB('00059669'); // Emerald/Hijau
                $currentColIndex++;

                $row++;
            }

            // --- STYLING EXCEL ---
            // Format Ribuan (Accounting) untuk kolom F (Gaji Pokok) hingga kolom Netto Terakhir
            $lastCol = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(count($headers));
            $sheet->getStyle('F2:' . $lastCol . ($row - 1))->getNumberFormat()->setFormatCode('#,##0');

            // Warna Background Header Abu-abu
            $sheet->getStyle('A1:' . $lastCol . '1')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('E2E8F0');

            // 5. Cetak File dan Triger Download
            $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
            $fileName = "Rekap_Gaji_Finance_" . str_replace(' ', '_', $period->period_name) . ".xlsx";

            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment; filename="' . $fileName . '"');
            header('Cache-Control: max-age=0');

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

        } catch (\Exception $e) {
            return response()->json(['success' => false, 'message' => 'Gagal generate Excel Pivot: ' . $e->getMessage()], 500);
        }
    }

    /**
     * Cetak Slip Gaji Massal (Grid 4 per A4 / Ukuran A6)
     */
    public function printMassPayslips($periodId)
    {
        try {
            $period = PayrollPeriod::with('branch')->findOrFail($periodId);

            // Tarik semua slip gaji di periode ini
            $payrolls = DB::table('payrolls')
                ->join('employees', 'payrolls.employee_id', '=', 'employees.id')
                ->leftJoin('positions', 'employees.position_id', '=', 'positions.id')
                ->where('payrolls.payroll_period_id', $periodId)
                ->select(
                    'payrolls.*',
                    'employees.employee_code',
                    'employees.name as employee_name',
                    'positions.name as position_name'
                )
                ->orderBy('employees.name', 'asc')
                ->get();

            if ($payrolls->isEmpty()) {
                return abort(404, 'Tidak ada data slip gaji pada periode ini.');
            }

            // Tarik semua detail transaksi sekaligus untuk efisiensi query
            $payrollIds = $payrolls->pluck('id')->toArray();
            $allDetails = DB::table('payroll_details')->whereIn('payroll_id', $payrollIds)->get();

            // Gabungkan detail ke masing-masing slip
            foreach ($payrolls as $payroll) {
                $payroll->earnings = $allDetails->where('payroll_id', $payroll->id)->where('type', 'earning');
                $payroll->deductions = $allDetails->where('payroll_id', $payroll->id)->where('type', 'deduction');
                $payroll->period_name = $period->period_name;
                $payroll->branch_name = $period->branch->name ?? 'Pusat';
            }

            return view('payslip-mass', compact('period', 'payrolls'));

        } catch (\Exception $e) {
            return abort(500, 'Terjadi kesalahan saat memuat dokumen massal: ' . $e->getMessage());
        }
    }
}