<?php
namespace App\Http\Controllers\API\v1;

use App\Http\Controllers\Controller;
use App\Models\Payroll;
use App\Models\PayrollSummary;
use App\Models\PayrollDate;
use App\Models\SssTable;
use App\Models\PhicTable;
use App\Models\TaxTable;
use App\Models\AttendancePosting;
use App\Models\AttendancePostingDtls;
use App\Models\PremiumOvertimePaySetting;
use App\Models\ProcessedTimelog;
use App\Models\ProcessedAbsent;
use App\Models\ProcessedLeave;
use App\Models\ProcessedTardiness;
use App\Models\ProcessedUndertime;
use App\Models\ProcessedOvertime;
use App\Models\ProcessedHolidayRestdayDuty;
use App\Models\ProcessedDailyWorkHours;
use App\Models\ProcessedSchedule;
use App\Models\ProcessedOb;
use App\Models\EmployeeBenefits;
use App\Models\EmployeeLoan;
use App\Models\PaymentContribution;
use App\Models\AdjustmentsAndDeductions;
use App\Models\SalaryAndWages;
use App\Models\PayrollSetting;
use Illuminate\Http\Request;
use Illuminate\Http\Response;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Facades\Validator;
use Illuminate\Support\Facades\DB;
use Illuminate\Validation\Rule;
use Carbon\Carbon;
use App\Helpers\CommonHelper;
use App\Models\Employee;
use App\Models\EmployeePaymentContributionHistory;
use App\Models\Loan;
use App\Models\PaymentContributionHistory;
use DateInterval;
use DatePeriod;
use DateTime;

class PayrollController extends Controller
{
    /**
     * Display a listing of the resource.
     */
    public function index(Request $request)
    {

        $keyword = $request->input('keyword', null);
        if($keyword) $keyword = str_replace(' ','%', $keyword);
        $perPage = $request->input('per_page', PHP_INT_MAX);
        $sortBy = $request->input('sortBy', null);
        $sortType = $request->input('sortType', null);
        $data = Payroll::with(['company','dealership'])
        ->when($keyword, fn($q) => $q->whereHas('company', fn($qc) => $qc->where('name', 'like', '%'.$keyword.'%'))->orWhereHas('dealership', fn($qc) => $qc->where('name', 'like', '%'.$keyword.'%'))->orWhere('payroll_date', 'like', $keyword.'%'))
        ->when($sortBy, fn ($q) => $q->orderBy($sortBy, $sortType))
        ->orderBy('created_at', 'desc')
        ->paginate($perPage);

        return response()->json($data);
    }


    /**
     * Store a newly created resource in storage.
     */
    public function store(Request $request)
    {
        DB::connection()->beginTransaction();

        try {
            $where = ['company_id' => $request->company_id, 'dealership_id' => $request->dealership_id, 'payroll_date' => $request->payroll_date];
            $data = Payroll::where($where)->first();
            if(!$data) {
                $prefix = join('-',[
                    'PYRL',
                    \App\Models\Dealership::find($request->dealership_id)->code
                ]);
                /**get reference code */
                do {
                    $reference_no = CommonHelper::generateReferenceCode($prefix);
                } while (Payroll::where('reference_no',$reference_no)->exists());

                $data = Payroll::create(array_merge(
                    $request->except(['details']),
                    [
                        'reference_no' => $reference_no
                    ]
                ));
            } else {
                $data->fill([
                    'file' => $request->file,
                    'pdf_file' => $request->pdf_file,
                ])->save();

                $data->touch(); // Optional: Only if you want to explicitly update updated_at again

            }
            /**Delete SUMMARY if any */
            PayrollSummary::where($where)->delete();
            foreach($request->details as $detail) {

                PayrollSummary::create(array_merge(
                    $detail,
                    [
                        'adjustments_total' => $detail['adjustment_totals'],
                        'payroll_id' => $data->id,
                        'reference_no' => $data->reference_no,
                    ]
                ));

            }

            DB::connection()->commit();

            return response()->json([
                'message' => 'Record Successfully added!',
                'status' => 'success',
                'data' => $data
            ],201);

        } catch (\Throwable $e) {
            DB::connection()->rollback();
            return response()->json([
                'status'  => false,
                'message' => 'Unable to process request. Please try again.',
                'data'    => $e->getMessage()
            ], 422);
        }
    }


    /**
     * Display the specified resource.
     */
    public function show(int $id)
    {
        DB::enableQueryLog();

        $data = PayrollSummary::with(['employee'])->where('payroll_id', $id)->get();

        return response()->json($data, 200);
    }


     /**
     * Display the specified resource.
     */
    public function getSummary(Request $request, string $payroll_date)
    {
        DB::enableQueryLog();
        $company_id = $request->company_id;
        $posted = AttendancePosting::where(['payroll_date' => $payroll_date, 'company_id' => $company_id])->whereNotNull('posted_at')->first();

        $data = AttendancePostingDtls::select('dealership_id', 'department_id')
        ->whereHas('header', fn($q) => $q->where(['payroll_date' => $payroll_date, 'company_id' => $company_id]))
        ->distinct()
        ->orderBy('dealership_id')
        ->orderBy('department_id')
        ->get() // Fetch all records
        ->map(function ($item) use ($posted) {
            $item->posted_status = $posted ? true : false;
            return $item;
        });
        // dd(DB::getQueryLog());

        return response()->json($data, 200);
    }


    /**
     * Remove the specified resource from storage.
     */
    public function destroy(int $id)
    {
        $data = AttendancePosting::find($id);
        if(!$data)
        {
            return response()->json(['message' => "Record not found!"],204);
        }
        DB::connection()->beginTransaction();
        try {

            $data->delete();
            DB::connection()->commit();
            return response()->json(['message' => "Record successfully deleted!"],201);

        } catch (Throwable $e) {
            DB::connection()->rollback();
            return response()->json([
                'status'  => false,
                'message' => 'Unable to process request. Please try again.',
                'data'    => $e->getMessage()
            ]);
        }
    }

