<?php

namespace App\Services;

use App\Modules\MasterApproval\Models\ApprovalFlowModel;
use App\Modules\MasterApproval\Models\ApprovalRequestModel;
use App\Modules\MasterApproval\Services\ApprovalWorkflowService;
use App\Modules\AttendanceReview\Models\AttendanceDailyReviewModel;
use App\Modules\MasterKaryawan\Models\KaryawanModel;
use App\Modules\MasterProfilJamKerja\Models\ProfilJamKerjaModel;
use App\Modules\MasterProfilBulan\Models\ProfilBulanKaryawanModel;
use App\Modules\MasterProfilBulan\Models\ProfilBulanModel;
use App\Modules\ProjectJobCosting\Models\ProjectJobAssignmentModel;
use App\Modules\ProjectJobCosting\Models\ProjectJobModel;
use App\Modules\SuratPerintahLembur\Models\SplThirdPartyImportModel;
use App\Modules\SuratPerintahLembur\Models\SuratPerintahLemburModel;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

class ThirdPartySplSyncService
{
    public function importDummy(array $options = []): array
    {
        if (!$this->tableExists(null, 'spl_third_party_imports')) {
            return $this->fail('Tabel staging spl_third_party_imports belum tersedia. Jalankan migration dulu.');
        }

        $preview = $this->preview(array_merge($options, [
            'preview_limit' => max(10, min(5000, (int) ($options['preview_limit'] ?? 1000))),
        ]));
        if (!($preview['ok'] ?? false)) {
            return $preview;
        }

        $batchId = 'SPL-DUMMY-' . now()->format('YmdHis');
        $created = 0;
        $updated = 0;
        $failed = 0;
        $errors = [];
        $userId = max(0, (int) ($options['user_id'] ?? 0));
        $sourceTable = trim((string) ($options['source_table'] ?? 'hrd_input_lembur')) ?: 'hrd_input_lembur';

        foreach ((array) ($preview['rows'] ?? []) as $row) {
            try {
                $payload = [
                    'source_table' => $sourceTable,
                    'source_pk' => $row['source_pk'] ?? null,
                    'finger_id' => $row['finger_raw'] ?? null,
                    'source_employee_code' => $row['source_employee_code'] ?? null,
                    'source_employee_name' => $row['source_employee_name'] ?? null,
                    'join_source_value' => $row['join_source_value'] ?? null,
                    'join_employee_value' => $row['join_employee_value'] ?? null,
                    'work_date' => $row['work_date'] ?? null,
                    'start_time' => $row['start_time'] ?? null,
                    'end_time' => $row['end_time'] ?? null,
                    'matched_karyawan_id' => $row['karyawan_id'] ?? null,
                    'matched_employee_code' => $row['ahsp_employee_code'] ?? null,
                    'matched_employee_name' => $row['ahsp_employee_name'] ?? null,
                    'matched_job_id' => $row['job_id'] ?? null,
                    'preview_status' => $row['status'] ?? null,
                    'preview_action' => $row['action_label'] ?? null,
                    'reason' => $row['reason'] ?? null,
                    'preview_notes' => $row['notes'] ?? null,
                    'import_batch_id' => $batchId,
                    'source_synced_at' => now(),
                ];
                if ($userId > 0) {
                    $payload['created_by'] = $userId;
                }

                $existing = SplThirdPartyImportModel::query()
                    ->where('source_system', 'third_party_spl_dummy')
                    ->where('source_reference', (string) ($row['source_reference'] ?? ''))
                    ->first();

                if ($existing) {
                    $existing->fill($payload);
                    $existing->save();
                    $updated++;
                } else {
                    SplThirdPartyImportModel::query()->create(array_merge($payload, [
                        'source_system' => 'third_party_spl_dummy',
                        'source_reference' => (string) ($row['source_reference'] ?? ''),
                    ]));
                    $created++;
                }
            } catch (\Throwable $e) {
                $failed++;
                if (count($errors) < 20) {
                    $errors[] = $e->getMessage();
                }
            }
        }

        return [
            'ok' => true,
            'status' => $failed > 0 ? 'warning' : 'success',
            'message' => 'Import dummy SPL ke staging selesai.',
            'meta' => array_merge((array) ($preview['meta'] ?? []), [
                'batch_id' => $batchId,
            ]),
            'stats' => array_merge((array) ($preview['stats'] ?? []), [
                'dummy_created' => $created,
                'dummy_updated' => $updated,
                'dummy_failed' => $failed,
            ]),
            'rows' => $preview['rows'] ?? [],
            'errors' => $errors,
            'output' => implode("\n", array_filter([
                'Import Dummy SPL 3rd Party',
                'Batch: ' . $batchId,
                'Source rows previewed: ' . (int) (($preview['stats']['previewed'] ?? 0)),
                'Created staging: ' . $created,
                'Updated staging: ' . $updated,
                'Failed staging: ' . $failed,
                $errors ? ('Sample error: ' . implode(' | ', $errors)) : null,
            ])),
        ];
    }

