<?php

namespace App\Http\Controllers\System;

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

class DashboardController extends Controller
{
    function __construct()
    {
        $this->middleware('permission:Dashboard', ['only' => ['getData','getJournal','getDataJournalByYearly']]);
    }
    public function getData(Request $request)
    {
        $dateS = Carbon::now()->startOfMonth();
        $dateE = Carbon::now()->endOfMonth();
        $now   = date('Y-m-d');
        $month = empty($request->month)?null:date('Y-m',strtotime($request->month));

        if($month)
        {
            $loan = Loan::where('currency_id',Helper::byCurrency())
            // ->whereIn('co_id',Helper::group())
            ->whereIn('branch_id',Helper::byBranch())
            ->when(request('month'), function ($query) use ($month){
                $query->where(DB::raw("DATE_FORMAT(loans.installment_date, '%Y-%m')"), $month);
            })
            ->whereHas('co', function ($query) {
                $query->where('status',1);
            })
            ->whereHas('client', function ($query) {
                $query->where('status',1);
            })->get();
        }
        else {
            $loan = Loan::where('currency_id',Helper::byCurrency())
                ->whereIn('branch_id',Helper::byBranch())
                // ->whereIn('co_id',Helper::group())
                ->whereHas('co', function ($query) {
                    $query->where('status',1);
                })
                ->whereHas('client', function ($query) {
                    $query->where('status',1);
                })->get();
        }

        $late = AccReceivables::where('currency_id',Helper::byCurrency())
            // ->whereIn('co_id',Helper::group())
            ->whereIn('branch_id',Helper::byBranch())
            ->whereIn('payment_status_id',[2,3])->where('payment_date','<',$now)
            ->whereHas('co', function ($query) {
                $query->where('status',1);
            })
            ->whereHas('client', function ($query) {
                $query->where('status',1);
            })
            ->groupBy('client_id')->get()
            ->count();

        $today = Loan::where('currency_id',Helper::byCurrency())
            // ->whereIn('co_id',Helper::group())
            ->whereIn('branch_id',Helper::byBranch())
            ->whereNotIn('loan_status_id',[1,4])
            ->where('installment_date',$now)
            ->count();

        $now  = date('Y-m-d');
        $date = [];
        $NetProfitLoss = [];
        $ArrayDetail   = [];
        $ArrayLoan     = [];
        $toDate        = Carbon::parse($now)->subDay(5)->format('Y-m-d');
        while( $toDate != $now ){
            $toDate = Carbon::parse($toDate)->addDay(1)->format('Y-m-d');
            // $calculator = AccReceivableDetails::where('currency_id',Helper::byCurrency())->whereIn('co_id',Helper::group())->whereIn('branch_id',Helper::byBranch())->where('received_date',$toDate)->sum('received');
            $calculator = AccReceivableDetails::where('currency_id',Helper::byCurrency())->whereIn('branch_id',Helper::byBranch())->where('received_date',$toDate)->sum('received');
            $ArrayDetail[]= [
                Carbon::parse($toDate)->format('d-M-Y'),$calculator
            ];
            // $loanOut = Loan::where('currency_id',Helper::byCurrency())->whereIn('co_id',Helper::group())->whereIn('branch_id',Helper::byBranch())->where('installment_date',$toDate)->whereIn('loan_status_id',[2,3])->sum('loan_out');
            $loanOut = Loan::where('currency_id',Helper::byCurrency())->whereIn('branch_id',Helper::byBranch())->where('installment_date',$toDate)->whereIn('loan_status_id',[2,3])->sum('loan_out');
            $ArrayLoan[]=
            [
                Carbon::parse($toDate)->format('d-M-Y'),$loanOut
            ];

            $chartIncome = Journal::select(
                        DB::raw("SUM(journals.debit-journals.credit) AS total"),
                        'journals.chart_of_acc_id',
                    )
                    ->whereHas('chartOfAcc',function($query){
                        $query->where('chart_code','LIKE' , '4%');
                        $query->where('chart_code','!=' ,'40001');
                    })
                    ->where('journal_date',$toDate)
                    ->where('journals.currency_id',Helper::byCurrency())
                    ->whereIn('journals.branch_id',Helper::byBranch())
                    ->leftjoin('chart_of_accounts','chart_of_accounts.id','journals.chart_of_acc_id')
                    ->orderBy('chart_of_accounts.chart_code', 'ASC')
                    ->get()->map(function($value){
                        return [
                            'total' => -$value->total,
                        ];
                    });
            $chartExpense = Journal::select(
                        DB::raw("SUM(journals.debit-journals.credit) AS total"),
                        'journals.chart_of_acc_id',
                    )
                    ->whereHas('chartOfAcc',function($query){
                        $query->where('chart_code','LIKE' ,'5%');
                        $query->where('chart_code','!=' ,'50001');
                    })
                    ->where('journal_date',$toDate)
                    ->where('journals.currency_id',Helper::byCurrency())
                    ->whereIn('journals.branch_id',Helper::byBranch())
                    ->leftjoin('chart_of_accounts','chart_of_accounts.id','journals.chart_of_acc_id')
                    ->orderBy('chart_of_accounts.chart_code', 'ASC')

                    ->get()->map(function($value){
                        return [
                            'total' => $value->total,
                        ];
                    });
            $NetProfitLoss[]=
            [
                Carbon::parse($toDate)->format('d-M-Y'),
                $chartIncome[0]['total']??0,
                $chartExpense[0]['total']??0,
            ];

        }
        $incomes = $this->getJournalDashBoard($month)
            ->whereHas('chartOfAcc',function($query){
                $query->where('chart_code','LIKE' , '4%');
                $query->where('chart_code','!=' ,'40001');
            })
            ->get()->map(function($value){
                return [
                    'total' => -$value->total,
                    'chart_code' => $value->chartOfAcc->chart_code??'',
                    'chart_name' => ($value->chartOfAcc->chart_name??'').' '.($value->chartOfAcc->desc??''),
                ];
            });

        $generalExep = $this->getJournalDashBoard($month)
            ->whereHas('chartOfAcc',function($query){
                $query->where('chart_code','LIKE' ,'5%');
                $query->where('chart_code','!=' ,'50001');
            })
            ->get()->map(function($value){
                return [
                    'total' => $value->total,
                    'chart_code' => $value->chartOfAcc->chart_code??'',
                    'chart_name' => ($value->chartOfAcc->chart_name??'').' '.($value->chartOfAcc->desc??''),
                ];
            });

        $net_profit = 0;
        $total_income  = 0;
        $total_expense = 0;

        foreach ($incomes as  $income) {
            $total_income += $income["total"];
        }
        foreach ($generalExep as  $expense) {
            $total_expense += $expense["total"];
        }
        //Net Income
        $net_profit = $total_income  - $total_expense;

        // if($month)
        // {
        //     $balance_principal = Loan::whereIn('loan_status_id',[2,5])->where('currency_id',Helper::byCurrency())->whereIn('branch_id',Helper::byBranch())->when(request('month'), function ($query) use ($month){
        //         $query->where(DB::raw("DATE_FORMAT(loans.installment_date, '%Y-%m')"),'>=', $month);
        //     })->sum('balance_principal');
        // }else{
        //     $balance_principal = Loan::whereIn('loan_status_id',[2,5])->where('currency_id',Helper::byCurrency())->whereIn('branch_id',Helper::byBranch())
        //                         ->sum('balance_principal');
        // }
        $balance_principal = Journal::select(
            DB::raw("SUM(journals.debit-journals.credit) AS total"),
        )
        ->where(function($query) use ($request, $month,$dateS,$dateE){
            $query->whereIn('branch_id',Helper::byBranch());
            $query->where('currency_id',Helper::byCurrency());
            if($month) {
                $query->where(DB::raw("DATE_FORMAT(journal_date, '%Y-%m')"),'<=', $month);
            }
        })
        ->whereHas('chartOfAcc',function($query){
            $query->where('chart_code','LIKE' , '10006');
        })
        ->first()->total;

        $totalClient = Loan::where('currency_id',Helper::byCurrency())
            ->whereIn('branch_id',Helper::byBranch())
            // ->whereIn('co_id',Helper::group())
            ->whereIn('loan_status_id',[2])
            ->when(request('month'), function ($query) use ($month){
                if($month){
                    $query->where(DB::raw("DATE_FORMAT(loans.installment_date, '%Y-%m')"),'<=', $month);
                }
            })
            ->whereHas('co', function ($query) {
                $query->where('status',1);
            })
            ->whereHas('client', function ($query) {
                $query->where('status',1);
            })
            ->count();

        $currency = Helper::byCurrency();

        return [
            'late'               => $late,
            'today'              => $today,
            'ArrayDetail'        => $ArrayDetail,
            'ArrayLoan'          => $ArrayLoan,
            'client'             => $totalClient,
            'active'             => $loan->where('loan_status_id',2)->count(),
            'completed'          => $loan->where('loan_status_id',3)->count(),
            'loanOut'            => $loan->whereIn('loan_status_id',[2,3])->sum('loan_out'),
            'paidPrincipal'      => $loan->whereIn('loan_status_id',[2,3])->sum('paid_principal'),
            'totalReceived'      => $loan->whereIn('loan_status_id',[2,3])->sum('loan_in'),
            'total_income'       => $total_income,
            'total_expense'      => $total_expense,
            'net_profit'         => $net_profit,
            // 'balance_principal'  => $balance_principal,
            'balance_principal'  => $loan->where('loan_status_id',2)->sum('balance_principal'),
            'currency'           => $currency,
            'NetProfitLoss'      => $NetProfitLoss,
        ];
    }

