<?php
/**
 * Pengujian skenario bisnis end-to-end (19 skenario wajib + verifikasi integritas).
 * Seluruh perubahan dijalankan di dalam transaksi dan di-rollback pada akhir proses,
 * sehingga data hasil seeding tetap utuh.
 *
 *   php tests/test_scenarios.php
 */
require __DIR__ . '/_boot.php';

use App\Core\Database;
use App\Core\Dates;
use App\Core\Num;
use App\Core\Settings;
use App\Services\AcpService;
use App\Services\AgingService;
use App\Services\ArCalculator;
use App\Services\CollectionService;
use App\Services\DataQualityService;
use App\Services\ExportService;
use App\Services\ImportService;
use App\Services\PaymentService;
use App\Services\ReportBuilder;
use App\Services\RevisionService;

$db = Database::i();
$db->begin();
register_shutdown_function(function () use ($db) {
    try { while ($db->pdo()->inTransaction()) $db->rollback(); } catch (\Throwable $e) {}
    echo "\n\033[2mSeluruh perubahan pengujian telah dibatalkan (rollback).\033[0m\n";
});

// Autentikasi sebagai superadmin agar created_by / audit trail terisi.
$_SESSION['user_id'] = 1;
$_SESSION['user'] = $db->one('SELECT u.*, r.code role_code, r.data_scope
                                FROM users u JOIN roles r ON r.id = u.role_id WHERE u.id = 1');
\App\Core\Auth::forget();

$hariIni = Dates::today();
$seqInv = 0;

/** Membuat invoice uji baru (nilai turunan dihitung ulang oleh mesin AR). */
function mkInvoice(array $over = []): int
{
    global $seqInv;
    $db = Database::i();
    $seqInv++;
    $base = [
        'invoice_no'    => 'UJI/26/' . str_pad((string)$seqInv, 4, '0', STR_PAD_LEFT),
        'invoice_date'  => '2026-03-02',
        'sent_date'     => '2026-03-05',
        'customer_id'   => 1,
        'area_id'       => 1,
        'business_unit_id' => 1,
        'gross_amount'  => 11100000.0,
        'pph23_rate'    => 0,
        'doc_status'    => 'active',
        'is_revenue_counted' => 1,
        'source'        => 'manual',
        'created_by'    => 1,
    ];
    $data = array_merge($base, $over);
    // Tanggal jatuh tempo dihitung dengan aturan R-05 (dasar TGL KIRIM, fallback TGL INVOICE).
    $due = ArCalculator::computeDueDate($data);
    $data['due_date'] = $due['due_date'];
    $data['due_date_basis'] = $due['basis'] ?? 'sent_date';
    $data['payment_term_days'] = $due['term_days'] ?? null;
    $id = $db->insert('invoices', $data);
    ArCalculator::recalculate($id, null, true);
    return $id;
}

function inv(int $id): array { return Database::i()->one('SELECT * FROM invoices WHERE id = :id', ['id' => $id]) ?: []; }
function money($v): string { return Num::rupiah((float)$v); }

// =====================================================================
section('SKENARIO 1-3 — Pembayaran penuh, sebagian, dan bertahap');
// =====================================================================

$id1 = mkInvoice(['invoice_no' => 'UJI/26/LUNAS']);
$i1 = inv($id1);
$net1 = (float)$i1['net_amount'];
eq(11100000.0, (float)$i1['gross_amount'], 'S1 bruto invoice');
eq(10000000.0, round((float)$i1['dpp_amount'], 2), 'S1 DPP = bruto / 1,11');
eq(1100000.0, round((float)$i1['vat_amount'], 2), 'S1 PPN 11%');
eq(11100000.0, round($net1, 2), 'S1 nilai bersih tanpa potongan = bruto');
ok(in_array($i1['payment_status_code'], ['BELUM_JATUH_TEMPO', 'JATUH_TEMPO', 'TERLAMBAT'], true),
   'S1 status awal belum terbayar (' . $i1['payment_status_code'] . ')');

PaymentService::create(
    ['payment_date' => '2026-04-01', 'customer_id' => 1, 'method' => 'TRANSFER', 'reference_no' => 'UJI-TRF-1'],
    [['invoice_id' => $id1, 'amount' => $net1]]
);
$i1 = inv($id1);
eq(0.0, round((float)$i1['outstanding_amount'], 2), 'S1 saldo nol setelah pelunasan');
eq('LUNAS', $i1['payment_status_code'], 'S1 status menjadi LUNAS');
eq('2026-04-01', $i1['last_payment_date'], 'S1 tanggal pembayaran terakhir tercatat');
ok((int)$i1['collection_days'] > 0, 'S1 hari penagihan terhitung (' . $i1['collection_days'] . ' hari)');
eq(1, (int)$i1['payment_count'], 'S1 jumlah pembayaran');

$id2 = mkInvoice(['invoice_no' => 'UJI/26/SEBAGIAN']);
$net2 = (float)inv($id2)['net_amount'];
PaymentService::create(
    ['payment_date' => '2026-04-05', 'customer_id' => 1, 'method' => 'TRANSFER'],
    [['invoice_id' => $id2, 'amount' => 4000000.0]]
);
$i2 = inv($id2);
eq('PEMBAYARAN_SEBAGIAN', $i2['payment_status_code'], 'S2 status pembayaran sebagian');
eq(round($net2 - 4000000.0, 2), round((float)$i2['outstanding_amount'], 2), 'S2 sisa saldo');
eq(4000000.0, round((float)$i2['paid_amount'], 2), 'S2 nilai terbayar');

$id3 = mkInvoice(['invoice_no' => 'UJI/26/BERTAHAP']);
$net3 = (float)inv($id3)['net_amount'];
foreach ([['2026-04-02', 3000000.0], ['2026-04-10', 3000000.0], ['2026-04-20', $net3 - 6000000.0]] as [$tgl, $amt]) {
    PaymentService::create(['payment_date' => $tgl, 'customer_id' => 1, 'method' => 'TRANSFER'],
                           [['invoice_id' => $id3, 'amount' => $amt]]);
}
$i3 = inv($id3);
eq('LUNAS', $i3['payment_status_code'], 'S3 lunas setelah 3 kali pembayaran');
eq(0.0, round((float)$i3['outstanding_amount'], 2), 'S3 saldo nol');
eq(3, (int)$db->value('SELECT COUNT(*) FROM payment_allocations WHERE invoice_id = ?', [$id3]), 'S3 tiga alokasi tercatat');
eq(3, (int)$i3['payment_count'], 'S3 jumlah pembayaran');
eq('2026-04-02', $i3['first_payment_date'], 'S3 tanggal pembayaran pertama');
eq('2026-04-20', $i3['last_payment_date'], 'S3 tanggal pembayaran terakhir');
eq(round($net3, 2), round((float)$db->value('SELECT SUM(amount) FROM payment_allocations WHERE invoice_id = ?', [$id3]), 2),
   'S3 jumlah alokasi = nilai bersih invoice');

// =====================================================================
section('SKENARIO 4-6 — Belum jatuh tempo, terlambat, dan dibatalkan');
// =====================================================================

$id4 = mkInvoice(['invoice_no' => 'UJI/26/BELUMJT',
                  'invoice_date' => Dates::addDays($hariIni, -5), 'sent_date' => Dates::addDays($hariIni, -5)]);
$i4 = inv($id4);
ok($i4['due_date'] > $hariIni, 'S4 jatuh tempo di masa depan (' . Dates::fmt($i4['due_date']) . ')');
eq('BELUM_JATUH_TEMPO', $i4['payment_status_code'], 'S4 status BELUM_JATUH_TEMPO');
eq(0, (int)$i4['days_late'], 'S4 belum ada keterlambatan');
ok((int)$i4['aging_days'] <= 0, 'S4 aging_days <= 0 (' . $i4['aging_days'] . ')');

$id5 = mkInvoice(['invoice_no' => 'UJI/26/TERLAMBAT',
                  'invoice_date' => Dates::addDays($hariIni, -120), 'sent_date' => Dates::addDays($hariIni, -118)]);
$i5 = inv($id5);
ok($i5['due_date'] < $hariIni, 'S5 sudah melewati jatuh tempo (' . Dates::fmt($i5['due_date']) . ')');
ok(in_array($i5['payment_status_code'], ['JATUH_TEMPO', 'TERLAMBAT'], true),
   'S5 status ' . $i5['payment_status_code']);
ok((int)$i5['aging_days'] > 0, 'S5 umur piutang > 0 (' . $i5['aging_days'] . ' hari)');
eq(Dates::diffDays($i5['due_date'], $hariIni), (int)$i5['aging_days'],
   'S5 umur piutang = selisih hari sejak jatuh tempo (R-07)');
ok($i5['days_late'] === null, 'S5 hari keterlambatan bayar masih kosong karena belum ada pembayaran');

// Invoice terlambat yang akhirnya dibayar -> days_late terisi
$id5b = mkInvoice(['invoice_no' => 'UJI/26/BAYARTELAT',
                   'invoice_date' => Dates::addDays($hariIni, -120), 'sent_date' => Dates::addDays($hariIni, -118)]);
$i5b = inv($id5b);
$tglBayarTelat = Dates::addDays($i5b['due_date'], 17);
PaymentService::create(['payment_date' => $tglBayarTelat, 'customer_id' => 1, 'method' => 'TRANSFER'],
                       [['invoice_id' => $id5b, 'amount' => (float)$i5b['net_amount']]]);
$i5b = inv($id5b);
eq(17, (int)$i5b['days_late'], 'S5 hari keterlambatan pembayaran = tgl bayar - jatuh tempo');
ok($i5b['aging_days'] === null, 'S5 umur piutang dikosongkan setelah invoice lunas');
eq('LUNAS', $i5b['payment_status_code'], 'S5 status invoice terlambat setelah dibayar');

$id6 = mkInvoice(['invoice_no' => 'UJI/26/BATAL']);
$db->exec("UPDATE invoices SET doc_status='cancelled', is_revenue_counted=0 WHERE id = ?", [$id6]);
ArCalculator::recalculate($id6, null, false);
$i6 = inv($id6);
eq('cancelled', $i6['doc_status'], 'S6 dokumen berstatus dibatalkan');
eq(0.0, round((float)$i6['outstanding_amount'], 2), 'S6 saldo dinolkan');
eq(0, (int)$i6['is_revenue_counted'], 'S6 tidak dihitung sebagai pendapatan');
eq('DIBATALKAN', $i6['payment_status_code'], 'S6 status pembayaran DIBATALKAN');
eq(0.0, round((float)$i6['revenue_amount'], 2), 'S6 nilai pendapatan nol');
eq(0, (int)$db->value("SELECT COUNT(*) FROM invoices WHERE id = ? AND doc_status='active'", [$id6]),
   'S6 tidak lagi masuk populasi piutang aktif');
$saldoTanpaBatal = (float)$db->value("SELECT COALESCE(SUM(outstanding_amount),0) FROM invoices
                                       WHERE deleted_at IS NULL AND doc_status='active' AND id = ?", [$id6]);
eq(0.0, round($saldoTanpaBatal, 2), 'S6 tidak berkontribusi pada saldo piutang');

// =====================================================================
section('SKENARIO 7-8 — Invoice revisi & invoice pengganti (anti hitung ganda)');
// =====================================================================

$idLama = mkInvoice(['invoice_no' => 'UJI/26/RF-LAMA', 'gross_amount' => 22200000.0]);
$idBaru = mkInvoice(['invoice_no' => 'UJI/26/RF-BARU', 'gross_amount' => 19980000.0]);
$brutoLama = (float)inv($idLama)['gross_amount'];
$brutoBaru = (float)inv($idBaru)['gross_amount'];

$revId = $db->insert('invoice_revisions', [
    'rf_no' => 'UJI-RF-001', 'rf_date' => '2026-04-15',
    'old_invoice_id' => $idLama, 'new_invoice_id' => $idBaru,
    'old_invoice_no' => 'UJI/26/RF-LAMA', 'new_invoice_no' => 'UJI/26/RF-BARU',
    'old_invoice_date' => '2026-03-02', 'new_invoice_date' => '2026-03-02',
    'revision_type_id' => (int)$db->value('SELECT id FROM revision_types ORDER BY id LIMIT 1'),
    'original_amount' => $brutoLama, 'revised_amount' => $brutoBaru,
    'old_invoice_action' => 'replaced',
    'reason' => 'Uji revisi otomatis', 'status' => 'approved', 'source' => 'manual', 'requested_by' => 1,
]);
$res = RevisionService::apply($revId);
ok(!empty($res['sukses']), 'S7 revisi diterapkan: ' . ($res['pesan'] ?? ''));

$iLama = inv($idLama); $iBaru = inv($idBaru);
eq('replaced', $iLama['doc_status'], 'S7 invoice lama berstatus digantikan');
eq(0, (int)$iLama['is_revenue_counted'], 'S7 invoice lama tidak dihitung sebagai pendapatan');
eq(0.0, round((float)$iLama['outstanding_amount'], 2), 'S7 saldo invoice lama dinolkan');
eq($idBaru, (int)$iLama['replaced_by_invoice_id'], 'S7 tautan invoice lama -> pengganti tersimpan');
eq($idLama, (int)$iBaru['replaces_invoice_id'], 'S8 tautan invoice pengganti -> lama tersimpan');
eq(1, (int)$iBaru['is_revenue_counted'], 'S8 hanya invoice pengganti yang dihitung');
ok((float)$iBaru['outstanding_amount'] > 0, 'S8 invoice pengganti memiliki saldo (' . money($iBaru['outstanding_amount']) . ')');

$revenuePasangan = (float)$db->value(
    'SELECT COALESCE(SUM(revenue_amount),0) FROM invoices WHERE id IN (?,?) AND is_revenue_counted = 1', [$idLama, $idBaru]);
eq(round($brutoBaru / 1.11, 2), round($revenuePasangan, 2), 'S7/S8 pendapatan tidak terhitung ganda');
eq(round($brutoLama / 1.11, 2), round($brutoLama / 1.11, 2), 'S7 pendapatan yang dicegah = ' . money($brutoLama / 1.11));

RevisionService::unapply($revId);
$iLama = inv($idLama);
eq('active', $iLama['doc_status'], 'S7 pembatalan penerapan mengembalikan invoice lama ke aktif');
ok((float)$iLama['outstanding_amount'] > 0, 'S7 saldo invoice lama pulih setelah dibatalkan');
RevisionService::apply($revId, true);

// =====================================================================
section('SKENARIO 9-10 — Potongan PPh 23 dan potongan lainnya');
// =====================================================================

$tarif = Settings::float('pajak.tarif_pph23', 2);
$tipePph = (int)$db->value("SELECT id FROM deduction_types WHERE code='PPH23'");
$id9 = mkInvoice(['invoice_no' => 'UJI/26/PPH23', 'pph23_rate' => $tarif]);
// Potongan disimpan di tabel rincian (sumber kebenaran), bukan sekadar kolom tarif.
$db->insert('invoice_deductions', ['invoice_id' => $id9, 'deduction_type_id' => $tipePph, 'rate' => $tarif,
    'amount' => ArCalculator::pph23(11100000.0, $tarif), 'notes' => 'Uji PPh 23', 'created_by' => 1]);
ArCalculator::recalculate($id9, null, false);
$i9 = inv($id9);
$dpp9 = (float)$i9['dpp_amount'];
ok((float)$i9['pph23_amount'] > 0, 'S9 PPh 23 terhitung (' . money($i9['pph23_amount']) . ' @ ' . $tarif . '%)');
eq(round($dpp9 * $tarif / 100, 2), round((float)$i9['pph23_amount'], 2), 'S9 PPh 23 = tarif x DPP (bukan x bruto)');
eq(round((float)$i9['gross_amount'] * $tarif / 100, 2) !== round((float)$i9['pph23_amount'], 2) ? 1 : 0, 1,
   'S9 PPh 23 tidak dihitung dari bruto (kesalahan umum di Excel)');
eq($tarif, ArCalculator::effectivePphRate((float)$i9['gross_amount'], (float)$i9['pph23_amount']),
   'S9 tarif efektif hasil rekonstruksi = tarif master');
eq(round((float)$i9['gross_amount'] - (float)$i9['pph23_amount'], 2), round((float)$i9['net_amount'], 2),
   'S9 nilai bersih = bruto - PPh 23');
eq(round((float)$i9['net_amount'], 2), round((float)$i9['outstanding_amount'], 2), 'S9 saldo awal = nilai bersih');

PaymentService::create(['payment_date' => '2026-04-08', 'customer_id' => 1, 'method' => 'TRANSFER'],
                       [['invoice_id' => $id9, 'amount' => (float)$i9['net_amount']]]);
eq('LUNAS', inv($id9)['payment_status_code'], 'S9 lunas dengan pembayaran senilai nilai bersih');

$id10 = mkInvoice(['invoice_no' => 'UJI/26/POTONGAN']);
$jenisPot = (int)$db->value("SELECT id FROM deduction_types WHERE code = 'LAIN'");
$db->insert('invoice_deductions', ['invoice_id' => $id10, 'deduction_type_id' => $jenisPot,
                                   'amount' => 250000.0, 'notes' => 'Uji potongan materai & admin', 'created_by' => 1]);
ArCalculator::recalculate($id10, null, false);
$i10 = inv($id10);
eq(250000.0, round((float)$i10['other_deduction_amount'], 2), 'S10 potongan lain tercatat');
eq(250000.0, round((float)$i10['total_deduction'], 2), 'S10 total potongan');
eq(round((float)$i10['gross_amount'] - 250000.0, 2), round((float)$i10['net_amount'], 2), 'S10 nilai bersih dikurangi potongan');
eq(round((float)$i10['net_amount'], 2), round((float)$i10['outstanding_amount'], 2), 'S10 saldo mengikuti nilai bersih');

// Gabungan PPh 23 + potongan lain
$id10b = mkInvoice(['invoice_no' => 'UJI/26/POT-GABUNG', 'pph23_rate' => $tarif]);
$db->insert('invoice_deductions', ['invoice_id' => $id10b, 'deduction_type_id' => $jenisPot,
                                   'amount' => 100000.0, 'notes' => 'Uji gabungan', 'created_by' => 1]);
ArCalculator::recalculate($id10b, null, false);
$i10b = inv($id10b);
eq(round((float)$i10b['pph23_amount'] + 100000.0, 2), round((float)$i10b['total_deduction'], 2),
   'S10 total potongan = PPh 23 + potongan lain');
eq(round((float)$i10b['gross_amount'] - (float)$i10b['total_deduction'], 2), round((float)$i10b['net_amount'], 2),
   'S10 nilai bersih memperhitungkan seluruh potongan');

// =====================================================================
section('SKENARIO 11-13 — Tanggal Excel tidak valid, duplikat, formula rusak');
// =====================================================================

$kasusTanggal = [
    ['', null], [' ', null], ['#REF!', null], ['#VALUE!', null], ['2025', null], ['31-02-2026', null],
    ['45658', '2025-01-01'], ['31-05-2026', '2026-05-31'], ['/31-05-26', '2026-05-31'],
    ['2026-05-31', '2026-05-31'], ['31 Mei 2026', '2026-05-31'], ['31/05/2026', '2026-05-31'],
];
$lolos = 0;
foreach ($kasusTanggal as [$in, $harap]) {
    $out = Dates::parse($in);
    if ($out === $harap) $lolos++;
    else bad("S11 parse tanggal '$in' -> " . var_export($out, true) . ' (diharapkan ' . var_export($harap, true) . ')');
}
eq(count($kasusTanggal), $lolos, 'S11 kasus tanggal Excel ditangani benar');

// Import berkas uji berisi baris bermasalah
$ujiXlsx = sys_get_temp_dir() . '/uji_import_skenario.xlsx';
$w = new \App\Services\ExcelWriter();
$kolomImport = [
    ['label' => 'AREA', 'key' => 'area', 'type' => 'text'],
    ['label' => 'NO. INVOICE', 'key' => 'no', 'type' => 'text'],
    ['label' => 'TGL INVOICE', 'key' => 'tgl', 'type' => 'text'],
    ['label' => 'TGL KIRIM', 'key' => 'kirim', 'type' => 'text'],
    ['label' => 'NAMA CUSTOMER', 'key' => 'cust', 'type' => 'text'],
    ['label' => 'NOMINAL', 'key' => 'nominal', 'type' => 'text'],
    ['label' => 'UMUR PIUTANG', 'key' => 'umur', 'type' => 'text'],
];
$barisImport = [
    ['area' => 'JKT', 'no' => 'UJI/IMP/001', 'tgl' => '#REF!',      'kirim' => '10-06-2026',
     'cust' => 'PT UJI TANGGAL RUSAK', 'nominal' => '5.550.000', 'umur' => '#VALUE!'],
    ['area' => 'JKT', 'no' => 'UJI/IMP/001', 'tgl' => '10-06-2026', 'kirim' => '10-06-2026',
     'cust' => 'PT UJI TANGGAL RUSAK', 'nominal' => '5.550.000', 'umur' => '30'],
    ['area' => 'JKT', 'no' => 'UJI/IMP/002', 'tgl' => '',           'kirim' => '',
     'cust' => 'PT UJI TANPA TANGGAL', 'nominal' => '1.110.000', 'umur' => ''],
    ['area' => 'BALI', 'no' => 'UJI/IMP/003', 'tgl' => '15-06-2026', 'kirim' => '16-06-2026',
     'cust' => 'PT UJI NORMAL', 'nominal' => '#DIV/0!', 'umur' => '=I5-H5'],
    ['area' => 'BDG', 'no' => 'UJI/IMP/004', 'tgl' => '20-06-2026', 'kirim' => '21-06-2026',
     'cust' => 'PT UJI NORMAL', 'nominal' => '2.220.000', 'umur' => '20'],
];
$w->addSheet('UJI IMPORT', $kolomImport, $barisImport);
$w->save($ujiXlsx);
ok(is_file($ujiXlsx), 'S11 berkas Excel uji berhasil dibuat');

$imp = new ImportService($ujiXlsx);
$guess = $imp->guessMapping('UJI IMPORT');
$mapping = $guess['mapping'];
eq(7, count($mapping) + count($guess['diabaikan']) + count($guess['tidak_terpetakan']),
   'S11 seluruh kolom header dikenali atau ditandai');
ok(($mapping['C'] ?? '') === 'invoice_date' && ($mapping['D'] ?? '') === 'sent_date',
   'S11 pemetaan kolom otomatis benar (' . count($mapping) . ' kolom terpetakan)');
ok(in_array('UMUR PIUTANG', array_values($guess['diabaikan']), true) || isset($guess['diabaikan']['G']),
   'S11 kolom hasil formula Excel (UMUR PIUTANG) sengaja diabaikan');

$batchUji = ImportService::createBatch(['file_name' => 'uji_import_skenario.xlsx', 'sheet_name' => 'UJI IMPORT',
                                        'import_type' => 'invoice', 'total_rows' => count($barisImport)]);
$hasilImp = $imp->run('UJI IMPORT', $mapping, false, $batchUji);
ImportService::finishBatch($batchUji, $hasilImp);

$berhasil = (int)$hasilImp['sukses'];
$ditolak  = (int)$hasilImp['gagal'];
$duplikat = (int)$hasilImp['duplikat'];
eq(count($barisImport), (int)$hasilImp['total'], 'S11 seluruh baris data terbaca dari berkas');
ok($berhasil >= 1, "S11 baris valid terimpor ($berhasil baris)");
ok($duplikat >= 1, "S12 duplikat nomor invoice terdeteksi dan ditolak ($duplikat baris)");
ok($ditolak >= 1, "S11 baris tanpa tanggal apa pun ditolak ($ditolak baris)");
eq(count($barisImport), $berhasil + $ditolak + $duplikat, 'S11 rekapitulasi baris seimbang (sukses+gagal+duplikat=total)');
eq(1, (int)$db->value('SELECT COUNT(*) FROM invoices WHERE invoice_no = ?', ['UJI/IMP/001']),
   'S12 hanya satu baris tersimpan untuk nomor invoice yang sama');
eq(0, (int)$db->value('SELECT COUNT(*) FROM invoices WHERE invoice_no = ?', ['UJI/IMP/002']),
   'S11 invoice tanpa tanggal tidak tersimpan');

$impRusak = $db->one('SELECT * FROM invoices WHERE invoice_no = ?', ['UJI/IMP/001']);
ok($impRusak !== null, 'S11 invoice dengan TGL INVOICE rusak tetap tersimpan memakai tanggal cadangan');
if ($impRusak) {
    eq('2026-06-10', $impRusak['invoice_date'], 'S11 tanggal cadangan diambil dari TGL KIRIM');
    ok(str_contains((string)$impRusak['dq_flags'], 'CADANGAN'), 'S11 penanda kualitas data tanggal cadangan tercatat');
}
$impNol = $db->one('SELECT * FROM invoices WHERE invoice_no = ?', ['UJI/IMP/003']);
if ($impNol) eq(0.0, round((float)$impNol['gross_amount'], 2), 'S13 nominal #DIV/0! dinormalisasi menjadi 0');

ok((int)$db->value('SELECT COUNT(*) FROM import_batches WHERE id = ?', [$batchUji]) === 1,
   'S11 batch import tercatat di riwayat');
$barisGagal = (int)$db->value("SELECT COUNT(*) FROM import_rows WHERE import_batch_id = ? AND status IN ('failed','duplicate')", [$batchUji]);
ok($barisGagal >= 2, "S11/S12 baris gagal tersimpan untuk diunduh ulang ($barisGagal baris)");
@unlink($ujiXlsx);

// --- 13. Nilai error Excel & formula rusak
$rusak = ['#REF!', '#VALUE!', '#DIV/0!', '#N/A', '#NAME?', '#NULL!', '#NUM!', '', ' ', '-'];
$semuaNol = true;
foreach ($rusak as $v) if (Num::parse($v) !== 0.0) { $semuaNol = false; bad("S13 Num::parse('$v') != 0"); }
ok($semuaNol, 'S13 seluruh ' . count($rusak) . ' nilai error Excel dinormalisasi menjadi 0');

$id13 = mkInvoice(['invoice_no' => 'UJI/26/FORMULA', 'gross_amount' => 8880000.0]);
// Simulasi nilai turunan salah warisan Excel, lalu dihitung ulang oleh sistem
$db->exec("UPDATE invoices SET dpp_amount=0, vat_amount=0, net_amount=0, outstanding_amount=0,
           aging_days=0, payment_status_code='LUNAS' WHERE id = ?", [$id13]);
ArCalculator::recalculate($id13, null, true);
$i13 = inv($id13);
eq(8000000.0, round((float)$i13['dpp_amount'], 2), 'S13 DPP dihitung ulang di sisi server');
eq(880000.0, round((float)$i13['vat_amount'], 2), 'S13 PPN dihitung ulang di sisi server');
eq(8880000.0, round((float)$i13['outstanding_amount'], 2), 'S13 saldo dipulihkan dari nilai Excel yang salah');
ok($i13['payment_status_code'] !== 'LUNAS', 'S13 status LUNAS palsu dikoreksi menjadi ' . $i13['payment_status_code']);
ok((int)$i13['aging_days'] != 0, 'S13 umur piutang dihitung ulang (' . $i13['aging_days'] . ' hari)');

// =====================================================================
section('SKENARIO 14-15 — Perhitungan ACP bulanan & tahunan');
// =====================================================================

$acpBulan = AcpService::calculate('2026-03-01', '2026-03-31', [], 'finansial');
eq(31, (int)$acpBulan['jumlah_hari'], 'S14 jumlah hari periode Maret');
ok((float)$acpBulan['acp_hari'] >= 0, 'S14 ACP Maret 2026 (metode finansial) = ' . round((float)$acpBulan['acp_hari'], 1) . ' hari');
if ((float)$acpBulan['penjualan_kredit_bersih'] > 0) {
    $manual = ((float)$acpBulan['ar_rata_rata'] / (float)$acpBulan['penjualan_kredit_bersih']) * (int)$acpBulan['jumlah_hari'];
    eq(round($manual, 2), round((float)$acpBulan['acp_hari'], 2), 'S14 rumus ACP standar terverifikasi manual');
}
eq(round(((float)$acpBulan['ar_awal'] + (float)$acpBulan['ar_akhir']) / 2, 2), round((float)$acpBulan['ar_rata_rata'], 2),
   'S14 rata-rata piutang = (AR awal + AR akhir) / 2');

$acpTahun = AcpService::calculate('2026-01-01', '2026-12-31', [], 'finansial');
eq(365, (int)$acpTahun['jumlah_hari'], 'S15 jumlah hari periode tahunan 2026');
ok((float)$acpTahun['acp_hari'] >= 0, 'S15 ACP tahunan 2026 = ' . round((float)$acpTahun['acp_hari'], 1) . ' hari');
ok((float)$acpTahun['perputaran_piutang'] > 0, 'S15 perputaran piutang = ' . $acpTahun['perputaran_piutang'] . 'x');

$acpOps = AcpService::calculate('2026-01-01', '2026-07-31', [], 'operasional');
ok((int)$acpOps['jumlah_invoice'] > 0, 'S15 metode operasional memakai ' . nf((int)$acpOps['jumlah_invoice']) . ' invoice terbayar');
ok((float)$acpOps['acp_hari'] > 0, 'S15 ACP operasional = ' . $acpOps['acp_hari'] . ' hari');
eq($acpOps['rata_sederhana'], $acpOps['acp_hari'], 'S15 ACP operasional default = rata-rata sederhana');
ok($acpOps['median'] !== null, 'S15 median hari penagihan = ' . $acpOps['median'] . ' hari');
ok($acpOps['minimum'] <= $acpOps['acp_hari'] && $acpOps['acp_hari'] <= $acpOps['maksimum'],
   'S15 rata-rata berada di antara minimum (' . $acpOps['minimum'] . ') dan maksimum (' . $acpOps['maksimum'] . ')');
eq(100.0, round((float)$acpOps['persen_tepat_waktu'] + (float)$acpOps['persen_terlambat'], 1),
   'S15 persentase tepat waktu + terlambat = 100%');
eq((int)$acpOps['jumlah_invoice'], (int)$acpOps['jumlah_tepat_waktu'] + (int)$acpOps['jumlah_terlambat'],
   'S15 jumlah tepat waktu + terlambat = total invoice');

$tren = AcpService::monthlyTrend(2026);
$bulanBerjalan = (int)date('n', strtotime($hariIni));
eq($bulanBerjalan, count($tren), 'S15 tren bulanan berhenti di bulan berjalan (tidak memproyeksikan masa depan)');
ok(count($tren) > 0 && $tren[0]['label'] !== '', 'S15 label bulan dalam bahasa Indonesia: '
   . implode(', ', array_column($tren, 'label')));
$banding = AcpService::comparisons('2026-07-31');
ok(count($banding) >= 3, 'S15 perbandingan periode tersedia (' . count($banding) . ' periode)');
$perDim = AcpService::byDimension('area', '2026-01-01', '2026-07-31');
ok(count($perDim) > 0, 'S15 ACP per area tersedia (' . count($perDim) . ' area)');

// =====================================================================
section('SKENARIO 16 — Konfigurasi aging kustom');
// =====================================================================

$cfgId = $db->insert('aging_configurations', [
    'code' => 'UJI_AGING_30', 'name' => 'Aging Uji 30 Harian', 'basis' => 'outstanding',
    'reference' => 'due_date', 'include_paid' => 0, 'is_active' => 1, 'is_default' => 0, 'created_by' => 1,
]);
foreach ([['Belum jatuh tempo', -99999, 0, 'success'], ['1-30 hari', 1, 30, 'info'],
          ['31-60 hari', 31, 60, 'warning'], ['61-90 hari', 61, 90, 'danger'],
          ['> 90 hari', 91, null, 'dark']] as $i => [$lbl, $f, $t, $c]) {
    $db->insert('aging_buckets', ['aging_configuration_id' => $cfgId, 'label' => $lbl, 'from_days' => $f,
                                  'to_days' => $t, 'color' => $c, 'sort_order' => $i + 1]);
}

$lapUji = AgingService::report([], 'none', $hariIni, $cfgId);
eq(5, count($lapUji['buckets']), 'S16 konfigurasi kustom menghasilkan 5 bucket');
eq('Belum jatuh tempo', $lapUji['buckets'][0]['label'], 'S16 label bucket pertama sesuai konfigurasi');

$lapDefault = AgingService::report([], 'none', $hariIni, null);
eq(4, count($lapDefault['buckets']), 'S16 konfigurasi default tetap 4 bucket (0-45/46-90/91-120/>120)');

$totalKustom  = (float)$lapUji['total']['total_nilai'];
$totalDefault = (float)$lapDefault['total']['total_nilai'];
eq(round($totalDefault, 2), round($totalKustom, 2), 'S16 total nilai identik pada kedua konfigurasi (' . money($totalKustom) . ')');

$jmlBucketKustom = 0.0;
foreach ($lapUji['buckets'] as $i => $b) $jmlBucketKustom += (float)$lapUji['total']["b$i"];
eq(round($totalKustom, 2), round($jmlBucketKustom, 2), 'S16 penjumlahan 5 bucket = total keseluruhan');

$jmlBucketDefault = 0.0;
foreach ($lapDefault['buckets'] as $i => $b) $jmlBucketDefault += (float)$lapDefault['total']["b$i"];
eq(round($totalDefault, 2), round($jmlBucketDefault, 2), 'S16 penjumlahan 4 bucket default = total keseluruhan');

$saldoSistem = (float)$db->value("SELECT COALESCE(SUM(outstanding_amount),0) FROM invoices
                                   WHERE deleted_at IS NULL AND doc_status='active' AND outstanding_amount > 1");
eq(round($saldoSistem, 2), round($totalKustom, 2), 'S16 total aging = saldo piutang sistem');

$lapArea = AgingService::report([], 'area', $hariIni, $cfgId);
ok(count($lapArea['rows']) > 0, 'S16 aging per area menghasilkan ' . count($lapArea['rows']) . ' baris');
$sumArea = 0.0; foreach ($lapArea['rows'] as $r) $sumArea += (float)$r['total_nilai'];
eq(round($totalKustom, 2), round($sumArea, 2), 'S16 total per area = total keseluruhan');

$drill = AgingService::invoicesInBucket(4, [], $hariIni, $cfgId, 50);
ok(is_array($drill), 'S16 drill-down bucket > 90 hari mengembalikan ' . count($drill) . ' invoice');

// =====================================================================
section('SKENARIO 17 — Pembuat laporan fleksibel');
// =====================================================================

$defLaporan = [
    'name' => 'Uji Laporan Saldo per Area',
    'description' => 'Dibuat otomatis oleh pengujian skenario',
    'data_source' => 'invoices',
    'group_by' => ['area'],
    'columns' => [
        ['field' => 'area', 'aggregate' => 'none'],
        ['field' => 'invoice_no', 'aggregate' => 'count', 'label' => 'Jumlah Invoice'],
        ['field' => 'outstanding_amount', 'aggregate' => 'sum'],
        ['field' => 'outstanding_amount', 'aggregate' => 'avg'],
        ['field' => 'aging_days', 'aggregate' => 'max'],
    ],
    'filters' => [
        ['field' => 'outstanding_amount', 'operator' => 'gt', 'value' => 0],
        ['field' => 'doc_status', 'operator' => 'eq', 'value' => 'active'],
    ],
    'chart_type' => 'bar',
    'options' => ['subtotal' => true, 'grand_total' => true],
    'visibility' => 'shared',
];
$res17 = ReportBuilder::execute($defLaporan);
ok(!empty($res17['rows']), 'S17 laporan kustom menghasilkan ' . count($res17['rows']) . ' baris');
eq(5, count($res17['columns']), 'S17 lima kolom terbentuk sesuai definisi');
eq('Jumlah Invoice', $res17['columns'][1]['label'], 'S17 label kolom kustom dipakai');
ok(!empty($res17['total']), 'S17 grand total dihitung');

$kolSum = null;
foreach ($res17['columns'] as $c) if ($c['field'] === 'outstanding_amount' && $c['aggregate'] === 'sum') $kolSum = $c;
ok($kolSum !== null, 'S17 kolom agregat SUM ditemukan');
$aliasSum = $kolSum['alias'];
$grand = 0.0;
foreach ($res17['rows'] as $r) $grand += (float)($r[$aliasSum] ?? 0);
eq(round($grand, 2), round((float)($res17['total'][$aliasSum] ?? 0), 2),
   'S17 grand total = jumlah seluruh baris (' . money($grand) . ')');
ok(!empty($res17['subtotals']), 'S17 subtotal per kelompok dihitung (' . count($res17['subtotals']) . ' kelompok)');
ok(!empty($res17['sql']), 'S17 SQL laporan dibangun dari whitelist kolom');
ok(!str_contains(strtolower($res17['sql']), 'drop ') && !str_contains($res17['sql'], '--'),
   'S17 SQL bebas dari fragmen berbahaya');

$tplId = ReportBuilder::saveTemplate($defLaporan);
ok($tplId > 0, "S17 template laporan tersimpan (id=$tplId)");
$loaded = ReportBuilder::loadTemplate($tplId);
eq('Uji Laporan Saldo per Area', $loaded['name'] ?? '', 'S17 template dapat dimuat kembali');
eq(5, count($loaded['columns'] ?? []), 'S17 kolom template tersimpan utuh');
eq(2, count($loaded['filters'] ?? []), 'S17 filter template tersimpan utuh');
eq('area', implode(',', $loaded['group_by'] ?? []), 'S17 grouping template tersimpan utuh');
eq('bar', $loaded['chart_type'] ?? '', 'S17 jenis grafik template tersimpan');

$dupId = ReportBuilder::duplicateTemplate($tplId, 'Salinan Uji Laporan');
ok($dupId > 0 && $dupId !== $tplId, 'S17 template dapat diduplikasi');
$res17b = ReportBuilder::execute($loaded);
eq(count($res17['rows']), count($res17b['rows']), 'S17 hasil dari template = hasil definisi asli');

$opsUji = [
    ['outstanding_amount', 'gt', 0], ['outstanding_amount', 'gte', 0], ['outstanding_amount', 'lt', 999999999999],
    ['outstanding_amount', 'lte', 999999999999], ['doc_status', 'eq', 'active'], ['doc_status', 'neq', 'xxx'],
    ['invoice_no', 'contains', 'FA'], ['invoice_no', 'starts', 'FA'], ['invoice_no', 'ends', '1'],
    ['doc_status', 'in', 'active,replaced'], ['doc_status', 'not_in', 'xxx'],
    ['outstanding_amount', 'between', '0,999999999'], ['last_payment_date', 'is_null', ''],
    ['invoice_date', 'is_not_null', ''], ['invoice_date', 'between', '2026-01-01,2026-12-31'],
];
$opsOk = 0;
foreach ($opsUji as [$fld, $op, $val]) {
    try {
        $r = ReportBuilder::execute(['data_source' => 'invoices',
            'columns' => [['field' => 'invoice_no', 'aggregate' => 'count']],
            'filters' => [['field' => $fld, 'operator' => $op, 'value' => $val]]]);
        if (isset($r['rows'])) $opsOk++; else bad("S17 operator $op tidak menghasilkan baris");
    } catch (\Throwable $e) { bad("S17 operator $op gagal: " . $e->getMessage()); }
}
eq(count($opsUji), $opsOk, 'S17 seluruh operator filter berfungsi');

$srcOk = 0; $srcAll = array_keys(ReportBuilder::dataSources());
foreach ($srcAll as $src) {
    try {
        $f = ReportBuilder::fields($src);
        $k = array_key_first($f);
        $r = ReportBuilder::execute(['data_source' => $src, 'columns' => [['field' => $k, 'aggregate' => 'none']]], 5);
        if (isset($r['rows'])) $srcOk++; else bad("S17 sumber data $src tidak menghasilkan baris");
    } catch (\Throwable $e) { bad("S17 sumber data $src gagal: " . $e->getMessage()); }
}
eq(count($srcAll), $srcOk, 'S17 seluruh ' . count($srcAll) . ' sumber data dapat dieksekusi');

$aggOk = 0; $aggAll = array_keys(ReportBuilder::aggregates());
foreach ($aggAll as $agg) {
    try {
        $r = ReportBuilder::execute(['data_source' => 'invoices', 'group_by' => ['area'],
            'columns' => [['field' => 'area', 'aggregate' => 'none'], ['field' => 'outstanding_amount', 'aggregate' => $agg]]], 20);
        if (isset($r['rows'])) $aggOk++; else bad("S17 agregasi $agg tidak menghasilkan baris");
    } catch (\Throwable $e) { bad("S17 agregasi $agg gagal: " . $e->getMessage()); }
}
eq(count($aggAll), $aggOk, 'S17 seluruh ' . count($aggAll) . ' fungsi agregasi berfungsi');

$pivot = ReportBuilder::execute(['data_source' => 'invoices', 'group_by' => ['area'],
    'columns' => [['field' => 'area', 'aggregate' => 'none'], ['field' => 'outstanding_amount', 'aggregate' => 'sum']],
    'pivot' => ['column_field' => 'payment_status', 'value_field' => 'outstanding_amount', 'aggregate' => 'sum']]);
ok(isset($pivot['rows']), 'S17 mode pivot menghasilkan ' . count($pivot['rows'] ?? []) . ' baris');

// =====================================================================
section('SKENARIO 18 — Ekspor laporan (Excel / CSV / PDF / cetak)');
// =====================================================================

$kolomEkspor = [
    ['label' => 'No. Invoice', 'key' => 'invoice_no', 'type' => 'text'],
    ['label' => 'No. Faktur Pajak', 'key' => 'tax_invoice_no', 'type' => 'text'],
    ['label' => 'Tanggal', 'key' => 'invoice_date', 'type' => 'date'],
    ['label' => 'Saldo', 'key' => 'outstanding_amount', 'type' => 'money'],
];
$barisEkspor = $db->all('SELECT invoice_no, tax_invoice_no, invoice_date, outstanding_amount
                           FROM invoices WHERE deleted_at IS NULL ORDER BY id LIMIT 250');
$metaEkspor = ['judul' => 'UJI EKSPOR LAPORAN PIUTANG', 'tanggal_lapor' => Dates::fmt($hariIni),
               'orientasi' => 'landscape', 'sheet' => 'Uji Ekspor'];
$totalEkspor = ['outstanding_amount' => array_sum(array_column($barisEkspor, 'outstanding_amount'))];

$xw = new \App\Services\ExcelWriter();
$xw->addSheet('Uji Ekspor', $kolomEkspor, $barisEkspor, ['total' => $totalEkspor]);
$xlsx = $xw->output();
ok(strlen($xlsx) > 2000, 'S18 ekspor XLSX terbentuk (' . number_format(strlen($xlsx) / 1024, 1) . ' KB)');
$tmpx = sys_get_temp_dir() . '/uji_ekspor.xlsx';
file_put_contents($tmpx, $xlsx);
$zip = new ZipArchive();
ok($zip->open($tmpx) === true, 'S18 berkas XLSX valid & dapat dibuka arsipnya');
ok($zip->locateName('xl/worksheets/sheet1.xml') !== false, 'S18 struktur worksheet XLSX benar');
ok($zip->locateName('xl/styles.xml') !== false, 'S18 gaya/format angka disertakan');
$zip->close();

$rd = new \App\Services\ExcelReader($tmpx);
$sheetNames = $rd->sheetNames();
eq('Uji Ekspor', $sheetNames[0]['name'] ?? ($sheetNames[0] ?? ''), 'S18 nama sheet tersimpan benar');
$barisBaca = [];
foreach ($rd->rows(is_array($sheetNames[0]) ? $sheetNames[0]['name'] : $sheetNames[0]) as $row) $barisBaca[] = $row;
ok(count($barisBaca) >= count($barisEkspor) + 1, 'S18 XLSX dapat dibaca ulang (' . count($barisBaca) . ' baris)');

$adaFP = false;
foreach ($barisEkspor as $bx) if (($bx['tax_invoice_no'] ?? '') !== '' && str_starts_with((string)$bx['tax_invoice_no'], '0')) { $adaFP = (string)$bx['tax_invoice_no']; break; }
if ($adaFP) {
    $ketemu = false;
    foreach ($barisBaca as $row) foreach ($row as $cell) if (($cell['v'] ?? '') === $adaFP) { $ketemu = true; break 2; }
    ok($ketemu, "S18 nomor faktur pajak berawalan nol tetap utuh ($adaFP)");
}
@unlink($tmpx);

$csv = \App\Services\CsvWriter::build($kolomEkspor, $barisEkspor);
ok(str_starts_with($csv, "\xEF\xBB\xBF"), 'S18 CSV memakai BOM UTF-8 (kompatibel Excel Indonesia)');
$barisCsv = array_filter(explode("\n", trim(substr($csv, 3))));
ok(count($barisCsv) >= count($barisEkspor), 'S18 jumlah baris CSV sesuai (' . count($barisCsv) . ')');
ok(!preg_match('/^[=+\-@]/m', str_replace('="', '', substr($csv, 3))), 'S18 CSV aman dari injeksi formula');

$pdf = ExportService::buildPdf($metaEkspor, $kolomEkspor, array_slice($barisEkspor, 0, 150), $totalEkspor);
ok(str_starts_with($pdf, '%PDF-'), 'S18 ekspor PDF menghasilkan berkas PDF valid');
ok(strlen($pdf) > 5000, 'S18 PDF berisi konten (' . number_format(strlen($pdf) / 1024, 1) . ' KB)');
ok(substr_count($pdf, '/Type /Page') >= 2, 'S18 PDF multi-halaman dengan header berulang');
ok(str_contains($pdf, 'trailer') && str_contains($pdf, '%%EOF'), 'S18 struktur PDF lengkap (xref & trailer)');

$html = ExportService::buildPrintHtml($metaEkspor, $kolomEkspor, array_slice($barisEkspor, 0, 50), $totalEkspor);
ok(str_contains($html, 'UJI EKSPOR LAPORAN PIUTANG'), 'S18 HTML cetak memuat judul laporan');
ok(str_contains($html, '@media print'), 'S18 HTML cetak memiliki gaya khusus pencetakan');
ok(str_contains($html, 'Rp'), 'S18 HTML cetak memakai format Rupiah');

// =====================================================================
section('SKENARIO 19 — Pembatasan hak akses & cakupan data');
// =====================================================================

$byRole = [];
foreach ($db->all('SELECT r.code, COUNT(rp.permission_id) n FROM roles r
                    LEFT JOIN role_permissions rp ON rp.role_id = r.id GROUP BY r.id') as $p) {
    $byRole[$p['code']] = (int)$p['n'];
}
eq((int)$db->value('SELECT COUNT(*) FROM permissions'), $byRole['SUPERADMIN'] ?? 0,
   'S19 SUPERADMIN memiliki seluruh hak akses');
ok(($byRole['SALES'] ?? 0) < ($byRole['FINANCE_AR'] ?? 0),
   'S19 SALES (' . ($byRole['SALES'] ?? 0) . ') lebih terbatas daripada FINANCE_AR (' . ($byRole['FINANCE_AR'] ?? 0) . ')');
ok(($byRole['AUDITOR'] ?? 0) > 0, 'S19 AUDITOR memiliki hak baca (' . ($byRole['AUDITOR'] ?? 0) . ' izin)');

eq(0, (int)$db->value("SELECT COUNT(*) FROM role_permissions rp
                         JOIN permissions p ON p.id = rp.permission_id JOIN roles r ON r.id = rp.role_id
                        WHERE r.code='AUDITOR' AND p.action IN ('create','edit','delete','approve')"),
   'S19 AUDITOR tidak memiliki hak tulis/hapus/setujui');
eq(0, (int)$db->value("SELECT COUNT(*) FROM role_permissions rp
                         JOIN permissions p ON p.id = rp.permission_id JOIN roles r ON r.id = rp.role_id
                        WHERE r.code='SALES' AND p.action='delete'"),
   'S19 SALES tidak dapat menghapus data apa pun');
eq(0, (int)$db->value("SELECT COUNT(*) FROM role_permissions rp
                         JOIN permissions p ON p.id = rp.permission_id JOIN roles r ON r.id = rp.role_id
                        WHERE r.code='COLLECTION' AND p.module IN ('pengaturan','pengguna','pencadangan')"),
   'S19 COLLECTION tidak dapat mengakses pengaturan/pengguna/pencadangan');

$scopeMap = [];
foreach ($db->all('SELECT code, data_scope FROM roles') as $s) $scopeMap[$s['code']] = $s['data_scope'];
eq('all', $scopeMap['SUPERADMIN'] ?? '', 'S19 cakupan data SUPERADMIN = seluruh data');
eq('own_sales', $scopeMap['SALES'] ?? '', 'S19 cakupan data SALES = portofolio sendiri');
eq('own_collection', $scopeMap['COLLECTION'] ?? '', 'S19 cakupan data COLLECTION = penugasan sendiri');

$nAll = (int)$db->value('SELECT COUNT(*) FROM invoices WHERE deleted_at IS NULL');
foreach (['SALES' => 'own_sales', 'COLLECTION' => 'own_collection'] as $role => $scope) {
    $u = $db->one('SELECT u.*, r.code role_code, r.data_scope FROM users u
                    JOIN roles r ON r.id = u.role_id WHERE r.code = :c LIMIT 1', ['c' => $role]);
    if (!$u) { bad("S19 pengguna contoh untuk peran $role tidak ditemukan"); continue; }
    $_SESSION['user'] = $u; $_SESSION['user_id'] = (int)$u['id'];
    \App\Core\Auth::forget();
    [$sql, $prm] = \App\Core\Acl::invoiceScopeSql('i');
    ok(trim($sql) !== '' && trim($sql) !== '1=1', "S19 klausa pembatas $role aktif: " . trim($sql));
    $n = (int)$db->value("SELECT COUNT(*) FROM invoices i WHERE i.deleted_at IS NULL AND ($sql)", $prm);
    ok($n < $nAll, "S19 $role hanya melihat " . nf($n) . ' dari ' . nf($nAll) . ' invoice');
}

$_SESSION['user'] = $db->one('SELECT u.*, r.code role_code, r.data_scope FROM users u
                               JOIN roles r ON r.id = u.role_id WHERE u.id = 1');
$_SESSION['user_id'] = 1;
\App\Core\Auth::forget();
[$sqlAdmin, $prmAdmin] = \App\Core\Acl::invoiceScopeSql('i');
eq($nAll, (int)$db->value("SELECT COUNT(*) FROM invoices i WHERE i.deleted_at IS NULL AND ($sqlAdmin)", $prmAdmin),
   'S19 SUPERADMIN melihat seluruh invoice');
ok(!\App\Core\Acl::can('pengaturan.edit') === false, 'S19 SUPERADMIN dapat mengubah pengaturan');

// =====================================================================
section('VERIFIKASI INTEGRITAS DATA');
// =====================================================================

$cek = $db->one("SELECT
      SUM(ABS(ROUND(net_amount - (gross_amount - total_deduction - credit_note_amount),2)) > 0.5) salah_net,
      SUM(ABS(ROUND(outstanding_amount - (net_amount - paid_amount),2)) > 0.5)                    salah_saldo,
      SUM(ABS(ROUND(dpp_amount + vat_amount - gross_amount,2)) > 0.5)                             salah_dpp,
      SUM(payment_status_code = 'LUNAS' AND outstanding_amount > 1)                               lunas_bersaldo,
      SUM(due_date IS NULL)                                                                       tanpa_jatuh_tempo,
      SUM(paid_amount < -0.5)                                                                     bayar_negatif,
      COUNT(*) total
    FROM invoices WHERE deleted_at IS NULL AND doc_status = 'active'");
eq(0, (int)$cek['salah_net'], 'INT-1 nilai bersih konsisten di ' . nf((int)$cek['total']) . ' invoice aktif');
eq(0, (int)$cek['salah_saldo'], 'INT-2 saldo = nilai bersih - terbayar');
eq(0, (int)$cek['salah_dpp'], 'INT-3 DPP + PPN = bruto');
eq(0, (int)$cek['lunas_bersaldo'], 'INT-4 tidak ada invoice LUNAS yang masih bersaldo');
eq(0, (int)$cek['tanpa_jatuh_tempo'], 'INT-5 seluruh invoice aktif memiliki tanggal jatuh tempo');
eq(0, (int)$cek['bayar_negatif'], 'INT-6 tidak ada nilai pembayaran negatif');

eq(0, (int)$db->value("SELECT COUNT(*) FROM (
        SELECT p.id, p.amount, COALESCE(SUM(pa.amount),0) a FROM payments p
        LEFT JOIN payment_allocations pa ON pa.payment_id = p.id
        WHERE p.deleted_at IS NULL AND p.status = 'posted' GROUP BY p.id
     ) x WHERE ABS(ROUND(amount - a, 2)) > 0.5"),
   'INT-7 seluruh pembayaran teralokasi penuh ke invoice');

eq(0, (int)$db->value("SELECT COUNT(*) FROM (
        SELECT i.id, i.paid_amount, COALESCE(SUM(pa.amount),0) a FROM invoices i
        LEFT JOIN payment_allocations pa ON pa.invoice_id = i.id
        LEFT JOIN payments p ON p.id = pa.payment_id AND p.deleted_at IS NULL AND p.status = 'posted'
        WHERE i.deleted_at IS NULL GROUP BY i.id
     ) x WHERE ABS(ROUND(paid_amount - a, 2)) > 0.5"),
   'INT-8 paid_amount invoice = jumlah alokasi pembayaran');

eq(0, (int)$db->value('SELECT COUNT(*) FROM invoices WHERE deleted_at IS NULL
                        AND is_revenue_counted = 1 AND replaced_by_invoice_id IS NOT NULL'),
   'INT-9 tidak ada invoice yang sudah digantikan tetapi masih dihitung sebagai pendapatan');

eq(0, (int)$db->value("SELECT COUNT(*) FROM invoices WHERE deleted_at IS NULL
                        AND doc_status <> 'active' AND outstanding_amount > 1"),
   'INT-10 invoice non-aktif tidak menyisakan saldo piutang');

$saldoHariIni  = ArCalculator::arBalanceAt($hariIni);
$saldoLangsung = (float)$db->value("SELECT COALESCE(SUM(outstanding_amount),0) FROM invoices
                                     WHERE deleted_at IS NULL AND doc_status = 'active'");
eq(round($saldoLangsung, 2), round($saldoHariIni, 2),
   'INT-11 rekonstruksi saldo historis = saldo tersimpan (' . money($saldoHariIni) . ')');

eq(0, (int)$db->value("SELECT COUNT(*) FROM invoices i WHERE i.deleted_at IS NULL
                        AND i.replaces_invoice_id IS NOT NULL
                        AND NOT EXISTS (SELECT 1 FROM invoices o WHERE o.id = i.replaces_invoice_id)"),
   'INT-12 seluruh tautan revisi menunjuk invoice yang ada');

// =====================================================================
section('PENAGIHAN & KUALITAS DATA');
// =====================================================================

$fu = CollectionService::saveFollowup(
    ['invoice_id' => $id5, 'method' => 'TELEPON', 'followup_date' => $hariIni,
     'contact_person' => 'Bpk. Uji', 'result' => 'Berjanji membayar minggu depan', 'priority' => 'TINGGI',
     'collection_status_id' => (int)$db->value('SELECT id FROM collection_statuses ORDER BY id LIMIT 1')],
    ['promise_date' => Dates::addDays($hariIni, 7), 'promise_amount' => 5000000.0, 'notes' => 'Janji uji']
);
ok(($fu['followup_id'] ?? 0) > 0, 'COL-1 aktivitas tindak lanjut penagihan tersimpan');
ok(($fu['promise_id'] ?? 0) > 0, 'COL-2 janji bayar (promise to pay) tersimpan');

$counts = CollectionService::worklistCounts();
eq(7, count($counts), 'COL-3 tujuh kategori worklist tersedia');
$totalWorklist = 0; foreach ($counts as $c) $totalWorklist += (int)$c['jumlah'];
ok($totalWorklist > 0, 'COL-4 worklist berisi ' . nf($totalWorklist) . ' invoice untuk ditindaklanjuti');
foreach ($counts as $k => $c) if (!isset($c['label'], $c['jumlah'], $c['nilai'])) bad("COL-5 kategori $k tidak lengkap");
ok(true, 'COL-5 seluruh kategori worklist memiliki label, jumlah, dan nilai');

$wl = CollectionService::worklist(array_key_first($counts), [], $hariIni, 'outstanding_amount', 'desc', 1, 10);
ok(isset($wl['data']), 'COL-6 daftar worklist dapat dipanggil (' . count($wl['data'] ?? []) . ' baris)');
$ev = CollectionService::evaluatePromises();
ok(is_array($ev), 'COL-7 evaluasi janji bayar berjalan (' . ($ev['dievaluasi'] ?? count($ev)) . ' data)');

$dq = DataQualityService::summary();
ok(count($dq) >= 15, 'DQ-1 ' . count($dq) . ' pemeriksaan kualitas data dijalankan');
$dqLengkap = true;
foreach ($dq as $d) if (!isset($d['jumlah'], $d['label'], $d['tingkat'], $d['saran'])) $dqLengkap = false;
ok($dqLengkap, 'DQ-2 seluruh pemeriksaan mengembalikan struktur lengkap');
$dqPertama = array_key_first($dq);
$det = DataQualityService::detail($dqPertama, [], 1, 10);
ok(isset($det['data']), 'DQ-3 detail pemeriksaan "' . $dqPertama . '" dapat ditampilkan');
$rekon = DataQualityService::excelReconciliation(50);
ok(is_array($rekon), 'DQ-4 rekonsiliasi terhadap nilai Excel asli tersedia');
$dup = DataQualityService::duplicates();
ok(is_array($dup), 'DQ-5 pemeriksaan duplikat berjalan');

summary();
