<?php

namespace App\Services;

use App\Models\Activity;
use App\Models\Attendance;
use App\Models\Location;
use App\Models\User;
use Carbon\Carbon;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Facades\Log;

class ExcelImportService
{
    // Header mapping for the exact template format
    // Template columns: NO | TANGGAL | Hari | WAKTU | URAIAN | OUTPUT | KETERANGAN
    protected array $headerMapping = [
        // Template format columns
        'no' => 'no',
        'tanggal' => 'date',
        'hari' => 'day_name',
        'waktu' => 'time_range',
        'uraian' => 'activity',          // Maps to check_in_notes (aktivitas)
        'output' => 'output',             // Maps to check_out_notes
        'keterangan' => 'location',       // Maps to check_in_address (lokasi)

        // Legacy/alternative column names
        'nama karyawan' => 'user_name',
        'nama' => 'user_name',
        'nip' => 'employee_id',
        'jam masuk' => 'check_in_time',
        'jam pulang' => 'check_out_time',
        'durasi' => 'work_duration',
        'durasi kerja' => 'work_duration',
        'status' => 'status',
        'tipe' => 'type',
        'alamat masuk' => 'check_in_address',
        'alamat pulang' => 'check_out_address',
        'catatan' => 'check_in_notes',
        'catatan masuk' => 'check_in_notes',
        'catatan pulang' => 'check_out_notes',
        'aktivitas' => 'activity',
        'lokasi' => 'location',
    ];

    // Required columns for template format (minimal)
    protected array $requiredColumns = ['date'];

    // Info extracted from header section
    protected ?string $employeeName = null;
    protected ?string $employeePosition = null;
    protected ?string $reportMonth = null;

    public function import(string $filePath, ?int $userId = null): array
    {
        $result = [
            'success' => 0,
            'failed' => 0,
            'errors' => [],
            'skipped_rows' => 0,
            'employee_name' => null,
            'month' => null,
        ];

        try {
            $spreadsheet = IOFactory::load($filePath);
            $sheet = $spreadsheet->getActiveSheet();
            $rows = $sheet->toArray();

            // Extract employee info from header section (rows 7-9)
            $this->extractHeaderInfo($rows);
            $result['employee_name'] = $this->employeeName;
            $result['month'] = $this->reportMonth;

            // Find user - either by userId parameter or by name from header
            $user = null;
            if ($userId) {
                $user = User::find($userId);
            } elseif ($this->employeeName) {
                $user = User::where('name', 'like', "%{$this->employeeName}%")->first();
            }

            if (!$user) {
                $result['errors'][] = 'User tidak ditemukan. Nama dari file: ' . ($this->employeeName ?? 'tidak terdeteksi');
                return $result;
            }

            // Find header row (row 11 in template, but auto-detect)
            $headerRowIndex = $this->findHeaderRow($rows);

            if ($headerRowIndex === null) {
                $result['errors'][] = 'Header tabel tidak ditemukan. Pastikan ada baris dengan kolom: NO, TANGGAL, WAKTU, URAIAN';
                return $result;
            }

            $result['skipped_rows'] = $headerRowIndex;

            // Map headers to column indices
            $columnMap = $this->mapHeaders($rows[$headerRowIndex]);

            // Skip the merged header row (row 12 in template)
            $dataStartRow = $headerRowIndex + 1;
            // Check if next row is empty or part of merged header
            if (isset($rows[$dataStartRow]) && $this->isEmptyRow($rows[$dataStartRow])) {
                $dataStartRow++;
            }

            // Process data rows
            $consecutiveEmptyOrSummary = 0;
            $maxConsecutiveEmpty = 5; // Stop after 5 consecutive empty/summary rows

            for ($i = $dataStartRow; $i < count($rows); $i++) {
                $row = $rows[$i];

                // Skip empty rows
                if ($this->isEmptyRow($row)) {
                    $consecutiveEmptyOrSummary++;
                    if ($consecutiveEmptyOrSummary >= $maxConsecutiveEmpty) {
                        break; // End of data section
                    }
                    continue;
                }

                // Check for summary/footer rows
                if ($this->isSummaryRow($row)) {
                    $consecutiveEmptyOrSummary++;
                    if ($consecutiveEmptyOrSummary >= $maxConsecutiveEmpty) {
                        break; // End of data section
                    }
                    continue; // Skip but don't stop - might have more data below
                }

                // Reset counter when we find a valid data row
                $consecutiveEmptyOrSummary = 0;

                try {
                    $data = $this->extractRowData($row, $columnMap);

                    // Skip if no valid date
                    if (empty($data['date'])) {
                        continue;
                    }

                    $attendance = $this->createOrUpdateAttendance($data, $user);

                    if ($attendance) {
                        $result['success']++;
                    } else {
                        $result['failed']++;
                        $result['errors'][] = "Baris " . ($i + 1) . ": Gagal menyimpan data";
                    }
                } catch (\Exception $e) {
                    $result['failed']++;
                    $result['errors'][] = "Baris " . ($i + 1) . ": " . $e->getMessage();
                }
            }
        } catch (\Exception $e) {
            $result['errors'][] = 'Error membaca file: ' . $e->getMessage();
            Log::error('Excel import error', ['error' => $e->getMessage(), 'trace' => $e->getTraceAsString()]);
        }

        return $result;
    }

