<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Inertia\Inertia;
use Illuminate\Support\Facades\DB;
use App\Models\Ledger;
use App\Models\LedgerCategory;
use App\Models\BankAccount;
use App\Models\SubBankAccount;
use App\Models\LastBankAccount;
use App\Models\MoreAccountBelow;
use App\Models\Beneficiary;
use App\Models\GeneralLedger;
use App\Traits\LogActivityTrait;
use Maatwebsite\Excel\Facades\Excel;
use Illuminate\Support\Facades\Validator;
use App\Imports\ImportBeneficieriesExcelFile;
use App\Models\BeneficiaryPayments;
use App\Models\District;
use App\Models\ExpenseDetails;
use Auth;
use Illuminate\Support\Facades\Crypt;
use App\Models\ExpenseSummary;


class BeneficieriesController extends Controller
{

    use LogActivityTrait;

    public function __construct()
    {
        $this->middleware('auth');
    }

    public function index() {
        $all_accounts = array();
        $bank_accounts = DB::table('bank_accounts')->select('id', 'name', 'account_number')->where('account_number', '321108')->get();
        $sub_bank_accounts = DB::table('sub_bank_accounts')->select('id', 'name', 'account_number')->where('account_number', '321108')->get();
        $last_bank_accounts = DB::table('last_bank_accounts')->select('id', 'name', 'account_number')->where('account_number', '321108')->get();
        $more_account_belows = DB::table('more_account_belows')->select('id', 'name', 'account_number')->where('account_number', '321108')->get();

        // array_push($all_accounts, $bank_accounts);
        // array_push($all_accounts, $sub_bank_accounts);
        // array_push($all_accounts, $last_bank_accounts);
        // array_push($all_accounts, $more_account_belows);


        foreach ($bank_accounts as $value) {
            $data1 = array(
                // 'id' => $value->id,
                'name' => $value->name.' - '.$value->account_number,
                'account_number' => $value->account_number,
            );
            array_push($all_accounts, $data1);
        }
        foreach ($sub_bank_accounts as $value) {
            $data2 = array(
                // 'id' => $value->id,
                'name' => $value->name.' - '.$value->account_number,
                'account_number' => $value->account_number,
            );
            array_push($all_accounts, $data2);
        }
        foreach ($last_bank_accounts as $value) {
            $data3 = array(
                // 'id' => $value->id,
                'name' => $value->name.' - '.$value->account_number,
                'account_number' => $value->account_number,
            );
            array_push($all_accounts, $data3);
        }

        foreach ($more_account_belows as $value) {
            $data4 = array(
                // 'id' => $value->id,
                'name' => $value->name.' - '.$value->account_number,
                'account_number' => $value->account_number,
            );
            array_push($all_accounts, $data4);
        }

        return Inertia::render('ChartOfAccounts/UploadBeneficiariesComponent', [
            'all_accounts' => $all_accounts,
        ]);
       // return Inertia::render('ChartOfAccounts/UploadBeneficiariesComponent');

        // return view('upload');
    }

    public function expense() {
        $all_accounts = array();
        $bank_accounts = DB::table('bank_accounts')->select('id', 'name', 'account_number')->get();
        $sub_bank_accounts = DB::table('sub_bank_accounts')->select('id', 'name', 'account_number')->get();
        $last_bank_accounts = DB::table('last_bank_accounts')->select('id', 'name', 'account_number')->get();
        $more_account_belows = DB::table('more_account_belows')->select('id', 'name', 'account_number')->get();

        // array_push($all_accounts, $bank_accounts);
        // array_push($all_accounts, $sub_bank_accounts);
        // array_push($all_accounts, $last_bank_accounts);
        // array_push($all_accounts, $more_account_belows);


        foreach ($bank_accounts as $value) {
            $data1 = array(
                // 'id' => $value->id,
                'name' => $value->name.' - '.$value->account_number,
                'account_number' => $value->account_number,
            );
            array_push($all_accounts, $data1);
        }
        foreach ($sub_bank_accounts as $value) {
            $data2 = array(
                // 'id' => $value->id,
                'name' => $value->name.' - '.$value->account_number,
                'account_number' => $value->account_number,
            );
            array_push($all_accounts, $data2);
        }
        foreach ($last_bank_accounts as $value) {
            $data3 = array(
                // 'id' => $value->id,
                'name' => $value->name.' - '.$value->account_number,
                'account_number' => $value->account_number,
            );
            array_push($all_accounts, $data3);
        }

        foreach ($more_account_belows as $value) {
            $data4 = array(
                // 'id' => $value->id,
                'name' => $value->name.' - '.$value->account_number,
                'account_number' => $value->account_number,
            );
            array_push($all_accounts, $data4);
        }

        return Inertia::render('ChartOfAccounts/UploadExpensesComponent', [
            'all_accounts' => $all_accounts,
        ]);
       // return Inertia::render('ChartOfAccounts/UploadBeneficiariesComponent');

        // return view('upload');
    }

