<?php

namespace App\Http\Controllers\SuperAdmin;

use App\Http\Controllers\Controller;
use App\Models\EmployeeAttendance;
use Illuminate\Http\Request;
use App\Models\EmployeeDetails;
use App\Models\EmployeeSalaryDetail;
use Illuminate\Support\Facades\Storage;
use Yajra\DataTables\Facades\DataTables;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Validator;
use PhpOffice\PhpSpreadsheet\IOFactory;
use Illuminate\Support\Facades\DB;
use App\Models\EmployeeLeave;
use App\Models\EmployeeSalaryStructure;
use App\Models\Holiday;
use App\Models\EmployeeLeaveBalance;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use Illuminate\Support\Carbon;

use Illuminate\Validation\Rule;
use Maatwebsite\Excel\Facades\Excel;
use Barryvdh\DomPDF\Facade\Pdf;


use Carbon\CarbonPeriod;

class SuperAdminEmployeeController extends Controller
{

    public function employeeDetails()
    {
        $employees = EmployeeDetails::all();
        return view('superadmin.employee.employeedetails', compact('employees'));
    }

    public function employeeAttendance(Request $request)
    {
        $employees = EmployeeDetails::all();
        $query = EmployeeAttendance::orderByDesc('date');

        if ($request->has('date') && $request->date != '') {
            $query->whereDate('date', $request->date);
        } else {
            // Optional: Default to today or current month to avoid huge data load
             $query->whereDate('date', now()->format('Y-m-d'));
        }

        $attendances = $query->get();
        return view('superadmin.employee.employeeattendance', compact('employees', 'attendances'));
    }

    // Store
    public function storeEmployeeAttendance(Request $request)
    {
        $request->validate([
            'employee_id' => 'required|exists:employees,employee_id', // Assuming employee_id column matches
            'date' => 'required|date',
            'check_in' => 'nullable', // Allow manual entry without time initially? User said "missing data... added manually". Likely expects times.
            'check_out' => 'nullable',
        ]);

        $employee = EmployeeDetails::where('employee_id', $request->employee_id)->first();

        // Calculate Hours
        $checkIn = $request->check_in ? \Carbon\Carbon::parse($request->check_in) : null;
        $checkOut = $request->check_out ? \Carbon\Carbon::parse($request->check_out) : null;
        $breakOut = $request->break_out ? \Carbon\Carbon::parse($request->break_out) : null;
        $breakIn = $request->break_in ? \Carbon\Carbon::parse($request->break_in) : null;

        $otHours = 0;
        $shortageHours = 0;
        $workingHours = 0;

        if ($checkIn && $checkOut) {
            $totalMinutes = $checkOut->diffInMinutes($checkIn);
            $breakMinutes = ($breakOut && $breakIn) ? $breakIn->diffInMinutes($breakOut) : 0;
            
            // If break times are inverted or just wrong, handle grace? Defaulting to simple math
            $netMinutes = max(0, $totalMinutes - $breakMinutes);
            $workingHours = round($netMinutes / 60, 2);

            // Logic: Standard 9 hours?
            $standardHours = 9; 
            if ($workingHours > $standardHours) {
                $otHours = round($workingHours - $standardHours, 2);
            } elseif ($workingHours < $standardHours) {
                $shortageHours = round($standardHours - $workingHours, 2);
            }
        }

        EmployeeAttendance::create([
            'employee_id' => $request->employee_id,
            'employee_name' => $employee ? $employee->name : 'Unknown',
            'date' => $request->date,
            'check_in' => $request->check_in,
            'break_out' => $request->break_out,
            'break_in' => $request->break_in,
            'check_out' => $request->check_out,
            'ot_hours' => $otHours,
            'shortage_hours' => $shortageHours,
            // 'month_year' => ... // if needed by legacy, but schema didn't highlight it as critical unique key
        ]);

        return back()->with('success', 'Attendance added successfully.');
    }

    // Get for edit (AJAX already existed? No, the route was commented out in web.php search, actually lines 1547.
    // I need to ensure web.php has the route enabled.
    // But my View uses Modal with data attributes, I might not need a specialized GET route if I pass data in loop.
    // Yes, View uses data attributes. So I just need Update method.)

    // Update
    public function updateEmployeeAttendance(Request $request, $id)
    {
        $attendance = EmployeeAttendance::findOrFail($id);
        
        $request->validate([
            'date' => 'required|date',
            'check_in' => 'nullable',
            'check_out' => 'nullable',
        ]);

        // Recalculate Logic (Same as store - simpler to refactor into private method if reused often, but inline is fine here)
        $checkIn = $request->check_in ? \Carbon\Carbon::parse($request->check_in) : null;
        $checkOut = $request->check_out ? \Carbon\Carbon::parse($request->check_out) : null;
        $breakOut = $request->break_out ? \Carbon\Carbon::parse($request->break_out) : null;
        $breakIn = $request->break_in ? \Carbon\Carbon::parse($request->break_in) : null;

        $otHours = 0;
        $shortageHours = 0;
        $workingHours = 0;

        if ($checkIn && $checkOut) {
            // Ensure dates are same for diff (since request only has time, Parse uses today.
            // But if cross-midnight? Usually attendance is same day or specific logic. 
            // Assuming same day for simplify unless 'date' + 'time' combined.
            // Carbon::parse($time) gives today's date with that time.
            // diffInMinutes handles it fine for same day.
            
            // To be safe with "night shifts" we might need full datetimes, but input is separate date and time.
            // If check_out < check_in, simple parse implies CheckOut is earlier today -> negative diff?
            // Carbon::parse("18:00")->diffInMinutes(Carbon::parse("09:00")) -> 540 (abs)
            // But direction matters.
            // If user enters CheckIn 09:00, CheckOut 18:00.
            // $checkOut->diffInMinutes($checkIn) is absolute usually? No, diffInMinutes is abs by default false?
            // "diffInMinutes($dt, $abs)" default true. So order doesn't matter for magnitude.
            
            $totalMinutes = $checkOut->diffInMinutes($checkIn); // absolute diff
             // Break
            $breakMinutes = ($breakOut && $breakIn) ? $breakIn->diffInMinutes($breakOut) : 0;

            $netMinutes = max(0, $totalMinutes - $breakMinutes);
            $workingHours = round($netMinutes / 60, 2);

            $standardHours = 9; 
            if ($workingHours > $standardHours) {
                $otHours = round($workingHours - $standardHours, 2);
            } elseif ($workingHours < $standardHours) {
                $shortageHours = round($standardHours - $workingHours, 2);
            }
        }

        $attendance->update([
            'employee_id' => $request->employee_id ?? $attendance->employee_id, // employee_id might not be passed in edit if disabled? View didn't disable it.
            // 'employee_name' ... strictly speaking should update if ID changes, but user might just correct time.
            'date' => $request->date,
            'check_in' => $request->check_in,
            'break_out' => $request->break_out,
            'break_in' => $request->break_in,
            'check_out' => $request->check_out,
            'ot_hours' => $otHours,
            'shortage_hours' => $shortageHours,
        ]);

        return back()->with('success', 'Attendance updated successfully.');
    }

    // Delete
    public function destroyEmployeeAttendance($id)
    {
        EmployeeAttendance::destroy($id);
        return back()->with('success', 'Attendance deleted.');
    }

    public function getEmployeeAttendanceEmployee($employee_id)
    {
        // Assuming employee_id is something like "EMP1001"
        $employee = \App\Models\EmployeeDetails::where('employee_id', $employee_id)->first();
        if ($employee) {
            return response()->json([
                'name' => $employee->name,
                'gender' => $employee->gender,
                'dob' => $employee->dob,
                'doj' => $employee->doj,
                'department' => $employee->department,
                'designation' => $employee->designation,
            ]);
        }
        return response()->json([
            'name' => '',
            'gender' => '',
            'dob' => '',
            'doj' => '',
            'department' => '',
            'designation' => ''
        ]);
    }