    /**
     * Extract employee information from header section (rows 7-9)
     * Row 7: NAMA | [Employee Name]
     * Row 8: POSISI | [Position]
     * Row 9: BULAN | [Month Year]
     */
    protected function extractHeaderInfo(array $rows): void
    {
        // Check rows 6-10 (0-indexed: 5-9) for header info
        for ($i = 5; $i <= 10 && $i < count($rows); $i++) {
            $row = $rows[$i];
            $firstCell = strtolower(trim((string)($row[0] ?? '')));
            $secondCell = trim((string)($row[1] ?? ''));

            if ($firstCell === 'nama' && !empty($secondCell)) {
                $this->employeeName = $secondCell;
            } elseif ($firstCell === 'posisi' && !empty($secondCell)) {
                $this->employeePosition = $secondCell;
            } elseif ($firstCell === 'bulan' && !empty($secondCell)) {
                $this->reportMonth = $secondCell;
            }
        }
    }

    protected function findHeaderRow(array $rows): ?int
    {
        foreach ($rows as $index => $row) {
            $rowText = strtolower(implode(' ', array_filter(array_map('strval', $row))));

            // Check for template header keywords: NO, TANGGAL, WAKTU, URAIAN
            $hasNo = str_contains($rowText, 'no');
            $hasDate = str_contains($rowText, 'tanggal');
            $hasTime = str_contains($rowText, 'waktu') || str_contains($rowText, 'jam');
            $hasActivity = str_contains($rowText, 'uraian') || str_contains($rowText, 'aktivitas');

            // Template format
            if ($hasNo && $hasDate && ($hasTime || $hasActivity)) {
                return $index;
            }

            // Legacy format
            $hasName = str_contains($rowText, 'nama') || str_contains($rowText, 'karyawan');
            if ($hasDate && $hasName && $hasTime) {
                return $index;
            }
        }

        return null;
    }

    protected function mapHeaders(array $headerRow): array
    {
        $columnMap = [];

        foreach ($headerRow as $colIndex => $header) {
            if ($header === null) {
                continue;
            }

            $normalizedHeader = strtolower(trim((string)$header));

            if (isset($this->headerMapping[$normalizedHeader])) {
                $columnMap[$this->headerMapping[$normalizedHeader]] = $colIndex;
            }
        }

        return $columnMap;
    }

    protected function isEmptyRow(array $row): bool
    {
        foreach ($row as $cell) {
            if ($cell !== null && trim((string) $cell) !== '') {
                return false;
            }
        }
        return true;
    }