    public function preview(array $options = []): array
    {
        $connection = trim((string) ($options['connection'] ?? 'third_party')) ?: 'third_party';
        $sourceTable = trim((string) ($options['source_table'] ?? 'hrd_input_lembur')) ?: 'hrd_input_lembur';
        $employeeTable = trim((string) ($options['employee_table'] ?? 'pegawai_all')) ?: 'pegawai_all';
        $dateFrom = $this->normalizeDateString((string) ($options['date_from'] ?? '2025-12-26')) ?: '2025-12-26';
        $dateTo = $this->normalizeDateString((string) ($options['date_to'] ?? now()->toDateString())) ?: now()->toDateString();
        $updateExisting = (bool) ($options['update_existing'] ?? true);
        $previewLimit = max(10, min(50000, (int) ($options['preview_limit'] ?? 100)));
        $sourceEmployeeKey = trim((string) ($options['source_employee_key'] ?? 'id_pegawai')) ?: 'id_pegawai';
        $employeeJoinKey = trim((string) ($options['employee_join_key'] ?? 'idx')) ?: 'idx';
        $employeeFingerOverride = trim((string) ($options['employee_finger_column'] ?? 'kd_finger')) ?: 'kd_finger';
        $allowJoinFallback = (bool) ($options['allow_join_fallback'] ?? false);

        $connectionError = $this->ensureConnectionConfigured($connection);
        if ($connectionError) {
            return $this->fail($connectionError);
        }
        if (!$this->tableExists(null, 'surat_perintah_lemburs')) {
            return $this->fail('Tabel target surat_perintah_lemburs belum tersedia.');
        }

        try {
            if (!$this->tableExists($connection, $sourceTable)) {
                return $this->fail("Tabel sumber SPL tidak ditemukan: {$connection}.{$sourceTable}");
            }
            if (!$this->tableExists($connection, $employeeTable)) {
                return $this->fail("Tabel pegawai sumber tidak ditemukan: {$connection}.{$employeeTable}");
            }
        } catch (\Throwable $e) {
            return $this->fail($this->formatConnectionException($connection, $e));
        }

        try {
            $sourceColumns = Schema::connection($connection)->getColumnListing($sourceTable);
            $employeeColumns = Schema::connection($connection)->getColumnListing($employeeTable);
        } catch (\Throwable $e) {
            return $this->fail($this->formatConnectionException($connection, $e));
        }
        $sourcePkColumn = $this->pickColumn($sourceColumns, $options['source_pk_column'] ?? null, [
            'id_cek', 'id', 'id_lembur', 'lembur_id', 'input_id', 'id_input_lembur',
        ]);
        $dateColumn = $this->pickColumn($sourceColumns, $options['date_column'] ?? null, [
            'tanggal', 'tgl', 'tanggal_lembur', 'tgl_lembur', 'work_date', 'date', 'tanggal_input', 'tgl_input', 'waktu',
        ]);
        $startColumn = $this->pickColumn($sourceColumns, $options['start_column'] ?? null, [
            'awal', 'jam_awal', 'mulai', 'start_time', 'jam_mulai', 'dari', 'checkin', 'jam_masuk',
        ]);
        $endColumn = $this->pickColumn($sourceColumns, $options['end_column'] ?? null, [
            'akhir', 'jam_akhir', 'selesai', 'end_time', 'jam_selesai', 'sampai', 'checkout', 'jam_pulang',
        ]);
        $sourceNotransColumn = $this->pickColumn($sourceColumns, $options['source_notrans_column'] ?? null, [
            'notrans', 'no_trans', 'nomor_transaksi', 'transaction_no', 'document_no',
        ]);
        $reasonColumn = $this->pickColumn($sourceColumns, $options['reason_column'] ?? null, [
            'keterangan', 'keperluan', 'reason', 'catatan', 'uraian', 'notes', 'remark',
        ]);
        $sourceFingerColumn = $this->pickColumn($sourceColumns, $options['source_finger_column'] ?? null, [
            'id_finger', 'finger_id', 'no_finger', 'nomor_finger', 'pin', 'barcode',
        ]);

        $employeeJoinPair = null;
        $employeeJoinFallbackPair = null;
        if (!$sourceFingerColumn) {
            $employeeJoinPair = $this->detectJoinPair(
                $sourceColumns,
                $employeeColumns,
                $sourceEmployeeKey,
                $employeeJoinKey
            );
            if (!$employeeJoinPair) {
                return $this->fail(
                    "Kolom join tabel SPL ke {$employeeTable} tidak ditemukan. Isi manual source_employee_key + employee_join_key.",
                    [
                        'source_columns' => $sourceColumns,
                        'employee_columns' => $employeeColumns,
                    ]
                );
            }
            $employeeJoinFallbackPair = $allowJoinFallback
                ? $this->detectJoinFallbackPair($sourceColumns, $employeeColumns, $employeeJoinPair)
                : null;
        }

        $employeeFingerColumn = $this->pickColumn($employeeColumns, $employeeFingerOverride, [
            'kd_finger', 'id_finger', 'finger_id', 'no_finger', 'nomor_finger', 'pin', 'barcode',
        ]);
        $employeeNameColumn = $this->pickColumn($employeeColumns, $options['employee_name_column'] ?? null, [
            'nama', 'nama_pegawai', 'nama_lengkap', 'full_name', 'employee_name', 'name',
        ]);
        $employeeCodeColumn = $this->pickColumn($employeeColumns, $options['employee_code_column'] ?? null, [
            'nik', 'employee_code', 'kode', 'kode_karyawan', 'nip',
        ]);

        if (!$dateColumn || !$startColumn || !$endColumn) {
            return $this->fail(
                'Kolom tanggal / awal / akhir pada tabel sumber SPL belum bisa dikenali otomatis.',
                ['source_columns' => $sourceColumns]
            );
        }
        if (!$sourceFingerColumn && !$employeeFingerColumn) {
            return $this->fail(
                'Kolom finger_id pada sumber tidak ditemukan. Isi manual source_finger_column atau employee_finger_column.',
                [
                    'source_columns' => $sourceColumns,
                    'employee_columns' => $employeeColumns,
                ]
            );
        }

        $baseQuery = DB::connection($connection)->table($sourceTable . ' as spl');
        if ($employeeJoinPair) {
            $baseQuery->leftJoin(
                $employeeTable . ' as peg',
                'spl.' . $employeeJoinPair['source_key'],
                '=',
                'peg.' . $employeeJoinPair['employee_key']
            );
        }
        if ($employeeJoinFallbackPair) {
            $baseQuery->leftJoin(
                $employeeTable . ' as peg_fb',
                'spl.' . $employeeJoinFallbackPair['source_key'],
                '=',
                'peg_fb.' . $employeeJoinFallbackPair['employee_key']
            );
        }
        $baseQuery
            ->whereNotNull('spl.' . $dateColumn)
            ->whereDate('spl.' . $dateColumn, '>=', $dateFrom)
            ->whereDate('spl.' . $dateColumn, '<=', $dateTo);

        try {
            $sourceTotal = (int) (clone $baseQuery)->count();
        } catch (\Throwable $e) {
            return $this->fail($this->formatConnectionException($connection, $e));
        }

        $query = DB::connection($connection)->table($sourceTable . ' as spl');
        if ($employeeJoinPair) {
            $query->leftJoin(
                $employeeTable . ' as peg',
                'spl.' . $employeeJoinPair['source_key'],
                '=',
                'peg.' . $employeeJoinPair['employee_key']
            );
        }
        if ($employeeJoinFallbackPair) {
            $query->leftJoin(
                $employeeTable . ' as peg_fb',
                'spl.' . $employeeJoinFallbackPair['source_key'],
                '=',
                'peg_fb.' . $employeeJoinFallbackPair['employee_key']
            );
        }

        $selects = [
            $this->selectColumn('spl.' . $dateColumn, '_work_date_raw'),
            $this->selectColumn('spl.' . $startColumn, '_start_time_raw'),
            $this->selectColumn('spl.' . $endColumn, '_end_time_raw'),
        ];
        if ($sourcePkColumn) {
            $selects[] = $this->selectColumn('spl.' . $sourcePkColumn, '_source_pk');
        }
        if ($sourceNotransColumn) {
            $selects[] = $this->selectColumn('spl.' . $sourceNotransColumn, '_erp_notrans_raw');
        }
        if ($reasonColumn) {
            $selects[] = $this->selectColumn('spl.' . $reasonColumn, '_reason_raw');
        }
        if (in_array('status', $sourceColumns, true)) {
            $selects[] = $this->selectColumn('spl.status', '_source_status_raw');
        }
        if (in_array('jml_jam', $sourceColumns, true)) {
            $selects[] = $this->selectColumn('spl.jml_jam', '_erp_jml_jam_raw');
        }
        if (in_array('jenis_lembur', $sourceColumns, true)) {
            $selects[] = $this->selectColumn('spl.jenis_lembur', '_erp_jenis_lembur_raw');
        }
        if ($sourceFingerColumn) {
            $selects[] = $this->selectColumn('spl.' . $sourceFingerColumn, '_finger_id_raw');
        } elseif ($employeeFingerColumn && $employeeJoinFallbackPair) {
            $selects[] = $this->selectCoalesce(['peg.' . $employeeFingerColumn, 'peg_fb.' . $employeeFingerColumn], '_finger_id_raw');
        } elseif ($employeeFingerColumn) {
            $selects[] = $this->selectColumn('peg.' . $employeeFingerColumn, '_finger_id_raw');
        }
        if ($employeeNameColumn && $employeeJoinFallbackPair) {
            $selects[] = $this->selectCoalesce(['peg.' . $employeeNameColumn, 'peg_fb.' . $employeeNameColumn], '_employee_name_raw');
        } elseif ($employeeNameColumn) {
            $selects[] = $this->selectColumn('peg.' . $employeeNameColumn, '_employee_name_raw');
        }
        if ($employeeCodeColumn && $employeeJoinFallbackPair) {
            $selects[] = $this->selectCoalesce(['peg.' . $employeeCodeColumn, 'peg_fb.' . $employeeCodeColumn], '_employee_code_raw');
        } elseif ($employeeCodeColumn) {
            $selects[] = $this->selectColumn('peg.' . $employeeCodeColumn, '_employee_code_raw');
        }
        if ($employeeJoinPair) {
            $selects[] = $this->selectColumn('spl.' . $employeeJoinPair['source_key'], '_source_join_key_raw');
            $selects[] = $this->selectColumn('peg.' . $employeeJoinPair['employee_key'], '_employee_join_key_raw');
        }
        if ($employeeJoinFallbackPair) {
            $selects[] = $this->selectColumn('spl.' . $employeeJoinFallbackPair['source_key'], '_source_join_fallback_key_raw');
            $selects[] = $this->selectColumn('peg_fb.' . $employeeJoinFallbackPair['employee_key'], '_employee_join_fallback_key_raw');
        }

        try {
            $rows = $query->selectRaw(implode(', ', $selects))
                ->whereNotNull('spl.' . $dateColumn)
                ->whereDate('spl.' . $dateColumn, '>=', $dateFrom)
                ->whereDate('spl.' . $dateColumn, '<=', $dateTo)
                ->orderBy('spl.' . $dateColumn)
                ->orderBy($sourcePkColumn ? ('spl.' . $sourcePkColumn) : ('spl.' . $startColumn))
                ->limit($previewLimit)
                ->get();
        } catch (\Throwable $e) {
            return $this->fail($this->formatConnectionException($connection, $e));
        }

        $employeeMap = $this->buildEmployeeMap($rows);
        $stats = [
            'previewed' => 0,
            'ready_create' => 0,
            'will_update' => 0,
            'existing_locked' => 0,
            'existing_local' => 0,
            'missing_finger' => 0,
            'missing_karyawan' => 0,
            'invalid_datetime' => 0,
            'needs_review' => 0,
        ];
        $previewRows = [];

        foreach ($rows as $index => $row) {
            $fingerRaw = trim((string) ($row->_finger_id_raw ?? ''));
            $fingerCandidates = $this->fingerLookupKeys($fingerRaw);
            $workDate = $this->normalizeDateValue($row->_work_date_raw ?? null);
            $startTime = $this->normalizeTimeValue($row->_start_time_raw ?? null);
            $erpaType = $this->classifyErpaOvertimeType(
                $row->_start_time_raw ?? null,
                $row->_end_time_raw ?? null,
                $row->_erp_jml_jam_raw ?? null,
                $row->_erp_jenis_lembur_raw ?? null,
                $workDate
            );
            $endTime = $erpaType['normalized_end_time'];

            $status = 'ready_create';
            $statusLabel = 'Siap Create';
            $notes = [];
            $karyawan = null;

            $primaryJoinSource = isset($row->_source_join_key_raw) ? trim((string) $row->_source_join_key_raw) : '';
            $primaryJoinEmployee = isset($row->_employee_join_key_raw) ? trim((string) $row->_employee_join_key_raw) : '';
            $fallbackJoinSource = isset($row->_source_join_fallback_key_raw) ? trim((string) $row->_source_join_fallback_key_raw) : '';
            $fallbackJoinEmployee = isset($row->_employee_join_fallback_key_raw) ? trim((string) $row->_employee_join_fallback_key_raw) : '';
            $joinMatched = ($primaryJoinSource !== '' && $primaryJoinEmployee !== '') || ($fallbackJoinSource !== '' && $fallbackJoinEmployee !== '');

            if (empty($fingerCandidates)) {
                if (($primaryJoinSource !== '' || $fallbackJoinSource !== '') && !$joinMatched) {
                    $notes[] = 'Join ke pegawai 3rd party tidak menemukan pasangan, jadi kd_finger kosong.';
                } else {
                    $notes[] = 'Finger dari sumber belum terbaca.';
                }
            }

            foreach ($fingerCandidates as $candidate) {
                if (isset($employeeMap[$candidate])) {
                    $karyawan = $employeeMap[$candidate];
                    break;
                }
            }
            if (!$karyawan) {
                if (empty($fingerCandidates)) {
                    $status = $joinMatched ? 'missing_finger' : 'missing_join';
                    $statusLabel = $joinMatched ? 'Finger Kosong' : 'Join Pegawai Gagal';
                    $stats['missing_finger']++;
                } else {
                    $status = 'missing_karyawan';
                    $statusLabel = 'Belum Match AHSP';
                    $notes[] = 'Pencocokan SPL hanya memakai ' . $employeeTable . '.kd_finger -> master_karyawans.finger_id. Nilai kd_finger ini tidak ditemukan di AHSP.';
                    $stats['missing_karyawan']++;
                }
            }

            if ($karyawan && $workDate && $erpaType['overtime_type'] === SuratPerintahLemburModel::OVERTIME_TYPE_CONTINUES_NEXT_DAY
                && empty($erpaType['rule_payload']['erpa_target_continues_marker'])) {
                $erpaType = $this->resolveErpaContinuesPair($erpaType, (int) $karyawan->id, $workDate);
                $startTime = $erpaType['normalized_start_time'] ?? $startTime;
                $endTime = $erpaType['normalized_end_time'];
            }
            if ($karyawan && $workDate && $erpaType['overtime_type'] === SuratPerintahLemburModel::OVERTIME_TYPE_WORK_TARGET) {
                $erpaType = $this->resolveErpaTargetProfile($erpaType, (int) $karyawan->id, $workDate);
            }
            if ($karyawan && $workDate && $erpaType['overtime_type'] === SuratPerintahLemburModel::OVERTIME_TYPE_NO_REST) {
                $erpaType = $this->resolveErpaProfileBreak($erpaType, (int) $karyawan->id, $workDate);
                $startTime = $erpaType['normalized_start_time'] ?? $startTime;
            }
            if ($karyawan && $workDate && $erpaType['overtime_type'] === SuratPerintahLemburModel::OVERTIME_TYPE_NORMAL) {
                $erpaType = $this->markNormalBreakReviewCandidate($erpaType, (int) $karyawan->id, $workDate);
            }
            $sourceReference = $this->buildSourceReference($sourceTable, $row, $fingerRaw, $workDate ?: '-', $startTime ?: '-', $endTime ?: '-');
            $erpaType['rule_payload']['source_reference'] = $sourceReference;
            if (!$workDate || !$startTime || !$endTime) {
                $status = 'invalid_datetime';
                $statusLabel = 'Tanggal/Jam Invalid';
                $notes[] = 'Tanggal atau jam di sumber tidak valid.';
                $stats['invalid_datetime']++;
            }

            $existingId = null;
            $jobId = null;
            if ($erpaType['overtime_type'] === null) {
                if ($status === 'ready_create' || $status === 'invalid_datetime') {
                    $status = 'needs_review';
                    $statusLabel = 'Perlu Review';
                    $stats['needs_review']++;
                }
                $notes[] = $erpaType['review_reason'] ?? 'Jenis SPL ERPA belum dapat dipetakan; tidak dibuat atau diapprove otomatis.';
            }
            if ($karyawan && $workDate && $startTime && $endTime && $erpaType['overtime_type'] !== null) {
                $jobId = $this->resolveAssignedJobId((int) $karyawan->id, $workDate);
                $existingBySource = Schema::hasColumn('surat_perintah_lemburs', 'source_system') && Schema::hasColumn('surat_perintah_lemburs', 'source_reference')
                    ? SuratPerintahLemburModel::query()
                        ->where('source_system', 'third_party_spl')
                        ->where('source_reference', $sourceReference)
                        ->first()
                    : null;

                if ($existingBySource) {
                    $existingId = (int) $existingBySource->id;
                    $reason = $this->buildReason($row, $sourceTable, $fingerRaw, $dateFrom, $dateTo, trim((string) ($row->_erp_notrans_raw ?? '')), $erpaType['label']);
                    $sourcePayload = $this->buildSplSourcePayload(
                        (int) $karyawan->id,
                        $jobId,
                        $workDate,
                        $startTime,
                        $endTime,
                        $reason,
                        $fingerRaw,
                        $erpaType
                    );
                    $sourcePayloadHash = $this->buildPayloadHash($sourcePayload);
                    $changeSummary = $this->summarizePayloadChanges(
                        $this->buildComparableSplPayloadFromModel($existingBySource),
                        $sourcePayload,
                        [
                            'karyawan_id' => 'Karyawan',
                            'job_id' => 'No Job',
                            'work_date' => 'Tanggal Kerja',
                            'start_time' => 'Jam Mulai',
                            'end_time' => 'Jam Selesai',
                            'reason' => 'Alasan',
                            'overtime_type' => 'Jenis SPL',
                            'rule_payload' => 'Rule SPL',
                            'target_profil_bulan_id' => 'Profil Bulan',
                        ]
                    );
                    $hasSourceChange = $this->hasPayloadChanged((string) ($existingBySource->source_payload_hash ?? ''), $sourcePayloadHash, $changeSummary);

                    if (in_array((string) $existingBySource->status, ['approved', 'cancelled'], true)) {
                        $status = 'existing_locked';
                        $statusLabel = 'Sudah Lock';
                        $notes[] = 'Sudah ada SPL hasil sync dengan status locked: ' . $existingBySource->status;
                        if ($hasSourceChange && $changeSummary) {
                            $notes[] = 'Ada perubahan sumber, tapi tidak diupdate otomatis karena status locked. Perubahan: ' . $changeSummary;
                        }
                        $stats['existing_locked']++;
                    } elseif ($updateExisting && $hasSourceChange) {
                        $status = 'will_update';
                        $statusLabel = 'Siap Update';
                        $notes[] = 'Akan update SPL sync lama: ' . (string) ($existingBySource->document_no ?? ('ID ' . $existingBySource->id));
                        if ($changeSummary) {
                            $notes[] = 'Perubahan: ' . $changeSummary;
                        }
                        $stats['will_update']++;
                    } else {
                        $status = 'existing_local';
                        $statusLabel = $hasSourceChange ? 'Sudah Ada' : 'Sudah Sinkron';
                        $notes[] = $hasSourceChange
                            ? 'SPL hasil sync lama berubah di sumber, tapi opsi update existing sedang OFF.'
                            : 'SPL hasil sync sudah sama dengan sumber. Tidak perlu diproses ulang.';
                        $stats['existing_local']++;
                    }
                } else {
                    $existingLocal = SuratPerintahLemburModel::query()
                        ->where('karyawan_id', (int) $karyawan->id)
                        ->whereDate('work_date', $workDate)
                        ->where('start_time', $startTime)
                        ->where('end_time', $endTime)
                        ->first(['id', 'document_no', 'status']);
                    if ($existingLocal) {
                        $existingId = (int) $existingLocal->id;
                        $status = 'existing_local';
                        $statusLabel = 'Sudah Ada';
                        $notes[] = 'Sudah ada SPL lokal dengan jam yang sama: ' . (string) ($existingLocal->document_no ?? ('ID ' . $existingLocal->id));
                        $stats['existing_local']++;
                    } elseif ($status === 'ready_create') {
                        $notes[] = 'Siap dibuat sebagai SPL draft di AHSP.';
                        $stats['ready_create']++;
                    }
                }
            }

            $stats['previewed']++;
            $previewRows[] = [
                'row_no' => $index + 1,
                'source_pk' => $row->_source_pk ?? null,
                'erp_notrans' => trim((string) ($row->_erp_notrans_raw ?? '')),
                'source_status' => isset($row->_source_status_raw) ? (string) $row->_source_status_raw : null,
                'overtime_type' => $erpaType['overtime_type'],
                'overtime_type_label' => $erpaType['label'],
                'rule_payload' => $erpaType['rule_payload'],
                'work_date' => $workDate,
                'start_time' => $startTime,
                'end_time' => $endTime,
                'finger_raw' => $fingerRaw,
                'source_reference' => $sourceReference,
                'source_employee_code' => trim((string) ($row->_employee_code_raw ?? '')),
                'source_employee_name' => trim((string) ($row->_employee_name_raw ?? '')),
                'join_source_value' => isset($row->_source_join_key_raw) ? trim((string) $row->_source_join_key_raw) : null,
                'join_employee_value' => isset($row->_employee_join_key_raw) ? trim((string) $row->_employee_join_key_raw) : null,
                'join_source_fallback_value' => isset($row->_source_join_fallback_key_raw) ? trim((string) $row->_source_join_fallback_key_raw) : null,
                'join_employee_fallback_value' => isset($row->_employee_join_fallback_key_raw) ? trim((string) $row->_employee_join_fallback_key_raw) : null,
                'reason' => trim((string) ($row->_reason_raw ?? '')),
                'karyawan_id' => $karyawan ? (int) $karyawan->id : null,
                'ahsp_employee_code' => $karyawan ? (string) ($karyawan->employee_code ?? '') : '',
                'ahsp_employee_name' => $karyawan ? (string) ($karyawan->full_name ?? '') : '',
                'job_id' => $jobId,
                'status' => $status,
                'status_label' => $statusLabel,
                'action_label' => match ($status) {
                    'ready_create' => 'Create',
                    'will_update' => 'Update',
                    'existing_locked', 'existing_local' => 'Skip',
                    default => 'Perlu Cek',
                },
                'existing_id' => $existingId,
                'notes' => implode(' ', array_filter(array_unique($notes))),
            ];
        }

        return [
            'ok' => true,
            'status' => 'success',
            'message' => 'Preview source SPL berhasil diambil.',
            'meta' => [
                'source' => "{$connection}.{$sourceTable}",
                'employee_source' => "{$connection}.{$employeeTable}",
                'date_range' => [$dateFrom, $dateTo],
                'date_column' => $dateColumn,
                'start_column' => $startColumn,
                'end_column' => $endColumn,
                'reason_column' => $reasonColumn ?: '-',
                'source_finger_column' => $sourceFingerColumn ?: '-',
                'employee_finger_column' => $employeeFingerColumn ?: '-',
                'join' => $employeeJoinPair ? ($employeeJoinPair['source_key'] . ' = ' . $employeeJoinPair['employee_key']) : 'direct finger from source',
                'join_fallback' => $employeeJoinFallbackPair ? ($employeeJoinFallbackPair['source_key'] . ' = ' . $employeeJoinFallbackPair['employee_key']) : '-',
                'source_rows' => $sourceTotal,
                'preview_limit' => $previewLimit,
                'mode' => 'PREVIEW_ROWS',
            ],
            'stats' => $stats,
            'rows' => $previewRows,
            'errors' => [],
            'output' => 'Preview source SPL berhasil diambil.',
        ];
    }

