<?php

namespace App\Services;

use App\Helpers\NumberHelper;
use App\Models\Account;
use App\Models\AuditLog;
use App\Models\BankAccount;
use App\Models\BankReconciliation;
use App\Models\BankReconciliationItem;
use App\Models\BankStatementImport;
use App\Models\BankStatementLine;
use App\Models\LedgerEntry;
use App\Models\Setting;
use App\Models\Voucher;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Http\Request;
use Illuminate\Http\UploadedFile;
use Illuminate\Support\Carbon;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use Illuminate\Validation\ValidationException;
use Maatwebsite\Excel\Facades\Excel;

class BankReconciliationService
{
    public function __construct(
        private readonly ChequePrintService $chequePrint,
        private readonly LedgerReportService $ledgerReports,
    ) {
    }

    public function bankAccounts(int $companyId): Collection
    {
        return Account::withoutGlobalScopes()
            ->where('company_id', $companyId)
            ->postable()
            ->where('is_bank', true)
            ->where('is_cash', false)
            ->orderBy('code')
            ->orderBy('name')
            ->get(['id', 'code', 'name']);
    }

    public function history(int $companyId, int $accountId): Collection
    {
        $bankAccount = $this->resolveBankAccount($companyId, $accountId);

        return BankReconciliation::withoutGlobalScopes()
            ->where('company_id', $companyId)
            ->where('bank_account_id', $bankAccount->id)
            ->orderByDesc('statement_date')
            ->orderByDesc('id')
            ->get();
    }

    public function inProgress(int $companyId, int $accountId): ?BankReconciliation
    {
        $bankAccount = $this->resolveBankAccount($companyId, $accountId);

        return BankReconciliation::withoutGlobalScopes()
            ->where('company_id', $companyId)
            ->where('bank_account_id', $bankAccount->id)
            ->where('status', 'in_progress')
            ->latest('id')
            ->first();
    }

    public function beginningBalance(int $companyId, int $accountId, ?string $beforeDate = null): float
    {
        $bankAccount = $this->resolveBankAccount($companyId, $accountId);

        $lastCompleted = BankReconciliation::withoutGlobalScopes()
            ->where('company_id', $companyId)
            ->where('bank_account_id', $bankAccount->id)
            ->where('status', 'reconciled')
            ->when($beforeDate, fn ($query) => $query->whereDate('statement_date', '<', $beforeDate))
            ->orderByDesc('statement_date')
            ->orderByDesc('id')
            ->first();

        if ($lastCompleted) {
            return (float) $lastCompleted->statement_balance;
        }

        $query = LedgerEntry::withoutGlobalScopes()
            ->where('company_id', $companyId)
            ->where('account_id', $accountId);

        if ($beforeDate) {
            $query->whereDate('entry_date', '<', $beforeDate);
        }

        return (float) $query
            ->selectRaw('COALESCE(SUM(debit),0) - COALESCE(SUM(credit),0) as net')
            ->value('net');
    }

    public function start(int $companyId, int $accountId, array $data, ?int $userId = null): BankReconciliation
    {
        $existing = $this->inProgress($companyId, $accountId);
        if ($existing) {
            throw ValidationException::withMessages([
                'statement_date' => 'An in-progress reconciliation already exists for this account. Resume or complete it first.',
            ]);
        }

        $bankAccount = $this->resolveBankAccount($companyId, $accountId);
        $statementDate = Carbon::parse($data['statement_date'])->toDateString();
        $beginning = array_key_exists('beginning_balance', $data)
            ? (float) $data['beginning_balance']
            : $this->beginningBalance($companyId, $accountId, $statementDate);

        $reco = BankReconciliation::withoutGlobalScopes()->create([
            'company_id' => $companyId,
            'bank_account_id' => $bankAccount->id,
            'statement_date' => $statementDate,
            'statement_balance' => (float) $data['statement_balance'],
            'beginning_balance' => $beginning,
            'book_balance' => $this->bookBalance($companyId, $accountId, $statementDate),
            'reconciled_balance' => 0,
            'notes' => $data['notes'] ?? null,
            'status' => 'in_progress',
        ]);

        AuditLog::record('create', 'bank_reconciliation', (string) $reco->id, null, [
            'account_id' => $accountId,
            'statement_date' => $statementDate,
        ], 'Bank reconciliation started');

        return $reco;
    }

