<?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('loan', function ($query) {
                $query->where('loan_status_id',2);
            })
            ->whereHas('co', function ($query) {
                $query->where('status',1);
            })
            ->whereHas('client', function ($query) {
                $query->where('status',1);
            })
            ->groupBy('client_id')->get()
            ->count();

            

        $amount_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('loan', function ($query) {
                $query->where('loan_status_id',2);
            })
            ->whereHas('co', function ($query) {
                $query->where('status',1);
            })
            ->whereHas('client', function ($query) {
                $query->where('status',1);
            })
            ->whereNull('deleted_at')
            // ->groupBy('client_id')
            ->get()
            ->sum('unreceived');

        $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();

        $loan_out_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)
            ->sum('loan_out');

        $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');
            $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');
            $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');
                        $query->whereHas('typeAccDetails',function($sub_query){
                            $sub_query->where('type_of_acc_id',50);
                        });
                    })
                    ->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');
                        $query->whereHas('typeAccDetails',function($sub_query){
                            $sub_query->where('type_of_acc_id',60);
                        });
                    })
                    ->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??''),
        //         ];
        //     });

        $incomes = $this->getJournalDashBoard($request)
            ->whereHas('chartOfAcc',function($query){
                $query->whereHas('typeAccDetails',function($sub_query){
                    $sub_query->where('type_of_acc_id',50);
                });
            })
            ->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??''),
        //         ];
        //     });
        $generalExep = $this->getJournalDashBoard($request)
            ->whereHas('chartOfAcc',function($query){
                $query->whereHas('typeAccDetails',function($sub_query){
                    $sub_query->where('type_of_acc_id',60);
                });
            })->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();

        $total_client_room = 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);
                $query->where('accommodation',2);
            })
            ->count();

        $balance_principal_client_room = 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);
                $query->where('accommodation',2);
            })
            ->get()
            ->sum('balance_principal');

        $balance_principal = $loan->where('loan_status_id',2)->sum('balance_principal');

        return [
            'late'               => $late,
            'amount_late'        => $amount_late??0,
            'today'              => $today,
            'loan_out_today'     => $loan_out_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??0,
            'currency'           => $currency,
            'NetProfitLoss'      => $NetProfitLoss,

            'total_client_room'         => $total_client_room??0,
            'total_amount_client_room'  => $balance_principal_client_room??0,
            'total_percent_client_room' => $balance_principal_client_room>0?round(($balance_principal_client_room/$balance_principal)*100,2):0,
        ];
    }

    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->whereHas('typeAccDetails',function($sub_query){
                    $sub_query->where('type_of_acc_id',10);
                    $sub_query->where('type_of_acc_id',60);
                });
                // $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->whereHas('typeAccDetails',function($sub_query){
                    $sub_query->where('type_of_acc_id',30);
                    $sub_query->where('type_of_acc_id',40);
                    $sub_query->where('type_of_acc_id',50);
                });
                // $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;
    }
}