<?php
namespace App\Services;

use App\Core\Auth;
use App\Core\Database;
use App\Core\Dates;
use App\Core\Num;
use App\Core\Settings;
use App\Core\Logger;
use App\Models\Payment;

/**
 * Mesin import Excel/CSV: deteksi header, pemetaan kolom, validasi 24 aturan,
 * pembuatan master otomatis, dan penulisan transaksi dalam satu transaksi DB.
 *
 * PRINSIP: kolom hasil rumus Excel (JT TEMPO, TOTAL, SALDO, PAYMENT, UMUR
 * PIUTANG, dan seluruh bucket aging) TIDAK PERNAH dipakai sebagai nilai sistem.
 * Nilai Excel tetap disimpan di import_rows.raw_json untuk keperluan
 * rekonsiliasi dan ditampilkan pada Laporan Kualitas Data.
 */
class ImportService
{
    /** Definisi field sistem untuk import invoice. */
    public const FIELDS = [
        'area'            => ['label' => 'Area',                    'aliases' => ['AREA', 'WILAYAH', 'CABANG'],                              'group' => 'Identitas'],
        'job_category'    => ['label' => 'Kategori Pekerjaan',      'aliases' => ['JOBS', 'JOB', 'KATEGORI PEKERJAAN', 'JENIS JASA'],        'group' => 'Identitas'],
        'so_no'           => ['label' => 'No. SO',                  'aliases' => ['NO. SO', 'NO SO', 'SO', 'SALES ORDER'],                   'group' => 'Identitas'],
        'invoice_date'    => ['label' => 'Tanggal Invoice',         'aliases' => ['TGL INVOICE', 'TANGGAL INVOICE', 'TGL. INVOICE', 'INVOICE DATE'], 'group' => 'Tanggal', 'required' => true],
        'salesperson'     => ['label' => 'Sales',                   'aliases' => ['SALES', 'SALESMAN', 'SALESPERSON'],                       'group' => 'Identitas'],
        'invoice_no'      => ['label' => 'No. Invoice',             'aliases' => ['NO INVOICE', 'NO. INVOICE', 'NOMOR INVOICE', 'INVOICE NO'], 'group' => 'Identitas', 'required' => true],
        'tax_invoice_no'  => ['label' => 'No. Faktur Pajak',        'aliases' => ['NO FAKTUR PAJAK', 'NO. FAKTUR PAJAK', 'FAKTUR PAJAK', 'NOMOR FAKTUR PAJAK'], 'group' => 'Identitas'],
        'customer'        => ['label' => 'Customer',                'aliases' => ['CUSTOMER', 'PELANGGAN', 'NAMA CUSTOMER'],                 'group' => 'Identitas', 'required' => true],
        'outlet'          => ['label' => 'Outlet',                  'aliases' => ['OUTLET', 'CABANG OUTLET', 'LOKASI'],                      'group' => 'Identitas'],
        'gross_amount'    => ['label' => 'Nominal (Bruto)',         'aliases' => ['NOMINAL', 'JUMLAH', 'AMOUNT', 'NILAI INVOICE'],           'group' => 'Nilai', 'required' => true],
        'revenue_category'=> ['label' => 'PMSC / By Case',          'aliases' => ['PMSC/BY CASE', 'PMSC / BY CASE', 'PMSC', 'KATEGORI PENDAPATAN'], 'group' => 'Identitas'],
        'job_type'        => ['label' => 'Jenis Pekerjaan',         'aliases' => ['STATUS', 'JENIS PEKERJAAN', 'DESKRIPSI PEKERJAAN'],       'group' => 'Identitas'],
        'notes'           => ['label' => 'Catatan',                 'aliases' => ['CATATAN', 'KETERANGAN', 'NOTE', 'NOTES'],                 'group' => 'Lain'],
        'wr_date'         => ['label' => 'Tanggal WR',              'aliases' => ['TGL WR', 'TANGGAL WR', 'WR DATE'],                        'group' => 'Tanggal'],
        'print_date'      => ['label' => 'Tanggal Print',           'aliases' => ['TGL PRINT', 'TANGGAL PRINT'],                             'group' => 'Tanggal'],
        'input_date'      => ['label' => 'Tanggal Input',           'aliases' => ['TGL. INPUT', 'TGL INPUT', 'TANGGAL INPUT'],               'group' => 'Tanggal'],
        'sent_date'       => ['label' => 'Tanggal Kirim',           'aliases' => ['TGL. KIRIM', 'TGL KIRIM', 'TANGGAL KIRIM', 'SENT DATE'],  'group' => 'Tanggal'],
        'received_date'   => ['label' => 'Tanggal Terima',          'aliases' => ['TGL TERIMA', 'TANGGAL TERIMA'],                           'group' => 'Tanggal'],
        'tax_invoice_date'=> ['label' => 'Tanggal Faktur Pajak',    'aliases' => ['TGL FAKTUR PAJAK', 'TANGGAL FAKTUR PAJAK'],               'group' => 'Tanggal'],
        'send_channel'    => ['label' => 'Kanal / PIC Pengiriman',  'aliases' => ['SEND / NOTES TO', 'SEND/NOTES TO', 'SEND TO', 'DIKIRIM KE'], 'group' => 'Lain'],
        'pph23_amount'    => ['label' => 'Potongan PPh 23',         'aliases' => ['POT PPH 23', 'PPH 23', 'POTONGAN PPH 23'],                'group' => 'Potongan'],
        'admin_fee_amount'=> ['label' => 'Potongan Administrasi',   'aliases' => ['POT ADM', 'POTONGAN ADM', 'BIAYA ADMIN'],                 'group' => 'Potongan'],
        'other_deduction' => ['label' => 'Potongan Lain-lain',      'aliases' => ['POT LAIN LAIN', 'POT LAIN-LAIN', 'POTONGAN LAIN'],        'group' => 'Potongan'],
        'paid_amount'     => ['label' => 'Pembayaran Diterima',     'aliases' => ['PEMBAYARAN', 'BAYAR', 'DIBAYAR', 'PAYMENT AMOUNT'],       'group' => 'Pembayaran'],
        'payment_date'    => ['label' => 'Tanggal Bayar',           'aliases' => ['TGL BAYAR', 'TANGGAL BAYAR', 'PAYMENT DATE'],             'group' => 'Pembayaran'],
        'collection_officer' => ['label' => 'Petugas Penagihan',    'aliases' => ['PENAGIH', 'COLLECTOR', 'PETUGAS PENAGIHAN'],              'group' => 'Lain'],
        'payment_term_days'  => ['label' => 'Termin (hari)',        'aliases' => ['TERMIN', 'TOP', 'TERM'],                                  'group' => 'Lain'],
    ];