    protected function isSummaryRow(array $row): bool
    {
        $firstCell = strtolower(trim((string) ($row[0] ?? '')));
        $secondCell = strtolower(trim((string) ($row[1] ?? '')));

        // Check for summary keywords - only in first cell (column NO)
        // These indicate end of data section
        $endKeywords = [
            'jumlah',
            'ringkasan',
            'total',
            'mengetahui',
            'prosentase',
            'honorarium',
        ];

        foreach ($endKeywords as $keyword) {
            if (str_contains($firstCell, $keyword)) {
                return true;
            }
        }

        // Check for footer keywords anywhere in first 3 columns
        // But only if first cell is empty or non-numeric (not a row number)
        if (!is_numeric($firstCell) || empty($firstCell)) {
            $footerKeywords = ['dicetak', 'dibuat', 'tanda tangan', 'ttd'];
            $rowText = strtolower(implode(' ', array_slice(array_map('strval', $row), 0, 3)));
            foreach ($footerKeywords as $keyword) {
                if (str_contains($rowText, $keyword)) {
                    return true;
                }
            }
        }

        return false;
    }

    protected function extractRowData(array $row, array $columnMap): array
    {
        $data = [];

        foreach ($columnMap as $field => $colIndex) {
            $data[$field] = $row[$colIndex] ?? null;
        }

        return $data;
    }

