<?php

namespace App\Services;

use App\Models\Activity;
use App\Models\Attendance;
use App\Models\Company;
use App\Models\User;
use Carbon\Carbon;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\Storage;

class ExcelExportService
{
    protected Spreadsheet $spreadsheet;
    protected $sheet;
    protected int $currentRow = 1;
    protected Company $company;
    protected ?User $user = null;

    // Column widths matching the template exactly (from actual template)
    protected array $columnWidths = [
        'A' => 6.6,    // NO
        'B' => 10.6,   // TANGGAL
        'C' => 12.6,   // Hari
        'D' => 12.6,   // WAKTU
        'E' => 54,     // URAIAN (Aktivitas)
        'F' => 40,     // OUTPUT
        'G' => 16,     // KETERANGAN (Lokasi)
    ];

    // Indonesian day names
    protected array $dayNames = [
        'Monday' => 'Senin',
        'Tuesday' => 'Selasa',
        'Wednesday' => 'Rabu',
        'Thursday' => 'Kamis',
        'Friday' => 'Jumat',
        'Saturday' => 'Sabtu',
        'Sunday' => 'Minggu',
    ];

    // Indonesian month names
    protected array $monthNames = [
        1 => 'Januari',
        2 => 'Februari',
        3 => 'Maret',
        4 => 'April',
        5 => 'Mei',
        6 => 'Juni',
        7 => 'Juli',
        8 => 'Agustus',
        9 => 'September',
        10 => 'Oktober',
        11 => 'November',
        12 => 'Desember',
    ];

    // Short month names for date format
    protected array $shortMonthNames = [
        1 => 'Jan',
        2 => 'Feb',
        3 => 'Mar',
        4 => 'Apr',
        5 => 'May',
        6 => 'Jun',
        7 => 'Jul',
        8 => 'Aug',
        9 => 'Sep',
        10 => 'Oct',
        11 => 'Nov',
        12 => 'Dec',
    ];

    // Colors (matching reference timesheet)
    protected string $headerBgColor = '003399';  // Dark blue for header
    protected string $headerTextColor = 'FFFFFF'; // White text
    protected string $yellowHighlight = 'FFFF00'; // Yellow for incomplete data
    protected string $orangeHighlight = 'FF9966'; // Orange for missing workdays (from reference)
    protected string $borderColor = '0099CC';    // Cyan/light blue border color (from reference)

    // Font settings (matching reference timesheet)
    protected string $fontTitle = 'Arial';       // For title
    protected string $fontHeader = 'Calibri';    // For table header
    protected string $fontData = 'Arial Narrow'; // For data rows
    protected string $fontLabel = 'Arial';       // For labels (NAMA, POSISI, BULAN)

    public function __construct()
    {
        $this->spreadsheet = new Spreadsheet();
        $this->sheet = $this->spreadsheet->getActiveSheet();
    }

    public function exportAttendance(
        Collection $attendances,
        Company $company,
        ?Carbon $startDate = null,
        ?Carbon $endDate = null,
        ?User $user = null
    ): string {
        $this->company = $company;
        $this->user = $user;

        // Set column widths
        foreach ($this->columnWidths as $col => $width) {
            $this->sheet->getColumnDimension($col)->setWidth($width);
        }

        // Set sheet name
        $sheetName = $user ? substr(str_replace(' ', '_', $user->name), 0, 25) : 'Laporan_Kehadiran';
        $this->sheet->setTitle($sheetName);

        // Add header section with logos
        $this->addHeader($startDate, $endDate, $user);

        // Add data table
        $this->addDataTable($attendances, $startDate, $endDate);

        // Add footer with signatures
        $this->addFooter($attendances, $startDate, $endDate);

        // Generate file
        $filename = $this->generateFilename($startDate, $endDate, $user);
        $path = 'exports/' . $filename;

        $writer = new Xlsx($this->spreadsheet);

        // Save to storage
        $tempPath = storage_path('app/' . $path);
        $dir = dirname($tempPath);
        if (!is_dir($dir)) {
            mkdir($dir, 0755, true);
        }

        $writer->save($tempPath);

        return $path;
    }

