<?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 PhpOffice\PhpSpreadsheet\Spreadsheet;
use Illuminate\Support\Carbon;

use Illuminate\Validation\Rule;
use Maatwebsite\Excel\Facades\Excel;


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()
    {
        $employees = EmployeeDetails::all();
        $leaves = EmployeeLeave::orderBy('id', 'desc')->with('employee')->get();
    
        // Pre-calculate CL balances for each employee for this year/month
        $clBalances = [];
        $currentYear = now()->year;
        $currentMonth = now()->month;
        foreach ($employees as $emp) {
            $usedCl = EmployeeLeave::where('employee_id', $emp->id)
                ->where('leave_type', 'CL')
                ->where('status', 'Approved')
                ->whereYear('leave_from', $currentYear)
                ->sum('number_of_days');
    
            $monthlyUsedCl = EmployeeLeave::where('employee_id', $emp->id)
                ->where('leave_type', 'CL')
                ->where('status', 'Approved')
                ->whereYear('leave_from', $currentYear)
                ->whereMonth('leave_from', $currentMonth)
                ->sum('number_of_days');
    
            $clBalances[$emp->id] = [
                'year' => $currentYear,
                'month' => $currentMonth,
                'cl_available_year' => max(0, 12 - $usedCl),
                'cl_available_month' => max(0, 1 - $monthlyUsedCl),
            ];
        }
        return view('superadmin.employee.employeeleave', compact('employees', 'leaves', 'clBalances'));
    }

    // Store leave request
    public function storeEmployeeLeave(Request $request)
    {
        $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',
            'reason'        => 'required',
        ]);

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

        $status = 'Pending'; // Default status for new requests

        // --- Begin CL validation & auto-LOP logic ---
        if ($request->leave_type === 'CL') {
            $leaveFrom = Carbon::parse($request->leave_from);
            $year = $leaveFrom->year;
            $month = $leaveFrom->month;

            // CL accrued up to requested month (e.g., March = 3 CL)
            // Assuming 1 CL per month accrual
            $accruedCL = $month; 
            $takenCL = $employee->leaves()
                ->where('leave_type', 'CL')
                ->whereYear('leave_from', $year)
                ->whereIn('status', ['Approved', 'Pending'])
                ->sum('number_of_days');
            $availableCL = max(0, $accruedCL - $takenCL);

            $appliedCL = $request->number_of_days;

            if ($appliedCL > $availableCL) {
                // Split leave: part CL, part LOP
                $clPortion = $availableCL;
                $lopPortion = $appliedCL - $availableCL;

                DB::beginTransaction();
                try {
                    if ($clPortion > 0) {
                        EmployeeLeave::create([
                            'employee_id'   => $employee->id,
                            'employee_name' => $employee->name,
                            'department'    => $employee->department,
                            'leave_type'    => 'CL',
                            'leave_from'    => $request->leave_from,
                            'leave_to'      => $request->leave_to, 
                            'number_of_days'=> $clPortion,
                            'duration_type' => $request->duration_type,
                            'status'        => $status,
                            'reason'        => $request->reason,
                            'requested_on'  => now(),
                        ]);
                    }
                    // Remaining as LOP
                    EmployeeLeave::create([
                        'employee_id'   => $employee->id,
                        'employee_name' => $employee->name,
                        'department'    => $employee->department,
                        'leave_type'    => 'LOP',
                        'leave_from'    => $request->leave_from,
                        'leave_to'      => $request->leave_to,
                        'number_of_days'=> $lopPortion,
                        'duration_type' => $request->duration_type,
                        'status'        => $status,
                        'reason'        => $request->reason . ' (Excess CL auto-converted to LOP)',
                        'requested_on'  => now(),
                    ]);
                    DB::commit();
                    return back()->with('success', "{$clPortion} CL and {$lopPortion} LOP leave applied. Excess CL automatically converted to LOP.");
                } catch (\Exception $e) {
                    DB::rollBack();
                    return back()->with('error', 'Failed to apply leave: ' . $e->getMessage());
                }
            }
            // If enough CL, continue to normal creation below...
        }
        
        // --- Normal leave creation (CL/SL/PL/LOP/Other) ---
        EmployeeLeave::create([
            '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'        => $status,
            'reason'        => $request->reason,
            'requested_on'  => now(),
        ]);
        return back()->with('success', 'Leave request added successfully.');
    }
    
    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,
            '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();

        // 1. Get Days Worked from Attendance
        // Assuming 'present' status or just existence of record implies worked?
        // Checking EmployeeAttendance model: has 'date', 'check_in', 'check_out'.
        // We count records where date is in range.
        // TODO: Filter by status if there is a status column? Model doesn't show one, so existence = present.
        $daysWorked = EmployeeAttendance::where('employee_id', $employeeId)
            ->whereBetween('date', [$startOfMonth, $endOfMonth])
            ->count();

        // 2. Get Leaves Taken (Approved Only)
        // Need to handle multi-day leaves spanning across months? 
        // For simplicity, we might just sum days overlapping this month.
        
        $leaves = EmployeeLeave::where('employee_id', $employeeId)
            ->where('status', 'Approved')
            ->where(function ($query) use ($startOfMonth, $endOfMonth) {
                // Overlap logic: (StartA <= EndB) and (EndA >= StartB)
                $query->whereDate('leave_from', '<=', $endOfMonth)
                      ->whereDate('leave_to', '>=', $startOfMonth);
            })
            ->get();

        $leaveDays = 0;
        foreach ($leaves as $leave) {
            // Calculate overlap days
            $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) {
                // +1 because diffInDays is exclusive of end date usually, check Carbon
                $leaveDays += $start->diffInDays($end) + 1;
            }
        }
        
        // Handle half-days if duration_type is tracked? 
        // Logic above counts full days. If duration_type 'Half Day', we might need adjustment.
        // Let's check duration_type field in EmployeeLeave.
        // It exists. If any leave in the collection is "Half Day", we might need to adjust logic, 
        // but typically "number_of_days" stores the total (e.g. 0.5). 
        // BUT "number_of_days" is total content. intersection logic might be safer if we assume "Full Day".
        // Better Approach: Use 'number_of_days' if it falls completely within month. 
        // If it spans, we need to calculate pro-rata.
        // Given complexity, let's stick to intersection days count for now.
        // Refinement: If duration_type is 'Half Day', the range is usually 1 day, so intersection is 1 day, but effective is 0.5.
        // Let's refine:
        $refinedLeaveDays = 0;
        foreach ($leaves as $leave) {
            $leaveStart = Carbon::parse($leave->leave_from);
            $leaveEnd = Carbon::parse($leave->leave_to);
            $start = $leaveStart->max($startOfMonth);
            $end = $leaveEnd->min($endOfMonth);
            
            if ($start <= $end) {
                $days = $start->diffInDays($end) + 1;
                if ($leave->duration_type === 'Half Day') {
                    $days = 0.5; // Half day is always single date usually
                }
                $refinedLeaveDays += $days;
            }
        }

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

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

}