    public function diagnoseFingerMatch(array $options = []): array
    {
        try {
            $employeeTable = trim((string) ($options['employee_table'] ?? 'pegawai_all')) ?: 'pegawai_all';
            $options['preview_limit'] = max(50, min(50000, (int) ($options['preview_limit'] ?? 500)));
            $preview = $this->preview($options);
            if (!($preview['ok'] ?? false)) {
                return $preview;
            }

            $rows = array_values(array_filter((array) ($preview['rows'] ?? []), function (array $row) {
                return in_array((string) ($row['status'] ?? ''), ['missing_finger', 'missing_join', 'missing_karyawan', 'ready_create', 'will_update', 'existing_local', 'existing_locked'], true);
            }));

        $detailRows = $this->attachProfilBulanDiagnostics($rows);
        usort($detailRows, function (array $a, array $b) {
            $cmp = strcmp((string) ($a['work_date'] ?? ''), (string) ($b['work_date'] ?? ''));
            if ($cmp !== 0) {
                return $cmp;
            }
            $cmp = strcmp((string) ($a['start_time'] ?? ''), (string) ($b['start_time'] ?? ''));
            if ($cmp !== 0) {
                return $cmp;
            }
            return strcmp((string) ($a['source_employee_name'] ?? ''), (string) ($b['source_employee_name'] ?? ''));
        });

        $grouped = [];
        foreach ($rows as $row) {
            $key = implode('|', [
                (string) ($row['finger_raw'] ?? ''),
                (string) ($row['source_employee_code'] ?? ''),
                (string) ($row['source_employee_name'] ?? ''),
                (string) ($row['status'] ?? ''),
                (string) ($row['ahsp_employee_code'] ?? ''),
            ]);

            if (!isset($grouped[$key])) {
                $grouped[$key] = [
                    'finger_raw' => (string) ($row['finger_raw'] ?? ''),
                    'source_employee_code' => (string) ($row['source_employee_code'] ?? ''),
                    'source_employee_name' => (string) ($row['source_employee_name'] ?? ''),
                    'status' => (string) ($row['status'] ?? ''),
                    'status_label' => (string) ($row['status_label'] ?? ''),
                    'ahsp_employee_code' => (string) ($row['ahsp_employee_code'] ?? ''),
                    'ahsp_employee_name' => (string) ($row['ahsp_employee_name'] ?? ''),
                    'ahsp_karyawan_id' => $row['karyawan_id'] ?? null,
                    'sample_work_date' => (string) ($row['work_date'] ?? ''),
                    'sample_start_time' => (string) ($row['start_time'] ?? ''),
                    'sample_end_time' => (string) ($row['end_time'] ?? ''),
                    'notes' => (string) ($row['notes'] ?? ''),
                    'count_rows' => 0,
                ];
            }

            $grouped[$key]['count_rows']++;
        }

        $diagnosticRows = array_values($grouped);
        usort($diagnosticRows, function (array $a, array $b) {
            $statusWeight = fn (string $status): int => match ($status) {
                'missing_finger' => 0,
                'missing_join' => 1,
                'missing_karyawan' => 2,
                'ready_create' => 3,
                'will_update' => 4,
                'existing_local' => 5,
                'existing_locked' => 6,
                default => 9,
            };
            $cmp = $statusWeight((string) ($a['status'] ?? '')) <=> $statusWeight((string) ($b['status'] ?? ''));
            if ($cmp !== 0) {
                return $cmp;
            }
            $cmp = strcmp((string) ($a['finger_raw'] ?? ''), (string) ($b['finger_raw'] ?? ''));
            if ($cmp !== 0) {
                return $cmp;
            }
            return strcmp((string) ($a['source_employee_name'] ?? ''), (string) ($b['source_employee_name'] ?? ''));
        });

        $stats = (array) ($preview['stats'] ?? []);
        $diagnostics = [
            'previewed_rows' => count($rows),
            'grouped_rows' => count($diagnosticRows),
            'matched_rows' => (int) (($stats['ready_create'] ?? 0) + ($stats['will_update'] ?? 0) + ($stats['existing_local'] ?? 0) + ($stats['existing_locked'] ?? 0)),
            'missing_finger_rows' => (int) ($stats['missing_finger'] ?? 0),
            'missing_match_rows' => (int) ($stats['missing_karyawan'] ?? 0),
            'invalid_datetime_rows' => (int) ($stats['invalid_datetime'] ?? 0),
        ];

            return [
                'ok' => true,
                'status' => 'success',
                'message' => 'Diagnosa kd_finger 3rd party selesai.',
                'meta' => array_merge((array) ($preview['meta'] ?? []), [
                    'mode' => 'DIAGNOSE_FINGER',
                ]),
                'stats' => $stats,
                'diagnostics' => $diagnostics,
                'rows' => $diagnosticRows,
                'detail_rows' => $detailRows,
                'errors' => [],
                'output' => implode("\n", [
                    'Diagnosa Match Finger SPL 3rd Party',
                    'Rule: ' . $employeeTable . '.kd_finger -> master_karyawans.finger_id',
                    'Previewed rows: ' . $diagnostics['previewed_rows'],
                    'Grouped rows: ' . $diagnostics['grouped_rows'],
                    'Matched rows: ' . $diagnostics['matched_rows'],
                    'Missing kd_finger: ' . $diagnostics['missing_finger_rows'],
                    'Missing match AHSP: ' . $diagnostics['missing_match_rows'],
                    'Invalid datetime: ' . $diagnostics['invalid_datetime_rows'],
                ]),
            ];
        } catch (\Throwable $e) {
            return $this->fail('Diagnosa sync SPL 3rd party gagal: ' . $e->getMessage());
        }
    }

    public function suggestSyncDateRanges(array $options = []): array
    {
        $connection = trim((string) ($options['connection'] ?? 'third_party')) ?: 'third_party';
        $sourceTable = trim((string) ($options['source_table'] ?? 'hrd_input_lembur')) ?: 'hrd_input_lembur';
        $dateFrom = $this->normalizeDateString((string) ($options['date_from'] ?? '2025-12-26')) ?: '2025-12-26';
        $dateTo = $this->normalizeDateString((string) ($options['date_to'] ?? now()->toDateString())) ?: now()->toDateString();

        $connectionError = $this->ensureConnectionConfigured($connection);
        if ($connectionError) {
            return $this->fail($connectionError);
        }
        if (!$this->tableExists(null, 'surat_perintah_lemburs')) {
            return $this->fail('Tabel target surat_perintah_lemburs belum tersedia.');
        }

        try {
            if (!$this->tableExists($connection, $sourceTable)) {
                return $this->fail("Tabel sumber SPL tidak ditemukan: {$connection}.{$sourceTable}");
            }
        } catch (\Throwable $e) {
            return $this->fail($this->formatConnectionException($connection, $e));
        }

        $sourceColumns = Schema::connection($connection)->getColumnListing($sourceTable);
        $sourceStatusColumn = $this->pickColumn($sourceColumns, null, ['status', 'approval_status', 'approved']);
        $dateColumn = $this->pickColumn($sourceColumns, $options['date_column'] ?? null, [
            'tanggal', 'tgl', 'tanggal_lembur', 'tgl_lembur', 'work_date', 'date', 'tanggal_input', 'tgl_input', 'waktu',
        ]);
        if (!$dateColumn) {
            return $this->fail('Kolom tanggal pada tabel sumber SPL belum bisa dikenali otomatis.', [
                'source_columns' => $sourceColumns,
            ]);
        }

        $sourceDateExpression = 'DATE(' . $this->wrapIdentifier('spl.' . $dateColumn) . ')';
        // Rows present in hrd_input_lembur are already approved by ERPA.
        $statusSelect = ', COUNT(*) as approved_count, 0 as unapproved_count';
        try {
            $sourceCounts = DB::connection($connection)->table($sourceTable . ' as spl')
                ->selectRaw($sourceDateExpression . ' as ' . $this->wrapIdentifier('work_date') . ', COUNT(*) as ' . $this->wrapIdentifier('source_count') . $statusSelect)
                ->whereNotNull('spl.' . $dateColumn)
                ->whereDate('spl.' . $dateColumn, '>=', $dateFrom)
                ->whereDate('spl.' . $dateColumn, '<=', $dateTo)
                ->groupByRaw($sourceDateExpression)
                ->orderByRaw($sourceDateExpression)
                ->get();
        } catch (\Throwable $e) {
            return $this->fail($this->formatConnectionException($connection, $e));
        }

        $localQuery = SuratPerintahLemburModel::query()
            ->selectRaw('DATE(' . $this->wrapIdentifier('work_date') . ') as ' . $this->wrapIdentifier('work_date') . ', COUNT(*) as ' . $this->wrapIdentifier('local_count'))
            ->whereDate('work_date', '>=', $dateFrom)
            ->whereDate('work_date', '<=', $dateTo);
        if (Schema::hasColumn('surat_perintah_lemburs', 'source_system')) {
            $localQuery->where('source_system', 'third_party_spl');
        }
        $localCounts = $localQuery
            ->groupByRaw('DATE(' . $this->wrapIdentifier('work_date') . ')')
            ->pluck('local_count', 'work_date');

        $suggestionPreviewLimit = max(1000, min(50000, (int) ($options['suggestion_preview_limit'] ?? $options['preview_limit'] ?? 50000)));
        $preview = $this->preview(array_merge($options, [
            'date_from' => $dateFrom,
            'date_to' => $dateTo,
            'preview_limit' => $suggestionPreviewLimit,
            'update_existing' => (bool) ($options['update_existing'] ?? true),
        ]));
        if (!($preview['ok'] ?? false)) {
            return $preview;
        }

        $actionableStatuses = ['ready_create', 'will_update'];
        $skippedStatuses = ['existing_local', 'existing_locked'];
        $blockedStatuses = ['missing_finger', 'missing_join', 'missing_karyawan', 'invalid_datetime', 'needs_review'];
        $actionableCounts = [];
        $approvedSkippedCounts = [];
        $skippedCounts = [];
        $blockedCounts = [];
        foreach ((array) ($preview['rows'] ?? []) as $previewRow) {
            $workDate = $this->normalizeDateValue($previewRow['work_date'] ?? null);
            if (!$workDate) {
                continue;
            }

            $status = (string) ($previewRow['status'] ?? '');
            if (in_array($status, $actionableStatuses, true)) {
                $actionableCounts[$workDate] = (int) ($actionableCounts[$workDate] ?? 0) + 1;
            } elseif (in_array($status, $skippedStatuses, true)) {
                if ((string) ($previewRow['source_status'] ?? '') === '1') {
                    $approvedSkippedCounts[$workDate] = (int) ($approvedSkippedCounts[$workDate] ?? 0) + 1;
                }
                $skippedCounts[$workDate] = (int) ($skippedCounts[$workDate] ?? 0) + 1;
            } elseif (in_array($status, $blockedStatuses, true)) {
                $blockedCounts[$workDate] = (int) ($blockedCounts[$workDate] ?? 0) + 1;
            }
        }

        $dateRows = [];
        foreach ($sourceCounts as $row) {
            $workDate = $this->normalizeDateValue($row->work_date ?? null);
            if (!$workDate) {
                continue;
            }
            $sourceCount = (int) ($row->source_count ?? 0);
            $approvedCount = (int) ($row->approved_count ?? $sourceCount);
            $unapprovedCount = (int) ($row->unapproved_count ?? 0);
            $localCount = (int) ($localCounts[$workDate] ?? 0);
            $remaining = (int) ($actionableCounts[$workDate] ?? 0);
            $skipped = (int) ($skippedCounts[$workDate] ?? 0);
            $blocked = (int) ($blockedCounts[$workDate] ?? 0);
            $syncedApproved = min($approvedCount, (int) ($approvedSkippedCounts[$workDate] ?? 0));
            $approvedRemaining = max(0, $approvedCount - $syncedApproved);

            $dateRows[] = [
                'work_date' => $workDate,
                'source_count' => $sourceCount,
                'approved_count' => $approvedCount,
                'unapproved_count' => $unapprovedCount,
                'local_count' => $localCount,
                'synced_count' => $syncedApproved,
                'skipped_count' => $skipped,
                'blocked_count' => $blocked,
                'remaining_count' => $remaining,
                'approved_remaining_count' => $approvedRemaining,
                'status' => $approvedRemaining > 0 ? 'actionable' : ($blocked > 0 ? 'review' : 'complete'),
                'status_label' => $approvedRemaining > 0 ? 'Belum Sync' : ($blocked > 0 ? 'Perlu Review' : 'Sudah Sync'),
            ];
        }

        $pendingDateRows = array_values(array_filter($dateRows, fn (array $row) => (int) ($row['remaining_count'] ?? 0) > 0));
        $ranges = $this->groupSyncDateRanges($pendingDateRows);
        $suggestedRange = !empty($ranges) ? end($ranges) : null;
        $sourceRows = (int) (($preview['meta']['source_rows'] ?? 0) ?: $sourceCounts->sum('source_count'));
        $scanRows = count((array) ($preview['rows'] ?? []));
        $scanLimited = $sourceRows > $scanRows;
        $actionableRows = array_sum($actionableCounts);
        $skippedRows = array_sum($skippedCounts);
        $blockedRows = array_sum($blockedCounts);

        return [
            'ok' => true,
            'status' => $scanLimited ? 'warning' : 'success',
            'message' => empty($ranges)
                ? 'Tidak ada kandidat SPL 3rd party yang perlu create/update pada range ini.'
                : 'Saran range tanggal sync SPL berhasil disiapkan dari kandidat yang bisa create/update.',
            'meta' => [
                'source' => "{$connection}.{$sourceTable}",
                'date_column' => $dateColumn,
                'date_range' => [$dateFrom, $dateTo],
                'source_dates' => count($dateRows),
                'source_rows' => $sourceRows,
                'pending_dates' => count($pendingDateRows),
                'pending_rows' => $actionableRows,
                'actionable_rows' => $actionableRows,
                'skipped_rows' => $skippedRows,
                'blocked_rows' => $blockedRows,
                'scan_rows' => $scanRows,
                'scan_limited' => $scanLimited,
                'range_count' => count($ranges),
                'suggested_from' => $suggestedRange['date_from'] ?? $dateFrom,
                'suggested_to' => $suggestedRange['date_to'] ?? $dateTo,
                'mode' => 'ACTIONABLE_SUGGESTIONS',
            ],
            'rows' => $dateRows,
            'ranges' => $ranges,
            'errors' => [],
            'output' => implode("\n", array_filter([
                'Saran Sync SPL 3rd Party',
                'Range cek: ' . $dateFrom . ' s/d ' . $dateTo,
                'Tanggal source: ' . count($dateRows),
                'Tanggal perlu sync: ' . count($pendingDateRows),
                'Row perlu sync: ' . $actionableRows,
                'Row sudah ada/locked: ' . $skippedRows,
                'Row perlu cek mapping: ' . $blockedRows,
                $scanLimited ? ('Catatan: scan dibatasi ' . $scanRows . ' dari ' . $sourceRows . ' row source.') : null,
                $suggestedRange ? ('Saran aktif: ' . $suggestedRange['date_from'] . ' s/d ' . $suggestedRange['date_to']) : 'Tidak ada kandidat create/update.',
            ])),
        ];
    }