    protected function addHeader(?Carbon $startDate, ?Carbon $endDate, ?User $user): void
    {
        // Set row height for logo row (row 1) - 75 points to fit logos properly
        // $this->sheet->getRowDimension(1)->setRowHeight(75);

        // Logo dimensions - consistent height, width auto-scales proportionally
        $logoHeight1 = 65;
        $logoHeight2 = 75;

        // Row 1: Logo left (B2) - company logo, positioned at left
        if ($this->company->logo && Storage::disk('public')->exists($this->company->logo)) {
            try {
                $drawing = new Drawing();
                $drawing->setName('Logo Kiri');
                $drawing->setDescription('Company Logo');
                $drawing->setPath(Storage::disk('public')->path($this->company->logo));
                $drawing->setHeight($logoHeight1);
                $drawing->setResizeProportional(true);  // Keep aspect ratio
                $drawing->setCoordinates('B2');
                $drawing->setOffsetX(10);   // 10px from left edge
                $drawing->setOffsetY(8);    // 8px from top
                $drawing->setWorksheet($this->sheet);
            } catch (\Exception $e) {
                // Skip logo if error
            }
        }

        // Row 1: Logo right (G2) - secondary logo, positioned at right
        // Using G2 with offset to position it towards the right side of column G
        if ($this->company->secondary_logo && Storage::disk('public')->exists($this->company->secondary_logo)) {
            try {
                $drawing2 = new Drawing();
                $drawing2->setName('Logo Kanan');
                $drawing2->setDescription('Secondary Logo');
                $drawing2->setPath(Storage::disk('public')->path($this->company->secondary_logo));
                $drawing2->setHeight($logoHeight2);
                $drawing2->setResizeProportional(true);  // Keep aspect ratio - prevents cutting
                $drawing2->setCoordinates('G2');
                // $drawing2->setOffsetX(50);   // Push to the right within F-G columns
                $drawing2->setOffsetY(8);    // Same top offset as left logo
                $drawing2->setWorksheet($this->sheet);
            } catch (\Exception $e) {
                // Skip logo if error
            }
        }

        // Row 2-3: Main Title "LAPORAN KEGIATAN BULANAN" (merged A2:G3, center)
        $this->sheet->mergeCells('A2:G3');
        $this->sheet->setCellValue('A2', 'LAPORAN KEGIATAN BULANAN');
        $this->sheet->getStyle('A2')->applyFromArray([
            'font' => [
                'name' => $this->fontTitle,
                'bold' => true,
                'size' => 18,
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER,
            ],
        ]);

        // Row 4: Project Name (merged A4:G4)
        $this->sheet->mergeCells('A4:G4');
        $projectName = $this->company->project_name ?? 'PROJECT MANAGEMENT CONSULTANT (PMC)';
        $this->sheet->setCellValue('A4', $projectName);
        $this->sheet->getStyle('A4')->applyFromArray([
            'font' => [
                'name' => $this->fontLabel,
                'bold' => true,
                'size' => 10,
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
            ],
        ]);

        // Row 5: Company Name (merged A5:G5)
        $this->sheet->mergeCells('A5:G5');
        $this->sheet->setCellValue('A5', strtoupper($this->company->name));
        $this->sheet->getStyle('A5')->applyFromArray([
            'font' => [
                'name' => $this->fontLabel,
                'bold' => true,
                'size' => 10,
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
            ],
        ]);

        // Row 7: NAMA | Employee Name with border (B7:D7 merged like template)
        // Labels NOT bold as per reference
        $this->sheet->setCellValue('A7', 'NAMA');
        $this->sheet->getStyle('A7')->applyFromArray([
            'font' => ['name' => $this->fontLabel, 'size' => 10],
            'borders' => [
                'allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => $this->borderColor]],
            ],
        ]);
        $this->sheet->mergeCells('B7:D7');
        $this->sheet->setCellValue('B7', $user ? $user->name : '-');
        $this->sheet->getStyle('B7:D7')->applyFromArray([
            'font' => ['name' => $this->fontLabel, 'size' => 10],
            'borders' => [
                'allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => $this->borderColor]],
            ],
        ]);

        // Row 8: POSISI | Position with border (B8:D8 merged)
        $this->sheet->setCellValue('A8', 'POSISI');
        $this->sheet->getStyle('A8')->applyFromArray([
            'font' => ['name' => $this->fontLabel, 'size' => 10],
            'borders' => [
                'allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => $this->borderColor]],
            ],
        ]);
        $this->sheet->mergeCells('B8:D8');
        $position = $user && $user->position ? $user->position : ($user && $user->department ? $user->department : 'Karyawan');
        $this->sheet->setCellValue('B8', $position);
        $this->sheet->getStyle('B8:D8')->applyFromArray([
            'font' => ['name' => $this->fontLabel, 'size' => 10],
            'borders' => [
                'allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => $this->borderColor]],
            ],
        ]);

        // Row 9: BULAN | Month Year with border (B9:D9 merged)
        $this->sheet->setCellValue('A9', 'BULAN');
        $this->sheet->getStyle('A9')->applyFromArray([
            'font' => ['name' => $this->fontLabel, 'size' => 10],
            'borders' => [
                'allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => $this->borderColor]],
            ],
        ]);
        $this->sheet->mergeCells('B9:D9');
        $monthYear = '';
        if ($startDate) {
            $monthYear = $this->monthNames[$startDate->month] . ' ' . $startDate->year;
        } elseif ($endDate) {
            $monthYear = $this->monthNames[$endDate->month] . ' ' . $endDate->year;
        } else {
            $monthYear = $this->monthNames[Carbon::now()->month] . ' ' . Carbon::now()->year;
        }
        $this->sheet->setCellValue('B9', $monthYear);
        $this->sheet->getStyle('B9:D9')->applyFromArray([
            'font' => ['name' => $this->fontLabel, 'size' => 10],
            'borders' => [
                'allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => $this->borderColor]],
            ],
        ]);

        // Row 10: Empty row
        $this->currentRow = 11;
    }