    public function payrollDates(Request $request)
    {
        $company_id = $request->input('company_id');
        $dealership_id = $request->input('dealership_id');
        try {
            $payroll_dates = PayrollDate::where(fn($q) => $q->where('company_id', $company_id)->orWhereNull('company_id'))
            ->where(fn($q) => $q->where('dealer_id', $dealership_id)->orWhereNull('dealer_id'))
            ->whereNotExists(fn($q) => $q->select(DB::raw(1))
                    ->from('payrolls')
                    ->whereRaw('payrolls.payroll_date = payroll_dates.payroll_date')
                    // ->whereRaw('payrolls.company_id = payroll_dates.company_id')
                    // ->whereRaw('payrolls.dealership_id = payroll_dates.dealer_id')
                    ->where('payrolls.company_id', $company_id)
                    ->where('payrolls.dealership_id', $dealership_id)
                    ->whereNotNull('payrolls.posted_at')
            )
            ->whereExists(fn($q) => $q->select(DB::raw(1))
                    ->from('attendance_postings')
                    ->whereRaw('attendance_postings.payroll_date = payroll_dates.payroll_date')
                    // ->whereRaw('attendance_postings.company_id = payroll_dates.company_id')
                    // ->whereRaw('attendance_postings.dealership_id = payroll_dates.dealer_id')
                    ->where('attendance_postings.company_id', $company_id)
                    ->where('attendance_postings.dealership_id', $dealership_id)
                    ->whereNotNull('attendance_postings.posted_at')
                    ->whereNull('attendance_postings.released_at')
            )->get();

            return response()->json($payroll_dates, 200);

        } catch (Throwable $e) {
            return response()->json([
                'status'  => false,
                'message' => 'Unable to process request. Please try again.',
                'data'    => $e->getMessage()
            ], 422);
        }
    }
    public function adjustmentsAndDeductions(Request $request) {
        $cut_off = $request->input('cut_off');
        $cut_off_start = $request->input('cut_off_start');
        $cut_off_end = $request->input('cut_off_end');
        $company_id = $request->input('company_id');
        $dealership_id = $request->input('dealership_id');
        DB::enableQueryLog();
        $data = AdjustmentsAndDeductions::with([
            'employee' => fn($q) => $q->select('employee_id', 'firstname', 'middlename', 'lastname', 'position_id'),
            'employee.position' => fn($q) => $q->select('id', 'name'),
            'payroll_item' => fn($q) => $q->select('id', 'code', 'name', 'add_less', 'taxable', 'thirteenth_month')
        ])
        ->where([
            'company_id' => $company_id,
            'dealer_id' => $dealership_id,
            'enabled' => 1
        ])
        ->where(fn($q) => $q->where('frequency', $cut_off == '1st' ? 1 : 2)->orWhere('frequency', 3))
        ->where(function($q) use ($cut_off_start, $cut_off_end) {
            $q->where(function($q) use ($cut_off_start, $cut_off_end) {
                $q->where('recurring', 1)
                ->where('date_from', '<=', $cut_off_start)->where('date_to', '>=', $cut_off_end)
                ->where(
                    fn($q2) => $q2->where('paused',0)
                    ->orWhere(
                        fn($q3) => $q3->where('paused', 1)
                        ->where(
                            fn($q4) => $q4->whereNull('resume_date')->orWhere('resume_date', '<=', $cut_off_end)
                        )
                    )
                );
            })
            ->orWhere(function($q) use ($cut_off_start, $cut_off_end) {
                $q->where('recurring', 1)
                ->where('paused', 0)
                ->where('date_from', '<=', $cut_off_start);
            })
            ->orWhere(function($q) use ($cut_off_start, $cut_off_end) {
                  $q->where('recurring', 0)
                    ->whereBetween('date_from', [$cut_off_start, $cut_off_end])
                    ->whereBetween('date_to', [$cut_off_start, $cut_off_end]);
              });
        })
        ->get();
        // dd(DB::getQueryLog());
        return response()->json($data);
    }
    public function computePayroll(Request $request)
    {
        $payroll_date = $request->input('payroll_date');
        $employee_id = $request->input('employee_id');
        $company_id = $request->input('company_id');
        $dealership_id = $request->input('dealership_id');
        $cut_off = $request->input('cut_off','1st');
        $cut_off_start = $request->input('cut_off_start',null);
        $cut_off_end = $request->input('cut_off_end',null);
        $work_days = $request->input('work_days', 0);
        $actual_work_days = $request->input('actual_work_days', 0);
        $work_hours = $request->input('work_hours', 0);
        $leave_hours = $request->input('leave_hours', 0);
        $minutes_late = $request->input('minutes_late', 0);
        $minutes_undertime = $request->input('minutes_undertime', 0);
        $days_absent = $request->input('days_absent', 0);
        $overtime_totals = $request->input('overtime_totals',null);
        $night_diff_totals = $request->input('night_diff_totals',null);
        $holiday_totals = $request->input('holiday_totals',null);
        $adjustments = $request->input('adjustments', null);
        $prevPayroll = null;
        // dd($request->input('night_diff_totals',null));
        $employee_classification_id = Employee::where('employee_id', $employee_id)->whereNull('date_resign')->select('classification_id')->first();
        if($cut_off=='2nd') {
            $prevPayroll = PayrollSummary::where([
                'company_id' => $company_id,
                'dealership_id' => $dealership_id,
                'employee_id' => $employee_id,
                'cut_off' => '1st',
                ])
                ->whereHas('payroll', fn($q) => $q->whereNotNull('posted_at'))
                ->orderBy('payroll_date', 'desc')->first();
        }

        // $payrollSetting = PayrollSetting::first();
        $payrollSetting = PayrollSetting::where('company_id',$company_id)->first();
        if(!$payrollSetting) {
            $payrollSetting = PayrollSetting::whereNull('company_id')->first();
        }
        $salary = SalaryAndWages::where('employee_id', $employee_id)->where('salary_effectivity_date', '<=', $cut_off_end)->orderBy('salary_effectivity_date','DESC')->first();
        $payrollRate = $salary->payroll_rate ?? 'Monthly';
        $shields = !empty($salary->shields) ? json_decode($salary->shields) : null;
        $totalWorkingDays = $payrollSetting->working_days_per_year;
        $salaryRate = $salary ? floatval($salary->salary_rate) : 0;
        $colaRate = $salary ? floatval($salary->cola) : 0;


        $dailyRate = $payrollRate == 'Monthly' ? ($salaryRate*12)/$totalWorkingDays : $salaryRate;
        $hourlyRate = $dailyRate/8;
        $cola = $colaRate * $work_days;
        $leave_pay = $leave_hours * $hourlyRate;
        $basic_pay = $payrollRate == 'Monthly' ? ($salaryRate/2) - $leave_pay : ($dailyRate * $actual_work_days);
        // $basic_pay = $salary->payroll_rate == 'Monthly' ? ($salaryRate/2) - $leave_pay : $dailyRate * $actual_work_days;

        /**Tax basis settings */
        $taxApplication = $salary ? $salary->tax_application : 1;
        $minimumWageAmount = $payrollSetting->minimum_wage_for_tax_exemption;
        $minimumWageEarner = $minimumWageAmount >= $dailyRate && $dailyRate != 695;

        // dd($employee_classification_id->classification_id);
        if($employee_classification_id->classification_id == 3){
            $late_amount = 0;
        } else {
            $late_amount = (isset($shields->no_late) && $shields->no_late == 1) ? 0 : $minutes_late * ($hourlyRate / 60);
        }

        // $late_amount = $employee_classification_id->classification_id == 3 ? 0 : $minutes_late * ($hourlyRate/60);
        $undertime_amount = $employee_classification_id->classification_id == 3 ? 0 : $minutes_undertime * ($hourlyRate/60);
        $absent_amount = $payrollRate == 'Daily' ? 0 : $days_absent * ($dailyRate);

        if($absent_amount > $basic_pay) {
            $absent_amount = $basic_pay;
        }

        $holiday_total = 0;
        $restday_total = 0;
        $restday_hours = 0;
        $overtime_total = 0;
        $overtime_hours = 0;
        $night_diff_total = 0;
        $night_diff_hours = 0;
        $holidayRestdayMeal = 0;
        $holidayRestdayTranspo = 0;
        $holidayRestdayTranspoAmount = 0;
        // if($employee_id == 2745) {
        //     dd($holiday_totals);
        // }

        if($holiday_totals) {
            $holiday_totals = collect(json_decode($holiday_totals));
            $holiday_totals = $holiday_totals->map(function ($holiday) use($payrollSetting, $hourlyRate, $payrollRate, &$holiday_total, &$restday_total, &$restday_hours, &$holidayRestdayMeal, &$holidayRestdayTranspo, &$holidayRestdayTranspoAmount) {
                $paymentSetting = PremiumOvertimePaySetting::find($holiday->payment_setting_id);
                if($payrollRate == 'Monthly') {
                    if($holiday->holiday_type==null) { /**if RESTDAY duty */
                        $amount = $holiday->no_of_hours * $hourlyRate * (($holiday->percentage)/100);
                    } else {
                        $amount = $holiday->no_of_hours * $hourlyRate * (($holiday->percentage-100)/100);
                    }
                }
                elseif($payrollRate == 'Daily') {
                    $amount = $holiday->no_of_hours * $hourlyRate * ($holiday->percentage/100);
                }
                if($paymentSetting->isRestday == 1) {
                    $restday_total += $amount;
                    $restday_hours += $holiday->no_of_hours;
                }
                else {
                    $holiday_total += $amount;
                }


                // if($holiday->meal == 1){
                    if(isset($holiday->hours_per_day) && !empty($holiday->hours_per_day)) {
                        foreach($holiday->hours_per_day as $dailyHours) {
                            if($dailyHours->meal == 1) {
                                $holidayRestdayMeal += floor($dailyHours->hours/$payrollSetting->restday_meal_every_hours);
                            }
                            $holidayRestdayTranspo += $dailyHours->transportation ?? 0;
                            $holidayRestdayTranspoAmount += $dailyHours->transportation_amount ?? 0;
                        }
                    } else {
                        $holidayRestdayMeal += floor($holiday->no_of_hours/$payrollSetting->restday_meal_every_hours);
                    }
                // }

                // $otMealsArray[] = $count;

                return array_merge((array) $holiday, ['amount' => $amount, 'isRestday' => $paymentSetting->isRestday]);
            });
        }

        $otMeal = 0;
        $otTranspo = 0;
        $otTranspoAmount = 0;
        if($overtime_totals) {
            // dd($overtime_totals);
            $overtime_totals = collect(json_decode($overtime_totals));

            $overtime_totals = $overtime_totals->map(function ($overtime) use($payrollSetting, $hourlyRate, &$overtime_total, &$otMeal, &$otTranspo, &$otTranspoAmount, &$employee_id) {
                // if($employee_id==2607) {
                //     dd($overtime->meal);
                // }
                $amount = $overtime->no_of_hours * $hourlyRate * ($overtime->percentage/100);
                $overtime_total += $amount;
                // $count = 0;

                if(isset($overtime->hours_per_day) && !empty($overtime->hours_per_day)) {
                    foreach($overtime->hours_per_day as $dailyHours) {
                        if($dailyHours->meal==1) {
                            $otMeal += floor($dailyHours->hours/$payrollSetting->overtime_meal_every_hours);
                        }
                        $otTranspo += $dailyHours->transportation;
                        $otTranspoAmount += $dailyHours->transportation_amount;
                    }
                } else {
                    $otMeal += floor($overtime->no_of_hours/$payrollSetting->overtime_meal_every_hours);
                }
                // if($overtime->meal == 1){
                // }

                // $otMealsArray[] = $count;
                // $otTranspo += $overtime->transportation ?? 0;
                return array_merge((array) $overtime, ['amount' => $amount]);
            });
            $overtime_hours = $overtime_totals->sum('no_of_hours');
        }

        // if($employee_id==2024) {
        //     dd(['totals'=>$overtime_totals, 'otMeal'=>$otMeal]);
        // }

        if ($night_diff_totals) {
            // Ensure $night_diff_totals is a collection
            $night_diff_totals = collect(is_string($night_diff_totals) ? json_decode($night_diff_totals) : $night_diff_totals);

            // Calculate the total night diff hours
            $night_diff_hours = $night_diff_totals->sum('amount');

            // Map through and compute new amounts
            $night_diff_total = 0; // Initialize total amount
            $night_diff_totals = $night_diff_totals->map(function ($nightDiff) use ($hourlyRate, &$night_diff_total) {
                $amount = $nightDiff->no_of_hours * $hourlyRate * ($nightDiff->percentage / 100);
                $night_diff_total += $amount;
                return array_merge((array) $nightDiff, ['amount' => $amount]);
            });

        }

        $obMeal = 0;
        $obMealAmount = 0;
        /**Processed OT if any  */
        $obTranspo = 0;
        $obTranspoAmount = 0;
        // if($employee_id=='60623') {

            // $obMeal = ProcessedOb::where(['employee_id' => $employee_id, 'payroll_date'=>$payroll_date])->whereNotNull('header_id')->whereNotNull('meal_amount')->distinct('ob_id')->count();
            // $obTranspo = ProcessedOb::where(['employee_id' => $employee_id, 'payroll_date'=>$payroll_date])->whereNotNull('header_id')->whereNotNull('transportation_amount')->distinct('ob_id')->count();
            // DB::enableQueryLog();
            // $obMealAmount = ProcessedOb::where(['employee_id' => $employee_id, 'payroll_date'=>$payroll_date])->whereNotNull('header_id')->whereNotNull('meal_amount')->groupBy('ob_id')->sum('meal_amount');
            // // dd(DB::getQueryLog());
            // $obTranspoAmount = ProcessedOb::where(['employee_id' => $employee_id, 'payroll_date'=>$payroll_date])->whereNotNull('header_id')->whereNotNull('transportation_amount')->groupBy('ob_id')->sum('transportation_amount');
            // dd($obMeal);
        // }

        /**BENEFITS */
        // DB::enableQueryLog();
        $employee_benefits = EmployeeBenefits::with(['benefit'])->where('employee_id', $employee_id)
        ->where(fn($q) => $q->where('date_from','<=',$cut_off_end)->where(fn($q2) => $q2->whereBetween('date_to', [$cut_off_start,$cut_off_end])->orWhereNull('date_to')))
        ->get();

        // if($employee_id=='707') {
        //     dd($work_days);
        // }

        $taxable_benefits_total = 0;
        $non_taxable_benefits_total = 0;
        $benefits = [];
        if($employee_benefits && $employee_benefits->isNotEmpty()) {
                $taxable_benefits_total = $employee_benefits->filter(fn($benefit) => $benefit['benefit']['taxable'] == 1)
                ->map(fn($benefit) => [
                    'amount' => match($benefit['benefit']['basis']) {
                        '3' => $benefit['benefit']['fixed'] == 1 ? $benefit['amount'] : round($benefit['amount'] - ((($benefit['amount']) * (12 / $payrollSetting->working_days_per_year)) * $days_absent),2),
                        // '2' => $benefit['amount'] * $work_days,
                        '2' => $benefit['amount'] * ceil($actual_work_days),
                        '1' => $benefit['amount'] * $work_hours
                    }
                ])
                ->sum('amount');
                // dd($employee_benefits->toArray());
                $non_taxable_benefits_total = $employee_benefits->filter(fn($benefit) => $benefit['benefit']['taxable'] == 0)
                ->map(fn($benefit) => [
                    'amount' => match($benefit['benefit']['basis']) {
                        '3' => $benefit['benefit']['fixed'] == 1 ? $benefit['amount'] : round($benefit['amount'] - ((($benefit['amount']) * (12 / $payrollSetting->working_days_per_year)) * $days_absent),2),
                        // '2' => $benefit['amount'] * $work_days,
                        '2' => $benefit['amount'] * ceil($actual_work_days),
                        '1' => $benefit['amount'] * $work_hours
                    }
                    ])
                    ->sum('amount');
                    $benefits = $employee_benefits->map(fn($benefit) => [
                'payroll_date' => $payroll_date,
                'cut_off' => $cut_off,
                'company_id' => $company_id,
                'dealership_id' => $dealership_id,
                'employee_id' => $employee_id,
                'benefit_id' => $benefit['benefits_type_id'],
                'benefit_name' => $benefit['benefit']['name'],
                'benefit_amount' => $benefit['amount'],
                'total_amount' => match($benefit['benefit']['basis']) {
                    '1' => $benefit['amount'] * $work_hours,
                    // '2' => $benefit['amount'] * $work_days,
                    '2' => $benefit['amount'] * ceil($actual_work_days),
                    '3' => $benefit['benefit']['fixed'] == 1 ? $benefit['amount'] : round($benefit['amount'] - ((($benefit['amount']) * (12 / $payrollSetting->working_days_per_year)) * $days_absent),2),
                },
                'basis' => match($benefit['benefit']['basis']) {
                    '1' => 'work hours',
                    '2' => 'attendance',
                    '3' => 'cut-off',
                },
                'days_month' => match($benefit['benefit']['basis']) {
                    '1' => $work_hours,
                    // '2' => $work_days,
                    '2' => ceil($actual_work_days),
                    '3' => '1/2',
                },
                'addition' => $benefit['add_less'] == 1
            ])->toArray();
        }
        if($holidayRestdayMeal > 0) {
            $benefits[] = [
                'payroll_date' => $payroll_date,
                'cut_off' => $cut_off,
                'company_id' => $company_id,
                'dealership_id' => $dealership_id,
                'employee_id' => $employee_id,
                'benefit_id' => 3,
                'benefit_name' => 'Meal Allowance',
                'benefit_amount' => $payrollSetting->restday_meal_amount,
                'total_amount' => $holidayRestdayMeal * $payrollSetting->restday_meal_amount,
                'basis' => 1,
                'days_month' => $holidayRestdayMeal,
                'addition' => 1,
            ];
            $non_taxable_benefits_total += $holidayRestdayMeal * $payrollSetting->restday_meal_amount;
        }
        if($otMeal > 0) {
            $benefits[] = [
                'payroll_date' => $payroll_date,
                'cut_off' => $cut_off,
                'company_id' => $company_id,
                'dealership_id' => $dealership_id,
                'employee_id' => $employee_id,
                'benefit_id' => 3,
                'benefit_name' => 'Meal Allowance',
                'benefit_amount' => $payrollSetting->overtime_meal_amount,
                'total_amount' => $otMeal * $payrollSetting->overtime_meal_amount,
                'basis' => 1,
                'days_month' => $otMeal,
                'addition' => 1,
            ];
            $non_taxable_benefits_total += $otMeal * $payrollSetting->overtime_meal_amount;
        }
        if($holidayRestdayTranspo > 0) {

                // $holidayRestdayTranspoAmount = ProcessedHolidayRestdayDuty::where(['employee_id' => $employee_id, 'payroll_date' => $payroll_date])->where(fn($q) => $q->whereNotNull('transportation_amount')->orWhere('transportation_amount', '>', 0))->sum('transportation_amount');
                $benefits[] = [
                    'payroll_date' => $payroll_date,
                    'cut_off' => $cut_off,
                    'company_id' => $company_id,
                    'dealership_id' => $dealership_id,
                    'employee_id' => $employee_id,
                    'benefit_id' => 17,
                    'benefit_name' => 'Transportation Allowance',
                    'benefit_amount' => null,
                    'total_amount' => $holidayRestdayTranspoAmount,
                    'basis' => 1,
                    'days_month' => $holidayRestdayTranspo,
                    'addition' => 1,
                ];
                $non_taxable_benefits_total += $holidayRestdayTranspoAmount;
        }

        // if($employee_id==661) {
        //     // dd(DB::getQueryLog());
        //     dd($benefits);
        // }
        if($otTranspo > 0) {
            if($payrollSetting->overtime_transpo_basis == 'fixed amount') {
                $benefits[] = [
                    'payroll_date' => $payroll_date,
                    'cut_off' => $cut_off,
                    'company_id' => $company_id,
                    'dealership_id' => $dealership_id,
                    'employee_id' => $employee_id,
                    'benefit_id' => 17,
                    'benefit_name' => 'Transportation Allowance',
                    'benefit_amount' => $payrollSetting->overtime_transportation_amount,
                    'total_amount' => $otTranspo * $payrollSetting->overtime_transportation_amount,
                    'basis' => 1,
                    'days_month' => $otTranspo,
                    'addition' => 1,
                ];
                $non_taxable_benefits_total += $otTranspo * $payrollSetting->overtime_transportation_amount;
            }
            else {
                // $otTranspoAmount = ProcessedOvertime::where(['employee_id' => $employee_id, 'payroll_date' => $payroll_date])->whereNotNull('transportation_amount')->sum('transportation_amount');
                $benefits[] = [
                    'payroll_date' => $payroll_date,
                    'cut_off' => $cut_off,
                    'company_id' => $company_id,
                    'dealership_id' => $dealership_id,
                    'employee_id' => $employee_id,
                    'benefit_id' => 17,
                    'benefit_name' => 'Transportation Allowance',
                    'benefit_amount' => null,
                    'total_amount' => $otTranspoAmount,
                    'basis' => 1,
                    'days_month' => $otTranspo,
                    'addition' => 1,
                ];
                $non_taxable_benefits_total += $otTranspoAmount;
            }
        }
        // if($obMeal > 0) {
        //     $benefits[] = [
        //         'payroll_date' => $payroll_date,
        //         'cut_off' => $cut_off,
        //         'company_id' => $company_id,
        //         'dealership_id' => $dealership_id,
        //         'employee_id' => $employee_id,
        //         'benefit_id' => 3,
        //         'benefit_name' => 'Meal',
        //         'benefit_amount' => null,
        //         'total_amount' => $obMealAmount,
        //         'basis' => 3,
        //         'days_month' => $obMeal,
        //         'addition' => 1,
        //     ];
        //     $non_taxable_benefits_total += $obMealAmount;
        // }

        // if($obTranspo > 0) {
        //         $benefits[] = [
        //             'payroll_date' => $payroll_date,
        //             'cut_off' => $cut_off,
        //             'company_id' => $company_id,
        //             'dealership_id' => $dealership_id,
        //             'employee_id' => $employee_id,
        //             'benefit_id' => 17,
        //             'benefit_name' => 'Transportation Allowance',
        //             'benefit_amount' => null,
        //             'total_amount' => $obTranspoAmount,
        //             'basis' => 3,
        //             'days_month' => $obTranspo,
        //             'addition' => 1,
        //         ];
        //         $non_taxable_benefits_total += $obTranspoAmount;
        // }

        // if($employee_id == 60653) dd($benefits);


        $adjusted_absent = $absent_amount != 0 && $payrollRate == 'Daily' ? $absent_amount - $leave_pay : $absent_amount;
        $gross_pay = 0;
        $gross_pay_for_sss = 0;
        $gross_pay = $basic_pay + $leave_pay + $taxable_benefits_total + $holiday_total + $restday_total + $overtime_total + $night_diff_total - ($late_amount + $undertime_amount + $adjusted_absent);

        $gross_pay_for_sss = $basic_pay + $leave_pay - ($late_amount + $undertime_amount + $adjusted_absent);

        // if($employee_id ==) {

        // }
        // if($employee_id == '2670'){
        //     echo $basic_pay .' + '. $leave_pay .' - '. $late_amount .' - '. $undertime_amount .' - '. $adjusted_absent .' = '. $gross_pay_for_sss;
        //     dd($gross_pay_for_sss);

        // }
        // dd();
        $commissionAmount = 0;
        if($taxApplication == "3" || $taxApplication == "1") { /** Forced tax */
            $commissionAmount = !empty($adjustments) ? collect($adjustments)->filter(fn($item) => str_contains(strtolower($item['payroll_item']['name']), 'commission') && $item['payroll_item']['taxable'] == 1)->sum('amount') : 0;
            $gross_pay_for_sss = $commissionAmount + (!$minimumWageEarner ? $gross_pay_for_sss : 0);
            // dd($commissionAmount);
        }

        // minimumWageEarner
        // $taxable_adjustments = 0;
        // if($taxApplication !=2){
        //      $taxable_adjustments = !empty($adjustments) ? collect($adjustments)->filter(fn($item) =>  $item['payroll_item']['taxable'] == 1)->sum('amount') : 0;
        //     //  $commissionAmount = !empty($adjustments) ? collect($adjustments)->filter(fn($item) =>  $item['payroll_item']['taxable'] == 1)->sum('amount') : 0;
        //     $gross_pay_for_sss +=$taxable_adjustments;
        // }
        $sssBasisAdjustments = !empty($adjustments) ? collect($adjustments)->filter(
            fn($item) => str_contains(strtolower($item['payroll_item']['name']), 'basic pay') ||
            str_contains(strtolower($item['payroll_item']['name']), 'ot pay') ||
            str_contains(strtolower($item['payroll_item']['name']),'holiday pay') // ||
            // str_contains(strtolower($item['payroll_item']['name']),'honorarium allowance')
            )->sum('amount') : 0 ;
            // if($employee_id == '2751') dd(['basic' =>$basic_pay,'gross' => $gross_pay_for_sss,'adj' => $sssBasisAdjustments]);
            // DB::enableQueryLog();
            $gross_pay_for_sss += in_array($taxApplication,[1,3]) ? $sssBasisAdjustments : 0;

        // DB::enableQueryLog();
        $sssTable = SssTable::where('compensation_from', '<=', $gross_pay_for_sss + ($prevPayroll ? $prevPayroll->gross_pay_for_sss : 0))->where('compensation_to', '>=', $gross_pay_for_sss + ($prevPayroll ? $prevPayroll->gross_pay_for_sss : 0))->first();

        $sss_employee_share = 0;
        $sss_employer_share = 0;

        if($sssTable) {
            $sss_employee_share = $sssTable->total_ee - ($prevPayroll ? $prevPayroll->sss_employee_share : 0);
            $sss_employer_share = $sssTable->total_er;
        }

        $philhealth_contribution = 0;
        // dd($sss_employee_share);
        if($payrollRate == 'Monthly') {
            $phicTable = PhicTable::where('salary_range', '<=',$salaryRate)->orderBy('salary_range', 'desc')->first();
            if($phicTable) {
                if($cut_off == '1st') {
                    /**get contribution for half-month */
                    $philhealth_contribution = (($salaryRate/2) < 100000 ? (($salaryRate/2) * ($phicTable->contribution_rate/100))/2 : 2500);
                    /**set amount to minimun or maximum contribution */
                    $philhealth_contribution = min(max($philhealth_contribution,250),2500);
                } else {
                    /**get philhealth for the whole month */
                    $philhealth_contribution = (($salaryRate) < 100000 ? (($salaryRate) * ($phicTable->contribution_rate/100))/2 : 2500);
                    /**deduct phic contribution for 1st cut-off*/
                    $philhealth_contribution -= $prevPayroll->philhealth_contribution ?? 0;
                    /**set minimum amount to zero */
                    $philhealth_contribution = max($philhealth_contribution,0);
                }
                // if($employee_id=='0003') {
                //     dd(['salary' => $salaryRate/2, 'rate' => $phicTable->contribution_rate, 'contri' => $philhealth_contribution, 'actual' => (($salaryRate/2) * ($phicTable->contribution_rate/100))/2]);
                // }
            }
        } elseif($payrollRate == 'Daily') {
            $salaryRange = $basic_pay + $leave_pay + ($prevPayroll->basic_pay ?? 0) + ($prevPayroll->leave_pay ?? 0);
            // if($employee_id == 2625)

            // dd($salaryRange);
            // dd(['basic' => $basic_pay, 'leave' => $leave_pay, 'prev_basic' => $prevPayroll->basic_pay, 'prev_leave' => $prevPayroll->leave_pay]);

            $phicTable = PhicTable::where('salary_range', '<=',$salaryRange)->orderBy('salary_range', 'desc')->first();

            // dd([
            //     'basic_pay' => $basic_pay,
            //     'leave_pay' => $leave_pay,
            //     'prev_basic_pay' => $prevPayroll->basic_pay,
            //     'prev_leave_pay' => $prevPayroll->leave_pay,
            //     'salaryRange' => $salaryRange,
            //     'calculation' => '('. $salaryRange .')' .'*'. '('.$phicTable->contribution_rate.'/100))/2'
            // ]);

            if($phicTable) {
                if($cut_off == '1st') {
                        $philhealth_contribution = (($salaryRange) < 100000 ? (($salaryRange) * ($phicTable->contribution_rate/100))/2 : 2500);
                        $philhealth_contribution = min(max($philhealth_contribution,250),2500);
                    } else {
                        $philhealth_contribution = (($salaryRange) < 100000 ? (($salaryRange) * ($phicTable->contribution_rate/100))/2 : 2500);
                        $philhealth_contribution -= $prevPayroll->philhealth_contribution ?? 0;
                        $philhealth_contribution = max($philhealth_contribution,0);
                    }
                }
        }

        // $philhealth_contribution = $philhealth_contribution > 2500 ? 2500 : $philhealth_contribution;
        // $philhealth_contribution = $philhealth_contribution <= 250 ? 250 : $philhealth_contribution;

        // $pagibig_contribution = 100;
        // if($salary->fixed_pagibig_contribution) {
        //     if($salary->fixed_pagibig_contribution > 100) {
        //         $pagibig_contribution = $salary->fixed_pagibig_contribution;
        //     } else {
        //         $pagibig_contribution = 100;
        //     }
        // }


        // if($salary->fixed_pagibig_amount_er) {
        //     if($salary->fixed_pagibig_amount_er > 100) {
        //         $pagibig_contribution_employer = $salary->fixed_pagibig_amount_er;
        //     } else {
        //         $pagibig_contribution_employer = 100;
        //     }
        // } else {
        //     $pagibig_contribution_employer = 100;
        // }
        $pagibig_contribution = $salary->fixed_pagibig_contribution ?? 0 > 100 ? $salary->fixed_pagibig_contribution ?? 0 : 100;
        $pagibig_contribution_employer = $salary->fixed_pagibig_amount_er ?? 0 > 100 ? $salary->fixed_pagibig_amount_er ?? 0 : 100;

        if($gross_pay == 0){
            $pagibig_contribution = 0;
            $pagibig_contribution_employer = 0;
        }

        $taxPeriod = $payrollSetting->tax_table_basis;

        $currentTaxable = 0;
        $income_before_tax =  $gross_pay - ($sss_employee_share + $philhealth_contribution + $pagibig_contribution);

        // if($taxApplication == "3") { /** Forced tax */
        //     // $commissionAmount = !empty($adjustments) ? collect($adjustments)->filter(fn($item) => str_contains(strtolower($item['payroll_item']['name']), 'commission') && $item['payroll_item']['taxable'] == 1)->sum('amount') : 0;
        //     $commissionAmount = !empty($adjustments) ? collect($adjustments)->filter(fn($item) =>  $item['payroll_item']['taxable'] == 1)->sum('amount') : 0;
        //     $currentTaxable = $commissionAmount;
        //     if($basic_pay <= 0) { /**if No basic pay, deduct govt contributions */
        //         $currentTaxable -= ($sss_employee_share + $philhealth_contribution + $pagibig_contribution);
        //     }
        // } elseif($taxApplication == "2" ) { /**No Tax */
        //     $currentTaxable = 0;
        // } else { /**Based on table or no setup */
        //     $taxableAdjustments = !empty($adjustments) ? collect($adjustments)->filter(fn($item) => $item['payroll_item']['taxable'] == 1)->sum('amount') : 0;
        //     $currentTaxable = ($gross_pay + $taxableAdjustments) - ($sss_employee_share + $philhealth_contribution + $pagibig_contribution);
        // }
        $taxableAdjustments = !empty($adjustments) ? collect($adjustments)->filter( fn($item) => $item['payroll_item']['taxable'] == 1)->sum('amount') : 0 ;


        switch($taxApplication){
            case 3; /** Forced tax */
                $currentTaxable  = $taxableAdjustments + $gross_pay;
                $currentTaxable -= ($sss_employee_share + $philhealth_contribution + $pagibig_contribution);
                // if($basic_pay <= 0) { /**if No basic pay, deduct govt contributions */
                // }
                if($minimumWageEarner) {
                    $currentTaxable = $commissionAmount;
                }
                break;
            case 2: /** No Tax */
                $currentTaxable  = 0;
                break;

            default: /** Base on table */
                $currentTaxable = ($gross_pay + $taxableAdjustments) -  ($sss_employee_share + $philhealth_contribution + $pagibig_contribution);
                if($minimumWageEarner) {
                    $currentTaxable = $commissionAmount;
                }
                // dd([
                //     'currentTaxable' => $currentTaxable,
                //     'gross_pay' => $gross_pay,
                //     'taxableAdjustments' => $taxableAdjustments,
                //     'sss_employee_share' => $sss_employee_share,
                //     'philhealth_contribution' => $philhealth_contribution,
                //     'pagibig_contribution' => $pagibig_contribution,
                // ]);
                break;
        }

        if($currentTaxable < 0){
            $currentTaxable = 0;
        }
        $prevTaxable = 0;
        if($taxPeriod=="monthly") {
            $prevTaxable =  $prevPayroll ? $prevPayroll->taxable_amount : 0;
        }
        $taxable_amount =  $currentTaxable + $prevTaxable;

        // if($employee_id== 741) {
        //     dd(['ta' => $taxApplication, 'tamount' => $taxable_amount,'basic'=> $basic_pay,'taxben' => $taxable_benefits_total, 'salary' => $salary , 'adjustments' => $adjustments,]);
        // }

        DB::enableQueryLog();
        $taxTable = TaxTable::where('period', $taxPeriod)
            ->whereRaw('CAST(compensation_range AS DECIMAL(10, 2)) <= ?', [(float) $taxable_amount])
            ->orderByRaw('CAST(compensation_range AS DECIMAL(10, 2)) DESC')
            ->first();
        // $taxTable = TaxTable::where('period', $taxPeriod)->where('compensation_range', '<=', $taxable_amount)->orderBy('compensation_range', 'desc')->first();
        // if($employee_id==60699){
        //     // dd(DB::getQueryLog());
        //     dd($taxTable);
        // }
        $withholding_tax = 0;
        if($taxable_amount > 0 && $taxTable) {
            $basicTax = $taxTable->prescribed_withholding_tax;
            $compensationRange = $taxTable->compensation_range;
            $taxPercentage = $taxTable->percentage > 0 ? $taxTable->percentage/100 : 0;
            $addtlTax = $taxable_amount > $compensationRange ? ($taxable_amount - $compensationRange) * $taxPercentage : 0;
            if($taxPeriod=='monthly') {
                $withholding_tax = $basicTax + $addtlTax - ($prevPayroll ? $prevPayroll->withholding_tax : 0);
            } else {
                $withholding_tax = $basicTax + $addtlTax;
            }
        }

        // $taxable_amount = $taxable_amount - ($prevPayroll ? $prevPayroll->taxable_amount : 0);
        $income_after_tax = $income_before_tax - $withholding_tax;
        $non_taxable_additions = 0; //allowances and other benefits
        $non_taxable_deductions = 0; //loans, adjustments, etc
        /**get loans */
        $loans=[];
        $loans_total = 0;
        $employee_loans = EmployeeLoan::with(['loan'])
            ->where('employee_id', $employee_id)
            ->where('balance', '>', 0)
            ->where(fn($q)=> $q->where('frequency',$cut_off=='1st' ? 1 : 2)->orWhere('frequency',3))
            ->where('effectivity_date', '<=', $cut_off_end)
            ->where(fn($q) => $q->where('paused',0)->orWhere(fn($q2) => $q2->where('paused',1)->whereDate('resume_date', '<=', $cut_off_end)))
            ->whereNotExists(fn($q) => $q->select(DB::raw(1))
                ->from('loan_paused_histories')
                ->whereRaw('loan_paused_histories.employee_loan_id = employee_loans.id')
                ->where('loan_paused_histories.enabled', 1)
                ->where('loan_paused_histories.date_pause', '<=', $cut_off_end)
                ->where(fn($q2) => $q2->where('date_resume','<=',$cut_off_end)->orWhereNull('date_resume'))
            )
            ->get();
        if($employee_loans && $employee_loans->isNotEmpty()) {
            $loans = $employee_loans->map(function ($loan) use ($payroll_date, $cut_off, $company_id, $dealership_id, $employee_id) {
            $amount = $loan['paid'] == 1
                ? 0
                : match($loan['frequency']) {
                    '3' => (($loan['monthly'] / 2) < $loan['balance'])
                        ? $loan['monthly'] / 2
                        : $loan['balance'],
                    default => $loan['month'] < $loan['balance']
                        ? $loan['monthly']
                        : $loan['balance'],
                };

            return [
                'payroll_date'     => $payroll_date,
                'cut_off'          => $cut_off,
                'company_id'       => $company_id,
                'dealership_id'    => $dealership_id,
                'employee_id'      => $employee_id,
                'employee_loan_id' => $loan['id'],
                'loan_type_id'     => $loan['loan_id'],
                'loan_type_name'   => $loan['loan']['name'],
                'advance_payment'  => $loan['paid'],
                'amount'           => $amount,
                'balance'          => $loan['paid'] == 1 ? 0 : max($loan['balance'] - $amount, 0), // prevents negative balance
            ];
        });

        $loans_total = $loans->sum('amount');

        }
        /**get contributions */
        $payment_contributions=[];
        $payment_contributions_total = 0;
        $employee_payment_contributions = PaymentContribution::with(['payroll_item'])
            ->where('employee_id', $employee_id)
            ->where(fn($q)=> $q->where('frequency',$cut_off=='1st' ? 1 : 2)->orWhere('frequency',3))
            ->where('effectivity_date', '<=', $cut_off_end)
            ->where(fn($q) => $q->where('paused',0)->orWhere(fn($q2) => $q2->where('paused',1)->whereDate('resume_date', '<=', $cut_off_end)))
            ->whereNotExists(fn($q) => $q->select(DB::raw(1))
                ->from('payment_contribution_histories')
                ->whereRaw('payment_contribution_histories.employee_pc_id = payment_contributions.id')
                ->where('payment_contribution_histories.enabled', 1)
                ->where('payment_contribution_histories.date_pause', '<=', $cut_off_end)
                ->where(fn($q2) => $q2->where('payment_contribution_histories.date_resume','<=',$cut_off_end)->orWhereNull('payment_contribution_histories.date_resume'))
            )
            ->whereHas('payroll_item', fn($q) => $q->whereNull('deleted_at'))
            ->get();
        if($employee_payment_contributions && $employee_payment_contributions->isNotEmpty()) {
            $payment_contributions = $employee_payment_contributions->map(fn($contribution) => [
                'payroll_date' => $payroll_date,
                'cut_off' => $cut_off,
                'company_id' => $company_id,
                'dealership_id' => $dealership_id,
                'employee_id' => $employee_id,
                'employee_contribution_id' => $contribution['id'],
                'contribution_id' => $contribution['contribution_id'],
                'contribution_name' => $contribution['payroll_item']['name'],
                'amount' => $contribution['amount'],
                'balance' => ((float)$contribution['amount'] ?? 0) + ((float)$contribution['credit'] ?? 0),
            ]);
            $payment_contributions_total = $payment_contributions->sum('amount');
        }

        if($basic_pay == 0) {
            $loans_total = 0;
            $payment_contributions_total = 0;
        }

        /** get adjustments */
        $adjustment_totals = 0;
        $other_deductions = 0;
        $adjustments = collect($adjustments);


        if($adjustments->isNotEmpty()) {
            $adjustment_totals = $adjustments->filter(fn($adjustment) => $adjustment['payroll_item']['add_less'] == 1 || $adjustment['payroll_item']['add_less'] == NULL )->sum('amount');
            $other_deductions = $adjustments->filter(fn($adjustment) => $adjustment['payroll_item']['add_less'] == 2)->sum('amount');
        }

        // dd($adjustment_totals);
        if($basic_pay == 0) {
            $other_deductions = 0;
        }

        $non_taxable_additions += $adjustment_totals + $non_taxable_benefits_total;
        $non_taxable_deductions += $other_deductions + $loans_total + $payment_contributions_total;
        $net_pay = 0;
        $employee_shields = [];
        if($salaryRate > 0) {
            $net_pay = $income_after_tax + $non_taxable_additions - $non_taxable_deductions;
            /**Add back shields to net pay */
            if(isset($shields->tax) && $shields->tax==1) {
                $net_pay += $withholding_tax;
                $employee_shields['tax'] = $withholding_tax;
            }
            if(isset($shields->sss) && $shields->sss==1) {
                $net_pay += $sss_employee_share;
                $employee_shields['sss'] = $sss_employee_share;
            }
            if(isset($shields->pagibig) && $shields->pagibig==1) {
                $net_pay += $pagibig_contribution;
                $employee_shields['pagibig'] = $pagibig_contribution;
            }
            if(isset($shields->philhealth) && $shields->philhealth==1) {
                $net_pay += $philhealth_contribution;
                $employee_shields['philhealth'] = $philhealth_contribution;
            }
        }

        // dd($loans_total);

        if($net_pay < 0) $net_pay = 0;

         // $details = $request->details;
         $data = array_merge($request->except([
            'cut_off_start',
            'cut_off_end',
            'work_days',
            'actual_work_days',
            'work_hours',
            'leave_hours',
            'minutes_late',
            'minutes_undertime',
            'days_absent',
         ]), [
            'salary_rate' => $salaryRate,
            'basic_pay' => $basic_pay,
            'cola' => $cola,
            'leave_pay' => $leave_pay,
            'other_income' => $taxable_benefits_total,
            'benefits_total' => $non_taxable_benefits_total,
            'holiday_total' => $holiday_total,
            'restday_total' => $restday_total,
            'overtime_total' => $overtime_total,
            'night_diff_total' => $night_diff_total,
            'late_amount' => $late_amount,
            'undertime_amount' => $undertime_amount,
            'absent_amount' => $absent_amount,
            'loans_total' => $loans_total,
            'payment_contributions_total' => $payment_contributions_total,
            'gross_pay' => $gross_pay,
            'gross_pay_for_sss' => $gross_pay_for_sss,
            'sss_employee_share' => $sss_employee_share,
            'sss_employer_share' => $sss_employer_share,
            'philhealth_contribution' => $philhealth_contribution,
            'pagibig_contribution' => $pagibig_contribution,
            'pagibig_contribution_employer' => $pagibig_contribution_employer,
            'income_before_tax' => $income_before_tax,
            'taxable_amount' => $currentTaxable,
            'withholding_tax' => $withholding_tax,
            'income_after_tax' => $income_after_tax,
            'adjustment_totals' => $adjustment_totals,
            'other_deductions' => $other_deductions,
            'net_pay' => $net_pay,
            'taxTable' => $taxTable,
            'work_days' => $work_days,
            'actual_work_days' => $actual_work_days,
            'work_hours' => $work_hours,
            'details' => [
                'cut_off_start' => $cut_off_start,
                'cut_off_end' => $cut_off_end,
                'work_days' => $work_days,
                'actual_work_days' => $actual_work_days,
                'work_hours' => $work_hours,
                'restday_hours' => $restday_hours,
                'overtime_hours' => $overtime_hours,
                'night_diff_hours' => $night_diff_hours,
                // 'holiday_hours' => $holiday_hours,
                'leave_hours' => $leave_hours,
                'minutes_late' => $minutes_late,
                'minutes_undertime' => $minutes_undertime,
                'days_absent' => $days_absent,
                'adjustments' => $adjustments,
                'benefits' => $benefits,
                'loans' => $loans,
                'payment_contributions' => $payment_contributions,
                'overtimes' => $overtime_totals,
                'holidays' => $holiday_totals,
                'night_differrentials' => $night_diff_totals,
                'shields' => $employee_shields,
            ]
        ]);
        //  if($employee_id == 2713) {
        //     dd(['TA' => $taxApplication, 'salary_rate' => $salaryRate, 'data' => $data]);
        // }
        return response()->json($data);

    }

