<?php

namespace App\Services;

use App\Models\{Account, Dimension, DimensionValue, FiscalYear, LedgerEntry, Setting, VoucherLine};
use App\Services\VoucherNumberService;
use Illuminate\Support\Carbon;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;

class LedgerReportService
{
    public function defaultDateRange(int $companyId, ?int $fiscalYearId = null): array
    {
        $fiscalYear = FiscalYear::withoutGlobalScopes()
            ->where('company_id', $companyId)
            ->when($fiscalYearId, fn ($query) => $query->where('id', $fiscalYearId))
            ->when(!$fiscalYearId, fn ($query) => $query->where('is_current', true))
            ->orderByDesc('is_current')
            ->orderByDesc('start_date')
            ->first();

        if (!$fiscalYear && !$fiscalYearId) {
            try {
                $fiscalYear = app(FiscalYearService::class)->current($companyId);
            } catch (\RuntimeException) {
                $fiscalYear = null;
            }
        }

        return [
            'fiscal_year' => $fiscalYear,
            'date_from' => optional($fiscalYear?->start_date)->format('Y-m-d') ?? now()->startOfYear()->format('Y-m-d'),
            'date_to' => optional($fiscalYear?->end_date)->format('Y-m-d') ?? now()->format('Y-m-d'),
        ];
    }

    public function fiscalYears(int $companyId): Collection
    {
        return FiscalYear::withoutGlobalScopes()
            ->where('company_id', $companyId)
            ->orderByDesc('is_current')
            ->orderByDesc('start_date')
            ->get(['id', 'name', 'start_date', 'end_date', 'is_current', 'status']);
    }

    public function accounts(int $companyId): Collection
    {
        return Account::withoutGlobalScopes()
            ->where('company_id', $companyId)
            ->whereNull('deleted_at')
            ->where('is_group', false)
            ->orderBy('code')
            ->orderBy('name')
            ->get(['id', 'code', 'name', 'type']);
    }

    public function trialBalance(int $companyId, string $dateFrom, string $dateTo, bool $includeZero = false): array
    {
        $dateFrom = Carbon::parse($dateFrom)->toDateString();
        $dateTo = Carbon::parse($dateTo)->toDateString();

        $rows = DB::table('accounts as a')
            ->leftJoin('ledger_entries as le', function ($join) use ($companyId, $dateTo) {
                $join->on('le.account_id', '=', 'a.id')
                    ->where('le.company_id', '=', $companyId)
                    ->whereDate('le.entry_date', '<=', $dateTo);
            })
            ->where('a.company_id', $companyId)
            ->whereNull('a.deleted_at')
            ->where('a.is_group', false)
            ->select([
                'a.id',
                'a.code',
                'a.name',
                'a.type',
            ])
            ->selectRaw('COALESCE(SUM(CASE WHEN le.entry_date < ? THEN le.debit ELSE 0 END), 0) as opening_debit_raw', [$dateFrom])
            ->selectRaw('COALESCE(SUM(CASE WHEN le.entry_date < ? THEN le.credit ELSE 0 END), 0) as opening_credit_raw', [$dateFrom])
            ->selectRaw('COALESCE(SUM(CASE WHEN le.entry_date BETWEEN ? AND ? THEN le.debit ELSE 0 END), 0) as period_debit', [$dateFrom, $dateTo])
            ->selectRaw('COALESCE(SUM(CASE WHEN le.entry_date BETWEEN ? AND ? THEN le.credit ELSE 0 END), 0) as period_credit', [$dateFrom, $dateTo])
            ->groupBy('a.id', 'a.code', 'a.name', 'a.type')
            ->orderBy('a.code')
            ->orderBy('a.name')
            ->get()
            ->map(function ($row) {
                $openingNet = (float) $row->opening_debit_raw - (float) $row->opening_credit_raw;
                $periodDebit = (float) $row->period_debit;
                $periodCredit = (float) $row->period_credit;
                $closingNet = $openingNet + $periodDebit - $periodCredit;

                $row->opening_debit = max($openingNet, 0);
                $row->opening_credit = max(-$openingNet, 0);
                $row->period_debit = $periodDebit;
                $row->period_credit = $periodCredit;
                $row->closing_debit = max($closingNet, 0);
                $row->closing_credit = max(-$closingNet, 0);

                return $row;
            })
            ->filter(function ($row) use ($includeZero) {
                if ($includeZero) {
                    return true;
                }

                return abs($row->opening_debit)
                    + abs($row->opening_credit)
                    + abs($row->period_debit)
                    + abs($row->period_credit)
                    + abs($row->closing_debit)
                    + abs($row->closing_credit) > 0.009;
            })
            ->values();

        $totals = [
            'opening_debit' => $rows->sum('opening_debit'),
            'opening_credit' => $rows->sum('opening_credit'),
            'period_debit' => $rows->sum('period_debit'),
            'period_credit' => $rows->sum('period_credit'),
            'closing_debit' => $rows->sum('closing_debit'),
            'closing_credit' => $rows->sum('closing_credit'),
        ];

        return compact('rows', 'totals');
    }

    public function generalLedgerFilterOptions(int $companyId): array
    {
        return [
            'accountTypes' => $this->coaAccountTypeOptions(),
            'accountTree' => \App\Support\AccountPickerTreeBuilder::buildForGeneralLedger($companyId),
            'voucherTypes' => VoucherNumberService::voucherTypes(),
            'dimensionTree' => $this->profitLossDimensionTree($companyId),
        ];
    }

    private function coaAccountTypeOptions(): array
    {
        return collect(\App\Support\QuickBooksChartOfAccounts::sections())
            ->mapWithKeys(fn (array $meta, string $key) => [$key => $meta['label']])
            ->all();
    }

    private function accountIdsForCoaSections(int $companyId, array $sections): array
    {
        if ($sections === []) {
            return [];
        }

        return Account::withoutGlobalScopes()
            ->where('company_id', $companyId)
            ->whereNull('deleted_at')
            ->where('is_group', false)
            ->get(['id', 'type', 'sub_type', 'detail_type', 'is_bank', 'is_cash'])
            ->filter(fn (Account $account) => in_array(
                \App\Support\QuickBooksChartOfAccounts::sectionForAccount($account),
                $sections,
                true,
            ))
            ->pluck('id')
            ->all();
    }

    public function generalLedger(int $companyId, int|string $accountId, string $dateFrom, string $dateTo, array $filters = []): array
    {
        if ($accountId === 'all') {
            return $this->generalLedgerAll($companyId, $dateFrom, $dateTo, $filters);
        }

        $account = Account::withoutGlobalScopes()
            ->where('company_id', $companyId)
            ->whereNull('deleted_at')
            ->findOrFail((int) $accountId);

        $dateFrom = Carbon::parse($dateFrom)->toDateString();
        $dateTo = Carbon::parse($dateTo)->toDateString();

        if (!$this->accountMatchesGeneralLedgerSectionFilter($account, $filters)) {
            return $this->emptySingleLedger($account);
        }

        $openingNet = $this->openingBalanceForAccount($companyId, $account->id, $dateFrom);

        $entries = $this->generalLedgerEntryQuery($companyId, $dateFrom, $dateTo, $account->id, $filters)
            ->get();

        $this->enrichGeneralLedgerEntries($entries, $companyId);

        $running = $openingNet;
        $entries->each(function (LedgerEntry $entry) use (&$running) {
            $running += (float) $entry->debit - (float) $entry->credit;
            $entry->running_balance = $running;
            $entry->balance_side = $running >= 0 ? 'Dr' : 'Cr';
        });

        $totals = [
            'opening_debit' => max($openingNet, 0),
            'opening_credit' => max(-$openingNet, 0),
            'period_debit' => $entries->sum(fn ($entry) => (float) $entry->debit),
            'period_credit' => $entries->sum(fn ($entry) => (float) $entry->credit),
            'closing_debit' => max($running, 0),
            'closing_credit' => max(-$running, 0),
        ];

        return [
            'mode' => 'single',
            'account' => $account,
            'groups' => [],
            'entries' => $entries,
            'totals' => $totals,
        ];
    }