    public function addBenficiaryLedgerEntries($amount,  $credit_account, $name, $debit_account, $no_of_beneficiaries, $ent_amount, $service_fee, $withdraw,$quarter,$paydate,$fy) {


        $get_last_reference_number = DB::table('general_ledgers')->select('general_ledgers.*')
            ->latest('general_ledgers.reference')->first();

        if ($get_last_reference_number) {
            $defaut_journal_reference_code = $get_last_reference_number->reference;
            $finalcode = str_pad(intval($defaut_journal_reference_code) + 1, strlen($defaut_journal_reference_code), '0', STR_PAD_LEFT);
        } else {
            $initialreferencecode = '0000001';
            $finalcode = $initialreferencecode;
        }

        //GET DATE

        $totalAmount = $ent_amount + $service_fee + $withdraw;

        $date = intval($paydate);
        $time = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($date)->format('Y-m-d');
        $final_pay_date = date('Y-m-d', strtotime($time));

        //DEBIT ACCOUNT
        $general_ledger1 = GeneralLedger::create([
            'details' => 'Beneficiary Payment For '.$name,
            'account' => $debit_account,
            'debit' => $totalAmount,
            'transaction_date' => date('Y-m-d h:i:s'),
            'reference' => $finalcode,
            'quarter' => $quarter,
            'pay_date' => $final_pay_date,
            'fy' => $fy,

        ]);

        //CREDIT ACCOUNT

        if ($general_ledger1) {
            GeneralLedger::create([
                'details' => 'Beneficiary Payment For '.$name,
                'account' => $credit_account,
                'credit' => $totalAmount,
                'transaction_date' => date('Y-m-d h:i:s'),
                'reference' => $finalcode,
                'quarter' => $quarter,
                'pay_date' => $final_pay_date,
                'fy' => $fy,
            ]);
        }


        // RECORD THE PAYMENT

        BeneficiaryPayments::create([
            'district' => $name,
            'no_of_beneficiaries' => $no_of_beneficiaries,
            'ent_amount' => $ent_amount,
            'service_fee' => $service_fee,
            'withdraw_charge' => $withdraw,
            'total' => $totalAmount,
            'quarter' => $quarter,
            'pay_date' => $final_pay_date,
            'fy' => $fy,
            'reference' => $finalcode,
            'created_by' => Auth::user()->id,
        ]);
    }



