<?php

namespace App\Http\Controllers\Admin\Accounting;

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

class GeneralLedgerController extends Controller
{
    
    public function groupChartAcc(Request $request)
    {
        $chart_of_acc_id = $request->chart_of_acc_id;
        $data = Journal::leftjoin('chart_of_accounts','chart_of_accounts.id','journals.chart_of_acc_id')
        ->leftjoin('type_of_acc_details','type_of_acc_details.id','chart_of_accounts.type_of_acc_detail_id')
        ->groupby('journals.chart_of_acc_id')
        ->select('journals.chart_of_acc_id','journals.journal_date')
        ->whereIn('journals.branch_id',Helper::byBranch())
        ->where('journals.currency_id',Helper::byCurrency())
        ->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('journals.invoice_number', 'LIKE',isset($request->invoice_number)? $request->invoice_number:NULL)
        ->where(function($query) use($request){
            if(isset($request->journal_date)){
                $query->whereBetween(DB::raw("DATE_FORMAT(journal_date, '%Y-%m-%d')"), $request->journal_date ?? CustomDateFilter::thisMonth());
            }
        });
        return $data;
    }

    public function funGetJournal(Request $request)
    {
        $data = Journal::leftjoin('chart_of_accounts','chart_of_accounts.id','journals.chart_of_acc_id')
        ->leftjoin('users','users.id','journals.created_by')
        ->select(
            'journals.debit',
            'journals.credit',
            'journals.journal_date',
            'journals.desc',
            'journals.invoice_number',
            'users.name as created_by',
            DB::raw("CONCAT(chart_of_accounts.chart_code,'-',chart_of_accounts.chart_name) as chart_code_name"),
        )
        ->whereIn('journals.branch_id',Helper::byBranch())
        ->where('journals.currency_id',Helper::byCurrency())
        ->where('journals.invoice_number', 'LIKE',isset($request->invoice_number)? $request->invoice_number:NULL)
        ->where(function($query) use($request){
            if(isset($request->journal_date)){
                $query->whereBetween(DB::raw("DATE_FORMAT(journal_date, '%Y-%m-%d')"), $request->journal_date ?? CustomDateFilter::thisMonth());
            }
        });
        return $data;
    }

    public function journalSumAmount(Request $request)
    {
        $data = Journal::leftjoin('chart_of_accounts','chart_of_accounts.id','journals.chart_of_acc_id')
        ->where('journals.invoice_number', 'LIKE',isset($request->invoice_number)? $request->invoice_number:NULL)
        ->whereIn('journals.branch_id',Helper::byBranch())
        ->where('journals.currency_id',Helper::byCurrency())
        ->where(function($query) use($request){
            if(isset($request->journal_date)){
                $query->whereBetween(DB::raw("DATE_FORMAT(journal_date, '%Y-%m-%d')"), $request->journal_date ?? CustomDateFilter::thisMonth());
            }
        });
        return $data;
    }

    public function getGeneralLedger(Request $request)
    {
        //////////////////////////////////////////////// Assets ////////////////////////////////////////
        $group_asset = $this->groupChartAcc($request)
        ->where('type_of_acc_details.type_of_acc_id',10)
        ->get();

        foreach($group_asset as $key => $value){
            $asset = $this->funGetJournal($request)
            ->where('journals.chart_of_acc_id',$value->chart_of_acc_id)
            ->get();
            $value->array_asset = $asset;

            $total_asset = $this->journalSumAmount($request)
            ->select(
                DB::raw("SUM(journals.debit-journals.credit) AS total_debit"),
            )
            ->where('journals.chart_of_acc_id',$value->chart_of_acc_id)
            ->get();
            $value->total = $total_asset;
        }

        //////////////////////////////////////////////// Liabilities ///////////////////////////////////
        $group_liabilities = $this->groupChartAcc($request)
        ->where('type_of_acc_details.type_of_acc_id',20)
        ->get();

        foreach($group_liabilities as $key => $value){
            $liabilities = $this->funGetJournal($request)
            ->where('journals.chart_of_acc_id',$value->chart_of_acc_id)
            ->get();
            $value->array_liabilities = $liabilities;

            $total_lb = $this->journalSumAmount($request)
            ->select(
                DB::raw("SUM(journals.credit-journals.debit) AS total_credit"),
            )
            ->where('journals.chart_of_acc_id',$value->chart_of_acc_id)
            ->get();
            $value->total = $total_lb;
        }
        //////////////////////////////////////////////// Equity ///////////////////////////////////
        $group_equity = $this->groupChartAcc($request)
        ->where('type_of_acc_details.type_of_acc_id',30)
        ->get();

        foreach($group_equity as $key => $value){
            $equity = $this->funGetJournal($request)
            ->where('journals.chart_of_acc_id',$value->chart_of_acc_id)
            ->get();
            $value->array_equity = $equity;

            $total_eq = $this->journalSumAmount($request)
            ->select(
                DB::raw("SUM(journals.credit-journals.debit) AS total_credit"),
            )
            ->where('journals.chart_of_acc_id',$value->chart_of_acc_id)
            ->get();
            $value->total = $total_eq;
        }

        //////////////////////////////////////////////// Income ///////////////////////////////////
        $group_income = $this->groupChartAcc($request)
        ->where('type_of_acc_details.type_of_acc_id',40)
        ->get();

        foreach($group_income as $key => $value){
            $income = $this->funGetJournal($request)
            ->where('journals.chart_of_acc_id',$value->chart_of_acc_id)
            ->get();
            $value->array_income = $income;

            $total_lb = $this->journalSumAmount($request)
            ->select(
                DB::raw("SUM(journals.credit-journals.debit) AS total_credit"),
            )
            ->where('journals.chart_of_acc_id',$value->chart_of_acc_id)
            ->get();
            $value->total = $total_lb;
        }

        //////////////////////////////////////////////// Expense ////////////////////////////////////////
        $group_expense = $this->groupChartAcc($request)
        ->where('type_of_acc_details.type_of_acc_id',50)
        ->get();

        foreach($group_expense as $key => $value){
            $expense = $this->funGetJournal($request)
            ->where('journals.chart_of_acc_id',$value->chart_of_acc_id)
            ->get();
            $value->array_expense = $expense;

            $total_expense = $this->journalSumAmount($request)
            ->select(
                DB::raw("SUM(journals.debit-journals.credit) AS total_debit"),
            )
            ->where('journals.chart_of_acc_id',$value->chart_of_acc_id)
            ->get();
            $value->total = $total_expense;
        }

        return $data = array(
            'asset'       => $group_asset,
            'liabilities' => $group_liabilities,
            'equity'      => $group_equity,
            'income'      => $group_income,
            'expense'     => $group_expense,
        );
        return $data;
    }

}