<?php

namespace  App\Repositories\Hrm\Payroll;

use Carbon\Carbon;
use App\Models\User;
use App\Models\Finance\Account;
use App\Models\Finance\Expense;
use App\Models\Payroll\Commission;
use Illuminate\Support\Facades\DB;
use App\Models\Finance\Transaction;
use App\Models\Payroll\AdvanceSalary;
use App\Models\Payroll\SalaryGenerate;
use Illuminate\Support\Facades\Session;
use App\Models\Payroll\AdvanceSalaryLog;
use App\Models\Payroll\SalaryPaymentLog;
use App\Models\Hrm\Department\Department;
use App\Models\Payroll\SalarySetupDetails;
use App\Helpers\CoreApp\Traits\ApiReturnFormatTrait;
use Modules\SpecialAttendance\Entities\DutyCalendar;

class SalaryRepository
{
    use ApiReturnFormatTrait;

    protected $model;

    public function __construct(SalaryGenerate $model)
    {
        $this->model = $model;
    }

    public function model($filter = null)
    {
        $model = $this->model;
        if ($filter) {
            $model = $this->model->where($filter);
        }

        return $model;
    }

    public function fields()
    {
        return [
            _trans('common.ID'),
            _trans('common.Name'),
            _trans('payroll.Salary'),
            _trans('payroll.Month'),
            _trans('payroll.Salary Type'),
            _trans('payroll.Summary'),
            _trans('payroll.Status'),
            _trans('payroll.Action'),
        ];
    }

    function getPayslipList($request)
    {
        try {
            $content = $this->model->query()->where('company_id', auth()->user()->company_id);
            $params = [];
            if (auth()->user()->role->slug == 'staff') {
                $params['user_id'] = auth()->user()->id;
            }

            if ($request->month) {
                $content = $content->where('date', 'LIKE', '%' . $request->month . '%');
            } else {
                $content = $content->whereYear('date', date('Y'));
            }

            $content = $content->where($params)->latest()->get();

            $payslip_collection = $content->map(function ($payslip) {
                return [
                    'id' => $payslip->id,
                    'employee_name' => @$payslip->employee->name,
                    'month' => date('F', strtotime($payslip->date)),
                    'is_calculated' => $payslip->is_calculated == 1 ? true : false,
                    'salary' => $payslip->gross_salary,
                    // 'payslip_view_link'=>route('appPayslip.show.html',[encrypt($payslip->id),encrypt($payslip->user_id)] ),
                    'payslip_link' => route('appPayslip.show', [encrypt($payslip->id), encrypt($payslip->user_id)]),
                ];
            });
            return $this->responseWithSuccess('Payslip List', $payslip_collection);
        } catch (\Throwable $th) {
            throw $th;
            return $this->responseWithError($th->getMessage(), [], 500);
        }
    }

    public function staffDataTable($request)
    {

        $content = $this->model->query()->with('employee:id,name,department_id,payslip_type')->where('company_id', auth()->user()->company_id);
        $params = [];
        $params['user_id'] = auth()->user()->id;
        if (@$request->status_id) {
            $params['status_id'] = $request->status_id;
        }
        if (@$request->date) {
            $content = $content->whereMonth('date', date('m', strtotime($request->date)));
        }
        if (@$request->department_id) {
            $content->whereHas('employee', function ($query) use ($request) {
                $query->where('department_id', $request->department_id);
            });
        }
        $content = $content->where($params)->latest()->get();
        return $this->generateDatatable($content);
    }

    public function userDataTable($request, $user_id)
    {

        $content = $this->model->query()->with('employee:id,name,department_id,payslip_type')->where('company_id', auth()->user()->company_id);
        $params = [];
        $params['user_id'] = $user_id;
        if (@$request->status_id) {
            $params['status_id'] = $request->status_id;
        }
        if (@$request->date) {
            $content = $content->whereMonth('date', date('m', strtotime($request->date)));
        }
        if (@$request->department_id) {
            $content->whereHas('employee', function ($query) use ($request) {
                $query->where('department_id', $request->department_id);
            });
        }
        $content = $content->where($params)->latest()->get();
        return $this->generateDatatable($content);
    }