    public function handleUploadingOfBeneficiaries(Request $request) {

        //return $request->item_code;

        $rows = Excel::toArray(new ImportBeneficieriesExcelFile, $request->uploaded_attachment);

        for ($i = 0; $i < count($rows) ; $i++) {

            $row = $rows[0];
        }

        for ($i=0; $i < count($row) ; $i++) {

            if($i > 1) {

                // CHECK IF DISTRICT  HAS BEEN REGISTERED BEFORE
                $district = District::select('id',
                'name')->where('name', 'like', '%'.$row[$i][0].'%' )->first();

                if($district == NULL) {
                    // CREATE NEW DISTRICT
                    $user = District::create([
                        'name' => $row[$i][0]
                    ]);

                    // //Get the last created sub account under a sub account
                    $last_account = DB::table('more_account_belows')
                        ->select('more_account_belows.id', 'more_account_belows.created_at',
                        'more_account_belows.account_below_bank_account_id', 'more_account_belows.account_number')
                        ->latest('more_account_belows.id')
                        ->where('more_account_belows.account_below_bank_account_id', 18)
                        ->first();

                    //Get information about a given sub account
                    $sub_account = LastBankAccount::select('id', 'account_number')->where('id', 18)->first();
                    $amount = (int)  $row[$i][5];


                    if ($last_account != NULL) {
                        //Get last two digits of item code
                        $new_string = substr($last_account->account_number, -2);
                        //convert string to integer
                        $integer_string = (int) $new_string;

                        if ($integer_string <= 9) {
                            $new_last_digit = $integer_string + 1;
                            $code = (string) $new_last_digit;
                            $intact_code = '0' . $code;
                            $new_item_code = $sub_account->account_number . $intact_code;
                        } else {
                            $new_last_digit = $integer_string + 1;
                            $code = (string) $new_last_digit;
                            $new_item_code = $sub_account->account_number  . $code;
                        }

                        $created_account = MoreAccountBelow::create([
                            'account_below_bank_account_id' => 18,
                            'name' => ucwords($row[$i][0]),
                            'account_number' => $new_item_code
                        ]);
                        $subject = 'Creating a District account';
                        $details = 'Creating account with name : ' . $row[$i][0];
                        $this->addToLog($subject, $details);

                        if($created_account) {
                            $select_district = District::select('id',
                            'name', 'coa_code')->where('name', 'like', '%'.$row[$i][0].'%' )->first();
                            $select_district->coa_code = $created_account->account_number;
                            $select_district->save();
                        }
                        $this->addBenficiaryLedgerEntries($amount, $request->item_code, $select_district->name, $select_district->coa_code,
                        $row[$i][1],$row[$i][2],$row[$i][3],$row[$i][4],$row[$i][6],$row[$i][7],$row[$i][8]);

                    } else {
                        $new_item_code = $sub_account->account_number . '01';
                        $created_account = MoreAccountBelow::create([
                            'account_below_bank_account_id' => 18,
                            'name' => ucwords($row[$i][1]),
                            'account_number' => $new_item_code
                        ]);
                        $subject = 'Creating a District account';
                        $details = 'Creating account with name : ' . $row[$i][0];
                        $this->addToLog($subject, $details);

                        if($created_account) {
                            $select_district = District::select('id',
                            'name', 'coa_code')->where('name', 'like', '%'.$row[$i][0].'%')->first();
                            $select_district->coa_code = $created_account->account_number;
                            $select_district->save();
                        }
                        $this->addBenficiaryLedgerEntries($amount, $request->item_code, $select_district->name, $select_district->coa_code,
                        $row[$i][1],$row[$i][2],$row[$i][3],$row[$i][4],$row[$i][6],$row[$i][7],$row[$i][8]);
                    }

                }else {

                    // IF DISTRICT HAS ALREADY BEEN REGISTERED
                    $amount = (int)  $row[$i][5];
                    //GET DISTRICT ACCOUNT CODE  FROM CHART OF ACCOUNTS
                    $select_district = District::select('id',
                     'name', 'coa_code')->where('name', 'like', '%'.$row[$i][0].'%')->first();

                    $this->addBenficiaryLedgerEntries($amount, $request->item_code, $select_district->name, $select_district->coa_code,
                     $row[$i][1],$row[$i][2],$row[$i][3],$row[$i][4],$row[$i][6],$row[$i][7],$row[$i][8]);

                }
            }
        }

        return response()->json([
            'success' => true,
            'message' => 'Sucessfully Add Ledger Entries',
        ], 200);
    }

