<?php

namespace App\Http\Controllers\Reports;

use App\Http\Controllers\Controller;
use App\Models\CashModal;
use App\Models\CashMutation;
use App\Models\Deposit;
use App\Models\Expense;
use App\Models\Order;
use App\Models\Payment;
use App\Models\PurchasePayment;
use App\Models\Salary;
use Carbon\Carbon;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

class BalanceSheetController extends Controller
{
    private const AUTO_DEPOSIT_TAG = '[AUTO-DEPOSIT]';

    public function __construct()
    {
        $this->middleware(\App\Http\Middleware\PermissionMiddleware::class . ':view reports')
            ->only(['index', 'print', 'export']);
    }

    public function index(Request $request)
    {
        $data = $this->buildBalanceSheetData($request);

        return view('pages.reports.balance-sheet', $data);
    }

    public function print(Request $request)
    {
        $data = $this->buildBalanceSheetData($request);
        $data['title'] = 'Cetak - Neraca';
        $data['isPrint'] = true;

        return view('pages.reports.balance-sheet-print', $data);
    }

    public function export(Request $request)
    {
        $data = $this->buildBalanceSheetData($request);
        $filename = 'neraca-' . ($data['asOfDate'] ?? now()->toDateString()) . '.xls';

        return response()
            ->view('pages.reports.balance-sheet-export', $data)
            ->header('Content-Type', 'application/vnd.ms-excel; charset=UTF-8')
            ->header('Content-Disposition', 'attachment; filename="' . $filename . '"');
    }

    private function buildBalanceSheetData(Request $request): array
    {
        $asOfDate = $request->input('as_of_date', now()->toDateString());
        $asOf = Carbon::parse($asOfDate)->endOfDay();

        $context = [
            'paymentDateField' => $this->pickDateField('payments', ['payment_date', 'paid_at', 'created_at']),
            'purchasePayDateField' => Schema::hasTable('purchase_payments')
                ? $this->pickDateField('purchase_payments', ['payment_date', 'created_at'])
                : null,
            'expenseDateField' => Schema::hasTable('expenses')
                ? $this->pickDateField('expenses', ['expense_date', 'created_at'])
                : null,
            'depositDateField' => Schema::hasTable('deposits')
                ? $this->pickDateField('deposits', ['deposit_date', 'created_at'])
                : null,
            'modalDateField' => Schema::hasTable('cash_modals')
                ? $this->pickDateField('cash_modals', ['modal_date', 'created_at'])
                : null,
            'mutationDateField' => Schema::hasTable('cash_mutations')
                ? $this->pickDateField('cash_mutations', ['mutation_date', 'created_at'])
                : null,
            'paymentsHasAccount' => Schema::hasColumn('payments', 'payment_account_id'),
            'paymentsHasMethod' => Schema::hasColumn('payments', 'payment_method'),
            'purchasePaysHasAccount' => Schema::hasTable('purchase_payments') && Schema::hasColumn('purchase_payments', 'payment_account_id'),
            'purchasePaysHasMethod' => Schema::hasTable('purchase_payments') && Schema::hasColumn('purchase_payments', 'payment_method'),
        ];

        $kasKecil = $this->buildCashBalance('petty', $asOf, $context);
        $kasTunai = $this->buildCashBalance('cash', $asOf, $context);
        $kasBank = $this->buildCashBalance('bank', $asOf, $context);

        $piutangUsaha = $this->getReceivables($asOf, $context['paymentDateField']);
        $persediaan = $this->getInventoryValue($asOf);
        $asetLancarLainnya = $this->getOtherCurrentAssets($asOf);

        $totalAsetLancar = $kasKecil + $kasTunai + $kasBank + $piutangUsaha + $persediaan + $asetLancarLainnya;

        $fixedAsset = $this->getFixedAssetSummary($asOf);
        $asetTetapBruto = (float) ($fixedAsset['gross'] ?? 0);
        $akumulasiPenyusutan = (float) ($fixedAsset['accumulated_depreciation'] ?? 0);
        $asetTetapBersih = (float) ($fixedAsset['net'] ?? 0);

        $totalAset = $totalAsetLancar + $asetTetapBersih;

        $hutangUsaha = $this->getTradePayables($asOf, $context['purchasePayDateField']);
        $utangGaji = $this->getSalaryPayables($asOf);
        $utangLainLain = $this->getOtherPayables($asOf);

        $totalKewajiban = $hutangUsaha + $utangGaji + $utangLainLain;

        $modalPemilik = $this->getOwnerCapital($asOf, $context['modalDateField']);
        $prive = $this->getOwnerDrawings($asOf);

        $totalEkuitas = $totalAset - $totalKewajiban;
        $labaDitahan = $totalEkuitas - $modalPemilik + $prive;

        $totalPasiva = $totalKewajiban + $totalEkuitas;
        $selisih = $totalAset - $totalPasiva;
        $isBalanced = abs($selisih) < 1;

        return [
            'title' => 'Neraca',
            'asOfDate' => $asOfDate,
            'summary' => [
                'kas_kecil' => $kasKecil,
                'kas_tunai' => $kasTunai,
                'kas_bank' => $kasBank,
                'piutang_usaha' => $piutangUsaha,
                'persediaan' => $persediaan,
                'aset_lancar_lainnya' => $asetLancarLainnya,
                'total_aset_lancar' => $totalAsetLancar,

                'aset_tetap_bruto' => $asetTetapBruto,
                'akumulasi_penyusutan' => $akumulasiPenyusutan,
                'aset_tetap_bersih' => $asetTetapBersih,

                'total_aset' => $totalAset,

                'hutang_usaha' => $hutangUsaha,
                'utang_gaji' => $utangGaji,
                'utang_lain_lain' => $utangLainLain,
                'total_kewajiban' => $totalKewajiban,

                'modal_pemilik' => $modalPemilik,
                'prive' => $prive,
                'laba_ditahan' => $labaDitahan,
                'total_ekuitas' => $totalEkuitas,

                'total_pasiva' => $totalPasiva,
                'selisih' => $selisih,
                'is_balanced' => $isBalanced,
            ],
        ];
    }

