<?php

namespace App\Http\Controllers\User;

use App\Http\Controllers\Controller;
use Barryvdh\DomPDF\Facade\Pdf;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

class ProfitLossController extends Controller
{
    public function index(Request $request)
    {
        $data = $this->buildProfitLossData($request);

        return view('user.reports.profit-loss', $data);
    }

    public function downloadPdf(Request $request)
    {
        $data = $this->buildProfitLossData($request);

        $fileName = 'profit-loss-report-' . now()->format('Y-m-d-H-i-s') . '.pdf';

        $pdf = Pdf::loadView('user.reports.profit-loss-pdf', $data)
            ->setPaper('a4', 'portrait')
            ->setOptions([
                'defaultFont' => 'DejaVu Sans',
                'isHtml5ParserEnabled' => true,
                'isPhpEnabled' => true,
                'isRemoteEnabled' => false,
                'fontHeightRatio' => 1.0,
                'dpi' => 96,
            ]);

        return $pdf->download($fileName);
    }

    private function buildProfitLossData(Request $request): array
    {
        $userId = auth()->id();

        $expenseDateCol   = $this->dateColumn('expenses', 'posted_at');
        $supplierDateCol  = $this->dateColumn('supplier_transactions', 'posted_at');
        $customerDateCol  = $this->dateColumn('customer_transactions', 'posted_at');
        $ledgerDateCol    = $this->dateColumn('account_ledgers', 'posted_at');
        $loanPaymentDateCol = $this->dateColumn('loan_payments', 'paid_at');

        $payrollDateCol = Schema::hasTable('payrolls') && Schema::hasColumn('payrolls', 'payroll_date')
            ? 'payroll_date'
            : 'created_at';

        [$from, $to, $fromMonth, $fromYear, $toMonth, $toYear] = $this->resolveDateRange($request);

        /*
        |--------------------------------------------------------------------------
        | INCOME
        |--------------------------------------------------------------------------
        */
        $customerBillIncome = 0.00;
        if (
            Schema::hasTable('customer_transactions') &&
            Schema::hasColumn('customer_transactions', 'user_id') &&
            Schema::hasColumn('customer_transactions', $customerDateCol) &&
            Schema::hasColumn('customer_transactions', 'debit')
        ) {
            $query = DB::table('customer_transactions')
                ->where('user_id', $userId)
                ->whereBetween($customerDateCol, [$from, $to]);

            if (Schema::hasColumn('customer_transactions', 'type')) {
                $query->where('type', 'bill');
            }

            $customerBillIncome = (float) $query->sum('debit');
        }

        $totalIncome = round($customerBillIncome, 2);

        /*
        |--------------------------------------------------------------------------
        | EXPENSES
        |--------------------------------------------------------------------------
        | Expense table        = Daily Expense
        | Supplier purchase    = Supplier Expense
        | Payroll gross_salary = Salary Expense
        | Paid bonus           = Employee Bonus Expense
        | bank_charge          = Bank Charge Expense
        | loan interest + fee  = Loan Finance Cost
        |
        | IMPORTANT:
        | Loan principal payment is NOT an expense for Profit & Loss.
        */
        $dailyExpenseTotal = 0.00;
        if (
            Schema::hasTable('expenses') &&
            Schema::hasColumn('expenses', 'user_id') &&
            Schema::hasColumn('expenses', $expenseDateCol) &&
            Schema::hasColumn('expenses', 'amount')
        ) {
            $dailyExpenseTotal = (float) DB::table('expenses')
                ->where('user_id', $userId)
                ->whereBetween($expenseDateCol, [$from, $to])
                ->sum('amount');
        }

        $supplierExpenseTotal = 0.00;
        if (
            Schema::hasTable('supplier_transactions') &&
            Schema::hasColumn('supplier_transactions', 'user_id') &&
            Schema::hasColumn('supplier_transactions', $supplierDateCol) &&
            Schema::hasColumn('supplier_transactions', 'debit')
        ) {
            $query = DB::table('supplier_transactions')
                ->where('user_id', $userId)
                ->whereBetween($supplierDateCol, [$from, $to]);

            if (Schema::hasColumn('supplier_transactions', 'type')) {
                $query->where('type', 'purchase');
            }

            $supplierExpenseTotal = (float) $query->sum('debit');
        }

        $salaryExpenseTotal = 0.00;
        if (
            Schema::hasTable('payrolls') &&
            Schema::hasTable('payroll_items') &&
            Schema::hasColumn('payrolls', 'id') &&
            Schema::hasColumn('payrolls', 'user_id') &&
            Schema::hasColumn('payroll_items', 'payroll_id') &&
            Schema::hasColumn('payroll_items', 'gross_salary')
        ) {
            $query = DB::table('payroll_items as pi')
                ->join('payrolls as p', 'p.id', '=', 'pi.payroll_id')
                ->where('p.user_id', $userId)
                ->whereBetween("p.$payrollDateCol", [$from, $to]);

            if (Schema::hasColumn('payrolls', 'status')) {
                $query->where('p.status', '!=', 'cancelled');
            }

            $salaryExpenseTotal = (float) $query->sum('pi.gross_salary');
        }

        $paidBonusExpenseTotal = 0.00;
        if (Schema::hasTable('employee_bonuses')) {
            $bonusDateCol = Schema::hasColumn('employee_bonuses', 'paid_at')
                ? 'paid_at'
                : (Schema::hasColumn('employee_bonuses', 'bonus_date') ? 'bonus_date' : 'created_at');

            if (
                Schema::hasColumn('employee_bonuses', 'user_id') &&
                Schema::hasColumn('employee_bonuses', $bonusDateCol) &&
                Schema::hasColumn('employee_bonuses', 'amount')
            ) {
                $query = DB::table('employee_bonuses')
                    ->where('user_id', $userId)
                    ->whereBetween($bonusDateCol, [$from, $to]);

                if (Schema::hasColumn('employee_bonuses', 'status')) {
                    $query->where('status', 'paid');
                }

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

        $bankChargeExpenseTotal = 0.00;
        if (
            Schema::hasTable('account_ledgers') &&
            Schema::hasColumn('account_ledgers', 'user_id') &&
            Schema::hasColumn('account_ledgers', $ledgerDateCol) &&
            Schema::hasColumn('account_ledgers', 'credit')
        ) {
            $query = DB::table('account_ledgers')
                ->where('user_id', $userId)
                ->whereBetween($ledgerDateCol, [$from, $to]);

            if (Schema::hasColumn('account_ledgers', 'type')) {
                $query->where('type', 'bank_charge');
            } else {
                $query->whereRaw('1 = 0');
            }

            $bankChargeExpenseTotal = (float) $query->sum('credit');
        }

        $loanFinanceCostTotal = $this->getLoanFinanceCostTotal($userId, $loanPaymentDateCol, $from, $to);

        $totalExpense = round(
            $dailyExpenseTotal
            + $supplierExpenseTotal
            + $salaryExpenseTotal
            + $paidBonusExpenseTotal
            + $bankChargeExpenseTotal
            + $loanFinanceCostTotal,
            2
        );

        $netProfitLoss = round($totalIncome - $totalExpense, 2);

        /*
        |--------------------------------------------------------------------------
        | STATEMENT ROWS
        |--------------------------------------------------------------------------
        */
        $statementIncomeRows = [];
        if ($customerBillIncome > 0) {
            $statementIncomeRows[] = [
                'label'  => 'Customer Bill Income',
                'amount' => round($customerBillIncome, 2),
            ];
        }

        $statementExpenseRows = [];

        if (
            Schema::hasTable('expenses') &&
            Schema::hasTable('expense_categories') &&
            Schema::hasColumn('expenses', 'expense_category_id') &&
            Schema::hasColumn('expenses', 'amount')
        ) {
            $categoryExpenses = DB::table('expenses as e')
                ->leftJoin('expense_categories as ec', 'ec.id', '=', 'e.expense_category_id')
                ->where('e.user_id', $userId)
                ->whereBetween("e.$expenseDateCol", [$from, $to])
                ->selectRaw("COALESCE(ec.name, 'Uncategorized') as category_name, SUM(e.amount) as total")
                ->groupBy('category_name')
                ->orderByDesc('total')
                ->get();

            foreach ($categoryExpenses as $row) {
                $statementExpenseRows[] = [
                    'label'  => 'Daily Expense - ' . $row->category_name,
                    'amount' => (float) $row->total,
                ];
            }
        }

        if ($supplierExpenseTotal > 0) {
            $statementExpenseRows[] = [
                'label'  => 'Supplier Expense',
                'amount' => round($supplierExpenseTotal, 2),
            ];
        }

        if ($salaryExpenseTotal > 0) {
            $statementExpenseRows[] = [
                'label'  => 'Salary Expense',
                'amount' => round($salaryExpenseTotal, 2),
            ];
        }

        if ($paidBonusExpenseTotal > 0) {
            $statementExpenseRows[] = [
                'label'  => 'Employee Bonus Expense',
                'amount' => round($paidBonusExpenseTotal, 2),
            ];
        }

        if ($bankChargeExpenseTotal > 0) {
            $statementExpenseRows[] = [
                'label'  => 'Bank Charge Expense',
                'amount' => round($bankChargeExpenseTotal, 2),
            ];
        }

        if ($loanFinanceCostTotal > 0) {
            $statementExpenseRows[] = [
                'label'  => 'Loan Finance Cost',
                'amount' => round($loanFinanceCostTotal, 2),
            ];
        }

        usort($statementExpenseRows, fn ($a, $b) => $b['amount'] <=> $a['amount']);

        /*
        |--------------------------------------------------------------------------
        | CHART DATA
        |--------------------------------------------------------------------------
        */
        $monthsDiff = max(
            1,
            $from->copy()->startOfMonth()->diffInMonths($to->copy()->startOfMonth()) + 1
        );

        $chartMode = $monthsDiff > 24 ? 'yearly' : 'monthly';

        $chartLabels = [];
        $chartIncome = [];
        $chartExpense = [];
        $chartNet = [];

        if ($chartMode === 'monthly') {
            $incomeRows          = $this->getMonthlyCustomerBillIncome($userId, $customerDateCol, $from, $to);
            $dailyExpenseRows    = $this->getMonthlyDailyExpense($userId, $expenseDateCol, $from, $to);
            $supplierExpenseRows = $this->getMonthlySupplierExpense($userId, $supplierDateCol, $from, $to);
            $salaryExpenseRows   = $this->getMonthlySalaryExpense($userId, $payrollDateCol, $from, $to);
            $bonusExpenseRows    = $this->getMonthlyBonusExpense($userId, $from, $to);
            $bankChargeRows      = $this->getMonthlyBankChargeExpense($userId, $ledgerDateCol, $from, $to);
            $loanFinanceRows     = $this->getMonthlyLoanFinanceCost($userId, $loanPaymentDateCol, $from, $to);

            $cursor = $from->copy()->startOfMonth();
            $endCursor = $to->copy()->startOfMonth();

            while ($cursor <= $endCursor) {
                $ym = $cursor->format('Y-m');

                $inc = (float) ($incomeRows[$ym] ?? 0);

                $exp =
                    (float) ($dailyExpenseRows[$ym] ?? 0) +
                    (float) ($supplierExpenseRows[$ym] ?? 0) +
                    (float) ($salaryExpenseRows[$ym] ?? 0) +
                    (float) ($bonusExpenseRows[$ym] ?? 0) +
                    (float) ($bankChargeRows[$ym] ?? 0) +
                    (float) ($loanFinanceRows[$ym] ?? 0);

                $chartLabels[]  = $cursor->format('M y');
                $chartIncome[]  = round($inc, 2);
                $chartExpense[] = round($exp, 2);
                $chartNet[]     = round($inc - $exp, 2);

                $cursor->addMonth();
            }
        } else {
            $incomeRows          = $this->getYearlyCustomerBillIncome($userId, $customerDateCol, $from, $to);
            $dailyExpenseRows    = $this->getYearlyDailyExpense($userId, $expenseDateCol, $from, $to);
            $supplierExpenseRows = $this->getYearlySupplierExpense($userId, $supplierDateCol, $from, $to);
            $salaryExpenseRows   = $this->getYearlySalaryExpense($userId, $payrollDateCol, $from, $to);
            $bonusExpenseRows    = $this->getYearlyBonusExpense($userId, $from, $to);
            $bankChargeRows      = $this->getYearlyBankChargeExpense($userId, $ledgerDateCol, $from, $to);
            $loanFinanceRows     = $this->getYearlyLoanFinanceCost($userId, $loanPaymentDateCol, $from, $to);

            for ($y = (int) $from->year; $y <= (int) $to->year; $y++) {
                $inc = (float) ($incomeRows[$y] ?? 0);

                $exp =
                    (float) ($dailyExpenseRows[$y] ?? 0) +
                    (float) ($supplierExpenseRows[$y] ?? 0) +
                    (float) ($salaryExpenseRows[$y] ?? 0) +
                    (float) ($bonusExpenseRows[$y] ?? 0) +
                    (float) ($bankChargeRows[$y] ?? 0) +
                    (float) ($loanFinanceRows[$y] ?? 0);

                $chartLabels[]  = (string) $y;
                $chartIncome[]  = round($inc, 2);
                $chartExpense[] = round($exp, 2);
                $chartNet[]     = round($inc - $exp, 2);
            }
        }

        return compact(
            'from',
            'to',
            'fromMonth',
            'fromYear',
            'toMonth',
            'toYear',
            'totalIncome',
            'totalExpense',
            'netProfitLoss',
            'customerBillIncome',
            'dailyExpenseTotal',
            'supplierExpenseTotal',
            'salaryExpenseTotal',
            'paidBonusExpenseTotal',
            'bankChargeExpenseTotal',
            'loanFinanceCostTotal',
            'statementIncomeRows',
            'statementExpenseRows',
            'chartMode',
            'chartLabels',
            'chartIncome',
            'chartExpense',
            'chartNet'
        );
    }

    private function resolveDateRange(Request $request): array
    {
        $currentMonth = (int) now()->month;
        $currentYear  = (int) now()->year;

        $fromMonth = (int) $request->get('from_month', $currentMonth);
        $fromYear  = (int) $request->get('from_year', $currentYear);
        $toMonth   = (int) $request->get('to_month', $currentMonth);
        $toYear    = (int) $request->get('to_year', $currentYear);

        if ($fromMonth < 1 || $fromMonth > 12) {
            $fromMonth = $currentMonth;
        }

        if ($toMonth < 1 || $toMonth > 12) {
            $toMonth = $currentMonth;
        }

        if ($fromYear < 2000 || $fromYear > 2100) {
            $fromYear = $currentYear;
        }

        if ($toYear < 2000 || $toYear > 2100) {
            $toYear = $currentYear;
        }

        $from = Carbon::create($fromYear, $fromMonth, 1)->startOfMonth();
        $to   = Carbon::create($toYear, $toMonth, 1)->endOfMonth();

        if ($from->gt($to)) {
            [$from, $to] = [$to->copy()->startOfMonth(), $from->copy()->endOfMonth()];
            [$fromMonth, $toMonth] = [$toMonth, $fromMonth];
            [$fromYear, $toYear] = [$toYear, $fromYear];
        }

        return [$from, $to, $fromMonth, $fromYear, $toMonth, $toYear];
    }

    private function dateColumn(string $table, string $preferred = 'posted_at'): string
    {
        if (Schema::hasTable($table) && Schema::hasColumn($table, $preferred)) {
            return $preferred;
        }

        return 'created_at';
    }

    private function getLoanFinanceCostTotal(int $userId, string $dateCol, $from, $to): float
    {
        if (
            !Schema::hasTable('loan_payments') ||
            !Schema::hasColumn('loan_payments', 'user_id') ||
            !Schema::hasColumn('loan_payments', $dateCol)
        ) {
            return 0.00;
        }

        $hasInterest = Schema::hasColumn('loan_payments', 'interest_paid');
        $hasFee      = Schema::hasColumn('loan_payments', 'fee_paid');

        if (!$hasInterest && !$hasFee) {
            return 0.00;
        }

        $selectParts = [];
        if ($hasInterest) {
            $selectParts[] = 'COALESCE(SUM(interest_paid), 0)';
        }
        if ($hasFee) {
            $selectParts[] = 'COALESCE(SUM(fee_paid), 0)';
        }

        $total = DB::table('loan_payments')
            ->where('user_id', $userId)
            ->whereBetween($dateCol, [$from, $to])
            ->selectRaw(implode(' + ', $selectParts) . ' as total')
            ->value('total');

        return round((float) $total, 2);
    }

    private function getMonthlyLoanFinanceCost(int $userId, string $dateCol, $from, $to)
    {
        if (
            !Schema::hasTable('loan_payments') ||
            !Schema::hasColumn('loan_payments', 'user_id') ||
            !Schema::hasColumn('loan_payments', $dateCol)
        ) {
            return collect();
        }

        $hasInterest = Schema::hasColumn('loan_payments', 'interest_paid');
        $hasFee      = Schema::hasColumn('loan_payments', 'fee_paid');

        if (!$hasInterest && !$hasFee) {
            return collect();
        }

        $sumExpr = [];
        if ($hasInterest) {
            $sumExpr[] = 'COALESCE(SUM(interest_paid), 0)';
        }
        if ($hasFee) {
            $sumExpr[] = 'COALESCE(SUM(fee_paid), 0)';
        }

        return DB::table('loan_payments')
            ->where('user_id', $userId)
            ->whereBetween($dateCol, [$from, $to])
            ->selectRaw("DATE_FORMAT($dateCol, '%Y-%m') as ym, " . implode(' + ', $sumExpr) . " as total")
            ->groupBy('ym')
            ->pluck('total', 'ym');
    }

    private function getYearlyLoanFinanceCost(int $userId, string $dateCol, $from, $to)
    {
        if (
            !Schema::hasTable('loan_payments') ||
            !Schema::hasColumn('loan_payments', 'user_id') ||
            !Schema::hasColumn('loan_payments', $dateCol)
        ) {
            return collect();
        }

        $hasInterest = Schema::hasColumn('loan_payments', 'interest_paid');
        $hasFee      = Schema::hasColumn('loan_payments', 'fee_paid');

        if (!$hasInterest && !$hasFee) {
            return collect();
        }

        $sumExpr = [];
        if ($hasInterest) {
            $sumExpr[] = 'COALESCE(SUM(interest_paid), 0)';
        }
        if ($hasFee) {
            $sumExpr[] = 'COALESCE(SUM(fee_paid), 0)';
        }

        return DB::table('loan_payments')
            ->where('user_id', $userId)
            ->whereBetween($dateCol, [$from, $to])
            ->selectRaw("YEAR($dateCol) as yy, " . implode(' + ', $sumExpr) . " as total")
            ->groupBy('yy')
            ->pluck('total', 'yy');
    }

    private function getMonthlyCustomerBillIncome(int $userId, string $dateCol, $from, $to)
    {
        if (
            !Schema::hasTable('customer_transactions') ||
            !Schema::hasColumn('customer_transactions', 'debit')
        ) {
            return collect();
        }

        $query = DB::table('customer_transactions')
            ->where('user_id', $userId)
            ->whereBetween($dateCol, [$from, $to]);

        if (Schema::hasColumn('customer_transactions', 'type')) {
            $query->where('type', 'bill');
        }

        return $query
            ->selectRaw("DATE_FORMAT($dateCol, '%Y-%m') as ym, SUM(debit) as total")
            ->groupBy('ym')
            ->pluck('total', 'ym');
    }

    private function getYearlyCustomerBillIncome(int $userId, string $dateCol, $from, $to)
    {
        if (
            !Schema::hasTable('customer_transactions') ||
            !Schema::hasColumn('customer_transactions', 'debit')
        ) {
            return collect();
        }

        $query = DB::table('customer_transactions')
            ->where('user_id', $userId)
            ->whereBetween($dateCol, [$from, $to]);

        if (Schema::hasColumn('customer_transactions', 'type')) {
            $query->where('type', 'bill');
        }

        return $query
            ->selectRaw("YEAR($dateCol) as yy, SUM(debit) as total")
            ->groupBy('yy')
            ->pluck('total', 'yy');
    }

    private function getMonthlyDailyExpense(int $userId, string $dateCol, $from, $to)
    {
        if (!Schema::hasTable('expenses') || !Schema::hasColumn('expenses', 'amount')) {
            return collect();
        }

        return DB::table('expenses')
            ->where('user_id', $userId)
            ->whereBetween($dateCol, [$from, $to])
            ->selectRaw("DATE_FORMAT($dateCol, '%Y-%m') as ym, SUM(amount) as total")
            ->groupBy('ym')
            ->pluck('total', 'ym');
    }

    private function getYearlyDailyExpense(int $userId, string $dateCol, $from, $to)
    {
        if (!Schema::hasTable('expenses') || !Schema::hasColumn('expenses', 'amount')) {
            return collect();
        }

        return DB::table('expenses')
            ->where('user_id', $userId)
            ->whereBetween($dateCol, [$from, $to])
            ->selectRaw("YEAR($dateCol) as yy, SUM(amount) as total")
            ->groupBy('yy')
            ->pluck('total', 'yy');
    }

    private function getMonthlySupplierExpense(int $userId, string $dateCol, $from, $to)
    {
        if (
            !Schema::hasTable('supplier_transactions') ||
            !Schema::hasColumn('supplier_transactions', 'debit')
        ) {
            return collect();
        }

        $query = DB::table('supplier_transactions')
            ->where('user_id', $userId)
            ->whereBetween($dateCol, [$from, $to]);

        if (Schema::hasColumn('supplier_transactions', 'type')) {
            $query->where('type', 'purchase');
        }

        return $query
            ->selectRaw("DATE_FORMAT($dateCol, '%Y-%m') as ym, SUM(debit) as total")
            ->groupBy('ym')
            ->pluck('total', 'ym');
    }

    private function getYearlySupplierExpense(int $userId, string $dateCol, $from, $to)
    {
        if (
            !Schema::hasTable('supplier_transactions') ||
            !Schema::hasColumn('supplier_transactions', 'debit')
        ) {
            return collect();
        }

        $query = DB::table('supplier_transactions')
            ->where('user_id', $userId)
            ->whereBetween($dateCol, [$from, $to]);

        if (Schema::hasColumn('supplier_transactions', 'type')) {
            $query->where('type', 'purchase');
        }

        return $query
            ->selectRaw("YEAR($dateCol) as yy, SUM(debit) as total")
            ->groupBy('yy')
            ->pluck('total', 'yy');
    }

    private function getMonthlySalaryExpense(int $userId, string $dateCol, $from, $to)
    {
        if (
            !Schema::hasTable('payrolls') ||
            !Schema::hasTable('payroll_items') ||
            !Schema::hasColumn('payroll_items', 'gross_salary')
        ) {
            return collect();
        }

        $query = DB::table('payroll_items as pi')
            ->join('payrolls as p', 'p.id', '=', 'pi.payroll_id')
            ->where('p.user_id', $userId)
            ->whereBetween("p.$dateCol", [$from, $to]);

        if (Schema::hasColumn('payrolls', 'status')) {
            $query->where('p.status', '!=', 'cancelled');
        }

        return $query
            ->selectRaw("DATE_FORMAT(p.$dateCol, '%Y-%m') as ym, SUM(pi.gross_salary) as total")
            ->groupBy('ym')
            ->pluck('total', 'ym');
    }

    private function getYearlySalaryExpense(int $userId, string $dateCol, $from, $to)
    {
        if (
            !Schema::hasTable('payrolls') ||
            !Schema::hasTable('payroll_items') ||
            !Schema::hasColumn('payroll_items', 'gross_salary')
        ) {
            return collect();
        }

        $query = DB::table('payroll_items as pi')
            ->join('payrolls as p', 'p.id', '=', 'pi.payroll_id')
            ->where('p.user_id', $userId)
            ->whereBetween("p.$dateCol", [$from, $to]);

        if (Schema::hasColumn('payrolls', 'status')) {
            $query->where('p.status', '!=', 'cancelled');
        }

        return $query
            ->selectRaw("YEAR(p.$dateCol) as yy, SUM(pi.gross_salary) as total")
            ->groupBy('yy')
            ->pluck('total', 'yy');
    }

    private function getMonthlyBonusExpense(int $userId, $from, $to)
    {
        if (!Schema::hasTable('employee_bonuses') || !Schema::hasColumn('employee_bonuses', 'amount')) {
            return collect();
        }

        $bonusDateCol = Schema::hasColumn('employee_bonuses', 'paid_at')
            ? 'paid_at'
            : (Schema::hasColumn('employee_bonuses', 'bonus_date') ? 'bonus_date' : 'created_at');

        $query = DB::table('employee_bonuses')
            ->where('user_id', $userId)
            ->whereBetween($bonusDateCol, [$from, $to]);

        if (Schema::hasColumn('employee_bonuses', 'status')) {
            $query->where('status', 'paid');
        }

        return $query
            ->selectRaw("DATE_FORMAT($bonusDateCol, '%Y-%m') as ym, SUM(amount) as total")
            ->groupBy('ym')
            ->pluck('total', 'ym');
    }

    private function getYearlyBonusExpense(int $userId, $from, $to)
    {
        if (!Schema::hasTable('employee_bonuses') || !Schema::hasColumn('employee_bonuses', 'amount')) {
            return collect();
        }

        $bonusDateCol = Schema::hasColumn('employee_bonuses', 'paid_at')
            ? 'paid_at'
            : (Schema::hasColumn('employee_bonuses', 'bonus_date') ? 'bonus_date' : 'created_at');

        $query = DB::table('employee_bonuses')
            ->where('user_id', $userId)
            ->whereBetween($bonusDateCol, [$from, $to]);

        if (Schema::hasColumn('employee_bonuses', 'status')) {
            $query->where('status', 'paid');
        }

        return $query
            ->selectRaw("YEAR($bonusDateCol) as yy, SUM(amount) as total")
            ->groupBy('yy')
            ->pluck('total', 'yy');
    }

    private function getMonthlyBankChargeExpense(int $userId, string $dateCol, $from, $to)
    {
        if (
            !Schema::hasTable('account_ledgers') ||
            !Schema::hasColumn('account_ledgers', 'credit')
        ) {
            return collect();
        }

        $query = DB::table('account_ledgers')
            ->where('user_id', $userId)
            ->whereBetween($dateCol, [$from, $to]);

        if (Schema::hasColumn('account_ledgers', 'type')) {
            $query->where('type', 'bank_charge');
        } else {
            return collect();
        }

        return $query
            ->selectRaw("DATE_FORMAT($dateCol, '%Y-%m') as ym, SUM(credit) as total")
            ->groupBy('ym')
            ->pluck('total', 'ym');
    }

    private function getYearlyBankChargeExpense(int $userId, string $dateCol, $from, $to)
    {
        if (
            !Schema::hasTable('account_ledgers') ||
            !Schema::hasColumn('account_ledgers', 'credit')
        ) {
            return collect();
        }

        $query = DB::table('account_ledgers')
            ->where('user_id', $userId)
            ->whereBetween($dateCol, [$from, $to]);

        if (Schema::hasColumn('account_ledgers', 'type')) {
            $query->where('type', 'bank_charge');
        } else {
            return collect();
        }

        return $query
            ->selectRaw("YEAR($dateCol) as yy, SUM(credit) as total")
            ->groupBy('yy')
            ->pluck('total', 'yy');
    }
}