    /** Kolom Excel hasil rumus yang sengaja diabaikan (dihitung ulang server). */
    public const IGNORED_HEADERS = [
        'JT TEMPO', 'JATUH TEMPO', 'TOTAL', 'SALDO', 'PAYMENT', 'UMUR PIUTANG',
        '0-45', '45-90', '90-120', '>120', 'TOTAL REVENUE', 'NET', 'COLUMN 1',
    ];

    /** Definisi field untuk import RF (revisi invoice). */
    public const FIELDS_RF = [
        'rf_date'          => ['label' => 'Tanggal RF',          'aliases' => ['TGL RF', 'TANGGAL RF'],                                 'group' => 'RF'],
        'rf_no'            => ['label' => 'Nomor RF',            'aliases' => ['NO RF', 'NOMOR RF'],                                    'group' => 'RF', 'required' => true],
        'old_invoice_date' => ['label' => 'Tanggal Invoice Lama','aliases' => ['TGL INVOICE', 'TANGGAL INVOICE LAMA'],                  'group' => 'RF'],
        'old_invoice_no'   => ['label' => 'No. Invoice Lama',    'aliases' => ['NO INVOICE YANG DI RF', 'NO INVOICE LAMA', 'INVOICE LAMA'], 'group' => 'RF', 'required' => true],
        'new_invoice_no'   => ['label' => 'No. Invoice Baru',    'aliases' => ['NO INVOICE YANG BARU', 'NO INVOICE BARU', 'INVOICE BARU'],  'group' => 'RF'],
        'amount'           => ['label' => 'Nominal',             'aliases' => ['NOMINAL', 'JUMLAH'],                                    'group' => 'RF'],
        'reason'           => ['label' => 'Keterangan Revisi',   'aliases' => ['KETERANGAN', 'ALASAN', 'KETERANGAN REVISI'],            'group' => 'RF'],
    ];

    private ExcelReader $reader;
    private MasterResolver $resolver;
    private array $options;
    public array $stats = [];

    public function __construct(string $file, array $options = [])
    {
        $this->reader = new ExcelReader($file);
        $this->options = array_merge([
            'auto_buat_master'   => Settings::bool('import.auto_buat_master', true),
            'perbarui_duplikat'  => Settings::bool('import.perbarui_invoice_duplikat', false),
            'buat_pembayaran'    => Settings::bool('import.buat_pembayaran_dari_kolom', true),
            'header_row'         => 1,
            'start_row'          => 0,     // 0 = header_row + 1
            'import_type'        => 'invoice',
            'source_sheet'       => null,
            'default_area'       => null,
            'lewati_baris_kosong'=> true,
        ], $options);
        $this->resolver = new MasterResolver((bool)$this->options['auto_buat_master']);
    }

    public function reader(): ExcelReader { return $this->reader; }
    public function resolver(): MasterResolver { return $this->resolver; }

    public function sheets(): array { return $this->reader->sheetNames(); }

    /** Daftar field sesuai jenis import. */
    public function fields(): array
    {
        return $this->options['import_type'] === 'revision' ? self::FIELDS_RF : self::FIELDS;
    }

    /**
     * Menebak pemetaan kolom Excel -> field sistem dari judul header.
     * @return array{mapping:array<string,string>,headers:array<string,string>,diabaikan:array<string,string>,tidak_terpetakan:array<string,string>}
     */
    public function guessMapping(?string $sheet = null): array
    {
        $headers = $this->reader->headers($sheet, (int)$this->options['header_row']);
        $fields  = $this->fields();
        $mapping = [];
        $ignored = [];
        $unmapped = [];

        foreach ($headers as $col => $title) {
            $t = MasterResolver::key($title);
            if ($t === '') continue;
            if (in_array($t, self::IGNORED_HEADERS, true)) { $ignored[$col] = $title; continue; }
            $found = null;
            foreach ($fields as $key => $def) {
                foreach ($def['aliases'] as $al) {
                    if (MasterResolver::key($al) === $t) { $found = $key; break 2; }
                }
            }
            if (!$found) {
                // Pencocokan longgar
                $fz = MasterResolver::fuzzyKey($t);
                foreach ($fields as $key => $def) {
                    foreach ($def['aliases'] as $al) {
                        if (MasterResolver::fuzzyKey($al) === $fz) { $found = $key; break 2; }
                    }
                }
            }
            if ($found && !in_array($found, $mapping, true)) $mapping[$col] = $found;
            elseif ($found) $unmapped[$col] = $title . ' (duplikat field ' . $found . ')';
            else $unmapped[$col] = $title;
        }
        return ['mapping' => $mapping, 'headers' => $headers, 'diabaikan' => $ignored, 'tidak_terpetakan' => $unmapped];
    }

