<?php
/**
 * SIPIUTANG — Alat baris perintah untuk mengimpor workbook Excel AR.
 *
 * Penggunaan:
 *   php database/import_excel.php <berkas.xlsx> [--sheet=NAMA] [--dry-run] [--quiet]
 *
 * Tanpa --sheet, seluruh worksheet diproses; sheet yang memuat kolom "NO RF"
 * otomatis diperlakukan sebagai register revisi invoice (RF).
 *
 * Skrip ini dipakai untuk membuat database/03_seed_data.sql dan dapat dipakai
 * kembali oleh administrator untuk impor massal di server (lebih cepat dan
 * tidak dibatasi max_execution_time seperti impor lewat peramban).
 */

if (PHP_SAPI !== 'cli') { fwrite(STDERR, "Skrip ini hanya dapat dijalankan dari baris perintah.\n"); exit(1); }

$root = dirname(__DIR__);
require $root . '/app/bootstrap.php';

use App\Core\Database;
use App\Core\Dates;
use App\Core\Num;
use App\Core\Settings;
use App\Services\ArCalculator;
use App\Services\ImportService;
use App\Services\RevisionService;

$args = array_slice($argv, 1);
$file = null; $onlySheet = null; $dryRun = false; $quiet = false;
foreach ($args as $a) {
    if (str_starts_with($a, '--sheet=')) $onlySheet = substr($a, 8);
    elseif ($a === '--dry-run') $dryRun = true;
    elseif ($a === '--quiet') $quiet = true;
    elseif (!str_starts_with($a, '--')) $file = $a;
}
if (!$file || !is_file($file)) {
    fwrite(STDERR, "Penggunaan: php database/import_excel.php <berkas.xlsx> [--sheet=NAMA] [--dry-run]\n");
    exit(1);
}

function say(string $s = ''): void { global $quiet; if (!$quiet) echo $s . "\n"; }

$t0 = microtime(true);
$db = Database::i();

say(str_repeat('=', 78));
say('IMPORT WORKBOOK: ' . basename($file));
say(str_repeat('=', 78));

$probe = new ImportService($file);
$sheets = $probe->sheets();
say('Worksheet ditemukan: ' . count($sheets));

$totalAll = ['total' => 0, 'sukses' => 0, 'gagal' => 0, 'duplikat' => 0, 'peringatan' => 0, 'dilewati' => 0];
$errorSummary = [];
$masterBaru = [];

foreach ($sheets as $sh) {
    $name = $sh['name'];
    if ($onlySheet !== null && strcasecmp($name, $onlySheet) !== 0) continue;
    if (($sh['state'] ?? 'visible') !== 'visible') { say("  - $name : dilewati (tersembunyi)"); continue; }

    $svc = new ImportService($file, ['source_sheet' => $name]);
    $type = $svc->guessType($name);
    $svc = new ImportService($file, ['source_sheet' => $name, 'import_type' => $type]);

    $g = $svc->guessMapping($name);
    if (!$g['mapping']) { say("  - $name : dilewati (tidak ada kolom yang dikenali)"); continue; }

    say('');
    say(sprintf('SHEET "%s"  [jenis: %s]', $name, $type === 'revision' ? 'Revisi Invoice (RF)' : 'Invoice / Piutang'));
    say('  Kolom terpetakan  : ' . count($g['mapping']));
    if ($g['diabaikan']) say('  Kolom rumus diabaikan : ' . implode(', ', array_values($g['diabaikan'])));
    if ($g['tidak_terpetakan']) say('  Kolom tidak dikenali  : ' . implode(', ', array_values($g['tidak_terpetakan'])));

    $batchId = null;
    if (!$dryRun) {
        $batchId = ImportService::createBatch([
            'file_name' => basename($file),
            'file_size' => filesize($file) ?: 0,
            'sheet_name' => $name,
            'import_type' => $type,
            'mapping_json' => json_encode($g['mapping'], JSON_UNESCAPED_UNICODE),
            'status' => 'processing',
        ]);
    }

    $db->begin();
    try {
        $res = $svc->run($name, $g['mapping'], $dryRun, $batchId, function ($n, $tot) use ($quiet) {
            if (!$quiet) { echo "\r    memproses $n baris..."; }
        });
        $db->commit();
    } catch (\Throwable $e) {
        $db->rollback();
        say("\n  GAGAL: " . $e->getMessage());
        if ($batchId) $db->update('import_batches', ['status' => 'failed', 'error_summary' => $e->getMessage()], 'id = :pk', ['pk' => $batchId]);
        continue;
    }
    if (!$quiet) echo "\r" . str_repeat(' ', 40) . "\r";

    if (!$dryRun && $batchId) ImportService::finishBatch($batchId, $res);

    say(sprintf('  Hasil: %d baris | sukses %d | gagal %d | duplikat %d | peringatan %d | dilewati %d',
        $res['total'], $res['sukses'], $res['gagal'], $res['duplikat'], $res['peringatan'], $res['dilewati']));

    foreach (['total', 'sukses', 'gagal', 'duplikat', 'peringatan', 'dilewati'] as $k) $totalAll[$k] += $res[$k];
    foreach ($res['ringkasan_error'] as $k => $v) $errorSummary[$k] = ($errorSummary[$k] ?? 0) + $v;
    foreach ($res['master_baru'] as $k => $v) $masterBaru[$k] = ($masterBaru[$k] ?? 0) + $v['jumlah'];

    if ($res['gagal'] > 0) {
        say('  Contoh kegagalan:');
        $n = 0;
        foreach ($res['baris'] as $b) {
            if ($b['status'] !== 'failed') continue;
            say('    baris ' . $b['baris'] . ' (' . ($b['invoice_no'] ?? '-') . '): ' . implode(' ', $b['errors']));
            if (++$n >= 5) break;
        }
    }
}