    private function attachProfilBulanDiagnostics(array $rows): array
    {
        if (empty($rows)) {
            return [];
        }

        $dates = array_values(array_filter(array_map(fn (array $row) => (string) ($row['work_date'] ?? ''), $rows)));
        $karyawanIds = array_values(array_unique(array_filter(array_map(fn (array $row) => (int) ($row['karyawan_id'] ?? 0), $rows))));
        $jobIds = array_values(array_unique(array_filter(array_map(fn (array $row) => (int) ($row['job_id'] ?? 0), $rows))));

        $minDate = $dates ? min($dates) : null;
        $maxDate = $dates ? max($dates) : null;

        $profilBulans = collect();
        if ($minDate && $maxDate) {
            $profilBulans = ProfilBulanModel::query()
                ->whereDate('start_date', '<=', $maxDate)
                ->whereDate('end_date', '>=', $minDate)
                ->orderBy('start_date')
                ->get(['id', 'code', 'name', 'start_date', 'end_date', 'is_active', 'status']);
        }

        $assignmentMap = [];
        if ($profilBulans->isNotEmpty() && !empty($karyawanIds)) {
            $assignments = ProfilBulanKaryawanModel::query()
                ->whereIn('karyawan_id', $karyawanIds)
                ->whereIn('profil_bulan_id', $profilBulans->pluck('id')->all())
                ->get(['profil_bulan_id', 'karyawan_id', 'is_active']);

            foreach ($assignments as $assignment) {
                $assignmentMap[(int) $assignment->karyawan_id . '|' . (int) $assignment->profil_bulan_id] = [
                    'is_active' => (bool) ($assignment->is_active ?? true),
                ];
            }
        }

        $jobMap = [];
        if (!empty($jobIds)) {
            $jobs = ProjectJobModel::query()
                ->whereIn('id', $jobIds)
                ->get(['id', 'code', 'name', 'is_active', 'effective_start', 'effective_end']);

            foreach ($jobs as $job) {
                $jobMap[(int) $job->id] = $job;
            }
        }

        $result = [];
        foreach ($rows as $row) {
            $workDate = (string) ($row['work_date'] ?? '');
            $karyawanId = (int) ($row['karyawan_id'] ?? 0);
            $jobId = (int) ($row['job_id'] ?? 0);
            $profil = null;
            $job = $jobId > 0 ? ($jobMap[$jobId] ?? null) : null;

            if ($workDate !== '') {
                foreach ($profilBulans as $candidate) {
                    $start = $this->normalizeDateValue($candidate->start_date ?? null);
                    $end = $this->normalizeDateValue($candidate->end_date ?? null);
                    if ($start && $end && $workDate >= $start && $workDate <= $end) {
                        $profil = $candidate;
                        break;
                    }
                }
            }

            $profilStatus = 'no_profile_period';
            $profilStatusLabel = 'Tidak ada Profil Bulan';
            $profilNotes = '';
            $profilAssigned = false;
            if ($karyawanId <= 0) {
                $profilStatus = 'no_karyawan';
                $profilStatusLabel = 'Belum Match AHSP';
                $profilNotes = 'Belum bisa cek profil bulan karena karyawan AHSP belum ketemu.';
            } elseif (!$profil) {
                $profilStatus = 'no_profile_period';
                $profilStatusLabel = 'Tidak ada Profil Bulan';
                $profilNotes = 'Tidak ada profil bulan aktif yang mencakup tanggal kerja ini.';
            } else {
                $assignmentKey = $karyawanId . '|' . (int) $profil->id;
                $assignmentMeta = $assignmentMap[$assignmentKey] ?? null;
                $profilAssigned = $assignmentMeta !== null;
                if ($profilAssigned) {
                    $profilStatus = 'profil_match';
                    $profilStatusLabel = 'Match Profil Bulan';
                    $profilNotes = 'Karyawan sudah terdaftar di profil bulan yang mencakup tanggal kerja.'
                        . ((bool) ($assignmentMeta['is_active'] ?? true) ? '' : ' Assignment profil bulan ini statusnya nonaktif.');
                } else {
                    $profilStatus = 'profil_not_assigned';
                    $profilStatusLabel = 'Belum di Profil Bulan';
                    $profilNotes = 'Tanggal kerja masuk profil bulan, tapi karyawan belum terdaftar di profil bulan tersebut.';
                }
            }

            $result[] = array_merge($row, [
                'job_code' => $job ? (string) ($job->code ?? '') : '',
                'job_name' => $job ? (string) ($job->name ?? '') : '',
                'job_is_active' => $job ? (bool) ($job->is_active ?? false) : null,
                'job_status_label' => $job && $workDate !== '' ? $job->statusLabel($workDate) : ($job ? $job->statusLabel() : 'Unjob'),
                'job_display' => $job
                    ? trim(implode(' - ', array_filter([
                        (string) ($job->code ?? ''),
                        (string) ($job->name ?? ''),
                    ])))
                    : 'Unjob',
                'job_number' => $job ? (string) ($job->code ?? '') : 'Unjob',
                'profil_bulan_id' => $profil ? (int) $profil->id : null,
                'profil_bulan_code' => $profil ? (string) ($profil->code ?? '') : '',
                'profil_bulan_name' => $profil ? (string) ($profil->name ?? '') : '',
                'profil_bulan_active' => $profil ? (bool) ($profil->is_active ?? false) : null,
                'profil_bulan_status' => $profil ? (string) ($profil->status ?? '') : '',
                'profil_bulan_range' => $profil
                    ? trim(implode(' s/d ', array_filter([
                        $this->normalizeDateValue($profil->start_date ?? null),
                        $this->normalizeDateValue($profil->end_date ?? null),
                    ])))
                    : '',
                'profil_bulan_assigned' => $profilAssigned,
                'profil_status' => $profilStatus,
                'profil_status_label' => $profilStatusLabel,
                'profil_notes' => $profilNotes,
            ]);
        }

        return $result;
    }