    function generateDatatable($content)
    {
        return datatables()->of($content)
            ->addColumn('action', function ($data) {
                $action_button = '';


                if (hasPermission('salary_view')) {
                    $action_button .= '<a href="' . route('hrm.payroll_salary.show', $data->id) . '" class="dropdown-item"> ' . _trans('common.View') . '</a>';
                }
                if (hasPermission('salary_update') && $data->is_calculated == 0) {
                    $action_button .= actionButton(_trans('common.Calculate'), 'mainModalOpen(`' . route('hrm.payroll_salary.calculate_modal', $data->id) . '`)', 'modal');
                }
                if (hasPermission('salary_pay') && $data->status_id != 8 && $data->is_calculated == 1) {
                    $action_button .= actionButton(_trans('common.Pay'), 'mainModalOpen(`' . route('hrm.payroll_salary.pay', $data->id) . '`)', 'modal');
                }
                if (hasPermission('salary_invoice')) {
                    $action_button .= '<a href="' . route('hrm.payroll_salary.invoice', $data->id) . '" class="dropdown-item"> ' . _trans('common.Payslip') . '</a>';
                }

                if (hasPermission('salary_delete') && $data->status_id == 9) {
                    $action_button .= actionButton(_trans('common.Delete'), '__globalDelete(' . $data->id . ',`hrm/payroll/salary/delete/`)', 'delete');
                }
                $button = '<div class="flex-nowrap">
                    <div class="dropdown">
                        <button class="btn btn-white dropdown-toggle align-text-top action-dot-btn" data-boundary="viewport" data-toggle="dropdown">
                            <i class="fas fa-ellipsis-v"></i>
                        </button>
                        <div class="dropdown-menu dropdown-menu-right">' . $action_button . '</div>
                    </div>
                </div>';
                return $button;
            })
            ->addColumn('employee', function ($data) {
                $id = $data->employee->employee_id ?? '0000';
                if (hasPermission('salary_view')) {
                    return '<a class="text-success text-decoration-none text-muted" href="' . route('hrm.payroll_salary.show', $data->id) . '" class="dropdown-item"> #' . $id . '</a>';
                } else {
                    return '<a" class="text-success' . '"> #' . $id . '</a>';
                }
            })
            ->addColumn('name', function ($data) {
                return $data->employee->name;
            })
            ->addColumn('salary', function ($data) {
                $amount = '';
                $amount .= '<span class="text-info">' . currency_format($data->gross_salary) . '</span><br>';
                return $amount;
            })
            ->addColumn('month', function ($data) {
                return '<span class="text-dark">' . date('F Y', strtotime($data->date)) . '</span>';
            })
            ->addColumn('type', function ($data) {
                return $data->employee->payslip_type ? 'Monthly' : 'Yearly';
            })
            ->addColumn('is_calculated', function ($data) {
                if (!$data->is_calculated) {
                    return '<span class="badge badge-danger">' . _trans('common.No') . '</span>';
                }
                $content = '';
                $content .= '<span class="text-success">' . _trans('payroll.Addition') . ' : ' . currency_format(number_format($data->allowance_amount, 2)) . '</span><br>';
                $content .= '<span class="text-danger">' . _trans('payroll.Deduction') . ' : ' . currency_format(number_format(($data->deduction_amount + $data->absent_amount + $data->advance_amount), 2)) . '</span><br>';
                $content .= '<span class="text-success">' . _trans('payroll.Adjust Salary') . ' : ' . currency_format(number_format($data->adjust, 2)) . '</span><br>';
                $content .= '<span class="text-info">' . _trans('payroll.Net Salary') . ' : ' . currency_format(number_format($data->net_salary, 2)) . '</span><br>';
                return $content;
            })
            ->addColumn('status', function ($data) {
                return '<small class="badge badge-' . @$data->status->class . '">' . @$data->status->name . '</small>';
            })
            ->rawColumns(array('employee', 'name', 'month', 'salary', 'type', 'status', 'is_calculated',  'action'))
            ->make(true);
    }

    public function weekends($date)
    {
        $workdays  = array();
        $weekends  = array();
        $type      = CAL_GREGORIAN;
        $month     = date('n', strtotime($date));
        $year      = date('Y', strtotime($date));
        $day_count = cal_days_in_month($type, $month, $year);

        // Get authenticated user's joining date
        $joiningDate = auth()->user()->joining_date;
        $joiningDateCarbon = Carbon::parse($joiningDate);

        // Check if the joining date is within the given month and year
        if ($joiningDateCarbon->year > $year || ($joiningDateCarbon->year == $year && $joiningDateCarbon->month > $month)) {
            // User joined after the given month
            return [
                'workdays' => [],
                'weekends' => [],
            ];
        }

        if (!Session::has('weekends')) {
            $weekendsData = DB::table('weekends')
                ->where('company_id', auth()->user()->company_id)
                ->where('is_weekend', 'yes')
                ->pluck('name')
                ->toArray();

            Session::put('weekends', array_map('strtolower', $weekendsData));
        }

        // Loop through all days of the month
        for ($i = 1; $i <= $day_count; $i++) {
            $currentDate = Carbon::createFromDate($year, $month, $i);

            // Skip dates before the joining date
            if ($currentDate->lessThan($joiningDateCarbon)) {
                continue;
            }

            $dayName = strtolower($currentDate->format('l'));
            if (!in_array($dayName, Session::get('weekends'))) {
                $workdays[] = $currentDate->toDateString();
            } else {
                $weekends[] = $currentDate->toDateString();
            }
        }

        return [
            'workdays' => $workdays,
            'weekends' => $weekends,
        ];
    }