    /** Menebak jenis import dari judul header. */
    public function guessType(?string $sheet = null): string
    {
        $h = array_map([MasterResolver::class, 'key'], $this->reader->headers($sheet, (int)$this->options['header_row']));
        foreach ($h as $t) {
            if (str_contains($t, 'NO RF') || str_contains($t, 'YANG DI RF')) return 'revision';
        }
        return 'invoice';
    }

    /** Beberapa baris pertama untuk pratinjau. */
    public function preview(?string $sheet = null, int $limit = 15): array
    {
        $headerRow = (int)$this->options['header_row'];
        $start = $this->options['start_row'] ?: $headerRow + 1;
        $rows = $this->reader->rows($sheet, $limit, $start);
        $out = [];
        foreach ($rows as $n => $cells) {
            $r = ['_baris' => $n];
            foreach ($cells as $col => $c) $r[$col] = $c['v'];
            $out[] = $r;
        }
        return $out;
    }

    // ==================================================================
    // VALIDASI + PEMROSESAN
    // ==================================================================

    /**
     * Memvalidasi & (opsional) menyimpan data.
     *
     * @param string|null $sheet
     * @param array       $mapping [kolomExcel => fieldSistem]
     * @param bool        $dryRun  true = hanya validasi, tidak menulis ke DB
     * @param int|null    $batchId id import_batches (dibuat bila null & !dryRun)
     * @return array ringkasan
     */
    public function run(?string $sheet, array $mapping, bool $dryRun = true, ?int $batchId = null, ?callable $progress = null): array
    {
        $db = Database::i();
        $type = $this->options['import_type'];
        $headerRow = (int)$this->options['header_row'];
        $start = $this->options['start_row'] ?: $headerRow + 1;

        $rows = $this->reader->rows($sheet, 0, $start);
        $maxRows = Settings::int('import.maks_baris_per_batch', 5000);

        $result = [
            'total' => 0, 'sukses' => 0, 'gagal' => 0, 'duplikat' => 0,
            'peringatan' => 0, 'dilewati' => 0, 'baris' => [], 'ringkasan_error' => [],
            'master_baru' => [], 'sheet' => $sheet, 'jenis' => $type,
        ];

        $seenInvoice = [];
        $seenTax = [];
        $n = 0;

        foreach ($rows as $rowNum => $cells) {
            $raw = [];
            foreach ($cells as $col => $c) $raw[$col] = $c['v'];

            // Lewati baris kosong (termasuk baris hantu berisi rumus - DQ-30)
            if ($this->isEmptyRow($cells, $mapping)) { $result['dilewati']++; continue; }

            $n++;
            $result['total']++;
            if ($n > $maxRows) {
                $result['ringkasan_error'][] = "Batas maksimum $maxRows baris per batch tercapai. Sisa baris tidak diproses.";
                break;
            }

            $rec = $this->extract($cells, $mapping);
            $errors = [];
            $warnings = [];

            $processed = $type === 'revision'
                ? $this->processRevisionRow($rec, $raw, $rowNum, $errors, $warnings, $dryRun, $batchId)
                : $this->processInvoiceRow($rec, $raw, $rowNum, $errors, $warnings, $dryRun, $batchId, $seenInvoice, $seenTax);

            // Baris duplikat diklasifikasikan sebagai "duplicate" walaupun ikut
            // menghasilkan pesan kesalahan, agar rekap wizard import dapat
            // membedakan duplikat dari kegagalan validasi lainnya.
            $status = 'ok';
            if (!empty($processed['duplikat'])) $status = 'duplicate';
            elseif ($errors) $status = 'failed';
            elseif ($warnings) $status = 'warning';

            if ($status === 'failed')        $result['gagal']++;
            elseif ($status === 'duplicate') { $result['duplikat']++; if (!empty($processed['tersimpan'])) $result['sukses']++; }
            else {
                $result['sukses']++;
                if ($warnings) $result['peringatan']++;
            }

            $entry = [
                'baris' => $rowNum, 'status' => $status, 'errors' => $errors, 'warnings' => $warnings,
                'invoice_no' => $rec['invoice_no'] ?? ($rec['rf_no'] ?? null),
                'customer' => $rec['customer'] ?? null,
                'invoice_id' => $processed['invoice_id'] ?? null,
                'revision_id' => $processed['revision_id'] ?? null,
                'raw' => $raw,
            ];
            $result['baris'][] = $entry;

            if (!$dryRun && $batchId) {
                $db->insert('import_rows', [
                    'import_batch_id' => $batchId,
                    'row_number' => $rowNum,
                    'raw_json' => json_encode($raw, JSON_UNESCAPED_UNICODE | JSON_INVALID_UTF8_SUBSTITUTE),
                    'normalized_json' => json_encode($processed['normalized'] ?? $rec, JSON_UNESCAPED_UNICODE | JSON_INVALID_UTF8_SUBSTITUTE),
                    'status' => $status,
                    'errors_json' => $errors ? json_encode($errors, JSON_UNESCAPED_UNICODE) : null,
                    'warnings_json' => $warnings ? json_encode($warnings, JSON_UNESCAPED_UNICODE) : null,
                    'invoice_id' => $processed['invoice_id'] ?? null,
                    'revision_id' => $processed['revision_id'] ?? null,
                ]);
            }

            foreach ($errors as $e) {
                $key = preg_replace('/\d+/', 'N', $e);
                $result['ringkasan_error'][$key] = ($result['ringkasan_error'][$key] ?? 0) + 1;
            }
            if ($progress && $n % 100 === 0) $progress($n, count($rows));
        }

        $result['master_baru'] = $this->resolver->summaryCreated();
        $this->stats = $result;
        return $result;
    }