    //function untuk cron job, untuk melakukan sync otomatis dari 3rd party ke AHSP
    public function sync(array $options = []): array
    {
        $connection = trim((string) ($options['connection'] ?? 'third_party')) ?: 'third_party';
        $sourceTable = trim((string) ($options['source_table'] ?? 'hrd_input_lembur')) ?: 'hrd_input_lembur';
        $employeeTable = trim((string) ($options['employee_table'] ?? 'pegawai_all')) ?: 'pegawai_all';
        $dateFrom = $this->normalizeDateString((string) ($options['date_from'] ?? '2025-12-26')) ?: '2025-12-26';
        $dateTo = $this->normalizeDateString((string) ($options['date_to'] ?? now()->toDateString())) ?: now()->toDateString();
        $apply = (bool) ($options['apply'] ?? false);
        $autoSubmit = (bool) ($options['auto_submit'] ?? false);
        $autoApprove = (bool) ($options['auto_approve'] ?? true);
        if ($autoApprove) {
            $autoSubmit = true;
        }
        $updateExisting = (bool) ($options['update_existing'] ?? true);
        $limit = max(0, (int) ($options['limit'] ?? 0));
        $onlyErpaJenis = array_key_exists('only_erpa_jenis', $options) && $options['only_erpa_jenis'] !== null
            ? (int) $options['only_erpa_jenis'] : null;
        // ERPA approval is represented by insertion into hrd_input_lembur.
        // Do not filter status: approved ERPA rows can retain status=0.
        $onlyApproved = (bool) ($options['only_approved'] ?? false);
        $chunk = max(50, min(2000, (int) ($options['chunk'] ?? 1000)));
        $sourceEmployeeKey = trim((string) ($options['source_employee_key'] ?? 'id_pegawai')) ?: 'id_pegawai';
        $employeeJoinKey = trim((string) ($options['employee_join_key'] ?? 'idx')) ?: 'idx';
        $employeeFingerOverride = trim((string) ($options['employee_finger_column'] ?? 'kd_finger')) ?: 'kd_finger';
        $allowJoinFallback = (bool) ($options['allow_join_fallback'] ?? false);
        $progressCallback = $options['progress_callback'] ?? null;
        $progressCallback = is_callable($progressCallback) ? $progressCallback : null;
        $userId = max(0, (int) ($options['user_id'] ?? 0));

        $connectionError = $this->ensureConnectionConfigured($connection);
        if ($connectionError) {
            return $this->fail($connectionError);
        }
        if (!$this->tableExists(null, 'surat_perintah_lemburs')) {
            return $this->fail('Tabel target surat_perintah_lemburs belum tersedia.');
        }

        try {
            if (!$this->tableExists($connection, $sourceTable)) {
                return $this->fail("Tabel sumber SPL tidak ditemukan: {$connection}.{$sourceTable}");
            }
            if (!$this->tableExists($connection, $employeeTable)) {
                return $this->fail("Tabel pegawai sumber tidak ditemukan: {$connection}.{$employeeTable}");
            }
        } catch (\Throwable $e) {
            return $this->fail($this->formatConnectionException($connection, $e));
        }

        $flowId = $this->resolveThirdPartySplFlowId();
        if ($flowId <= 0) {
            return $this->fail('Flow approval SPL belum ada. Buat flow overtime/lembur aktif dulu di Master Approval.');
        }

        $sourceColumns = Schema::connection($connection)->getColumnListing($sourceTable);
        $sourceStatusColumn = $this->pickColumn($sourceColumns, null, ['status', 'approval_status', 'approved']);
        $employeeColumns = Schema::connection($connection)->getColumnListing($employeeTable);
        if ($onlyErpaJenis !== null && !in_array('jenis_lembur', $sourceColumns, true)) {
            return $this->fail('Filter ERPA jenis_lembur diminta, tetapi kolom jenis_lembur tidak tersedia.');
        }
        $sourcePkColumn = $this->pickColumn($sourceColumns, $options['source_pk_column'] ?? null, [
            'id_cek', 'id', 'id_lembur', 'lembur_id', 'input_id', 'id_input_lembur',
        ]);
        $dateColumn = $this->pickColumn($sourceColumns, $options['date_column'] ?? null, [
            'tanggal', 'tgl', 'tanggal_lembur', 'tgl_lembur', 'work_date', 'date', 'tanggal_input', 'tgl_input', 'waktu',
        ]);
        $startColumn = $this->pickColumn($sourceColumns, $options['start_column'] ?? null, [
            'awal', 'jam_awal', 'mulai', 'start_time', 'jam_mulai', 'dari', 'checkin', 'jam_masuk',
        ]);
        $endColumn = $this->pickColumn($sourceColumns, $options['end_column'] ?? null, [
            'akhir', 'jam_akhir', 'selesai', 'end_time', 'jam_selesai', 'sampai', 'checkout', 'jam_pulang',
        ]);
        $sourceNotransColumn = $this->pickColumn($sourceColumns, $options['source_notrans_column'] ?? null, [
            'notrans', 'no_trans', 'nomor_transaksi', 'transaction_no', 'document_no',
        ]);
        $reasonColumn = $this->pickColumn($sourceColumns, $options['reason_column'] ?? null, [
            'keterangan', 'keperluan', 'reason', 'catatan', 'uraian', 'notes', 'remark',
        ]);
        $sourceFingerColumn = $this->pickColumn($sourceColumns, $options['source_finger_column'] ?? null, [
            'id_finger', 'finger_id', 'no_finger', 'nomor_finger', 'pin', 'barcode',
        ]);

        $employeeJoinPair = null;
        $employeeJoinFallbackPair = null;
        if (!$sourceFingerColumn) {
            $employeeJoinPair = $this->detectJoinPair(
                $sourceColumns,
                $employeeColumns,
                $sourceEmployeeKey,
                $employeeJoinKey
            );
            if (!$employeeJoinPair) {
                return $this->fail(
                    "Kolom join tabel SPL ke {$employeeTable} tidak ditemukan. Isi manual source_employee_key + employee_join_key.",
                    [
                        'source_columns' => $sourceColumns,
                        'employee_columns' => $employeeColumns,
                    ]
                );
            }
            $employeeJoinFallbackPair = $allowJoinFallback
                ? $this->detectJoinFallbackPair($sourceColumns, $employeeColumns, $employeeJoinPair)
                : null;
        }

        $employeeFingerColumn = $this->pickColumn($employeeColumns, $employeeFingerOverride, [
            'kd_finger', 'id_finger', 'finger_id', 'no_finger', 'nomor_finger', 'pin', 'barcode',
        ]);
        $employeeNameColumn = $this->pickColumn($employeeColumns, $options['employee_name_column'] ?? null, [
            'nama', 'nama_pegawai', 'nama_lengkap', 'full_name', 'employee_name', 'name',
        ]);
        $employeeCodeColumn = $this->pickColumn($employeeColumns, $options['employee_code_column'] ?? null, [
            'nik', 'employee_code', 'kode', 'kode_karyawan', 'nip',
        ]);

        if (!$dateColumn || !$startColumn || !$endColumn) {
            return $this->fail(
                'Kolom tanggal / awal / akhir pada tabel sumber SPL belum bisa dikenali otomatis.',
                ['source_columns' => $sourceColumns]
            );
        }
        if (!$sourceFingerColumn && !$employeeFingerColumn) {
            return $this->fail(
                'Kolom finger_id pada sumber tidak ditemukan. Isi manual source_finger_column atau employee_finger_column.',
                [
                    'source_columns' => $sourceColumns,
                    'employee_columns' => $employeeColumns,
                ]
            );
        }

        $baseQuery = DB::connection($connection)->table($sourceTable . ' as spl');
        if ($employeeJoinPair) {
            $baseQuery->leftJoin(
                $employeeTable . ' as peg',
                'spl.' . $employeeJoinPair['source_key'],
                '=',
                'peg.' . $employeeJoinPair['employee_key']
            );
        }
        if ($employeeJoinFallbackPair) {
            $baseQuery->leftJoin(
                $employeeTable . ' as peg_fb',
                'spl.' . $employeeJoinFallbackPair['source_key'],
                '=',
                'peg_fb.' . $employeeJoinFallbackPair['employee_key']
            );
        }

        $baseQuery
            ->whereNotNull('spl.' . $dateColumn)
            ->whereDate('spl.' . $dateColumn, '>=', $dateFrom)
            ->whereDate('spl.' . $dateColumn, '<=', $dateTo);
        if ($onlyErpaJenis !== null) {
            $baseQuery->where('spl.jenis_lembur', $onlyErpaJenis);
        }
        if ($onlyApproved && $sourceStatusColumn) {
            $baseQuery->where('spl.' . $sourceStatusColumn, 1);
        }

        if ($limit > 0) {
            $baseQuery->limit($limit);
        }

        $sourceTotal = (int) (clone $baseQuery)->count();
        $stats = [
            'processed' => 0,
            'matched_karyawan' => 0,
            'created' => 0,
            'updated' => 0,
            'unchanged' => 0,
            'changed_pending' => 0,
            'changed_locked' => 0,
            'skipped_missing_finger' => 0,
            'skipped_missing_karyawan' => 0,
            'skipped_invalid_datetime' => 0,
            'skipped_needs_review' => 0,
            'skipped_existing' => 0,
            'skipped_locked' => 0,
            'submitted' => 0,
            'skipped_auto_submit' => 0,
            'failed_auto_submit' => 0,
            'approved' => 0,
            'skipped_auto_approve' => 0,
            'failed_auto_approve' => 0,
            'failed' => 0,
        ];
        $errors = [];

        if ($progressCallback) {
            $progressCallback([
                'processed' => 0,
                'total' => $sourceTotal,
                'percent' => 0,
                'stats' => $stats,
                'message' => 'Mengambil data SPL dari database pihak ketiga...',
            ]);
        }

        $query = DB::connection($connection)->table($sourceTable . ' as spl');
        if ($employeeJoinPair) {
            $query->leftJoin(
                $employeeTable . ' as peg',
                'spl.' . $employeeJoinPair['source_key'],
                '=',
                'peg.' . $employeeJoinPair['employee_key']
            );
        }
        if ($employeeJoinFallbackPair) {
            $query->leftJoin(
                $employeeTable . ' as peg_fb',
                'spl.' . $employeeJoinFallbackPair['source_key'],
                '=',
                'peg_fb.' . $employeeJoinFallbackPair['employee_key']
            );
        }
        $selects = [
            $this->selectColumn('spl.' . $dateColumn, '_work_date_raw'),
            $this->selectColumn('spl.' . $startColumn, '_start_time_raw'),
            $this->selectColumn('spl.' . $endColumn, '_end_time_raw'),
        ];
        if ($sourcePkColumn) {
            $selects[] = $this->selectColumn('spl.' . $sourcePkColumn, '_source_pk');
        }
        if ($sourceNotransColumn) {
            $selects[] = $this->selectColumn('spl.' . $sourceNotransColumn, '_erp_notrans_raw');
        }
        if ($reasonColumn) {
            $selects[] = $this->selectColumn('spl.' . $reasonColumn, '_reason_raw');
        }
        if (in_array('jml_jam', $sourceColumns, true)) {
            $selects[] = $this->selectColumn('spl.jml_jam', '_erp_jml_jam_raw');
        }
        if (in_array('jenis_lembur', $sourceColumns, true)) {
            $selects[] = $this->selectColumn('spl.jenis_lembur', '_erp_jenis_lembur_raw');
        }
        if ($sourceFingerColumn) {
            $selects[] = $this->selectColumn('spl.' . $sourceFingerColumn, '_finger_id_raw');
        } elseif ($employeeFingerColumn && $employeeJoinFallbackPair) {
            $selects[] = $this->selectCoalesce(['peg.' . $employeeFingerColumn, 'peg_fb.' . $employeeFingerColumn], '_finger_id_raw');
        } elseif ($employeeFingerColumn) {
            $selects[] = $this->selectColumn('peg.' . $employeeFingerColumn, '_finger_id_raw');
        }
        if ($employeeNameColumn && $employeeJoinFallbackPair) {
            $selects[] = $this->selectCoalesce(['peg.' . $employeeNameColumn, 'peg_fb.' . $employeeNameColumn], '_employee_name_raw');
        } elseif ($employeeNameColumn) {
            $selects[] = $this->selectColumn('peg.' . $employeeNameColumn, '_employee_name_raw');
        }
        if ($employeeCodeColumn && $employeeJoinFallbackPair) {
            $selects[] = $this->selectCoalesce(['peg.' . $employeeCodeColumn, 'peg_fb.' . $employeeCodeColumn], '_employee_code_raw');
        } elseif ($employeeCodeColumn) {
            $selects[] = $this->selectColumn('peg.' . $employeeCodeColumn, '_employee_code_raw');
        }

        $query->selectRaw(implode(', ', $selects))
            ->whereNotNull('spl.' . $dateColumn)
            ->whereDate('spl.' . $dateColumn, '>=', $dateFrom)
            ->whereDate('spl.' . $dateColumn, '<=', $dateTo)
            ->orderBy('spl.' . $dateColumn)
            ->orderBy($sourcePkColumn ? ('spl.' . $sourcePkColumn) : ('spl.' . $startColumn));
        if ($onlyErpaJenis !== null) {
            $query->where('spl.jenis_lembur', $onlyErpaJenis);
        }

        if ($limit > 0) {
            $query->limit($limit);
        }

        $query->chunk($chunk, function ($rows) use (
            $connection,
            $sourceTable,
            $flowId,
            $apply,
            $autoSubmit,
            $autoApprove,
            $updateExisting,
            $progressCallback,
            $sourceTotal,
            $dateFrom,
            $dateTo,
            $userId,
            &$stats,
            &$errors
        ) {
            $employeeMap = $this->buildEmployeeMap($rows);

            foreach ($rows as $row) {
                $stats['processed']++;

                try {
                    $fingerRaw = (string) ($row->_finger_id_raw ?? '');
                    $fingerCandidates = $this->fingerLookupKeys($fingerRaw);
                    $karyawan = null;
                    foreach ($fingerCandidates as $candidate) {
                        if (isset($employeeMap[$candidate])) {
                            $karyawan = $employeeMap[$candidate];
                            break;
                        }
                    }
                    if (!$karyawan) {
                        if (empty($fingerCandidates)) {
                            $stats['skipped_missing_finger']++;
                            continue;
                        }
                        $stats['skipped_missing_karyawan']++;
                        if (count($errors) < 20) {
                            $errors[] = 'kd_finger 3rd party tidak ditemukan di AHSP (master_karyawans.finger_id): ' . $fingerRaw;
                        }
                        continue;
                    }
                    $stats['matched_karyawan']++;

                    $workDate = $this->normalizeDateValue($row->_work_date_raw ?? null);
                    $startTime = $this->normalizeTimeValue($row->_start_time_raw ?? null);
                    $endTime = $this->normalizeTimeValue($row->_end_time_raw ?? null);
                    $erpaType = $this->classifyErpaOvertimeType(
                        $row->_start_time_raw ?? null,
                        $row->_end_time_raw ?? null,
                        $row->_erp_jml_jam_raw ?? null,
                        $row->_erp_jenis_lembur_raw ?? null,
                        $workDate
                    );
                    if ($workDate && $erpaType['overtime_type'] === SuratPerintahLemburModel::OVERTIME_TYPE_CONTINUES_NEXT_DAY
                        && empty($erpaType['rule_payload']['erpa_target_continues_marker'])) {
                        $erpaType = $this->resolveErpaContinuesPair($erpaType, (int) $karyawan->id, $workDate);
                        $startTime = $erpaType['normalized_start_time'] ?? $startTime;
                    }
                    if ($workDate && $erpaType['overtime_type'] === SuratPerintahLemburModel::OVERTIME_TYPE_WORK_TARGET) {
                        $erpaType = $this->resolveErpaTargetProfile($erpaType, (int) $karyawan->id, $workDate);
                    }
                    if ($workDate && $erpaType['overtime_type'] === SuratPerintahLemburModel::OVERTIME_TYPE_NO_REST) {
                        $erpaType = $this->resolveErpaProfileBreak($erpaType, (int) $karyawan->id, $workDate);
                        $startTime = $erpaType['normalized_start_time'] ?? $startTime;
                    }
                    if ($workDate && $erpaType['overtime_type'] === SuratPerintahLemburModel::OVERTIME_TYPE_NORMAL) {
                        $erpaType = $this->markNormalBreakReviewCandidate($erpaType, (int) $karyawan->id, $workDate);
                    }
                    $endTime = $erpaType['normalized_end_time'] ?? $endTime;
                    if ($erpaType['overtime_type'] === null) {
                        $stats['skipped_needs_review']++;
                        if (count($errors) < 20) {
                            $errors[] = ($erpaType['review_reason'] ?? 'Perlu Review jenis SPL ERPA') . ' untuk finger ' . $fingerRaw
                                . ', tanggal ' . ($workDate ?: '-') . ', No Trans: ' . ($row->_erp_notrans_raw ?? '-');
                        }
                        continue;
                    }
                    if (!$workDate || !$startTime || !$endTime) {
                        $stats['skipped_invalid_datetime']++;
                        if (count($errors) < 20) {
                            $errors[] = 'Tanggal/jam tidak valid untuk finger ' . $fingerRaw;
                        }
                        continue;
                    }

                    $sourceReference = $this->buildSourceReference($sourceTable, $row, $fingerRaw, $workDate, $startTime, $endTime);
                    $erpaType['rule_payload']['source_reference'] = $sourceReference;
                    $erpNotrans = trim((string) ($row->_erp_notrans_raw ?? ''));
                    $reason = $this->buildReason($row, $sourceTable, $fingerRaw, $dateFrom, $dateTo, trim((string) ($row->_erp_notrans_raw ?? '')), (string) ($erpaType['label'] ?? ''));
                    $jobId = $this->resolveAssignedJobId((int) $karyawan->id, $workDate);
                    $sourcePayload = $this->buildSplSourcePayload(
                        (int) $karyawan->id,
                        $jobId,
                        $workDate,
                        $startTime,
                        $endTime,
                        $reason,
                        $fingerRaw,
                        $erpaType
                    );
                    $sourcePayloadHash = $this->buildPayloadHash($sourcePayload);

                    $existingBySource = Schema::hasColumn('surat_perintah_lemburs', 'source_system') && Schema::hasColumn('surat_perintah_lemburs', 'source_reference')
                        ? SuratPerintahLemburModel::query()
                            ->where('source_system', 'third_party_spl')
                            ->where('source_reference', $sourceReference)
                            ->first()
                        : null;

                    if ($existingBySource) {
                        $changeSummary = $this->summarizePayloadChanges(
                            $this->buildComparableSplPayloadFromModel($existingBySource),
                            $sourcePayload,
                            [
                                'karyawan_id' => 'Karyawan',
                                'job_id' => 'No Job',
                                'work_date' => 'Tanggal Kerja',
                                'start_time' => 'Jam Mulai',
                                'end_time' => 'Jam Selesai',
                                'reason' => 'Alasan',
                                'overtime_type' => 'Jenis SPL',
                                'rule_payload' => 'Rule SPL',
                                'target_profil_bulan_id' => 'Profil Bulan',
                            ]
                        );
                        $hasSourceChange = $this->hasPayloadChanged((string) ($existingBySource->source_payload_hash ?? ''), $sourcePayloadHash, $changeSummary);

                        if (!$hasSourceChange) {
                            if ($apply) {
                                $this->applySyncMetaToSpl($existingBySource, [
                                    'erp_notrans' => $erpNotrans,
                                    'source_synced_at' => now(),
                                    'source_payload_hash' => $sourcePayloadHash,
                                    'sync_status' => 'unchanged',
                                    'sync_last_checked_at' => now(),
                                    'sync_change_summary' => null,
                                ]);
                                if ($autoSubmit) {
                                    $this->autoSubmitSplRow($existingBySource, $stats, $errors);
                                }
                                if ($autoApprove) {
                                    $this->autoApproveSplRow($existingBySource, $stats, $errors, $userId);
                                }
                            }
                            $stats['unchanged']++;
                            continue;
                        }

                        if (!$updateExisting) {
                            if ($apply) {
                                $this->applySyncMetaToSpl($existingBySource, [
                                    'erp_notrans' => $erpNotrans,
                                    'source_synced_at' => now(),
                                    'source_payload_hash' => $sourcePayloadHash,
                                    'sync_status' => 'changed_pending',
                                    'sync_last_checked_at' => now(),
                                    'sync_change_summary' => $changeSummary,
                                ]);
                            }
                            $stats['changed_pending']++;
                            $stats['skipped_existing']++;
                            continue;
                        }
                        if ($this->canRepairApprovedTargetClassification($existingBySource, $erpaType)) {
                            if ($apply) {
                                $existingBySource->fill($this->mergeSyncMetaIntoSplPayload([
                                    'start_time' => $startTime,
                                    'end_time' => $endTime,
                                    'overtime_type' => $erpaType['overtime_type'],
                                    'rule_payload' => $erpaType['rule_payload'] ?? null,
                                    'target_profil_bulan_id' => $erpaType['target_profil_bulan_id'] ?? null,
                                    'updated_at' => now(),
                                ], [
                                    'erp_notrans' => $erpNotrans,
                                    'source_synced_at' => now(),
                                    'source_payload_hash' => $sourcePayloadHash,
                                    'sync_status' => 'classification_repaired',
                                    'sync_last_checked_at' => now(),
                                    'sync_change_summary' => !empty($erpaType['rule_payload']['erpa_target_continues_marker'])
                                        ? 'Jenis SPL dikoreksi: Target ERPA akhir 31/32 menjadi Continues Work Days.'
                                        : 'Jenis SPL dikoreksi ke Target dari ERPA jenis_lembur=3.',
                                ]));
                                $existingBySource->save();
                            }
                            $stats['updated']++;
                            continue;
                        }
                        if (in_array((string) $existingBySource->status, ['approved', 'cancelled'], true)) {
                            if ($apply) {
                                $this->applySyncMetaToSpl($existingBySource, [
                                    'erp_notrans' => $erpNotrans,
                                    'source_synced_at' => now(),
                                    'source_payload_hash' => $sourcePayloadHash,
                                    'sync_status' => 'changed_locked',
                                    'sync_last_checked_at' => now(),
                                    'sync_change_summary' => $changeSummary,
                                ]);
                            }
                            $stats['changed_locked']++;
                            $stats['skipped_locked']++;
                            continue;
                        }

                        $payload = [
                            'karyawan_id' => (int) $karyawan->id,
                            'job_id' => $jobId,
                            'approval_flow_id' => $flowId,
                            'request_date' => $workDate,
                            'work_date' => $workDate,
                            'start_time' => $startTime,
                            'end_time' => $endTime,
                            'reason' => $reason,
                            'overtime_type' => (string) ($erpaType['overtime_type'] ?? SuratPerintahLemburModel::OVERTIME_TYPE_NORMAL),
                            'rule_payload' => $erpaType['rule_payload'] ?? null,
                            'target_profil_bulan_id' => $erpaType['target_profil_bulan_id'] ?? null,
                            'updated_at' => now(),
                        ];
                        $payload = $this->mergeSyncMetaIntoSplPayload($payload, [
                            'erp_notrans' => $erpNotrans,
                            'source_synced_at' => now(),
                            'source_payload_hash' => $sourcePayloadHash,
                            'sync_status' => 'updated_from_source',
                            'sync_last_checked_at' => now(),
                            'sync_change_summary' => $changeSummary,
                        ]);

                        if ($apply) {
                            $existingBySource->fill($payload);
                            $existingBySource->save();
                            if ($autoSubmit) {
                                $this->autoSubmitSplRow($existingBySource, $stats, $errors);
                            }
                            if ($autoApprove) {
                                $this->autoApproveSplRow($existingBySource, $stats, $errors, $userId);
                            }
                        }
                        $stats['updated']++;
                        continue;
                    }

                    $existingLocal = SuratPerintahLemburModel::query()
                        ->where('karyawan_id', (int) $karyawan->id)
                        ->whereDate('work_date', $workDate)
                        ->where('start_time', $startTime)
                        ->where('end_time', $endTime)
                        ->first();
                    if ($existingLocal) {
                        $stats['skipped_existing']++;
                        continue;
                    }

                        if ($apply) {
                            $tmpDoc = 'SPL-' . now()->format('Ymd') . '-' . str_replace('-', '', (string) \Illuminate\Support\Str::uuid());
                            $created = SuratPerintahLemburModel::query()->create(array_filter([
                            'document_no' => $tmpDoc,
                            'karyawan_id' => (int) $karyawan->id,
                            'job_id' => $jobId,
                            'approval_flow_id' => $flowId,
                            'request_date' => $workDate,
                            'work_date' => $workDate,
                            'start_time' => $startTime,
                            'end_time' => $endTime,
                            'overtime_type' => (string) ($erpaType['overtime_type'] ?? SuratPerintahLemburModel::OVERTIME_TYPE_NORMAL),
                            'rule_payload' => $erpaType['rule_payload'] ?? null,
                            'target_profil_bulan_id' => $erpaType['target_profil_bulan_id'] ?? null,
                            'reason' => $reason,
                            'status' => 'draft',
                            'created_by' => $userId > 0 ? $userId : null,
                            'is_active' => true,
                            'source_system' => Schema::hasColumn('surat_perintah_lemburs', 'source_system') ? 'third_party_spl' : null,
                            'source_reference' => Schema::hasColumn('surat_perintah_lemburs', 'source_reference') ? $sourceReference : null,
                            'erp_notrans' => Schema::hasColumn('surat_perintah_lemburs', 'erp_notrans') ? ($erpNotrans !== '' ? $erpNotrans : null) : null,
                            'source_synced_at' => Schema::hasColumn('surat_perintah_lemburs', 'source_synced_at') ? now() : null,
                            'source_payload_hash' => Schema::hasColumn('surat_perintah_lemburs', 'source_payload_hash') ? $sourcePayloadHash : null,
                            'sync_status' => Schema::hasColumn('surat_perintah_lemburs', 'sync_status') ? 'created_from_source' : null,
                            'sync_last_checked_at' => Schema::hasColumn('surat_perintah_lemburs', 'sync_last_checked_at') ? now() : null,
                            'sync_change_summary' => Schema::hasColumn('surat_perintah_lemburs', 'sync_change_summary') ? 'Insert baru dari source 3rd party.' : null,
                            ], fn ($value) => $value !== null));
                            $created->document_no = 'SPL-' . now()->format('Ymd') . '-' . str_pad((string) $created->id, 5, '0', STR_PAD_LEFT);
                            $created->save();
                            if ($autoSubmit) {
                                $this->autoSubmitSplRow($created, $stats, $errors);
                            }
                            if ($autoApprove) {
                                $this->autoApproveSplRow($created, $stats, $errors, $userId);
                            }
                        }
                        $stats['created']++;
                } catch (\Throwable $e) {
                    $stats['failed']++;
                    if (count($errors) < 20) {
                        $errors[] = $e->getMessage();
                    }
                }
            }

            if ($progressCallback) {
                $progressCallback([
                    'processed' => $stats['processed'],
                    'total' => $sourceTotal,
                    'percent' => $sourceTotal > 0 ? min(100, round(($stats['processed'] / $sourceTotal) * 100, 2)) : 100,
                    'stats' => $stats,
                    'errors' => $errors,
                    'message' => 'Memproses data SPL ke AHSP...',
                ]);
            }
        });

        return [
            'ok' => true,
            'status' => 'success',
            'message' => $apply ? 'Sync SPL selesai.' : 'Preview sync SPL selesai.',
            'meta' => [
                'source' => "{$connection}.{$sourceTable}",
                'employee_source' => "{$connection}.{$employeeTable}",
                'date_range' => [$dateFrom, $dateTo],
                'date_column' => $dateColumn,
                'start_column' => $startColumn,
                'end_column' => $endColumn,
                'reason_column' => $reasonColumn ?: '-',
                'source_finger_column' => $sourceFingerColumn ?: '-',
                'employee_finger_column' => $employeeFingerColumn ?: '-',
                'join' => $employeeJoinPair ? ($employeeJoinPair['source_key'] . ' = ' . $employeeJoinPair['employee_key']) : 'direct finger from source',
                'source_rows' => $sourceTotal,
                'mode' => $apply ? 'APPLY' : 'DRY-RUN',
                'auto_submit' => $autoSubmit,
                'auto_approve' => $autoApprove,
            ],
            'stats' => $stats,
            'errors' => $errors,
            'output' => $this->formatOutput($connection, $sourceTable, $employeeTable, $dateFrom, $dateTo, $stats, $apply, $errors),
        ];
    }