    public function join_weekends($join_date)
    {
        $workdays  = [];
        $weekends  = [];
        $type      = CAL_GREGORIAN;
        $month     = date('n', strtotime($join_date));
        $year      = date('Y', strtotime($join_date));
        $day_count = cal_days_in_month($type, $month, $year);

        if (!Session::has('weekends')) {
            $weekendNames = DB::table('weekends')
                ->where('company_id', auth()->user()->company_id)
                ->where('is_weekend', 'yes')
                ->pluck('name')
                ->toArray();

            Session::put('weekends', array_map('strtolower', $weekendNames));
        }

        // Loop through all days of the month
        for ($i = 1; $i <= $day_count; $i++) {
            $currentDate = $year . '-' . $month . '-' . $i;
            $currentDateTimestamp = strtotime($currentDate);
            $joiningDateTimestamp = strtotime($join_date);

            // Skip dates before the joining date
            if ($currentDateTimestamp < $joiningDateTimestamp) {
                continue;
            }

            $dayName = strtolower(date('l', $currentDateTimestamp));
            if (!in_array($dayName, Session::get('weekends'))) {
                $workdays[] = date('Y-m-d', $currentDateTimestamp);
            } else {
                $weekends[] = date('Y-m-d', $currentDateTimestamp);
            }
        }

        return [
            'workdays' => $workdays,
            'weekends' => $weekends,
        ];
    }


    public  function holiday($date, $weekends)
    {
        $workdays = array();
        foreach (DB::table('holidays')->where('company_id', auth()->user()->company_id)->whereMonth('start_date', date('m', strtotime($date)))->orWhereMonth('end_date', date('m', strtotime($date)))->get() as $holiday) {
            $current_date = strtotime($holiday->start_date);
            $end_date = strtotime($holiday->end_date);
            while ($current_date <= $end_date) {
                if (date('N', $current_date)  && !in_array(date('Y-m-d', $current_date), $weekends) && date('m', strtotime($date)) == date('m', $current_date)) {
                    $workdays[] = date('Y-m-d', $current_date);
                }
                if ($current_date <= $end_date) {
                    $current_date = strtotime('+1 day', $current_date);
                }
            }
        }
        $workdays = array_unique($workdays);

        return $workdays;
    }

    public function getDay($start_date, $end_date, $date)
    {
        $start_date = strtotime($start_date);
        $end_date = strtotime($end_date);
        $workdays = array();
        while ($start_date <= $end_date) {
            if (date('N', $start_date) && date('m', strtotime($date)) == date('m', $start_date)) {
                $workdays[] = date('Y-m-d', $start_date);
            }
            if ($start_date <= $end_date) {
                $start_date = strtotime('+1 day', $start_date);
            }
        }
        return $workdays;
    }

    public function getLeave($date, $user, $per_day_salary, $total_absent)
    {
        if ($total_absent <= 0) {
            return 0;
        }
        $leave         = DB::table('leave_requests')->where('company_id', auth()->user()->company_id)->where('user_id', $user->id)->where('status_id', 1);
        $yearlyLeave   =  0;
        $monthlyLeave  = $user->extra_leave ?? 0;
        foreach ($leave->clone()->whereYear('leave_from', '<=', date('Y', strtotime($date)))->whereYear('leave_to', '>=', date('Y', strtotime($date)))->get() as $yLeave) {
            $yearlyLeave += intval($this->getDay($yLeave->leave_from, $yLeave->leave_to, $date));
        }
        foreach ($leave->clone()->whereMonth('leave_from', '<=', date('m', strtotime($date)))->whereMonth('leave_to', '>=', date('m', strtotime($date)))->get() as $mLeave) {
            $monthlyLeave += intval($this->getDay($mLeave->leave_from, $mLeave->leave_to, $date));
        }

        $minus_salary  = 0;

        $total_leaves  = ($user->Leave->count() + $user->extra_leave) - $yearlyLeave;

        if ($total_leaves > 0) {
            $minus_salary  =  ($monthlyLeave - $total_absent)  * $per_day_salary;
        } else {
            $minus_salary  = $total_absent * $per_day_salary;
        }
        return abs($minus_salary);
    }