    public function workspace(int $companyId, BankReconciliation $reco, array $filters = []): array
    {
        $this->assertCompany($companyId, $reco);
        $reco->load('bankAccount.account');

        $accountId = (int) $reco->bankAccount->account_id;
        $clearedIds = $this->clearedEntryIds($reco);
        $allTransactions = $this->transactions($companyId, $accountId, $reco, $this->emptyFilters($reco));
        $transactions = $this->transactions($companyId, $accountId, $reco, $filters);
        $visibleIds = $transactions->pluck('id')->map(fn ($id) => (int) $id)->all();
        $hiddenClearedIds = array_values(array_diff($clearedIds, $visibleIds));
        $statementLines = $this->statementLines($companyId, $reco);
        $summary = $this->summary($reco, $clearedIds, $allTransactions);

        return [
            'reconciliation' => $reco,
            'account' => $reco->bankAccount->account,
            'bankAccount' => $reco->bankAccount,
            'transactions' => $transactions,
            'hiddenClearedIds' => $hiddenClearedIds,
            'clearedEntryIds' => $clearedIds,
            'statementLines' => $statementLines,
            'summary' => $summary,
            'history' => $this->history($companyId, $accountId),
        ];
    }

    public function parseFilters(Request $request, BankReconciliation $reco): array
    {
        $statementDate = $reco->statement_date?->toDateString() ?? now()->toDateString();

        return [
            'date_from' => $request->input('date_from', ''),
            'date_to' => $request->input('date_to', $statementDate),
            'voucher_type' => trim((string) $request->input('voucher_type', '')),
            'parties' => array_values(array_filter((array) $request->input('parties', []))),
            'search' => trim((string) $request->input('search', '')),
        ];
    }

    public function filterOptions(int $companyId, int $accountId, BankReconciliation $reco): array
    {
        $types = Voucher::withoutGlobalScopes()
            ->where('company_id', $companyId)
            ->where('status', 'posted')
            ->whereIn('id', function ($sub) use ($companyId, $accountId, $reco) {
                $sub->select('voucher_id')
                    ->from('ledger_entries')
                    ->where('company_id', $companyId)
                    ->where('account_id', $accountId)
                    ->whereDate('entry_date', '<=', $reco->statement_date->toDateString())
                    ->whereNull('bank_reconciliation_id');
            })
            ->orderBy('voucher_type')
            ->distinct()
            ->pluck('voucher_type');

        $allTypes = VoucherNumberService::voucherTypes();
        $voucherTypes = ['' => 'All voucher types'];
        foreach ($types as $type) {
            if (isset($allTypes[$type])) {
                $voucherTypes[$type] = $allTypes[$type];
            }
        }

        return [
            'voucherTypes' => $voucherTypes,
            'partyTree' => $this->ledgerReports->profitLossPartyTree($companyId),
        ];
    }

    public function selectedPartyLabel(array $filters): string
    {
        return $this->ledgerReports->profitLossPartyLabels($filters['parties'] ?? []);
    }

    public function transactions(int $companyId, int $accountId, BankReconciliation $reco, array $filters = []): Collection
    {
        $statementDate = $reco->statement_date->toDateString();
        $clearedIds = $this->clearedEntryIds($reco);
        $dateTo = $filters['date_to'] ?: $statementDate;
        $dateFrom = $filters['date_from'] ?? '';

        $entries = LedgerEntry::withoutGlobalScopes()
            ->with([
                'voucher:id,voucher_type,voucher_number,voucher_date,status,narration,reference,extra_data,party_type,party_id',
                'voucher.lines:id,voucher_id,account_id,party_type,party_id,description,dr_cr',
                'voucher.lines.account:id,code,name,is_bank,is_cash',
            ])
            ->where('company_id', $companyId)
            ->where('account_id', $accountId)
            ->whereDate('entry_date', '<=', $dateTo)
            ->when($dateFrom !== '', fn (Builder $query) => $query->whereDate('entry_date', '>=', $dateFrom))
            ->whereNull('bank_reconciliation_id')
            ->whereHas('voucher', function (Builder $query) use ($filters) {
                $query->where('status', 'posted');

                if (($filters['voucher_type'] ?? '') !== '') {
                    $query->where('voucher_type', $filters['voucher_type']);
                }

                if (($filters['parties'] ?? []) !== []) {
                    $this->applyPartyFilter($query, $filters['parties']);
                }
            })
            ->orderBy('entry_date')
            ->orderBy('id')
            ->get();

        $transactions = $entries->map(
            fn (LedgerEntry $entry) => $this->presentTransaction($entry, $companyId, $accountId, $clearedIds),
        );

        if (($filters['search'] ?? '') !== '') {
            $needle = strtolower($filters['search']);
            $transactions = $transactions->filter(function (array $row) use ($needle) {
                foreach ([
                    $row['voucher_number'],
                    $row['payee'],
                    $row['memo'],
                    $row['type_label'],
                    $row['cheque_no'],
                    $row['cheque_date'],
                ] as $value) {
                    if ($value && str_contains(strtolower((string) $value), $needle)) {
                        return true;
                    }
                }

                return false;
            })->values();
        }

        return $transactions;
    }