    private function buildCashBalance(string $account, Carbon $asOf, array $context): float
    {
        if ($account === 'petty') {
            return
                $this->sumCashModalByAccount('petty', $asOf, $context['modalDateField']) +
                $this->sumNetMutation('petty', $asOf, $context['mutationDateField'], true) -
                $this->sumPettyExpenses($asOf, $context['expenseDateField']);
        }

        if ($account === 'cash') {
            return
                $this->sumPaymentsByBucket(
                    'cash',
                    $asOf,
                    $context['paymentDateField'],
                    $context['paymentsHasAccount'],
                    $context['paymentsHasMethod']
                ) +
                $this->sumCashModalByAccount('cash', $asOf, $context['modalDateField']) +
                $this->sumNetMutation('cash', $asOf, $context['mutationDateField'], true) -
                $this->sumDeposits($asOf, $context['depositDateField']) -
                $this->sumPurchasePaymentsByBucket(
                    'cash',
                    $asOf,
                    $context['purchasePayDateField'],
                    $context['purchasePaysHasAccount'],
                    $context['purchasePaysHasMethod']
                ) -
                $this->sumPaidSalariesBySource('cash', $asOf);
        }

        if ($account === 'bank') {
            return
                $this->sumPaymentsByBucket(
                    'bank',
                    $asOf,
                    $context['paymentDateField'],
                    $context['paymentsHasAccount'],
                    $context['paymentsHasMethod']
                ) +
                $this->sumDeposits($asOf, $context['depositDateField']) +
                $this->sumCashModalByAccount('bank', $asOf, $context['modalDateField']) +
                $this->sumNetMutation('bank', $asOf, $context['mutationDateField'], true) -
                $this->sumPurchasePaymentsByBucket(
                    'bank',
                    $asOf,
                    $context['purchasePayDateField'],
                    $context['purchasePaysHasAccount'],
                    $context['purchasePaysHasMethod']
                ) -
                $this->sumPaidSalariesBySource('bank', $asOf);
        }

        return 0.0;
    }