    private function isEmptyRow(array $cells, array $mapping): bool
    {
        $critical = ['invoice_no', 'customer', 'gross_amount', 'invoice_date', 'rf_no', 'old_invoice_no'];
        foreach ($mapping as $col => $field) {
            if (!in_array($field, $critical, true)) continue;
            $v = $cells[$col]['v'] ?? null;
            if ($v !== null && trim((string)$v) !== '' && !(is_string($v) && $v[0] === '#')) return false;
        }
        return true;
    }

    private function extract(array $cells, array $mapping): array
    {
        $rec = [];
        foreach ($mapping as $col => $field) {
            if ($field === '' || $field === '-') continue;
            $c = $cells[$col] ?? null;
            $rec[$field] = $c['v'] ?? null;
            $rec['_err_' . $field] = $c['err'] ?? null;
            $rec['_f_' . $field] = $c['f'] ?? null;
        }
        return $rec;
    }

    // ------------------------------------------------------------------ INVOICE
    private function processInvoiceRow(array $rec, array $raw, int $rowNum, array &$errors, array &$warnings, bool $dryRun, ?int $batchId, array &$seenInvoice, array &$seenTax): array
    {
        $db = Database::i();
        $dq = [];

        // ---- V-17 nilai error Excel
        foreach ($rec as $k => $v) {
            if (str_starts_with((string)$k, '_err_') && $v) {
                $field = substr((string)$k, 5);
                $warnings[] = "Sel kolom \"$field\" berisi nilai error Excel ($v) — diabaikan dan dikosongkan.";
                $rec[$field] = null;
                $dq[] = 'EXCEL_ERROR_' . strtoupper($field);
            }
        }

        // ---- V-01 nomor invoice
        $invoiceNo = MasterResolver::key($rec['invoice_no'] ?? '');
        if ($invoiceNo === '') { $errors[] = 'Nomor invoice wajib diisi.'; }
        elseif (mb_strlen($invoiceNo) > 60) { $errors[] = 'Nomor invoice melebihi 60 karakter.'; }

        // ---- V-03 duplikat dalam berkas
        $dupDalamBerkas = false;
        if ($invoiceNo !== '') {
            if (isset($seenInvoice[$invoiceNo])) {
                $dupDalamBerkas = true;
                $errors[] = "Nomor invoice $invoiceNo duplikat di dalam berkas (baris {$seenInvoice[$invoiceNo]}).";
            } else {
                $seenInvoice[$invoiceNo] = $rowNum;
            }
        }

        // ---- V-04 customer
        $customerName = MasterResolver::norm((string)($rec['customer'] ?? ''));
        if ($customerName === '') $errors[] = 'Nama customer wajib diisi.';

        // ---- V-05/V-06 nominal
        $grossRaw = $rec['gross_amount'] ?? null;
        if ($grossRaw === null || $grossRaw === '') {
            $paidHint = Num::parse($rec['paid_amount'] ?? 0);
            $errors[] = 'Nominal invoice wajib diisi.'
                . ($paidHint > 0 ? ' Baris ini memuat pembayaran ' . Num::rupiah($paidHint) . ' tanpa nilai invoice — lengkapi nominal di berkas sumber lalu impor ulang.' : '');
            $gross = 0.0;
        } else {
            if (is_string($grossRaw) && !Num::looksNumeric($grossRaw)) {
                $errors[] = 'Nominal invoice berisi teks yang tidak dapat dibaca sebagai angka: "' . mb_substr((string)$grossRaw, 0, 40) . '".';
                $gross = 0.0;
            } else {
                $gross = Num::parse($grossRaw);
                if (is_string($grossRaw)) { $warnings[] = 'Nominal invoice berformat teks, dinormalisasi menjadi ' . Num::fmt($gross, 2) . '.'; $dq[] = 'NOMINAL_TEKS'; }
                if ($gross <= 0) $errors[] = 'Nominal invoice harus lebih besar dari nol.';
            }
        }

        // ---- V-07/V-08 tanggal invoice (dengan tanggal cadangan agar baris bernilai tidak hilang)
        $invoiceDate = Dates::parse($rec['invoice_date'] ?? null);
        if (!$invoiceDate) {
            $fallbacks = [
                'tanggal kirim'        => $rec['sent_date'] ?? null,
                'tanggal WR'           => $rec['wr_date'] ?? null,
                'tanggal faktur pajak' => $rec['tax_invoice_date'] ?? null,
                'tanggal print'        => $rec['print_date'] ?? null,
                'tanggal input'        => $rec['input_date'] ?? null,
            ];
            foreach ($fallbacks as $label => $cand) {
                $d = Dates::parse($cand);
                if ($d) {
                    $invoiceDate = $d;
                    $warnings[] = 'Tanggal invoice kosong/tidak valid — memakai ' . $label . ' (' . Dates::fmt($d) . ') sebagai penggantinya.';
                    $dq[] = 'TGL_INVOICE_DARI_CADANGAN';
                    break;
                }
            }
        }
        if (!$invoiceDate) {
            $errors[] = 'Tanggal invoice tidak valid atau kosong dan tidak ada tanggal lain yang dapat dipakai: "'
                      . mb_substr((string)($rec['invoice_date'] ?? ''), 0, 30) . '".';
        } elseif ($invoiceDate > Dates::addDays(Dates::today(), 365)) {
            $warnings[] = 'Tanggal invoice lebih dari 1 tahun ke depan (' . Dates::fmt($invoiceDate) . ').';
            $dq[] = 'TGL_INVOICE_MASA_DEPAN';
        }

        // ---- V-09/V-10 faktur pajak
        $taxNo = MasterResolver::norm((string)($rec['tax_invoice_no'] ?? ''));
        if ($taxNo !== '') {
            if (isset($seenTax[$taxNo])) {
                $warnings[] = "Nomor faktur pajak $taxNo duplikat di dalam berkas (baris {$seenTax[$taxNo]}).";
                $dq[] = 'FP_DUPLIKAT_FILE';
            } else { $seenTax[$taxNo] = $rowNum; }

            if (!$dryRun || true) {
                $exists = $db->value('SELECT invoice_no FROM invoices WHERE tax_invoice_no = :t AND invoice_no <> :i AND deleted_at IS NULL LIMIT 1',
                                     ['t' => $taxNo, 'i' => $invoiceNo]);
                if ($exists) { $warnings[] = "Nomor faktur pajak $taxNo sudah dipakai invoice $exists."; $dq[] = 'FP_DUPLIKAT_DB'; }
            }
            if (!preg_match('/^\d{16,17}$/', $taxNo)) {
                $warnings[] = 'Format nomor faktur pajak tidak standar (diharapkan 16–17 digit angka): "' . mb_substr($taxNo, 0, 40) . '".';
                $dq[] = 'FP_FORMAT';
            }
        } else {
            $dq[] = 'FP_KOSONG';
        }

        // ---- Tanggal lainnya
        $sentDate  = Dates::parse($rec['sent_date'] ?? null);
        $wrDate    = Dates::parse($rec['wr_date'] ?? null);
        $printDate = Dates::parse($rec['print_date'] ?? null);
        $inputDate = Dates::parse($rec['input_date'] ?? null);
        $recvDate  = Dates::parse($rec['received_date'] ?? null);
        $taxDate   = Dates::parse($rec['tax_invoice_date'] ?? null);

        // ---- V-11/V-12
        if ($invoiceDate && $sentDate) {
            $d = Dates::diffDays($invoiceDate, $sentDate);
            if ($d !== null && $d < 0) { $warnings[] = 'Tanggal kirim (' . Dates::fmt($sentDate) . ') lebih awal dari tanggal invoice (' . Dates::fmt($invoiceDate) . ').'; $dq[] = 'KIRIM_SEBELUM_INVOICE'; }
            if ($d !== null && $d > 90) { $warnings[] = "Selisih tanggal kirim dan tanggal invoice $d hari (lebih dari 90 hari)."; $dq[] = 'KIRIM_TERLALU_LAMA'; }
        }
        if (!$sentDate) $dq[] = 'TGL_KIRIM_KOSONG';

        // ---- Potongan (V-18/V-19/V-20)
        $pph  = Num::parse($rec['pph23_amount'] ?? 0);
        $adm  = Num::parse($rec['admin_fee_amount'] ?? 0);
        $oth  = Num::parse($rec['other_deduction'] ?? 0);
        foreach ([['PPh 23', $pph], ['administrasi', $adm], ['lain-lain', $oth]] as [$lbl, $val]) {
            if ($val < 0) $errors[] = "Potongan $lbl tidak boleh negatif.";
        }
        if ($gross > 0 && ($pph + $adm + $oth) > $gross) {
            $errors[] = 'Total potongan (' . Num::rupiah($pph + $adm + $oth) . ') melebihi nominal invoice (' . Num::rupiah($gross) . ').';
        }
        if ($pph > 0 && $gross > 0) {
            $rate = ArCalculator::effectivePphRate($gross, $pph);
            $batas = Settings::float('pajak.batas_wajar_pph23', 20);
            $standar = Settings::float('pajak.tarif_pph23', 2);
            if ($rate !== null && $rate > $batas) {
                $warnings[] = 'Tarif efektif PPh 23 ' . Num::fmt($rate, 2) . '% melebihi batas kewajaran ' . Num::fmt($batas, 0) . '%.';
                $dq[] = 'PPH23_TARIF_TINGGI';
            } elseif ($rate !== null && abs($rate - $standar) > 0.05) {
                $warnings[] = 'Tarif efektif PPh 23 ' . Num::fmt($rate, 2) . '% berbeda dari tarif standar ' . Num::fmt($standar, 2) . '%.';
                $dq[] = 'PPH23_TARIF_NONSTANDAR';
            }
        }

        // ---- Pembayaran (V-13 s/d V-16)
        $paid = Num::parse($rec['paid_amount'] ?? 0);
        $payDate = Dates::parse($rec['payment_date'] ?? null);
        if ($paid < 0) { $errors[] = 'Nilai pembayaran tidak boleh negatif.'; }
        if ($payDate && $paid <= 0) { $warnings[] = 'Terdapat tanggal bayar (' . Dates::fmt($payDate) . ') tetapi nominal pembayaran kosong.'; $dq[] = 'TGL_BAYAR_TANPA_NOMINAL'; }
        if ($paid > 0 && !$payDate) {
            $warnings[] = 'Terdapat pembayaran ' . Num::rupiah($paid) . ' tetapi tanggal bayar kosong — memakai tanggal invoice.';
            $payDate = $invoiceDate;
            $dq[] = 'BAYAR_TANPA_TANGGAL';
        }
        if ($payDate && $invoiceDate && $payDate < $invoiceDate) {
            $warnings[] = 'Tanggal bayar lebih awal dari tanggal invoice.';
            $dq[] = 'BAYAR_SEBELUM_INVOICE';
        }
        $netCheck = $gross - $pph - $adm - $oth;
        if ($paid > $netCheck + 0.5) {
            $warnings[] = 'Pembayaran ' . Num::rupiah($paid) . ' melebihi nilai bersih invoice ' . Num::rupiah($netCheck) . ' (kelebihan bayar ' . Num::rupiah($paid - $netCheck) . ').';
            $dq[] = 'KELEBIHAN_BAYAR';
        }

        // ---- V-02 duplikat di database
        $existing = null;
        if ($invoiceNo !== '') {
            $existing = $db->one('SELECT * FROM invoices WHERE invoice_no = :n', ['n' => $invoiceNo]);
        }
        $isDuplicate = (bool)$existing || $dupDalamBerkas;
        if ($existing && !$this->options['perbarui_duplikat']) {
            $errors[] = "Nomor invoice $invoiceNo sudah ada di database (dibuat " . Dates::fmt(substr((string)$existing['created_at'], 0, 10)) . '). Aktifkan opsi "Perbarui invoice yang sudah ada" bila ingin menimpa.';
        }

        if ($errors) return ['duplikat' => $isDuplicate, 'normalized' => null];

        // ---- Rekonsiliasi dengan kolom rumus Excel (tidak dipakai, hanya dibandingkan)
        $excelTotal = isset($raw['W']) ? Num::parse($raw['W']) : null;
        $excelSaldo = isset($raw['Y']) ? Num::parse($raw['Y']) : null;

        if ($dryRun) {
            return ['duplikat' => $isDuplicate, 'normalized' => [
                'invoice_no' => $invoiceNo, 'customer' => $customerName, 'gross' => $gross,
                'net_hitung' => $netCheck, 'net_excel' => $excelTotal,
                'saldo_hitung' => $netCheck - $paid, 'saldo_excel' => $excelSaldo, 'dq' => $dq,
            ]];
        }

        // ================= PENYIMPANAN =================
        $area = $this->resolver->area((string)($rec['area'] ?? $this->options['default_area'] ?? ''));
        $salesId = $this->resolver->salesperson((string)($rec['salesperson'] ?? ''));
        $cust = $this->resolver->customer($customerName, [
            'area_id' => $area['area_id'], 'business_unit_id' => $area['business_unit_id'], 'salesperson_id' => $salesId,
        ]);
        $customerId = $cust['customer_id'];
        $outletId = $this->resolver->outlet((string)($rec['outlet'] ?? ''), (int)$customerId, ['area_id' => $area['area_id']]);
        $jobCatId = $this->resolver->jobCategory((string)($rec['job_category'] ?? ''));
        $jobTypeId = $this->resolver->jobType((string)($rec['job_type'] ?? ''), $jobCatId);
        $revCatId = $this->resolver->revenueCategory((string)($rec['revenue_category'] ?? ''));
        $send = $this->resolver->sendChannel((string)($rec['send_channel'] ?? ''));

        $termDays = null;
        if (isset($rec['payment_term_days']) && $rec['payment_term_days'] !== null && $rec['payment_term_days'] !== '') {
            $termDays = (int)Num::parse($rec['payment_term_days']);
        }

        $invData = [
            'invoice_no' => $invoiceNo,
            'tax_invoice_no' => $taxNo !== '' ? $taxNo : null,
            'so_no' => MasterResolver::key((string)($rec['so_no'] ?? '')) ?: null,
            'invoice_date' => $invoiceDate,
            'wr_date' => $wrDate, 'print_date' => $printDate, 'input_date' => $inputDate,
            'sent_date' => $sentDate, 'received_date' => $recvDate, 'tax_invoice_date' => $taxDate,
            'area_id' => $area['area_id'], 'business_unit_id' => $area['business_unit_id'],
            'customer_id' => $customerId, 'outlet_id' => $outletId,
            'salesperson_id' => $salesId,
            'job_category_id' => $jobCatId, 'job_type_id' => $jobTypeId, 'revenue_category_id' => $revCatId,
            'send_channel' => $send['channel'], 'send_pic' => $send['pic'],
            'notes' => ($rec['notes'] ?? null) !== null ? mb_substr(MasterResolver::norm((string)$rec['notes']), 0, 2000) : null,
            'gross_amount' => $gross,
            'dq_flags' => $dq ? json_encode(array_values(array_unique($dq)), JSON_UNESCAPED_UNICODE) : null,
            'source' => 'import',
            'import_batch_id' => $batchId,
            'import_row_no' => $rowNum,
            'source_sheet' => $this->options['source_sheet'],
            'created_by' => Auth::check() ? Auth::id() : null,
        ];

        // Jatuh tempo dihitung ulang oleh server
        $due = ArCalculator::computeDueDate($invData + ['payment_term_days' => $termDays], null, $termDays);
        $invData['due_date'] = $due['due_date'] ?: ($invoiceDate ?: Dates::today());
        $invData['due_date_basis'] = $due['basis'];
        $invData['payment_term_days'] = $due['term_days'];

        $invoiceId = null;
        if ($existing) {
            $invoiceId = (int)$existing['id'];
            $invData['updated_by'] = Auth::check() ? Auth::id() : null;
            $db->update('invoices', $invData, 'id = :pk', ['pk' => $invoiceId]);
            $db->exec('DELETE FROM invoice_deductions WHERE invoice_id = ?', [$invoiceId]);
        } else {
            $invoiceId = $db->insert('invoices', $invData);
        }

        // Potongan
        $this->saveDeduction($invoiceId, 'PPH23', $gross, $pph);
        $this->saveDeduction($invoiceId, 'ADM',   $gross, $adm);
        $this->saveDeduction($invoiceId, 'LAIN',  $gross, $oth);

        // Pembayaran dari kolom Excel
        if ($this->options['buat_pembayaran'] && $paid > 0 && $payDate) {
            $this->createImportPayment($invoiceId, (int)$customerId, $paid, $payDate, $netCheck, $batchId);
        }

        ArCalculator::recalculate($invoiceId);

        return ['duplikat' => $isDuplicate, 'tersimpan' => true, 'invoice_id' => $invoiceId,
                'normalized' => ['invoice_no' => $invoiceNo, 'net_excel' => $excelTotal, 'saldo_excel' => $excelSaldo, 'dq' => $dq]];
    }

