<?php

function normalizeImportText(string $value): string
{
    $value = trim(strtolower($value));
    $value = preg_replace('/\s+/', ' ', $value);
    return $value ?? '';
}

function mapPekerjaanOrtu(string $value): string
{
    $trimmed = trim($value);
    if ($trimmed === '') {
        return '';
    }
    if (validPekerjaanOrtu($trimmed)) {
        return $trimmed;
    }

    $normalized = normalizeImportText($trimmed);
    $aliases = [
        'tidak bekerja' => 'Tidak bekerja',
        'tidak kerja' => 'Tidak bekerja',
        'wirausaha' => 'Wirausaha',
        'wiraswasta' => 'Wiraswasta',
    ];

    return $aliases[$normalized] ?? '';
}

function normalizePenghasilanKey(string $text): string
{
    $key = strtolower(trim($text));
    $key = str_replace(['–', '—', '−'], '-', $key);
    $key = preg_replace('/\s+/', '', $key);
    $key = str_replace('.', '', $key);
    return $key;
}

function mapPenghasilanOrtu(string $value): string
{
    $trimmed = trim($value);
    if ($trimmed === '') {
        return '';
    }
    if (validPenghasilanOrtu($trimmed)) {
        return $trimmed;
    }

    $normalized = normalizeImportText($trimmed);
    // Label nominal (4 tingkat) → nilai dropdown default bila hanya label yang diisi.
    $aliases = [
        'sangat rendah' => 'Kurang dari Rp. 500.000',
        'tidak berpenghasilan' => 'Tidak Berpenghasilan',
        'rendah' => 'Rp. 500.000 - Rp. 999.999',
        'sedang' => 'Rp. 2.000.000 - Rp. 4.999.999',
        'tinggi' => 'Rp. 5.000.000 - Rp. 20.000.000',
    ];
    if (isset($aliases[$normalized])) {
        return $aliases[$normalized];
    }

    $inputKey = normalizePenghasilanKey($trimmed);
    foreach (penghasilanOrtuList() as $opt) {
        if (normalizePenghasilanKey($opt) === $inputKey) {
            return $opt;
        }
    }

    $looksLikeRange = preg_match('/[-–—−]/', $trimmed) === 1;
    if (!$looksLikeRange) {
        $numericRaw = preg_replace('/[^0-9]/', '', $trimmed);
        if ($numericRaw !== '' && strlen($numericRaw) <= 9) {
            $amount = (int) $numericRaw;
            if ($amount <= 0) {
                return 'Tidak Berpenghasilan';
            }
            if ($amount < 500000) {
                return 'Kurang dari Rp. 500.000';
            }
            if ($amount <= 999999) {
                return 'Rp. 500.000 - Rp. 999.999';
            }
            if ($amount <= 1999999) {
                return 'Rp. 1.000.000 - Rp. 1.999.999';
            }
            if ($amount <= 4999999) {
                return 'Rp. 2.000.000 - Rp. 4.999.999';
            }
            if ($amount <= 20000000) {
                return 'Rp. 5.000.000 - Rp. 20.000.000';
            }
            return 'Lebih dari Rp. 20.000.000';
        }
    }

    return '';
}

function mapYaTidak(string $value): string
{
    $normalized = normalizeImportText($value);
    if ($normalized === 'ya') {
        return 'Ya';
    }
    if ($normalized === 'tidak') {
        return 'Tidak';
    }
    return 'Tidak';
}

function penghasilanNominalKeteranganHtml(): string
{
    return '<strong>Keterangan nominal penghasilan:</strong><br>'
        . 'Sangat Rendah = Tidak Berpenghasilan, dan Kurang dari Rp. 500.000<br>'
        . 'Rendah = Rp. 500.000 - Rp. 999.999 dan Rp. 1.000.000 - Rp. 1.999.999<br>'
        . 'Sedang = Rp. 2.000.000 - Rp. 4.999.999<br>'
        . 'Tinggi = Rp. 5.000.000 - Rp. 20.000.000 dan Lebih dari Rp. 20.000.000';
}

function parseCsvRows(string $path, int $maxColumns = 6): array
{
    $rows = [];
    $handle = fopen($path, 'r');
    if (!$handle) {
        return $rows;
    }

    fgetcsv($handle);
    while (($row = fgetcsv($handle)) !== false) {
        $rows[] = array_slice($row, 0, $maxColumns);
    }
    fclose($handle);
    return $rows;
}

function columnToIndex(string $cellRef): int
{
    $letters = preg_replace('/[^A-Z]/', '', strtoupper($cellRef));
    $index = 0;
    for ($i = 0; $i < strlen($letters); $i++) {
        $index = ($index * 26) + (ord($letters[$i]) - 64);
    }
    return max(0, $index - 1);
}

function parseXlsxRows(string $path, int $maxColumns = 6): array
{
    $rows = [];
    $zip = new ZipArchive();
    if ($zip->open($path) !== true) {
        return $rows;
    }

    $sheetXml = $zip->getFromName('xl/worksheets/sheet1.xml');
    if ($sheetXml === false) {
        $zip->close();
        return $rows;
    }

    $sharedStrings = [];
    $sharedXml = $zip->getFromName('xl/sharedStrings.xml');
    if ($sharedXml !== false) {
        $shared = @simplexml_load_string($sharedXml);
        if ($shared !== false && isset($shared->si)) {
            foreach ($shared->si as $si) {
                $parts = [];
                if (isset($si->t)) {
                    $parts[] = (string) $si->t;
                }
                if (isset($si->r)) {
                    foreach ($si->r as $run) {
                        $parts[] = (string) $run->t;
                    }
                }
                $sharedStrings[] = implode('', $parts);
            }
        }
    }

    $sheet = @simplexml_load_string($sheetXml);
    if ($sheet === false || !isset($sheet->sheetData->row)) {
        $zip->close();
        return $rows;
    }

    $isFirstRow = true;
    $lastCol = max(0, $maxColumns - 1);
    foreach ($sheet->sheetData->row as $row) {
        if ($isFirstRow) {
            $isFirstRow = false;
            continue;
        }

        $cells = array_fill(0, $maxColumns, '');
        foreach ($row->c as $cell) {
            $ref = (string) ($cell['r'] ?? '');
            $idx = columnToIndex($ref);
            if ($idx < 0 || $idx > $lastCol) {
                continue;
            }

            $type = (string) ($cell['t'] ?? '');
            $value = '';
            if ($type === 's') {
                $sharedIndex = (int) ($cell->v ?? 0);
                $value = $sharedStrings[$sharedIndex] ?? '';
            } elseif ($type === 'inlineStr') {
                $value = (string) ($cell->is->t ?? '');
            } else {
                $value = (string) ($cell->v ?? '');
            }
            $cells[$idx] = trim($value);
        }
        $rows[] = $cells;
    }

    $zip->close();
    return $rows;
}