    public function summary(BankReconciliation $reco, array $clearedEntryIds, ?Collection $transactions = null): array
    {
        $transactions ??= collect();
        $cleared = $transactions->filter(fn (array $row) => in_array($row['id'], $clearedEntryIds, true));

        $deposits = $cleared->sum(fn (array $row) => $row['deposit_raw']);
        $payments = $cleared->sum(fn (array $row) => $row['payment_raw']);
        $beginning = (float) $reco->beginning_balance;
        $clearedBalance = $beginning + $deposits - $payments;
        $difference = (float) $reco->statement_balance - $clearedBalance;

        return [
            'beginning_balance' => $beginning,
            'cleared_deposits' => $deposits,
            'cleared_payments' => $payments,
            'cleared_deposit_count' => $cleared->filter(fn (array $row) => $row['deposit_raw'] > 0)->count(),
            'cleared_payment_count' => $cleared->filter(fn (array $row) => $row['payment_raw'] > 0)->count(),
            'cleared_balance' => $clearedBalance,
            'statement_balance' => (float) $reco->statement_balance,
            'difference' => $difference,
            'is_balanced' => abs($difference) < 0.005,
        ];
    }

    public function saveProgress(int $companyId, BankReconciliation $reco, array $data): BankReconciliation
    {
        $this->assertCompany($companyId, $reco);
        $this->assertInProgress($reco);

        $reco->update([
            'statement_date' => Carbon::parse($data['statement_date'])->toDateString(),
            'statement_balance' => (float) $data['statement_balance'],
            'beginning_balance' => (float) $data['beginning_balance'],
            'notes' => $data['notes'] ?? null,
        ]);

        $entryIds = array_values(array_unique(array_map('intval', $data['cleared_entry_ids'] ?? [])));
        $this->syncClearedItems($companyId, $reco, $entryIds);

        $accountId = (int) $reco->bankAccount()->value('account_id');
        $reco->update([
            'book_balance' => $this->bookBalance($companyId, $accountId, $reco->statement_date->toDateString()),
        ]);

        AuditLog::record('update', 'bank_reconciliation', (string) $reco->id, null, [
            'cleared_count' => count($entryIds),
        ], 'Bank reconciliation progress saved');

        return $reco->fresh();
    }

    public function finalize(int $companyId, BankReconciliation $reco, array $data, int $userId): BankReconciliation
    {
        $this->assertCompany($companyId, $reco);
        $this->assertInProgress($reco);

        $reco = $this->saveProgress($companyId, $reco, $data);
        $accountId = (int) $reco->bankAccount()->value('account_id');
        $transactions = $this->transactions($companyId, $accountId, $reco, $this->emptyFilters($reco));
        $clearedIds = $this->clearedEntryIds($reco);
        $summary = $this->summary($reco, $clearedIds, $transactions);

        if (!$summary['is_balanced']) {
            throw ValidationException::withMessages([
                'statement_balance' => 'Difference must be zero before finishing reconciliation.',
            ]);
        }

        $now = now();
        DB::transaction(function () use ($reco, $clearedIds, $summary, $userId, $now) {
            LedgerEntry::withoutGlobalScopes()
                ->whereIn('id', $clearedIds)
                ->update([
                    'bank_reconciliation_id' => $reco->id,
                    'reconciled_at' => $now,
                ]);

            $reco->update([
                'status' => 'reconciled',
                'reconciled_balance' => $summary['cleared_balance'],
                'reconciled_by' => $userId,
                'reconciled_at' => $now,
            ]);
        });

        AuditLog::record('reconcile', 'bank_reconciliation', (string) $reco->id, null, [
            'cleared_count' => count($clearedIds),
            'statement_balance' => $summary['statement_balance'],
        ], 'Bank reconciliation completed');

        return $reco->fresh();
    }