    private function saveDeduction(int $invoiceId, string $code, float $gross, float $amount): void
    {
        if (abs($amount) < 0.005) return;
        $typeId = $this->resolver->deductionTypeId($code);
        if (!$typeId) return;
        $dpp = ArCalculator::dpp($gross);
        Database::i()->insert('invoice_deductions', [
            'invoice_id' => $invoiceId,
            'deduction_type_id' => $typeId,
            'base_amount' => $dpp,
            'rate' => $dpp > 0 ? round($amount / $dpp * 100, 4) : 0,
            'amount' => round($amount, 2),
            'is_manual' => 1,
            'notes' => 'Dari import Excel',
            'created_by' => Auth::check() ? Auth::id() : null,
        ]);
    }

    private function createImportPayment(int $invoiceId, int $customerId, float $amount, string $date, float $net, ?int $batchId): void
    {
        $db = Database::i();
        $pm = new Payment();
        $type = ($amount + 0.5) >= $net ? 'PELUNASAN' : 'SEBAGIAN';
        $paymentId = $db->insert('payments', [
            'payment_no' => $pm->nextNo($date),
            'payment_date' => $date,
            'customer_id' => $customerId,
            'method' => 'TRANSFER',
            'payment_type' => $type,
            'amount' => round($amount, 2),
            'allocated_amount' => round($amount, 2),
            'notes' => 'Dibuat otomatis dari kolom PEMBAYARAN pada import Excel',
            'status' => 'posted',
            'approval_status' => 'not_required',
            'created_by' => Auth::check() ? Auth::id() : null,
            'import_batch_id' => $batchId,
        ]);
        $db->insert('payment_allocations', [
            'payment_id' => $paymentId,
            'invoice_id' => $invoiceId,
            'amount' => round($amount, 2),
        ]);
    }