    private function sumPaymentsByBucket(
        string $bucket,
        Carbon $asOf,
        string $paymentDateField,
        bool $hasAccountColumn,
        bool $hasMethodColumn
    ): float {
        $query = Payment::query()->orderBy($paymentDateField);

        $this->applyDateTimeRange($query, $paymentDateField, null, $asOf);
        $this->applyActiveOrderFilterToPayments($query);

        if ($bucket === 'cash') {
            if (!$hasAccountColumn && !$hasMethodColumn) {
                return (float) $query->sum('amount');
            }

            $query->where(function ($q) use ($hasAccountColumn, $hasMethodColumn) {
                $added = false;

                if ($hasMethodColumn) {
                    $q->where('payment_method', 'cash');
                    $added = true;
                }

                if ($hasAccountColumn) {
                    if ($added) {
                        $q->orWhereNull('payment_account_id');
                    } else {
                        $q->whereNull('payment_account_id');
                    }
                }
            });

            return (float) $query->sum('amount');
        }

        if ($bucket === 'bank') {
            if (!$hasAccountColumn && !$hasMethodColumn) {
                return (float) $query->sum('amount');
            }

            $query->where(function ($q) use ($hasAccountColumn, $hasMethodColumn) {
                $added = false;

                if ($hasMethodColumn) {
                    $q->whereIn('payment_method', ['transfer', 'ewallet']);
                    $added = true;
                }

                if ($hasAccountColumn) {
                    if ($added) {
                        $q->orWhereNotNull('payment_account_id');
                    } else {
                        $q->whereNotNull('payment_account_id');
                    }
                }
            });

            return (float) $query->sum('amount');
        }

        return 0.0;
    }

    private function sumPettyExpenses(Carbon $asOf, ?string $expenseDateField): float
    {
        if (!Schema::hasTable('expenses') || !$expenseDateField) {
            return 0.0;
        }

        $query = Expense::query()
            ->where(function ($q) {
                $q->whereNull('description')
                    ->orWhere('description', 'not like', '%purchase_payment:%');
            });

        $this->applyDateTimeRange($query, $expenseDateField, null, $asOf);

        return (float) $query->sum('amount');
    }

    private function sumDeposits(Carbon $asOf, ?string $depositDateField): float
    {
        if (!Schema::hasTable('deposits') || !$depositDateField) {
            return 0.0;
        }

        $query = Deposit::query();
        $this->applyDateTimeRange($query, $depositDateField, null, $asOf);

        return (float) $query->sum('amount');
    }

    private function sumPurchasePaymentsByBucket(
        string $bucket,
        Carbon $asOf,
        ?string $purchasePayDateField,
        bool $hasAccountColumn,
        bool $hasMethodColumn
    ): float {
        if (!Schema::hasTable('purchase_payments') || !$purchasePayDateField) {
            return 0.0;
        }

        $query = PurchasePayment::query()->orderBy($purchasePayDateField);
        $this->applyDateTimeRange($query, $purchasePayDateField, null, $asOf);

        $rows = $query->get();

        return (float) $rows->filter(function ($item) use ($bucket, $hasAccountColumn, $hasMethodColumn) {
            $method = $this->resolvePurchasePaymentMethod($item, $hasAccountColumn, $hasMethodColumn);

            if ($bucket === 'cash') {
                return $method === 'cash';
            }

            if ($bucket === 'bank') {
                return $method === 'transfer';
            }

            return false;
        })->sum('amount');
    }

    private function resolvePurchasePaymentMethod($item, bool $hasAccountColumn, bool $hasMethodColumn): ?string
    {
        if ($hasMethodColumn && !empty($item->payment_method)) {
            return strtolower(trim((string) $item->payment_method));
        }

        $info = $this->extractPaymentInfoFromNotes((string) ($item->notes ?? ''));

        if (!empty($info['method'])) {
            return $info['method'];
        }

        if ($hasAccountColumn && !empty($item->payment_account_id)) {
            return 'transfer';
        }

        return null;
    }

    private function sumCashModalByAccount(string $account, Carbon $asOf, ?string $modalDateField): float
    {
        if (!Schema::hasTable('cash_modals') || !$modalDateField) {
            return 0.0;
        }

        $query = CashModal::query()->where('account', $account);
        $this->applyDateTimeRange($query, $modalDateField, null, $asOf);

        return (float) $query->sum('amount');
    }

    private function sumNetMutation(
        string $account,
        Carbon $asOf,
        ?string $mutationDateField,
        bool $excludeAutoDeposit = true
    ): float {
        if (!Schema::hasTable('cash_mutations') || !$mutationDateField) {
            return 0.0;
        }

        $query = CashMutation::query();

        if ($excludeAutoDeposit) {
            $query->where(function ($q) {
                $q->whereNull('notes')
                    ->orWhere('notes', 'not like', '%' . self::AUTO_DEPOSIT_TAG . '%');
            });
        }

        $this->applyDateTimeRange($query, $mutationDateField, null, $asOf);

        $in = (clone $query)->where('to_account', $account)->sum('amount');
        $out = (clone $query)->where('from_account', $account)->sum('amount');

        return (float) $in - (float) $out;
    }

