<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Maatwebsite\Excel\Facades\Excel;

class CheckExcel extends Command
{
    protected $signature = 'check:excel {file}';
    protected $description = 'Check Excel file structure';

    public function handle()
    {
        $file = $this->argument('file');

        if (!file_exists($file)) {
            $this->error("File not found: $file");
            return;
        }

        $data = Excel::toArray([], $file);
        $this->info("Total sheets: " . count($data));

        foreach ($data as $sheetIndex => $rows) {
            $this->info("Sheet $sheetIndex: " . count($rows) . " rows");

            if ($sheetIndex > 0) break;
        }

        $rows = $data[0];
        $this->info("\nChecking for ORANG TUA ASUH data...");

        for ($i = 0; $i < count($rows); $i++) {
            $rowStr = implode(',', array_slice($rows[$i], 0, 15));
            if (stripos($rowStr, 'ORANG TUA ASUH') !== false || stripos($rowStr, 'ORTU ASUH') !== false) {
                $this->line("Found at row $i: $rowStr");
            }
        }

        $this->info("\nAll unique values in last column (KETERANGAN):");
        $keterangan = [];
        for ($i = 12; $i < count($rows); $i++) {
            $val = $rows[$i][9] ?? '';
            if ($val && !in_array($val, $keterangan)) {
                $keterangan[] = $val;
            }
        }
        foreach ($keterangan as $k) {
            $this->line("- $k");
        }
    }
}