    private function buildEmployeeMap(iterable $rows): array
    {
        $rawFingerIds = [];
        foreach ($rows as $row) {
            foreach ($this->fingerLookupKeys((string) ($row->_finger_id_raw ?? '')) as $fingerKey) {
                $rawFingerIds[$fingerKey] = true;
            }
        }

        if (empty($rawFingerIds)) {
            return [];
        }

        $map = [];
        foreach (array_chunk(array_keys($rawFingerIds), 200) as $keys) {
            $employees = KaryawanModel::query()
                ->whereNotNull('finger_id')
                ->where(function ($query) use ($keys): void {
                    $query->whereIn('finger_id', $keys)
                        ->orWhereRaw("TRIM(LEADING '0' FROM finger_id) IN (" . implode(',', array_fill(0, count($keys), '?')) . ')', $keys);
                })
                ->get(['id', 'finger_id', 'employee_code', 'full_name']);

            foreach ($employees as $employee) {
                foreach ($this->fingerLookupKeys((string) ($employee->finger_id ?? '')) as $fingerKey) {
                    if ($fingerKey !== '' && isset($rawFingerIds[$fingerKey])) {
                        $map[$fingerKey] = $employee;
                    }
                }
            }
        }

        return $map;
    }

    private function resolveAssignedJobId(int $karyawanId, string $workDate): ?int
    {
        if ($karyawanId <= 0 || $workDate === '') {
            return null;
        }

        $jobId = ProjectJobAssignmentModel::query()
            ->where('is_active', true)
            ->where('karyawan_id', $karyawanId)
            ->whereDate('work_date', $workDate)
            ->value('job_id');

        return $jobId ? (int) $jobId : null;
    }

    private function classifyErpaOvertimeType(mixed $rawStart, mixed $rawEnd, mixed $rawHours, mixed $rawJenis, ?string $workDate): array
    {
        $start = is_numeric($rawStart) ? (float) $rawStart : null;
        $end = is_numeric($rawEnd) ? (float) $rawEnd : null;
        $hours = is_numeric($rawHours) ? (float) $rawHours : null;
        $jenis = is_numeric($rawJenis) ? (float) $rawJenis : null;
        $normalizedEnd = $this->normalizeTimeValue($rawEnd);
        $endDayOffset = 0;
        if ($end !== null && $end >= 24 && $end < 48) {
            $endDayOffset = 1;
            $normalizedEnd = $this->normalizeTimeValue((string) ($end - 24));
        }
        $normalizedStart = $this->normalizeTimeValue($rawStart);
        $startAt = $workDate && $normalizedStart ? Carbon::parse($workDate.' '.$normalizedStart) : null;
        $endAt = $workDate && $normalizedEnd ? Carbon::parse($workDate.' '.$normalizedEnd) : null;
        if ($endAt && $endDayOffset > 0) {
            $endAt->addDays($endDayOffset);
        } elseif ($endAt && $startAt && $endAt->lt($startAt)) {
            $endAt->addDay();
        }
        $audit = ['source' => 'third_party_spl', 'erpa_jenis_lembur' => $rawJenis,
            'erpa_awal' => $rawStart, 'erpa_akhir' => $rawEnd, 'erpa_jml_jam' => $rawHours];

        $isTargetCarryMarker = in_array($end, [31.0, 32.0], true);
        if ($isTargetCarryMarker) {
            $normalizedEnd = $this->normalizeTimeValue((string) ($end - 24));
            $endAt = $workDate && $normalizedEnd
                ? Carbon::parse($workDate.' '.$normalizedEnd)->addDay()
                : $endAt;
            if (! $startAt || ! $endAt || $endAt->lte($startAt)) {
                return ['overtime_type' => null, 'label' => 'Perlu Review', 'normalized_end_time' => $normalizedEnd,
                    'rule_payload' => $audit, 'review_reason' => 'Window SPL Continues Work Days ERPA tidak valid.'];
            }
            return [
                'overtime_type' => SuratPerintahLemburModel::OVERTIME_TYPE_CONTINUES_NEXT_DAY,
                'label' => 'Continues Work Days',
                'normalized_start_time' => $normalizedStart,
                'normalized_end_time' => $normalizedEnd,
                'rule_payload' => $audit + [
                    'erpa_target_continues_marker' => true,
                    'erpa_end_marker' => (int) $end,
                    'special_type' => SuratPerintahLemburModel::OVERTIME_TYPE_CONTINUES_NEXT_DAY,
                    'payment_mode' => 'payroll',
                ],
            ];
        }

        if ($jenis === 3.0) {
            if (! $startAt || ! $endAt || $endAt->lte($startAt)) {
                return ['overtime_type' => null, 'label' => 'Perlu Review', 'normalized_end_time' => $normalizedEnd,
                    'rule_payload' => $audit, 'review_reason' => 'Window SPL Target ERPA tidak valid.'];
            }
            return [
                'overtime_type' => SuratPerintahLemburModel::OVERTIME_TYPE_WORK_TARGET,
                'label' => 'Target',
                'normalized_end_time' => $normalizedEnd,
                'rule_payload' => $audit + [
                    'work_target_start_at' => $startAt->format('Y-m-d H:i:s'),
                    'work_target_end_at' => $endAt->format('Y-m-d H:i:s'),
                    'work_target_bonus_cap_hours' => 3,
                    'work_target_minimum_actual_hours' => 0,
                    'work_target_bonus_source' => 'remaining_target',
                    'work_target_normal_overtime_after' => true,
                    'work_target_bonus_multiplier_source' => 'profile_target_rule',
                    'work_target_carry_candidate' => $isTargetCarryMarker,
                    'work_target_carry_cutoff_time' => $isTargetCarryMarker ? '12:00' : null,
                    'work_target_carry_bonus_hours' => $isTargetCarryMarker ? 4 : 0,
                    'erpa_end_marker' => $isTargetCarryMarker ? (int) $end : null,
                    'payment_mode' => 'payroll',
                ],
            ];
        }

        if (in_array($jenis, [1.0, 5.0], true)
            && (($start === 12.0 && $end === 13.0) || ($start === 24.0 && $end === 25.0))
            && $hours === 1.0) {
            return [
                'overtime_type' => SuratPerintahLemburModel::OVERTIME_TYPE_NO_REST,
                'label' => 'NoRest 1',
                'normalized_start_time' => $start === 24.0 ? '00:00' : $normalizedStart,
                'normalized_end_time' => $normalizedEnd,
                'rule_payload' => $audit,
            ];
        }

        if (($start === 0.0 || $start === 24.0) && ($end === 1.0 || $end === 25.0)) {
            return ['overtime_type' => null, 'label' => 'Perlu Review', 'normalized_end_time' => $normalizedEnd,
                'rule_payload' => $audit, 'review_reason' => 'Interval malam tidak cocok dengan NoRest 24-25 profil shift; review dahulu.'];
        }
        if ($jenis === 1.0) {
            return ['overtime_type' => null, 'label' => 'Perlu Review', 'normalized_end_time' => $normalizedEnd,
                'rule_payload' => $audit, 'review_reason' => 'Jenis NoRest 1 di luar interval istirahat yang didukung perlu review.'];
        }

        $duration = null;
        if ($start !== null && $end !== null) {
            $duration = $end >= $start ? $end - $start : (24 - $start) + $end;
        }
                $label = $duration !== null && $hours !== null && round($duration - $hours, 4) === -1.0
            ? 'Normal - Bukan NoRest'
            : ($duration !== null && $hours !== null && round($duration - $hours, 4) === 0.0
                ? 'Normal - Overtime'
                : 'Perlu Review');

        return [
            'overtime_type' => $label === 'Perlu Review' ? null : SuratPerintahLemburModel::OVERTIME_TYPE_NORMAL,
            'label' => $label,
            'normalized_end_time' => $normalizedEnd,
            'rule_payload' => $audit,
        ];
    }

    private function resolveErpaContinuesPair(array $erpaType, int $karyawanId, string $workDate): array
    {
        $pairs = AttendanceDailyReviewModel::query()
            ->where('karyawan_id', $karyawanId)
            ->whereDate('work_date', $workDate)
            ->whereNotNull('checkin_at')
            ->whereNotNull('checkout_at')
            ->orderByDesc('id')
            ->get(['id', 'profil_bulan_id', 'checkin_at', 'checkout_at'])
            ->filter(fn ($review) => $review->checkin_at->toDateString() === $workDate
                && $review->checkout_at->toDateString() === Carbon::parse($workDate)->addDay()->toDateString()
                && $review->checkout_at->gt($review->checkin_at))
            ->unique(fn ($review) => $review->checkin_at->format('Y-m-d H:i:s').'|'.$review->checkout_at->format('Y-m-d H:i:s'));

        if ($pairs->count() !== 1) {
            $erpaType['overtime_type'] = null;
            $erpaType['review_reason'] = 'SPL Continues perlu pasangan IN/OUT H+1 yang pasti; lengkapi pairing HR dahulu.';
            return $erpaType;
        }

        $review = $pairs->first();
        $erpaType['normalized_start_time'] = $review->checkin_at->format('H:i');
        $erpaType['normalized_end_time'] = $review->checkout_at->format('H:i');
        $erpaType['target_profil_bulan_id'] = (int) $review->profil_bulan_id;
        $erpaType['rule_payload']['attendance_review_id'] = (int) $review->id;
        $erpaType['rule_payload']['anchor_checkin_at'] = $review->checkin_at->format('Y-m-d H:i:s');
        $erpaType['rule_payload']['carry_checkout_at'] = $review->checkout_at->format('Y-m-d H:i:s');
        return $erpaType;
    }

    private function resolveErpaProfileBreak(array $erpaType, int $karyawanId, string $workDate): array
    {
        $reviews = AttendanceDailyReviewModel::query()
            ->where('karyawan_id', $karyawanId)
            ->whereDate('work_date', $workDate)
            ->get(['profil_bulan_id', 'planned_profil_jam_kerja_id']);
        $shiftIds = $reviews->pluck('planned_profil_jam_kerja_id')->filter()->unique();
        if ($reviews->isEmpty() || $shiftIds->count() !== 1 || $reviews->contains(fn ($review) => ! $review->planned_profil_jam_kerja_id)) {
            $erpaType['overtime_type'] = null;
            $erpaType['review_reason'] = 'Jadwal efektif tanggal SPL belum pasti; verifikasi profil istirahat dahulu.';
            return $erpaType;
        }

        $scheduleModes = ProfilBulanKaryawanModel::query()
            ->where('karyawan_id', $karyawanId)
            ->whereIn('profil_bulan_id', $reviews->pluck('profil_bulan_id'))
            ->pluck('schedule_mode', 'profil_bulan_id');
        foreach ($reviews as $review) {
            $profileId = (int) $review->profil_bulan_id;
            $schedule = DB::table('master_shift_schedules')
                ->where('karyawan_id', $karyawanId)
                ->whereDate('work_date', $workDate)
                ->where('is_active', true)
                ->where(fn ($query) => $query->whereNull('profil_bulan_id')->orWhere('profil_bulan_id', $profileId))
                ->orderByRaw('CASE WHEN profil_bulan_id = ? AND is_locked = 1 THEN 1 WHEN profil_bulan_id = ? AND is_locked = 0 THEN 2 WHEN profil_bulan_id IS NULL AND is_locked = 1 THEN 3 ELSE 4 END', [$profileId, $profileId])
                ->orderByDesc('id')
                ->first(['profil_jam_kerja_id']);
            if ((! $schedule && $scheduleModes->get($profileId) === 'roster_shift')
                || ($schedule && (int) $schedule->profil_jam_kerja_id !== (int) $shiftIds->first())) {
                $erpaType['overtime_type'] = null;
                $erpaType['review_reason'] = 'Roster belum lengkap atau profil attendance belum sesuai jadwal; review dahulu.';
                return $erpaType;
            }
        }

        $profile = ProfilJamKerjaModel::query()->find((int) $shiftIds->first());
        $intervalStart = Carbon::parse($workDate)->startOfDay()->addHours((int) $erpaType['rule_payload']['erpa_awal']);
        $intervalEnd = Carbon::parse($workDate)->startOfDay()->addHours((int) $erpaType['rule_payload']['erpa_akhir']);
        if (! $profile || ! $this->matchesErpaBreak($profile, $workDate, $intervalStart, $intervalEnd)) {
            $erpaType['overtime_type'] = null;
            $erpaType['review_reason'] = 'Interval SPL tidak cocok dengan istirahat shift profil pada tanggal kerja tersebut.';
            return $erpaType;
        }

        $erpaType['rule_payload']['profil_jam_kerja_id'] = (int) $profile->id;
        $erpaType['rule_payload']['break_start_at'] = $intervalStart->format('Y-m-d H:i:s');
        $erpaType['rule_payload']['break_end_at'] = $intervalEnd->format('Y-m-d H:i:s');
        return $erpaType;
    }

    private function markNormalBreakReviewCandidate(array $erpaType, int $karyawanId, string $workDate): array
    {
        $start = (float) ($erpaType['rule_payload']['erpa_awal'] ?? -1);
        $end = (float) ($erpaType['rule_payload']['erpa_akhir'] ?? -1);
        if (!(($start === 12.0 && $end === 13.0) || ($start === 24.0 && $end === 25.0))) {
            return $erpaType;
        }

        $candidate = $erpaType;
        $candidate['overtime_type'] = SuratPerintahLemburModel::OVERTIME_TYPE_NO_REST;
        $resolved = $this->resolveErpaProfileBreak($candidate, $karyawanId, $workDate);
        if (($resolved['overtime_type'] ?? null) !== SuratPerintahLemburModel::OVERTIME_TYPE_NO_REST) {
            return $erpaType;
        }

        $erpaType['rule_payload'] = array_merge($erpaType['rule_payload'] ?? [], [
            'norest_review_candidate' => true,
            'norest_review_type' => SuratPerintahLemburModel::OVERTIME_TYPE_NO_REST,
            'norest_review_reason' => 'SPL Normal berada tepat pada jam istirahat profil; konfirmasi NoRest-1 oleh HR.',
            'profil_jam_kerja_id' => $resolved['rule_payload']['profil_jam_kerja_id'] ?? null,
            'break_start_at' => $resolved['rule_payload']['break_start_at'] ?? null,
            'break_end_at' => $resolved['rule_payload']['break_end_at'] ?? null,
        ]);
        $erpaType['review_reason'] = 'SPL Normal berada pada jam istirahat profil; review dan ubah ke NoRest-1 bila sesuai.';
        return $erpaType;
    }