    public function undo(int $companyId, BankReconciliation $reco): void
    {
        $this->assertCompany($companyId, $reco);

        if (!$reco->isReconciled()) {
            throw ValidationException::withMessages([
                'reconciliation' => 'Only completed reconciliations can be undone.',
            ]);
        }

        DB::transaction(function () use ($reco) {
            LedgerEntry::withoutGlobalScopes()
                ->where('bank_reconciliation_id', $reco->id)
                ->update([
                    'bank_reconciliation_id' => null,
                    'reconciled_at' => null,
                ]);

            $reco->update([
                'status' => 'in_progress',
                'reconciled_by' => null,
                'reconciled_at' => null,
                'reconciled_balance' => 0,
            ]);
        });

        AuditLog::record('undo', 'bank_reconciliation', (string) $reco->id, null, null, 'Bank reconciliation undone');
    }

    public function importStatement(int $companyId, BankReconciliation $reco, UploadedFile $file, ?int $userId = null): array
    {
        $this->assertCompany($companyId, $reco);
        $this->assertInProgress($reco);

        $rows = $this->parseStatementFile($file);
        if ($rows === []) {
            throw ValidationException::withMessages([
                'statement_file' => 'No valid statement rows were found in the uploaded file.',
            ]);
        }

        $import = BankStatementImport::withoutGlobalScopes()->create([
            'company_id' => $companyId,
            'bank_reconciliation_id' => $reco->id,
            'original_filename' => $file->getClientOriginalName(),
            'row_count' => count($rows),
            'imported_by' => $userId,
        ]);

        foreach ($rows as $row) {
            BankStatementLine::withoutGlobalScopes()->create([
                'company_id' => $companyId,
                'bank_statement_import_id' => $import->id,
                'bank_reconciliation_id' => $reco->id,
                ...$row,
            ]);
        }

        $matched = $this->autoMatch($companyId, $reco);

        return [
            'import' => $import,
            'imported_rows' => count($rows),
            'auto_matched' => $matched,
        ];
    }

    public function autoMatch(int $companyId, BankReconciliation $reco): int
    {
        $this->assertCompany($companyId, $reco);
        $accountId = (int) $reco->bankAccount()->value('account_id');
        $transactions = $this->transactions($companyId, $accountId, $reco, $this->emptyFilters($reco));
        $clearedIds = $this->clearedEntryIds($reco);

        $lines = BankStatementLine::withoutGlobalScopes()
            ->where('bank_reconciliation_id', $reco->id)
            ->whereNull('matched_ledger_entry_id')
            ->orderBy('transaction_date')
            ->get();

        $matched = 0;

        foreach ($lines as $line) {
            $candidate = $this->findMatchForStatementLine($line, $transactions, $clearedIds);
            if (!$candidate) {
                continue;
            }

            $line->update([
                'matched_ledger_entry_id' => $candidate['id'],
                'matched_at' => now(),
            ]);

            if (!in_array($candidate['id'], $clearedIds, true)) {
                BankReconciliationItem::withoutGlobalScopes()->firstOrCreate([
                    'company_id' => $companyId,
                    'bank_reconciliation_id' => $reco->id,
                    'ledger_entry_id' => $candidate['id'],
                ]);
                $clearedIds[] = $candidate['id'];
            }

            $matched++;
        }

        return $matched;
    }

    public function formatAmount(float $amount, int $companyId): string
    {
        return NumberHelper::formatAmount($amount, $companyId);
    }

    public function dateFormat(int $companyId): string
    {
        return match ((string) Setting::get($companyId, 'date_format', 'DD/MM/YYYY')) {
            'MM/DD/YYYY' => 'm-d-Y',
            'YYYY-MM-DD' => 'Y-m-d',
            'DD-MM-YYYY' => 'd-m-Y',
            'DD.MM.YYYY' => 'd.m.Y',
            default => 'd/m/Y',
        };
    }