    public function myPayslips(Request $request) {
        $employee_id = $request->input('employee_id');
        $keyword = $request->input('keyword', null);
        if($keyword) $keyword = str_replace(' ','%', $keyword);
        $perPage = $request->input('per_page', PHP_INT_MAX);
        $sortBy = $request->input('sortBy', null);
        $sortType = $request->input('sortType', null);
        $data = Payroll::with(['company','dealership', 'summary' => fn($q) =>$q->where('employee_id', $employee_id)->with(['employee'])])
        ->whereHas('summary', fn($q) => $q->where('employee_id',$employee_id))
        ->whereNotNull('released_at')
        ->when($keyword, fn($q) => $q->whereHas('company', fn($qc) => $qc->where('name', 'like', '%'.$keyword.'%'))->orWhereHas('dealership', fn($qc) => $qc->where('name', 'like', '%'.$keyword.'%'))->orWhere('payroll_date', 'like', $keyword.'%')->orWhere('cut_off', 'like', $keyword.'%'))
        ->when($sortBy, fn ($q) => $q->orderBy($sortBy, $sortType))
        ->paginate($perPage);

        return response()->json($data);
    }

    public function employeePayslips(Request $request) {
        $employee_id = $request->input('employee_id');
        $payroll_date = $request->input('payroll_date', null);
        $payslips = Payroll::with(['company','dealership'])
        ->whereHas('summary', fn($q) => $q->where('employee_id',$employee_id))
        ->when($payroll_date, fn($q) => $q->where('payroll_date', $payroll_date))
        ->whereNotNull('released_at')
        ->orderBy('payroll_date', 'DESC')
        ->get();
        $data = [];
        $data[0] = [
                'payroll_date' => null,
                'company_id' => null,
                'company_name' => null,
                'dealer_id' => null,
                'dealer_name' => null,
                'cut_off' => null,
                'date_released' => null,
            ];
        $frontend_url = env('FRONTEND_URL');
        $token = env('JWT_SECRET');
        if($payslips && !empty($payslips->toArray())) {
            $data = $payslips->map(fn($row) => [
                'payroll_date' => $row['payroll_date'],
                'company_id' => $row['company_id'],
                'company_name' => $row['company']['name'],
                'dealer_id' => $row['dealership_id'],
                'dealer_name' => $row['dealership']['name'],
                'cut_off' => $row['cut_off'],
                'date_released' => $row['released_at'],
                'link' => $frontend_url."/view-payslip?e=".$employee_id."&p=".$row['payroll_date']."&t=".$token,
            ]);
        }
        return response()->json($data);
    }