    public function generate($request)
    {
        DB::beginTransaction();

        try {
            $date = Carbon::createFromFormat('Y-m', $request->month);
            $month = $date->month;
            $year  = $date->year;

            $users = User::select('id', 'company_id', 'branch_id', 'basic_salary')
                ->where('company_id', userCompanies())
                ->where('branch_id', userBranch())
                ->where('status_id', 1)
                ->where('basic_salary', '>', 0)
                ->when($request->department_id, fn($query) => $query->where('department_id', $request->department_id))
                ->when($request->employee_id, fn($query) => $query->where('id', $request->employee_id))
                ->get();

            $exists = $this->model
                ->where('company_id', auth()->user()->company_id)
                ->whereMonth('date', $month)
                ->whereYear('date', $year)
                ->whereIn('user_id', $users->pluck('id')->toArray())
                ->exists();

            if ($exists) {
                return $this->responseWithError(_trans('message.Salary already generated'), [], 400);
            }

            foreach ($users as $user) {
                $dutyCalendarSummary = $this->dutyCalendarSummary($user, $month, $year);

                if (!$dutyCalendarSummary['office_working_days']) {
                    continue;
                }

                $salaryCalculation = $this->salaryCalculation($user, $month, $year, $dutyCalendarSummary['per_day_salary'], $dutyCalendarSummary['absent_amount']);

                $salary = new SalaryGenerate();

                $salary->user_id           = $user->id;
                $salary->date              = $date;
                $salary->amount            = $salaryCalculation['payable_amount'];
                $salary->due_amount        = $salaryCalculation['payable_amount'];
                $salary->gross_salary      = $user->basic_salary;
                $salary->status_id         = 9; // Unpaid
                $salary->total_working_day = $dutyCalendarSummary['office_working_days'];
                $salary->present           = $dutyCalendarSummary['total_present'];
                $salary->absent            = $dutyCalendarSummary['total_absent'];
                $salary->late              = $dutyCalendarSummary['total_late'];
                $salary->left_early        = $dutyCalendarSummary['total_left_early'];
                $salary->is_calculated     = 1;
                $salary->absent_amount     = $dutyCalendarSummary['absent_amount'];
                $salary->advance_amount    = $salaryCalculation['advance_amount'];
                $salary->advance_details   = $salaryCalculation['advance_details'];
                $salary->allowance_amount  = $salaryCalculation['addition_amount'];
                $salary->allowance_details = $salaryCalculation['addition_details'];
                $salary->deduction_amount  = $salaryCalculation['deduction_amount'];
                $salary->deduction_details = $salaryCalculation['deduction_details'];
                $salary->net_salary   = $salaryCalculation['payable_amount'];
                $salary->created_by   = auth()->id();
                $salary->updated_by   = auth()->id();
                $salary->save();
            }

            DB::commit();

            return $this->responseWithSuccess(_trans('message.Salary generated successfully.'), []);
        } catch (\Throwable $th) {
            DB::rollBack();
            throw $th;
        }
    }

    function findById($id)
    {
        return $this->model->findOrFail($id);
    }

    public function adjust($request, $id)
    {
        $salary = $this->findById($id);

        if ($salary->adjust_type == 1) {
            $payableAmount = $salary->amount + $request->amount;

            $salary->due_amount = $payableAmount;
            $salary->net_salary = $payableAmount;
            $salary->deduct     = 0;
            $salary->adjust     = $request->amount;
        } else {
            $payableAmount = $salary->amount - $request->amount;

            $salary->due_amount = $payableAmount;
            $salary->net_salary = $payableAmount;
            $salary->deduct     = $request->amount;
            $salary->adjust     = 0;
        }

        $salary->adjust_type = $request->adjust_type;
        $salary->note        = $request->note;
        $salary->save();

        return $salary;
    }