    protected function createOrUpdateAttendance(array $data, User $user): ?Attendance
    {
        // Parse date
        $date = $this->parseDate($data['date'] ?? null);
        if (!$date) {
            throw new \Exception('Format tanggal tidak valid: ' . ($data['date'] ?? 'kosong'));
        }

        // Parse time range (format: "07:33 - 16:47")
        $checkInTime = null;
        $checkOutTime = null;

        if (!empty($data['time_range'])) {
            $times = $this->parseTimeRange($data['time_range'], $date);
            $checkInTime = $times['check_in'];
            $checkOutTime = $times['check_out'];
        } else {
            // Try individual time columns
            $checkInTime = $this->parseTime($data['check_in_time'] ?? null, $date);
            $checkOutTime = $this->parseTime($data['check_out_time'] ?? null, $date);
        }

        // Get activity (uraian) and output
        $activityText = $data['activity'] ?? $data['check_in_notes'] ?? null;
        $outputText = $data['output'] ?? $data['check_out_notes'] ?? null;

        // Get location (keterangan) - extract status from it
        $locationRaw = $data['location'] ?? $data['check_in_address'] ?? '';
        $locationParts = $this->parseLocationAndStatus($locationRaw);
        $locationName = $locationParts['location'];
        $status = $locationParts['status'];
        $type = $locationParts['type'];

        // Override status if explicitly provided
        if (!empty($data['status'])) {
            $status = $this->mapStatus($data['status'], $checkInTime, $user);
        }

        // Override type if explicitly provided
        if (!empty($data['type'])) {
            $type = $this->mapType($data['type']);
        }

        // Calculate work duration
        $workDuration = null;
        if ($checkInTime && $checkOutTime) {
            $workDuration = Carbon::parse($checkInTime)->diffInMinutes(Carbon::parse($checkOutTime));
        }

        // Try to find matching location for coordinates
        $checkInLatitude = null;
        $checkInLongitude = null;
        $checkOutLatitude = null;
        $checkOutLongitude = null;
        $locationId = null;

        // Find matching location from KETERANGAN using Location model
        $matchedLocation = $this->findMatchingLocation($locationRaw, $user->company_id);

        if ($matchedLocation && $matchedLocation->latitude && $matchedLocation->longitude) {
            $locationId = $matchedLocation->id;

            // Determine type based on location and KETERANGAN
            // Logic:
            // 1. If KETERANGAN explicitly contains "Dinas" → business_trip
            // 2. If location type is 'headquarters' or 'bpkp' → office (these are BPKP offices)
            // 3. If location is for business trip (pemda, external) → business_trip
            $isExplicitDinas = stripos($locationRaw, 'Dinas') !== false;
            $isOfficeLocation = in_array($matchedLocation->type, ['headquarters', 'bpkp']);

            if ($isExplicitDinas && !$isOfficeLocation) {
                // Explicit "Dinas" and going to non-office location
                $type = 'business_trip';
            } elseif ($isOfficeLocation) {
                // BPKP offices (headquarters or branches) are always 'office' type
                $type = 'office';
            } elseif ($matchedLocation->type === 'pemda' || $matchedLocation->type === 'other') {
                // Pemda or other external locations are business trips
                $type = 'business_trip';
            }
            // Otherwise keep the type from parseLocationAndStatus

            // Generate random coordinates based on type:
            // - Dinas (business_trip): 5-50 meters
            // - Kantor (office): 5-30 meters
            if ($type === 'business_trip') {
                $checkInCoords = \App\Models\SuratTugas::applyRandomOffsetDinas(
                    (float) $matchedLocation->latitude,
                    (float) $matchedLocation->longitude
                );
            } else {
                $checkInCoords = \App\Models\SuratTugas::applyRandomOffsetKantor(
                    (float) $matchedLocation->latitude,
                    (float) $matchedLocation->longitude
                );
            }
            $checkInLatitude = $checkInCoords['latitude'];
            $checkInLongitude = $checkInCoords['longitude'];

            // Also generate different random coords for check-out (if exists)
            if ($checkOutTime) {
                if ($type === 'business_trip') {
                    $checkOutCoords = \App\Models\SuratTugas::applyRandomOffsetDinas(
                        (float) $matchedLocation->latitude,
                        (float) $matchedLocation->longitude
                    );
                } else {
                    $checkOutCoords = \App\Models\SuratTugas::applyRandomOffsetKantor(
                        (float) $matchedLocation->latitude,
                        (float) $matchedLocation->longitude
                    );
                }
                $checkOutLatitude = $checkOutCoords['latitude'];
                $checkOutLongitude = $checkOutCoords['longitude'];
            }

            // Use location name as address
            $locationName = $matchedLocation->name;
        }

        // Create or update attendance
        $attendance = Attendance::updateOrCreate(
            [
                'user_id' => $user->id,
                'date' => $date,
            ],
            [
                'company_id' => $user->company_id,
                'type' => $type,
                'check_in_time' => $checkInTime,
                'check_in_address' => $locationName,
                'check_in_latitude' => $checkInLatitude,
                'check_in_longitude' => $checkInLongitude,
                'check_out_time' => $checkOutTime,
                'check_out_latitude' => $checkOutLatitude,
                'check_out_longitude' => $checkOutLongitude,
                'work_duration' => $workDuration,
                'status' => $status,
                'location_id' => $locationId,
            ]
        );

        // Create Activity record if there's URAIAN or OUTPUT
        if ($attendance && (!empty($activityText) || !empty($outputText))) {
            $this->createOrUpdateActivity($attendance, $activityText, $outputText, $checkInTime, $checkOutTime);
        }

        return $attendance;
    }

    /**
     * Create or update Activity record from URAIAN and OUTPUT columns
     */
    protected function createOrUpdateActivity(Attendance $attendance, ?string $activityText, ?string $outputText, ?string $startTime, ?string $endTime): void
    {
        // Skip if both activity and output are empty
        if (empty($activityText) && empty($outputText)) {
            return;
        }

        // Skip if no start_time available (e.g., SAKIT/CUTI records)
        if (empty($startTime)) {
            return;
        }

        // Clean up activity text
        $activityText = $activityText ? trim($activityText) : null;
        $outputText = $outputText ? trim($outputText) : null;

        // Parse start_time and end_time to use the attendance date (not today)
        $attendanceDate = Carbon::parse($attendance->date)->format('Y-m-d');
        $parsedStartTime = Carbon::parse($startTime)->format('H:i:s');
        $parsedEndTime = $endTime ? Carbon::parse($endTime)->format('H:i:s') : null;

        // Combine attendance date with the time
        $finalStartTime = $attendanceDate . ' ' . $parsedStartTime;
        $finalEndTime = $parsedEndTime ? $attendanceDate . ' ' . $parsedEndTime : null;

        // Create or update single activity for this attendance
        Activity::updateOrCreate(
            [
                'attendance_id' => $attendance->id,
                'user_id' => $attendance->user_id,
                'date' => $attendance->date,
            ],
            [
                'start_time' => $finalStartTime,
                'end_time' => $finalEndTime,
                'activity' => $activityText,
                'output' => $outputText,
            ]
        );
    }