    public function generalLedgerAll(int $companyId, string $dateFrom, string $dateTo, array $filters = []): array
    {
        return $this->generalLedgerGrouped($companyId, $dateFrom, $dateTo, $filters, null);
    }

    public function generalLedgerForAccounts(int $companyId, array $accountIds, string $dateFrom, string $dateTo, array $filters = []): array
    {
        $accountIds = array_values(array_unique(array_filter(array_map('intval', $accountIds))));

        return $this->generalLedgerGrouped($companyId, $dateFrom, $dateTo, $filters, $accountIds);
    }

    private function generalLedgerGrouped(int $companyId, string $dateFrom, string $dateTo, array $filters, ?array $restrictAccountIds): array
    {
        $dateFrom = Carbon::parse($dateFrom)->toDateString();
        $dateTo = Carbon::parse($dateTo)->toDateString();

        $accounts = Account::withoutGlobalScopes()
            ->where('company_id', $companyId)
            ->whereNull('deleted_at')
            ->where('is_group', false)
            ->when(!empty($filters['account_sections']), function ($query) use ($companyId, $filters) {
                $ids = $this->accountIdsForCoaSections($companyId, $filters['account_sections']);
                $query->whereIn('id', $ids ?: [-1]);
            })
            ->when($restrictAccountIds !== null, fn ($query) => $query->whereIn('id', $restrictAccountIds ?: [-1]))
            ->orderBy('code')
            ->orderBy('name')
            ->get(['id', 'code', 'name', 'type']);

        $entries = $this->generalLedgerEntryQuery($companyId, $dateFrom, $dateTo, null, $filters)
            ->orderBy('voucher_id')
            ->get();

        $entriesByAccount = $entries->groupBy('account_id');
        $accountIds = $accounts->pluck('id');
        $openingByAccount = $this->openingBalancesForAccounts($companyId, $accountIds->all(), $dateFrom);

        $groups = collect();
        $aggregatePeriodDebit = 0.0;
        $aggregatePeriodCredit = 0.0;

        foreach ($accounts as $account) {
            $accountEntries = ($entriesByAccount->get($account->id) ?? collect())->values();
            $openingNet = (float) ($openingByAccount[$account->id] ?? 0);

            if ($accountEntries->isEmpty() && abs($openingNet) < 0.009) {
                continue;
            }

            $this->enrichGeneralLedgerEntries($accountEntries, $companyId);

            $running = $openingNet;
            $accountEntries->each(function (LedgerEntry $entry) use (&$running) {
                $running += (float) $entry->debit - (float) $entry->credit;
                $entry->running_balance = $running;
                $entry->balance_side = $running >= 0 ? 'Dr' : 'Cr';
            });

            $periodDebit = $accountEntries->sum(fn ($entry) => (float) $entry->debit);
            $periodCredit = $accountEntries->sum(fn ($entry) => (float) $entry->credit);
            $aggregatePeriodDebit += $periodDebit;
            $aggregatePeriodCredit += $periodCredit;

            $groups->push([
                'account' => $account,
                'entries' => $accountEntries,
                'totals' => [
                    'opening_debit' => max($openingNet, 0),
                    'opening_credit' => max(-$openingNet, 0),
                    'period_debit' => $periodDebit,
                    'period_credit' => $periodCredit,
                    'closing_debit' => max($running, 0),
                    'closing_credit' => max(-$running, 0),
                ],
            ]);
        }

        return [
            'mode' => 'all',
            'account' => null,
            'groups' => $groups->all(),
            'entries' => $entries,
            'totals' => [
                'opening_debit' => 0,
                'opening_credit' => 0,
                'period_debit' => $aggregatePeriodDebit,
                'period_credit' => $aggregatePeriodCredit,
                'closing_debit' => 0,
                'closing_credit' => 0,
            ],
        ];
    }

    private function emptySingleLedger(Account $account): array
    {
        return [
            'mode' => 'single',
            'account' => $account,
            'groups' => [],
            'entries' => collect(),
            'totals' => [
                'opening_debit' => 0,
                'opening_credit' => 0,
                'period_debit' => 0,
                'period_credit' => 0,
                'closing_debit' => 0,
                'closing_credit' => 0,
            ],
        ];
    }

    private function accountMatchesGeneralLedgerSectionFilter(Account $account, array $filters): bool
    {
        $sections = $filters['account_sections'] ?? [];

        if ($sections === []) {
            return true;
        }

        return in_array(
            \App\Support\QuickBooksChartOfAccounts::sectionForAccount($account),
            $sections,
            true,
        );
    }

    private function generalLedgerEntryQuery(int $companyId, string $dateFrom, string $dateTo, ?int $accountId, array $filters)
    {
        $query = LedgerEntry::withoutGlobalScopes()
            ->with([
                'account:id,code,name,type,sub_type,detail_type,is_bank,is_cash',
                'voucher:id,voucher_type,voucher_number,voucher_date,status,reference,narration,party_id,party_type',
            ])
            ->where('company_id', $companyId)
            ->whereBetween('entry_date', [$dateFrom, $dateTo])
            ->when($accountId, fn ($builder) => $builder->where('account_id', $accountId));

        $this->applyGeneralLedgerEntryFilters($query, $companyId, $filters);

        return $query->orderBy('entry_date')->orderBy('id');
    }

    private function applyGeneralLedgerEntryFilters($query, int $companyId, array $filters): void
    {
        $sections = $filters['account_sections'] ?? [];
        if ($sections !== []) {
            $accountIds = $this->accountIdsForCoaSections($companyId, $sections);
            $query->whereIn('account_id', $accountIds ?: [-1]);
        }

        $voucherTypes = $filters['voucher_types'] ?? [];
        if ($voucherTypes !== []) {
            $query->whereHas('voucher', fn ($voucherQuery) => $voucherQuery
                ->where('status', 'posted')
                ->whereIn('voucher_type', $voucherTypes));
        }

        $dimensionValueIds = array_values(array_unique(array_filter(array_map(
            'intval',
            $filters['dimension_value_ids'] ?? [],
        ))));

        if ($dimensionValueIds !== []) {
            $query->whereHas('voucherLine', function ($lineQuery) use ($dimensionValueIds) {
                $lineQuery->where(function ($dimensionQuery) use ($dimensionValueIds) {
                    foreach (range(1, 7) as $index) {
                        $dimensionQuery->orWhereIn("dim{$index}_id", $dimensionValueIds);
                    }
                });
            });
        }

        $search = trim((string) ($filters['search'] ?? ''));
        if ($search !== '') {
            $like = '%' . $search . '%';
            $query->where(function ($searchQuery) use ($like) {
                $searchQuery
                    ->where('reference', 'like', $like)
                    ->orWhere('narration', 'like', $like)
                    ->orWhereHas('voucher', function ($voucherQuery) use ($like) {
                        $voucherQuery->where('voucher_number', 'like', $like)
                            ->orWhere('reference', 'like', $like)
                            ->orWhere('narration', 'like', $like);
                    })
                    ->orWhereHas('account', function ($accountQuery) use ($like) {
                        $accountQuery->where('name', 'like', $like)
                            ->orWhere('code', 'like', $like);
                    });
            });
        }
    }

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