    public function salaryCalculation($user, $month, $year, $perDaySalary, $absentAmount)
    {
        $installmentAmount = 0;
        $additionAmount    = 0;
        $deductionAmount   = 0;
        $advanceDetails    = [];
        $additionDetails   = [];
        $deductionDetails  = [];
        $basicSalary       = $user->basic_salary;

        //advance salary
        $advanceSalary = AdvanceSalary::with('payment', 'advance_type')
            ->where('status_id', 5)
            ->where('company_id', userCompanies())
            ->where('company_id', userBranch())
            ->where('user_id', $user->id)
            ->where('due_amount', '>', 0);

        $installmentAdvance = $advanceSalary->clone()->where('recovery_mode', 1)->first();

        $onetimeAmount = $advanceSalary->clone()
            ->whereMonth('recover_from', $month)
            ->whereYear('recover_from', $year)
            ->where('recovery_mode', 2)
            ->sum('amount');


        if ($installmentAdvance) {
            $installmentAmount = $installmentAdvance->installment_amount > $installmentAdvance->due_amount ? $installmentAdvance->due_amount : $installmentAdvance->installment_amount;

            $advanceDetails[] = [
                'amount'        => $installmentAmount,
                'recovery_mode' => 'Installment',
                'type'          => @$installmentAdvance->advance_type->name,
                'is_applicable' => 1
            ];

            $installmentAdvance->due_amount -= $installmentAmount;
            $installmentAdvance->paid_amount += $installmentAmount;
            $installmentAdvance->updated_by = auth()->id();

            if ($installmentAdvance->due_amount <= 0) {
                $installmentAdvance->pay = 8;
                $installmentAdvance->return_status = 23;
            } else {
                $installmentAdvance->return_status = 21;
            }

            $installmentAdvance->save();

            if ($installmentAdvance) {
                $advanceSalaryLog = new AdvanceSalaryLog();

                $advanceSalaryLog->advance_salary_id = $installmentAdvance->id;
                $advanceSalaryLog->is_pay            = 1;
                $advanceSalaryLog->amount            = $installmentAmount;
                $advanceSalaryLog->due_amount        = $installmentAdvance->due_amount;
                $advanceSalaryLog->user_id           = $installmentAdvance->user_id;
                $advanceSalaryLog->payment_note      = @$installmentAdvance->description ?? 'Payment';
                $advanceSalaryLog->created_by        = auth()->id();
                $advanceSalaryLog->updated_by        = auth()->id();
                $advanceSalaryLog->save();
            }
        }

        $advanceSalaries = $advanceSalary
            ->whereMonth('recover_from', $month)
            ->whereYear('recover_from', $year)
            ->where('recovery_mode', 2)
            ->get();

        foreach ($advanceSalaries ?? [] as $advance) {
            $advanceDetails[] = [
                'type'        => 2,
                'amount_type' => 1,
                'amount'        => $advance->amount,
                'recovery_mode' => 'One time',
                'type'          => @$advance->advance_type->name,
                'is_applicable' => 1
            ];

            $advance->due_amount -= $installmentAmount;
            $advance->paid_amount += $installmentAmount;
            $advance->updated_by = auth()->id();
            $advance->pay = 8;
            $advance->return_status = 23;

            $advance->save();

            if ($advance) {
                $advanceLog = new AdvanceSalaryLog();

                $advanceLog->advance_salary_id = $advance->id;
                $advanceLog->is_pay            = 1;
                $advanceLog->amount            = $advance->amount;
                $advanceLog->due_amount        = 0;
                $advanceLog->user_id           = $advance->user_id;
                $advanceLog->payment_note      = @$advance->description ?? 'Payment';
                $advanceLog->created_by        = auth()->id();
                $advanceLog->updated_by        = auth()->id();
                $advanceLog->save();
            }
        }

        // commission calculation
        $commissionDetails = SalarySetupDetails::with('commission:id,type,name')
            ->where('status_id', 1)
            ->where('user_id', $user->id)
            ->get();

        foreach ($commissionDetails as $commissionDetail) {
            if ($commissionDetail->commission->type == 1) {
                $additionAmount += $commissionDetail->amount_type == 1 ? $commissionDetail->amount : (($commissionDetail->amount / 100) * $basicSalary);

                $additionDetails[] = [
                    'type'        => @$commissionDetail->commission->type,
                    'amount_type' => @$commissionDetail->amount_type,
                    'amount'      => @$commissionDetail->amount_type == 1 ? $commissionDetail->amount : (($commissionDetail->amount / 100) * $basicSalary),
                    'old_amount'  => @$commissionDetail->amount,
                    'name'        => @$commissionDetail->commission->name,
                ];
            } else {
                $deductionAmount += $commissionDetail->amount_type == 1 ? $commissionDetail->amount : (($commissionDetail->amount / 100) * $basicSalary);

                $deductionDetails[] = [
                    'type'        => @$commissionDetail->commission->type,
                    'amount_type' => @$commissionDetail->amount_type,
                    'old_amount'  => @$commissionDetail->amount,
                    'amount'      => @$commissionDetail->amount_type == 1 ? $commissionDetail->amount : (($commissionDetail->amount / 100) * $basicSalary),
                    'name'        => @$commissionDetail->commission->name,
                ];
            }
        }

        // Calculate absent amount
        if ($absentAmount > 0) {
            $deductionAmount += $absentAmount;

            $deductionDetails[] = [
                'type'        => 2,
                'amount_type' => 1,
                'old_amount'  => round($absentAmount, 2),
                'amount'      => round($absentAmount, 2),
                'name'        => _trans('common.Absent Amount'),
            ];
        }

        // Calculate tardy deduction
        if (isModuleActive('Tardy')) {
            $assignedRules = $user->tardyRules()->with('tardyRule')->get();

            foreach ($assignedRules as $assign) {
                $rule = $assign->tardyRule;

                // Count tardy occurrences for this rule
                $violationCount = $user->tardyRecords()
                    ->where('tardy_rule_id', $rule->id)
                    ->whereMonth('date', $month)
                    ->whereYear('date', $year)
                    ->where('is_appeal_approved', 0)
                    ->count();

                if ($violationCount >= $rule->threshold_days) {
                    // Calculate how many full cycles of violation occurred
                    $cycles = floor($violationCount / $rule->threshold_days);
                    $deductionDays = $cycles * $rule->deduction_days;
                    $deductionAmount += $deductionDays * $perDaySalary;

                    $deductionDetails[] = [
                        'type'        => 2,
                        'amount_type' => 1,
                        'old_amount'  => round($deductionDays * $perDaySalary, 2),
                        'amount'      => round($deductionDays * $perDaySalary, 2),
                        'name'        => $rule->title,
                    ];
                }
            }
        }

        $totalAdvance = $installmentAmount + $onetimeAmount;

        return [
            'payable_amount' => ($basicSalary + $additionAmount) - ($deductionAmount + $totalAdvance),
            'advance_amount' => $totalAdvance,
            'advance_details' => $advanceDetails,

            'addition_amount' => $additionAmount,
            'addition_details' => $additionDetails,

            'deduction_amount' => $deductionAmount,
            'deduction_details' => $deductionDetails
        ];
    }

