<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Arr;

class ProcessPayrollController extends Controller
{

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

    public function index(){
        $business_units = DB::table('business_units')
            ->select('id', 'business_unit')
            ->get();

        $departments = DB::table('departments')
            ->select('id', 'department_name')
            ->get();

        # Load all the instances of the processed payroll for this month and year
        $instances = DB::table('processed_payroll')
            ->get();

        return view('ProcessPayroll.index', compact(['departments', 'business_units', 'instances']));
    }

    Public function process_payroll(Request $request){
        $month = $request->month;
        $year = $request->year;

        $departments = $request->departments;
        $business_units = $request->businessUnits;

        # Retrieve all the departments in this organization
        if ($departments[0] == 0){
            $departments = DB::table('departments')->orderBy('id', 'asc')->pluck('id');
        }

        # Retrieve all the business units
        if ($business_units[0] == 0){
            $business_units = DB::table('business_units')->orderBy('id', 'asc')->pluck('id');
        }

        /*# Sort the values in the arrays
        $departments = $departments;
        $business_units = $business_units;*/

        # Convert the arrays into strings
        $departments = $departments->implode(',');
        $business_units = $business_units->implode(',');

        #Check if payroll has already been processed
        # This process is a little bit complex
        # Because we have to check whether all the business units/departments have been processed
        /*$payroll_status = DB::table('processed_payroll')
            ->where([
                ['month', $month],
                ['year', $year]
            ])
            ->get();*/


        #######################################################################################
        # CAUTION! EXCESSIVE USE OF ARRAYS AHEAD. BE CAREFUL WITH ANY MODIFICATIONS
        # If payroll status is ok then process, otherwise, abort.


        # if (count($payroll_status) > 0){
            # Respond: Failed, payroll has already been processed
        #     return response()->json(['status' => 0, 'error_message' => 'This payroll has already been processed.'], 200);
        # }else{
            DB::beginTransaction();

            try {

                ############################################################################################
                #1. Allowances
                ############################################################################################
                $allowances = DB::table('allowance_config')->get();

                $item = '1'; #1 = Allowances
                foreach ($allowances as $allowance) {
                    $item_name = $allowance->income_name;
                    $tax_option = $allowance->tax_option;

                    #Check if item is one time and the current status
                    $one_time = $allowance->one_time;

                    #If one_time is == 2, item has already been processed, skip processing
                    if ($one_time == 1){
                        continue;
                    }

                    $allowance_groups = DB::table('payroll_item_groups')
                        ->where([
                            ['payroll_item_id', $allowance->id],
                            ['payroll_item', 'allowance']
                        ])
                        ->get();

                    foreach ($allowance_groups as $allowance_group) {
                        $employees = $allowance_group->employees;
                        $employees = explode(',', $employees);
                        #Get the details of all the employees in this array
                        $employees = DB::table('main_users')
                            ->whereIn('main_users.id', $employees)
                            ->join('employee_salary_details', 'main_users.id', '=', 'employee_salary_details.user_id')
                            ->select('main_users.*', 'employee_salary_details.salary')
                            ->get();


                        #TABULATION TYPE
                        #Tabulation type determines the calculation method to be applied on the income
                        Switch ($allowance->tabulation_type) {
                            case 1: #Constant value
                                #Save in the payslip
                                foreach ($employees as $employee) {
                                    DB::table('payslips')
                                        ->insert([
                                            'user_id' => $employee->id,
                                            'month' => $month,
                                            'year' => $year,
                                            'item' => $item,
                                            'tax_option' => $tax_option,
                                            'item_name' => $item_name,
                                            'item_amount' => round($allowance_group->value, 2),
                                            'created_by' => 1,
                                            'created_at' => DB::raw('NOW()')
                                        ]);
                                }

                                break;
                            case 2: #Income percentage
                                #Save in the payslip
                                foreach ($employees as $employee) {
                                    DB::table('payslips')
                                        ->insert([
                                            'user_id' => $employee->id,
                                            'month' => $month,
                                            'year' => $year,
                                            'item' => $item,
                                            'tax_option' => $tax_option,
                                            'item_name' => $item_name,
                                            'item_amount' => round(($allowance_group->value / 100) * $employee->salary, 2),
                                            'created_by' => 1,
                                            'created_at' => DB::raw('NOW()')
                                        ]);
                                }
                                break;
                            case 3: #Range
                                $range_id = $allowance->range;
                                $range = DB::table('ranges')
                                    ->where('id', $range_id)
                                    ->first();
                                $range_calculation_method = $range->calculation_method;

                                foreach ($employees as $employee) {
                                    $range_details = DB::table('range_config')
                                        ->where([
                                            ['range_id', $range_id],
                                            ['lower_limit', '<=', $employee->salary],
                                            ['upper_limit', '>=', $employee->salary]
                                        ])
                                        ->first();

                                    $range_rate = $range_details->rate;

                                    switch ($range_calculation_method){
                                        case 1:
                                            DB::table('payslips')
                                                ->insert([
                                                    'user_id' => $employee->id,
                                                    'month' => $month,
                                                    'year' => $year,
                                                    'item' => $item,
                                                    'tax_option' => $tax_option,
                                                    'item_name' => $item_name,
                                                    'item_amount' => round($range_rate, 2),
                                                    'created_by' => 1,
                                                    'created_at' => DB::raw('NOW()')
                                                ]);
                                            break;
                                        case 2:
                                            DB::table('payslips')
                                                ->insert([
                                                    'user_id' => $employee->id,
                                                    'month' => $month,
                                                    'year' => $year,
                                                    'item' => $item,
                                                    'tax_option' => $tax_option,
                                                    'item_name' => $item_name,
                                                    'item_amount' => round((round($range_rate/100, 2)) * $employee->salary, 2),
                                                    'created_by' => 1,
                                                    'created_at' => DB::raw('NOW()')
                                                ]);
                                            break;
                                    }
                                }
                                break;
                        }
                    }

                    if ($allowance->income_type == 2){
                        DB::table('allowance_config')
                            ->where('id', $allowance->id)
                            ->update(
                                ['one_time' => 1]
                            );
                    }

                }

                ############################################################################################
                #2. Deductions
                ############################################################################################
                $deductions = DB::table('deduction_config')->get();

                $item = '2'; #2 = Deductions
                foreach ($deductions as $deduction) {
                    $item_name = $deduction->deduction_name;
                    $tax_option = $deduction->tax_option;

                    #Check if item is one time and the current status
                    $one_time = $deduction->one_time;

                    #If one_time is == 2, item has already been processed, skip processing
                    if ($one_time == 1){
                        continue;
                    }

                    $deduction_groups = DB::table('payroll_item_groups')
                        ->where([
                            ['payroll_item_id', $deduction->id],
                            ['payroll_item', 'deduction']
                        ])
                        ->get();

                    foreach ($deduction_groups as $deduction_group) {
                        $employees = $deduction_group->employees;
                        $employees = explode(',', $employees);

                        #Get the details of all the employees in this array
                        $employees = DB::table('main_users')
                            ->whereIn('main_users.id', $employees)
                            ->join('employee_salary_details', 'main_users.id', '=', 'employee_salary_details.user_id')
                            ->select('main_users.*', 'employee_salary_details.salary')
                            ->get();

                        #INCOME TYPE
                        #Income type establishes whether or not the deduction should be processed for this month


                        #TABULATION TYPE
                        #Tabulation type determines the calculation method to be applied on the income
                        Switch ($deduction->tabulation_type) {
                            case 1: #Constant value
                                #Save in the payslip
                                foreach ($employees as $employee) {
                                    DB::table('payslips')
                                        ->insert([
                                            'user_id' => $employee->id,
                                            'month' => $month,
                                            'year' => $year,
                                            'item' => $item,
                                            'tax_option' => $tax_option,
                                            'item_name' => $item_name,
                                            'item_amount' => round($deduction_group->value, 2),
                                            'created_by' => 1,
                                            'created_at' => DB::raw('NOW()')
                                        ]);
                                }

                                break;
                            case 2: #Income percentage
                                #Save in the payslip
                                foreach ($employees as $employee) {
                                    DB::table('payslips')
                                        ->insert([
                                            'user_id' => $employee->id,
                                            'month' => $month,
                                            'year' => $year,
                                            'item' => $item,
                                            'tax_option' => $tax_option,
                                            'item_name' => $item_name,
                                            'item_amount' => round(round($deduction_group->value/100, 2) * $employee->salary, 2),
                                            'created_by' => 1,
                                            'created_at' => DB::raw('NOW()')
                                        ]);
                                }
                                break;
                            case 3: #Range
                                $range_id = $deduction->range;
                                $range = DB::table('ranges')
                                    ->where('id', $range_id)
                                    ->first();
                                $range_calculation_method = $range->calculation_method;

                                foreach ($employees as $employee) {
                                    #Check whether the calculation method is for PAYE
                                    if ($range_calculation_method == 3){
                                        $range_details = DB::table('range_config')
                                            ->where([
                                                ['range_id', $range_id]
                                            ])
                                            ->orderBy('lower_limit', 'asc')
                                            ->get();
                                    }else{
                                        $range_details = DB::table('range_config')
                                            ->where([
                                                ['range_id', $range_id],
                                                ['lower_limit', '<=', $employee->salary],
                                                ['upper_limit', '>=', $employee->salary]
                                            ])
                                            ->first();

                                        $range_rate = $range_details->rate;
                                    }


                                    switch ($range_calculation_method){
                                        case 1:
                                            DB::table('payslips')
                                                ->insert([
                                                    'user_id' => $employee->id,
                                                    'month' => $month,
                                                    'year' => $year,
                                                    'item' => $item,
                                                    'tax_option' => $tax_option,
                                                    'item_name' => $item_name,
                                                    'item_amount' => round($range_rate, 2),
                                                    'created_by' => 1,
                                                    'created_at' => DB::raw('NOW()')
                                                ]);
                                            break;
                                        case 2:
                                            DB::table('payslips')
                                                ->insert([
                                                    'user_id' => $employee->id,
                                                    'month' => $month,
                                                    'year' => $year,
                                                    'item' => $item,
                                                    'tax_option' => $tax_option,
                                                    'item_name' => $item_name,
                                                    'item_amount' => round(round(round($range_rate/100, 2), 2) * $employee->salary, 2),
                                                    'created_by' => 1,
                                                    'created_at' => DB::raw('NOW()')
                                                ]);
                                            break;
                                        case 3:
                                            #Logic for PAYE calculator
                                            $salary = $employee->salary;

                                            $paye = 0;
                                            $count = 0;
                                            foreach ($range_details as $range_detail){
                                                if ($range_detail->lower_limit <= $salary && $range_detail->upper_limit >= $salary){
                                                    $paye_amount = round(($salary - $range_detail->lower_limit + 1) * round($range_detail->rate/100, 2), 2);

                                                    $paye = $paye + $paye_amount;
                                                    $count += 1;
                                                    break;
                                                }else{
                                                    if ($count == 0){
                                                        $paye_amount = round(($range_detail->upper_limit - $range_detail->lower_limit) * round($range_detail->rate/100, 2), 2);
                                                    }else{
                                                        $paye_amount = round(($range_detail->upper_limit - $range_detail->lower_limit + 1) * round($range_detail->rate/100, 2), 2);
                                                    }


                                                    $paye = $paye + $paye_amount;
                                                    $count += 1;
                                                }
                                            }

                                            DB::table('payslips')
                                                ->insert([
                                                    'user_id' => $employee->id,
                                                    'month' => $month,
                                                    'year' => $year,
                                                    'item' => $item,
                                                    'tax_option' => $tax_option,
                                                    'item_name' => $item_name,
                                                    'item_amount' => round($paye, 2),
                                                    'paye' => 1,
                                                    'created_by' => 1,
                                                    'created_at' => DB::raw('NOW()')
                                                ]);
                                            break;
                                    }
                                }
                                break;
                        }
                    }

                    if ($deduction->deduction_type == 2){
                        DB::table('allowance_config')
                            ->where('id', $deduction->id)
                            ->update(
                                ['one_time' => 1]
                            );
                    }

                }

                ############################################################################################
                #3. Relief
                ############################################################################################
                $relief_ = DB::table('relief_config')->get();

                $item = '3'; #3 = Relief
                foreach ($relief_ as $relief) {
                    $item_name = $relief->relief_name;

                    $relief_groups = DB::table('payroll_item_groups')
                        ->where([
                            ['payroll_item_id', $relief->id],
                            ['payroll_item', 'relief']
                        ])
                        ->get();

                    foreach ($relief_groups as $relief_group) {
                        $employees = $relief_group->employees;
                        $employees = explode(',', $employees);

                        #Get the details of all the employees in this array
                        $employees = DB::table('main_users')
                            ->whereIn('main_users.id', $employees)
                            ->join('employee_salary_details', 'main_users.id', '=', 'employee_salary_details.user_id')
                            ->select('main_users.*', 'employee_salary_details.salary')
                            ->get();

                        #INCOME TYPE
                        #Income type establishes whether or not the relief should be processed for this month


                        #TABULATION TYPE
                        #Tabulation type determines the calculation method to be applied on the income
                        #Save in the payslip
                        foreach ($employees as $employee) {
                            DB::table('payslips')
                                ->insert([
                                    'user_id' => $employee->id,
                                    'month' => $month,
                                    'year' => $year,
                                    'item' => $item,
                                    'tax_option' => 0,
                                    'item_name' => $item_name,
                                    'item_amount' => round($relief_group->value, 2),
                                    'created_by' => 1,
                                    'created_at' => DB::raw('NOW()')
                                ]);
                        }
                    }

                }

                ############################################################################################
                #4. Calculate gross/taxable/pay after tax/net pay
                ############################################################################################
                #Get a list of all the users
                $users = DB::table('payslips')
                    ->where([
                        ['payslips.month', $month],
                        ['payslips.year', $year]
                    ])
                    ->join('main_users', 'main_users.id', '=', 'payslips.user_id')
                    ->join('employee_salary_details', 'employee_salary_details.user_id', '=', 'payslips.user_id')
                    ->select(DB::raw('distinct payslips.user_id'), 'employee_salary_details.salary')
                    ->get();

                #Loop through and calculate net pay for everyone
                foreach($users as $user){
                    #Get all the details of employee
                    $user_details = DB::table('payslips')
                        ->where([
                            ['user_id', $user->user_id],
                            ['month', $month],
                            ['year', $year]
                        ])
                        ->get();

                    $basic_salary = $user->salary;
                    $allowances = 0;
                    $non_taxable_deductions = 0;
                    $taxable_deductions = 0;
                    $relief = 0;

                    foreach($user_details as $user_detail){
                        if ($user_detail->item == 1){
                            #Allowances
                            $allowances += $user_detail->item_amount;
                        }else if ($user_detail->item == 2 && $user_detail->tax_option == 2){
                            #Non-taxable deductions
                            $non_taxable_deductions += $user_detail->item_amount;
                        }else if($user_detail->item == 2 && $user_detail->tax_option == 1){
                            #Taxable deductions
                            $taxable_deductions += $user_detail->item_amount;
                        }else if($user_detail->item == 3){
                            #Relief
                            $relief += $user_detail->item_amount;
                        }
                    }

                    #Get the PAYE config
                    $paye_range = DB::table('ranges')
                        ->where([
                            ['calculation_method', 3]
                        ])
                        ->first();

                    $paye_details = DB::table('range_config')
                        ->where([
                            ['range_id', $paye_range->id]
                        ])
                        ->orderBy('lower_limit', 'asc')
                        ->get();

                    #Add all the allowances to the basic pay to get gross pay
                    $gross_pay = $basic_salary + $allowances;

                    # Get taxable pay
                    $taxable_pay = $gross_pay - $non_taxable_deductions;

                    #Start calculating net pay
                    $net_pay = $taxable_pay;

                    #Calculate PAYE here;
                    $paye = 0;
                    $count = 0;
                    foreach ($paye_details as $paye_detail){
                        if ($paye_detail->lower_limit <= $taxable_pay && $paye_detail->upper_limit >= $taxable_pay){
                            $paye_amount = round(($taxable_pay - $paye_detail->lower_limit + 1) * round($paye_detail->rate/100, 2), 2);

                            $paye = $paye + $paye_amount;
                            $count += 1;
                            break;
                        }else{
                            if ($count == 0){
                                $paye_amount = round(($paye_detail->upper_limit - $paye_detail->lower_limit) * round($paye_detail->rate/100, 2), 2);
                            }else{
                                $paye_amount = round(($paye_detail->upper_limit - $paye_detail->lower_limit + 1) * round($paye_detail->rate/100, 2), 2);
                            }

                            $paye = $paye + $paye_amount;
                            $count += 1;
                        }
                    }

                    #Remove PAYE
                    $net_pay = $net_pay - $paye;

                    # Pay After Tax
                    $pay_after_tax = $net_pay;

                    #Add all the reliefs
                    $net_pay = $net_pay + $relief;

                    #Remove all taxable deductions
                    $net_pay = $net_pay - $taxable_deductions;

                    #Save the paye, taxable_pay, gross pay and net pay
                    DB::table('payslips')
                        ->insert([
                            ['user_id' => $user->user_id,
                                'month' => $month,
                                'year' => $year,
                                'item' => 2,
                                'tax_option' => 1,
                                'item_name' => 'Pay after tax',
                                'item_amount' => round($pay_after_tax, 2),
                                'paye' => 0,
                                'gross' => 0,
                                'created_by' => 1,
                                'created_at' => DB::raw('NOW()')],

                            ['user_id' => $user->user_id,
                                'month' => $month,
                                'year' => $year,
                                'item' => 2,
                                'tax_option' => 1,
                                'item_name' => 'Taxable pay',
                                'item_amount' => round($taxable_pay, 2),
                                'paye' => 0,
                                'gross' => 0,
                                'created_by' => 1,
                                'created_at' => DB::raw('NOW()')],

                            ['user_id' => $user->user_id,
                                'month' => $month,
                                'year' => $year,
                                'item' => 2,
                                'tax_option' => 1,
                                'item_name' => 'PAYE',
                                'item_amount' => round($paye, 2),
                                'paye' => 1,
                                'gross' => 0,
                                'created_by' => 1,
                                'created_at' => DB::raw('NOW()')],

                            ['user_id' => $user->user_id,
                                'month' => $month,
                                'year' => $year,
                                'item' => 2,
                                'tax_option' => 1,
                                'item_name' => 'Net pay',
                                'item_amount' => round($net_pay, 2),
                                'paye' => 0,
                                'gross' => 0,
                                'created_by' => 1,
                                'created_at' => DB::raw('NOW()')],

                            ['user_id' => $user->user_id,
                                'month' => $month,
                                'year' => $year,
                                'item' => 1,
                                'tax_option' => 1,
                                'item_name' => 'Gross pay',
                                'item_amount' => round($gross_pay, 2),
                                'paye' => 0,
                                'gross' => 1,
                                'created_by' => 1,
                                'created_at' => DB::raw('NOW()')]
                        ]);
                }


                ############################################################################################
                #5. Update the payroll tracker
                ############################################################################################

                //dd($year, $month, $business_units, $departments);

                DB::table('processed_payroll')
                    ->insert([
                        'month' => $month,
                        'year' => $year,
                        'business_units' => $business_units,
                        'departments' => $departments,
                        'created_at' => DB::raw('NOW()')
                    ]);

                DB::commit();

            }catch(\Exception $e){

                DB::rollback();

                throw $e;

            }

            return response()->json(['status' => 1], 200);
        # }
    }
}