say('');
say(str_repeat('-', 78));
say(sprintf('TOTAL: %d baris | sukses %d | gagal %d | duplikat %d | peringatan %d | dilewati %d',
    $totalAll['total'], $totalAll['sukses'], $totalAll['gagal'], $totalAll['duplikat'], $totalAll['peringatan'], $totalAll['dilewati']));

if ($masterBaru) {
    say('');
    say('Master data dibuat otomatis:');
    foreach ($masterBaru as $k => $v) say(sprintf('  %-22s %d', $k, $v));
}
if ($errorSummary) {
    say('');
    say('Ringkasan kesalahan:');
    arsort($errorSummary);
    foreach (array_slice($errorSummary, 0, 15, true) as $k => $v) say(sprintf('  (%3d x) %s', $v, mb_substr($k, 0, 110)));
}

if (!$dryRun) {
    say('');
    say('Menerapkan revisi invoice (RF) yang dapat dicocokkan...');
    $applied = RevisionService::autoApplyMatched();
    say(sprintf('  Revisi diterapkan: %d | tertunda (belum cocok): %d | dobel-hitung dicegah: %s',
        $applied['diterapkan'], $applied['tertunda'], Num::rupiah($applied['revenue_dicegah'])));

    say('');
    say('Menyegarkan status & umur piutang pada tanggal hari ini...');
    $n = ArCalculator::refreshAgingOnly();
    say("  Selesai (" . Num::fmt($db->value("SELECT COUNT(*) FROM invoices WHERE deleted_at IS NULL"), 0) . " invoice diperiksa).");

    // Menugaskan petugas penagihan bawaan bila belum ada (agar worklist tidak kosong)
    say('');
    say('Ringkasan akhir basis data:');
    $sum = $db->one('SELECT COUNT(*) AS n, COALESCE(SUM(gross_amount),0) bruto, COALESCE(SUM(net_amount),0) bersih,
                            COALESCE(SUM(paid_amount),0) bayar, COALESCE(SUM(outstanding_amount),0) saldo,
                            COALESCE(SUM(revenue_amount),0) revenue
                       FROM invoices WHERE deleted_at IS NULL');
    say(sprintf('  Invoice        : %s', Num::fmt($sum['n'], 0)));
    say(sprintf('  Nominal bruto  : %s', Num::rupiah($sum['bruto'])));
    say(sprintf('  Nilai bersih   : %s', Num::rupiah($sum['bersih'])));
    say(sprintf('  Pembayaran     : %s', Num::rupiah($sum['bayar'])));
    say(sprintf('  Saldo piutang  : %s', Num::rupiah($sum['saldo'])));
    say(sprintf('  Revenue diakui : %s', Num::rupiah($sum['revenue'])));
    foreach ($db->all('SELECT payment_status_code, COUNT(*) n, COALESCE(SUM(outstanding_amount),0) s
                         FROM invoices WHERE deleted_at IS NULL GROUP BY payment_status_code ORDER BY n DESC') as $r) {
        say(sprintf('    %-22s %5d  %s', $r['payment_status_code'], $r['n'], Num::rupiah($r['s'])));
    }
    foreach ([['customers', 'Customer'], ['outlets', 'Outlet'], ['salespersons', 'Sales'],
              ['job_categories', 'Kategori pekerjaan'], ['job_types', 'Jenis pekerjaan'],
              ['payments', 'Transaksi pembayaran'], ['invoice_revisions', 'Revisi invoice (RF)'],
              ['invoice_deductions', 'Rincian potongan'], ['value_aliases', 'Pemetaan nilai']] as [$t, $lbl]) {
        say(sprintf('  %-22s %s', $lbl, Num::fmt($db->value("SELECT COUNT(*) FROM `$t`"), 0)));
    }
}

say('');
say('Selesai dalam ' . round(microtime(true) - $t0, 1) . ' detik.');