    private function sumPaidSalariesBySource(string $source, Carbon $asOf): float
    {
        if (!Schema::hasTable('salaries')) {
            return 0.0;
        }

        $dateField = Schema::hasColumn('salaries', 'paid_at')
            ? 'paid_at'
            : (Schema::hasColumn('salaries', 'updated_at') ? 'updated_at' : 'created_at');

        $query = Salary::query();

        if (Schema::hasColumn('salaries', 'status')) {
            $query->where('status', 'paid');
        } elseif (Schema::hasColumn('salaries', 'is_paid')) {
            $query->where('is_paid', 1);
        } else {
            return 0.0;
        }

        if (Schema::hasColumn('salaries', 'payment_source')) {
            $query->where('payment_source', $source);
        } elseif (Schema::hasColumn('salaries', 'payment_method')) {
            $query->where('payment_method', $source === 'cash' ? 'cash' : 'transfer');
        } elseif (Schema::hasColumn('salaries', 'payment_account_id')) {
            if ($source === 'cash') {
                $query->whereNull('payment_account_id');
            } else {
                $query->whereNotNull('payment_account_id');
            }
        } else {
            return 0.0;
        }

        $this->applyDateTimeRange($query, $dateField, null, $asOf);

        return (float) $query->get()->sum(function ($salary) {
            return $this->getSalaryAmount($salary);
        });
    }

    private function getSalaryAmount($salary): float
    {
        if (isset($salary->total)) {
            return (float) $salary->total;
        }

        return
            (float) ($salary->base_salary ?? 0) +
            (float) ($salary->allowance ?? 0) +
            (float) ($salary->overtime ?? 0) +
            (float) ($salary->bonus ?? 0) -
            (float) ($salary->deduction ?? 0);
    }

    private function getReceivables(Carbon $asOf, string $paymentDateField): float
    {
        if (!Schema::hasTable('orders')) {
            return 0.0;
        }

        $remainingField = $this->pickColumn('orders', [
            'remaining_balance',
            'remaining_amount',
            'due_amount',
            'unpaid_amount',
            'outstanding_amount',
        ]);

        $orderDateField = $this->pickColumn('orders', [
            'order_date',
            'created_at',
            'paid_at',
        ]);

        if ($remainingField && $orderDateField) {
            $query = Order::query()->where($orderDateField, '<=', $asOf);

            if (Schema::hasColumn('orders', 'is_cancelled')) {
                $query->where('is_cancelled', false);
            }

            return (float) $query->where($remainingField, '>', 0)->sum($remainingField);
        }

        if (!Schema::hasTable('payments') || !Schema::hasColumn('payments', 'order_id')) {
            return 0.0;
        }

        $totalField = $this->pickColumn('orders', [
            'total_price',
            'grand_total',
            'total',
            'amount',
        ]);

        if (!$orderDateField || !$totalField) {
            return 0.0;
        }

        $orders = Order::query()
            ->select(['id', $totalField, $orderDateField])
            ->where($orderDateField, '<=', $asOf);

        if (Schema::hasColumn('orders', 'is_cancelled')) {
            $orders->where('is_cancelled', false);
        }

        $orderRows = $orders->get();

        if ($orderRows->isEmpty()) {
            return 0.0;
        }

        $orderIds = $orderRows->pluck('id')->values();

        $paymentQuery = Payment::query()
            ->select('order_id', DB::raw('SUM(amount) as total_paid'))
            ->whereNotNull('order_id')
            ->whereIn('order_id', $orderIds)
            ->groupBy('order_id');

        $this->applyDateTimeRange($paymentQuery, $paymentDateField, null, $asOf);
        $this->applyActiveOrderFilterToPayments($paymentQuery);

        $paidMap = $paymentQuery->pluck('total_paid', 'order_id');

        $totalReceivable = 0.0;

        foreach ($orderRows as $order) {
            $grandTotal = (float) ($order->{$totalField} ?? 0);
            $paid = (float) ($paidMap[$order->id] ?? 0);
            $remaining = $grandTotal - $paid;

            if ($remaining > 0) {
                $totalReceivable += $remaining;
            }
        }

        return $totalReceivable;
    }