    private function openingBalancesForAccounts(int $companyId, array $accountIds, string $dateFrom): array
    {
        if ($accountIds === []) {
            return [];
        }

        return LedgerEntry::withoutGlobalScopes()
            ->where('company_id', $companyId)
            ->whereIn('account_id', $accountIds)
            ->whereDate('entry_date', '<', $dateFrom)
            ->select('account_id')
            ->selectRaw('COALESCE(SUM(debit),0) - COALESCE(SUM(credit),0) as net')
            ->groupBy('account_id')
            ->pluck('net', 'account_id')
            ->map(fn ($net) => (float) $net)
            ->all();
    }

    private function enrichGeneralLedgerEntries(Collection $entries, int $companyId): void
    {
        if ($entries->isEmpty()) {
            return;
        }

        $this->attachSplitAccounts($entries, $companyId);
        $this->attachGeneralLedgerEntryMetadata($entries, $companyId);
    }

    private function attachGeneralLedgerEntryMetadata(Collection $entries, int $companyId): void
    {
        $lineIds = $entries->pluck('voucher_line_id')->filter()->unique()->values();
        $linesById = $lineIds->isEmpty()
            ? collect()
            : VoucherLine::withoutGlobalScopes()
                ->whereIn('id', $lineIds)
                ->get(['id', 'dim1_id', 'dim2_id', 'dim3_id', 'dim4_id', 'dim5_id', 'dim6_id', 'dim7_id'])
                ->keyBy('id');

        $dimensionIds = $linesById
            ->flatMap(function (VoucherLine $line) {
                $ids = [];
                foreach (range(1, 7) as $index) {
                    $value = (int) ($line->{"dim{$index}_id"} ?? 0);
                    if ($value > 0) {
                        $ids[] = $value;
                    }
                }

                return $ids;
            })
            ->unique()
            ->values();

        $dimensionNames = $dimensionIds->isEmpty()
            ? collect()
            : DimensionValue::withoutGlobalScopes()
                ->where('company_id', $companyId)
                ->whereIn('id', $dimensionIds)
                ->pluck('name', 'id');

        $partyNames = $this->partyNames($companyId, $entries);

        $entries->each(function (LedgerEntry $entry) use ($linesById, $dimensionNames, $partyNames) {
            $labels = [];
            $line = $linesById->get($entry->voucher_line_id);

            if ($line) {
                foreach (range(1, 7) as $index) {
                    $dimensionValueId = (int) ($line->{"dim{$index}_id"} ?? 0);
                    if ($dimensionValueId > 0 && $dimensionNames->has($dimensionValueId)) {
                        $labels[$dimensionNames[$dimensionValueId]] = true;
                    }
                }
            }

            $entry->dimension_label = implode(', ', array_keys($labels));

            $partyType = $entry->voucher?->party_type ?: $entry->party_type;
            $partyId = $entry->voucher?->party_id ?: $entry->party_id;
            $entry->party_name = $partyNames[$partyType . ':' . $partyId] ?? '';
        });
    }

    private function attachSplitAccounts(Collection $entries, int $companyId): void
    {
        $voucherIds = $entries->pluck('voucher_id')->filter()->unique()->values();

        if ($voucherIds->isEmpty()) {
            $entries->each(function (LedgerEntry $entry) {
                $entry->split_accounts = '';
            });

            return;
        }

        $siblingsByVoucher = LedgerEntry::withoutGlobalScopes()
            ->with(['account:id,code,name'])
            ->where('company_id', $companyId)
            ->whereIn('voucher_id', $voucherIds)
            ->get()
            ->groupBy('voucher_id');

        $entries->each(function (LedgerEntry $entry) use ($siblingsByVoucher) {
            $labels = ($siblingsByVoucher->get($entry->voucher_id) ?? collect())
                ->filter(fn (LedgerEntry $sibling) => (int) $sibling->account_id !== (int) $entry->account_id)
                ->map(fn (LedgerEntry $sibling) => $this->formatAccountLabel($sibling->account))
                ->filter()
                ->unique()
                ->values();

            $entry->split_accounts = $labels->implode(', ');
        });
    }

    private function formatAccountLabel(?Account $account): ?string
    {
        if (!$account) {
            return null;
        }

        return $account->code
            ? '[' . $account->code . '] ' . $account->name
            : $account->name;
    }


    public function journalReport(int $companyId, string $dateFrom, string $dateTo, ?string $voucherType = null, ?string $search = null): array
    {
        $dateFrom = Carbon::parse($dateFrom)->toDateString();
        $dateTo = Carbon::parse($dateTo)->toDateString();
        $search = trim((string) $search);

        $entries = LedgerEntry::withoutGlobalScopes()
            ->with([
                'account:id,code,name,type',
                'voucher:id,voucher_type,voucher_number,voucher_date,reference,narration,party_id,party_type,status',
            ])
            ->where('company_id', $companyId)
            ->whereBetween('entry_date', [$dateFrom, $dateTo])
            ->whereHas('voucher', function ($query) use ($voucherType) {
                $query->where('status', 'posted');
                if ($voucherType) {
                    $query->where('voucher_type', $voucherType);
                }
            })
            ->when($search !== '', function ($query) use ($search) {
                $query->where(function ($searchQuery) use ($search) {
                    $searchQuery
                        ->whereHas('voucher', function ($voucherQuery) use ($search) {
                            $voucherQuery->where('voucher_number', 'like', "%{$search}%")
                                ->orWhere('reference', 'like', "%{$search}%")
                                ->orWhere('narration', 'like', "%{$search}%");
                        })
                        ->orWhereHas('account', function ($accountQuery) use ($search) {
                            $accountQuery->where('name', 'like', "%{$search}%")
                                ->orWhere('code', 'like', "%{$search}%");
                        });
                });
            })
            ->orderBy('entry_date')
            ->orderBy('voucher_id')
            ->orderBy('id')
            ->get();

        $partyNames = $this->partyNames($companyId, $entries);
        $entries->each(function (LedgerEntry $entry) use ($partyNames) {
            $partyType = $entry->voucher?->party_type ?: $entry->party_type;
            $partyId = $entry->voucher?->party_id ?: $entry->party_id;
            $entry->journal_party_name = $partyNames[$partyType . ':' . $partyId] ?? null;
        });

        $totals = [
            'debit' => $entries->sum(fn ($entry) => (float) $entry->debit),
            'credit' => $entries->sum(fn ($entry) => (float) $entry->credit),
        ];

        return [
            'entries' => $entries,
            'groups' => $entries->groupBy('voucher_id'),
            'totals' => $totals,
        ];
    }

    public function profitLossFilterOptions(int $companyId): array
    {
        $plSections = ['income', 'cost_of_goods_sold', 'expenses', 'other_income', 'other_expense'];
        $accountTypes = collect($plSections)->mapWithKeys(fn (string $key) => [
            $key => $this->sectionLabel($key),
        ])->all();

        $dimensions = DB::table('dimension_values')
            ->where('company_id', $companyId)
            ->whereNull('deleted_at')
            ->where('is_active', true)
            ->orderBy('name')
            ->pluck('name', 'id');

        $names = [];
        foreach (['customer' => 'customers', 'vendor' => 'vendors', 'employee' => 'employees', 'other_name' => 'other_names'] as $type => $table) {
            DB::table($table)
                ->where('company_id', $companyId)
                ->whereNull('deleted_at')
                ->orderBy('name')
                ->pluck('name', 'id')
                ->each(function ($name, $id) use (&$names, $type) {
                    $names[$type . ':' . $id] = $name;
                });
        }

        return [
            'columns' => $this->profitLossColumnOptions(),
            'dimensions' => $dimensions,
            'dimensionTree' => $this->profitLossDimensionTree($companyId),
            'partyTree' => $this->profitLossPartyTree($companyId),
            'accountTypes' => $accountTypes,
            'voucherTypes' => VoucherNumberService::voucherTypes(),
            'names' => $names,
        ];
    }