    // ------------------------------------------------------------------ REVISI (RF)
    private function processRevisionRow(array $rec, array $raw, int $rowNum, array &$errors, array &$warnings, bool $dryRun, ?int $batchId): array
    {
        $db = Database::i();

        $rfNo = MasterResolver::key((string)($rec['rf_no'] ?? ''));
        if ($rfNo === '') { $errors[] = 'Nomor RF wajib diisi.'; }

        // V-24: parser tanggal bertingkat untuk format campur
        $rfDateRaw = $rec['rf_date'] ?? null;
        $rfDate = Dates::parse($rfDateRaw);
        if ($rfDateRaw !== null && $rfDateRaw !== '' && !$rfDate) {
            $warnings[] = 'Tanggal RF tidak dapat dibaca: "' . mb_substr((string)$rfDateRaw, 0, 30) . '" — dikosongkan.';
        }
        // DQ-28: tahun RF tidak sesuai seri nomor RF
        if ($rfDate && preg_match('#^RF/(\d{2})/#', $rfNo, $m)) {
            $seriYear = 2000 + (int)$m[1];
            $dateYear = (int)substr($rfDate, 0, 4);
            if ($dateYear > $seriYear + 1) {
                $warnings[] = "Tahun tanggal RF ($dateYear) tidak sesuai seri nomor RF ($seriYear).";
            }
        }

        // DQ-25: nomor invoice terduplikasi dalam satu sel (FA/25/2567FA/25/2567)
        $oldNo = $this->cleanInvoiceNo((string)($rec['old_invoice_no'] ?? ''), $warnings, 'lama');
        $newNoRaw = MasterResolver::key((string)($rec['new_invoice_no'] ?? ''));
        $isCancelText = $newNoRaw !== '' && !preg_match('#[A-Z]+/\d{2}/\d+#', $newNoRaw);
        $newNo = $isCancelText ? '' : $this->cleanInvoiceNo($newNoRaw, $warnings, 'baru');
        if ($isCancelText && $newNoRaw !== '') {
            $warnings[] = 'Kolom nomor invoice baru berisi teks status "' . $newNoRaw . '", bukan nomor invoice — diperlakukan sebagai pembatalan.';
        }

        if ($oldNo === '') $errors[] = 'Nomor invoice lama wajib diisi.';
        $amount = Num::parse($rec['amount'] ?? 0);
        $reason = MasterResolver::norm((string)($rec['reason'] ?? ''));
        if ($reason === '' || $reason === $rfNo) {
            $warnings[] = 'Keterangan revisi kosong atau hanya berisi nomor RF.';
            if ($reason === $rfNo) $reason = '';
        }

        if ($rfNo !== '') {
            $dup = $db->one('SELECT id FROM invoice_revisions WHERE rf_no = :n AND deleted_at IS NULL', ['n' => $rfNo]);
            if ($dup) $errors[] = "Nomor RF $rfNo sudah tercatat di sistem.";
        }

        if ($errors) return ['normalized' => null];

        // V-23: pencocokan invoice
        $oldInv = $oldNo !== '' ? $db->one('SELECT * FROM invoices WHERE invoice_no = :n AND deleted_at IS NULL', ['n' => $oldNo]) : null;
        $newInv = $newNo !== '' ? $db->one('SELECT * FROM invoices WHERE invoice_no = :n AND deleted_at IS NULL', ['n' => $newNo]) : null;

        $matchNotes = [];
        if ($oldNo !== '' && !$oldInv) {
            $matchNotes[] = "Invoice lama $oldNo tidak ditemukan di database";
            $warnings[] = "Invoice lama $oldNo tidak ditemukan — revisi disimpan berstatus \"Menunggu Pencocokan\".";
        }
        if ($newNo !== '' && !$newInv) {
            $matchNotes[] = "Invoice pengganti $newNo tidak ditemukan di database";
            $warnings[] = "Invoice pengganti $newNo tidak ditemukan di database.";
        }

        $cls = $this->resolver->revisionType($reason, $newNo !== '');
        $status = ($oldInv && ($newNo === '' || $newInv)) ? 'pending' : 'menunggu_pencocokan';

        if ($dryRun) {
            return ['normalized' => ['rf_no' => $rfNo, 'old' => $oldNo, 'new' => $newNo,
                                     'jenis' => $cls['name'], 'tindakan' => $cls['action'], 'status' => $status]];
        }

        $revId = $db->insert('invoice_revisions', [
            'rf_no' => $rfNo,
            'rf_date' => $rfDate,
            'rf_date_raw' => $rfDateRaw !== null ? mb_substr((string)$rfDateRaw, 0, 60) : null,
            'old_invoice_id' => $oldInv['id'] ?? null,
            'old_invoice_no' => $oldNo ?: null,
            'old_invoice_date' => Dates::parse($rec['old_invoice_date'] ?? null) ?: ($oldInv['invoice_date'] ?? null),
            'new_invoice_id' => $newInv['id'] ?? null,
            'new_invoice_no' => $newNo ?: null,
            'new_invoice_date' => $newInv['invoice_date'] ?? null,
            'original_amount' => $amount ?: (float)($oldInv['gross_amount'] ?? 0),
            'revised_amount' => (float)($newInv['gross_amount'] ?? 0),
            'revision_type_id' => $cls['revision_type_id'],
            'old_invoice_action' => $cls['action'],
            'reason' => $reason ?: null,
            'status' => $status,
            'match_notes' => $matchNotes ? mb_substr(implode('; ', $matchNotes), 0, 400) : null,
            'requested_by' => Auth::check() ? Auth::id() : null,
            'source' => 'import',
            'import_batch_id' => $batchId,
        ]);

        return ['tersimpan' => true, 'revision_id' => $revId,
                'normalized' => ['rf_no' => $rfNo, 'status' => $status, 'tindakan' => $cls['action']]];
    }