    private function getInventoryValue(Carbon $asOf): float
    {
        if (!Schema::hasTable('materials')) {
            return 0.0;
        }

        $qtyField = $this->pickColumn('materials', [
            'current_stock',
            'stock',
            'qty',
            'quantity',
        ]);

        $costField = $this->pickColumn('materials', [
            'buy_price',
            'purchase_price',
            'unit_cost',
            'cost_price',
            'hpp',
            'price',
        ]);

        $dateField = $this->pickColumn('materials', [
            'created_at',
            'date',
        ]);

        if (!$qtyField || !$costField) {
            return 0.0;
        }

        $query = DB::table('materials');

        if ($dateField) {
            $query->where($dateField, '<=', $asOf);
        }

        $total = $query
            ->selectRaw("COALESCE(SUM(COALESCE($qtyField, 0) * COALESCE($costField, 0)), 0) as total_value")
            ->value('total_value');

        return (float) ($total ?? 0);
    }

    private function getOtherCurrentAssets(Carbon $asOf): float
    {
        $total = 0.0;

        $candidates = [
            [
                'table' => 'other_current_assets',
                'date' => ['asset_date', 'date', 'created_at'],
                'amount' => ['amount', 'value', 'balance'],
            ],
            [
                'table' => 'current_assets',
                'date' => ['asset_date', 'date', 'created_at'],
                'amount' => ['amount', 'value', 'balance'],
            ],
            [
                'table' => 'prepaid_expenses',
                'date' => ['expense_date', 'date', 'created_at'],
                'amount' => ['remaining_amount', 'amount', 'balance'],
            ],
        ];

        foreach ($candidates as $cfg) {
            if (!Schema::hasTable($cfg['table'])) {
                continue;
            }

            $dateField = $this->pickColumn($cfg['table'], $cfg['date']);
            $amountField = $this->pickColumn($cfg['table'], $cfg['amount']);

            if (!$amountField) {
                continue;
            }

            $query = DB::table($cfg['table']);

            if ($dateField) {
                $query->where($dateField, '<=', $asOf);
            }

            if (Schema::hasColumn($cfg['table'], 'status')) {
                $query->whereNotIn('status', ['cancelled', 'draft', 'inactive']);
            }

            $total += (float) $query->sum($amountField);
        }

        return $total;
    }

    private function getFixedAssetSummary(Carbon $asOf): array
    {
        $table = null;
        foreach (['fixed_assets', 'assets', 'machines'] as $candidate) {
            if (Schema::hasTable($candidate)) {
                $table = $candidate;
                break;
            }
        }

        if (!$table) {
            return [
                'gross' => 0.0,
                'accumulated_depreciation' => 0.0,
                'net' => 0.0,
            ];
        }

        $dateField = $this->pickColumn($table, [
            'purchase_date',
            'acquisition_date',
            'asset_date',
            'date',
            'created_at',
        ]);

        $grossField = $this->pickColumn($table, [
            'purchase_price',
            'acquisition_cost',
            'cost',
            'amount',
            'price',
            'book_price',
        ]);

        $bookValueField = $this->pickColumn($table, [
            'book_value',
            'current_value',
            'net_value',
        ]);

        $accDepField = $this->pickColumn($table, [
            'accumulated_depreciation',
            'depreciation_total',
            'total_depreciation',
        ]);

        $rows = DB::table($table)
            ->when($dateField, function ($q) use ($dateField, $asOf) {
                $q->where($dateField, '<=', $asOf);
            })
            ->when(Schema::hasColumn($table, 'status'), function ($q) {
                $q->whereNotIn('status', ['disposed', 'sold', 'retired', 'deleted', 'inactive']);
            })
            ->get();

        $gross = 0.0;
        $acc = 0.0;
        $net = 0.0;

        foreach ($rows as $row) {
            $rowGross = $grossField ? (float) ($row->{$grossField} ?? 0) : 0.0;
            $rowBook = $bookValueField ? (float) ($row->{$bookValueField} ?? 0) : 0.0;
            $rowAcc = $accDepField ? (float) ($row->{$accDepField} ?? 0) : 0.0;

            if ($rowGross <= 0 && $rowBook > 0) {
                $rowGross = $rowBook;
            }

            if ($rowAcc <= 0 && $rowGross > 0 && $rowBook > 0 && $rowGross >= $rowBook) {
                $rowAcc = $rowGross - $rowBook;
            }

            $rowNet = $rowGross - $rowAcc;
            if ($rowNet <= 0 && $rowBook > 0) {
                $rowNet = $rowBook;
            }

            $gross += max(0, $rowGross);
            $acc += max(0, $rowAcc);
            $net += max(0, $rowNet);
        }

        return [
            'gross' => $gross,
            'accumulated_depreciation' => $acc,
            'net' => $net,
        ];
    }

