<?php

namespace App\Http\Controllers\Admin\Accounting;

use App\Helpers\Helper;
use App\Http\Controllers\Controller;
use App\Models\Accounting\Journal;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class TrialBalanceController extends Controller
{

    public function getJournal(Request $request){
        $chart_of_acc_id = $request->chart_of_acc_id;
        $data = Journal::select(
            DB::raw("SUM(journals.credit-journals.debit) AS total_credit"),
            DB::raw("SUM(journals.debit-journals.credit) AS total_debit"),
            DB::raw("CONCAT(chart_of_accounts.chart_code,'-',chart_of_accounts.chart_name,'-',chart_of_accounts.desc) as chart_code_name"),
            'journals.chart_of_acc_id',
        )
        ->leftjoin('chart_of_accounts','chart_of_accounts.id','journals.chart_of_acc_id')
        ->where(function($query) use($chart_of_acc_id){
            if(!$chart_of_acc_id ==[]){
                $query->whereIn('journals.chart_of_acc_id',$chart_of_acc_id);
            }
        })
        ->where(function($query) use($request,$chart_of_acc_id){
            $query->whereIn('branch_id',Helper::byBranch());
            $query->where('currency_id',Helper::byCurrency());
            if(!$request->journal_date==[])
            {
                $query->whereBetween(DB::raw("DATE_FORMAT(journal_date, '%Y-%m-%d')"), $request->journal_date);
            }
        })
        ->groupBy('journals.chart_of_acc_id')
        ->orderBy('chart_of_accounts.chart_code', 'ASC');
        return $data;
    }

    public function getTrialBalance(Request $request)
    {
        //////////////////////////////////////////////// Assets ////////////////////////////////////////
        $assets = $this->getJournal($request)
        ->whereHas('chartOfAcc',function($query){
            $query->where('chart_code','LIKE' , '1%');
        })
        ->get()->map(function($value){
            return [
                'total' => $value->total_debit,
                'chart_code_name' => $value->chart_code_name,
            ];
        });

        //////////////////////////////////////////////// Liabilities ////////////////////////////////////////
        $liabilities = $this->getJournal($request)
        ->whereHas('chartOfAcc',function($query){
            $query->where('chart_code','LIKE' , '2%');
        })
        ->get()->map(function($value){
            return [
                'total' => $value->total_credit,
                'chart_code_name' => $value->chart_code_name,
            ];
        });

        //////////////////////////////////////////////// Equity ////////////////////////////////////////
        $equity = $this->getJournal($request)
        ->whereHas('chartOfAcc',function($query){
            $query->where('chart_code','LIKE' , '3%');
        })
        ->get()->map(function($value){
            return [
                'total' => $value->total_credit,
                'chart_code_name' => $value->chart_code_name,
            ];
        });

        //////////////////////////////////////////////// Income ////////////////////////////////////////
        $incomes = $this->getJournal($request)
            ->whereHas('chartOfAcc',function($query){
                $query->where('chart_code','LIKE' , '4%');
            })
            ->get()->map(function($value){
                return [
                    'total' => $value->total_credit,
                    'chart_code_name' => $value->chart_code_name,
                ];
            });

        //////////////////////////////////////////////// Expense ////////////////////////////////////////
        $expense = $this->getJournal($request)
        ->whereHas('chartOfAcc',function($query){
            $query->where('chart_code','LIKE' , '5%');
        })
        ->get()->map(function($value){
            return [
                'total' => $value->total_debit,
                'chart_code_name' => $value->chart_code_name,
            ];
        });

        return $data = array(
            'asset'       => $assets,
            'liabilities' => $liabilities,
            'equity'      => $equity,
            'income'      => $incomes,
            'expense'     => $expense,
        );
        return $data;
    }
}