<?php

namespace App\Http\Controllers\Accounting;

use Illuminate\Http\Request;
use App\Models\AccountTransaction;
use App\Models\Account;
use App\Http\Controllers\Controller;
use App\Models\AccountingGroup;
use Illuminate\Support\Facades\DB;

class FinancialReportsController extends Controller
{
    // Trial Balance
    public function trialBalance1(Request $request)
    {
    
        $accounts = AccountingGroup::select(['accounts.id','accounts.name','accounts.accounting_group_id'])
        ->leftJoin('accounts','accounts.accounting_group_id','=','accounting_groups.id')
        ->groupBy('accounts.accounting_group_id')
        ->where('accounts.name','!=','')
        ->get();
        //dd($accounts);
        $sort_by      = $request->get('sortby') ?? 10;

        $trialBalance = [];

        $accountIds = $accounts->pluck('id');

        // Fetch the sum of debit and credit transactions for the paginated accounts
        $transactions = AccountTransaction::select(
            'account_id',
            DB::raw('SUM(IF(transaction_type = "DEBIT", amount, 0)) as total_debit'),
            DB::raw('SUM(IF(transaction_type = "CREDIT", amount, 0)) as total_credit')
        )
            ->whereIn('account_id', $accountIds)
            ->groupBy('account_id')
            ->get()
            ->keyBy('account_id');

        // Filter out accounts with zero transactions
        // $filteredAccountIds = $transactions->filter(function ($transaction) {
        //     return $transaction->total_debit != 0 || $transaction->total_credit != 0;
        // })->keys();

        // Fetch only the accounts that have non-zero transactions
        // $accounts = $accounts->whereIn('id', $filteredAccountIds);
        // $accounts = $accounts->latest()->get;

        foreach ($accounts as $account) {

            $accountId = $account->id;
            $debitTransactions = $transactions[$accountId]->total_debit ?? 0;
            $creditTransactions = $transactions[$accountId]->total_credit ?? 0;
            $balance = $debitTransactions - $creditTransactions;

            $trialBalance[$accountId] = [
                'account_name' => $account->name ?? '',
                'debit' => $debitTransactions ?? '',
                'credit' => $creditTransactions ?? '',
                'balance' => $balance ?? '',
            ];
        }

        if ($request->ajax()) {

            return view('financial-reports.trail-balance.table', compact('trialBalance', 'accounts', 'sort_by'));
        } else {

            return view('financial-reports.trail-balance.index', compact('trialBalance', 'accounts', 'sort_by'));
        }
    }

    // Profit and Loss Account
    public function profitAndLoss(Request $request)
    {

        $sort_by  = $request->get('sortby') ?? 10;
        $accounts = Account::select(['accounts.id', 'accounts.name'])
            ->leftJoin('accounting_groups', 'accounting_groups.id', '=', 'accounts.accounting_group_id')
            ->leftJoin('accounting_types', 'accounting_types.id', '=', 'accounting_groups.accounting_type_id')
            ->whereIn('accounting_types.code', ['INCOME', 'EXPENSES']);

        $accountIds = $accounts->pluck('id');

        // Fetch the sum of debit and credit transactions for the paginated accounts
        $transactions = AccountTransaction::select(
            'account_id',
            DB::raw('SUM(IF(transaction_type = "DEBIT", amount, 0)) as Expense'),
            DB::raw('SUM(IF(transaction_type = "CREDIT", amount, 0)) as Income')
        )
            ->whereIn('account_id', $accountIds)
            ->groupBy('account_id')
            ->get()
            ->keyBy('account_id');

        // Filter out accounts with zero transactions
        // $filteredAccountIds = $transactions->filter(function ($transaction) {
        //     return $transaction->Expense != 0 || $transaction->Income != 0;
        // })->keys();

        // Fetch only the accounts that have non-zero transactions
        // $accounts = $accounts->whereIn('accounts.id', $filteredAccountIds);
        $accounts = $accounts->latest('accounts.id')->paginate($sort_by);
        $profitAndLoss = [];

        foreach ($accounts as $account) {

            $accountId = $account->id;
            $expensesTransactions = $transactions[$accountId]->Expense ?? 0;
            $incomeTransactions = $transactions[$accountId]->Income ?? 0;

            $profitAndLoss[$accountId] = [
                'account_name' => $account->name ?? '',
                'expenses' => $expensesTransactions ?? '',
                'income' => $incomeTransactions ?? '',
            ];
        }

        if ($request->ajax()) {

            return view('financial-reports.profit-and-loss.table', compact('profitAndLoss', 'accounts', 'sort_by'));
        } else {

            return view('financial-reports.profit-and-loss.index', compact('profitAndLoss', 'accounts', 'sort_by'));
        }
    }