    public function employeeDailyReport(Request $request)
    {
        $date = $request->get('date', date('Y-m-d'));
        $department = $request->get('department');
        $employeeId = $request->get('employee_id');

        // Get employees query
        $employeesQuery = EmployeeDetails::query();

        if ($department) {
            $employeesQuery->where('department', $department);
        }
        if ($employeeId) {
            $employeesQuery->where('id', $employeeId);
        }

        $employees = $employeesQuery->get();

        // Get unique departments for filter dropdown
        $departments = EmployeeDetails::whereNotNull('department')
            ->where('department', '!=', '')
            ->distinct()
            ->pluck('department')
            ->sort()
            ->values();

        // Get attendance data for the selected date
        $attendanceData = EmployeeAttendance::whereDate('date', $date)->get();

        // Get leave data for the selected date
        $leaveData = EmployeeLeave::where(function($query) use ($date) {
            $query->whereDate('leave_from', '<=', $date)
                  ->whereDate('leave_to', '>=', $date);
        })->get();

        return view('superadmin.employee.employeedailyreport', compact(
            'employees',
            'departments',
            'attendanceData',
            'leaveData'
        ));
    }

    public function employeeCalendar()
    {
        $employees = EmployeeDetails::all();
        return view('superadmin.employee.employeecalendar', compact('employees'));
    }
    
    public function list()
    {
        $employees = \App\Models\EmployeeDetails::select('id', 'employee_id', 'name')->get();
        return response()->json($employees);
    }

    public function index()
    {
        $employees = EmployeeDetails::all();
        return view('superadmin.employee.index', compact('employees'));
    }
    
    public function store_old(Request $request)
    {
        try {
            Log::info('Storing employee data', $request->all());
            $validated = $request->validate([
                'employee_id' => 'required|unique:employees,employee_id',
                'name' => 'required',
                'gender' => 'required',
                'department' => 'nullable',
                'designation' => 'nullable',
                'dob' => 'nullable|date',
                'doj' => 'nullable|date',
                'mobile' => 'nullable|digits:10',
                'aadhar_number' => 'nullable|digits:12',
                'category' => 'nullable|in:E,M,O',
                'status' => 'nullable|in:ACTIVE,INACTIVE',
                'profile_picture' => 'nullable|image|max:2048',
            ]);

            if ($request->hasFile('profile_picture')) {
                $validated['profile_picture'] = $request->file('profile_picture')->store('employees', 'public');
            }

            EmployeeDetails::create($validated);

            return back()->with('success', 'Employee added successfully.');
        } catch (\Exception $e) {
            Log::error('Error storing employee: ' . $e->getMessage());
            return back()->with('error', 'Failed to add employee. Please check the logs.');
        }
    }
    