    /**
     * Parse time range format "07:33 - 16:47"
     */
    protected function parseTimeRange(string $timeRange, string $date): array
    {
        $result = ['check_in' => null, 'check_out' => null];

        // Split by " - " or "-"
        $parts = preg_split('/\s*-\s*/', trim($timeRange));

        if (count($parts) >= 1 && !empty($parts[0]) && $parts[0] !== '-') {
            $result['check_in'] = $this->parseTime($parts[0], $date);
        }

        if (count($parts) >= 2 && !empty($parts[1]) && $parts[1] !== '-') {
            $result['check_out'] = $this->parseTime($parts[1], $date);
        }

        return $result;
    }

    /**
     * Parse location and extract status/type from it
     * Example: "BPKP Pusat - HADIR" -> location: "BPKP Pusat", status: "present"
     * Example: "Dinas - HADIR" -> location: "Dinas", type: "business_trip", status: "present"
     */
    protected function parseLocationAndStatus(string $raw): array
    {
        $location = trim($raw);
        $status = 'present';
        $type = 'office';

        // Status keywords to extract
        $statusKeywords = [
            'HADIR' => 'present',
            'TERLAMBAT' => 'late',
            'TIDAK HADIR' => 'absent',
            'SAKIT' => 'sick',
            'CUTI' => 'leave',
            'IZIN' => 'permit',
        ];

        // Type keywords
        $typeKeywords = [
            'Dinas' => 'business_trip',
            'Remote' => 'remote',
            'WFH' => 'remote',
            'Kantor' => 'office',
        ];

        // Check for status at the end
        foreach ($statusKeywords as $keyword => $statusValue) {
            if (str_contains(strtoupper($location), $keyword)) {
                $status = $statusValue;
                // Remove status from location
                $location = preg_replace('/\s*-?\s*' . preg_quote($keyword, '/') . '\s*/i', '', $location);
                break;
            }
        }

        // Check for type
        foreach ($typeKeywords as $keyword => $typeValue) {
            if (stripos($location, $keyword) !== false) {
                $type = $typeValue;
                break;
            }
        }

        return [
            'location' => trim($location, ' -'),
            'status' => $status,
            'type' => $type,
        ];
    }