    public function dutyCalendarSummary($user, $month, $year)
    {
        $today = now()->toDateString();

        $summary = DutyCalendar::query()
            ->leftJoin('attendances', 'attendances.duty_calendar_id', '=', 'duty_calendars.id')
            ->where('duty_calendars.employee_id', $user->id)
            ->whereMonth('duty_calendars.date', $month)
            ->whereYear('duty_calendars.date', $year)
            ->selectRaw("
            COUNT(DISTINCT CASE WHEN duty_calendars.duty_schedule_id IS NOT NULL THEN duty_calendars.id END) AS working_day,
            COUNT(DISTINCT CASE WHEN duty_calendars.holiday_id IS NOT NULL THEN duty_calendars.id END) AS total_holiday,
            COUNT(DISTINCT CASE WHEN duty_calendars.leave_id IS NOT NULL THEN duty_calendars.id END) AS total_leave,
            COUNT(DISTINCT CASE WHEN duty_calendars.is_weekend = 1 THEN duty_calendars.id END) AS total_weekend,
            COUNT(DISTINCT CASE WHEN duty_calendars.duty_schedule_id IS NOT NULL AND duty_calendars.date <= '{$today}'
                        AND attendances.id IS NULL THEN duty_calendars.id END) AS total_absent,
            COUNT(DISTINCT CASE WHEN attendances.in_status = 'OT' THEN attendances.id END) AS total_on_time_in,
            COUNT(DISTINCT CASE WHEN attendances.in_status = 'L' THEN attendances.id END) AS total_late,
            COUNT(DISTINCT CASE WHEN attendances.out_status = 'LE' THEN attendances.id END) AS total_left_early,
            COUNT(DISTINCT CASE WHEN attendances.out_status = 'LT' THEN attendances.id END) AS total_left_timely,
            COUNT(DISTINCT CASE WHEN attendances.out_status = 'LL' THEN attendances.id END) AS total_left_later
        ")
            ->first()
            ->toArray();

        // Calculate totals
        $summary['office_working_days'] = $summary['working_day'] + $summary['total_leave'];
        $summary['total_present'] = $summary['office_working_days'] - $summary['total_absent'];

        // Salary calculation (rounded to 2 decimal places)
        $perDaySalary = $summary['office_working_days'] > 0 ? ($user->basic_salary / $summary['office_working_days']) : 0;
        $summary['absent_amount'] = round($perDaySalary * $summary['total_absent'], 2);
        $summary['per_day_salary'] = round($perDaySalary, 2);

        return $summary;
    }

    function pay($request, $salary)
    {
        DB::beginTransaction();
        try {
            if ($salary->status_id == 8) {
                return $this->responseWithError(_trans('message.Salary already Paid'));
            }

            $transaction = new Transaction;

            $transaction->account_id       = $request->account;
            $transaction->company_id       = $salary->company_id;
            $transaction->date             = date('Y-m-d');
            $transaction->description      = @$request->description ?? 'salary Payment';
            $transaction->amount           = $request->amount;
            $transaction->transaction_type = 18;
            $transaction->status_id        = 8;
            $transaction->created_by       = auth()->id();
            $transaction->updated_by       = auth()->id();
            $transaction->save();

            $expense = new Expense();

            $expense->user_id                    = $salary->user_id;
            $expense->income_expense_category_id = $request->category;
            $expense->company_id                 = $salary->company_id;
            $expense->date                       = date('Y-m-d');
            $expense->amount                     = $request->amount;
            $expense->request_amount             = $request->amount;
            $expense->ref                        = auth()->user()->name;
            $expense->remarks                    = $request->description ?? 'salary Payment';
            $expense->created_by                 = auth()->id();
            $expense->updated_by                 = auth()->id();
            $expense->approver_id                = auth()->user()->id;
            $expense->transaction_id             = $transaction->id;
            $expense->payment_method_id          = $request->payment_method;
            $expense->pay                        = 8;
            $expense->status_id                  = 5;
            $expense->save();

            $salaryPaymentLog = new SalaryPaymentLog();

            $salaryPaymentLog->salary_generate_id = $salary->id;
            $salaryPaymentLog->amount             = $request->amount;
            $salaryPaymentLog->user_id            = $salary->user_id;
            $salaryPaymentLog->due_amount         = $salary->amount - $request->amount;
            $salaryPaymentLog->paid_by            = $salary->user_id;
            $salaryPaymentLog->transaction_id     = $transaction->id;
            $salaryPaymentLog->payment_method_id  = $request->payment_method;
            $salaryPaymentLog->payment_note       = $request->description ?? 'salary Payment';
            $salaryPaymentLog->created_by         = auth()->id();
            $salaryPaymentLog->updated_by         = auth()->id();
            $salaryPaymentLog->company_id         = $salary->company_id;
            $salaryPaymentLog->save();

            $account = Account::findOrFail($request->account);
            $account->amount = $account->amount - $transaction->amount;
            $account->save();

            $salary->due_amount = $salary->due_amount - $request->amount;
            $salary->updated_by = auth()->id();
            if ($salary->due_amount <= 0) {
                $salary->status_id = 8;
            } else {
                $salary->status_id = 20;
            }
            $salary->save();


            DB::commit();
            return $this->responseWithSuccess(_trans('message.Salary pay successfully.'), $salary);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseExceptionError($th->getMessage(), [], 400);
        }
    }

    function delete($id, $company_id)
    {
        $salary = $this->model()->where('id', $id)->where('company_id', $company_id)->first();
        if (!$salary) {
            return $this->responseWithError(_trans('message.Data not found'), 'id', 404);
        }

        try {
            if ($salary->status_id == 9) {
                $salary->delete();
                return $this->responseWithSuccess(_trans('message.Payslip Delete successfully.'), $salary);
            } else {
                return $this->responseWithError(_trans('message.You cannot delete'), 'id', 404);
            }
        } catch (\Throwable $th) {
            return $this->responseExceptionError($th->getMessage(), [], 400);
        }
    }

    // new functions for
    public function table($request)
    {
        $data = $this->model->query()->with('employee:id,name,department_id,payslip_type')->where('company_id', auth()->user()->company_id);

        $params = [];

        if (!isAdminOrHr()) {
            $params['user_id'] = auth()->user()->id;
        }
        if ($request->has('user_id')) {
            $params['user_id'] = $request->user_id;
        }
        if (@$request->status) {
            $params['status_id'] = $request->status;
        }
        if ($request->from && $request->to) {
            $data = $data->whereBetween('created_at', start_end_datetime($request->from, $request->to));
        }
        if ($request->search) {
            $data->whereHas('employee', function ($query) use ($request) {
                $query->where('name', 'like', '%' . $request->search . '%');
            });
        }
        if (@$request->department) {
            $data->whereHas('employee', function ($query) use ($request) {
                $query->where('department_id', $request->department);
            });
        }

        $data = $data->where($params)->latest()->paginate($request->limit ?? 2);

        return [
            'data' => $data->map(function ($data) {
                $action_button = '';
                if (hasPermission('salary_read')) {
                    $action_button .= '<a href="' . route('hrm.payroll_salary.show', $data->id) . '" class="dropdown-item"> ' . _trans('common.View') . '</a>';
                }
                if (hasPermission('salary_update') && $data->status_id != 8) {
                    $action_button .= actionButton(_trans('common.Adjust'), 'mainModalOpen(`' . route('hrm.payroll_salary.adjust_modal', $data->id) . '`)', 'modal');
                }
                if (hasPermission('salary_pay') && $data->status_id != 8 && $data->is_calculated == 1) {
                    $action_button .= actionButton(_trans('common.Pay'), 'mainModalOpen(`' . route('hrm.payroll_salary.pay', $data->id) . '`)', 'modal');
                }
                if (hasPermission('salary_payslip')) {
                    $action_button .= '<a href="' . route('hrm.payroll_salary.invoice', $data->id) . '" class="dropdown-item"> ' . _trans('common.Payslip') . '</a>';
                }

                if (hasPermission('salary_delete') && $data->status_id == 9) {
                    $action_button .= actionButton(_trans('common.Delete'), '__globalDelete(' . $data->id . ',`hrm/payroll/salary/delete/`)', 'delete');
                }
                $button = ' <div class="dropdown dropdown-action">
                        <button type="button" class="btn-dropdown" data-bs-toggle="dropdown"
                            aria-expanded="false">
                            <i class="fa-solid fa-ellipsis"></i>
                        </button>
                        <ul class="dropdown-menu dropdown-menu-end">
                        ' . $action_button . '
                        </ul>
                </div>';

                $is_calculated = '';
                if ($data->is_calculated) {
                    $is_calculated .= '<span class="text-danger">' . _trans('payroll.Deduction') . ' : ' . currency_format(number_format(($data->deduction_amount  + $data->advance_amount), 2)) . '</span><br>';

                    if ($data->adjust > 0 || $data->deduct > 0) {
                        $adjustClass = $data->adjust_type == 1 ? 'success' : 'danger';
                        $adjustSign = $data->adjust_type == 1 ? '+' : '-';
                        $amount     = $data->adjust_type == 1 ? $data->adjust : $data->deduct;

                        $is_calculated .= '<span class="text-' . $adjustClass . '">' . _trans('payroll.Adjust') . ' : ' . $adjustSign . currency_format(number_format(($amount), 2)) . '</span><br>';
                    }

                    $is_calculated .= '<span class="text-success">' . _trans('payroll.Addition') . ' : ' . currency_format(number_format($data->allowance_amount, 2)) . '</span><br>';

                    $is_calculated .= '<span class="text-info">' . _trans('payroll.Net Salary') . ' : ' . currency_format(number_format($data->net_salary, 2)) . '</span><br>';
                } else {
                    $is_calculated = '<span class="badge badge-danger">' . _trans('common.No') . '</span>';
                }

                return [
                    'id'             => $data->id,
                    'employee'       => $data->employee->name,
                    'salary'         => showAmount($data->gross_salary),
                    'month'          =>  date('F Y', strtotime($data->date)),
                    'type'           => $data->employee->payslip_type ? 'Monthly' : 'Yearly',
                    'is_calculated'  => $is_calculated,
                    'status'     => '<span class="badge badge-' . @$data->status->class . '">' . @$data->status->name . '</span>',
                    'action'     => $button
                ];
            }),
            'pagination' => [
                'total' => $data->total(),
                'count' => $data->count(),
                'per_page' => $data->perPage(),
                'current_page' => $data->currentPage(),
                'total_pages' => $data->lastPage(),
                'pagination_html' =>  $data->links('backend.pagination.custom')->toHtml(),
            ],
        ];
    }

    public function salaryReport($isPaginate = true)
    {
        $data['departments']        = Department::where('status_id', 1)->pluck('title', 'id');
        $data['employees']          = User::query()
            ->where('company_id', userCompanies())
            ->where('branch_id', userBranch())
            ->where('status_id', 1)
            ->get(['id', 'name', 'employee_id'])
            ->map(function ($item) {
                $item->employee = $item->name . ' [' . $item->employee_id . ']';
                return $item;
            })
            ->pluck('employee', 'id');

        $commissions                = $this->commissions();
        $data['additions']          = $commissions['additions'];
        $data['deductions']         = $commissions['deductions'];

        $data['salaryGenerates']    = $this->salaryGenerates(true);

        return $data;
    }

    public function salaryGenerates($isPaginate = true)
    {
        $requestedMonth = Carbon::parse(request('month'))->format('m');
        $requestedYear  = Carbon::parse(request('month'))->format('Y');

        $salaryGenerates = SalaryGenerate::query()
            ->when(request('user_id'), function ($q) {
                $q->where('user_id', request('user_id'));
            })
            ->when(request('department_id'), function ($q) {
                $q->whereHas('employee', fn($q) => $q->where('department_id', request('department_id')));
            })
            ->when(request('month'), function ($q) use ($requestedMonth, $requestedYear) {
                $q->whereMonth('date', $requestedMonth)
                    ->whereYear('date', $requestedYear);
            })
            ->where('is_calculated', 1)
            ->when(request('commission_type'), function ($q) {
                if (request('commission_type') == 'addition') {
                    $q->where('allowance_amount', '>', 0);
                } else {
                    $q->where(function ($q) {
                        $q->where('advance_amount', '>', 0)
                            ->orWhere('absent_amount', '>', 0)
                            ->orWhere('deduction_amount', '>', 0);
                    });
                }
            });

        if (!isAdminOrHr()) {
            $salaryGenerates = $salaryGenerates->where('user_id', auth()->id());
        }

        return $isPaginate ? $salaryGenerates->paginate(20) : $salaryGenerates->get();
    }

    public function commissions()
    {
        $data['additions']  = Commission::addition()->where('status_id', 1)->pluck('name')->toArray();
        $data['deductions'] = Commission::deduction()->where('status_id', 1)->pluck('name')->toArray();

        return $data;
    }
}