    /** Membersihkan nomor invoice yang terduplikasi dalam satu sel. */
    private function cleanInvoiceNo(string $raw, array &$warnings, string $label): string
    {
        $v = MasterResolver::key($raw);
        if ($v === '') return '';
        if (preg_match_all('#[A-Z]{1,4}/\d{2}/\d{1,6}#', $v, $m) && count($m[0]) > 1) {
            $uniq = array_values(array_unique($m[0]));
            $warnings[] = "Kolom nomor invoice $label memuat lebih dari satu nomor (\"$v\") — dipakai \"{$uniq[0]}\".";
            return $uniq[0];
        }
        return $v;
    }

    // ==================================================================
    // BATCH
    // ==================================================================

    public static function createBatch(array $data): int
    {
        $db = Database::i();
        $no = 'IMP/' . date('ymd') . '/' . strtoupper(bin2hex(random_bytes(3)));
        return $db->insert('import_batches', array_merge([
            'batch_no' => $no,
            'uploaded_by' => Auth::check() ? Auth::id() : null,
            'status' => 'draft',
        ], $data));
    }

    public static function finishBatch(int $batchId, array $result): void
    {
        $status = $result['gagal'] > 0
            ? ($result['sukses'] > 0 ? 'completed_with_errors' : 'failed')
            : 'completed';
        Database::i()->update('import_batches', [
            'total_rows' => $result['total'],
            'success_rows' => $result['sukses'],
            'failed_rows' => $result['gagal'],
            'duplicate_rows' => $result['duplikat'],
            'warning_rows' => $result['peringatan'],
            'skipped_rows' => $result['dilewati'],
            'status' => $status,
            'error_summary' => json_encode([
                'ringkasan_error' => $result['ringkasan_error'],
                'master_baru' => $result['master_baru'],
            ], JSON_UNESCAPED_UNICODE),
            'finished_at' => Dates::now(),
        ], 'id = :pk', ['pk' => $batchId]);
    }