    protected function parseDate($value): ?string
    {
        if (empty($value)) {
            return null;
        }

        // Handle Excel serial date (numeric value)
        if (is_numeric($value) && $value > 1) {
            // Excel serial date: days since 1899-12-30
            $unixTimestamp = ($value - 25569) * 86400;
            return Carbon::createFromTimestamp($unixTimestamp)->format('Y-m-d');
        }

        $stringValue = trim((string)$value);

        // Convert Indonesian month names to English
        $indonesianMonths = [
            'januari' => 'January', 'februari' => 'February', 'maret' => 'March',
            'april' => 'April', 'mei' => 'May', 'juni' => 'June',
            'juli' => 'July', 'agustus' => 'August', 'september' => 'September',
            'oktober' => 'October', 'november' => 'November', 'desember' => 'December',
            'jan' => 'Jan', 'feb' => 'Feb', 'mar' => 'Mar', 'apr' => 'Apr',
            'jun' => 'Jun', 'jul' => 'Jul', 'agu' => 'Aug', 'agt' => 'Aug',
            'sep' => 'Sep', 'okt' => 'Oct', 'nov' => 'Nov', 'des' => 'Dec',
        ];

        $normalizedValue = $stringValue;
        foreach ($indonesianMonths as $id => $en) {
            $normalizedValue = preg_replace('/\b' . $id . '\b/i', $en, $normalizedValue);
        }

        // Handle various date formats
        $formats = [
            'd-M-y',    // 03-Nov-25 (template format)
            'd-M-Y',    // 03-Nov-2025
            'd/m/Y',
            'd-m-Y',
            'Y-m-d',
            'd/m/y',
            'd-m-y',
            'd F Y',    // 03 May 2025
            'd M Y',    // 03 May 25
            'd-F-y',
            'd-F-Y',
            'j F Y',    // 3 May 2025 (no leading zero)
            'j M Y',    // 3 May 25
            'j-M-y',    // 3-May-25
            'j-M-Y',    // 3-May-2025
        ];

        foreach ($formats as $format) {
            try {
                return Carbon::createFromFormat($format, $normalizedValue)->format('Y-m-d');
            } catch (\Exception $e) {
                continue;
            }
        }

        // Try Carbon parse as fallback
        try {
            return Carbon::parse($normalizedValue)->format('Y-m-d');
        } catch (\Exception $e) {
            return null;
        }
    }

