<?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',
            'chart_of_accounts.chart_code',
            'chart_of_accounts.chart_name'
        )
        ->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->whereHas('typeAccDetails',function($sub_query){
                $sub_query->where('type_of_acc_id',10);
            });
        })
        ->get()
        ->filter(function($value) {
            return $value->total_debit != 0;
        })
        ->map(function($value){
            return [
                'total_debit' => $value->total_debit,
                'total_credit' => 0,
                'chart_code_name' => $value->chart_code_name,
                'chart_code' => $value->chart_code,
                'chart_name' => $value->chart_name
            ];
        })->toArray();

        //////////////////////////////////////////////// Liabilities ////////////////////////////////////////
        $liabilities = $this->getJournal($request)
        ->whereHas('chartOfAcc',function($query){
            $query->whereHas('typeAccDetails',function($sub_query){
                $sub_query->where('type_of_acc_id',20);
            });
        })
        ->get()
        ->filter(function($value) {
            return $value->total_credit != 0;
        })
        ->map(function($value){
            return [
                'total_debit' => 0,
                'total_credit' => $value->total_credit,
                'chart_code_name' => $value->chart_code_name,
                'chart_code' => $value->chart_code,
                'chart_name' => $value->chart_name
            ];
        })->toArray();

        //////////////////////////////////////////////// Equity ////////////////////////////////////////
        $equity = $this->getJournal($request)
        ->whereHas('chartOfAcc',function($query){
            $query->whereHas('typeAccDetails',function($sub_query){
                $sub_query->where('type_of_acc_id',30);
            });
        })
        ->get()
        ->filter(function($value) {
            return $value->total_credit != 0;
        })
        ->map(function($value){
            return [
                'total_debit' => 0,
                'total_credit' => $value->total_credit,
                'chart_code_name' => $value->chart_code_name,
                'chart_code' => $value->chart_code,
                'chart_name' => $value->chart_name
            ];
        })->toArray();

        //////////////////////////////////////////////// Income ////////////////////////////////////////
        $incomes = $this->getJournal($request)
            ->whereHas('chartOfAcc',function($query){
                $query->whereHas('typeAccDetails',function($sub_query){
                    $sub_query->where('type_of_acc_id',40);
                });
            })
            ->get()
        ->filter(function($value) {
            return $value->total_credit != 0;
        })
        ->map(function($value){
                return [
                    'total_debit' => 0,
                    'total_credit' => $value->total_credit,
                    'chart_code_name' => $value->chart_code_name,
                    'chart_code' => $value->chart_code,
                    'chart_name' => $value->chart_name
                ];
            })->toArray();

        //////////////////////////////////////////////// Expense ////////////////////////////////////////
        $expense = $this->getJournal($request)
        ->whereHas('chartOfAcc',function($query){
            $query->whereHas('typeAccDetails',function($sub_query){
                $sub_query->where('type_of_acc_id',50);
            });
        })
        ->get()
        ->filter(function($value) {
            return $value->total_debit != 0;
        })
        ->map(function($value){
            return [
                'total_debit' => $value->total_debit,
                'total_credit' => 0,
                'chart_code_name' => $value->chart_code_name,
                'chart_code' => $value->chart_code,
                'chart_name' => $value->chart_name
            ];
        })->toArray();

        $data = array_merge($assets,$liabilities,$equity,$incomes,$expense);
        return $data;
    }
 
}