    public function employeePayrollDates(Request $request)
    {
        $employee_id = $request->input('employee_id');
        $year = $request->input('year', date('Y'));
        $data = Payroll::select('payroll_date')
        ->when($year, fn($q) => $q->where('payroll_date','like',$year.'%'))
        ->whereHas('summary', fn($q) => $q->where('employee_id',$employee_id))
        ->whereNotNull('released_at')
        ->orderBy('payroll_date','DESC')
        ->get();
        if($data && $data->isNotEmpty()) {
            $data = $data->toArray();
        } else {
            $data = [
                [
                    'payroll_date'=>null,
                ]
            ];
        }
        return response()->json($data);
    }


    public function employeePayslipDetails(Request $request) {
        $payroll_date = $request->input('payroll_date');
        $employee_id = $request->input('employee_id');
        $summary = PayrollSummary::with(['employee','payroll' => fn($q) => $q->with(['company','dealership'])])->where(['payroll_date' => $payroll_date, 'employee_id' => $employee_id])->first();
        $data = [];
        $data[0] = [
                'employee_id' => null,
                'employee_name' => null,
                'payroll_date' => null,
                'company_id' => null,
                'company_code' => null,
                'company_name' => null,
                'dealer_id' => null,
                'dealer_code' => null,
                'dealer_name' => null,
                'cut_off' => null,
                'date_released' => null,
                'grossPay' => null,
                'deductions' => null,
                'netPay' => null,
                'link' => null,
            ];
        $frontend_url = env('FRONTEND_URL');
        $token = env('JWT_SECRET');
        if($summary) {
            $salary_rate = $summary->salary_rate;
            $basic_pay = $summary->basic_pay;
            $cola = $summary->cola;
            $other_income = $summary->other_income;
            $absent_amount = $summary->absent_amount;
            $late_amount = $summary->late_amount;
            $undertime_amount = $summary->undertime_amount;
            $overtime_amount = $summary->overtime_total;
            $night_diff_amount = $summary->night_diff_total;
            $leave_amount = $summary->leave_pay;
            $holiday_amount = !empty($summary->details['holidays'] ? collect($summary->details['holidays'])->sum('amount') : 0) ;
            $restday_amount = $summary->restday_total;
            $adjustments_amount = $summary->adjustments_total;
            $benefits_amount = $summary->benefits_total;
            $total_income = $basic_pay + $cola + $other_income + $overtime_amount + $night_diff_amount + $leave_amount + $holiday_amount + $restday_amount + $adjustments_amount + $benefits_amount - ($absent_amount + $late_amount + $undertime_amount);
            $sss_amount = $summary->sss_employee_share;
            $philhealth_amount = $summary->philhealth_contribution;
            $pagibig_amount = $summary->pagibig_contribution;
            $withholding_tax = $summary->withholding_tax;
            $other_deductions_amount = $summary->other_deductions;
            $loans_amount = $summary->loans_total;
            $total_deductions = $sss_amount + $philhealth_amount + $pagibig_amount + $withholding_tax + $other_deductions_amount + $loans_amount;
            $net_pay = $summary->net_pay;
            $cut_off = Carbon::parse($summary->payroll->cut_off_start)->format('M j'). ' to '.Carbon::parse($summary->payroll->cut_off_end)->format('M j');
            $salary_date = Carbon::parse($payroll_date)->format('M j Y');
            $employee_name = $summary->employee->firstname." ".$summary->employee->lastname;
            $data = [
                'employee_id' => $employee_id,
                'employee_name' => $employee_name,
                'payroll_date' => $salary_date,
                'company_id' => $summary->company_id,
                'company_code' => $summary->payroll->company->code,
                'company_name' => $summary->payroll->company->name,
                'dealer_id' => $summary->dealership_id,
                'dealer_code' => $summary->payroll->dealership->code,
                'dealer_name' => $summary->payroll->dealership->name,
                'cut_off' => $cut_off,
                'date_released' => $summary->payroll->released_at,
                'grossPay' => number_format(round($total_income,2),2),
                'deductions' => number_format(round($total_deductions,2),2),
                'netPay' => number_format($net_pay,2),
                'link' => $frontend_url."/view-payslip?e=".$employee_id."&p=".$payroll_date."&t=".$token,
            ];

            return response()->json($data, 200);
        }
    }