    protected function parseTime($value, string $date): ?string
    {
        if (empty($value) || $value === '-') {
            return null;
        }

        // Generate random seconds (0-59) to add variation
        $randomSeconds = mt_rand(0, 59);

        // Handle format desimal waktu seperti "16.5" (16:50) atau "16.05" (16:05)
        // Format ini muncul ketika user mengetik waktu dengan titik sebagai pemisah jam:menit
        // Logika: 
        // - 16.5 = 16:50 (1 digit menit = puluhan menit, jadi 5 = 50 menit)
        // - 16.05 = 16:05 (2 digit menit = menit langsung, jadi 05 = 5 menit)
        // - 16.50 = 16:50 (2 digit menit = menit langsung, jadi 50 = 50 menit)
        $valueStr = trim((string) $value);
        if (preg_match('/^(\d{1,2})\.(\d{1,2})$/', $valueStr, $matches)) {
            $hours = (int) $matches[1];
            $minutesPart = $matches[2];
            
            // Jika bagian menit 1 digit, kalikan dengan 10 (5 → 50 menit)
            // Jika bagian menit 2 digit, gunakan langsung (05 → 5 menit, 50 → 50 menit)
            if (strlen($minutesPart) == 1) {
                $minutes = (int) $minutesPart * 10;
            } else {
                $minutes = (int) $minutesPart;
            }
            
            // Validasi: jam harus 0-23, menit harus 0-59
            if ($hours >= 0 && $hours < 24 && $minutes >= 0 && $minutes < 60) {
                return Carbon::parse($date)->setTime($hours, $minutes, $randomSeconds)->format('Y-m-d H:i:s');
            }
        }

        // Handle Excel numeric time (0.354166667 for 08:30)
        if (is_numeric($value) && $value < 1) {
            $totalSeconds = round($value * 86400);
            $hours = floor($totalSeconds / 3600);
            $minutes = floor(($totalSeconds % 3600) / 60);
            // Use random seconds instead of calculated
            return Carbon::parse($date)->setTime($hours, $minutes, $randomSeconds)->format('Y-m-d H:i:s');
        }

        // Handle Excel datetime numeric
        if (is_numeric($value) && $value > 1) {
            $unixTimestamp = ($value - 25569) * 86400;
            $dt = Carbon::createFromTimestamp($unixTimestamp);
            
            // Check if the parsed date is in 1900 (Excel epoch) - this indicates a time-only value
            // that was incorrectly stored as a datetime. Excel stores time-only values as days since
            // 1900-01-01, so values like 14.3 (14 days + 0.3 day = 7:12 AM) become 1900-01-14 07:12:00.
            // We need to extract just the time portion and combine it with the actual attendance date.
            if ($dt->year === 1900) {
                // This is a time-only value, extract time and combine with actual date
                $hours = $dt->hour;
                $minutes = $dt->minute;
                $seconds = $dt->second;
                return Carbon::parse($date)->setTime($hours, $minutes, $seconds ?: $randomSeconds)->format('Y-m-d H:i:s');
            }
            
            // Valid datetime, use as-is
            // Add random seconds
            return $dt->setSecond($randomSeconds)->format('Y-m-d H:i:s');
        }

        $value = trim((string) $value);

        // Already full datetime
        if (preg_match('/^\d{4}-\d{2}-\d{2}/', $value)) {
            $dt = Carbon::parse($value);
            
            // Check if the parsed date is in 1900 (Excel epoch) - this indicates a time-only value
            // that was incorrectly stored as a datetime. Extract just the time and use the actual date.
            if ($dt->year === 1900) {
                // This is a time-only value, extract time and combine with actual date
                $hours = $dt->hour;
                $minutes = $dt->minute;
                $seconds = $dt->second;
                return Carbon::parse($date)->setTime($hours, $minutes, $seconds ?: $randomSeconds)->format('Y-m-d H:i:s');
            }
            
            // Add random seconds if seconds are 0
            if ($dt->second === 0) {
                $dt->setSecond($randomSeconds);
            }
            return $dt->format('Y-m-d H:i:s');
        }

        // Time only formats (HH:MM or HH:MM:SS)
        if (preg_match('/^\d{1,2}:\d{2}(:\d{2})?$/', $value)) {
            $dt = Carbon::parse($date . ' ' . $value);
            // Add random seconds if format is HH:MM (no seconds) or ends with :00
            $hasNoSeconds = !preg_match('/^\d{1,2}:\d{2}:\d{2}$/', $value);
            $endsWithZeroSeconds = preg_match('/:00$/', $value);
            if ($hasNoSeconds || $endsWithZeroSeconds) {
                $dt->setSecond($randomSeconds);
            }
            return $dt->format('Y-m-d H:i:s');
        }

        // Try fallback - parse as datetime string
        try {
            // First try parsing as standalone datetime
            $dt = Carbon::parse($value);
            
            // Check if the parsed date is in 1900 (Excel epoch) - this indicates a time-only value
            if ($dt->year === 1900) {
                // This is a time-only value, extract time and combine with actual date
                $hours = $dt->hour;
                $minutes = $dt->minute;
                $seconds = $dt->second;
                return Carbon::parse($date)->setTime($hours, $minutes, $seconds ?: $randomSeconds)->format('Y-m-d H:i:s');
            }
            
            // Valid datetime, use as-is
            $dt->setSecond($randomSeconds);
            return $dt->format('Y-m-d H:i:s');
        } catch (\Exception $e) {
            // If parsing as standalone datetime fails, try combining with date
            try {
                $dt = Carbon::parse($date . ' ' . $value);
                $dt->setSecond($randomSeconds);
                return $dt->format('Y-m-d H:i:s');
            } catch (\Exception $e2) {
                return null;
            }
        }
    }

    protected function mapStatus(?string $status, ?string $checkInTime, User $user): string
    {
        if (empty($status) || $status === '-') {
            if ($checkInTime) {
                $checkIn = Carbon::parse($checkInTime);
                $workStart = Carbon::parse($user->company?->work_start_time ?? '08:00:00');
                return $checkIn->gt($workStart) ? 'late' : 'present';
            }
            return 'present';
        }

        return match (strtolower(trim($status))) {
            'hadir', 'present' => 'present',
            'terlambat', 'late' => 'late',
            'pulang awal', 'early_leave', 'early' => 'early_leave',
            'tidak hadir', 'absent' => 'absent',
            'sakit', 'sick' => 'sick',
            'cuti', 'leave' => 'leave',
            'izin', 'permit' => 'permit',
            default => 'present',
        };
    }