    public function profitLossPartyTree(int $companyId): array
    {
        $groups = [];

        $customerNodes = $this->profitLossCustomerPartyNodes($companyId);
        if ($customerNodes !== []) {
            $groups[] = $this->profitLossPartyGroupNode('customers', 'Customers', $customerNodes);
        }

        $vendorNodes = $this->profitLossHierarchicalPartyNodes($companyId, 'vendors', 'vendor', 'Vendor');
        if ($vendorNodes !== []) {
            $groups[] = $this->profitLossPartyGroupNode('vendors', 'Vendors', $vendorNodes);
        }

        $employeeNodes = $this->profitLossHierarchicalPartyNodes($companyId, 'employees', 'employee', 'Employee');
        if ($employeeNodes !== []) {
            $groups[] = $this->profitLossPartyGroupNode('employees', 'Employees', $employeeNodes);
        }

        $otherNameNodes = $this->profitLossOtherNamePartyNodes($companyId);
        if ($otherNameNodes !== []) {
            $groups[] = $this->profitLossPartyGroupNode('other_names', 'Other Names', $otherNameNodes);
        }

        return $groups;
    }

    public function profitLossPartyLabels(array $values): string
    {
        $values = array_values(array_filter($values));

        if ($values === []) {
            return 'All names';
        }

        if (count($values) === 1) {
            return $this->profitLossPartyLabel($values[0]) ?? '1 name selected';
        }

        return count($values) . ' names selected';
    }

    public function profitLossPartyLabel(?string $value): ?string
    {
        if (!$value || !str_contains($value, ':')) {
            return null;
        }

        [$partyType, $partyId] = explode(':', $value, 2);
        $partyId = (int) $partyId;
        $tables = [
            'customer' => 'customers',
            'vendor' => 'vendors',
            'employee' => 'employees',
            'other_name' => 'other_names',
        ];

        if (!isset($tables[$partyType]) || $partyId <= 0) {
            return null;
        }

        $table = $tables[$partyType];
        $row = DB::table($table)
            ->where('id', $partyId)
            ->whereNull('deleted_at')
            ->first(['name', 'is_job']);

        if (!$row) {
            return null;
        }

        if ($partyType === 'customer' && !empty($row->is_job)) {
            return $row->name . ' (Job)';
        }

        $typeLabels = [
            'customer' => 'Customer',
            'vendor' => 'Vendor',
            'employee' => 'Employee',
            'other_name' => 'Other Name',
        ];

        return $row->name . ' (' . ($typeLabels[$partyType] ?? ucfirst($partyType)) . ')';
    }

    public function profitLossPartyName(?string $value): ?string
    {
        if (!$value || !str_contains($value, ':')) {
            return null;
        }

        [$partyType, $partyId] = explode(':', $value, 2);
        $partyId = (int) $partyId;
        $tables = [
            'customer' => 'customers',
            'vendor' => 'vendors',
            'employee' => 'employees',
            'other_name' => 'other_names',
        ];

        if (!isset($tables[$partyType]) || $partyId <= 0) {
            return null;
        }

        $row = DB::table($tables[$partyType])
            ->where('id', $partyId)
            ->whereNull('deleted_at')
            ->first(['name']);

        return $row?->name ?: null;
    }

    private function profitLossPartyGroupNode(string $key, string $label, array $children): array
    {
        return [
            'id' => 'group:' . $key,
            'name' => $label,
            'code' => '',
            'type_label' => '',
            'selectable' => false,
            'value' => null,
            'children' => $children,
        ];
    }

    private function profitLossCustomerPartyNodes(int $companyId): array
    {
        $rows = DB::table('customers')
            ->where('company_id', $companyId)
            ->whereNull('deleted_at')
            ->orderBy('name')
            ->get(['id', 'parent_id', 'is_job', 'code', 'name']);

        $byParent = [];
        foreach ($rows as $row) {
            $byParent[$row->parent_id ?? 'root'][] = $row;
        }

        $build = function (string|int $parentKey) use (&$build, $byParent): array {
            $nodes = [];

            foreach ($byParent[$parentKey] ?? [] as $row) {
                if ($parentKey === 'root' && $row->is_job) {
                    continue;
                }

                $isJob = (bool) $row->is_job;
                $nodes[] = [
                    'id' => 'customer:' . $row->id,
                    'name' => $row->name,
                    'code' => $row->code ?? '',
                    'type_label' => $isJob ? 'Job' : 'Customer',
                    'selectable' => true,
                    'value' => 'customer:' . $row->id,
                    'children' => $build($row->id),
                ];
            }

            usort($nodes, static fn (array $a, array $b) => strcasecmp($a['name'], $b['name']));

            return $nodes;
        };

        return $build('root');
    }

    private function profitLossHierarchicalPartyNodes(int $companyId, string $table, string $partyType, string $typeLabel): array
    {
        $rows = DB::table($table)
            ->where('company_id', $companyId)
            ->whereNull('deleted_at')
            ->orderBy('name')
            ->get(['id', 'parent_id', 'code', 'name']);

        $byParent = [];
        foreach ($rows as $row) {
            $byParent[$row->parent_id ?? 'root'][] = $row;
        }

        $build = function (string|int $parentKey) use (&$build, $byParent, $partyType, $typeLabel): array {
            $nodes = [];

            foreach ($byParent[$parentKey] ?? [] as $row) {
                $nodes[] = [
                    'id' => $partyType . ':' . $row->id,
                    'name' => $row->name,
                    'code' => $row->code ?? '',
                    'type_label' => $typeLabel,
                    'selectable' => true,
                    'value' => $partyType . ':' . $row->id,
                    'children' => $build($row->id),
                ];
            }

            usort($nodes, static fn (array $a, array $b) => strcasecmp($a['name'], $b['name']));

            return $nodes;
        };

        return $build('root');
    }

    private function profitLossOtherNamePartyNodes(int $companyId): array
    {
        return DB::table('other_names')
            ->where('company_id', $companyId)
            ->whereNull('deleted_at')
            ->orderBy('name')
            ->get(['id', 'code', 'name'])
            ->map(static fn ($row) => [
                'id' => 'other_name:' . $row->id,
                'name' => $row->name,
                'code' => $row->code ?? '',
                'type_label' => 'Other Name',
                'selectable' => true,
                'value' => 'other_name:' . $row->id,
                'children' => [],
            ])
            ->sortBy('name', SORT_NATURAL | SORT_FLAG_CASE)
            ->values()
            ->all();
    }

    public function profitLossColumnOptions(): array
    {
        return [
            'total' => 'Total only',
            'month' => 'Month',
            'quarter' => 'Quarter',
            'half_year' => 'Half-Year',
            'year' => 'Year',
        ];
    }