    public function addExpenseLedgerEntries($amount, $credit_account, $debit_account,  $date_value, $account_no, $account_name, $bank, $fy, $quarter, $summary_id) {


        $get_last_reference_number = DB::table('general_ledgers')->select('general_ledgers.*')
            ->latest('general_ledgers.reference')->first();

        if ($get_last_reference_number) {
            $defaut_journal_reference_code = $get_last_reference_number->reference;
            $finalcode = str_pad(intval($defaut_journal_reference_code) + 1, strlen($defaut_journal_reference_code), '0', STR_PAD_LEFT);
        } else {
            $initialreferencecode = '0000001';
            $finalcode = $initialreferencecode;
        }

        $date = intval($date_value);
        $time = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($date)->format('Y-m-d');
        $final_pay_date = date('Y-m-d', strtotime($time));

        //DEBIT ACCOUNT
        $general_ledger1 = GeneralLedger::create([
            'details' => 'Payment for'.' '.$account_name ,
            'account' => $debit_account,
            'debit' => $amount,
            'transaction_date' => date('Y-m-d h:i:s'),
            'reference' => $finalcode,
            'fy' => $fy,
            'pay_date' => $final_pay_date,

        ]);

        //CREDIT ACCOUNT

        if ($general_ledger1) {
            GeneralLedger::create([
                'details' => 'Payment for'.' '.$account_name ,
                'account' => $credit_account,
                'credit' => $amount,
                'transaction_date' => date('Y-m-d h:i:s'),
                'reference' => $finalcode,
                'fy' => $fy,
                'pay_date' => $final_pay_date,
            ]);
        }

        // Create

        ExpenseDetails::create([
            'account_number' => $account_no,
            'amount' => $amount,
            'account_details' => $account_name,
            'bank' => $bank,
            'item_code' => $debit_account,
            'value_date' => $final_pay_date,
            'quarter' => $quarter,
            'fy' => $fy,
            'reference' => $finalcode,
            'created_by' => Auth::user()->id,
            'expense_id' => $summary_id
        ]);


    }

    public function handleUploadingOfExpenses(Request $request) {

        //return $request->item_code;

        $summary = ExpenseSummary::create(
            [
                'amount' => $request->amount,
                'requested_by' => $request->requested_by,
            ]
            );

        $rows = Excel::toArray(new ImportBeneficieriesExcelFile, $request->uploaded_attachment);

        for ($i = 0; $i < count($rows) ; $i++) {

            $row = $rows[0];
        }

        for ($i=0; $i < count($row) ; $i++) {

            if($i > 1) {


                    // IF BENFICIARY HAS ALREADY BEEN REGISTERED
                    $amount = (int)  $row[$i][2];


                    $this->addExpenseLedgerEntries($amount, $request->item_code, $row[$i][5], $row[$i][0],
                    $row[$i][1], $row[$i][3],$row[$i][4],$row[$i][6],$row[$i][7],$summary->id);


            }
        }

        return response()->json([
            'success' => true,
            'message' => 'Successfully Added Ledger Entries',
        ], 200);
    }

    public function showBeneficiairies(Request $request) {

    //Get Item accounts

    $all_accounts = array();
    $bank_accounts = DB::table('bank_accounts')->where('account_number', '5112')->get();
    $sub_bank_accounts = DB::table('sub_bank_accounts')->where('account_number', '5112')->get();
    $last_bank_accounts = DB::table('last_bank_accounts')->where('account_number', '5112')->get();
    $more_account_belows = DB::table('more_account_belows')->where('account_number', '5112')->get();


    foreach ($bank_accounts as $value) {
        $data1 = array(
            'name' => $value->name,
            'account_number' => $value->account_number,
        );
        array_push($all_accounts, $data1);
    }
    foreach ($sub_bank_accounts as $value) {
        $data2 = array(
            'name' => $value->name,
            'account_number' => $value->account_number,
        );
        array_push($all_accounts, $data2);
    }
    foreach ($last_bank_accounts as $value) {
        $data3 = array(
            'name' => $value->name,
            'account_number' => $value->account_number,
        );
        array_push($all_accounts, $data3);
    }

    foreach ($more_account_belows as $value) {
        $data4 = array(
            'name' => $value->name,
            'account_number' => $value->account_number,
        );
        array_push($all_accounts, $data4);
    }


    //get request made
    $entries = DB::table('beneficiary_payments')
    ->select('beneficiary_payments.*')
    ->orderby('beneficiary_payments.created_at', 'DESC')
    ->paginate(10);

   // return $entries;




    $data = array(
        'user' => Auth::user(),
        'entries' => $entries,
        'all_accounts' => $all_accounts,
    );

    return view('beneficiaries.index')->with($data);

    }