    private function resolveBankAccount(int $companyId, int $accountId): BankAccount
    {
        $account = Account::withoutGlobalScopes()
            ->where('company_id', $companyId)
            ->where('id', $accountId)
            ->postable()
            ->where('is_bank', true)
            ->where('is_cash', false)
            ->firstOrFail();

        return BankAccount::forAccount($companyId, $account);
    }

    private function bookBalance(int $companyId, int $accountId, string $asOfDate): float
    {
        return (float) LedgerEntry::withoutGlobalScopes()
            ->where('company_id', $companyId)
            ->where('account_id', $accountId)
            ->whereDate('entry_date', '<=', $asOfDate)
            ->selectRaw('COALESCE(SUM(debit),0) - COALESCE(SUM(credit),0) as net')
            ->value('net');
    }

    private function clearedEntryIds(BankReconciliation $reco): array
    {
        return BankReconciliationItem::withoutGlobalScopes()
            ->where('bank_reconciliation_id', $reco->id)
            ->pluck('ledger_entry_id')
            ->map(fn ($id) => (int) $id)
            ->all();
    }

    private function syncClearedItems(int $companyId, BankReconciliation $reco, array $entryIds): void
    {
        $accountId = (int) $reco->bankAccount()->value('account_id');
        $validIds = LedgerEntry::withoutGlobalScopes()
            ->where('company_id', $companyId)
            ->where('account_id', $accountId)
            ->whereIn('id', $entryIds)
            ->pluck('id')
            ->map(fn ($id) => (int) $id)
            ->all();

        BankReconciliationItem::withoutGlobalScopes()
            ->where('bank_reconciliation_id', $reco->id)
            ->whereNotIn('ledger_entry_id', $validIds)
            ->delete();

        foreach ($validIds as $entryId) {
            BankReconciliationItem::withoutGlobalScopes()->firstOrCreate([
                'company_id' => $companyId,
                'bank_reconciliation_id' => $reco->id,
                'ledger_entry_id' => $entryId,
            ]);
        }
    }

    private function statementLines(int $companyId, BankReconciliation $reco): Collection
    {
        return BankStatementLine::withoutGlobalScopes()
            ->where('company_id', $companyId)
            ->where('bank_reconciliation_id', $reco->id)
            ->orderBy('transaction_date')
            ->orderBy('id')
            ->get()
            ->map(function (BankStatementLine $line) use ($companyId) {
                return [
                    'id' => $line->id,
                    'date' => $line->transaction_date?->format($this->dateFormat($companyId)) ?? '—',
                    'description' => $line->description ?: '—',
                    'reference' => $line->reference ?: '—',
                    'deposit' => (float) $line->debit > 0 ? $this->formatAmount((float) $line->debit, $companyId) : '',
                    'payment' => (float) $line->credit > 0 ? $this->formatAmount((float) $line->credit, $companyId) : '',
                    'is_matched' => $line->isMatched(),
                    'matched_entry_id' => $line->matched_ledger_entry_id,
                ];
            });
    }

    private function presentTransaction(LedgerEntry $entry, int $companyId, int $accountId, array $clearedIds): array
    {
        $voucher = $entry->voucher;
        $deposit = (float) $entry->debit;
        $payment = (float) $entry->credit;
        $payee = '—';

        if ($voucher) {
            $payee = $this->chequePrint->presentVoucher($voucher, $companyId)['payee'];
        }

        $memo = trim((string) ($entry->narration ?: $voucher?->narration ?: $voucher?->reference ?: ''));
        $cheque = $voucher ? $this->chequeDetailsForAccount($voucher, $accountId, $companyId) : null;

        return [
            'id' => $entry->id,
            'date' => $entry->entry_date?->format($this->dateFormat($companyId)) ?? '—',
            'date_raw' => $entry->entry_date?->toDateString(),
            'voucher_number' => $voucher?->voucher_number ?? '—',
            'voucher_type' => $voucher?->voucher_type,
            'type_label' => $voucher?->getTypeLabel() ?? '—',
            'voucher_id' => $voucher?->id,
            'payee' => $payee,
            'memo' => $memo,
            'cheque_no' => $cheque['cheque_no'] ?? '—',
            'cheque_date' => $cheque['cheque_date'] ?? '—',
            'deposit' => $deposit > 0 ? $this->formatAmount($deposit, $companyId) : '',
            'payment' => $payment > 0 ? $this->formatAmount($payment, $companyId) : '',
            'deposit_raw' => $deposit,
            'payment_raw' => $payment,
            'is_cleared' => in_array($entry->id, $clearedIds, true),
        ];
    }