    private function matchesErpaBreak(ProfilJamKerjaModel $profile, string $workDate, Carbon $intervalStart, Carbon $intervalEnd): bool
    {
        $shiftStart = $profile->start_time;
        $shiftEnd = $profile->end_time;
        $start = $profile->break_start_time;
        $end = $profile->break_end_time;
        $dayRule = $profile->weekly_schedule[(string) Carbon::parse($workDate)->dayOfWeekIso] ?? null;
        if (is_array($dayRule) && ! empty($dayRule['start_time']) && ! empty($dayRule['end_time'])) {
            $shiftStart = $dayRule['start_time'];
            $shiftEnd = $dayRule['end_time'];
            $start = $dayRule['break_start_time'] ?? null;
            $end = $dayRule['break_end_time'] ?? null;
        }

        $times = array_map($this->normalizeTimeValue(...), [$shiftStart, $shiftEnd, $start, $end]);
        if (in_array(null, $times, true)) {
            return false;
        }
        [$shiftStartTime, $shiftEndTime, $breakStartTime, $breakEndTime] = $times;
        $shiftStartAt = Carbon::parse($workDate.' '.$shiftStartTime);
        $shiftEndAt = Carbon::parse($workDate.' '.$shiftEndTime);
        if ($shiftEndAt->lte($shiftStartAt)) {
            $shiftEndAt->addDay();
        }
        $breakStartAt = Carbon::parse($workDate.' '.$breakStartTime);
        if ($breakStartAt->lt($shiftStartAt)) {
            $breakStartAt->addDay();
        }
        $breakEndAt = Carbon::parse($workDate.' '.$breakEndTime);
        if ($breakEndAt->lte($breakStartAt)) {
            $breakEndAt->addDay();
        }

        return $breakStartAt->gte($shiftStartAt) && $breakEndAt->lte($shiftEndAt)
            && $intervalStart->equalTo($breakStartAt) && $intervalEnd->equalTo($breakEndAt);
    }

    private function resolveErpaTargetProfile(array $erpaType, int $karyawanId, string $workDate): array
    {
        $profileIds = AttendanceDailyReviewModel::query()
            ->where('karyawan_id', $karyawanId)
            ->whereDate('work_date', $workDate)
            ->distinct()
            ->pluck('profil_bulan_id')
            ->filter()
            ->unique();
        if ($profileIds->isEmpty()) {
            $profileIds = ProfilBulanKaryawanModel::query()
                ->where('karyawan_id', $karyawanId)
                ->where('is_active', true)
                ->whereHas('profilBulan', fn ($query) => $query
                    ->whereDate('start_date', '<=', $workDate)
                    ->whereDate('end_date', '>=', $workDate))
                ->pluck('profil_bulan_id')
                ->unique();
        }
        if ($profileIds->count() !== 1) {
            $erpaType['overtime_type'] = null;
            $erpaType['review_reason'] = 'SPL Target perlu satu Profil Bulan yang pasti pada tanggal kerja.';
            return $erpaType;
        }
        $erpaType['target_profil_bulan_id'] = (int) $profileIds->first();
        return $erpaType;
    }

    private function buildReason(object $row, string $sourceTable, string $fingerRaw, string $dateFrom, string $dateTo, string $erpNotrans = '', string $overtimeLabel = ''): string
    {
        $parts = [];
        $sourceReason = trim((string) ($row->_reason_raw ?? ''));
        if ($sourceReason !== '') {
            $parts[] = $sourceReason;
        }
        $employeeName = trim((string) ($row->_employee_name_raw ?? ''));
        $employeeCode = trim((string) ($row->_employee_code_raw ?? ''));
        $sourceLabel = trim(implode(' | ', array_filter([
            'Sync 3rd party: ' . $sourceTable,
            $employeeCode !== '' ? 'Emp ' . $employeeCode : null,
            $employeeName !== '' ? $employeeName : null,
            $fingerRaw !== '' ? 'Finger ' . $fingerRaw : null,
        ])));
        $parts[] = $sourceLabel;
        if ($erpNotrans !== '') {
            $parts[] = 'No Trans: ' . $erpNotrans;
        }
        if ($overtimeLabel !== '') {
            $parts[] = 'Jenis: ' . $overtimeLabel;
        }

        return trim(implode("\n", array_filter($parts)));
    }

    private function buildSourceReference(string $sourceTable, object $row, string $fingerRaw, string $workDate, string $startTime, string $endTime): string
    {
        $pk = trim((string) ($row->_source_pk ?? ''));
        if ($pk !== '') {
            return strtolower($sourceTable) . '#' . $pk;
        }

        return strtolower($sourceTable) . '#' . sha1(implode('|', [
            strtoupper(trim($fingerRaw)),
            $workDate,
            $startTime,
            $endTime,
            trim((string) ($row->_reason_raw ?? '')),
        ]));
    }

    private function detectJoinPair(array $sourceColumns, array $employeeColumns, string $explicitSourceKey = '', string $explicitEmployeeKey = ''): ?array
    {
        $explicitSourceKey = trim($explicitSourceKey);
        $explicitEmployeeKey = trim($explicitEmployeeKey);
        if ($explicitSourceKey !== '' && $explicitEmployeeKey !== '') {
            if (in_array($explicitSourceKey, $sourceColumns, true) && in_array($explicitEmployeeKey, $employeeColumns, true)) {
                return ['source_key' => $explicitSourceKey, 'employee_key' => $explicitEmployeeKey];
            }
            return null;
        }

        $pairs = [
            ['id_user', 'idx'],
            ['pegawai_id', 'pegawai_id'],
            ['id_pegawai', 'id_pegawai'],
            ['employee_id', 'employee_id'],
            ['karyawan_id', 'karyawan_id'],
            ['nik', 'nik'],
            ['employee_code', 'employee_code'],
            ['id_finger', 'id_finger'],
            ['finger_id', 'finger_id'],
            ['pegawai_id', 'id'],
            ['id_pegawai', 'id'],
            ['employee_id', 'id'],
            ['karyawan_id', 'id'],
        ];
        foreach ($pairs as [$sourceKey, $employeeKey]) {
            if (in_array($sourceKey, $sourceColumns, true) && in_array($employeeKey, $employeeColumns, true)) {
                return ['source_key' => $sourceKey, 'employee_key' => $employeeKey];
            }
        }

        return null;
    }

    private function detectJoinFallbackPair(array $sourceColumns, array $employeeColumns, ?array $primaryPair = null): ?array
    {
        $pairs = [
            ['id_pegawai', 'id_pegawai'],
            ['id_pegawai', 'id'],
            ['pegawai_id', 'id_pegawai'],
            ['pegawai_id', 'id'],
            ['employee_id', 'id'],
            ['karyawan_id', 'id'],
        ];

        foreach ($pairs as [$sourceKey, $employeeKey]) {
            if ($primaryPair
                && $primaryPair['source_key'] === $sourceKey
                && $primaryPair['employee_key'] === $employeeKey) {
                continue;
            }
            if (in_array($sourceKey, $sourceColumns, true) && in_array($employeeKey, $employeeColumns, true)) {
                return ['source_key' => $sourceKey, 'employee_key' => $employeeKey];
            }
        }

        return null;
    }

    private function pickColumn(array $columns, mixed $explicit, array $candidates): ?string
    {
        $explicit = trim((string) $explicit);
        if ($explicit !== '') {
            return in_array($explicit, $columns, true) ? $explicit : null;
        }

        $lowerMap = [];
        foreach ($columns as $column) {
            $lowerMap[strtolower((string) $column)] = (string) $column;
        }
        foreach ($candidates as $candidate) {
            $key = strtolower((string) $candidate);
            if (isset($lowerMap[$key])) {
                return $lowerMap[$key];
            }
        }

        return null;
    }

    private function groupSyncDateRanges(array $pendingDateRows): array
    {
        if (empty($pendingDateRows)) {
            return [];
        }

        usort($pendingDateRows, fn (array $a, array $b) => strcmp((string) ($a['work_date'] ?? ''), (string) ($b['work_date'] ?? '')));

        $ranges = [];
        $current = null;
        foreach ($pendingDateRows as $row) {
            $workDate = (string) ($row['work_date'] ?? '');
            if ($workDate === '') {
                continue;
            }

            if (!$current) {
                $current = [
                    'date_from' => $workDate,
                    'date_to' => $workDate,
                    'days' => 1,
                    'source_count' => (int) ($row['source_count'] ?? 0),
                    'local_count' => (int) ($row['local_count'] ?? 0),
                    'remaining_count' => (int) ($row['remaining_count'] ?? 0),
                    'status' => (string) ($row['status'] ?? 'missing'),
                ];
                continue;
            }

            $previousDate = Carbon::parse($current['date_to']);
            $currentDate = Carbon::parse($workDate);
            if ($previousDate->copy()->addDay()->isSameDay($currentDate)) {
                $current['date_to'] = $workDate;
                $current['days']++;
                $current['source_count'] += (int) ($row['source_count'] ?? 0);
                $current['local_count'] += (int) ($row['local_count'] ?? 0);
                $current['remaining_count'] += (int) ($row['remaining_count'] ?? 0);
                if (($row['status'] ?? '') === 'actionable' || $current['status'] === 'actionable') {
                    $current['status'] = 'actionable';
                } elseif (($row['status'] ?? '') === 'partial' || $current['status'] === 'partial') {
                    $current['status'] = 'partial';
                }
                continue;
            }

            $ranges[] = $current;
            $current = [
                'date_from' => $workDate,
                'date_to' => $workDate,
                'days' => 1,
                'source_count' => (int) ($row['source_count'] ?? 0),
                'local_count' => (int) ($row['local_count'] ?? 0),
                'remaining_count' => (int) ($row['remaining_count'] ?? 0),
                'status' => (string) ($row['status'] ?? 'missing'),
            ];
        }

        if ($current) {
            $ranges[] = $current;
        }

        return array_map(function (array $range) {
            $range['status_label'] = match ($range['status']) {
                'actionable' => 'Perlu Sync',
                'partial' => 'Belum Penuh',
                default => 'Belum Terambil',
            };
            return $range;
        }, $ranges);
    }

    private function fingerLookupKeys(string $value): array
    {
        $value = strtoupper(trim($value));
        if ($value === '') {
            return [];
        }

        $keys = [$value];
        $digits = preg_replace('/\D+/', '', $value);
        if ($digits !== '') {
            $trimmed = ltrim($digits, '0');
            if ($trimmed !== '' && !in_array($trimmed, $keys, true)) {
                $keys[] = $trimmed;
            }
        }

        return array_values(array_unique(array_filter($keys)));
    }

    private function normalizeDateString(string $value): ?string
    {
        $value = trim($value);
        if ($value === '') {
            return null;
        }
        try {
            return Carbon::parse($value)->toDateString();
        } catch (\Throwable) {
            return null;
        }
    }

    private function normalizeDateValue(mixed $value): ?string
    {
        if ($value === null || $value === '') {
            return null;
        }
        try {
            return Carbon::parse((string) $value)->toDateString();
        } catch (\Throwable) {
            return null;
        }
    }

    private function normalizeTimeValue(mixed $value): ?string
    {
        if ($value === null || $value === '') {
            return null;
        }

        $raw = trim((string) $value);
        if ($raw === '') {
            return null;
        }

        if (preg_match('/^\d{1,2}$/', $raw)) {
            $hour = (int) $raw;
            if ($hour >= 0 && $hour <= 23) {
                return sprintf('%02d:00', $hour);
            }
        }

        if (preg_match('/^\d{3,4}$/', $raw)) {
            $padded = str_pad($raw, 4, '0', STR_PAD_LEFT);
            $hour = (int) substr($padded, 0, 2);
            $minute = (int) substr($padded, 2, 2);
            if ($hour >= 0 && $hour <= 23 && $minute >= 0 && $minute <= 59) {
                return sprintf('%02d:%02d', $hour, $minute);
            }
        }

        if (preg_match('/^\d{1,2}:\d{1,2}(:\d{1,2})?$/', $raw)) {
            $parts = explode(':', $raw);
            $hour = (int) ($parts[0] ?? 0);
            $minute = (int) ($parts[1] ?? 0);
            if ($hour >= 0 && $hour <= 23 && $minute >= 0 && $minute <= 59) {
                return sprintf('%02d:%02d', $hour, $minute);
            }
        }

        if (preg_match('/^\d{1,2}\.\d+$/', $raw)) {
            $float = (float) $raw;
            if ($float >= 0 && $float < 24) {
                $hour = (int) floor($float);
                $minute = (int) round(($float - $hour) * 60);
                if ($minute === 60) {
                    $hour++;
                    $minute = 0;
                }
                if ($hour >= 0 && $hour <= 23) {
                    return sprintf('%02d:%02d', $hour, $minute);
                }
            }
        }

        try {
            return Carbon::parse($raw)->format('H:i');
        } catch (\Throwable) {
            return null;
        }
    }

    private function wrapIdentifier(string $identifier): string
    {
        $identifier = trim($identifier);
        if ($identifier === '*') {
            return '*';
        }

        $segments = array_filter(explode('.', $identifier), fn ($segment) => trim((string) $segment) !== '');
        if (!$segments) {
            return '``';
        }

        return implode('.', array_map(function (string $segment): string {
            $segment = trim($segment);
            if ($segment === '*') {
                return '*';
            }

            return '`' . str_replace('`', '``', $segment) . '`';
        }, $segments));
    }

    private function selectColumn(string $identifier, string $alias): string
    {
        return $this->wrapIdentifier($identifier) . ' as ' . $this->wrapIdentifier($alias);
    }

    private function selectCoalesce(array $identifiers, string $alias): string
    {
        $wrapped = array_values(array_filter(array_map(function ($identifier) {
            $identifier = trim((string) $identifier);
            return $identifier !== '' ? $this->wrapIdentifier($identifier) : null;
        }, $identifiers)));

        return 'COALESCE(' . implode(', ', $wrapped) . ') as ' . $this->wrapIdentifier($alias);
    }

    private function buildSplSourcePayload(
        int $karyawanId,
        ?int $jobId,
        string $workDate,
        string $startTime,
        string $endTime,
        string $reason,
        string $fingerRaw,
        array $erpaType
    ): array {
        return [
            'karyawan_id' => $karyawanId,
            'job_id' => $jobId ?: null,
            'work_date' => $workDate,
            'start_time' => $startTime,
            'end_time' => $endTime,
            'reason' => trim($reason),
            'finger_raw' => strtoupper(trim($fingerRaw)),
            'overtime_type' => $erpaType['overtime_type'],
            'rule_payload' => $erpaType['rule_payload'],
            'target_profil_bulan_id' => $erpaType['target_profil_bulan_id'] ?? null,
        ];
    }

    private function canRepairApprovedTargetClassification(SuratPerintahLemburModel $row, array $erpaType): bool
    {
        if ((string) $row->source_system !== 'third_party_spl'
            || (string) $row->status !== 'approved'
            || (string) $row->overtime_type === (string) ($erpaType['overtime_type'] ?? '')) {
            return false;
        }

        if ((string) ($erpaType['overtime_type'] ?? '') === SuratPerintahLemburModel::OVERTIME_TYPE_CONTINUES_NEXT_DAY) {
            return !empty($erpaType['rule_payload']['erpa_target_continues_marker']);
        }

        return (string) ($erpaType['overtime_type'] ?? '') === SuratPerintahLemburModel::OVERTIME_TYPE_WORK_TARGET
            && (int) ($erpaType['target_profil_bulan_id'] ?? 0) > 0;
    }
    private function buildComparableSplPayloadFromModel(SuratPerintahLemburModel $row): array
    {
        return [
            'karyawan_id' => (int) ($row->karyawan_id ?? 0),
            'job_id' => $row->job_id ? (int) $row->job_id : null,
            'work_date' => $this->normalizeDateValue($row->work_date ?? null) ?: '',
            'start_time' => $this->normalizeTimeValue($row->start_time ?? null) ?: '',
            'end_time' => $this->normalizeTimeValue($row->end_time ?? null) ?: '',
            'reason' => trim((string) ($row->reason ?? '')),
            'overtime_type' => $row->overtime_type,
            'rule_payload' => $row->rule_payload,
            'target_profil_bulan_id' => $row->target_profil_bulan_id,
        ];
    }