    public function profitAndLoss(int $companyId, string $dateFrom, string $dateTo, bool $includeZero = false, array $filters = []): array
    {
        $dateFrom = Carbon::parse($dateFrom)->toDateString();
        $dateTo = Carbon::parse($dateTo)->toDateString();
        $sections = ['income', 'cost_of_goods_sold', 'expenses', 'other_income', 'other_expense'];
        $dimensionValueIds = array_values(array_unique(array_filter(array_map(
            'intval',
            $filters['dimension_value_ids'] ?? [],
        ))));
        $dimensionColumns = $dimensionValueIds !== []
            ? $this->resolveProfitLossDimensionColumns($dimensionValueIds, $companyId)
            : [];

        $columnsMode = (string) ($filters['columns'] ?? 'total');
        if (!array_key_exists($columnsMode, $this->profitLossColumnOptions())) {
            $columnsMode = 'total';
        }

        $columnPeriods = [];
        if ($dimensionColumns === []) {
            $columnPeriods = $columnsMode !== 'total'
                ? $this->buildProfitLossPeriodColumns($columnsMode, $dateFrom, $dateTo, $companyId)
                : [];

            if ($columnsMode !== 'total' && $columnPeriods === []) {
                $columnsMode = 'total';
            }
        } else {
            $columnPeriods = $dimensionColumns;
            $columnsMode = 'dimensions';
        }

        $columnKeys = array_column($columnPeriods, 'key');
        $reportFilters = $this->filtersWithoutDimensionColumns($filters);

        if ($dimensionColumns !== []) {
            $leafAccounts = $this->accountBalancesByDimension(
                $companyId,
                $dateFrom,
                $dateTo,
                ['income', 'expense'],
                $reportFilters,
                $dimensionColumns,
            );
        } elseif ($columnsMode !== 'total') {
            $leafAccounts = $this->accountBalancesByPeriod(
                $companyId,
                $dateFrom,
                $dateTo,
                ['income', 'expense'],
                $reportFilters,
                $columnPeriods,
            );
        } else {
            $leafAccounts = $this->accountBalances(
                $companyId,
                $dateFrom,
                $dateTo,
                ['income', 'expense'],
                false,
                $reportFilters,
            );
        }

        $accounts = $leafAccounts
            ->when(!empty($filters['account_sections']), fn (Collection $collection) => $collection->filter(
                fn (Account $account) => in_array($account->report_section, $filters['account_sections'], true)
            ))
            ->filter(fn ($account) => $includeZero || abs($account->report_amount) > 0.009)
            ->when($filters['amount_min'] ?? null, fn (Collection $collection, float $min) => $collection->filter(
                fn (Account $account) => abs($account->report_amount) >= $min
            ))
            ->when($filters['amount_max'] ?? null, fn (Collection $collection, float $max) => $collection->filter(
                fn (Account $account) => abs($account->report_amount) <= $max
            ))
            ->groupBy('report_section');

        $groups = collect($sections)->mapWithKeys(function (string $section) use ($accounts, $companyId, $columnKeys) {
            $sectionAccounts = $accounts->get($section, collect());

            return [$section => [
                'key' => $section,
                'label' => $this->sectionLabel($section),
                'accounts' => $sectionAccounts->values(),
                'tree' => $this->buildProfitLossTree($sectionAccounts, $companyId, $columnKeys),
                'period_totals' => $this->sumAccountPeriodTotals($sectionAccounts, $columnKeys),
                'total' => $sectionAccounts->sum('report_amount'),
            ]];
        })->all();

        $totals = [
            'income' => $groups['income']['total'],
            'cost_of_goods_sold' => $groups['cost_of_goods_sold']['total'],
            'gross_profit' => $groups['income']['total'] - $groups['cost_of_goods_sold']['total'],
            'expenses' => $groups['expenses']['total'],
            'operating_income' => $groups['income']['total'] - $groups['cost_of_goods_sold']['total'] - $groups['expenses']['total'],
            'other_income' => $groups['other_income']['total'],
            'other_expense' => $groups['other_expense']['total'],
        ];
        $totals['net_other_income'] = $totals['other_income'] - $totals['other_expense'];
        $totals['net_income'] = $totals['operating_income'] + $totals['net_other_income'];

        if ($columnKeys !== []) {
            $totals['periods'] = [
                'gross_profit' => $this->diffPeriodTotals(
                    $groups['income']['period_totals'],
                    $groups['cost_of_goods_sold']['period_totals'],
                    $columnKeys,
                ),
                'operating_income' => $this->diffPeriodTotals(
                    $this->diffPeriodTotals(
                        $groups['income']['period_totals'],
                        $groups['cost_of_goods_sold']['period_totals'],
                        $columnKeys,
                    ),
                    $groups['expenses']['period_totals'],
                    $columnKeys,
                ),
                'net_income' => $this->sumPeriodTotals(
                    $this->diffPeriodTotals(
                        $this->diffPeriodTotals(
                            $groups['income']['period_totals'],
                            $groups['cost_of_goods_sold']['period_totals'],
                            $columnKeys,
                        ),
                        $groups['expenses']['period_totals'],
                        $columnKeys,
                    ),
                    $this->diffPeriodTotals(
                        $groups['other_income']['period_totals'],
                        $groups['other_expense']['period_totals'],
                        $columnKeys,
                    ),
                    $columnKeys,
                ),
            ];
        }

        return compact('groups', 'totals', 'columnPeriods', 'columnsMode', 'dimensionColumns');
    }

    public function balanceSheet(int $companyId, string $asOfDate, ?string $fiscalYearStart = null, bool $includeZero = false): array
    {
        $asOfDate = Carbon::parse($asOfDate)->toDateString();
        $fiscalYearStart = $fiscalYearStart ? Carbon::parse($fiscalYearStart)->toDateString() : Carbon::parse($asOfDate)->startOfYear()->toDateString();
        $sections = [
            'bank', 'accounts_receivable', 'other_current_assets', 'fixed_assets', 'other_assets',
            'accounts_payable', 'credit_card', 'other_current_liabilities', 'long_term_liabilities', 'equity',
        ];
        $accounts = $this->accountBalances($companyId, null, $asOfDate, ['asset', 'liability', 'equity'], true)
            ->filter(fn ($account) => $includeZero || abs($account->report_amount) > 0.009)
            ->groupBy('report_section');

        $groups = collect($sections)->mapWithKeys(fn (string $section) => [$section => [
            'key' => $section,
            'label' => $this->sectionLabel($section),
            'type' => $this->sectionType($section),
            'accounts' => $accounts->get($section, collect())->values(),
            'total' => $accounts->get($section, collect())->sum('report_amount'),
        ]])->all();

        $priorEarnings = $this->netIncomeAmount($companyId, null, Carbon::parse($fiscalYearStart)->subDay()->toDateString());
        $currentEarnings = $this->netIncomeAmount($companyId, $fiscalYearStart, $asOfDate);

        $totals = [
            'assets' => $groups['bank']['total'] + $groups['accounts_receivable']['total'] + $groups['other_current_assets']['total'] + $groups['fixed_assets']['total'] + $groups['other_assets']['total'],
            'liabilities' => $groups['accounts_payable']['total'] + $groups['credit_card']['total'] + $groups['other_current_liabilities']['total'] + $groups['long_term_liabilities']['total'],
            'equity_accounts' => $groups['equity']['total'],
            'prior_earnings' => $priorEarnings,
            'current_earnings' => $currentEarnings,
        ];
        $totals['equity'] = $totals['equity_accounts'] + $totals['prior_earnings'] + $totals['current_earnings'];
        $totals['liabilities_and_equity'] = $totals['liabilities'] + $totals['equity'];
        $totals['difference'] = $totals['assets'] - $totals['liabilities_and_equity'];

        return compact('groups', 'totals');
    }


    private function partyNames(int $companyId, Collection $entries): array
    {
        $tables = [
            'customer' => 'customers',
            'vendor' => 'vendors',
            'employee' => 'employees',
            'other_name' => 'other_names',
        ];
        $names = [];

        foreach ($tables as $type => $table) {
            $ids = $entries
                ->map(fn (LedgerEntry $entry) => $entry->voucher?->party_type === $type ? $entry->voucher?->party_id : ($entry->party_type === $type ? $entry->party_id : null))
                ->filter()
                ->unique()
                ->values();

            if ($ids->isEmpty()) {
                continue;
            }

            DB::table($table)
                ->where('company_id', $companyId)
                ->whereIn('id', $ids)
                ->pluck('name', 'id')
                ->each(function ($name, $id) use (&$names, $type) {
                    $names[$type . ':' . $id] = $name;
                });
        }

        return $names;
    }