    /** @return array{cheque_no: string, cheque_date: string}|null */
    private function chequeDetailsForAccount(Voucher $voucher, int $accountId, int $companyId): ?array
    {
        $extra = $voucher->extra_data ?? [];
        $chequeNo = trim((string) ($extra['cheque_no'] ?? ''));

        if ($chequeNo === '' || !$this->voucherUsesBankAccount($voucher, $accountId)) {
            return null;
        }

        $dateFormat = $this->dateFormat($companyId);
        $chequeDate = !empty($extra['cheque_date'])
            ? Carbon::parse($extra['cheque_date'])
            : $voucher->voucher_date;

        return [
            'cheque_no' => $chequeNo,
            'cheque_date' => $chequeDate?->format($dateFormat) ?? '—',
        ];
    }

    private function voucherUsesBankAccount(Voucher $voucher, int $accountId): bool
    {
        foreach ($voucher->lines as $line) {
            if ((int) $line->account_id !== $accountId) {
                continue;
            }

            $account = $line->account;
            if (!$account?->is_bank || $account->is_cash) {
                continue;
            }

            if (in_array($voucher->voucher_type, ['bank_payment', 'contra'], true) && $line->dr_cr === 'cr') {
                return true;
            }

            if (in_array($voucher->voucher_type, ['bank_receipt', 'contra'], true) && $line->dr_cr === 'dr') {
                return true;
            }
        }

        return false;
    }

    private function applyPartyFilter(Builder $query, array $parties): void
    {
        $partyFilters = collect($parties)
            ->map(function (string $value) {
                if (!str_contains($value, ':')) {
                    return null;
                }

                [$partyType, $partyId] = explode(':', $value, 2);

                return [
                    'party_type' => $partyType,
                    'party_id' => (int) $partyId,
                ];
            })
            ->filter()
            ->values();

        if ($partyFilters->isEmpty()) {
            return;
        }

        $query->where(function (Builder $builder) use ($partyFilters) {
            foreach ($partyFilters as $party) {
                $builder->orWhere(function (Builder $partyQuery) use ($party) {
                    $partyQuery->where('party_type', $party['party_type'])
                        ->where('party_id', $party['party_id'])
                        ->orWhereHas('lines', function (Builder $lineQuery) use ($party) {
                            $lineQuery->where('party_type', $party['party_type'])
                                ->where('party_id', $party['party_id']);
                        });
                });
            }
        });
    }

    private function findMatchForStatementLine(BankStatementLine $line, Collection $transactions, array $clearedIds): ?array
    {
        $lineDate = $line->transaction_date?->toDateString();
        $amount = (float) $line->debit > 0 ? (float) $line->debit : (float) $line->credit;
        $isDeposit = (float) $line->debit > 0;

        foreach ($transactions as $transaction) {
            if (in_array($transaction['id'], $clearedIds, true)) {
                continue;
            }

            $txnAmount = $isDeposit ? $transaction['deposit_raw'] : $transaction['payment_raw'];
            if (abs($txnAmount - $amount) > 0.009) {
                continue;
            }

            if ($line->reference && strcasecmp($line->reference, (string) $transaction['voucher_number']) === 0) {
                return $transaction;
            }

            if ($lineDate && !empty($transaction['date_raw'])) {
                if (abs(Carbon::parse($lineDate)->diffInDays($transaction['date_raw'])) <= 3) {
                    return $transaction;
                }
            }
        }

        return null;
    }

    /** @return array<int, array{transaction_date:string,description:?string,reference:?string,debit:float,credit:float,balance:?float}> */
    private function parseStatementFile(UploadedFile $file): array
    {
        $extension = strtolower($file->getClientOriginalExtension());

        if (in_array($extension, ['csv', 'txt'], true)) {
            return $this->parseCsvStatement($file->getRealPath());
        }

        if (in_array($extension, ['xlsx', 'xls'], true)) {
            return $this->parseExcelStatement($file);
        }

        throw ValidationException::withMessages([
            'statement_file' => 'Upload a CSV or Excel file (.csv, .xlsx, .xls).',
        ]);
    }

