<?php

namespace App\Services;

use App\Helpers\NumberHelper;
use App\Models\Account;
use App\Models\ChequeBook;
use App\Models\Setting;
use App\Models\Voucher;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Http\Request;
use Illuminate\Pagination\LengthAwarePaginator;
use Illuminate\Support\Carbon;
use Illuminate\Support\Collection;

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

    public function parseFilters(Request $request): array
    {
        $columns = array_values(array_filter(
            (array) $request->input('columns', []),
            fn ($column) => isset($this->columnOptions()[$column]),
        ));

        if ($columns === []) {
            $columns = $this->defaultColumns();
        } else {
            $columns = array_values(array_intersect(array_keys($this->columnOptions()), $columns));
        }

        return [
            'date_from' => $request->input('date_from', ''),
            'date_to' => $request->input('date_to', ''),
            'bank' => trim((string) $request->input('bank', '')),
            'bank_account_no' => trim((string) $request->input('bank_account_no', '')),
            'branch' => trim((string) $request->input('branch', '')),
            'book_no' => trim((string) $request->input('book_no', '')),
            'parties' => array_values(array_filter((array) $request->input('parties', []))),
            'search' => trim((string) $request->input('search', '')),
            'columns' => $columns,
            'sort' => $this->normalizeSort((string) $request->input('sort', '')),
            'status' => in_array($request->input('status'), ['issued', 'void'], true)
                ? $request->input('status')
                : '',
        ];
    }

    public function columnOptions(): array
    {
        return [
            'cheque_date' => 'Cheque Date',
            'bank' => 'Bank',
            'account_no' => 'Account No.',
            'book_no' => 'Book No.',
            'cheque_no' => 'Cheque No.',
            'voucher_number' => 'Voucher No',
            'payee' => 'Payee',
            'amount' => 'Amount',
            'status' => 'Status',
            'reference' => 'Reference',
            'narration' => 'Memo',
        ];
    }

    public function defaultColumns(): array
    {
        return array_values(array_diff(array_keys($this->columnOptions()), ['reference', 'narration']));
    }

    public function sortOptions(): array
    {
        return [
            '' => 'Default order',
            'cheque_date_asc' => 'Cheque Date (ascending)',
            'cheque_date_desc' => 'Cheque Date (descending)',
            'cheque_no_asc' => 'Cheque No. (ascending)',
            'cheque_no_desc' => 'Cheque No. (descending)',
            'voucher_number_asc' => 'Voucher No (ascending)',
            'voucher_number_desc' => 'Voucher No (descending)',
            'payee_asc' => 'Payee (ascending)',
            'payee_desc' => 'Payee (descending)',
            'bank_asc' => 'Bank (ascending)',
            'bank_desc' => 'Bank (descending)',
            'amount_asc' => 'Amount (ascending)',
            'amount_desc' => 'Amount (descending)',
            'status_asc' => 'Status (ascending)',
            'status_desc' => 'Status (descending)',
            'reference_asc' => 'Reference (ascending)',
            'reference_desc' => 'Reference (descending)',
        ];
    }

    public function selectedColumnsLabel(array $filters): string
    {
        $count = count($filters['columns'] ?? []);

        if ($count === 0 || $count === count($this->defaultColumns())) {
            return 'All columns';
        }

        return $count . ' selected';
    }

    public function filterOptions(int $companyId): array
    {
        $books = ChequeBook::withoutGlobalScopes()
            ->where('company_id', $companyId)
            ->orderBy('bank')
            ->orderBy('book_no')
            ->get(['bank', 'branch', 'short_account_no', 'book_no']);

        return [
            'banks' => $books->pluck('bank')->filter()->unique()->sort()->values()->all(),
            'branches' => $books->pluck('branch')->filter()->unique()->sort()->values()->all(),
            'account_nos' => $books->pluck('short_account_no')->filter()->unique()->sort()->values()->all(),
            'book_nos' => $books->pluck('book_no')->filter()->unique()->sort()->values()->all(),
            'statuses' => [
                '' => 'All statuses',
                'issued' => 'Issued',
                'void' => 'Void',
            ],
            'partyTree' => $this->ledgerReports->profitLossPartyTree($companyId),
            'columns' => $this->columnOptions(),
            'sortOptions' => $this->sortOptions(),
        ];
    }

    public function paginate(int $companyId, array $filters, int $perPage = 25): LengthAwarePaginator
    {
        $query = $this->filteredQuery($companyId, $filters)
            ->with(['lines.account:id,code,name,is_bank,is_cash']);

        $vouchers = $query->paginate($perPage)->withQueryString();
        $books = $this->booksById($companyId, $vouchers->getCollection());

        return $vouchers->through(function (Voucher $voucher, int $index) use ($companyId, $books, $vouchers) {
            return $this->presentRow($voucher, $companyId, $books, $vouchers->firstItem() + $index);
        });
    }

    public function list(int $companyId, array $filters): Collection
    {
        $vouchers = $this->filteredQuery($companyId, $filters)
            ->with(['lines.account:id,code,name,is_bank,is_cash'])
            ->get();

        $books = $this->booksById($companyId, $vouchers);

        return $vouchers->values()->map(
            fn (Voucher $voucher, int $index) => $this->presentRow($voucher, $companyId, $books, $index + 1),
        );
    }

    public function filteredQuery(int $companyId, array $filters): Builder
    {
        $query = Voucher::withoutGlobalScopes()
            ->where('company_id', $companyId)
            ->whereIn('voucher_type', ['bank_payment', 'contra'])
            ->where('status', 'posted')
            ->where(function (Builder $builder) {
                $builder->whereNotNull('extra_data->cheque_no')
                    ->whereRaw("TRIM(JSON_UNQUOTE(JSON_EXTRACT(extra_data, '$.cheque_no'))) <> ''");
            });

        if (!empty($filters['date_from'])) {
            $query->whereRaw(
                "DATE(COALESCE(JSON_UNQUOTE(JSON_EXTRACT(extra_data, '$.cheque_date')), voucher_date)) >= ?",
                [$filters['date_from']],
            );
        }

        if (!empty($filters['date_to'])) {
            $query->whereRaw(
                "DATE(COALESCE(JSON_UNQUOTE(JSON_EXTRACT(extra_data, '$.cheque_date')), voucher_date)) <= ?",
                [$filters['date_to']],
            );
        }

        if ($filters['status'] === 'void') {
            $query->where('extra_data->cheque_status', 'void');
        } elseif ($filters['status'] === 'issued') {
            $query->where(function (Builder $builder) {
                $builder->whereNull('extra_data->cheque_status')
                    ->orWhere('extra_data->cheque_status', '!=', 'void');
            });
        }

        if ($filters['bank'] !== '') {
            $bookIds = ChequeBook::withoutGlobalScopes()
                ->where('company_id', $companyId)
                ->where('bank', $filters['bank'])
                ->pluck('id');

            $query->where(function (Builder $builder) use ($bookIds, $filters) {
                $builder->whereIn('extra_data->cheque_book_id', $bookIds->map(fn ($id) => (string) $id))
                    ->orWhereRaw(
                        "JSON_UNQUOTE(JSON_EXTRACT(extra_data, '$.cheque_bank')) = ?",
                        [$filters['bank']],
                    );
            });
        }

        if ($filters['branch'] !== '') {
            $bookIds = ChequeBook::withoutGlobalScopes()
                ->where('company_id', $companyId)
                ->where('branch', $filters['branch'])
                ->pluck('id');

            $query->whereIn('extra_data->cheque_book_id', $bookIds->map(fn ($id) => (string) $id));
        }

        if ($filters['bank_account_no'] !== '') {
            $bookIds = ChequeBook::withoutGlobalScopes()
                ->where('company_id', $companyId)
                ->where('short_account_no', $filters['bank_account_no'])
                ->pluck('id');

            $query->whereIn('extra_data->cheque_book_id', $bookIds->map(fn ($id) => (string) $id));
        }

        if ($filters['book_no'] !== '') {
            $bookIds = ChequeBook::withoutGlobalScopes()
                ->where('company_id', $companyId)
                ->where('book_no', $filters['book_no'])
                ->pluck('id');

            $query->whereIn('extra_data->cheque_book_id', $bookIds->map(fn ($id) => (string) $id));
        }

        if (($filters['parties'] ?? []) !== []) {
            $partyFilters = collect($filters['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->isNotEmpty()) {
                $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']);
                                });
                        });
                    }
                });
            }
        }

        if (($filters['search'] ?? '') !== '') {
            $like = '%' . $filters['search'] . '%';
            $query->where(function (Builder $builder) use ($like) {
                $builder->where('voucher_number', 'like', $like)
                    ->orWhere('reference', 'like', $like)
                    ->orWhere('narration', 'like', $like)
                    ->orWhereRaw("JSON_UNQUOTE(JSON_EXTRACT(extra_data, '$.cheque_no')) LIKE ?", [$like])
                    ->orWhereRaw("JSON_UNQUOTE(JSON_EXTRACT(extra_data, '$.cheque_bank')) LIKE ?", [$like]);
            });
        }

        $this->applySort($query, $filters['sort'] ?? '');

        return $query;
    }

    private function applySort(Builder $query, string $sort): void
    {
        $computedSorts = [
            'payee_asc', 'payee_desc',
            'bank_asc', 'bank_desc',
        ];

        if (in_array($sort, $computedSorts, true)) {
            $this->applyDefaultSort($query);

            return;
        }

        match ($sort) {
            'cheque_date_asc' => $query->orderByRaw("COALESCE(JSON_UNQUOTE(JSON_EXTRACT(extra_data, '$.cheque_date')), voucher_date) ASC")->orderBy('id'),
            'cheque_date_desc' => $query->orderByRaw("COALESCE(JSON_UNQUOTE(JSON_EXTRACT(extra_data, '$.cheque_date')), voucher_date) DESC")->orderByDesc('id'),
            'cheque_no_asc' => $query->orderByRaw("JSON_UNQUOTE(JSON_EXTRACT(extra_data, '$.cheque_no')) ASC")->orderBy('id'),
            'cheque_no_desc' => $query->orderByRaw("JSON_UNQUOTE(JSON_EXTRACT(extra_data, '$.cheque_no')) DESC")->orderByDesc('id'),
            'voucher_number_asc' => $query->orderBy('voucher_number')->orderBy('id'),
            'voucher_number_desc' => $query->orderByDesc('voucher_number')->orderByDesc('id'),
            'amount_asc' => $query->orderBy('grand_total')->orderBy('id'),
            'amount_desc' => $query->orderByDesc('grand_total')->orderByDesc('id'),
            'status_asc' => $query->orderByRaw("COALESCE(JSON_UNQUOTE(JSON_EXTRACT(extra_data, '$.cheque_status')), 'issued') ASC")->orderBy('id'),
            'status_desc' => $query->orderByRaw("COALESCE(JSON_UNQUOTE(JSON_EXTRACT(extra_data, '$.cheque_status')), 'issued') DESC")->orderByDesc('id'),
            'reference_asc' => $query->orderBy('reference')->orderBy('id'),
            'reference_desc' => $query->orderByDesc('reference')->orderByDesc('id'),
            default => $this->applyDefaultSort($query),
        };
    }

    private function applyDefaultSort(Builder $query): void
    {
        $query->orderByRaw("COALESCE(JSON_UNQUOTE(JSON_EXTRACT(extra_data, '$.cheque_date')), voucher_date) DESC")
            ->orderByDesc('id');
    }

    private function normalizeSort(string $sort): string
    {
        return array_key_exists($sort, $this->sortOptions()) ? $sort : '';
    }

    public function presentRow(Voucher $voucher, int $companyId, Collection $booksById, int $serial): array
    {
        $extra = $voucher->extra_data ?? [];
        $book = !empty($extra['cheque_book_id']) ? $booksById->get((int) $extra['cheque_book_id']) : null;
        $bankAccount = $this->bankCreditAccount($voucher);
        $payee = $this->chequePrint->presentVoucher($voucher, $companyId)['payee'];
        $amount = (float) ($voucher->grand_total ?: $voucher->total_amount ?: 0);
        $isVoid = ($extra['cheque_status'] ?? '') === 'void';
        $chequeDate = !empty($extra['cheque_date'])
            ? Carbon::parse($extra['cheque_date'])
            : $voucher->voucher_date;

        $bank = $book?->bank ?: ($extra['cheque_bank'] ?? ($bankAccount?->name ?? '—'));
        $branch = $book?->branch ?: ($extra['cheque_branch'] ?? '—');
        $accountNo = $book?->short_account_no ?: ($bankAccount?->code ?? '—');
        $accountLabel = trim($accountNo . ($branch && $branch !== '—' ? " ({$branch})" : ''));
        $bookNo = $book
            ? trim($book->bank . ' # ' . $book->book_no)
            : ($extra['cheque_book_label'] ?? '—');

        $cells = [
            'cheque_date' => $chequeDate?->format($this->dateFormat($companyId)) ?? '—',
            'bank' => $bank ?: '—',
            'account_no' => $accountLabel ?: '—',
            'book_no' => $bookNo,
            'cheque_no' => $extra['cheque_no'] ?? '—',
            'voucher_number' => $voucher->voucher_number ?? '—',
            'payee' => $payee,
            'amount' => NumberHelper::formatAmount($amount, $companyId),
            'status' => $isVoid ? 'Void' : 'Issued',
            'reference' => $voucher->reference ?: '—',
            'narration' => $voucher->narration ?: '—',
        ];

        return [
            'serial' => $serial,
            'id' => $voucher->id,
            'voucher_type' => $voucher->voucher_type,
            'voucher_number' => $voucher->voucher_number ?? '—',
            'cheque_date' => $cells['cheque_date'],
            'bank' => $cells['bank'],
            'account_no' => $cells['account_no'],
            'book_no' => $cells['book_no'],
            'cheque_no' => $cells['cheque_no'],
            'payee' => $cells['payee'],
            'amount' => $cells['amount'],
            'amount_raw' => $amount,
            'status' => $isVoid ? 'void' : 'issued',
            'status_label' => $cells['status'],
            'can_void' => !$isVoid,
            'can_print' => !$isVoid,
            'cells' => $cells,
        ];
    }

    public function filterSummary(array $filters): string
    {
        $parts = [];

        if (!empty($filters['date_from']) || !empty($filters['date_to'])) {
            $from = $filters['date_from'] ?: '…';
            $to = $filters['date_to'] ?: '…';
            $parts[] = "Date: {$from} to {$to}";
        }

        foreach ([
            'bank' => 'Bank',
            'bank_account_no' => 'Account No',
            'branch' => 'Branch',
            'book_no' => 'Book No',
            'status' => 'Status',
        ] as $key => $label) {
            if (!empty($filters[$key])) {
                $value = $filters[$key] === 'issued' ? 'Issued' : ($filters[$key] === 'void' ? 'Void' : $filters[$key]);
                $parts[] = "{$label}: {$value}";
            }
        }

        if (($filters['parties'] ?? []) !== []) {
            $parts[] = 'Payee: ' . $this->selectedPartyLabel($filters);
        }

        if (!empty($filters['search'])) {
            $parts[] = 'Search: ' . $filters['search'];
        }

        if (($filters['columns'] ?? []) !== [] && count($filters['columns']) !== count($this->defaultColumns())) {
            $parts[] = $this->selectedColumnsLabel($filters);
        }

        if (!empty($filters['sort'])) {
            $parts[] = $this->sortOptions()[$filters['sort']] ?? $filters['sort'];
        }

        return $parts === [] ? 'All issued and void cheques' : implode(' | ', $parts);
    }

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

    private function booksById(int $companyId, Collection $vouchers): Collection
    {
        $bookIds = $vouchers
            ->map(fn (Voucher $voucher) => (int) (($voucher->extra_data ?? [])['cheque_book_id'] ?? 0))
            ->filter()
            ->unique()
            ->values();

        if ($bookIds->isEmpty()) {
            return collect();
        }

        return ChequeBook::withoutGlobalScopes()
            ->where('company_id', $companyId)
            ->whereIn('id', $bookIds)
            ->get()
            ->keyBy('id');
    }

    private function bankCreditAccount(Voucher $voucher): ?Account
    {
        foreach ($voucher->lines as $line) {
            $account = $line->account;
            if ($account && $account->is_bank && !$account->is_cash && $line->dr_cr === 'cr') {
                return $account;
            }
        }

        return null;
    }

    private 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',
        };
    }
}