    public function getJournalDashBoard($month){
        $dateS = Carbon::now()->startOfMonth();
        $dateE = Carbon::now()->endOfMonth();
        $month = empty($month)?null:date('Y-m',strtotime($month));
        $journal = Journal::select(
            DB::raw("SUM(journals.debit-journals.credit) AS total"),
            'journals.chart_of_acc_id',
        )
        ->leftjoin('chart_of_accounts','chart_of_accounts.id','journals.chart_of_acc_id')
        ->where(function($query){
            $query->whereIn('branch_id',Helper::byBranch());
            $query->where('currency_id',Helper::byCurrency());
        })
        ->groupBy('journals.chart_of_acc_id')
        ->orderBy('chart_of_accounts.chart_code', 'ASC');
        if($month)
        {
            $data= $journal->when(request('month'), function ($query) use ($month){
                $query->where(DB::raw("DATE_FORMAT(journal_date, '%Y-%m')"), $month);
            });
        }else{
            $data= $journal;
        }

        return $data;
    }

    private function getJournal()
    {
        $data = Journal::select(
            DB::raw("SUM(journals.debit-journals.credit) AS total"),
            'journals.chart_of_acc_id',
        )
        ->where('journals.currency_id',Helper::byCurrency())
        ->whereIn('journals.branch_id',Helper::byBranch())
        ->leftjoin('chart_of_accounts','chart_of_accounts.id','journals.chart_of_acc_id')
        ->groupBy('journals.chart_of_acc_id')
        ->orderBy('chart_of_accounts.chart_code', 'ASC');
        return $data;
    }

    public function getDataJournalByYearly(Request $request)
    {
        $debit = $this->getJournal($request)
            ->whereHas('chartOfAcc',function($query){
                $query->where('chart_code','LIKE' , '1%');
                $query->orwhere('chart_code','LIKE' , '5%');
            })
            ->get()->map(function($value){
                return [
                    'total' => $value->total,
                    'chart_name' => $value->chartOfAcc->chart_name??'',
                ];
            })->pluck('total', 'chart_name');

        $credit = $this->getJournal($request)
            ->whereHas('chartOfAcc',function($query){
                $query->where('chart_code','LIKE' , '2%');
                $query->orwhere('chart_code','LIKE' , '3%');
                $query->orwhere('chart_code','LIKE' , '4%');
            })
            ->get()->map(function($value){
                return [
                    'total' => -$value->total,
                    'chart_name' => $value->chartOfAcc->chart_name??'',
                ];
            })->pluck('total', 'chart_name');
        $arr = array_merge($debit->toArray(), $credit->toArray());
        $data = [
            'jouranls' => $arr,
        ];
        return $data;
    }
}
