<?php

namespace App\Services\QuickBooks;

use Carbon\Carbon;
use PhpOffice\PhpSpreadsheet\IOFactory;

class QuickBooksJournalParser
{
    /**
     * @return array<int, array{
     *   trans_no: string,
     *   date: ?Carbon,
     *   party_name: ?string,
     *   narration: ?string,
     *   reference_no: ?string,
     *   lines: array<int, array{account: string, account_type: ?string, memo: ?string, debit: float, credit: float}>
     * }>
     */
    public function parse(string $filePath): array
    {
        $reader = IOFactory::createReaderForFile($filePath);
        $reader->setReadDataOnly(true);
        $spreadsheet = $reader->load($filePath);
        $sheet = $spreadsheet->getSheetByName('Sheet1') ?? $spreadsheet->getActiveSheet();
        $rows = $sheet->toArray(null, true, true, false);

        if (count($rows) < 2) {
            return [];
        }

        $header = $this->headerMap(array_shift($rows));
        $groups = [];
        $currentKey = null;
        $currentDate = null;
        $currentParty = null;
        $currentNarration = null;

        foreach ($rows as $row) {
            $trans = $this->cell($row, $header, ['trans #', 'trans', 'transaction #']);
            $account = $this->cell($row, $header, ['account']);
            $debit = $this->amount($this->cell($row, $header, ['debit']));
            $credit = $this->amount($this->cell($row, $header, ['credit']));

            if ($trans !== '') {
                $currentKey = $this->normalizeTransNo($trans);
                $groups[$currentKey] ??= [
                    'trans_no' => $currentKey,
                    'date' => null,
                    'party_name' => null,
                    'reference_no' => null,
                    'narration' => null,
                    'lines' => [],
                ];
            }

            if ($currentKey === null || $account === '') {
                continue;
            }

            $dateValue = $this->cell($row, $header, ['date']);
            if ($dateValue !== '') {
                $currentDate = $this->parseDate($dateValue);
                $groups[$currentKey]['date'] = $currentDate;
            }

            $nameValue = $this->cell($row, $header, ['name']);
            if ($nameValue !== '') {
                $currentParty = $nameValue;
                $groups[$currentKey]['party_name'] = $currentParty;
            }

            $referenceNo = $this->cell($row, $header, ['num', 'number', 'ref', 'reference']);
            if ($referenceNo !== '') {
                $groups[$currentKey]['reference_no'] = $referenceNo;
            }

            $memo = $this->cell($row, $header, ['memo']);
            if ($memo !== '' && empty($groups[$currentKey]['narration'])) {
                $groups[$currentKey]['narration'] = $memo;
                $currentNarration = $memo;
            }

            if ($debit <= 0 && $credit <= 0) {
                continue;
            }

            $groups[$currentKey]['lines'][] = [
                'account' => $account,
                'account_type' => $this->cell($row, $header, ['account type']) ?: null,
                'memo' => $memo !== '' ? $memo : $currentNarration,
                'debit' => $debit,
                'credit' => $credit,
            ];
        }

        return array_values(array_filter($groups, fn(array $group) => count($group['lines']) > 0));
    }

    /**
     * @return array<string, int>
     */
    private function headerMap(array $headerRow): array
    {
        $map = [];

        foreach ($headerRow as $index => $label) {
            $key = strtolower(trim(preg_replace('/[\s_]+/', ' ', (string) $label)));
            if ($key !== '') {
                $map[$key] = $index;
            }
        }

        return $map;
    }

    private function cell(array $row, array $header, array $keys): string
    {
        foreach ($keys as $key) {
            if (!array_key_exists($key, $header)) {
                continue;
            }

            $value = $row[$header[$key]] ?? null;

            if ($value !== null && trim((string) $value) !== '') {
                return trim((string) $value);
            }
        }

        return '';
    }

    private function amount(mixed $value): float
    {
        if ($value === null || $value === '') {
            return 0.0;
        }

        return round((float) str_replace(',', '', (string) $value), 2);
    }

    private function normalizeTransNo(string $value): string
    {
        if (is_numeric($value)) {
            return (string) (int) (float) $value;
        }

        return trim($value);
    }

    private function parseDate(mixed $value): ?Carbon
    {
        if ($value instanceof \DateTimeInterface) {
            return Carbon::instance($value)->startOfDay();
        }

        $string = trim((string) $value);

        if ($string === '') {
            return null;
        }

        try {
            if (is_numeric($value)) {
                return Carbon::createFromTimestampUTC(((int) $value - 25569) * 86400)->startOfDay();
            }

            return Carbon::parse($string)->startOfDay();
        } catch (\Throwable) {
            return null;
        }
    }
}