    private function accountBalances(int $companyId, ?string $dateFrom, string $dateTo, array $types, bool $cumulative, array $filters = []): Collection
    {
        $accounts = Account::withoutGlobalScopes()
            ->where('company_id', $companyId)
            ->whereNull('deleted_at')
            ->where('is_group', false)
            ->whereIn('type', $types)
            ->orderBy('code')
            ->orderBy('name')
            ->get(['id', 'code', 'name', 'type', 'sub_type', 'detail_type', 'is_bank', 'is_cash', 'sort_order']);

        $amountsQuery = DB::table('ledger_entries as le')
            ->where('le.company_id', $companyId)
            ->whereDate('le.entry_date', '<=', $dateTo)
            ->when(!$cumulative && $dateFrom, fn ($query) => $query->whereDate('le.entry_date', '>=', $dateFrom))
            ->whereIn('le.account_id', $accounts->pluck('id'));

        $this->applyLedgerEntryFilters($amountsQuery, $filters);

        $amounts = $amountsQuery
            ->select('le.account_id')
            ->selectRaw('COALESCE(SUM(le.debit),0) as debit')
            ->selectRaw('COALESCE(SUM(le.credit),0) as credit')
            ->groupBy('le.account_id')
            ->get()
            ->keyBy('account_id');

        return $accounts->map(function (Account $account) use ($amounts) {
            $row = $amounts->get($account->id);
            $debit = (float) ($row->debit ?? 0);
            $credit = (float) ($row->credit ?? 0);
            $section = \App\Support\QuickBooksChartOfAccounts::sectionForAccount($account);
            $account->report_section = $section;
            $account->report_section_label = $this->sectionLabel($section);
            $account->report_amount = in_array($account->type, ['liability', 'equity', 'income'], true)
                ? $credit - $debit
                : $debit - $credit;

            return $account;
        });
    }

    private function netIncomeAmount(int $companyId, ?string $dateFrom, string $dateTo): float
    {
        if (Carbon::parse($dateTo)->lt(Carbon::parse('1900-01-01'))) {
            return 0.0;
        }

        return $this->accountBalances($companyId, $dateFrom, $dateTo, ['income', 'expense'], $dateFrom === null)
            ->sum(function (Account $account) {
                return in_array($account->report_section, ['income', 'other_income'], true)
                    ? $account->report_amount
                    : -$account->report_amount;
            });
    }

    private function applyLedgerEntryFilters($query, array $filters): void
    {
        $voucherTypes = $filters['voucher_types'] ?? [];
        if ($voucherTypes === [] && !empty($filters['voucher_type'])) {
            $voucherTypes = [(string) $filters['voucher_type']];
        }

        if ($voucherTypes !== []) {
            $query->join('vouchers as v', function ($join) {
                $join->on('v.id', '=', 'le.voucher_id')
                    ->where('v.status', 'posted');
            });
            $query->whereIn('v.voucher_type', $voucherTypes);
        }

        $parties = $filters['parties'] ?? [];
        if ($parties === [] && !empty($filters['party'])) {
            $parties = [(string) $filters['party']];
        }

        if ($parties !== []) {
            $query->where(function ($outerQuery) use ($parties) {
                foreach ($parties as $party) {
                    if (!is_string($party) || !str_contains($party, ':')) {
                        continue;
                    }

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

                    $outerQuery->orWhere(function ($partyQuery) use ($partyType, $partyId) {
                        $partyQuery->where(function ($entryPartyQuery) use ($partyType, $partyId) {
                            $entryPartyQuery->where('le.party_type', $partyType)
                                ->where('le.party_id', $partyId);
                        })->orWhereExists(function ($sub) use ($partyType, $partyId) {
                            $sub->select(DB::raw(1))
                                ->from('vouchers as v_party')
                                ->whereColumn('v_party.id', 'le.voucher_id')
                                ->where('v_party.party_type', $partyType)
                                ->where('v_party.party_id', $partyId);
                        });
                    });
                }
            });
        }

        if (!empty($filters['dimension_value_id'])) {
            $dimensionValueId = (int) $filters['dimension_value_id'];

            $query->whereExists(function ($sub) use ($dimensionValueId) {
                $sub->select(DB::raw(1))
                    ->from('voucher_lines as vl')
                    ->whereColumn('vl.id', 'le.voucher_line_id')
                    ->where(function ($dimensionQuery) use ($dimensionValueId) {
                        foreach (range(1, 7) as $index) {
                            $dimensionQuery->orWhere("vl.dim{$index}_id", $dimensionValueId);
                        }
                    });
            });
        }
    }

    private function buildProfitLossTree(Collection $leaves, int $companyId, array $periodKeys = []): array
    {
        if ($leaves->isEmpty()) {
            return [];
        }

        $allAccounts = Account::withoutGlobalScopes()
            ->where('company_id', $companyId)
            ->whereNull('deleted_at')
            ->whereIn('type', ['income', 'expense'])
            ->orderBy('code')
            ->orderBy('name')
            ->get(['id', 'parent_id', 'code', 'name', 'type', 'is_group', 'depth', 'sort_order'])
            ->keyBy('id');

        $needed = [];
        foreach ($leaves as $leaf) {
            $currentId = $leaf->id;
            while ($currentId && $allAccounts->has($currentId)) {
                $needed[$currentId] = true;
                $currentId = $allAccounts[$currentId]->parent_id;
            }
        }

        $leafAmounts = $leaves->mapWithKeys(fn (Account $account) => [$account->id => (float) $account->report_amount]);
        $leafPeriodAmounts = $leaves->mapWithKeys(fn (Account $account) => [
            $account->id => $account->report_period_amounts ?? [],
        ]);
        $periodAmountsById = [];
        $nodesById = $allAccounts
            ->filter(fn (Account $account) => isset($needed[$account->id]))
            ->map(function (Account $account) use ($leafAmounts, $leafPeriodAmounts, $periodKeys, &$periodAmountsById) {
                $node = clone $account;
                $node->report_amount = (float) ($leafAmounts[$node->id] ?? 0);
                $periodAmounts = $periodKeys === []
                    ? []
                    : array_fill_keys($periodKeys, 0.0);

                if ($periodKeys !== [] && isset($leafPeriodAmounts[$node->id])) {
                    foreach ($periodKeys as $periodKey) {
                        $periodAmounts[$periodKey] = (float) ($leafPeriodAmounts[$node->id][$periodKey] ?? 0);
                    }
                }

                $periodAmountsById[$node->id] = $periodAmounts;

                return $node;
            });

        foreach ($nodesById->sortByDesc(fn (Account $account) => $account->depth ?? 0) as $node) {
            if ($node->parent_id && $nodesById->has($node->parent_id)) {
                $nodesById[$node->parent_id]->report_amount += $node->report_amount;

                if ($periodKeys !== []) {
                    foreach ($periodKeys as $periodKey) {
                        $periodAmountsById[$node->parent_id][$periodKey] += $periodAmountsById[$node->id][$periodKey] ?? 0;
                    }
                }
            }
        }

        foreach ($nodesById as $node) {
            $node->report_period_amounts = $periodAmountsById[$node->id] ?? [];
        }

        return $this->nestProfitLossTreeNodes($nodesById);
    }

    private function nestProfitLossTreeNodes(Collection $nodesById, ?int $parentId = null): array
    {
        $tree = [];

        foreach ($nodesById as $node) {
            $isRoot = $parentId === null
                && (!$node->parent_id || !$nodesById->has($node->parent_id));
            $isChild = $parentId !== null && (int) $node->parent_id === $parentId;

            if (!$isRoot && !$isChild) {
                continue;
            }

            $node->children_tree = $this->nestProfitLossTreeNodes($nodesById, $node->id);
            $tree[] = $node;
        }

        usort($tree, fn (Account $a, Account $b) => $this->profitLossTreeSortKey($a) <=> $this->profitLossTreeSortKey($b));

        return $tree;
    }

    private function profitLossTreeSortKey(Account $account): string
    {
        return sprintf('%010s|%s', $account->code ?? '', strtolower($account->name));
    }