    /** Membatalkan seluruh dampak sebuah batch import. */
    public static function rollback(int $batchId): array
    {
        $db = Database::i();
        return $db->transaction(function () use ($db, $batchId) {
            $invIds = array_map('intval', $db->column('SELECT id FROM invoices WHERE import_batch_id = ? AND deleted_at IS NULL', [$batchId]));
            $payIds = array_map('intval', $db->column('SELECT id FROM payments WHERE import_batch_id = ? AND deleted_at IS NULL', [$batchId]));
            $revIds = array_map('intval', $db->column('SELECT id FROM invoice_revisions WHERE import_batch_id = ? AND deleted_at IS NULL', [$batchId]));

            foreach ($payIds as $pid) {
                $db->exec('UPDATE payment_allocations SET deleted_at = NOW() WHERE payment_id = ?', [$pid]);
                $db->exec('UPDATE payments SET deleted_at = NOW(), status = "reversed" WHERE id = ?', [$pid]);
            }
            foreach ($revIds as $rid) $db->exec('UPDATE invoice_revisions SET deleted_at = NOW() WHERE id = ?', [$rid]);
            foreach ($invIds as $iid) {
                $db->exec('UPDATE invoice_deductions SET deleted_at = NOW() WHERE invoice_id = ?', [$iid]);
                $db->exec('UPDATE invoices SET deleted_at = NOW() WHERE id = ?', [$iid]);
            }
            $db->update('import_batches', [
                'status' => 'rolled_back',
                'rolled_back_at' => Dates::now(),
                'rolled_back_by' => Auth::check() ? Auth::id() : null,
            ], 'id = :pk', ['pk' => $batchId]);

            return ['invoice' => count($invIds), 'pembayaran' => count($payIds), 'revisi' => count($revIds)];
        });
    }
}