    private function getTradePayables(Carbon $asOf, ?string $purchasePayDateField): float
    {
        if (!Schema::hasTable('purchases')) {
            return 0.0;
        }

        $remainingField = $this->pickColumn('purchases', [
            'remaining_balance',
            'remaining_amount',
            'due_amount',
            'unpaid_amount',
            'outstanding_amount',
        ]);

        $purchaseDateField = $this->pickColumn('purchases', [
            'purchase_date',
            'date',
            'created_at',
        ]);

        if ($remainingField && $purchaseDateField) {
            $query = DB::table('purchases')->where($purchaseDateField, '<=', $asOf);

            if (Schema::hasColumn('purchases', 'is_cancelled')) {
                $query->where('is_cancelled', false);
            }

            if (Schema::hasColumn('purchases', 'status')) {
                $query->whereNotIn('status', ['cancelled', 'draft']);
            }

            return (float) $query->where($remainingField, '>', 0)->sum($remainingField);
        }

        $totalField = $this->pickColumn('purchases', [
            'grand_total',
            'total',
            'total_price',
            'amount',
        ]);

        if (!$purchaseDateField || !$totalField) {
            return 0.0;
        }

        $purchaseRows = DB::table('purchases')
            ->select(['id', $totalField, $purchaseDateField])
            ->where($purchaseDateField, '<=', $asOf);

        if (Schema::hasColumn('purchases', 'is_cancelled')) {
            $purchaseRows->where('is_cancelled', false);
        }

        if (Schema::hasColumn('purchases', 'status')) {
            $purchaseRows->whereNotIn('status', ['cancelled', 'draft']);
        }

        $purchaseRows = $purchaseRows->get();

        if ($purchaseRows->isEmpty()) {
            return 0.0;
        }

        if (!Schema::hasTable('purchase_payments') || !Schema::hasColumn('purchase_payments', 'purchase_id') || !$purchasePayDateField) {
            return (float) collect($purchaseRows)->sum($totalField);
        }

        $purchaseIds = collect($purchaseRows)->pluck('id')->values();

        $paidMap = DB::table('purchase_payments')
            ->select('purchase_id', DB::raw('SUM(amount) as total_paid'))
            ->whereNotNull('purchase_id')
            ->whereIn('purchase_id', $purchaseIds)
            ->where($purchasePayDateField, '<=', $asOf)
            ->groupBy('purchase_id')
            ->pluck('total_paid', 'purchase_id');

        $totalPayable = 0.0;

        foreach ($purchaseRows as $purchase) {
            $grandTotal = (float) ($purchase->{$totalField} ?? 0);
            $paid = (float) ($paidMap[$purchase->id] ?? 0);
            $remaining = $grandTotal - $paid;

            if ($remaining > 0) {
                $totalPayable += $remaining;
            }
        }

        return $totalPayable;
    }

    private function getSalaryPayables(Carbon $asOf): float
    {
        if (!Schema::hasTable('salaries')) {
            return 0.0;
        }

        $dateField = $this->pickColumn('salaries', [
            'salary_date',
            'date',
            'created_at',
            'payment_date',
        ]);

        $amountField = $this->pickColumn('salaries', [
            'remaining_balance',
            'remaining_amount',
            'outstanding_amount',
            'total',
            'net_salary',
            'total_salary',
            'take_home_pay',
            'amount',
            'salary',
        ]);

        if (!$amountField) {
            return 0.0;
        }

        $query = DB::table('salaries');

        if ($dateField) {
            $query->where($dateField, '<=', $asOf);
        }

        if (Schema::hasColumn('salaries', 'remaining_balance')) {
            return (float) $query->where('remaining_balance', '>', 0)->sum('remaining_balance');
        }

        if (Schema::hasColumn('salaries', 'remaining_amount')) {
            return (float) $query->where('remaining_amount', '>', 0)->sum('remaining_amount');
        }

        if (Schema::hasColumn('salaries', 'outstanding_amount')) {
            return (float) $query->where('outstanding_amount', '>', 0)->sum('outstanding_amount');
        }

        if (Schema::hasColumn('salaries', 'status')) {
            return (float) $query
                ->whereNotIn('status', ['paid', 'completed', 'cancelled'])
                ->sum($amountField);
        }

        if (Schema::hasColumn('salaries', 'is_paid')) {
            return (float) $query->where('is_paid', 0)->sum($amountField);
        }

        return 0.0;
    }