    protected function mapType(?string $type): string
    {
        if (empty($type) || $type === '-') {
            return 'office';
        }

        return match (strtolower(trim($type))) {
            'kantor', 'office' => 'office',
            'remote', 'wfh' => 'remote',
            'dinas', 'business_trip' => 'business_trip',
            default => 'office',
        };
    }

    /**
     * Find matching location by name using Location model
     * Supports partial matching and common variations
     *
     * @param string $locationName Location name from KETERANGAN column
     * @param int $companyId Company ID to filter locations
     * @return Location|null
     */
    protected function findMatchingLocation(string $locationName, int $companyId): ?Location
    {
        if (empty($locationName)) {
            return null;
        }

        // Clean up location name - remove status suffix like "- HADIR"
        $cleanName = preg_replace('/\s*-\s*(HADIR|TERLAMBAT|TIDAK HADIR|SAKIT|CUTI|IZIN)\s*$/i', '', $locationName);
        $cleanName = trim($cleanName);

        // Also handle "Dinas ke [Location]" format
        if (preg_match('/^Dinas\s+ke\s+(.+)$/i', $cleanName, $matches)) {
            $cleanName = trim($matches[1]);
            // Remove star marker if present
            $cleanName = preg_replace('/\s*★\s*$/', '', $cleanName);
        }

        // Handle "Dinas" alone without specific location
        // Return null so it becomes a red flag that needs location to be filled
        if (strtolower($cleanName) === 'dinas') {
            return null;
        }

        // Handle empty location name
        if (empty($cleanName)) {
            return null;
        }

        // Try exact match first (case-insensitive)
        $location = Location::where('company_id', $companyId)
            ->where('is_active', true)
            ->whereRaw('LOWER(name) = ?', [strtolower($cleanName)])
            ->first();

        if ($location) {
            return $location;
        }

        // Try partial match - location name contains search term
        $location = Location::where('company_id', $companyId)
            ->where('is_active', true)
            ->whereRaw('LOWER(name) LIKE ?', ['%' . strtolower($cleanName) . '%'])
            ->first();

        if ($location) {
            return $location;
        }

        // Try reverse - search term contains location name
        $locations = Location::where('company_id', $companyId)
            ->where('is_active', true)
            ->get();

        foreach ($locations as $loc) {
            if (stripos($cleanName, $loc->name) !== false) {
                return $loc;
            }
        }

        return null;
    }

    /**
     * Generate random coordinate offset within specified radius (10-50 meters)
     * This creates natural-looking coordinates instead of exact location center
     *
     * @param float $latitude Original latitude
     * @param float $longitude Original longitude
     * @param int $minMeters Minimum offset in meters (default: 10)
     * @param int $maxMeters Maximum offset in meters (default: 50)
     * @return array ['latitude' => float, 'longitude' => float]
     */
    protected function addRandomCoordinateOffset(float $latitude, float $longitude, int $minMeters = 10, int $maxMeters = 50): array
    {
        // Random distance between min and max meters
        $distance = $minMeters + mt_rand(0, ($maxMeters - $minMeters) * 100) / 100;

        // Random angle in radians (0 to 2π)
        $angle = mt_rand(0, 360) * M_PI / 180;

        // Convert distance to degrees
        // 1 degree latitude ≈ 111,320 meters
        // 1 degree longitude varies by latitude
        $metersPerDegreeLat = 111320;
        $metersPerDegreeLon = 111320 * cos(deg2rad($latitude));

        // Calculate offset
        $latOffset = ($distance * cos($angle)) / $metersPerDegreeLat;
        $lonOffset = ($distance * sin($angle)) / $metersPerDegreeLon;

        return [
            'latitude' => round($latitude + $latOffset, 6),
            'longitude' => round($longitude + $lonOffset, 6),
        ];
    }
}