    private function parseCsvStatement(string $path): array
    {
        $handle = fopen($path, 'r');
        if ($handle === false) {
            return [];
        }

        $header = null;
        $rows = [];

        while (($data = fgetcsv($handle)) !== false) {
            if ($header === null) {
                $header = $this->normalizeStatementHeader($data);
                continue;
            }

            $row = $this->mapStatementRow($header, $data);
            if ($row) {
                $rows[] = $row;
            }
        }

        fclose($handle);

        return $rows;
    }

    private function parseExcelStatement(UploadedFile $file): array
    {
        $sheet = Excel::toArray(null, $file);
        $rows = [];
        $header = null;

        foreach ($sheet[0] ?? [] as $index => $data) {
            if ($index === 0) {
                $header = $this->normalizeStatementHeader($data);
                continue;
            }

            $row = $this->mapStatementRow($header, $data);
            if ($row) {
                $rows[] = $row;
            }
        }

        return $rows;
    }

    private function normalizeStatementHeader(array $cells): array
    {
        return array_map(function ($cell) {
            $key = strtolower(trim((string) $cell));
            $key = str_replace([' ', '-', '/'], '_', $key);

            return match ($key) {
                'transaction_date', 'txn_date', 'posting_date' => 'date',
                'details', 'narration', 'memo', 'particulars' => 'description',
                'ref', 'cheque_no', 'cheque_number', 'voucher', 'voucher_no', 'voucher_number' => 'reference',
                'withdrawal', 'withdrawals', 'payments', 'payment', 'money_out' => 'credit',
                'deposit', 'deposits', 'money_in' => 'debit',
                'amount', 'value' => 'amount',
                default => $key,
            };
        }, $cells);
    }

    private function mapStatementRow(array $header, array $cells): ?array
    {
        $mapped = [];
        foreach ($header as $index => $key) {
            $mapped[$key] = $cells[$index] ?? null;
        }

        $date = $this->parseStatementDate($mapped['date'] ?? null);
        if (!$date) {
            return null;
        }

        $debit = $this->parseAmount($mapped['debit'] ?? null);
        $credit = $this->parseAmount($mapped['credit'] ?? null);

        if ($debit <= 0 && $credit <= 0 && isset($mapped['amount'])) {
            $amount = $this->parseAmount($mapped['amount']);
            if ($amount < 0) {
                $credit = abs($amount);
            } else {
                $debit = $amount;
            }
        }

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

        return [
            'transaction_date' => $date,
            'description' => trim((string) ($mapped['description'] ?? '')) ?: null,
            'reference' => trim((string) ($mapped['reference'] ?? '')) ?: null,
            'debit' => $debit,
            'credit' => $credit,
            'balance' => $this->parseAmount($mapped['balance'] ?? null) ?: null,
        ];
    }

    private function parseStatementDate(mixed $value): ?string
    {
        $value = trim((string) $value);
        if ($value === '') {
            return null;
        }

        foreach (['Y-m-d', 'd/m/Y', 'd-m-Y', 'm/d/Y', 'd.m.Y'] as $format) {
            try {
                return Carbon::createFromFormat($format, $value)->toDateString();
            } catch (\Throwable) {
            }
        }

        try {
            return Carbon::parse($value)->toDateString();
        } catch (\Throwable) {
            return null;
        }
    }

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

        $normalized = preg_replace('/[^0-9.\-]/', '', (string) $value) ?? '0';

        return (float) $normalized;
    }

    private function emptyFilters(BankReconciliation $reco): array
    {
        return [
            'date_from' => '',
            'date_to' => $reco->statement_date->toDateString(),
            'voucher_type' => '',
            'parties' => [],
            'search' => '',
        ];
    }

    private function assertCompany(int $companyId, BankReconciliation $reco): void
    {
        if ($reco->company_id !== $companyId) {
            abort(403);
        }
    }

    private function assertInProgress(BankReconciliation $reco): void
    {
        if (!$reco->isInProgress()) {
            throw ValidationException::withMessages([
                'reconciliation' => 'This reconciliation is already completed.',
            ]);
        }
    }
}