    public function createBeneficiairy(Request $request) {

        //return $request->item_code;

        $validator = Validator::make($request->all(), [
            'name' => 'required',
            'district' => 'required',
            'account_number' => 'required',
            'telephone_number' => 'required',
            'amount' => 'required',
            'item_code' => 'required',
            'nin' => 'required',

        ]);



        if ($validator->fails()) {
            return redirect()->back()->withInput()->withErrors($validator);
        }
        //Check if Account Exists
        $beneficiary = Beneficiary::select('id',
                'account_number')->where('account_number', $request->account_number)->first();
        if($beneficiary == NULL) {
            // CREATE NEW BENEFICIARY
            $user = Beneficiary::create([
                'district' => $request->district,
                'name' => $request->name,
                'title' => $request->title,
                'duty_station' => $request->duty_station,
                'bank' => $request->bank,
                'account_number' => $request->account_number,
                'telephone_number' => $request->telephone_number,
                'nin' => $request->nin,
            ]);

            // //Get the last created sub account under a sub account
            $last_account = DB::table('more_account_belows')
                ->select('more_account_belows.id', 'more_account_belows.created_at',
                'more_account_belows.account_below_bank_account_id', 'more_account_belows.account_number')
                ->latest('more_account_belows.id')
                ->where('more_account_belows.account_below_bank_account_id', 18)
                ->first();

            //Get information about a given sub account
            $sub_account = LastBankAccount::select('id', 'account_number')->where('id', 18)->first();
            $amount = (int)  $request->amount;


            if ($last_account != NULL) {
                //Get last two digits of item code
                $new_string = substr($last_account->account_number, -2);
                //convert string to integer
                $integer_string = (int) $new_string;

                if ($integer_string <= 9) {
                    $new_last_digit = $integer_string + 1;
                    $code = (string) $new_last_digit;
                    $intact_code = '0' . $code;
                    $new_item_code = $sub_account->account_number . $intact_code;
                } else {
                    $new_last_digit = $integer_string + 1;
                    $code = (string) $new_last_digit;
                    $new_item_code = $sub_account->account_number  . $code;
                }

                $created_account = MoreAccountBelow::create([
                    'account_below_bank_account_id' => 18,
                    'name' => ucwords($request->name),
                    'account_number' => $new_item_code
                ]);
                $subject = 'Creating a beneficiary account';
                $details = 'Creating account with name : ' . $request->name;
                $this->addToLog($subject, $details);

                if($created_account) {
                    $select_beneficiary = Beneficiary::select('id',
                    'account_number', 'code_in_coa')->where('account_number', $request->account_number )->first();
                    $select_beneficiary->code_in_coa = $created_account->account_number;
                    $select_beneficiary->save();
                }
                $this->addBenficiaryLedgerEntries($amount, $request->name,
                $new_item_code, $request->item_code , $request->requested_by, $request->category, $request->details, $request->budget_code);

            } else {
                $new_item_code = $sub_account->account_number . '01';
                $created_account = MoreAccountBelow::create([
                    'account_below_bank_account_id' => 18,
                    'name' => ucwords($request->name),
                    'account_number' => $new_item_code
                ]);
                $subject = 'Creating a beneficiary account';
                $details = 'Creating account with name : ' . $request->name;
                $this->addToLog($subject, $details);

                if($created_account) {
                    $select_beneficiary = Beneficiary::select('id',
                    'account_number', 'code_in_coa')->where('account_number', $request->account_number )->first();
                    $select_beneficiary->code_in_coa = $created_account->account_number;
                    $select_beneficiary->save();
                }
                $this->addBenficiaryLedgerEntries($amount, $request->name,
                $new_item_code, $request->item_code , $request->requested_by, $request->category, $request->details, $request->budget_code);
            }

        }else {

            // IF BENFICIARY HAS ALREADY BEEN REGISTERED
            $amount = (int)  $request->amount;
            //GET BENEFICIARY ACCOUNT CODE  FROM CHART OF ACCOUNTS
            $select_beneficiary = Beneficiary::select('id',
            'account_number', 'name', 'code_in_coa')->where('account_number', $request->account_number )->first();

            $this->addBenficiaryLedgerEntries($amount, $select_beneficiary->name,
             $select_beneficiary->code_in_coa, $request->item_code, $request->requested_by, $request->category, $request->details ,$request->budget_code);

        }


        return redirect('list/beneficiaries')->with('message', 'Successfully Added Beneficiary');
    }

