<?php
require __DIR__ . '/_boot.php';

use App\Services\ExcelReader;

$file = SIPIUTANG_ROOT . '/storage/tmp/AR_RPP_2026.xlsx';
if (!is_file($file)) { echo "Berkas uji tidak tersedia: $file\n"; exit(0); }

head('Daftar worksheet');
$r = new ExcelReader($file);
$sheets = $r->sheetNames();
foreach ($sheets as $s) echo "   - {$s['name']} (index {$s['index']}, {$s['state']})\n";
assertEq(count($sheets), 8, 'Jumlah worksheet terdeteksi');

head('Header sheet JANUARI');
$h = $r->headers('JANUARI');
echo '   ' . implode(' | ', array_map(fn($k, $v) => "$k=$v", array_keys($h), $h)) . "\n";
assertEq($h['A'] ?? '', 'AREA', 'Kolom A');
assertEq($h['J'] ?? '', 'NOMINAL', 'Kolom J');
assertEq($h['AB'] ?? '', 'UMUR PIUTANG', 'Kolom AB');

head('Baris data & deteksi tipe');
$rows = $r->rows('JANUARI', 3, 2);
$first = reset($rows);
assertEq($first['A']['v'] ?? '', 'JKT', 'A2 area');
assertEq($first['D']['v'] ?? '', '2026-01-31', 'D2 tanggal invoice terdeteksi sebagai tanggal');
assertEq($first['D']['t'] ?? '', 'd', 'D2 bertipe tanggal');
assertEq($first['G']['v'] ?? '', '04002600029966887', 'G2 faktur pajak tetap teks dengan nol di depan');
assertEq($first['J']['v'] ?? 0, 832500, 'J2 nominal');
assertTrue(str_starts_with((string)($first['R']['f'] ?? ''), '=Q2+30'), 'R2 memuat rumus =Q2+30');
assertTrue(str_contains((string)($first['T']['f'] ?? ''), 'J2/1.11*0.02'), 'T2 memuat rumus PPh 23');

head('Deteksi rumus aging rusak (DQ-01)');
$all = $r->rows('JANUARI', 0, 2);
$rusak = 0; $benar = 0;
foreach ($all as $n => $c) {
    $f = (string)($c['AD']['f'] ?? '');
    if ($f === '') continue;
    if (preg_match('/,\s*I\d+\s*,/', $f) || str_contains($f, '#REF!')) $rusak++;
    elseif (preg_match('/,\s*J\d+\s*,/', $f)) $benar++;
}
echo "   AD rusak=$rusak  benar=$benar\n";
assertTrue($rusak > 300, 'Rumus AD rusak terdeteksi dalam jumlah besar');

head('Nilai error Excel');
$maret = $r->rows('MARET', 0, 2);
$found = null;
foreach ($maret as $n => $c) {
    foreach ($c as $col => $cell) {
        if (!empty($cell['err'])) { $found = "$col$n = {$cell['err']}"; break 2; }
    }
}
echo "   " . ($found ?? 'tidak ada') . "\n";
assertTrue($found !== null, 'Nilai error Excel terdeteksi (#VALUE!)');

head('Baris kosong berisi rumus di JULI (DQ-30)');
$juli = $r->rows('JULI', 0, 2);
$dataRows = 0; $ghost = 0;
foreach ($juli as $n => $c) {
    $hasData = false;
    foreach (['A','D','F','H','J'] as $col) {
        if (($c[$col]['v'] ?? null) !== null && trim((string)$c[$col]['v']) !== '') { $hasData = true; break; }
    }
    if ($hasData) $dataRows++; else $ghost++;
}
echo "   baris data=$dataRows  baris hantu=$ghost\n";
assertEq($dataRows, 143, 'Jumlah baris data JULI');
assertTrue($ghost > 800, 'Baris hantu berisi rumus terdeteksi');

head('Sheet RF');
$rf = $r->rows('Copy of KOLOM RF', 0, 2);
$n = 0;
foreach ($rf as $row) { if (($row['B']['v'] ?? null) !== null) $n++; }
assertEq($n, 29, 'Jumlah baris RF dengan nomor RF (r2-r30)');

summary();