    public function store(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'employee_id' => [
                'required',
                Rule::unique('employees', 'employee_id'),
            ],
            'name' => 'required|string|max:255',
            'gender' => 'required|in:Male,Female,Other',
            'department' => 'nullable|string|max:255',
            'designation' => 'nullable|string|max:255',
            'dob' => 'nullable|date',
            'doj' => 'nullable|date',
            'mobile' => 'nullable|digits:10',
            'aadhar_number' => 'nullable|digits:12',
            'category' => 'nullable|in:E,M,O',
            'status' => 'nullable|in:ACTIVE,INACTIVE',
            'profile_picture' => 'nullable|image|max:2048',
        ]);

        if ($validator->fails()) {
            return response()->json([
                'success' => false,
                'message' => 'Validation failed.',
                'errors' => $validator->errors(),
            ], 422);
        }

        $validated = $validator->validated();

        if ($request->hasFile('profile_picture')) {
            $validated['profile_picture'] = $request->file('profile_picture')->store('employees', 'public');
        }

        EmployeeDetails::create($validated);

        return response()->json(['success' => true, 'message' => 'Employee added successfully.']);
    }

    public function update_old(Request $request, $id)
    {
        $employee = EmployeeDetails::findOrFail($id);

        $validated = $request->validate([
            'employee_id' => 'required|unique:employees,employee_id,' . $employee->id,
            'name' => 'required',
            'gender' => 'required',
            'department' => 'nullable',
            'designation' => 'nullable',
            'dob' => 'nullable|date',
            'doj' => 'nullable|date',
            'mobile' => 'nullable|digits:10',
            'aadhar_number' => 'nullable|digits:12',
            'category' => 'nullable|in:E,M,O',
            'status' => 'nullable|in:ACTIVE,INACTIVE',
            'profile_picture' => 'nullable|image|max:2048',
        ]);

        if ($request->hasFile('profile_picture')) {
            // Delete the old profile picture if it exists
            if ($employee->profile_picture && Storage::disk('public')->exists($employee->profile_picture)) {
                Storage::disk('public')->delete($employee->profile_picture);
            }
            // Store the new profile picture and update the path
            $validated['profile_picture'] = $request->file('profile_picture')->store('employees', 'public');
        } else {
            // Prevent profile_picture field from being set to null if no file was uploaded
            unset($validated['profile_picture']);
        }

        $employee->update($validated);

        return back()->with('success', 'Employee updated successfully.');
    }
    
    public function update(Request $request, $id)
    {
        $employee = EmployeeDetails::findOrFail($id);
    
        $validator = Validator::make($request->all(), [
            'employee_id' => [
                'required',
                Rule::unique('employees', 'employee_id')->ignore($employee->id),
            ],
            'name' => 'required',
            'gender' => 'required',
            'department' => 'nullable',
            'designation' => 'nullable',
            'dob' => 'nullable|date',
            'doj' => 'nullable|date',
            'mobile' => 'nullable|digits:10',
            'aadhar_number' => 'nullable|digits:12',
            'category' => 'nullable|in:E,M,O',
            'status' => 'nullable|in:ACTIVE,INACTIVE',
            'profile_picture' => 'nullable|image|max:2048',
        ]);
    
        if ($validator->fails()) {
            return response()->json([
                'success' => false,
                'message' => 'Validation failed.',
                'errors' => $validator->errors(),
            ], 422);
        }
    
        $validated = $validator->validated();
    
        if ($request->hasFile('profile_picture')) {
            if ($employee->profile_picture && Storage::disk('public')->exists($employee->profile_picture)) {
                Storage::disk('public')->delete($employee->profile_picture);
            }
    
            $validated['profile_picture'] = $request->file('profile_picture')->store('employees', 'public');
        } else {
            unset($validated['profile_picture']);
        }
    
        $employee->update($validated);
    
        return response()->json([
            'success' => true,
            'message' => 'Employee updated successfully.',
        ]);
    }
    
    public function destroy($id)
    {
        $employee = EmployeeDetails::findOrFail($id);
        if ($employee->profile_picture) {
            Storage::disk('public')->delete($employee->profile_picture);
        }
        $employee->delete();
        return back()->with('success', 'Employee deleted successfully.');
    }

    public function show($id)
    {
        $employee = EmployeeDetails::find($id);
        if ($employee) {
            return response()->json($employee);
        } else {
            return response()->json(['error' => 'Employee not found'], 404);
        }
    }
    // List Salary Details
    public function salaryDetails()
    {
        $employees = EmployeeDetails::all();
        $salaryDetails = EmployeeSalaryDetail::with('employee')->orderByDesc('id')->get();
        return view('superadmin.employee.employeepayroll', compact('employees', 'salaryDetails'));
    }

    // Store Salary Detail
    public function storeSalaryDetail(Request $request)
    {
        $request->validate([
            'employee_id' => 'required|exists:employees,id',
            'month_year' => 'required',
            // Add validation for other fields as needed
        ]);
        $salary = EmployeeSalaryDetail::create($request->all());

        if ($request->ajax() || $request->wantsJson()) {
            return response()->json(['success' => true, 'message' => 'Salary details added successfully.', 'data' => $salary]);
        }
        return redirect()->back()->with('success', 'Salary details added successfully.');
    }

    // AJAX: Get Employee Name by ID
    public function getEmployeeById($id)
    {
        $employee = EmployeeDetails::find($id);
        if ($employee) {
            return response()->json(['name' => $employee->name]);
        }
        return response()->json(['name' => ''], 404);
    }
    // AJAX: Fetch single salary detail
    public function getSalaryDetail($id)
    {
        $salary = EmployeeSalaryDetail::findOrFail($id);
        $employee = $salary->employee;
        return response()->json([
            'month_year' => $salary->month_year,
            'employee_id' => $salary->employee_id,
            'employee_name' => $employee ? $employee->name : '',
            'basic_da' => $salary->basic_da,
            'hra' => $salary->hra,
            'conveyance' => $salary->conveyance,
            'washing_allowance' => $salary->washing_allowance,
            'fixed_salary' => $salary->fixed_salary,
            'no_of_days_worked' => $salary->no_of_days_worked,
            'leave_given' => $salary->leave_given,
            'total_days_for_salary' => $salary->total_days_for_salary,
            'drawn_salary' => $salary->drawn_salary,
            'incentive_hrs' => $salary->incentive_hrs,
            'incentive_rate' => $salary->incentive_rate,
            'incentive_amount' => $salary->incentive_amount,
            'attendance_bonus' => $salary->attendance_bonus,
            'gross_salary' => $salary->gross_salary,
            'esi' => $salary->esi,
            'pf' => $salary->pf,
            'pt' => $salary->pt,
            'advance_deduction' => $salary->advance_deduction,
            'net_salary' => $salary->net_salary,
            'payment_mode' => $salary->payment_mode,
        ]);
    }

    // Update salary detail
    public function updateSalaryDetail(Request $request, $id)
    {
        $request->validate([
            'employee_id' => 'required|exists:employees,id',
            'month_year' => 'required',
            // other validation...
        ]);
        $salary = EmployeeSalaryDetail::findOrFail($id);
        $salary->update($request->all());

        if ($request->ajax() || $request->wantsJson()) {
            return response()->json(['success' => true, 'message' => 'Salary detail updated successfully.', 'data' => $salary]);
        }
        return redirect()->route('superadmin.employee.employeepayroll')->with('success', 'Salary detail updated successfully.');
    }

    // Delete salary detail
    public function destroySalaryDetail($id)
    {
        $salary = EmployeeSalaryDetail::findOrFail($id);
        $salary->delete();

        if (request()->ajax() || request()->wantsJson()) {
            return response()->json(['success' => true, 'message' => 'Salary detail deleted successfully.']);
        }
        return redirect()->route('superadmin.employee.employeepayroll')->with('success', 'Salary detail deleted successfully.');
    }
    
    // List leave requests
    public function employeeLeave(Request $request)
    {
        $employees = EmployeeDetails::all();
        $departments = EmployeeDetails::distinct()->pluck('department')->filter()->values();

        // Build query with filters
        $query = EmployeeLeave::with('employee')->orderBy('id', 'desc');

        // Apply filters
        if ($request->filled('employee_id')) {
            $query->where('employee_id', $request->employee_id);
        }
        if ($request->filled('department')) {
            $query->where('department', $request->department);
        }
        if ($request->filled('leave_type')) {
            $query->where('leave_type', $request->leave_type);
        }
        if ($request->filled('status')) {
            $query->where('status', $request->status);
        }
        if ($request->filled('from_date')) {
            $query->whereDate('leave_from', '>=', $request->from_date);
        }
        if ($request->filled('to_date')) {
            $query->whereDate('leave_to', '<=', $request->to_date);
        }

        $leaves = $query->get();

        // Pre-calculate CL balances using the new EmployeeLeaveBalance model
        $clBalances = [];
        $currentYear = now()->year;
        $currentMonth = now()->month;

        foreach ($employees as $emp) {
            // Get balance from the new model
            $balance = EmployeeLeaveBalance::getOrCreateBalance($emp->id, $currentYear, 'CL');

            $clBalances[$emp->id] = [
                'year' => $currentYear,
                'month' => $currentMonth,
                'cl_available_year' => (float) $balance->current_balance,
                'cl_available_month' => max(0, 1 - $this->getMonthlyClUsed($emp->id, $currentYear, $currentMonth)),
                'opening_balance' => (float) $balance->opening_balance,
                'accrued' => (float) $balance->accrued,
                'used' => (float) $balance->used,
            ];
        }

        return view('superadmin.employee.employeeleave', compact('employees', 'leaves', 'clBalances', 'departments'));
    }

    /**
     * Get CL used for a specific month.
     */
    private function getMonthlyClUsed($employeeId, $year, $month)
    {
        return EmployeeLeave::where('employee_id', $employeeId)
            ->where('leave_type', 'CL')
            ->where('status', 'Approved')
            ->whereYear('leave_from', $year)
            ->whereMonth('leave_from', $month)
            ->sum('cl_days') ?: 0;
    }

    // Store leave request
    public function storeEmployeeLeave(Request $request)
    {
        // Base validation
        $rules = [
            'employee_id'   => 'required|exists:employees,id',
            'leave_type'    => 'required',
            'duration_type' => 'required|in:Full Day,Half Day,Hourly',
            'reason'        => 'required',
        ];

        // Add duration-specific validation
        if ($request->duration_type === 'Full Day') {
            $rules['leave_from'] = 'required|date';
            $rules['leave_to'] = 'required|date|after_or_equal:leave_from';
        } elseif ($request->duration_type === 'Half Day') {
            $rules['leave_from'] = 'required|date';
            $rules['half_type'] = 'required|in:First Half,Second Half';
        } elseif ($request->duration_type === 'Hourly') {
            $rules['leave_from'] = 'required|date';
            $rules['from_hour'] = 'required';
            $rules['to_hour'] = 'required';
        }

        $request->validate($rules);

        $employee = EmployeeDetails::findOrFail($request->employee_id);
        $status = 'Pending';

        // Parse dates and set leave_to
        $leaveFrom = Carbon::parse($request->leave_from);
        $leaveTo = $request->duration_type === 'Full Day' && $request->leave_to
            ? Carbon::parse($request->leave_to)
            : $leaveFrom->copy();

        $year = $leaveFrom->year;

        // Calculate working days (excluding Sundays and holidays)
        $holidays = EmployeeLeave::getHolidaysInRange($leaveFrom, $leaveTo);
        $workingDays = EmployeeLeave::calculateWorkingDays($leaveFrom, $leaveTo, $holidays);

        // Determine number of days based on duration type
        $numberOfDays = 0;
        $fromHour = null;
        $toHour = null;
        $halfType = null;

        if ($request->duration_type === 'Full Day') {
            $numberOfDays = $workingDays;
        } elseif ($request->duration_type === 'Half Day') {
            $numberOfDays = 0.5;
            $halfType = $request->half_type;
        } elseif ($request->duration_type === 'Hourly') {
            $fromHour = $request->from_hour;
            $toHour = $request->to_hour;
            // Calculate hours
            $fromMinutes = $this->timeToMinutes($fromHour);
            $toMinutes = $this->timeToMinutes($toHour);
            $hours = ($toMinutes - $fromMinutes) / 60;
            $numberOfDays = round($hours / 9, 2); // 9-hour workday = 1 day
        }

        // If no working days (all Sundays/holidays), reject
        if ($numberOfDays <= 0 && $request->duration_type === 'Full Day') {
            return back()->with('error', 'No working days in the selected date range (all Sundays/holidays).');
        }

        // --- CL validation & auto-LOP logic ---
        if ($request->leave_type === 'CL') {
            // Get available CL balance
            $balance = EmployeeLeaveBalance::getOrCreateBalance($employee->id, $year, 'CL');
            $availableCL = (float) $balance->current_balance;

            // For CL, only 1 day max can be CL, rest is LOP
            $maxClPerRequest = min(1, $availableCL);
            $clDays = min($numberOfDays, $maxClPerRequest);
            $lopDays = max(0, $numberOfDays - $clDays);

            DB::beginTransaction();
            try {
                EmployeeLeave::create([
                    'employee_id'       => $employee->id,
                    'employee_name'     => $employee->name,
                    'department'        => $employee->department,
                    'leave_type'        => 'CL',
                    'leave_from'        => $leaveFrom,
                    'leave_to'          => $leaveTo,
                    'from_hour'         => $fromHour,
                    'to_hour'           => $toHour,
                    'half_type'         => $halfType,
                    'number_of_days'    => $numberOfDays,
                    'working_days_count'=> $workingDays,
                    'cl_days'           => $clDays,
                    'lop_days'          => $lopDays,
                    'duration_type'     => $request->duration_type,
                    'status'            => $status,
                    'reason'            => $request->reason,
                    'requested_on'      => now(),
                ]);

                DB::commit();

                if ($lopDays > 0) {
                    return back()->with('success', "Leave request submitted: {$clDays} CL + {$lopDays} LOP days (insufficient CL balance).");
                }
                return back()->with('success', 'Leave request added successfully.');
            } catch (\Exception $e) {
                DB::rollBack();
                return back()->with('error', 'Failed to apply leave: ' . $e->getMessage());
            }
        }

        // --- Normal leave creation (SL/PL/LOP/Other) ---
        EmployeeLeave::create([
            'employee_id'       => $employee->id,
            'employee_name'     => $employee->name,
            'department'        => $employee->department,
            'leave_type'        => $request->leave_type,
            'leave_from'        => $leaveFrom,
            'leave_to'          => $leaveTo,
            'from_hour'         => $fromHour,
            'to_hour'           => $toHour,
            'half_type'         => $halfType,
            'number_of_days'    => $numberOfDays,
            'working_days_count'=> $workingDays,
            'cl_days'           => 0,
            'lop_days'          => $request->leave_type === 'LOP' ? $numberOfDays : 0,
            'duration_type'     => $request->duration_type,
            'status'            => $status,
            'reason'            => $request->reason,
            'requested_on'      => now(),
        ]);

        return back()->with('success', 'Leave request added successfully.');
    }

    /**
     * Convert time string (HH:MM) to minutes.
     */
    private function timeToMinutes($time)
    {
        $parts = explode(':', $time);
        return ((int)$parts[0] * 60) + (int)($parts[1] ?? 0);
    }

    public function getEmployeeClBalance($employee_id, Request $request)
    {
        $employee = EmployeeDetails::findOrFail($employee_id);
    
        // Use 'as_of' param or default to now
        $asOf = $request->query('as_of') ?? now()->format('Y-m');
        [$year, $month] = explode('-', $asOf);
    
        // Count approved CL for that year
        $usedCl = EmployeeLeave::where('employee_id', $employee->id)
            ->where('leave_type', 'CL')
            ->where('status', 'Approved')
            ->whereYear('leave_from', $year)
            ->sum('number_of_days');
    
        $monthlyUsedCl = EmployeeLeave::where('employee_id', $employee->id)
            ->where('leave_type', 'CL')
            ->where('status', 'Approved')
            ->whereYear('leave_from', $year)
            ->whereMonth('leave_from', $month)
            ->sum('number_of_days');
    
        $annualCl = 12;
        $monthlyCl = 1;
        $clAvailableYear = max(0, $annualCl - $usedCl);
        $clAvailableMonth = max(0, $monthlyCl - $monthlyUsedCl);
    
        return response()->json([
            'employee_id' => $employee->id,
            'year' => (int) $year,
            'month' => (int) $month,
            'annual_cl_entitlement' => $annualCl,
            'used_cl' => $usedCl,
            'cl_available_year' => $clAvailableYear,
            'monthly_cl_entitlement' => $monthlyCl,
            'used_cl_month' => $monthlyUsedCl,
            'cl_available_month' => $clAvailableMonth,
            'as_of' => "$year-$month",
        ]);
    }
    // Get leave for editing (AJAX)
    public function getEmployeeLeave($id)
    {
        $leave = EmployeeLeave::with('employee')->findOrFail($id);
        return response()->json([
            'employee_id'     => $leave->employee_id,
            'employee_name'   => $leave->employee_name,
            'department'      => $leave->department,
            'leave_type'      => $leave->leave_type,
            'leave_from'      => $leave->leave_from ? $leave->leave_from->format('Y-m-d\TH:i') : null,
            'leave_to'        => $leave->leave_to ? $leave->leave_to->format('Y-m-d\TH:i') : null,
            'number_of_days'  => $leave->number_of_days,
            'duration_type'   => $leave->duration_type,
            'half_type'       => $leave->half_type,
            'from_hour'       => $leave->from_hour,
            'to_hour'         => $leave->to_hour,
            'cl_days'         => $leave->cl_days,
            'lop_days'        => $leave->lop_days,
            'status'          => $leave->status,
            'reason'          => $leave->reason,
            'approved_by'     => $leave->approved_by,
            'approval_date'   => $leave->approval_date ? $leave->approval_date->format('Y-m-d') : null,
        ]);
    }

    // Update leave
    public function updateEmployeeLeave(Request $request, $id)
    {
        $request->validate([
            'employee_id'   => 'required|exists:employees,id',
            'leave_type'    => 'required',
            'leave_from'    => 'required|date',
            'leave_to'      => 'required|date|after_or_equal:leave_from',
            'number_of_days'=> 'required|numeric|min:0.5',
            'duration_type' => 'required',
            'status'        => 'required',
            'reason'        => 'required',
        ]);

        $leave = EmployeeLeave::findOrFail($id);
        $employee = EmployeeDetails::findOrFail($request->employee_id);

        // (Optional) Add similar CL/LOP logic for update if needed, or keep as normal update

        $leave->update([
            'employee_id'   => $employee->id,
            'employee_name' => $employee->name,
            'department'    => $employee->department,
            'leave_type'    => $request->leave_type,
            'leave_from'    => $request->leave_from,
            'leave_to'      => $request->leave_to,
            'number_of_days'=> $request->number_of_days,
            'duration_type' => $request->duration_type,
            'status'        => $request->status,
            'reason'        => $request->reason,
            'approved_by'   => $request->approved_by,
            'approval_date' => $request->approval_date,
        ]);
        return redirect()->route('superadmin.employee.employeeleave')->with('success', 'Leave request updated successfully.');
    }

    // Delete leave
     public function destroyEmployeeLeave($id)
    {
        $leave = EmployeeLeave::findOrFail($id);
        $leave->delete();

        if (request()->ajax() || request()->wantsJson()) {
            return response()->json(['success' => true, 'message' => 'Leave request deleted successfully.']);
        }
        return redirect()->route('superadmin.employee.employeeleave')->with('success', 'Leave request deleted successfully.');
    }
    
    public function getEmployeeClAvailable(Request $request, $id)
    {
        $asOf = $request->input('as_of');
        $date = $asOf ? \Carbon\Carbon::parse($asOf . '-01') : now();
        $employee = \App\Models\EmployeeDetails::findOrFail($id);
    
        $year = $date->year;
        $month = $date->month;
    
        // Used CL in year
        $usedClYear = \App\Models\EmployeeLeave::where('employee_id', $id)
            ->where('leave_type', 'CL')
            ->where('status', 'Approved')
            ->whereYear('leave_from', $year)
            ->sum('number_of_days');
    
        // Used CL in month
        $usedClMonth = \App\Models\EmployeeLeave::where('employee_id', $id)
            ->where('leave_type', 'CL')
            ->where('status', 'Approved')
            ->whereYear('leave_from', $year)
            ->whereMonth('leave_from', $month)
            ->sum('number_of_days');
    
        // 12 per year, 1 per month
        $clAvailableYear = max(0, 12 - $usedClYear);
        $clAvailableMonth = max(0, 1 - $usedClMonth);
    
        return response()->json([
            'cl_available_year' => $clAvailableYear,
            'cl_available_month' => $clAvailableMonth,
            'as_of' => $date->format('F Y'),
        ]);
    }

    // AJAX: Get Employee Name & Department
    public function getEmployeeNameDept($id)
    {
        $employee = EmployeeDetails::find($id);
        if ($employee) {
            return response()->json([
                'name'       => $employee->name,
                'department' => $employee->department,
            ]);
        }
        return response()->json(['name' => '', 'department' => ''], 404);
    }

    /**
     * Get Monthly Stats for Payroll (Days Worked, Leaves)
     */
    public function getMonthlyStats($employeeId, $monthYear)
    {
        // monthYear format: YYYY-MM
        $startOfMonth = Carbon::parse($monthYear . '-01')->startOfMonth();
        $endOfMonth = Carbon::parse($monthYear . '-01')->endOfMonth();

        // Get the employee to resolve the correct IDs
        // $employeeId here is the auto-increment id from employees table
        $employee = EmployeeDetails::findOrFail($employeeId);

        // 1. Get Days Worked from Attendance
        // Note: EmployeeAttendance stores employee_id as string code (e.g., "1002")
        // Use distinct date to avoid double counting if multiple entries exist per day
        $daysWorked = EmployeeAttendance::where('employee_id', $employee->employee_id)
            ->whereBetween('date', [$startOfMonth, $endOfMonth])
            ->distinct('date')
            ->count('date');

        // 2. Get Paid Leaves Taken (Approved Only)
        // Note: EmployeeLeave stores employee_id as auto-increment id
        // Exclude LOP (Loss of Pay) or Unpaid Leave
        $leaves = EmployeeLeave::where('employee_id', $employee->id)
            ->where('status', 'Approved')
            ->where(function ($q) {
                $q->where('leave_type', 'not like', '%LOP%')
                  ->where('leave_type', 'not like', '%Unpaid%')
                  ->where('leave_type', 'not like', '%Loss%');
            })
            ->where(function ($query) use ($startOfMonth, $endOfMonth) {
                // Overlap logic
                $query->whereDate('leave_from', '<=', $endOfMonth)
                      ->whereDate('leave_to', '>=', $startOfMonth);
            })
            ->get();

        $paidLeaveDays = 0;
        foreach ($leaves as $leave) {
            $leaveStart = Carbon::parse($leave->leave_from);
            $leaveEnd = Carbon::parse($leave->leave_to);
            
            // Intersection with current month
            $start = $leaveStart->max($startOfMonth);
            $end = $leaveEnd->min($endOfMonth);
            
            if ($start <= $end) {
                $days = $start->diffInDays($end) + 1;
                
                // Adjust for half-day
                if ($leave->duration_type === 'Half Day') {
                    // Start and End usually same for half day, so days=1. We take 0.5
                    // If range is > 1 day but marked as Half Day, it's ambiguous. 
                    // Usually Half Day applies to single date leaves.
                    // If multiple days, we assume 0.5 per day? No, usually 'Half Day' is a status for the request.
                    // Let's assume if duration_type is Half Day, the total EFFECTIVE leave is 0.5 * days? 
                    // Or usually Half Day implies total duration is 0.5.
                    // Let's assume it means 0.5 total for the record if strictly single day.
                    // Safest: Use number_of_days if it's fully inside the month?
                    // Let's stick to: if duration_type == Half Day, subtract 0.5 from the day count.
                    // Wait, if I take 1 day leave as Half Day, diffInDays+1 = 1. Effective = 0.5.
                    $days = 0.5 * $days; 
                }
                
                $paidLeaveDays += $days;
            }
        }

        return response()->json([
            'days_worked' => $daysWorked,
            'leave_given' => $paidLeaveDays,
        ]);
    }

    /**
     * Generate PDF Payslip
     */
    public function generatePayslip($id)
    {
        $salary = EmployeeSalaryDetail::with('employee')->findOrFail($id);

        $pdf = Pdf::loadView('superadmin.employee.payslip_pdf', compact('salary'));

        $filename = 'Payslip_' . ($salary->employee->employee_id ?? 'EMP') . '_' . $salary->month_year . '.pdf';

        return $pdf->download($filename);
    }

    /**
     * Bulk download payslips as ZIP file.
     */
    public function bulkDownloadPayslips(Request $request)
    {
        $request->validate([
            'ids' => 'required|array|min:1',
            'ids.*' => 'exists:employee_salary_details,id',
        ]);

        $ids = $request->ids;
        $salaries = EmployeeSalaryDetail::with('employee')->whereIn('id', $ids)->get();

        if ($salaries->isEmpty()) {
            return back()->with('error', 'No payslips found for the selected records.');
        }

        // Create temporary directory for PDFs
        $tempDir = storage_path('app/temp_payslips_' . time());
        if (!file_exists($tempDir)) {
            mkdir($tempDir, 0755, true);
        }

        $pdfFiles = [];

        try {
            // Generate PDF for each salary record
            foreach ($salaries as $salary) {
                $pdf = Pdf::loadView('superadmin.employee.payslip_pdf', compact('salary'));

                $empId = $salary->employee->employee_id ?? 'EMP';
                $empName = preg_replace('/[^A-Za-z0-9_\-]/', '_', $salary->employee->name ?? 'Unknown');
                $monthYear = str_replace('-', '_', $salary->month_year);

                $filename = "Payslip_{$empId}_{$empName}_{$monthYear}.pdf";
                $filepath = $tempDir . '/' . $filename;

                $pdf->save($filepath);
                $pdfFiles[] = $filepath;
            }

            // Create ZIP file
            $zipFilename = 'Payslips_' . date('Y-m-d_His') . '.zip';
            $zipPath = storage_path('app/' . $zipFilename);

            $zip = new \ZipArchive();
            if ($zip->open($zipPath, \ZipArchive::CREATE | \ZipArchive::OVERWRITE) !== true) {
                throw new \Exception('Cannot create ZIP file.');
            }

            foreach ($pdfFiles as $file) {
                $zip->addFile($file, basename($file));
            }

            $zip->close();

            // Clean up temporary PDF files
            foreach ($pdfFiles as $file) {
                if (file_exists($file)) {
                    unlink($file);
                }
            }
            rmdir($tempDir);

            // Return ZIP for download
            return response()->download($zipPath, $zipFilename)->deleteFileAfterSend(true);

        } catch (\Exception $e) {
            // Clean up on error
            foreach ($pdfFiles as $file) {
                if (file_exists($file)) {
                    unlink($file);
                }
            }
            if (file_exists($tempDir)) {
                rmdir($tempDir);
            }

            Log::error('Bulk payslip download error: ' . $e->getMessage());
            return back()->with('error', 'Failed to generate payslips: ' . $e->getMessage());
        }
    }

    /**
     * Update Leave Status (Quick Approve/Reject)
     */
    public function updateLeaveStatus(Request $request, $id)
    {
        $request->validate([
            'status' => 'required|in:Approved,Rejected,Pending',
        ]);

        $leave = EmployeeLeave::findOrFail($id);
        
        $updateData = [
            'status' => $request->status,
        ];

        if ($request->status == 'Approved') {
            $updateData['approved_by'] = auth()->user()->name ?? 'SuperAdmin';
            $updateData['approval_date'] = now();
        } elseif ($request->status == 'Rejected') {
             // Optional: Clear approval fields if rejected? Or keep track?
             // Usually just status update for rejection is enough.
             $updateData['approval_date'] = now(); 
        }

        $leave->update($updateData);

        return response()->json(['success' => true, 'message' => 'Leave status updated successfully.']);
    }

    public function importEmployees(Request $request)
    {
        $request->validate([
            'excel_file' => 'required|file|mimes:xlsx'
        ]);

        $file = $request->file('excel_file');
        Log::info('Bulk employee import started.', [
            'user_id' => auth()->id(),
            'filename' => $file->getClientOriginalName()
        ]);


        try 
        {
            $spreadsheet = IOFactory::load($file->getRealPath());
            $worksheet = $spreadsheet->getActiveSheet();
            $rows = $worksheet->toArray(null, true, true, true);

            // Headers as values
            $headerRow = array_map('strtolower', array_map('trim', array_values($rows[1])));
            
            $expectedHeaders = [
                'employee_id',
                'name',
                'gender',
                'department',
                'designation',
                'dob',
                'doj',
                'mobile',
                'aadhar_number',
                'category',
                'status'
            ];

            if ($headerRow !== $expectedHeaders) {
                Log::error('Bulk employee import: header mismatch.', [
                    'provided' => $headerRow,
                    'expected' => $expectedHeaders
                ]);
                return back()->with('error', 'Invalid template format. Please use the provided sample template.');
            }

            $successCount = 0;
            $errorCount = 0;
            $errorRows = [];
            DB::beginTransaction();
            
            //dd(array_slice($rows, 1));

            foreach (array_slice($rows, 1) as $index => $row) 
            {
                //if ($index == 0) continue; // Skip header, commented as the new excel gives correct row
                
                $rowData = array_combine($expectedHeaders, array_map('trim', array_values($row)));
                
                //dd($rowData);
                
                // Fix dates
                foreach (['dob', 'doj'] as $dateField) {
                    if (!empty($rowData[$dateField])) {
                        try {
                            // Try to convert Excel/US/ISO/other date formats to Y-m-d
                            $rowData[$dateField] = Carbon::parse($rowData[$dateField])->format('Y-m-d');
                        } catch (\Exception $e) {
                            // If parsing fails, keep as is, validation will catch it
                        }
                    } else {
                        $rowData[$dateField] = null; // Optional: set empty dates to null
                    }
                }
                $validator = Validator::make($rowData, [
                    'employee_id'    => 'required|unique:employees,employee_id',
                    'name'           => 'required',
                    'gender'         => 'required|in:Male,Female,Other',
                    'department'     => 'nullable',
                    'designation'    => 'nullable',
                    'dob'            => 'nullable|date',
                    'doj'            => 'nullable|date',
                    'mobile'         => 'nullable|digits:10',
                    'aadhar_number'  => 'nullable|digits:12',
                    'category'       => 'nullable|in:e,E,m,M,o,O',
                    'status'         => 'required|in:ACTIVE,INACTIVE,active,inactive'
                ]);

                if ($validator->fails()) {
                    $errorCount++;
                    $errorRows[] = "Row " . ($index + 1) . ": " . implode('; ', $validator->errors()->all());
                    Log::warning('Bulk employee import: validation failed.', [
                        'row_number' => $index + 1,
                        'errors' => $validator->errors()->all(),
                        'data' => $rowData
                    ]);
                    continue;
                }

                try {
                    // Adjust status to uppercase
                    $rowData['status'] = strtoupper($rowData['status']);
                
                    // Check if employee_id already exists
                    if (\App\Models\EmployeeDetails::where('employee_id', $rowData['employee_id'])->exists()) {
                        $errorCount++;
                        $errorRows[] = "Row " . ($index + 2) . ": Employee ID '{$rowData['employee_id']}' already exists.";
                        Log::warning('Bulk employee import: duplicate employee_id skipped.', [
                            'row_number' => $index + 2,
                            'employee_id' => $rowData['employee_id'],
                        ]);
                        continue;
                    }
                
                    \App\Models\EmployeeDetails::create($rowData);
                    $successCount++;
                } catch (\Exception $e) {
                    $errorCount++;
                    $errorRows[] = "Row " . ($index + 2) . ": " . $e->getMessage();
                    Log::error('Bulk employee import: DB insert failed.', [
                        'row_number' => $index + 2,
                        'exception' => $e->getMessage(),
                        'data' => $rowData
                    ]);
                }
            }
            DB::commit();

            $message = "{$successCount} employees imported.";
            if ($errorCount > 0) {
                $message .= " {$errorCount} errors. See below.";
                Log::warning('Bulk employee import completed with errors.', ['errors' => $errorRows]);
                return back()->with('success', $message)->with('errorRows', $errorRows);
            }
            return back()->with('success', $message);
        } catch (\Exception $e) {
            Log::critical('Bulk employee import failed.', ['exception' => $e->getMessage()]);
            return back()->with('error', 'Failed to import: ' . $e->getMessage());
        }
    }

    public function deleteProfilePicture($id)
    {
        try {
            $employee = EmployeeDetails::findOrFail($id);
    
            if ($employee->profile_picture && Storage::disk('public')->exists($employee->profile_picture)) {
                Storage::disk('public')->delete($employee->profile_picture);
                $employee->profile_picture = null;
                $employee->save();
            }
    
            return response()->json([
                'success' => true,
                'message' => 'Profile picture deleted successfully.',
            ]);
        } 
        catch (\Exception $e)
        {
            return response()->json([
                'success' => false,
                'message' => 'Error deleting profile picture.',
            ], 500);
        }
    }

    /**
     * Generate payroll for all employees for a specific month.
     *
     * Enhanced calculation logic:
     * - Leave days exclude Sundays and public holidays
     * - Incentive hours = OT hours + Sunday work hours + Holiday work hours
     * - ESI, PF, PT percentages from salary structure
     * - All payroll starts as 'pending' approval status
     */
    public function generatePayroll(Request $request)
    {
        $request->validate([
            'month_year' => 'required|date_format:Y-m',
        ]);

        $monthYear = $request->month_year;
        $startDate = Carbon::parse($monthYear . '-01');
        $endDate = $startDate->copy()->endOfMonth();
        $daysInMonth = $startDate->daysInMonth;

        // Get filter parameters
        $departmentFilter = $request->input('department');
        $employeeFilter = $request->input('employee_id');

        // Build employee query with filters
        $employeeQuery = EmployeeDetails::query();
        if ($departmentFilter) {
            $employeeQuery->where('department', $departmentFilter);
        }
        if ($employeeFilter) {
            if (is_array($employeeFilter)) {
                $employeeQuery->whereIn('id', $employeeFilter);
            } else {
                $employeeQuery->where('id', $employeeFilter);
            }
        }
        $employees = $employeeQuery->get();

        // Get all holidays for the month
        $holidays = Holiday::whereBetween('date', [$startDate->toDateString(), $endDate->toDateString()])
            ->pluck('date')
            ->map(fn($d) => Carbon::parse($d)->toDateString())
            ->toArray();

        // Get all Sundays in the month
        $sundays = [];
        for ($d = $startDate->copy(); $d->lte($endDate); $d->addDay()) {
            if ($d->dayOfWeek === 0) {
                $sundays[] = $d->toDateString();
            }
        }

        $generated = 0;
        $skipped = 0;
        $errors = [];

        // Standard shift hours (for Sunday/holiday OT calculation)
        $standardShiftHours = 9;

        foreach ($employees as $employee) {
            try {
                // Get salary structure for employee
                $structure = EmployeeSalaryStructure::where('employee_id', $employee->id)->first();

                if (!$structure) {
                    $skipped++;
                    $errors[] = "Employee {$employee->name} ({$employee->employee_id}): No salary structure defined";
                    continue;
                }

                // Get attendance data for the month
                $attendanceRecords = EmployeeAttendance::where('employee_id', $employee->employee_id)
                    ->whereBetween('date', [$startDate->toDateString(), $endDate->toDateString()])
                    ->get();

                // Count present days (has check_in and check_out)
                $daysWorked = $attendanceRecords->filter(function ($record) {
                    return $record->check_in && $record->check_out;
                })->count();

                // Get regular OT hours from attendance
                $regularOtHours = $attendanceRecords->sum('ot_hours') ?? 0;

                // Calculate Sunday OT hours (full shift for worked Sundays)
                $sundayOtHours = 0;
                $sundaysWorked = 0;
                foreach ($sundays as $sunday) {
                    $workedOnSunday = $attendanceRecords->first(function ($record) use ($sunday) {
                        return Carbon::parse($record->date)->toDateString() === $sunday
                               && $record->check_in && $record->check_out;
                    });
                    if ($workedOnSunday) {
                        $sundaysWorked++;
                        // Full shift counts as OT for Sunday work
                        $sundayOtHours += $standardShiftHours;
                    }
                }

                // Calculate Holiday OT hours (full shift for worked holidays)
                $holidayOtHours = 0;
                $holidaysWorked = 0;
                foreach ($holidays as $holiday) {
                    $workedOnHoliday = $attendanceRecords->first(function ($record) use ($holiday) {
                        return Carbon::parse($record->date)->toDateString() === $holiday
                               && $record->check_in && $record->check_out;
                    });
                    if ($workedOnHoliday) {
                        $holidaysWorked++;
                        // Full shift counts as OT for holiday work
                        $holidayOtHours += $standardShiftHours;
                    }
                }

                // Total incentive hours = regular OT + Sunday OT + Holiday OT
                $totalIncentiveHours = $regularOtHours + $sundayOtHours + $holidayOtHours;

                // Get approved leaves for the month
                $approvedLeaves = EmployeeLeave::where('employee_id', $employee->id)
                    ->where('status', 'Approved')
                    ->where(function ($q) use ($startDate, $endDate) {
                        $q->whereBetween('leave_from', [$startDate, $endDate])
                          ->orWhereBetween('leave_to', [$startDate, $endDate])
                          ->orWhere(function ($q2) use ($startDate, $endDate) {
                              $q2->where('leave_from', '<', $startDate)
                                 ->where('leave_to', '>', $endDate);
                          });
                    })
                    ->get();

                // Calculate paid leave days (excluding Sundays and holidays from leave count)
                $paidLeaveDays = 0;
                $lopDays = 0;

                foreach ($approvedLeaves as $leave) {
                    $leaveStart = Carbon::parse($leave->leave_from);
                    $leaveEnd = Carbon::parse($leave->leave_to);

                    // Clamp to current month
                    if ($leaveStart->lt($startDate)) $leaveStart = $startDate->copy();
                    if ($leaveEnd->gt($endDate)) $leaveEnd = $endDate->copy();

                    // Count leave days excluding Sundays and holidays
                    $effectiveLeaveDays = 0;
                    for ($d = $leaveStart->copy(); $d->lte($leaveEnd); $d->addDay()) {
                        $dateStr = $d->toDateString();
                        // Skip Sundays and holidays - they don't count as leave days
                        if (!in_array($dateStr, $sundays) && !in_array($dateStr, $holidays)) {
                            $effectiveLeaveDays++;
                        }
                    }

                    // Adjust for half-day leave
                    if ($leave->duration_type === 'Half Day') {
                        $effectiveLeaveDays = $effectiveLeaveDays * 0.5;
                    }

                    if (strtoupper($leave->leave_type) === 'LOP') {
                        $lopDays += $effectiveLeaveDays;
                    } else {
                        $paidLeaveDays += $effectiveLeaveDays;
                    }
                }

                // Count Sundays where employee didn't work (weekly off - paid)
                $paidSundays = count($sundays) - $sundaysWorked;

                // Count holidays where employee didn't work (paid)
                $paidHolidays = count($holidays) - $holidaysWorked;

                // Total payable days = days worked + paid leaves + paid sundays + paid holidays
                $totalPayableDays = $daysWorked + $paidLeaveDays + $paidSundays + $paidHolidays;

                // Salary calculations from structure
                $basicDa = $structure->basic_da ?? 0;
                $hra = $structure->hra ?? 0;
                $conveyance = $structure->conveyance ?? 0;
                $washingAllowance = $structure->washing_allowance ?? 0;
                $fixedSalary = $structure->fixed_salary ?? ($basicDa + $hra + $conveyance + $washingAllowance);

                // Drawn salary = (fixed_salary / days_in_month) * total_payable_days
                $drawnSalary = ($daysInMonth > 0) ? round(($fixedSalary / $daysInMonth) * $totalPayableDays, 2) : 0;

                // Incentive calculation (Total incentive hours * incentive per hour from salary structure)
                $incentiveRate = $structure->incentive_per_hour ?? 0;
                $incentiveAmount = round($totalIncentiveHours * $incentiveRate, 2);

                // Attendance bonus
                $attendanceBonus = 0;

                // Gross salary
                $grossSalary = round($drawnSalary + $incentiveAmount + $attendanceBonus, 2);

                // Deductions from salary structure
                $pfPercentage = $structure->pf_percentage ?? 12;
                $esiPercentage = $structure->esi_percentage ?? 0.75;
                $ptPercentage = $structure->pt_percentage ?? 0;

                // PF is calculated on Basic + DA
                $pfAmount = round($basicDa * ($pfPercentage / 100), 2);
                // ESI is calculated on Gross if gross < 21000
                $esiAmount = ($grossSalary < 21000) ? round($grossSalary * ($esiPercentage / 100), 2) : 0;
                // PT calculated on gross (or fixed amount based on state rules)
                $ptAmount = round($grossSalary * ($ptPercentage / 100), 2);

                // Net salary
                $netSalary = round($grossSalary - $pfAmount - $esiAmount - $ptAmount, 2);

                // Save or update salary detail with pending approval status
                EmployeeSalaryDetail::updateOrCreate(
                    [
                        'employee_id' => $employee->id,
                        'month_year' => $monthYear,
                    ],
                    [
                        'basic_da' => $basicDa,
                        'hra' => $hra,
                        'conveyance' => $conveyance,
                        'washing_allowance' => $washingAllowance,
                        'fixed_salary' => $fixedSalary,
                        'no_of_days_worked' => $daysWorked,
                        'leave_given' => $paidLeaveDays,
                        'sundays_count' => $paidSundays,
                        'holidays_count' => $paidHolidays,
                        'total_days_for_salary' => $daysInMonth,
                        'drawn_salary' => $drawnSalary,
                        'incentive_hrs' => $regularOtHours,
                        'sunday_ot_hours' => $sundayOtHours,
                        'holiday_ot_hours' => $holidayOtHours,
                        'incentive_rate' => $incentiveRate,
                        'incentive_amount' => $incentiveAmount,
                        'attendance_bonus' => $attendanceBonus,
                        'gross_salary' => $grossSalary,
                        'esi' => $esiAmount,
                        'pf' => $pfAmount,
                        'pt' => $ptAmount,
                        'advance_deduction' => 0,
                        'net_salary' => $netSalary,
                        'payment_mode' => 'Bank',
                        'approval_status' => EmployeeSalaryDetail::STATUS_PENDING,
                        'approved_by' => null,
                        'approved_at' => null,
                        'rejection_reason' => null,
                    ]
                );

                // Auto-accrue 1 CL if employee worked 20+ days in the month
                if ($daysWorked >= 20) {
                    $year = $startDate->year;
                    $month = $startDate->month;
                    $accrued = EmployeeLeaveBalance::accrueLeave($employee->id, $year, $month, 1.0, 'CL');
                    if ($accrued) {
                        Log::info("Accrued 1 CL for employee {$employee->employee_id} for {$monthYear} (worked {$daysWorked} days)");
                    }
                }

                $generated++;

            } catch (\Exception $e) {
                $skipped++;
                $errors[] = "Employee {$employee->name}: " . $e->getMessage();
                Log::error("Payroll generation error for {$employee->employee_id}: " . $e->getMessage());
            }
        }

        if (!empty($errors)) {
            Log::warning('Payroll generation warnings:', $errors);
        }

        $message = "Payroll generated for $generated employee(s) with pending approval.";
        if ($skipped > 0) {
            $message .= " Skipped $skipped employee(s).";
        }

        if ($request->ajax()) {
            return response()->json([
                'success' => true,
                'message' => $message,
                'generated' => $generated,
                'skipped' => $skipped,
                'errors' => $errors,
            ]);
        }

        return redirect()->back()->with('success', $message);
    }

    /**
     * Get employees by department (for AJAX dropdown filtering).
     */
    public function getEmployeesByDepartment(Request $request)
    {
        $query = EmployeeDetails::select('id', 'employee_id', 'name', 'department');

        if ($request->department) {
            $query->where('department', $request->department);
        }

        $employees = $query->orderBy('name')->get();

        return response()->json([
            'success' => true,
            'data' => $employees,
        ]);
    }

    /**
     * Get payroll data with filters.
     */
    public function getPayrollData(Request $request)
    {
        $query = EmployeeSalaryDetail::with('employee');

        // Apply filters
        if ($request->month_year) {
            $query->where('month_year', $request->month_year);
        }
        if ($request->department) {
            $query->whereHas('employee', function ($q) use ($request) {
                $q->where('department', $request->department);
            });
        }
        if ($request->employee_id) {
            // Handle both single ID and array of IDs
            $employeeIds = $request->employee_id;
            if (is_array($employeeIds)) {
                $query->whereIn('employee_id', $employeeIds);
            } else {
                $query->where('employee_id', $employeeIds);
            }
        }

        $salaryDetails = $query->orderByDesc('id')->get();

        return response()->json([
            'success' => true,
            'data' => $salaryDetails,
        ]);
    }

    /**
     * Approve a single payroll record.
     */
    public function approvePayroll(Request $request, $id)
    {
        try {
            $salary = EmployeeSalaryDetail::findOrFail($id);

            if ($salary->isApproved()) {
                return response()->json([
                    'success' => false,
                    'message' => 'This payroll record is already approved.',
                ], 400);
            }

            $salary->update([
                'approval_status' => EmployeeSalaryDetail::STATUS_APPROVED,
                'approved_by' => auth()->id(),
                'approved_at' => now(),
                'rejection_reason' => null,
            ]);

            return response()->json([
                'success' => true,
                'message' => 'Payroll approved successfully.',
            ]);
        } catch (\Exception $e) {
            Log::error("Payroll approval error: " . $e->getMessage());
            return response()->json([
                'success' => false,
                'message' => 'Failed to approve payroll.',
            ], 500);
        }
    }

    /**
     * Reject a single payroll record with reason.
     */
    public function rejectPayroll(Request $request, $id)
    {
        $request->validate([
            'reason' => 'required|string|max:500',
        ]);

        try {
            $salary = EmployeeSalaryDetail::findOrFail($id);

            if ($salary->isApproved()) {
                return response()->json([
                    'success' => false,
                    'message' => 'Cannot reject an already approved payroll.',
                ], 400);
            }

            $salary->update([
                'approval_status' => EmployeeSalaryDetail::STATUS_REJECTED,
                'approved_by' => auth()->id(),
                'approved_at' => now(),
                'rejection_reason' => $request->reason,
            ]);

            return response()->json([
                'success' => true,
                'message' => 'Payroll rejected successfully.',
            ]);
        } catch (\Exception $e) {
            Log::error("Payroll rejection error: " . $e->getMessage());
            return response()->json([
                'success' => false,
                'message' => 'Failed to reject payroll.',
            ], 500);
        }
    }

    /**
     * Bulk approve multiple payroll records.
     */
    public function bulkApprovePayroll(Request $request)
    {
        $request->validate([
            'ids' => 'required|array|min:1',
            'ids.*' => 'exists:employee_salary_details,id',
        ]);

        try {
            $approved = 0;
            $skipped = 0;

            foreach ($request->ids as $id) {
                $salary = EmployeeSalaryDetail::find($id);
                if ($salary && !$salary->isApproved()) {
                    $salary->update([
                        'approval_status' => EmployeeSalaryDetail::STATUS_APPROVED,
                        'approved_by' => auth()->id(),
                        'approved_at' => now(),
                        'rejection_reason' => null,
                    ]);
                    $approved++;
                } else {
                    $skipped++;
                }
            }

            return response()->json([
                'success' => true,
                'message' => "Approved $approved payroll record(s)." . ($skipped > 0 ? " Skipped $skipped already approved." : ''),
                'approved' => $approved,
                'skipped' => $skipped,
            ]);
        } catch (\Exception $e) {
            Log::error("Bulk payroll approval error: " . $e->getMessage());
            return response()->json([
                'success' => false,
                'message' => 'Failed to bulk approve payroll.',
            ], 500);
        }
    }

    /**
     * Bulk reject multiple payroll records.
     */
    public function bulkRejectPayroll(Request $request)
    {
        $request->validate([
            'ids' => 'required|array|min:1',
            'ids.*' => 'exists:employee_salary_details,id',
            'reason' => 'required|string|max:500',
        ]);

        try {
            $rejected = 0;
            $skipped = 0;

            foreach ($request->ids as $id) {
                $salary = EmployeeSalaryDetail::find($id);
                if ($salary && !$salary->isApproved()) {
                    $salary->update([
                        'approval_status' => EmployeeSalaryDetail::STATUS_REJECTED,
                        'approved_by' => auth()->id(),
                        'approved_at' => now(),
                        'rejection_reason' => $request->reason,
                    ]);
                    $rejected++;
                } else {
                    $skipped++;
                }
            }

            return response()->json([
                'success' => true,
                'message' => "Rejected $rejected payroll record(s)." . ($skipped > 0 ? " Skipped $skipped already approved." : ''),
                'rejected' => $rejected,
                'skipped' => $skipped,
            ]);
        } catch (\Exception $e) {
            Log::error("Bulk payroll rejection error: " . $e->getMessage());
            return response()->json([
                'success' => false,
                'message' => 'Failed to bulk reject payroll.',
            ], 500);
        }
    }

}