    protected function addDataTable(Collection $attendances, ?Carbon $startDate, ?Carbon $endDate): void
    {
        $tableStartRow = $this->currentRow;

        // Table Headers (Row 11-12, merged vertically)
        $headers = [
            'A' => 'NO',
            'B' => 'TANGGAL',
            'C' => 'Hari',
            'D' => 'WAKTU',
            'E' => 'URAIAN',
            'F' => 'OUTPUT',
            'G' => 'KETERANGAN',
        ];

        // Merge header cells vertically (row 11-12)
        foreach (array_keys($headers) as $col) {
            $this->sheet->mergeCells("{$col}11:{$col}12");
        }

        // Set header values
        foreach ($headers as $col => $header) {
            $this->sheet->setCellValue("{$col}11", $header);
        }

        // Style headers - Dark Blue background (#003399), white text, bold, centered
        // Using Calibri font size 10 as per reference timesheet
        // Border biru sesuai rujukan
        $this->sheet->getStyle("A11:G12")->applyFromArray([
            'font' => [
                'name' => $this->fontHeader,
                'bold' => true,
                'size' => 10,
                'color' => ['rgb' => $this->headerTextColor],
            ],
            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'startColor' => ['rgb' => $this->headerBgColor],
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER,
                'wrapText' => true,
            ],
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['rgb' => $this->borderColor],
                ],
            ],
        ]);

        $this->currentRow = 13;

        // Get all working days in the period
        $workingDays = $this->getWorkingDaysInPeriod($startDate, $endDate);

        // Create attendance map by date
        $attendanceMap = [];
        foreach ($attendances as $attendance) {
            $dateKey = Carbon::parse($attendance->date)->format('Y-m-d');
            $attendanceMap[$dateKey] = $attendance;
        }

        // Data rows - iterate through all working days
        $no = 1;
        foreach ($workingDays as $workingDay) {
            $dateKey = $workingDay->format('Y-m-d');
            $attendance = $attendanceMap[$dateKey] ?? null;

            // Format date: 03-Nov-25
            $dateStr = $workingDay->format('d') . '-' . $this->shortMonthNames[$workingDay->month] . '-' . $workingDay->format('y');
            $dayName = $this->dayNames[$workingDay->format('l')] ?? $workingDay->format('l');

            if ($attendance) {
                // Has attendance data
                $checkIn = $attendance->check_in_time ? Carbon::parse($attendance->check_in_time)->format('H:i') : '';
                $checkOut = $attendance->check_out_time ? Carbon::parse($attendance->check_out_time)->format('H:i') : '';
                $timeRange = '';
                if ($checkIn && $checkOut) {
                    $timeRange = "{$checkIn} - {$checkOut}";
                } elseif ($checkIn) {
                    $timeRange = $checkIn;
                }

                // Get URAIAN and OUTPUT from activities table (not attendance notes)
                // Combine all activities for this attendance into one string
                $activities = $attendance->activities ?? collect();
                $activityTexts = $activities->pluck('activity')->filter()->toArray();
                $outputTexts = $activities->pluck('output')->filter()->toArray();

                $activity = implode("\n", $activityTexts);
                $output = implode("\n", $outputTexts);

                // Fallback to attendance notes if no activities exist
                if (empty($activity)) {
                    $activity = $attendance->check_in_notes ?? '';
                }
                if (empty($output)) {
                    $output = $attendance->check_out_notes ?? '';
                }

                // Get location and status
                $location = $attendance->check_in_address ?? '';
                $statusText = $this->formatStatus($attendance->status);
                $typeText = $this->formatType($attendance->type);

                // Format keterangan with business trip location name
                $keterangan = $this->formatKeterangan($attendance, $typeText, $statusText, $location);

                // Check if data is incomplete (missing time or activity)
                $isIncomplete = empty($timeRange) || empty($activity);

                $this->sheet->setCellValue("A{$this->currentRow}", $no);
                $this->sheet->setCellValue("B{$this->currentRow}", $dateStr);
                $this->sheet->setCellValue("C{$this->currentRow}", $dayName);
                $this->sheet->setCellValue("D{$this->currentRow}", $timeRange);
                $this->sheet->setCellValue("E{$this->currentRow}", $activity);
                $this->sheet->setCellValue("F{$this->currentRow}", $output);
                $this->sheet->setCellValue("G{$this->currentRow}", $keterangan);

                // Apply yellow highlight if incomplete
                if ($isIncomplete) {
                    $this->sheet->getStyle("A{$this->currentRow}:G{$this->currentRow}")->applyFromArray([
                        'fill' => [
                            'fillType' => Fill::FILL_SOLID,
                            'startColor' => ['rgb' => $this->yellowHighlight],
                        ],
                    ]);
                }
            } else {
                // No attendance data for this working day - highlight orange
                $this->sheet->setCellValue("A{$this->currentRow}", '');
                $this->sheet->setCellValue("B{$this->currentRow}", $dateStr);
                $this->sheet->setCellValue("C{$this->currentRow}", $dayName);
                $this->sheet->setCellValue("D{$this->currentRow}", '');
                $this->sheet->setCellValue("E{$this->currentRow}", '');
                $this->sheet->setCellValue("F{$this->currentRow}", '');
                $this->sheet->setCellValue("G{$this->currentRow}", '');

                // Apply orange highlight for missing working day
                $this->sheet->getStyle("A{$this->currentRow}:G{$this->currentRow}")->applyFromArray([
                    'fill' => [
                        'fillType' => Fill::FILL_SOLID,
                        'startColor' => ['rgb' => $this->orangeHighlight],
                    ],
                ]);
            }

            // Style data row - Using Arial Narrow size 11 as per reference timesheet
            // Border biru sesuai rujukan
            $this->sheet->getStyle("A{$this->currentRow}:G{$this->currentRow}")->applyFromArray([
                'font' => [
                    'name' => $this->fontData,
                    'size' => 11,
                ],
                'alignment' => [
                    'vertical' => Alignment::VERTICAL_TOP,
                    'wrapText' => true,
                ],
                'borders' => [
                    'allBorders' => [
                        'borderStyle' => Border::BORDER_THIN,
                        'color' => ['rgb' => $this->borderColor],
                    ],
                ],
            ]);

            // Set row height to auto
            $this->sheet->getRowDimension($this->currentRow)->setRowHeight(-1);

            // Only increment $no for rows with attendance data
            if ($attendance) {
                $no++;
            }
            $this->currentRow++;
        }

        $this->tableEndRow = $this->currentRow - 1;
    }

    protected int $tableEndRow = 0;

    /**
     * Get all working days (Monday-Friday) in the given period
     */
    protected function getWorkingDaysInPeriod(?Carbon $startDate, ?Carbon $endDate): array
    {
        $workingDays = [];

        if (!$startDate || !$endDate) {
            return $workingDays;
        }

        $current = $startDate->copy();
        while ($current->lte($endDate)) {
            // Only include weekdays (Monday-Friday)
            if (!$current->isWeekend()) {
                $workingDays[] = $current->copy();
            }
            $current->addDay();
        }

        return $workingDays;
    }

    protected function addFooter(Collection $attendances, ?Carbon $startDate, ?Carbon $endDate): void
    {
        // Skip 2 empty rows after table
        $this->currentRow += 2;

        // Get month/year for summary
        $month = $startDate ? $startDate->month : Carbon::now()->month;
        $year = $startDate ? $startDate->year : Carbon::now()->year;
        $monthName = $this->monthNames[$month];

        // Count working days in the month
        $workingDays = $this->countWorkingDays($month, $year);
        $attendanceCount = $attendances->count();

        // Summary rows - Using Calibri font size 11 as per reference
        $this->sheet->setCellValue("A{$this->currentRow}", "Jumlah hari kerja bulan {$monthName} {$year}: {$workingDays} hk");
        $this->sheet->getStyle("A{$this->currentRow}")->applyFromArray([
            'font' => ['name' => $this->fontHeader, 'size' => 11],
        ]);
        $this->currentRow++;

        $this->sheet->setCellValue("A{$this->currentRow}", "Jumlah hari kehadiran bulan {$monthName} {$year}: {$attendanceCount} hk");
        $this->sheet->getStyle("A{$this->currentRow}")->applyFromArray([
            'font' => ['name' => $this->fontHeader, 'size' => 11],
        ]);
        $this->currentRow++;

        $this->sheet->setCellValue("A{$this->currentRow}", "Prosentase hari kerja: {$attendanceCount}/{$workingDays} x 100%");
        $this->sheet->getStyle("A{$this->currentRow}")->applyFromArray([
            'font' => ['name' => $this->fontHeader, 'size' => 11],
        ]);
        $this->currentRow++;

        // Skip rows before signature
        $this->currentRow += 2;

        // Signature section
        $signatureRow = $this->currentRow;

        // Left side: "Mengetahui,"
        $this->sheet->mergeCells("A{$signatureRow}:C{$signatureRow}");
        $this->sheet->setCellValue("A{$signatureRow}", 'Mengetahui,');
        $this->sheet->getStyle("A{$signatureRow}")->applyFromArray([
            'font' => ['name' => $this->fontLabel, 'bold' => true, 'size' => 10],
            'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
        ]);

        // Right side: "Jakarta, [Date]"
        $signatureDate = Carbon::now()->format('j') . ' ' . $this->monthNames[Carbon::now()->month] . ' ' . Carbon::now()->year;
        $city = $this->company->city ?? 'Jakarta';
        $this->sheet->mergeCells("F{$signatureRow}:G{$signatureRow}");
        $this->sheet->setCellValue("F{$signatureRow}", "{$city}, {$signatureDate}");
        $this->sheet->getStyle("F{$signatureRow}")->applyFromArray([
            'font' => ['name' => $this->fontLabel, 'bold' => true, 'size' => 10],
            'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
        ]);

        // Space for signatures (5-6 rows)
        $this->currentRow += 6;

        // Left signature name (Team Leader / Manager)
        $this->sheet->mergeCells("A{$this->currentRow}:C{$this->currentRow}");
        $teamLeaderName = $this->company->team_leader_name ?? '..............................';
        $this->sheet->setCellValue("A{$this->currentRow}", $teamLeaderName);
        $this->sheet->getStyle("A{$this->currentRow}")->applyFromArray([
            'font' => ['name' => $this->fontLabel, 'bold' => true, 'size' => 10],
            'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
        ]);

        // Right signature name (Employee name)
        $this->sheet->mergeCells("F{$this->currentRow}:G{$this->currentRow}");
        $employeeName = $this->user ? $this->user->name : '..............................';
        $this->sheet->setCellValue("F{$this->currentRow}", $employeeName);
        $this->sheet->getStyle("F{$this->currentRow}")->applyFromArray([
            'font' => ['name' => $this->fontLabel, 'bold' => true, 'size' => 10],
            'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
        ]);

        $this->currentRow++;

        // Left signature position
        $this->sheet->mergeCells("A{$this->currentRow}:C{$this->currentRow}");
        $teamLeaderPosition = $this->company->team_leader_position ?? 'Team Leader';
        $this->sheet->setCellValue("A{$this->currentRow}", $teamLeaderPosition);
        $this->sheet->getStyle("A{$this->currentRow}")->applyFromArray([
            'font' => ['name' => $this->fontLabel, 'bold' => true, 'size' => 10],
            'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
        ]);

        // Right signature position (Employee position)
        $this->sheet->mergeCells("F{$this->currentRow}:G{$this->currentRow}");
        $employeePosition = $this->user && $this->user->position ? $this->user->position : 'Karyawan';
        $this->sheet->setCellValue("F{$this->currentRow}", $employeePosition);
        $this->sheet->getStyle("F{$this->currentRow}")->applyFromArray([
            'font' => ['name' => $this->fontLabel, 'bold' => true, 'size' => 10],
            'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
        ]);
    }

    protected function countWorkingDays(int $month, int $year): int
    {
        $startDate = Carbon::create($year, $month, 1);
        $endDate = $startDate->copy()->endOfMonth();
        $workingDays = 0;

        while ($startDate->lte($endDate)) {
            if (!$startDate->isWeekend()) {
                $workingDays++;
            }
            $startDate->addDay();
        }

        return $workingDays;
    }

    protected function formatStatus(?string $status): string
    {
        return match ($status) {
            'present' => 'HADIR',
            'late' => 'TERLAMBAT',
            'early_leave' => 'PULANG AWAL',
            'absent' => 'TIDAK HADIR',
            'sick' => 'SAKIT',
            'leave' => 'CUTI',
            'permit' => 'IZIN',
            default => 'HADIR',
        };
    }

    protected function formatType(?string $type): string
    {
        return match ($type) {
            'office' => 'Kantor',
            'remote' => 'Remote',
            'wfh' => 'WFH',
            'business_trip' => 'Dinas',
            default => '',
        };
    }

    /**
     * Format keterangan dengan nama lokasi (unified location atau legacy)
     * Format: "Lokasi - STATUS" sesuai template timesheet
     * Contoh: "BPKP Pusat - HADIR", "Dinas - HADIR", "Pusdiklatwas Gadog - HADIR"
     */
    protected function formatKeterangan(Attendance $attendance, string $typeText, string $statusText, string $location): string
    {
        // Priority 1: Cek unified location (new system)
        $unifiedLocation = $attendance->location;
        if ($unifiedLocation && $unifiedLocation->name) {
            $locationName = $unifiedLocation->name;

            // Untuk business_trip dengan lokasi eksternal, tampilkan nama lokasi
            if ($attendance->type === 'business_trip') {
                return "{$locationName} - {$statusText}";
            }

            // Untuk office, tampilkan nama kantor
            if ($attendance->type === 'office') {
                return "{$locationName} - {$statusText}";
            }

            // Default: tampilkan nama lokasi
            return "{$locationName} - {$statusText}";
        }

        // Priority 2: Legacy - business_trip dengan businessTripLocation
        if ($attendance->type === 'business_trip') {
            $businessTripLocation = $attendance->businessTripLocation;

            if ($businessTripLocation && $businessTripLocation->name) {
                return "{$businessTripLocation->name} - {$statusText}";
            }

            // Tidak ada lokasi spesifik, tampilkan "Dinas"
            return "Dinas - {$statusText}";
        }

        // Priority 3: Legacy - office dengan office relationship
        if ($attendance->type === 'office') {
            $office = $attendance->office;

            if ($office && $office->name) {
                return "{$office->name} - {$statusText}";
            }

            // Default untuk kantor tanpa nama lokasi
            return "Kantor - {$statusText}";
        }

        // Priority 4: Tipe lain (remote, wfh, dll)
        if ($typeText && !empty($typeText)) {
            return "{$typeText} - {$statusText}";
        }

        // Fallback: hanya status
        return $statusText;
    }

    protected function generateFilename(?Carbon $startDate, ?Carbon $endDate, ?User $user): string
    {
        $parts = ['laporan_kehadiran'];

        if ($user) {
            $parts[] = str_replace(' ', '_', strtolower($user->name));
        }

        if ($startDate && $endDate) {
            $parts[] = $startDate->format('Ymd') . '-' . $endDate->format('Ymd');
        } else {
            $parts[] = Carbon::now()->format('Ymd_His');
        }

        return implode('_', $parts) . '.xlsx';
    }
}