    private function sectionLabel(string $section): string
    {
        return \App\Support\QuickBooksChartOfAccounts::sections()[$section]['label'] ?? ucfirst(str_replace('_', ' ', $section));
    }

    private function sectionType(string $section): string
    {
        return \App\Support\QuickBooksChartOfAccounts::sections()[$section]['type'] ?? 'asset';
    }

    private function accountBalancesByPeriod(
        int $companyId,
        string $dateFrom,
        string $dateTo,
        array $types,
        array $filters,
        array $columnPeriods,
    ): Collection {
        $periodKeys = array_column($columnPeriods, 'key');
        $accounts = Account::withoutGlobalScopes()
            ->where('company_id', $companyId)
            ->whereNull('deleted_at')
            ->where('is_group', false)
            ->whereIn('type', $types)
            ->orderBy('code')
            ->orderBy('name')
            ->get(['id', 'code', 'name', 'type', 'sub_type', 'detail_type', 'is_bank', 'is_cash', 'sort_order']);

        $monthlyQuery = DB::table('ledger_entries as le')
            ->where('le.company_id', $companyId)
            ->whereDate('le.entry_date', '>=', $dateFrom)
            ->whereDate('le.entry_date', '<=', $dateTo)
            ->whereIn('le.account_id', $accounts->pluck('id'));

        $this->applyLedgerEntryFilters($monthlyQuery, $filters);

        $monthlyRows = $monthlyQuery
            ->select('le.account_id')
            ->selectRaw("DATE_FORMAT(le.entry_date, '%Y-%m') as month_key")
            ->selectRaw('COALESCE(SUM(le.debit),0) as debit')
            ->selectRaw('COALESCE(SUM(le.credit),0) as credit')
            ->groupBy('le.account_id', 'month_key')
            ->get();

        $monthlyByAccount = [];
        foreach ($monthlyRows as $row) {
            $monthlyByAccount[$row->account_id][$row->month_key] = $row;
        }

        $monthToPeriod = [];
        foreach ($columnPeriods as $period) {
            $cursor = Carbon::parse($period['date_from'])->startOfMonth();
            $end = Carbon::parse($period['date_to'])->endOfMonth();
            while ($cursor->lte($end)) {
                $monthToPeriod[$cursor->format('Y-m')] = $period['key'];
                $cursor->addMonth()->startOfMonth();
            }
        }

        return $accounts->map(function (Account $account) use ($monthlyByAccount, $monthToPeriod, $periodKeys) {
            $periodAmounts = array_fill_keys($periodKeys, 0.0);

            foreach ($monthlyByAccount[$account->id] ?? [] as $monthKey => $row) {
                $periodKey = $monthToPeriod[$monthKey] ?? null;
                if ($periodKey === null) {
                    continue;
                }

                $debit = (float) $row->debit;
                $credit = (float) $row->credit;
                $amount = in_array($account->type, ['liability', 'equity', 'income'], true)
                    ? $credit - $debit
                    : $debit - $credit;
                $periodAmounts[$periodKey] += $amount;
            }

            $section = \App\Support\QuickBooksChartOfAccounts::sectionForAccount($account);
            $account->report_section = $section;
            $account->report_section_label = $this->sectionLabel($section);
            $account->report_period_amounts = $periodAmounts;
            $account->report_amount = array_sum($periodAmounts);

            return $account;
        });
    }

    private function buildProfitLossPeriodColumns(string $mode, string $dateFrom, string $dateTo, int $companyId): array
    {
        $from = Carbon::parse($dateFrom)->startOfDay();
        $to = Carbon::parse($dateTo)->endOfDay();

        if ($to->lt($from)) {
            return [];
        }

        return match ($mode) {
            'month' => $this->buildCalendarMonthPeriods($from, $to),
            'quarter' => $this->buildFiscalQuarterPeriods($from, $to, $companyId),
            'half_year' => $this->buildFiscalHalfYearPeriods($from, $to, $companyId),
            'year' => $this->buildFiscalYearPeriods($from, $to, $companyId),
            default => [],
        };
    }

    private function buildCalendarMonthPeriods(Carbon $from, Carbon $to): array
    {
        $periods = [];
        $cursor = $from->copy()->startOfMonth();

        while ($cursor->lte($to)) {
            $start = $cursor->copy()->max($from);
            $end = $cursor->copy()->endOfMonth()->min($to);
            $periods[] = [
                'key' => $cursor->format('Y-m'),
                'label' => $cursor->format('M Y'),
                'date_from' => $start->toDateString(),
                'date_to' => $end->toDateString(),
            ];
            $cursor->addMonth()->startOfMonth();
        }

        return $periods;
    }

    private function buildFiscalQuarterPeriods(Carbon $from, Carbon $to, int $companyId): array
    {
        return $this->buildFiscalChunkPeriods($from, $to, $companyId, 3, 'Q');
    }

    private function buildFiscalHalfYearPeriods(Carbon $from, Carbon $to, int $companyId): array
    {
        return $this->buildFiscalChunkPeriods($from, $to, $companyId, 6, 'H');
    }

    private function buildFiscalYearPeriods(Carbon $from, Carbon $to, int $companyId): array
    {
        $fiscalStartMonth = $this->fiscalYearStartMonth($companyId);
        $periods = [];
        $seen = [];
        $cursor = $from->copy();

        while ($cursor->lte($to)) {
            $periodStart = $this->fiscalYearStartForDate($cursor, $fiscalStartMonth);
            $periodEnd = $periodStart->copy()->addYear()->subDay()->endOfDay();
            $label = $this->fiscalYearLabel($periodStart, $fiscalStartMonth);

            if (!isset($seen[$label])) {
                $seen[$label] = true;
                $periods[] = [
                    'key' => $label,
                    'label' => $label,
                    'date_from' => $periodStart->copy()->max($from)->toDateString(),
                    'date_to' => $periodEnd->copy()->min($to)->toDateString(),
                ];
            }

            $cursor = $periodEnd->copy()->addDay()->startOfDay();
        }

        return $periods;
    }

    private function buildFiscalChunkPeriods(Carbon $from, Carbon $to, int $companyId, int $months, string $prefix): array
    {
        $fiscalStartMonth = $this->fiscalYearStartMonth($companyId);
        $periods = [];
        $seen = [];
        $cursor = $from->copy();

        while ($cursor->lte($to)) {
            $fyStart = $this->fiscalYearStartForDate($cursor, $fiscalStartMonth);
            $monthsIntoYear = $fyStart->diffInMonths($cursor->copy()->startOfMonth());
            $chunkIndex = intdiv($monthsIntoYear, $months);
            $periodStart = $fyStart->copy()->addMonths($chunkIndex * $months);
            $periodEnd = $periodStart->copy()->addMonths($months)->subDay()->endOfDay();
            $fyLabel = $this->fiscalYearLabel($periodStart, $fiscalStartMonth);
            $chunkNumber = intdiv($monthsIntoYear, $months) + 1;
            $key = $fyLabel . '-' . $prefix . $chunkNumber;

            if (!isset($seen[$key])) {
                $seen[$key] = true;
                $periods[] = [
                    'key' => $key,
                    'label' => $prefix . $chunkNumber . ' ' . $fyLabel,
                    'date_from' => $periodStart->copy()->max($from)->toDateString(),
                    'date_to' => $periodEnd->copy()->min($to)->toDateString(),
                ];
            }

            $cursor = $periodEnd->copy()->addDay()->startOfDay();
        }

        return $periods;
    }

    private function fiscalYearStartMonth(int $companyId): int
    {
        return max(1, min(12, (int) Setting::get($companyId, 'fiscal_year_start_month', 1)));
    }