    private function getOtherPayables(Carbon $asOf): float
    {
        $total = 0.0;

        $candidates = [
            [
                'table' => 'other_payables',
                'date' => ['payable_date', 'date', 'created_at'],
                'amount' => ['remaining_balance', 'remaining_amount', 'outstanding_amount', 'amount', 'balance'],
            ],
            [
                'table' => 'liabilities',
                'date' => ['liability_date', 'date', 'created_at'],
                'amount' => ['remaining_balance', 'remaining_amount', 'outstanding_amount', 'amount', 'balance'],
            ],
            [
                'table' => 'debts',
                'date' => ['debt_date', 'date', 'created_at'],
                'amount' => ['remaining_balance', 'remaining_amount', 'outstanding_amount', 'amount', 'balance'],
            ],
        ];

        foreach ($candidates as $cfg) {
            if (!Schema::hasTable($cfg['table'])) {
                continue;
            }

            $dateField = $this->pickColumn($cfg['table'], $cfg['date']);
            $amountField = $this->pickColumn($cfg['table'], $cfg['amount']);

            if (!$amountField) {
                continue;
            }

            $query = DB::table($cfg['table']);

            if ($dateField) {
                $query->where($dateField, '<=', $asOf);
            }

            if (Schema::hasColumn($cfg['table'], 'status')) {
                $query->whereNotIn('status', ['paid', 'completed', 'cancelled']);
            }

            foreach (['type', 'kind', 'source', 'category', 'liability_type'] as $typeColumn) {
                if (Schema::hasColumn($cfg['table'], $typeColumn)) {
                    $query->where(function ($q) use ($typeColumn) {
                        $q->whereNull($typeColumn)
                            ->orWhereNotIn($typeColumn, ['purchase', 'supplier', 'trade', 'salary', 'payroll']);
                    });
                    break;
                }
            }

            if (Schema::hasColumn($cfg['table'], 'remaining_balance')) {
                $total += (float) (clone $query)->where('remaining_balance', '>', 0)->sum('remaining_balance');
                continue;
            }

            if (Schema::hasColumn($cfg['table'], 'remaining_amount')) {
                $total += (float) (clone $query)->where('remaining_amount', '>', 0)->sum('remaining_amount');
                continue;
            }

            if (Schema::hasColumn($cfg['table'], 'outstanding_amount')) {
                $total += (float) (clone $query)->where('outstanding_amount', '>', 0)->sum('outstanding_amount');
                continue;
            }

            $total += (float) $query->sum($amountField);
        }

        return $total;
    }

    private function getOwnerCapital(Carbon $asOf, ?string $modalDateField): float
    {
        $total = 0.0;

        if (Schema::hasTable('cash_modals') && $modalDateField) {
            $query = CashModal::query();
            $this->applyDateTimeRange($query, $modalDateField, null, $asOf);

            if (Schema::hasColumn('cash_modals', 'type')) {
                $query->whereIn('type', ['initial', 'topup']);
            }

            $total += (float) $query->sum('amount');
        }

        $candidates = [
            [
                'table' => 'owner_capitals',
                'date' => ['capital_date', 'date', 'created_at'],
                'amount' => ['amount', 'value'],
            ],
            [
                'table' => 'capitals',
                'date' => ['capital_date', 'date', 'created_at'],
                'amount' => ['amount', 'value'],
            ],
        ];

        foreach ($candidates as $cfg) {
            if (!Schema::hasTable($cfg['table'])) {
                continue;
            }

            $dateField = $this->pickColumn($cfg['table'], $cfg['date']);
            $amountField = $this->pickColumn($cfg['table'], $cfg['amount']);

            if (!$amountField) {
                continue;
            }

            $query = DB::table($cfg['table']);

            if ($dateField) {
                $query->where($dateField, '<=', $asOf);
            }

            if (Schema::hasColumn($cfg['table'], 'status')) {
                $query->whereNotIn('status', ['cancelled', 'draft']);
            }

            $total += (float) $query->sum($amountField);
        }

        return $total;
    }