    public function entryedit($id)
    {

        $id = Crypt::decrypt($id);

        $item = Beneficiary::find($id);

        $data = array(
            'user' => Auth::user(),
            'item' => $item,
        );
        return view('beneficiaries.edit')->with($data);


    }

    public function entryupdate(Request $request)
    {


    $validator = Validator::make($request->all(), [
        'name' => 'required',
        'bank' => 'required',
        'account_number' => 'unique:beneficiaries,account_number,' . $request->id,
        'nin' => 'required',

    ]);



    if ($validator->fails()) {
        return redirect()->back()->withInput()->withErrors($validator);
    }

   // return 'hi';


    $item = Beneficiary::find( $request->input( 'id' ) );






        $item->name = ucfirst($request->name);
        $item->bank = $request->bank;
        $item->account_number = $request->account_number;
        $item->title = $request->title;
        $item->duty_station = $request->duty_station;
        $item->telephone_number = $request->telephone_number;
        $item->district = $request->district;
        $item->nin = $request->nin;


        $item->save();


    return redirect('/list/beneficiaries')->with('success', 'Successfully Updated Beneficiary ');


}


public function generateBenReport(Request $request) {


   // return $entries;

   $data = array(
        'user' => Auth::user(),
    );

    return view('beneficiaries.reportview')->with($data);

    }


public function showReport(Request $request) {


    // return $entries;

    $action = $request->action;

    if($action == 'excel'){
       $start_date = $request->statement_start_date;
       $end_date = $request->statement_end_date;
       $fy = $request->fy;
       $quarter = $request->quarter;

    }else{

        $query = DB::table('beneficiary_payments')
			->orderby('beneficiary_payments.created_at', 'desc')
			->whereBetween('beneficiary_payments.pay_date', [
                $request->statement_start_date, $request->statement_end_date,
            ]);

            if (!empty($request->fy)) {
                $query->Where('beneficiary_payments.fy', 'LIKE', '%' . $request->fy . '%');
            }

            if (!empty($request->quarter)) {
                $query->Where('beneficiary_payments.quarter', 'LIKE', '%' . $request->quarter . '%');
            }

            if (!empty($request->district)) {
                $query->Where('beneficiary_payments.district', 'LIKE', '%' . $request->district . '%');
            }


            $no_of_beneficiaries = $query->sum('no_of_beneficiaries');
            $ent_amount = $query->sum('ent_amount');
            $service_fee = $query->sum('service_fee');
            $withdraw_charge = $query->sum('withdraw_charge');
            $entries = $query->get();

            $total = $ent_amount + $service_fee + $withdraw_charge;






     $data = array(
         'user' => Auth::user(),
         'entries' =>  $entries,
         'district' => $request->district,
         'total' => $total,
         'ent_amount' => $ent_amount,
         'service_fee' => $service_fee,
         'withdraw_charge' =>  $withdraw_charge,
         'no_of_beneficiaries' => $no_of_beneficiaries,
         'start_date' => $request->statement_start_date,
         'end_date' => $request->statement_end_date,

     );

     return view('beneficiaries.report')->with($data);

     }

     }

     public function expensesummary(Request $request) {

        $entries = DB::table('expense_summary')
        ->orderBy('created_at', 'desc')
        ->get();

        $data = array(
            'user' => Auth::user(),
            'entries' =>  $entries,

        );

        return view('beneficiaries.summary')->with($data);

     }

     public function expenselist($id) {

        $id = Crypt::decrypt($id);

        $query = DB::table('expense_details')
        ->where('expense_id', $id)
        ->orderBy('created_at', 'desc');
        $total = $query->sum('amount');
        $entries = $query->get();

        $expenseSummary = DB::table('expense_summary')
        ->where('id', $id)
        ->orderBy('created_at', 'desc')
        ->first();


        $data = array(
            'user' => Auth::user(),
            'entries' =>  $entries,
            'summary' =>  $expenseSummary,
            'total' => $total,

        );

        return view('beneficiaries.expense_list')->with($data);

     }



}