    // Balance Sheet
    public function balanceSheet(Request $request)
    {
        $sort_by  = $request->get('sortby') ?? 10;
        $accounts = Account::select(['accounts.id', 'accounts.name', 'accounting_types.name as type'])
            ->leftJoin('accounting_groups', 'accounting_groups.id', '=', 'accounts.accounting_group_id')
            ->leftJoin('accounting_types', 'accounting_types.id', '=', 'accounting_groups.accounting_type_id')
            ->whereIn('accounting_types.code', ['ASSETS', 'LIABILITIES', 'ENQUITY']);

        $accountIds = $accounts->pluck('id');

        // Fetch the sum of debit and credit transactions for the paginated accounts
        $transactions = AccountTransaction::select(
            'account_id',
            DB::raw('SUM(CASE WHEN transaction_type = "DEBIT" THEN amount ELSE 0 END) - SUM(CASE WHEN transaction_type = "CREDIT" THEN amount ELSE 0 END) AS Balance')
        )
            ->whereIn('account_id', $accountIds)
            ->groupBy('account_id')
            ->get()
            ->keyBy('account_id');

        $accounts = $accounts->latest('accounts.id')->paginate($sort_by);
        $balanceSheet = [];

        foreach ($accounts as $account) {

            $accountId = $account->id;
            $balanceTransactions = $transactions[$accountId]->Balance ?? 0;


            $balanceSheet[$accountId] = [
                'account_name' => $account->name ?? '',
                'Balance'      => $balanceTransactions ?? '',
                'type'         => $account->type ?? '',
            ];
        }

        if ($request->ajax()) {

            return view('financial-reports.balance-sheet.table', compact('balanceSheet', 'accounts', 'sort_by'));
        } else {
            return view('financial-reports.balance-sheet.index', compact('balanceSheet', 'accounts', 'sort_by'));
        }
    }


    public function trialBalance()
    {
        $accountingGroups = AccountingGroup::select(['id','name'])->with('accounts')->get();
        $trialBalance = [];

        foreach ($accountingGroups as $group) {
            $groupTotalDebit = 0;
            $groupTotalCredit = 0;

            foreach ($group->chart_of_expanses as $account) {
                $accountTotalDebit = 0;
                $accountTotalCredit = 0;

                AccountTransaction::where('account_id', $account->id)
                    ->select(
                        DB::raw("SUM(CASE WHEN transaction_type = 'DEBIT' THEN amount ELSE 0 END) as total_debit"),
                        DB::raw("SUM(CASE WHEN transaction_type = 'CREDIT' THEN amount ELSE 0 END) as total_credit")
                    )
                    ->chunk(1000, function ($transactions) use (&$accountTotalDebit, &$accountTotalCredit) {
                        foreach ($transactions as $transaction) {
                            $accountTotalDebit += $transaction->total_debit;
                            $accountTotalCredit += $transaction->total_credit;
                        }
                    });

                // $accountBalance = $accountTotalDebit - $accountTotalCredit;

                // $trialBalance[$group->name]['chart_of_expanses'][] = [
                //     'account_name' => $account->name,
                //     'total_debit' => $accountTotalDebit,
                //     'total_credit' => $accountTotalCredit,
                //     'balance' => $accountBalance
                // ];

                $groupTotalDebit += $accountTotalDebit;
                $groupTotalCredit += $accountTotalCredit;
            }

            $trialBalance[$group->name]['total_debit'] = $groupTotalDebit;
            $trialBalance[$group->name]['total_credit'] = $groupTotalCredit;
            $trialBalance[$group->name]['total_balance'] = $groupTotalDebit-$groupTotalCredit;
        }

        // dd($trialBalance);
        return view('financial-reports.trail-balance.index1', compact('trialBalance'));
    }
}