    private function fiscalYearStartForDate(Carbon $date, int $fiscalStartMonth): Carbon
    {
        $year = $date->year;
        if ($date->month < $fiscalStartMonth) {
            $year--;
        }

        return Carbon::createFromDate($year, $fiscalStartMonth, 1)->startOfDay();
    }

    private function fiscalYearLabel(Carbon $date, int $fiscalStartMonth): string
    {
        $start = $this->fiscalYearStartForDate($date, $fiscalStartMonth);
        $end = $start->copy()->addYear()->subDay();

        return $start->format('Y') . '-' . $end->format('Y');
    }

    private function sumAccountPeriodTotals(Collection $accounts, array $periodKeys): array
    {
        if ($periodKeys === []) {
            return [];
        }

        $totals = array_fill_keys($periodKeys, 0.0);

        foreach ($accounts as $account) {
            foreach ($periodKeys as $periodKey) {
                $totals[$periodKey] += (float) ($account->report_period_amounts[$periodKey] ?? 0);
            }
        }

        return $totals;
    }

    private function diffPeriodTotals(array $left, array $right, array $periodKeys): array
    {
        $result = [];

        foreach ($periodKeys as $periodKey) {
            $result[$periodKey] = ($left[$periodKey] ?? 0) - ($right[$periodKey] ?? 0);
        }

        return $result;
    }

    private function sumPeriodTotals(array $left, array $right, array $periodKeys): array
    {
        $result = [];

        foreach ($periodKeys as $periodKey) {
            $result[$periodKey] = ($left[$periodKey] ?? 0) + ($right[$periodKey] ?? 0);
        }

        return $result;
    }

    public function profitLossDimensionTree(int $companyId): array
    {
        $dimensions = Dimension::withoutGlobalScopes()
            ->where('company_id', $companyId)
            ->where('is_active', true)
            ->orderBy('slot')
            ->get(['id', 'slot', 'name']);

        if ($dimensions->isEmpty()) {
            return [];
        }

        $values = DimensionValue::withoutGlobalScopes()
            ->where('company_id', $companyId)
            ->where('is_active', true)
            ->whereNull('deleted_at')
            ->whereIn('dimension_id', $dimensions->pluck('id'))
            ->orderBy('sort_order')
            ->orderBy('name')
            ->get(['id', 'dimension_id', 'parent_id', 'code', 'name']);

        $tree = [];

        foreach ($dimensions as $dimension) {
            $children = $this->buildProfitLossDimensionValueTree(
                $values->where('dimension_id', $dimension->id)->values(),
            );

            if ($children === []) {
                continue;
            }

            $tree[] = [
                'id' => 'dimension-' . $dimension->id,
                'name' => $dimension->name,
                'slot' => $dimension->slot,
                'selectable' => false,
                'children' => $children,
            ];
        }

        return $tree;
    }

    private function buildProfitLossDimensionValueTree(Collection $values, ?int $parentId = null): array
    {
        $nodes = [];

        foreach ($values->where('parent_id', $parentId) as $value) {
            $children = $this->buildProfitLossDimensionValueTree($values, $value->id);
            $nodes[] = [
                'id' => $value->id,
                'name' => $value->name,
                'code' => $value->code,
                'selectable' => true,
                'children' => $children,
            ];
        }

        return $nodes;
    }

    private function resolveProfitLossDimensionColumns(array $dimensionValueIds, int $companyId): array
    {
        if ($dimensionValueIds === []) {
            return [];
        }

        $values = DimensionValue::withoutGlobalScopes()
            ->with(['dimension:id,name,slot'])
            ->where('company_id', $companyId)
            ->where('is_active', true)
            ->whereNull('deleted_at')
            ->whereIn('id', $dimensionValueIds)
            ->get()
            ->keyBy('id');

        $columns = [];

        foreach ($dimensionValueIds as $dimensionValueId) {
            $value = $values->get($dimensionValueId);
            if (!$value) {
                continue;
            }

            $columns[] = [
                'key' => (string) $value->id,
                'label' => $value->name,
                'dimension_value_id' => $value->id,
                'dimension_name' => $value->dimension?->name,
            ];
        }

        return $columns;
    }

    private function filtersWithoutDimensionColumns(array $filters): array
    {
        unset($filters['dimension_value_id'], $filters['dimension_value_ids']);

        return $filters;
    }

    private function accountBalancesByDimension(
        int $companyId,
        string $dateFrom,
        string $dateTo,
        array $types,
        array $filters,
        array $dimensionColumns,
    ): Collection {
        $columnKeys = array_column($dimensionColumns, 'key');
        $selectedIds = array_map('intval', $columnKeys);
        $accounts = Account::withoutGlobalScopes()
            ->where('company_id', $companyId)
            ->whereNull('deleted_at')
            ->where('is_group', false)
            ->whereIn('type', $types)
            ->orderBy('code')
            ->orderBy('name')
            ->get(['id', 'code', 'name', 'type', 'sub_type', 'detail_type', 'is_bank', 'is_cash', 'sort_order']);

        $amountsQuery = DB::table('ledger_entries as le')
            ->join('voucher_lines as vl', 'vl.id', '=', 'le.voucher_line_id')
            ->where('le.company_id', $companyId)
            ->whereDate('le.entry_date', '>=', $dateFrom)
            ->whereDate('le.entry_date', '<=', $dateTo)
            ->whereIn('le.account_id', $accounts->pluck('id'));

        $this->applyLedgerEntryFilters($amountsQuery, $filters);

        $rows = $amountsQuery
            ->select('le.account_id', 'le.debit', 'le.credit')
            ->selectRaw('vl.dim1_id, vl.dim2_id, vl.dim3_id, vl.dim4_id, vl.dim5_id, vl.dim6_id, vl.dim7_id')
            ->get();

        $amountsByAccount = [];

        foreach ($rows as $row) {
            $matchedDimensionIds = [];

            foreach (range(1, 7) as $index) {
                $dimensionValueId = (int) ($row->{"dim{$index}_id"} ?? 0);
                if ($dimensionValueId > 0 && in_array($dimensionValueId, $selectedIds, true)) {
                    $matchedDimensionIds[$dimensionValueId] = true;
                }
            }

            if ($matchedDimensionIds === []) {
                continue;
            }

            foreach (array_keys($matchedDimensionIds) as $dimensionValueId) {
                $key = (string) $dimensionValueId;
                $amountsByAccount[$row->account_id][$key]['debit'] = ($amountsByAccount[$row->account_id][$key]['debit'] ?? 0) + (float) $row->debit;
                $amountsByAccount[$row->account_id][$key]['credit'] = ($amountsByAccount[$row->account_id][$key]['credit'] ?? 0) + (float) $row->credit;
            }
        }

        $baseAccounts = $this->accountBalances(
            $companyId,
            $dateFrom,
            $dateTo,
            $types,
            false,
            $filters,
        )->keyBy('id');

        return $accounts->map(function (Account $account) use ($amountsByAccount, $columnKeys, $baseAccounts) {
            $columnAmounts = array_fill_keys($columnKeys, 0.0);

            foreach ($columnKeys as $key) {
                $row = $amountsByAccount[$account->id][$key] ?? null;
                if ($row === null) {
                    continue;
                }

                $debit = (float) ($row['debit'] ?? 0);
                $credit = (float) ($row['credit'] ?? 0);
                $columnAmounts[$key] = in_array($account->type, ['liability', 'equity', 'income'], true)
                    ? $credit - $debit
                    : $debit - $credit;
            }

            $base = $baseAccounts->get($account->id);
            $section = \App\Support\QuickBooksChartOfAccounts::sectionForAccount($account);
            $account->report_section = $section;
            $account->report_section_label = $this->sectionLabel($section);
            $account->report_period_amounts = $columnAmounts;
            $account->report_amount = (float) ($base->report_amount ?? array_sum($columnAmounts));

            return $account;
        });
    }

}