    private function getOwnerDrawings(Carbon $asOf): float
    {
        $total = 0.0;

        $candidates = [
            [
                'table' => 'prives',
                'date' => ['prive_date', 'date', 'created_at'],
                'amount' => ['amount', 'value'],
            ],
            [
                'table' => 'owner_withdrawals',
                'date' => ['withdrawal_date', 'date', 'created_at'],
                'amount' => ['amount', 'value'],
            ],
            [
                'table' => 'drawings',
                'date' => ['drawing_date', 'date', 'created_at'],
                'amount' => ['amount', 'value'],
            ],
        ];

        foreach ($candidates as $cfg) {
            if (!Schema::hasTable($cfg['table'])) {
                continue;
            }

            $dateField = $this->pickColumn($cfg['table'], $cfg['date']);
            $amountField = $this->pickColumn($cfg['table'], $cfg['amount']);

            if (!$amountField) {
                continue;
            }

            $query = DB::table($cfg['table']);

            if ($dateField) {
                $query->where($dateField, '<=', $asOf);
            }

            if (Schema::hasColumn($cfg['table'], 'status')) {
                $query->whereNotIn('status', ['cancelled', 'draft']);
            }

            $total += (float) $query->sum($amountField);
        }

        if (
            $total <= 0 &&
            Schema::hasTable('expenses') &&
            Schema::hasTable('expense_categories') &&
            Schema::hasColumn('expenses', 'expense_category_id')
        ) {
            $expenseDateField = $this->pickColumn('expenses', ['expense_date', 'created_at']);

            $query = DB::table('expenses')
                ->join('expense_categories', 'expenses.expense_category_id', '=', 'expense_categories.id');

            if ($expenseDateField) {
                $query->where('expenses.' . $expenseDateField, '<=', $asOf);
            }

            $nameColumn = null;
            foreach (['name', 'title', 'label'] as $col) {
                if (Schema::hasColumn('expense_categories', $col)) {
                    $nameColumn = $col;
                    break;
                }
            }

            if ($nameColumn) {
                $query->where(function ($q) use ($nameColumn) {
                    $q->where('expense_categories.' . $nameColumn, 'like', '%prive%')
                        ->orWhere('expense_categories.' . $nameColumn, 'like', '%owner withdrawal%');
                });

                $total += (float) $query->sum('expenses.amount');
            }
        }

        return $total;
    }

    private function pickDateField(string $table, array $candidates): string
    {
        foreach ($candidates as $column) {
            if (Schema::hasColumn($table, $column)) {
                return $column;
            }
        }

        return 'created_at';
    }

    private function pickColumn(string $table, array $candidates): ?string
    {
        foreach ($candidates as $column) {
            if (Schema::hasColumn($table, $column)) {
                return $column;
            }
        }

        return null;
    }

    private function applyActiveOrderFilterToPayments(Builder $query): void
    {
        if (!Schema::hasColumn('payments', 'order_id')) {
            return;
        }

        $query->where(function ($q) {
            $q->whereNull('order_id')
                ->orWhereHas('order', function ($orderQuery) {
                    $orderQuery->where('is_cancelled', false);
                });
        });
    }

    private function extractPaymentInfoFromNotes(string $notes): array
    {
        $normalized = trim($notes);

        $method = null;
        $rekening = null;

        if (preg_match('/Metode:\s*Bank Transfer/i', $normalized)) {
            $method = 'transfer';
        } elseif (preg_match('/Metode:\s*Cash/i', $normalized)) {
            $method = 'cash';
        }

        if (preg_match('/Rekening:\s*(.+?)(\s*\|\s*|$)/i', $normalized, $matches)) {
            $rekening = trim($matches[1] ?? '');
        }

        return [
            'method' => $method,
            'rekening' => $rekening,
        ];
    }

    private function applyDateTimeRange($query, string $column, ?Carbon $start, ?Carbon $end): void
    {
        if ($start) {
            $query->where($column, '>=', $start->copy()->startOfDay());
        }

        if ($end) {
            $query->where($column, '<=', $end->copy()->endOfDay());
        }
    }
}