    private function buildPayloadHash(array $payload): string
    {
        ksort($payload);
        return sha1(json_encode($payload, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES));
    }

    private function summarizePayloadChanges(array $before, array $after, array $labels): ?string
    {
        $changes = [];
        foreach ($labels as $key => $label) {
            $from = $before[$key] ?? null;
            $to = $after[$key] ?? null;
            if ($this->stringifySyncValue($from) === $this->stringifySyncValue($to)) {
                continue;
            }
            $changes[] = $label . ': ' . $this->stringifySyncValue($from) . ' => ' . $this->stringifySyncValue($to);
        }

        return $changes ? implode('; ', $changes) : null;
    }

    private function stringifySyncValue(mixed $value): string
    {
        if ($value === null || $value === '') {
            return '-';
        }
        if (is_bool($value)) {
            return $value ? 'ya' : 'tidak';
        }
        if (is_array($value)) {
            ksort($value);
            return json_encode($value, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
        }

        return trim((string) $value);
    }

    private function hasPayloadChanged(string $storedHash, string $newHash, ?string $changeSummary): bool
    {
        if ($changeSummary === null && $storedHash !== '' && hash_equals($storedHash, $newHash)) {
            return false;
        }

        return $changeSummary !== null;
    }

    private function mergeSyncMetaIntoSplPayload(array $payload, array $meta): array
    {
        if (Schema::hasColumn('surat_perintah_lemburs', 'erp_notrans') && array_key_exists('erp_notrans', $meta)) {
            $payload['erp_notrans'] = trim((string) $meta['erp_notrans']) ?: null;
        }
        if (Schema::hasColumn('surat_perintah_lemburs', 'source_synced_at') && array_key_exists('source_synced_at', $meta)) {
            $payload['source_synced_at'] = $meta['source_synced_at'];
        }
        if (Schema::hasColumn('surat_perintah_lemburs', 'source_payload_hash') && array_key_exists('source_payload_hash', $meta)) {
            $payload['source_payload_hash'] = $meta['source_payload_hash'];
        }
        if (Schema::hasColumn('surat_perintah_lemburs', 'sync_status') && array_key_exists('sync_status', $meta)) {
            $payload['sync_status'] = $meta['sync_status'];
        }
        if (Schema::hasColumn('surat_perintah_lemburs', 'sync_last_checked_at') && array_key_exists('sync_last_checked_at', $meta)) {
            $payload['sync_last_checked_at'] = $meta['sync_last_checked_at'];
        }
        if (Schema::hasColumn('surat_perintah_lemburs', 'sync_change_summary') && array_key_exists('sync_change_summary', $meta)) {
            $payload['sync_change_summary'] = $meta['sync_change_summary'];
        }

        return $payload;
    }

    private function applySyncMetaToSpl(SuratPerintahLemburModel $row, array $meta): void
    {
        $payload = $this->mergeSyncMetaIntoSplPayload([], $meta);
        if ($payload === []) {
            return;
        }
        $row->fill($payload);
        $row->save();
    }

    private function ensureConnectionConfigured(string $connection): ?string
    {
        if ($connection !== 'third_party') {
            return config("database.connections.{$connection}")
                ? null
                : "Database connection [{$connection}] belum dikonfigurasi di config/database.php.";
        }

        $configured = config('database.connections.third_party', []);
        $host = trim((string) ($configured['host'] ?? ''));
        $database = trim((string) ($configured['database'] ?? ''));
        $username = trim((string) ($configured['username'] ?? ''));
        if ($host === '' || $database === '' || $username === '') {
            return 'Database connection [third_party] belum dikonfigurasi. Isi .env THIRD_DB_HOST, THIRD_DB_DATABASE, THIRD_DB_USERNAME, THIRD_DB_PASSWORD lalu jalankan php artisan optimize:clear.';
        }

        return null;
    }

    private function formatConnectionException(string $connection, \Throwable $e): string
    {
        $configured = config("database.connections.{$connection}", []);
        $host = (string) ($configured['host'] ?? '-');
        $port = (string) ($configured['port'] ?? '-');
        $database = (string) ($configured['database'] ?? '-');
        $username = (string) ($configured['username'] ?? '-');

        return implode("\n", [
            'Koneksi database pihak ketiga gagal.',
            "Connection: {$connection}",
            "Host: {$host}",
            "Port: {$port}",
            "Database: {$database}",
            "Username: {$username}",
            'Error: ' . $e->getMessage(),
        ]);
    }

    private function tableExists(?string $connection, string $table): bool
    {
        $table = trim($table);
        if ($table === '') {
            return false;
        }
        $db = $connection ? DB::connection($connection) : DB::connection();
        $row = $db->selectOne(
            'SELECT COUNT(*) as total FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = ?',
            [$table]
        );
        return (int) ($row->total ?? 0) > 0;
    }

    private function formatOutput(string $connection, string $sourceTable, string $employeeTable, string $dateFrom, string $dateTo, array $stats, bool $apply, array $errors): string
    {
        $lines = [
            'Sync SPL 3rd Party',
            'Source SPL: ' . $connection . '.' . $sourceTable,
            'Source Pegawai: ' . $connection . '.' . $employeeTable,
            'Range: ' . $dateFrom . ' s/d ' . $dateTo,
            'Mode: ' . ($apply ? 'APPLY' : 'DRY-RUN'),
            '',
            'Processed: ' . (int) ($stats['processed'] ?? 0),
            'Matched karyawan: ' . (int) ($stats['matched_karyawan'] ?? 0),
            'Created: ' . (int) ($stats['created'] ?? 0),
            'Updated: ' . (int) ($stats['updated'] ?? 0),
            'Unchanged: ' . (int) ($stats['unchanged'] ?? 0),
            'Changed pending: ' . (int) ($stats['changed_pending'] ?? 0),
            'Changed locked: ' . (int) ($stats['changed_locked'] ?? 0),
            'Skip missing finger: ' . (int) ($stats['skipped_missing_finger'] ?? 0),
            'Skip missing karyawan: ' . (int) ($stats['skipped_missing_karyawan'] ?? 0),
            'Skip invalid datetime: ' . (int) ($stats['skipped_invalid_datetime'] ?? 0),
            'Skip perlu review: ' . (int) ($stats['skipped_needs_review'] ?? 0),
            'Skip existing: ' . (int) ($stats['skipped_existing'] ?? 0),
            'Skip locked: ' . (int) ($stats['skipped_locked'] ?? 0),
            'Submitted: ' . (int) ($stats['submitted'] ?? 0),
            'Skip auto submit: ' . (int) ($stats['skipped_auto_submit'] ?? 0),
            'Failed auto submit: ' . (int) ($stats['failed_auto_submit'] ?? 0),
            'Approved: ' . (int) ($stats['approved'] ?? 0),
            'Skip auto approve: ' . (int) ($stats['skipped_auto_approve'] ?? 0),
            'Failed auto approve: ' . (int) ($stats['failed_auto_approve'] ?? 0),
            'Failed: ' . (int) ($stats['failed'] ?? 0),
        ];
        if ($errors) {
            $lines[] = '';
            $lines[] = 'Sample error:';
            foreach ($errors as $error) {
                $lines[] = '- ' . $error;
            }
        }

        return implode("\n", $lines);
    }

    private function fail(string $message, array $meta = []): array
    {
        return [
            'ok' => false,
            'status' => 'failed',
            'message' => $message,
            'meta' => $meta,
            'stats' => [],
            'errors' => [$message],
            'output' => $message,
        ];
    }

    private function autoSubmitSplRow(SuratPerintahLemburModel $row, array &$stats, array &$errors): void
    {
        if ($this->requiresManualSplApproval($row)) {
            $stats['skipped_auto_submit'] = (int) ($stats['skipped_auto_submit'] ?? 0) + 1;
            return;
        }
        $flowId = (int) ($row->approval_flow_id ?: 0);
        if ($flowId <= 0 && (string) ($row->source_system ?? '') === 'third_party_spl') {
            $flowId = $this->resolveThirdPartySplFlowId();
            if ($flowId > 0) {
                $row->approval_flow_id = $flowId;
                $row->save();
            }
        }
        if ($flowId <= 0) {
            $stats['failed_auto_submit'] = (int) ($stats['failed_auto_submit'] ?? 0) + 1;
            if (count($errors) < 20) {
                $errors[] = 'SPL #' . (int) $row->id . ' gagal auto submit: flow approval belum ada.';
            }
            return;
        }
        if (!$this->approvalFlowHasActiveSteps($flowId)) {
            $stats['skipped_auto_submit'] = (int) ($stats['skipped_auto_submit'] ?? 0) + 1;
            return;
        }

        try {
            DB::transaction(function () use ($row, $flowId): void {
                $service = new ApprovalWorkflowService();

                if ($row->approval_request_id) {
                    $oldRequest = DB::table('approval_requests')
                        ->where('id', (int) $row->approval_request_id)
                        ->first(['id', 'status', 'flow_id']);
                    if (!$oldRequest || (string) $oldRequest->status === 'rejected' || (int) $oldRequest->flow_id !== $flowId) {
                        DB::table('approval_requests')->where('id', (int) $row->approval_request_id)->delete();
                        $row->approval_request_id = null;
                        $row->status = 'draft';
                    }
                }

                if (!$row->approval_request_id) {
                    $req = $service->createDraftRequest(
                        $flowId,
                        'overtime',
                        'surat_perintah_lemburs',
                        (int) $row->id,
                        (int) ($row->created_by ?: 0) ?: null,
                        'APR-SPL-' . $row->id
                    );
                    $row->approval_request_id = (int) $req->id;
                }

                if (!in_array((string) ($row->status ?? ''), ['draft', 'submitted', 'in_review'], true)) {
                    return;
                }

                if ((string) ($row->status ?? '') !== 'draft' && $row->approval_request_id) {
                    return;
                }

                $req = $service->submit((int) $row->approval_request_id);
                $row->status = (string) ($req->status ?? 'draft');
                $row->save();
            });

            $stats['submitted'] = (int) ($stats['submitted'] ?? 0) + 1;
        } catch (\Throwable $e) {
            $stats['failed_auto_submit'] = (int) ($stats['failed_auto_submit'] ?? 0) + 1;
            if (count($errors) < 20) {
                $errors[] = 'SPL #' . (int) $row->id . ' gagal auto submit: ' . $e->getMessage();
            }
        }
    }

    private function autoApproveSplRow(SuratPerintahLemburModel $row, array &$stats, array &$errors, int $userId = 0): void
    {
        if ($this->requiresManualSplApproval($row)) {
            $stats['skipped_auto_approve'] = (int) ($stats['skipped_auto_approve'] ?? 0) + 1;
            return;
        }
        try {
            $row->refresh();
        } catch (\Throwable $e) {
            // Refresh is best-effort; the current model still contains enough data for error reporting.
        }

        if (!(int) ($row->approval_flow_id ?: 0) && (string) ($row->source_system ?? '') === 'third_party_spl') {
            $flowId = $this->resolveThirdPartySplFlowId();
            if ($flowId > 0) {
                $row->approval_flow_id = $flowId;
                $row->save();
            }
        }

        if ((string) ($row->status ?? '') === 'approved') {
            $stats['skipped_auto_approve'] = (int) ($stats['skipped_auto_approve'] ?? 0) + 1;
            return;
        }

        $approvalRequestId = (int) ($row->approval_request_id ?: 0);
        if ($approvalRequestId <= 0) {
            if ($this->isThirdPartyWithoutPicSpl($row)) {
                $row->status = 'approved';
                $row->save();
                $stats['approved'] = (int) ($stats['approved'] ?? 0) + 1;
                return;
            }

            $stats['failed_auto_approve'] = (int) ($stats['failed_auto_approve'] ?? 0) + 1;
            if (count($errors) < 20) {
                $errors[] = 'SPL #' . (int) $row->id . ' gagal auto approve: approval request belum ada.';
            }
            return;
        }

        $actorUserId = $userId > 0 ? $userId : ((int) ($row->created_by ?: 0) ?: 1);

        try {
            DB::transaction(function () use ($row, $approvalRequestId, $actorUserId): void {
                $service = new ApprovalWorkflowService();
                $request = ApprovalRequestModel::query()->with('steps')->findOrFail($approvalRequestId);

                if ((string) $request->status === 'draft') {
                    $request = $service->submit((int) $request->id);
                }

                $guard = 0;
                while (in_array((string) $request->status, ['submitted', 'in_review'], true) && $guard < 20) {
                    $request = $service->approve(
                        (int) $request->id,
                        $actorUserId,
                        'Auto approve SPL dari cron sync 3rd party.',
                        null,
                        true
                    );
                    $guard++;
                }

                if ((string) $request->status !== 'approved') {
                    throw new \RuntimeException('approval belum final: ' . (string) $request->status);
                }

                $row->status = 'approved';
                $row->save();
            });

            $stats['approved'] = (int) ($stats['approved'] ?? 0) + 1;
        } catch (\Throwable $e) {
            $stats['failed_auto_approve'] = (int) ($stats['failed_auto_approve'] ?? 0) + 1;
            if (count($errors) < 20) {
                $errors[] = 'SPL #' . (int) $row->id . ' gagal auto approve: ' . $e->getMessage();
            }
        }
    }

    private function requiresManualSplApproval(SuratPerintahLemburModel $row): bool
    {
        return in_array((string) $row->overtime_type, [
            SuratPerintahLemburModel::OVERTIME_TYPE_NO_REST,
            SuratPerintahLemburModel::OVERTIME_TYPE_NO_REST_SKIP_BREAK,
            SuratPerintahLemburModel::OVERTIME_TYPE_WORK_TARGET,
            SuratPerintahLemburModel::OVERTIME_TYPE_CONTINUES_NEXT_DAY,
        ], true) || ((string) $row->overtime_type === SuratPerintahLemburModel::OVERTIME_TYPE_NORMAL
            && (bool) data_get($row->rule_payload, 'norest_review_candidate', false));
    }

    private function resolveThirdPartySplFlowId(): int
    {
        return (int) (ApprovalFlowModel::query()
            ->where('is_active', true)
            ->where(function ($q) {
                $q->whereNull('module_key')->orWhereIn('module_key', ['overtime', 'lembur']);
            })
            ->orderByRaw("CASE
                WHEN UPPER(COALESCE(code, '')) = 'OT-DEFAULT' AND LOWER(COALESCE(name, '')) LIKE '%without pic%' THEN 0
                WHEN UPPER(COALESCE(code, '')) = 'OT-DEFAULT' THEN 1
                WHEN LOWER(COALESCE(name, '')) LIKE '%without pic%' THEN 2
                ELSE 9
            END")
            ->orderBy('name')
            ->value('id') ?: 0);
    }

    private function approvalFlowHasActiveSteps(int $flowId): bool
    {
        if ($flowId <= 0) {
            return false;
        }

        return DB::table('master_approval_flow_steps')
            ->where('flow_id', $flowId)
            ->where('is_active', true)
            ->exists();
    }

    private function isThirdPartyWithoutPicSpl(SuratPerintahLemburModel $row): bool
    {
        $flowId = (int) ($row->approval_flow_id ?: 0);
        return (string) ($row->source_system ?? '') === 'third_party_spl'
            && $flowId > 0
            && !$this->approvalFlowHasActiveSteps($flowId);
    }
}
