<?php

namespace  App\Repositories\Hrm\Payroll;

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 Illuminate\Support\Facades\Log;
use App\Models\Payroll\AdvanceSalary;
use App\Models\Hrm\Leave\LeaveRequest;
use App\Models\Payroll\SalaryGenerate;
use Illuminate\Support\Facades\Request;
use Illuminate\Support\Facades\Session;
use App\Models\Payroll\AdvanceSalaryLog;
use App\Models\Payroll\SalaryPaymentLog;
use App\Models\Hrm\Department\Department;
use Illuminate\Support\Facades\Validator;
use App\Models\Payroll\SalarySetupDetails;
use App\Models\Hrm\Designation\Designation;
use App\Helpers\CoreApp\Traits\ApiReturnFormatTrait;
use App\Services\Payroll\LegalDeductionsService;
use Carbon\Carbon;

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.Calculation'),
            _trans('payroll.Status'),
            _trans('payroll.Action'),
        ];
    }

    public function dataTable($request)
    {

        $content = $this->model->query()->with('employee:id,name,department_id,payslip_type')->where('company_id', auth()->user()->company_id);
        $params = [];
        if (auth()->user()->role->slug == 'staff') {
            $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);
    }

    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->year) {
            //     $content = $content->whereYear('date', $request->year);
            // }else{
            //     $content = $content->whereYear('date', date('Y'));
            // }


            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_calculate') && $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) {
                // Mapear correctamente el tipo de recibo según payslip_type
                // 1 = mensual, 2 = semanal, 3 = diario, 4 = quincenal
                $type = (int) ($data->employee->payslip_type ?? 0);
                switch ($type) {
                    case 1:
                        return _trans('payroll.Per Month');
                    case 2:
                        return _trans('payroll.Per Week');
                    case 3:
                        return _trans('payroll.Per Day');
                    case 4:
                        return _trans('payroll.Fortnight');
                    default:
                        return 'N/A';
                }
            })
            ->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, $joiningDate = null)
    {
        $workdays  = [];
        $weekends  = [];
        $type      = CAL_GREGORIAN;
        $month     = date('n', strtotime($date));
        $year      = date('Y', strtotime($date));
        $day_count = cal_days_in_month($type, $month, $year);

        // Fecha de ingreso (del empleado analizado)
        $joiningDateCarbon = $joiningDate ? Carbon::parse($joiningDate) : null;

        // Si el usuario ingresó después del mes consultado no hay días laborables
    if ($joiningDateCarbon && ($joiningDateCarbon->year > $year || ($joiningDateCarbon->year == $year && $joiningDateCarbon->month > $month))) {
            return [
                'workdays' => [],
                'weekends' => [],
            ];
        }

        // Obtener fines de semana vigentes para la compañía (sin cachear en sesión)
        $weekendsNumeric = $this->mapWeekendValuesToNumbers(
            DB::table('weekends')
                ->where('company_id', auth()->user()->company_id)
                ->where('is_weekend', 'yes')
                ->pluck('name')
                ->toArray()
        );

        for ($i = 1; $i <= $day_count; $i++) {
            $currentDate = Carbon::createFromDate($year, $month, $i);

            // Omitir fechas previas al ingreso
            if ($joiningDateCarbon && $currentDate->lessThan($joiningDateCarbon)) {
                continue;
            }

            $dayOfWeek = $currentDate->dayOfWeek; // 0..6
            if (!in_array($dayOfWeek, $weekendsNumeric, true)) {
                $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);

        $weekendsNumeric = $this->mapWeekendValuesToNumbers(
            DB::table('weekends')
                ->where('company_id', auth()->user()->company_id)
                ->where('is_weekend', 'yes')
                ->pluck('name')
                ->toArray()
        );

        for ($i = 1; $i <= $day_count; $i++) {
            $currentDate = $year . '-' . $month . '-' . $i;
            $currentDateTimestamp = strtotime($currentDate);
            $joiningDateTimestamp = strtotime($join_date);

            // Omitir fechas previas al ingreso
            if ($currentDateTimestamp < $joiningDateTimestamp) {
                continue;
            }

            $dayOfWeek = (int) date('w', $currentDateTimestamp); // 0..6
            if (!in_array($dayOfWeek, $weekendsNumeric, true)) {
                $workdays[] = date('Y-m-d', $currentDateTimestamp);
            } else {
                $weekends[] = date('Y-m-d', $currentDateTimestamp);
            }
        }

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

    // Map names in es/en or numeric strings to integers 0..6
    private function mapWeekendValuesToNumbers(array $values): array
    {
        $map = [
            // English
            'sunday' => 0, 'monday' => 1, 'tuesday' => 2, 'wednesday' => 3, 'thursday' => 4, 'friday' => 5, 'saturday' => 6,
            'sun' => 0, 'mon' => 1, 'tue' => 2, 'wed' => 3, 'thu' => 4, 'thur' => 4, 'fri' => 5, 'sat' => 6,
            // Spanish
            'domingo' => 0, 'lunes' => 1, 'martes' => 2, 'miércoles' => 3, 'miercoles' => 3, 'jueves' => 4, 'viernes' => 5, 'sábado' => 6, 'sabado' => 6,
            'dom' => 0, 'lun' => 1, 'mar' => 2, 'mié' => 3, 'mie' => 3, 'jue' => 4, 'vie' => 5, 'sáb' => 6, 'sab' => 6,
        ];

        $out = [];
        foreach ($values as $v) {
            if ($v === null) continue;
            if (is_int($v) || (is_string($v) && is_numeric($v))) {
                $n = (int) $v;
                if ($n >= 0 && $n <= 6) {
                    $out[] = $n;
                }
                continue;
            }
            $key = strtolower(trim((string) $v));
            if (array_key_exists($key, $map)) {
                $out[] = $map[$key];
            }
        }

        return array_values(array_unique($out));
    }

    // Determina el rango de quincena a partir de la fecha del recibo
    private function getFortnightRange(string $date): array
    {
        $d = Carbon::parse($date);
        $start = $d->copy()->startOfMonth();
        $endOfMonth = $d->copy()->endOfMonth();
        if ((int)$d->day <= 15) {
            return ['start' => $start->toDateString(), 'end' => $d->copy()->day(15)->toDateString()];
        }
        return ['start' => $d->copy()->day(16)->toDateString(), 'end' => $endOfMonth->toDateString()];
    }

    // Calcula días laborables y fines de semana dentro de un rango
    private function workdaysAndWeekendsInRange(string $start, string $end, ?string $joiningDate = null): array
    {
        $weekendsNumeric = $this->mapWeekendValuesToNumbers(
            DB::table('weekends')
                ->where('company_id', auth()->user()->company_id)
                ->where('is_weekend', 'yes')
                ->pluck('name')
                ->toArray()
        );

        $workdays = [];
        $weekends = [];
        $current = Carbon::parse($start);
        $endC = Carbon::parse($end);
        $joinC = $joiningDate ? Carbon::parse($joiningDate) : null;

        while ($current->lte($endC)) {
            if ($joinC && $current->lt($joinC)) {
                $current->addDay();
                continue;
            }
            $dow = $current->dayOfWeek; // 0..6
            $dateStr = $current->toDateString();
            if (!in_array($dow, $weekendsNumeric, true)) {
                $workdays[] = $dateStr;
            } else {
                $weekends[] = $dateStr;
            }
            $current->addDay();
        }
        return ['workdays' => $workdays, 'weekends' => $weekends];
    }

    // Feriados dentro del rango, excluyendo fines de semana ya detectados
    private function holidaysInRange(string $start, string $end, array $weekendDates): array
    {
        $holidayDates = [];
        $holidays = DB::table('holidays')
            ->where('company_id', auth()->user()->company_id)
            ->where(function ($q) use ($start, $end) {
                $q->whereBetween('start_date', [$start, $end])
                  ->orWhereBetween('end_date', [$start, $end])
                  ->orWhere(function ($q) use ($start, $end) {
                      $q->where('start_date', '<', $start)->where('end_date', '>', $end);
                  });
            })
            ->get();

        foreach ($holidays as $h) {
            $cur = Carbon::parse($h->start_date);
            $endC = Carbon::parse($h->end_date);
            while ($cur->lte($endC)) {
                $ds = $cur->toDateString();
                if ($ds >= $start && $ds <= $end && !in_array($ds, $weekendDates, true)) {
                    $holidayDates[] = $ds;
                }
                $cur->addDay();
            }
        }
        return array_values(array_unique($holidayDates));
    }


    public  function holiday($date, $weekends)
    {
        // Devuelve las fechas (YYYY-mm-dd) que son feriados y caen dentro del mes consultado, excluyendo fines de semana.
        $holidayDates = [];
        $month = date('m', strtotime($date));
        $year  = date('Y', strtotime($date));

        $holidays = DB::table('holidays')
            ->where('company_id', auth()->user()->company_id)
            ->where(function ($q) use ($month) {
                $q->whereMonth('start_date', $month)
                  ->orWhereMonth('end_date', $month);
            })
            ->get();

        foreach ($holidays as $holiday) {
            $current_date = strtotime($holiday->start_date);
            $end_date     = strtotime($holiday->end_date);
            while ($current_date <= $end_date) {
                // Dentro del mismo mes consultado y no es fin de semana
                if (date('m', $current_date) == $month && !in_array(date('Y-m-d', $current_date), $weekends)) {
                    $holidayDates[] = date('Y-m-d', $current_date);
                }
                $current_date = strtotime('+1 day', $current_date);
            }
        }
        return array_values(array_unique($holidayDates));
    }

    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)
    {
        // En info() ya descontamos las licencias aprobadas del total de días laborables,
        // por lo que aquí sólo corresponde cobrar las ausencias reales.
        if ($total_absent <= 0) return 0;
        return $total_absent * $per_day_salary;
    }


    public function generate($request)
    {
        $validator = Validator::make(\request()->all(), [
            'month' => 'required',
            'department' => 'required',
        ]);

        if ($validator->fails()) {
            return $this->responseWithError(__('Required field missing'), $validator->errors(), 400);
        }
        DB::beginTransaction();
        try {
            $companyId = auth()->user()->company_id;
            $query = User::where('company_id', $companyId)
                ->where('status_id', 1); // ★ Solo usuarios ACTIVOS

            if (@$request->department) {
                $query->where('department_id', $request->department);
            }
            $users = $query->pluck('id');

            // Evitar falsos positivos: validar duplicado por quincena, no por todo el mes
            $selectedDate = Carbon::parse($request->month)->toDateString();
            $fortnight = $this->getFortnightRange($selectedDate); // ['start' => Y-m-d, 'end' => Y-m-d]

            $existing = $this->model
                ->where('company_id', $companyId)
                ->whereBetween('date', [$fortnight['start'], $fortnight['end']])
                ->whereIn('user_id', $users)
                ->get();

            if (!blank($existing)) {
                // Ya existe nómina para esta misma quincena
                return $this->responseWithError(_trans('message.Salary already generated'), [], 400);
            }

            $legalDeductionsService = app(LegalDeductionsService::class);
            $lastSalary = null;

            foreach ($users as $key => $value) {
                $user = User::with('Leave')->where('id', $value)->first();
                if (blank($user) || $user->basic_salary <= 0) {
                    continue;
                }

                // ==============================
                // ASISTENCIA Y DÍAS LABORABLES
                // ==============================
                $cal = $this->workdaysAndWeekendsInRange(
                    $fortnight['start'], $fortnight['end'], $user->joining_date
                );
                $holidays = $this->holidaysInRange(
                    $fortnight['start'], $fortnight['end'], $cal['weekends']
                );

                $totalLeave = $this->approveLeaveOfMonth(
                    $user->id, date('Y-m', strtotime($selectedDate))
                );
                $totalWorkingDays = max(0, count($cal['workdays']) - count($holidays) - $totalLeave);

                $rawAtt = DB::table('attendances')
                    ->where('company_id', $companyId)
                    ->where('user_id', $user->id)
                    ->whereBetween('date', [$fortnight['start'], $fortnight['end']]);

                // ★ IMPORTANTE: ->groupBy()->count() en Query Builder solo retorna 1 fila.
                // Hay que usar ->get()->count() para contar los grupos correctamente.
                $totalPresent = $rawAtt->clone()->orderBy('id', 'asc')->groupBy('date')->get()->count();
                $totalAbsent  = max(0, $totalWorkingDays - $totalPresent);
                $totalLate    = $rawAtt->clone()->where('in_status', 'L')->orderBy('id', 'asc')->groupBy('date')->get()->count();
                $totalEarly   = $rawAtt->clone()->where('out_status', 'LE')->orderBy('id', 'desc')->get()->unique('date')->count();

                // ==============================
                // DESCUENTOS DE LEY (ISSS, AFP, RENTA)
                // ==============================
                $legal = $legalDeductionsService->calculate($user->basic_salary);
                $fortnightBasePay = $legal['fortnight_pay'];

                // Salario por día ajustado (basado en pago quincenal neto de ley)
                $perDayAdj = $totalWorkingDays > 0 ? ($fortnightBasePay / $totalWorkingDays) : 0;
                $leaveCuts = $totalAbsent > 0 ? ($totalAbsent * $perDayAdj) : 0;

                // ==============================
                // COMISIONES (adiciones y deducciones)
                // Filtra por vigencia de fechas y quincena aplicable
                // ==============================
                $fortnightNumber = (int)date('d', strtotime($fortnight['start'])) <= 15 ? 1 : 2;

                $commission = SalarySetupDetails::with('commission:id,type,name')
                    ->where('status_id', 1)
                    ->where('company_id', $companyId)
                    ->where('user_id', $user->id)
                    // Filtrar por vigencia de fechas
                    ->where(function($q) use ($fortnight) {
                        $q->whereNull('date_from')
                          ->orWhere('date_from', '<=', $fortnight['end']);
                    })
                    ->where(function($q) use ($fortnight) {
                        $q->whereNull('date_to')
                          ->orWhere('date_to', '>=', $fortnight['start']);
                    })
                    // Filtrar por quincena aplicable (3=Ambas, o coincide con la quincena)
                    ->where(function($q) use ($fortnightNumber) {
                        $q->where('fortnight_apply', 3)
                          ->orWhere('fortnight_apply', $fortnightNumber);
                    })
                    ->get();

                $addition = 0; $deduction = 0;
                $additionDetail = []; $deductionDetail = [];
                foreach ($commission as $c) {
                    if (!$c->commission) continue; // Skip orphaned records
                    $cAmt = $c->amount_type == 1 ? $c->amount : (($c->amount / 100) * $user->basic_salary);

                    // El monto se aplica completo en cada quincena donde corresponda
                    $fortAmt = $cAmt;

                    $detail = [
                        'type' => $c->commission->type,
                        'amount_type' => $c->amount_type,
                        'amount' => $cAmt,
                        'fortnight_amount' => $fortAmt,
                        'old_amount' => $c->amount,
                        'name' => $c->commission->name,
                        'fortnight_apply' => (int)($c->fortnight_apply ?? 3),
                        'payroll_column' => $c->commission->payroll_column ?? ($c->commission->type == 1 ? 'comision' : 'otros_desc'),
                    ];
                    if ($c->commission->type == 1) {
                        $addition += $fortAmt;
                        $additionDetail[] = $detail;
                    } else {
                        $deduction += $fortAmt;
                        $deductionDetail[] = $detail;
                    }
                }
                $additionFort = $addition;
                $deductionFort = $deduction;

                // ==============================
                // ANTICIPOS
                // ==============================
                $advanceSalary = AdvanceSalary::with('payment', 'advance_type')
                    ->where('status_id', 5)
                    ->where('company_id', $companyId)
                    ->where('user_id', $user->id)
                    ->whereMonth('recover_from', date('m', strtotime($selectedDate)));
                $installment = $advanceSalary->clone()->where('recovery_mode', 1)->sum('installment_amount');
                $onetime = $advanceSalary->clone()->where('recovery_mode', 2)->sum('amount');

                // ==============================
                // SALARIO NETO
                // ==============================
                $netSalary = ($fortnightBasePay + $additionFort) - ($deductionFort + $leaveCuts + $installment + $onetime);

                // Salario bruto quincenal (antes de descuentos de ley)
                $grossFortnight = round($user->basic_salary / 2, 2);

                // ==============================
                // GUARDAR REGISTRO
                // ==============================
                $salary                  = new SalaryGenerate();
                $salary->user_id         = $user->id;
                $salary->company_id      = $companyId;
                $salary->date            = date('Y-m-d', strtotime($request->month));
                $salary->department_id   = $user->department_id;
                $salary->gross_salary    = $grossFortnight;
                $salary->amount          = round($netSalary, 2);
                $salary->due_amount      = round($netSalary, 2);
                $salary->net_salary      = round($netSalary, 2);

                // Asistencia
                $salary->total_working_day = $totalWorkingDays;
                $salary->present           = $totalPresent;
                $salary->absent            = $totalAbsent;
                $salary->late              = $totalLate;
                $salary->left_early        = $totalEarly;

                // Descuentos de ley
                $salary->isss_amount              = $legal['isss_fortnight'];
                $salary->afp_amount               = $legal['afp_fortnight'];
                $salary->income_tax_amount        = $legal['income_tax_fortnight'];
                $salary->income_tax_bracket       = $legal['income_tax_bracket'];
                $salary->legal_deductions_detail  = $legal;

                // Comisiones
                $salary->allowance_amount  = $additionFort;
                $salary->allowance_details = $additionDetail;
                $salary->deduction_amount  = $deductionFort;
                $salary->deduction_details = $deductionDetail;

                // Ausencias y anticipos
                $salary->absent_amount   = round($leaveCuts, 2);
                $salary->advance_amount  = $installment + $onetime;
                $salary->advance_details = $advanceSalary->get();

                $salary->adjust      = 0;
                $salary->is_calculated = 1;  // ★ Ya viene calculado
                $salary->created_by  = auth()->user()->id;
                $salary->updated_by  = auth()->user()->id;
                $salary->save();

                $lastSalary = $salary;
            }

            if (!$lastSalary) {
                DB::rollBack();
                return $this->responseWithError(_trans('message.No employees with valid salary found for this period'), [], 400);
            }

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

    function approveLeaveOfMonth($user_id,$yearMonth){

        $leaves=LeaveRequest::where('user_id',$user_id)->where('status_id',1)
            ->where(function($query) use($yearMonth){
                $query->where('leave_from','like','%'.$yearMonth.'%')->orWhere('leave_to','like','%'.$yearMonth.'%');
            })
            ->get();

            $total_leave=0;

            foreach($leaves as $leave){
                $start_date=$leave->leave_from;
                $end_date=$leave->leave_to;
                $dates = getBetweenDates($start_date, $end_date);

                foreach($dates as $date){
                    $date=date('Y-m',strtotime($date));
                    if($date==$yearMonth){
                        $total_leave++;
                    }
                }
            }
            return $total_leave;
    }

    public function info($params)
    {
        try {
            $salary_info = $this->model($params)->first();
            $date = $salary_info->date;
            $user = $salary_info->employee;

            // Determinar rango según la quincena (fecha del registro)
            $range = $this->getFortnightRange($date);
            $cal = $this->workdaysAndWeekendsInRange($range['start'], $range['end'], $user->joining_date);
            $holiday = $this->holidaysInRange($range['start'], $range['end'], $cal['weekends']);

            // total working days
            $total_leave=$this->approveLeaveOfMonth($user->id,date('Y-m',strtotime($date)));
            $total_working_days    = max(0, count($cal['workdays']) - count($holiday) - $total_leave);
            $workable_days=count($cal['workdays']);
            $raw               =  DB::table('attendances')->where('company_id', auth()->user()->company_id)->where('user_id', $user->id)->whereBetween('date', [$range['start'], $range['end']]);
            $checkinAtt        = $raw->clone()->orderby('id', 'asc')->groupBy('date')->get();
            // $checkoutAtt       = $raw->clone()->orderby('id', 'desc')->get()->unique('date');

            $total_present = $checkinAtt->count();

            $total_absent  = max(0, $total_working_days - $total_present);
            $total_late    = $raw->clone()->where('in_status', 'L')->orderby('id', 'asc')->groupBy('date')->count();
            $total_early   = $raw->clone()->where('out_status', 'LE')->orderby('id', 'desc')->get()->unique('date')->count();
            $per_day_salary = $total_working_days > 0 ? ($user->basic_salary / $total_working_days) : 0;
            $leave_cuts = $this->getLeave($date, $user, $per_day_salary, $total_absent);
            //advance salary
            $advance_salary = AdvanceSalary::with('payment', 'advance_type')
                ->where('status_id', 5)
                ->where('company_id', auth()->user()->company_id)
                ->where('user_id', $user->id)
                ->whereMonth('recover_from', date('m', strtotime($date)));

            //installment salary
            $installment = $advance_salary->clone()->where('recovery_mode', 1)->sum('installment_amount');

            //onetime salary
            $onetime = $advance_salary->clone()->where('recovery_mode', 2)->sum('amount');

            // commission salary — filtrar por vigencia y quincena
            $fortnightNumberInfo = (int)date('d', strtotime($range['start'])) <= 15 ? 1 : 2;
            $commission = SalarySetupDetails::with('commission:id,type,name')
                ->where('status_id', 1)
                ->where('company_id', auth()->user()->company_id)
                ->where('user_id', $user->id)
                ->where(function($q) use ($range) {
                    $q->whereNull('date_from')->orWhere('date_from', '<=', $range['end']);
                })
                ->where(function($q) use ($range) {
                    $q->whereNull('date_to')->orWhere('date_to', '>=', $range['start']);
                })
                ->where(function($q) use ($fortnightNumberInfo) {
                    $q->where('fortnight_apply', 3)->orWhere('fortnight_apply', $fortnightNumberInfo);
                })
                ->get();
            $addition = 0;
            $deduction = 0;
            //json addition and deduction details
            $addition_detail = [];
            $deduction_detail = [];
            foreach ($commission as $key => $value) {
                if (!$value->commission) continue; // Skip orphaned records
                $cAmt = $value->amount_type == 1 ? $value->amount : (($value->amount / 100) * $user->basic_salary);
                $fortAmt = $cAmt;

                $detail = [
                    'type' => $value->commission->type,
                    'amount_type' => $value->amount_type,
                    'amount' => $cAmt,
                    'fortnight_amount' => $fortAmt,
                    'old_amount' => $value->amount,
                    'name' => $value->commission->name,
                    'fortnight_apply' => (int)($value->fortnight_apply ?? 3),
                    'payroll_column' => $value->commission->payroll_column ?? ($value->commission->type == 1 ? 'comision' : 'otros_desc'),
                ];
                if ($value->commission->type == 1) {
                    $addition += $fortAmt;
                    $addition_detail[] = $detail;
                } else {
                    $deduction += $fortAmt;
                    $deduction_detail[] = $detail;
                }
            }

            // ==============================
            // DEDUCCIONES POR TARDANZA (módulo SpecialAttendance)
            // ==============================
            // Las deducciones del módulo SpecialAttendance se acumulan mensualmente en la tabla
            // `deductions` con appeal_status: 2=Pending, 5=Approved (no aplica), 6=Rejected (sí aplica).
            // Se aplica la mitad en cada quincena del mes correspondiente.
            $tardyDeductionMonth = DB::table('deductions')
                ->where('company_id', auth()->user()->company_id)
                ->where('user_id', $user->id)
                ->where('year', (int) date('Y', strtotime($date)))
                ->where('month', (int) date('n', strtotime($date)))
                ->where(function ($q) {
                    $q->where('is_appealed', 0)
                      ->orWhereNull('is_appealed')
                      ->orWhere(function ($q2) {
                          // Apelación pendiente (2) o rechazada (6) → la deducción aplica.
                          // Apelación aprobada (5) → la deducción NO aplica.
                          $q2->where('is_appealed', 1)->whereIn('appeal_status', [2, 6]);
                      });
                })
                ->sum('amount');

            $tardyDeductionFortnight = round(((float) $tardyDeductionMonth) / 2, 2);
            if ($tardyDeductionFortnight > 0) {
                $deduction += $tardyDeductionFortnight;
                $deduction_detail[] = [
                    'type' => 2,
                    'amount_type' => 1,
                    'amount' => $tardyDeductionFortnight,
                    'fortnight_amount' => $tardyDeductionFortnight,
                    'old_amount' => (float) $tardyDeductionMonth,
                    'name' => 'Deducción por tardanza/temprano',
                    'fortnight_apply' => 3,
                    'payroll_column' => 'tardanza',
                ];
            }

            // ==============================
            // PAGO DOBLE POR TRABAJO EN ASUETO / DOMINGO (Art. 192 Código de Trabajo ES)
            // ==============================
            // Si un empleado tiene marcación en un día feriado o weekend, se le abona
            // un día adicional de salario (pago doble = salario normal + bono 100%).
            $worked_dates = $checkinAtt->pluck('date')->map(function ($d) {
                return is_string($d) ? substr($d, 0, 10) : Carbon::parse($d)->toDateString();
            })->unique()->values()->all();

            $holiday_worked = array_values(array_intersect($worked_dates, $holiday));
            $weekend_worked = array_values(array_intersect($worked_dates, $cal['weekends']));
            $double_pay_dates = array_values(array_unique(array_merge($holiday_worked, $weekend_worked)));

            // Para calcular el bono usamos el per_day_salary basado en el salario mensual completo.
            $per_day_for_bonus = $total_working_days > 0
                ? round($user->basic_salary / max(1, $total_working_days), 2)
                : 0;
            $holiday_bonus_amount = round(count($double_pay_dates) * $per_day_for_bonus, 2);
            if ($holiday_bonus_amount > 0) {
                $addition += $holiday_bonus_amount;
                $addition_detail[] = [
                    'type' => 1,
                    'amount_type' => 1,
                    'amount' => $holiday_bonus_amount,
                    'fortnight_amount' => $holiday_bonus_amount,
                    'old_amount' => $holiday_bonus_amount,
                    'name' => 'Bono por trabajo en asueto/domingo',
                    'fortnight_apply' => 3,
                    'payroll_column' => 'asueto_domingo',
                    'detail_dates' => $double_pay_dates,
                ];
            }

            // ==============================
            // DESCUENTOS DE LEY (ISSS, AFP, RENTA)
            // ==============================
            // Calcular sobre el salario mensual completo
            $legalDeductionsService = app(LegalDeductionsService::class);
            $legalDeductions = $legalDeductionsService->calculate($user->basic_salary);

            // El pago quincenal base es el salario neto mensual / 2
            $fortnightBasePay = $legalDeductions['fortnight_pay'];

            // Calcular salario por día basado en el pago quincenal
            $per_day_salary_adjusted = $total_working_days > 0 ? ($fortnightBasePay / $total_working_days) : 0;

            // Recalcular descuentos por ausencias basado en el pago quincenal ajustado
            $leave_cuts_adjusted = $total_absent > 0 ? ($total_absent * $per_day_salary_adjusted) : 0;

            // Salario neto quincenal = Pago quincenal base + adiciones - deducciones - ausencias - anticipos
            // NOTA: Los montos de comisiones ya vienen calculados por quincena arriba
            $addition_fortnight = $addition;
            $deduction_fortnight = $deduction;
            $installment_fortnight = $installment; // Los anticipos se cobran completos en la quincena correspondiente
            $onetime_fortnight = $onetime;

            $net_salary = ($fortnightBasePay + $addition_fortnight) - ($deduction_fortnight + $leave_cuts_adjusted + $installment_fortnight + $onetime_fortnight);

            return [
                'workable_days' => @$workable_days,
                'total_working_days' => $total_working_days,
                'total_present' => $total_present,
                'total_absent' => $total_absent,
                'total_late' => $total_late,
                'total_early' => $total_early,
                'per_day_salary' => $per_day_salary_adjusted,
                'advance_salary' => $advance_salary->get(),
                'total_leave' => $total_leave,
                'total_holiday' => count($holiday),
                'leave_cuts' => $leave_cuts_adjusted,
                'installment' => $installment_fortnight,
                'onetime' => $onetime_fortnight,
                'addition' => $addition_fortnight,
                'deduction' => $deduction_fortnight,
                'addition_detail' => $addition_detail,
                'deduction_detail' => $deduction_detail,
                // Punto 4 — visibilidad de los nuevos conceptos
                'tardy_deduction_month' => (float) $tardyDeductionMonth,
                'tardy_deduction_fortnight' => $tardyDeductionFortnight,
                'holiday_worked' => $holiday_worked,
                'weekend_worked' => $weekend_worked,
                'double_pay_dates' => $double_pay_dates,
                'holiday_bonus_amount' => $holiday_bonus_amount,
                // Descuentos de ley (por quincena)
                'legal_deductions' => $legalDeductions,
                'isss_amount' => $legalDeductions['isss_fortnight'],
                'afp_amount' => $legalDeductions['afp_fortnight'],
                'income_tax_amount' => $legalDeductions['income_tax_fortnight'],
                'income_tax_bracket' => $legalDeductions['income_tax_bracket'],
                'total_legal_deductions' => $legalDeductions['total_legal_deductions_fortnight'],
                // Salario base mensual y quincenal
                'gross_monthly_salary' => $user->basic_salary,
                'fortnight_base_pay' => $fortnightBasePay,
                'net_salary' => $net_salary,
            ];
        } catch (\Throwable $th) {
            return $this->responseExceptionError($th->getMessage(), [], 400);
        }
    }

    public function calculate($request, $params)
    {
        DB::beginTransaction();
        try {

            $salary_info = $this->model($params)->first();
            if (@$salary_info->is_calculated) {
                return $this->responseWithError(_trans('message.Salary already calculated!'), 'id', 404);
            }
            $info = $this->info($params);
            $salary_info->amount = floatval($info['net_salary']) + floatval($request->adjust);
            $salary_info->due_amount = $salary_info->amount;
            $salary_info->total_working_day = $info['total_working_days'];
            $salary_info->present = $info['total_present'];
            $salary_info->absent = $info['total_absent'];
            $salary_info->late = $info['total_late'];
            $salary_info->left_early = $info['total_early'];
            $salary_info->allowance_amount = $info['addition'];
            $salary_info->allowance_details = ($info['addition_detail']);
            $salary_info->deduction_amount = $info['deduction'];
            $salary_info->deduction_details = ($info['deduction_detail']);
            $salary_info->absent_amount = $info['leave_cuts'];
            $salary_info->net_salary = $salary_info->amount;
            $salary_info->adjust = floatval($request->adjust);
            $salary_info->is_calculated = 1;
            $salary_info->advance_amount = $info['installment'] + $info['onetime'];
            $salary_info->advance_details =  $info['advance_salary'];
            
            // Guardar descuentos de ley (ISSS, AFP, Renta)
            $salary_info->isss_amount = $info['isss_amount'] ?? 0;
            $salary_info->afp_amount = $info['afp_amount'] ?? 0;
            $salary_info->income_tax_amount = $info['income_tax_amount'] ?? 0;
            $salary_info->income_tax_bracket = $info['income_tax_bracket'] ?? 1;
            $salary_info->legal_deductions_detail = $info['legal_deductions'] ?? null;
            
            $salary_info->save();

            if ($salary_info->advance_amount > 0) {
                foreach ($salary_info->advance_details as $key => $value) {
                    $advance                              = AdvanceSalary::find($value['id']);
                    $get_amount         = $advance->recovery_mode == 1 ? $advance->installment_amount : $advance->amount;
                    $advance->due_amount = $advance->due_amount - $get_amount;
                    $advance->paid_amount = $advance->paid_amount + $get_amount;
                    $advance->updated_by = auth()->id();
                    if ($advance->due_amount <= 0) {
                        $advance->pay = 8;
                        $advance->return_status = 23;
                    } else {
                        $advance->return_status = 21;
                    }
                    $advance->save();
                    if (@$advance) {
                        $advanceSalaryLog                        = new AdvanceSalaryLog();
                        $advanceSalaryLog->advance_salary_id     = $advance->id;
                        $advanceSalaryLog->is_pay                = 1;
                        $advanceSalaryLog->amount                = $get_amount;
                        $advanceSalaryLog->due_amount            = $advance->due_amount;
                        $advanceSalaryLog->user_id               = $advance->user_id;
                        $advanceSalaryLog->payment_note           = @$request->description ?? 'Payment';
                        $advanceSalaryLog->created_by            = auth()->id();
                        $advanceSalaryLog->updated_by            = auth()->id();
                        $advanceSalaryLog->save();
                    }
                }
            }

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

    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_calculate') && $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_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-success">' . _trans('payroll.Addition') . ' : ' . currency_format(number_format($data->allowance_amount, 2)) . '</span><br>';
                    $is_calculated .= '<span class="text-danger">' . _trans('payroll.Deduction') . ' : ' . currency_format(number_format(($data->deduction_amount + $data->absent_amount + $data->advance_amount), 2)) . '</span><br>';
                    $is_calculated .= '<span class="text-success">' . _trans('payroll.Adjust Salary') . ' : ' . currency_format(number_format($data->adjust, 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)),
                    // Tipo de salario según payslip_type del empleado
                    'type'           => (function ($type) {
                        $type = (int) ($type ?? 0);
                        switch ($type) {
                            case 1:
                                return _trans('payroll.Per Month');
                            case 2:
                                return _trans('payroll.Per Week');
                            case 3:
                                return _trans('payroll.Per Day');
                            case 4:
                                return _trans('payroll.Fortnight');
                            default:
                                return 'N/A';
                        }
                    })($data->employee->payslip_type),
                    '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(),
            ],
        ];
    }

    function getMonthlyPayroll(){
        $data = [];
        $category = [];
        $info = [];
        for ($i = 1; $i <= 12; $i++) {
            $category[] = date('F', mktime(0, 0, 0, $i, 10));
            $info[] =  $this->model->query()->where('company_id', auth()->user()->company_id)->whereMonth('date', $i)->sum('amount');
        }
        $data['categories'] =  [
            [
                'name' => _trans('payroll.Payroll'),
                'type' => 'bar',
                'data' => $info,
            ]
        ];
        $data['date_array'] = $category;
        return $data;

    }


    public function salaryReport($isPaginate = true)
    {
        $data['departments']        = Department::authorizable()->where('status_id', 1)->pluck('title', 'id');
        $data['employees']          = User::query()
                                    ->authorizable()
                                    ->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);
                                            });
                                        }
                                    });

        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;
    }

    // =============================================
    // NUEVOS MÉTODOS: Vista agrupada, Export, Eliminar masivo
    // =============================================

    /**
     * Retorna datos de salario agrupados por quincena para la vista de acordeón.
     */
    public function groupedTable($request)
    {
        $query = SalaryGenerate::with([
                'employee:id,name,department_id,payslip_type,employee_id',
                'employee.department:id,title',
                'status',
            ])
            ->where('company_id', auth()->user()->company_id);

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

        $salaries = $query->orderBy('date', 'desc')->get();

        // Agrupar por quincena: "2026-02-primera" o "2026-02-segunda"
        $grouped = $salaries->groupBy(function ($item) {
            $day = (int) date('d', strtotime($item->date));
            $yearMonth = date('Y-m', strtotime($item->date));
            $quincena = $day <= 15 ? 'primera' : 'segunda';
            return $yearMonth . '-' . $quincena;
        });

        // Meses en español
        $mesesEs = [
            1 => 'Enero', 2 => 'Febrero', 3 => 'Marzo', 4 => 'Abril',
            5 => 'Mayo', 6 => 'Junio', 7 => 'Julio', 8 => 'Agosto',
            9 => 'Septiembre', 10 => 'Octubre', 11 => 'Noviembre', 12 => 'Diciembre',
        ];

        return $grouped->map(function ($items, $key) use ($mesesEs) {
            $parts = explode('-', $key);
            $year = $parts[0];
            $month = (int) $parts[1];
            $quincena = $parts[2];
            $monthName = $mesesEs[$month] ?? $month;
            $lastDay = date('t', mktime(0, 0, 0, $month, 1, $year));

            if ($quincena === 'primera') {
                $label = _trans('payroll.First fortnight') . " (1–15) — " . $monthName . " " . $year;
                $refDate = $year . '-' . str_pad($month, 2, '0', STR_PAD_LEFT) . '-15';
            } else {
                $label = _trans('payroll.Second fortnight') . " (16–" . $lastDay . ") — " . $monthName . " " . $year;
                $refDate = $year . '-' . str_pad($month, 2, '0', STR_PAD_LEFT) . '-' . $lastDay;
            }

            $calculated = $items->where('is_calculated', 1)->count();
            $paid = $items->where('status_id', 8)->count();
            $canDeleteAll = $items->every(fn($s) => $s->status_id == 9);
            $totalNet = $items->where('is_calculated', 1)->sum('net_salary');

            return [
                'key' => $key,
                'label' => $label,
                'date' => $refDate,
                'total_employees' => $items->count(),
                'calculated' => $calculated,
                'paid' => $paid,
                'can_delete_all' => $canDeleteAll,
                'total_net' => $totalNet,
                'items' => $items->map(function ($s) {
                    // Tipo de recibo
                    $type = (int) ($s->employee->payslip_type ?? 0);
                    $typeLabel = match ($type) {
                        1 => _trans('payroll.Per Month'),
                        2 => _trans('payroll.Per Week'),
                        3 => _trans('payroll.Per Day'),
                        4 => _trans('payroll.Fortnight'),
                        default => 'N/A',
                    };

                    return [
                        'id' => $s->id,
                        'employee_name' => $s->employee->name ?? '',
                        'employee_id' => $s->employee->employee_id ?? '',
                        'salary' => $s->gross_salary,
                        'type' => $typeLabel,
                        'is_calculated' => $s->is_calculated,
                        'net_salary' => $s->net_salary,
                        'allowance_amount' => $s->allowance_amount ?? 0,
                        'deduction_amount' => $s->deduction_amount ?? 0,
                        'absent_amount' => $s->absent_amount ?? 0,
                        'advance_amount' => $s->advance_amount ?? 0,
                        'adjust' => $s->adjust ?? 0,
                        'status_id' => $s->status_id,
                        'status_name' => $s->status->name ?? '',
                        'status_class' => $s->status->class ?? 'secondary',
                    ];
                })->values(),
            ];
        });
    }

    /**
     * Obtiene datos de planilla para exportar en formato Excel agrupado por departamento.
     */
    public function getPlanillaData($date, $departmentId = null)
    {
        $range = $this->getFortnightRange($date);

        $query = SalaryGenerate::with([
                'employee:id,name,department_id,payslip_type,employee_id',
                'employee.department:id,title',
            ])
            ->where('company_id', auth()->user()->company_id)
            ->whereBetween('date', [$range['start'], $range['end']]);

        if ($departmentId) {
            $query->whereHas('employee', function ($q) use ($departmentId) {
                $q->where('department_id', $departmentId);
            });
        }

        $salaries = $query->orderBy('user_id')->get();

        $day = (int) date('d', strtotime($date));
        $daysInPeriod = $day <= 15 ? 15 : ((int) date('t', strtotime($date)) - 15);

        // Pre-scan: encontrar todos los tipos únicos de ingreso/descuento
        $incomeTypes = [];
        $deductionTypes = [];
        foreach ($salaries as $s) {
            $allowDets = is_string($s->allowance_details)
                ? json_decode($s->allowance_details, true)
                : ($s->allowance_details ?? []);
            foreach ($allowDets as $det) {
                $name = $det['name'] ?? 'Otros Ingresos';
                if (!in_array($name, $incomeTypes)) {
                    $incomeTypes[] = $name;
                }
            }
            $deducDets = is_string($s->deduction_details)
                ? json_decode($s->deduction_details, true)
                : ($s->deduction_details ?? []);
            foreach ($deducDets as $det) {
                $name = $det['name'] ?? 'Otros Desc.';
                if (!in_array($name, $deductionTypes)) {
                    $deductionTypes[] = $name;
                }
            }
        }

        // Agrupar por departamento
        $byDept = $salaries->groupBy(function ($s) {
            return $s->employee->department->title ?? _trans('common.No Department');
        });

        return [
            'range' => $range,
            'days_in_period' => $daysInPeriod,
            'income_types' => $incomeTypes,
            'deduction_types' => $deductionTypes,
            'departments' => $byDept->map(function ($group) use ($daysInPeriod, $incomeTypes, $deductionTypes) {
                return $group->map(function ($s) use ($daysInPeriod, $incomeTypes, $deductionTypes) {
                    $isss = (float) ($s->isss_amount ?? 0);
                    $afp = (float) ($s->afp_amount ?? 0);
                    $renta = (float) ($s->income_tax_amount ?? 0);

                    // Desglosar ingresos por nombre de tipo
                    $ingresosByType = array_fill_keys($incomeTypes, 0);
                    $allowanceDetails = is_string($s->allowance_details)
                        ? json_decode($s->allowance_details, true)
                        : ($s->allowance_details ?? []);
                    foreach ($allowanceDetails as $det) {
                        $name = $det['name'] ?? 'Otros Ingresos';
                        $amt = (float) ($det['fortnight_amount'] ?? $det['amount'] ?? 0);
                        if (isset($ingresosByType[$name])) {
                            $ingresosByType[$name] += $amt;
                        }
                    }

                    // Desglosar descuentos por nombre de tipo
                    $descuentosByType = array_fill_keys($deductionTypes, 0);
                    $deductionDetails = is_string($s->deduction_details)
                        ? json_decode($s->deduction_details, true)
                        : ($s->deduction_details ?? []);
                    foreach ($deductionDetails as $det) {
                        $name = $det['name'] ?? 'Otros Desc.';
                        $amt = (float) ($det['fortnight_amount'] ?? $det['amount'] ?? 0);
                        if (isset($descuentosByType[$name])) {
                            $descuentosByType[$name] += $amt;
                        }
                    }

                    $salarioBase = (float) ($s->gross_salary ?? 0);
                    $diasTrabajados = (int) ($s->present ?? $daysInPeriod);
                    $salarioDevengado = $daysInPeriod > 0
                        ? round($salarioBase * $diasTrabajados / $daysInPeriod, 2)
                        : 0;

                    $totalIngresosExtra = array_sum($ingresosByType);
                    $totalIngresos = $salarioDevengado + $totalIngresosExtra;

                    $totalDescuentosExtra = array_sum($descuentosByType);
                    $totalDescs = $isss + $afp + $renta + $totalDescuentosExtra;

                    return [
                        'emp_code' => $s->employee->employee_id ?? '',
                        'nombre' => strtoupper($s->employee->name ?? ''),
                        'salario_base' => $salarioBase,
                        'dias_trabajados' => $diasTrabajados,
                        'dias_periodo' => $daysInPeriod,
                        'salario_devengado' => $salarioDevengado,
                        'h_extra' => 0,
                        'ingresos_by_type' => $ingresosByType,
                        'total_ingresos' => $totalIngresos,
                        'isss' => $isss,
                        'afp' => $afp,
                        'renta' => $renta,
                        'descuentos_by_type' => $descuentosByType,
                        'total_descs' => $totalDescs,
                        'liquido_recibir' => $totalIngresos - $totalDescs,
                    ];
                })->values();
            }),
        ];
    }

    /**
     * Eliminar toda la planilla de una quincena.
     * Solo si TODAS las nóminas están en estado Draft (status_id=9).
     */
    public function deleteBulkByQuincena($date)
    {
        $range = $this->getFortnightRange($date);

        $salaries = $this->model
            ->where('company_id', auth()->user()->company_id)
            ->whereBetween('date', [$range['start'], $range['end']])
            ->get();

        if ($salaries->isEmpty()) {
            return $this->responseWithError(_trans('message.Data not found'), [], 404);
        }

        // Verificar que NINGUNA esté pagada o calculada
        $nonDeletable = $salaries->filter(fn($s) => $s->status_id != 9);
        if ($nonDeletable->isNotEmpty()) {
            return $this->responseWithError(
                _trans('message.Cannot delete: some records are already calculated or paid'),
                [],
                400
            );
        }

        DB::beginTransaction();
        try {
            $count = $this->model
                ->where('company_id', auth()->user()->company_id)
                ->whereBetween('date', [$range['start'], $range['end']])
                ->where('status_id', 9)
                ->delete();

            DB::commit();
            return $this->responseWithSuccess(
                _trans('message.Payroll deleted successfully') . ' (' . $count . ' ' . _trans('common.records') . ')',
                []
            );
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseExceptionError($th->getMessage(), [], 400);
        }
    }
}