    public function saveFile(Request $request)
    {
        // Validate the file
        $request->validate([
            'file' => 'required|file|mimes:xlsx'
        ]);

        // Save the file
        if ($request->file('file')) {
            $filePath = $request->file('file')->store('generated_payrolls', 'public');
            return response()->json(['message' => 'File saved successfully', 'file' => $filePath], 201);
        }

        return response()->json(['message' => 'File upload failed'], 422);
    }

    public function saveFilePdf(Request $request)
    {
        // Validate the file
        $request->validate([
            'pdf_file' => 'required|file|mimes:pdf'
        ]);

        // Save the file
        if ($request->file('pdf_file')) {
            $filePath = $request->file('pdf_file')->store('generated_payrolls_pdf', 'public');
            return response()->json(['message' => 'PDF File saved successfully', 'pdf_file' => $filePath], 201);
        }

        return response()->json(['message' => 'File upload failed'], 422);
    }

    public function viewFile(Request $request)
    {
        $filename = $request->filename;
        $folder = $request->folder;
         // Define the path to the file
         $filePath = "public/{$folder}/{$filename}";

        // Check if the file exists in storage
        if (Storage::exists($filePath)) {
            // Return the file as a stream with appropriate headers for download
            return Storage::download($filePath, $filename, [
                'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            ]);
        }
        return response()->json(['message' => 'File not found'], 422);
    }

    public function post(int $id)
    {
        $data = Payroll::find($id);

        if(!$data) return response()->json(['message' => 'Record not found'], 204);

        DB::beginTransaction();

        $payroll_summary = PayrollSummary::where('reference_no', $data->reference_no)->get();

        foreach($payroll_summary as $summary){
            foreach($summary['details']['payment_contributions'] as $contribution){

                $payment_history = [
                    'employee_id' => $summary->employee_id,
                    'employee_pc_id' => $contribution['employee_contribution_id'],
                    'reference_no' => $data->reference_no,
                    'amount' => $contribution['amount'],
                    'date_contributed' => $this->current_datetime,
                    'payroll_date' => $data->payroll_date,
                ];

                try {
                    $paymentContribution = new EmployeePaymentContributionHistory();
                    $paymentContribution->fill($payment_history);
                    $paymentContribution->save();
                    $paymentContribution->touch();
                    DB::connection()->commit();
                } catch(Throwable $e) {
                    DB::rollback();
                    return response()->json([
                        'status'  => false,
                        'message' => 'Unable to process request. Please try again.',
                        'data'    => $e->getMessage()
                    ]);
                }
            }
        }

        try {
            $data->fill(['posted_at' => $this->current_datetime, 'posted_by' => Auth::user()->employee_id]);
            $data->touch();
            $data->save();
            DB::commit();
            return response()->json(['message' => "Record successfully released!"],201);
        } catch (Throwable $e) {
            DB::rollback();
            return response()->json([
                'status'  => false,
                'message' => 'Unable to process request. Please try again.',
                'data'    => $e->getMessage()
            ]);
        }
    }

    public function release(int $id)
    {
        DB::beginTransaction();

        $data = Payroll::find($id);

        if($data){
            $payrollSummary = PayrollSummary::where('reference_no', $data->reference_no)->get();

            foreach($payrollSummary as $summary){
                // if($summary->employee_id == '1841'){
                    foreach ($summary->details['loans'] as $loan) {

                        $employeeLoan = EmployeeLoan::with('loan')
                            ->where('employee_id', $summary->employee_id)
                            ->where('loan_id', $loan['loan_type_id'])
                            ->first();

                        $loanBalance = $employeeLoan->balance - $loan['amount'];

                        $employeeLoanUpdate = [
                            'balance' => $loanBalance < 0 ? 0 : $loanBalance
                        ];

                        $loanHistory = Loan::where('enabled', 1)
                            ->where('employee_loan_id', $loan['employee_loan_id'])
                            ->whereNull('date_paid')
                            ->orderBy('id')
                            ->first();

                        $loanHistoryUpdates = [
                            'amount_paid' => $loan['amount'] ?? 0,
                            'date_paid' => $this->current_datetime,
                            'status' => 1
                        ];

                        try {
                            if ($employeeLoan) {
                                $employeeLoan->fill($employeeLoanUpdate);
                                $employeeLoan->save();
                                $employeeLoan->touch();
                            }

                            if ($loanHistory) {
                                $loanHistory->fill($loanHistoryUpdates);
                                $loanHistory->save();
                                $loanHistory->touch();
                            }


                            DB::connection()->commit();

                        } catch(Throwable $e) {
                            DB::rollback();
                            return response()->json([
                                'status'  => false,
                                'message' => 'Unable to process request. Please try again.',
                                'data'    => $e->getMessage()
                            ]);
                        }
                    }
                // }
            }
        }

        if(!$data) return response()->json(['message' => 'Record not found'], 204);

        try {

            $data->fill(['released_at' => $this->current_datetime, 'released_by' => Auth::user()->employee_id]);
            $data->touch();
            $data->save();
            DB::commit();
            return response()->json(['message' => "Record successfully released!"],201);

        } catch (Throwable $e) {
            DB::rollback();
            return response()->json([
                'status'  => false,
                'message' => 'Unable to process request. Please try again.',
                'data'    => $e->getMessage()
            ]);
        }
    }

    public function getPayrollSummaries(Request $request)
    {
        $payroll_date = $request->input('payroll_date');
        $company_id = $request->input('company_id');
        $dealership_id = $request->input('dealership_id');

        $payrollSummaries = PayrollSummary::with(['employee','employee.position',
            'payroll' => fn($q) => $q->with([
                'company',
                'dealership',
            ])])
            ->where(['payroll_date' => $payroll_date, 'company_id' => $company_id, 'dealership_id' => $dealership_id])
            ->get()
            ->map(function ($item) {
                return [
                    ...$item->toArray(),
                    'employee_name' => $item->employee->lastname . ', ' . $item->employee->firstname . ' (' . $item->employee->employee_id . ')',
                    'position_name' => $item->employee->position?->title ?? null,
                    'actual_work_days' => $item->details['actual_work_days'] ?? null,
                    'work_hours' => $item->details['work_hours'] ?? null,
                    'adjustments' => $item->details['adjustments'] ?? null,
                    'loans_total' => $item->loans_total ?? null,
                    'adjustment_totals' => $item->adjustments_total ?? null,
                    // 'payment_contributions_total' => $item->payment_contributions_total ?? null,
                    // 'loans' => $item->loans ?? null,
                ];
            })
            ->values()
            // ->first();
            ->toArray();

            // dd($payrollSummaries[17]['adjustments_total']);

        return response()->json($payrollSummaries);
    }
}
