<?php

namespace App\Http\Controllers;

use App\Models\LeaveRequest;
use App\Models\LeavePackage;
use App\Models\TierPackage;
use App\Models\RosterAssign;
use App\Models\EmpPersonalDetails;
use App\Models\EmployeeAttendance;
use App\Models\EmpCompanyDetails;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\Validator;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Http\Request;
use App\Http\Controllers\Traits\EmailTrait;
use Illuminate\Support\Facades\Log;
use App\Models\BaseModel;

class LeaveRequestController extends Controller
{
    use EmailTrait;

    public function __construct()
    {
        $this->middleware('Permissions:Leave Request Maintain')->only(['create', 'edit']);
    }

    public function index(Request $request)
    {
        $query = LeaveRequest::query();
        $query = $this->applyCustomerWorkspaceFilter($query);
        $query->latest('id');
        // Apply search filter across leave package title and employee names
        if ($request->filled('search')) {
            $searchTerm = $request->search;
            $query->where(function ($q) use ($searchTerm) {
                // Search in leave package title
                $q->whereHas('leavepackage', function ($subquery) use ($searchTerm) {
                    $subquery->where('title', 'like', '%' . $searchTerm . '%');
                })
                // Apply name search using helper
                ->orWhere(function ($nameQuery) use ($searchTerm) {
                    $this->applyNameSearch($nameQuery, $searchTerm, 'empDetails');
                });
            });
        }
        if ($request->filled('filter')) {
            $query =  $this->filter(json_decode($request->filter, true), $query);
        }
        $query->with('LeavePackage')->with('EmpPersonalDetails');
       
        return $this->withCount($query, 'Get Leave Request List Successfully');
    }

 


    public function create()
    {
        $leave_packages =  LeavePackage::orderBy('id', 'DESC')->get();

        $emp_details =  EmpCompanyDetails::with('EmpPersonalDetails')->where('del', 0)->where('compeleted', 1)->orderBy('id', 'DESC')->get();
        return view('LeaveRequest.create', compact('leave_packages', 'emp_details'));
    }


    public function store(Request $request)
    {
        $userTable = $this->getUserTable();
        $auth_id = 0;
        $workspace_id = 0;
        if ($userTable === "customer") {
            $auth_id = Auth::user()->id;
            $workspace_id = Auth::user()->current_workspace_id;
        }
        if ($userTable === "emp") {
            $auth_id = auth()->user()->customer_id;
            $workspace_id = auth()->user()->workspace_id;
        }
        $validator = Validator::make($request->all(), [
            'employee' => 'required',
            'leave_package' => 'required',
            'start_date' => 'required|date',
            'end_date' => 'required|date|after_or_equal:start_date',
            'reason' => 'required',
            'full_day' => 'nullable',
            'start_time' => 'nullable',
            'end_time' => 'nullable'
        ], [
            // Custom validation messages
            'start_date.required' => 'Start Date is required.',
            'end_date.required' => 'End Date is required.',
            'end_date.after_or_equal' => 'The End Date must be a date after or equal to the Start Date.',
        ]);

        if ($validator->fails()) {
            return $this->handleValidationFailure($validator);
        } else {

            $validatedData = $validator->validated();
            // Check for overlapping leaves with proper date range logic
            $query = LeaveRequest::where('employee_id', $validatedData['employee'])
                ->where('status', '!=', '2'); // Exclude rejected leaves (status 2)
            
            // Apply customer and workspace filters
            $query = $this->applyCustomerWorkspaceFilter($query);
            
            $overlappingLeaves = $query->where(function ($query) use ($validatedData) {
                    $query->where(function ($q) use ($validatedData) {
                        // New leave starts during existing leave
                        $q->where('from', '<=', $validatedData['start_date'])
                          ->where('to', '>=', $validatedData['start_date']);
                    })->orWhere(function ($q) use ($validatedData) {
                        // New leave ends during existing leave
                        $q->where('from', '<=', $validatedData['end_date'])
                          ->where('to', '>=', $validatedData['end_date']);
                    })->orWhere(function ($q) use ($validatedData) {
                        // New leave completely contains existing leave
                        $q->where('from', '>=', $validatedData['start_date'])
                          ->where('to', '<=', $validatedData['end_date']);
                    });
                })
                ->exists();

            if ($overlappingLeaves) {
                return $this->message('You already have leave scheduled for this date', 400);
            }

            $userTable = $this->getUserTable();
            $auth_id = 0;
            $workspace_id = 0;
            if ($userTable === "customer") {
                $auth_id = Auth::user()->id;
                $workspace_id = Auth::user()->current_workspace_id;
            }
            if ($userTable === "emp") {
                $auth_id = auth()->user()->customer_id;
                $workspace_id = auth()->user()->workspace_id;
            }

            $insertedRecord = LeaveRequest::create([
                'employee_id' => $validatedData['employee'],
                'leave_package_id' => $validatedData['leave_package'],
                'from' => $validatedData['start_date'],
                'to' => $validatedData['end_date'],
                'reason' => $validatedData['reason'],
                'full_day' => $validatedData['full_day'],
                'start_time' => $validatedData['start_time'],
                'end_time' => $validatedData['end_time'],
                'applied_by' => Auth::user()->id,
                'customer_id' => $auth_id,
                'workspace_id' => $workspace_id,
            ]);

            $id = $insertedRecord->id;
            $data = LeaveRequest::find($id);
            return $this->success($data, 'Leave Request Saved Successfully');
        }
    }
    ////////////////leave Request Api///////////////
    public function applie_leave_request(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'employee_id'       => 'required',
            'leave_package_id'  => 'required',
            'from'              => 'required|date',
            'mail_to'           => 'required',
            'mail_cc'           => 'nullable|array',
            'to'                => 'required|date|after_or_equal:from',
            'reason'            => 'required',
            'start_time'        => 'nullable',
            'end_time'          => 'nullable'
        ], [
            // Here you specify your custom messages
            'from.required' => 'Start Date is required.',  // Custom message for `from` field
            'to.required'   => 'End Date is required.',    // Custom message for `to` field
            'to.after_or_equal' => 'The End Date must be a date after or equal to the Start Date.',  // Custom message for `to` after_or_equal validation
        ]);

        if ($validator->fails()) {
            return response()->json([
                'message' => $validator->errors()->first()
            ], 422);
        }
        $validatedData = $validator->validated();

        // Check for overlapping leaves with proper date range logic 
        $query = LeaveRequest::where('employee_id', $validatedData['employee_id'])
            ->where('status', '!=', '2'); // Exclude rejected leaves (status 2)
        
        // Apply customer and workspace filters
        $query = $this->applyCustomerWorkspaceFilter($query);
        
        $overlappingLeaves = $query->where(function ($query) use ($validatedData) {
                $query->where(function ($q) use ($validatedData) {
                    // New leave starts during existing leave
                    $q->where('from', '<=', $validatedData['from'])
                      ->where('to', '>=', $validatedData['from']);
                })->orWhere(function ($q) use ($validatedData) {
                    // New leave ends during existing leave
                    $q->where('from', '<=', $validatedData['to'])
                      ->where('to', '>=', $validatedData['to']);
                })->orWhere(function ($q) use ($validatedData) {
                    // New leave completely contains existing leave
                    $q->where('from', '>=', $validatedData['from'])
                      ->where('to', '<=', $validatedData['to']);
                });
            })
            ->exists();

        if ($overlappingLeaves) {
            return $this->message('You already have leave scheduled for this date', 400);
        }

        // Calculate number of leave days
        $fromDate = BaseModel::safeCarbonParse($validatedData['from'], 'LeaveRequestController.applie_leave_request.from');
        $toDate = BaseModel::safeCarbonParse($validatedData['to'], 'LeaveRequestController.applie_leave_request.to');

        if (!($fromDate instanceof Carbon) || !($toDate instanceof Carbon)) {
            Log::warning('Invalid leave date range provided', [
                'from' => $validatedData['from'],
                'to' => $validatedData['to'],
                'employee_id' => $validatedData['employee_id'] ?? null,
            ]);

            return response()->json([
                'message' => 'Invalid date range supplied. Please check the start and End Dates.',
            ], 422);
        }

        $numberOfDays = $fromDate->diffInDays($toDate) + 1; // Include start and End Dates
        // Fetching details
        $leavePackage = LeavePackage::find($validatedData['leave_package_id']);
        $empDetail = EmpPersonalDetails::where('emp_id', $validatedData['employee_id'])->first();
        // $mailTo = EmpCompanyDetails::find($validatedData['mail_to'])->employee_email;
        $employee = EmpCompanyDetails::with('empTeamsMembers.empTeamsList')
            ->where('id', $validatedData['employee_id'])
            ->first();
        $teamTitle = null;
        if ($employee) {
            foreach ($employee->empTeamsMembers as $teamMember) {
                if (isset($teamMember->empTeamsList)) {
                    $teamTitle = $teamMember->empTeamsList->title;
                    break;
                }
            }
        }
        $ids = $this->getCustomerAndWorkspaceIds();
        $validatedData['customer_id'] = $ids['customer_id'];
        $validatedData['workspace_id'] = $ids['workspace_id'];
        // Store leave request first
        $validatedData['number_of_leaves'] = $numberOfDays;
        $insertedRecord = LeaveRequest::create($validatedData);
        $data = LeaveRequest::find($insertedRecord->id);
        
        // Get employee email
        $employeeCompanyDetails = EmpCompanyDetails::find($validatedData['employee_id']);
        $employeeEmail = $employeeCompanyDetails->employee_email ?? null;
        // Get mail_to email directly from request
        $mailToEmployee = EmpCompanyDetails::find($validatedData['mail_to']);
        $mailToEmail = $mailToEmployee->employee_email ?? null;
        // Get mail_cc emails directly from request
        $ccEmails = EmpCompanyDetails::whereIn('id', $validatedData['mail_cc'])->pluck('employee_email')->toArray();
        // Prepare email data
        $employeeName = trim($empDetail->first_name . ' ' . ($empDetail->middle_name ? $empDetail->middle_name . ' ' : '') . $empDetail->last_name);
        $subject = 'Leave Request - ' . $leavePackage->title;
        
        // Create notification for employee about leave request submission
        $this->save_notifications(
            'Leave Request Submitted',
            'Your leave request for ' . $leavePackage->title . ' with Start Date ' . date('M d, Y', strtotime($validatedData['from'])) . ' and End Date ' . date('M d, Y', strtotime($validatedData['to'])) . ' has been submitted successfully.',
            config('constants.employee_types.employee'),
            Auth::user()->id,
            config('constants.employee_types.employee'),
            $validatedData['employee_id'],
            'leave_request_submitted',
            $ids['customer_id'],
            $ids['workspace_id']
        );
        
        // Create notification for supervisor/manager (mail_to)
        $this->save_notifications(
            'Leave Request Approval Required',
            'Employee ' . $employeeName . ' has submitted a leave request for ' . $leavePackage->title . ' with Start Date ' . 
            date('M d, Y', strtotime($validatedData['from'])) . ' and End Date ' . date('M d, Y', strtotime($validatedData['to'])) . 
            ' (Duration: ' . $numberOfDays . ' days). Reason: ' . $validatedData['reason'] . '. Please review and approve.',
            config('constants.employee_types.employee'),
            Auth::user()->id,
            config('constants.employee_types.employee'),
            $validatedData['mail_to'],
            'leave_request_approval_required',
            $ids['customer_id'],
            $ids['workspace_id']
        );
        
        // Create notifications for CC employees (multiple employees)
        if (!empty($validatedData['mail_cc']) && is_array($validatedData['mail_cc'])) {
            foreach ($validatedData['mail_cc'] as $ccEmployeeId) {
                $this->save_notifications(
                    'Leave Request Notification',
                    'Employee ' . $employeeName . ' has submitted a leave request for ' . $leavePackage->title . ' with Start Date ' . 
                    date('M d, Y', strtotime($validatedData['from'])) . ' and End Date ' . date('M d, Y', strtotime($validatedData['to'])) . 
                    ' (Duration: ' . $numberOfDays . ' days). Reason: ' . $validatedData['reason'] . '. This is for your information.',
                    config('constants.employee_types.employee'),
                    Auth::user()->id,
                    config('constants.employee_types.employee'),
                    $ccEmployeeId,
                    'leave_request_notification',
                    $ids['customer_id'],
                    $ids['workspace_id']
                );
            }
        }
        
        // Create notification for customer about leave request submission (after employee notifications)
        $this->save_notifications(
            'Leave Request Approval - Customer Notification',
            'Employee ' . $employeeName . ' has submitted a leave request for ' . $leavePackage->title . ' with Start Date ' . 
            date('M d, Y', strtotime($validatedData['from'])) . ' and End Date ' . date('M d, Y', strtotime($validatedData['to'])) . 
            ' (Duration: ' . $numberOfDays . ' days). Reason: ' . $validatedData['reason'],
            config('constants.employee_types.employee'),
            Auth::user()->id,
            config('constants.employee_types.customer'),
            $ids['customer_id'], // Send to customer
            'leave_request_customer_notification',
            $ids['customer_id'],
            $ids['workspace_id']
        );
        
        $baseEmailData = [
            'subject' => $subject,
            'employeeName' => $employeeName,
            'leavePackageName' => $leavePackage->title,
            'startDate' => date('F d, Y', strtotime($validatedData['from'])),
            'endDate' => date('F d, Y', strtotime($validatedData['to'])),
            'numberOfDays' => $numberOfDays,
            'teamName' => $teamTitle,
            'reason' => $validatedData['reason'],
            'leaveRequestId' => $insertedRecord->id
        ];
        $emailStatus = [];
        // Send email to employee
        if ($employeeEmail) {
            $employeeEmailParams = [
                'to' => $employeeEmail,
                'subject' => $subject . ' - Confirmation',
                'msg' => view('Emails.leave_request', array_merge($baseEmailData, [
                    'emailType' => 'employee'
                ]))->render()
            ];
            $employeeEmailSent = $this->SendInstantEmail($employeeEmailParams);
            $emailStatus['employee'] = $employeeEmailSent ? 'sent' : 'failed';
        } else {
            $emailStatus['employee'] = 'no_email';
        }
        // Send notification email to supervisor/manager (mail_to)
        if ($mailToEmail && filter_var($mailToEmail, FILTER_VALIDATE_EMAIL)) {
            $supervisorEmailParams = [
                'to' => $mailToEmail,
                'subject' => $subject . ' - Approval Required',
                'msg' => view('Emails.leave_request', array_merge($baseEmailData, [
                    'emailType' => 'supervisor'
                ]))->render()
            ];
            $supervisorEmailSent = $this->SendInstantEmail($supervisorEmailParams);
            $emailStatus['supervisor'] = $supervisorEmailSent ? 'sent' : 'failed';
        } else {
            $emailStatus['supervisor'] = $mailToEmail ? 'invalid_email' : 'no_email';
        }
        // Send notification emails to CC recipients
        $emailStatus['cc_emails'] = [];
        if (!empty($ccEmails) && is_array($ccEmails)) {
            foreach ($ccEmails as $index => $ccEmail) {
                if ($ccEmail && filter_var($ccEmail, FILTER_VALIDATE_EMAIL)) {
                    $ccEmailParams = [
                        'to' => $ccEmail,
                        'subject' => $subject . ' - Notification',
                        'msg' => view('Emails.leave_request', array_merge($baseEmailData, [
                            'emailType' => 'cc'
                        ]))->render()
                    ];
                    $ccEmailSent = $this->SendInstantEmail($ccEmailParams);
                    $emailStatus['cc_emails'][$index] = $ccEmailSent ? 'sent' : 'failed';
                } else {
                    $emailStatus['cc_emails'][$index] = 'invalid_email';
                }
            }
        }
        // Determine overall email status
        $totalSent = 0;
        $totalAttempted = 0;
        $totalInvalid = 0;
        if ($emailStatus['employee'] === 'sent') $totalSent++;
        if ($emailStatus['employee'] !== 'no_email') $totalAttempted++;
        if ($emailStatus['supervisor'] === 'sent') $totalSent++;
        if ($emailStatus['supervisor'] === 'invalid_email') $totalInvalid++;
        if ($emailStatus['supervisor'] !== 'no_email') $totalAttempted++;
        foreach ($emailStatus['cc_emails'] as $ccStatus) {
            if ($ccStatus === 'sent') $totalSent++;
            if ($ccStatus === 'invalid_email') $totalInvalid++;
            $totalAttempted++;
        }
        if ($totalAttempted === 0) {
            $overallEmailStatus = 'No email addresses found';
        } elseif ($totalInvalid > 0 && $totalSent === 0) {
            $overallEmailStatus = 'All email addresses are invalid';
        } elseif ($totalSent === $totalAttempted) {
            $overallEmailStatus = 'All emails sent successfully';
        } elseif ($totalSent > 0) {
            $overallEmailStatus = 'Some emails sent successfully';
        } else {
            $overallEmailStatus = 'All emails failed to send';
        }
        return response()->json([
            'message' => 'Leave Saved Successfully',
            'email_status' => $overallEmailStatus,
            'email_details' => $emailStatus,
            'data' => $data
        ], 200);
    }

    public function get_leave_request(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'employee_id' => 'required'
        ]);
        if ($validator->fails()) {
            $error = $validator->errors()->first();
            return $this->message($error, 422);
        } 
        $validatedData = $validator->validated();
        $data = LeaveRequest::where('employee_id', $validatedData['employee_id'])
            ->with(['empCompanyDetails.empPersonalDetails'])
            ->with('LeavePackage')
            ->get();
        if (!$data->isNotEmpty()) {
            return $this->message('Leave not found for the given employee ', 404);
        } 
        return $this->success($data, 'Leave Retrieved Successfully');
    }

    public function edit_leave_request(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'leave_id' => 'required'
        ]);

        if ($validator->fails()) {
            $error = $validator->errors()->first();
            return response()->json([
                'message' => $error
            ], 422);
        } else {
            $validatedData = $validator->validated();
            $data = LeaveRequest::where('id', $validatedData['leave_id'])->first();

            if ($data !== null) {
                return response()->json([
                    'message' => 'Leave Retrieved Successfully',
                    'data'   => $data
                ], 200);
            } else {
                return response()->json([
                    'message' => 'Leave not found',
                ], 404);
            }
        }
    }

    public function update_leave_request(Request $request, $id)
    {
        $validator = Validator::make($request->all(), [
            'employee_id' => 'required',
            'leave_package_id' => 'required',
            'from' => 'required|date',
            'to' => 'required|date|after_or_equal:from',
            'reason' => 'required'
        ], [
            // Custom validation messages
            'from.required' => 'Start Date is required.',
            'to.required' => 'End Date is required.',
            'to.after_or_equal' => 'The End Date must be a date after or equal to the Start Date.',
        ]);

        if ($validator->fails()) {
            $error = $validator->errors()->first();
            return response()->json([
                'message' => $error
            ], 422);
        } else {
            $validatedData = $validator->validated();

            $leaveRequest = LeaveRequest::find($id);

            if ($leaveRequest) {
                $leaveRequest->update($validatedData);

                $data = LeaveRequest::find($id);
                return response()->json([
                    'message' => 'Leave Updated Successfully',
                    'data'   => $data
                ], 200);
            } else {
                return response()->json([
                    'message' => 'Leave not found',
                ], 404);
            }
        }
    }

    public function employee_leave_pkg(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'employee_id' => 'required|integer',
        ]);

        if ($validator->fails()) {
            return response()->json([
                'message' => $validator->errors()->first()
            ], 422);
        }

        $validatedData = $validator->validated();
        $employeeId = $validatedData['employee_id'];

        $empDetails = EmpCompanyDetails::where('id', $employeeId)->first();

        if (!$empDetails || !$empDetails->accessRole) {
            return response()->json(['error' => 'Employee or employee role not found'], 404);
        }

        $accessRoleId = $empDetails->accessRole->id;

        $leavePackages = LeavePackage::whereRaw("FIND_IN_SET(?, employee_roles)", [$accessRoleId])
            ->where('customer_id', auth()->user()->customer_id)
            ->where('workspace_id', auth()->user()->workspace_id)
            ->get();

        if ($leavePackages->isEmpty()) {
            return response()->json(['message' => 'No leave packages found for this employee role'], 404);
        }

        $leavePackagesWithRemaining = $leavePackages->map(function ($package) use ($employeeId) {
            $totalAvailableLeaves = $package->leave_number;

            $approvedLeaves = LeaveRequest::where('employee_id', $employeeId)
                ->where('leave_package_id', $package->id)
                ->where('status', 1)
                ->get();

            $totalApprovedDays = 0;
            foreach ($approvedLeaves as $leave) {
                $fromDate = BaseModel::safeCarbonParse($leave->from, 'LeaveRequestController.employee_leave_pkg.from');
                $toDate = BaseModel::safeCarbonParse($leave->to, 'LeaveRequestController.employee_leave_pkg.to');

                if (!($fromDate instanceof Carbon) || !($toDate instanceof Carbon)) {
                    Log::warning('Skipping leave calculation due to unparsable dates', [
                        'leave_id' => $leave->id ?? null,
                        'from' => $leave->from,
                        'to' => $leave->to,
                        'context' => 'employee_leave_pkg',
                    ]);
                    continue;
                }

                $totalApprovedDays += $fromDate->diffInDays($toDate) + 1;
            }

            $remainingLeave = $totalAvailableLeaves - $totalApprovedDays;

            $package->remaining_leave = $remainingLeave;

            return $package;
        });

        return response()->json([
            'message' => 'Employee leave packages with remaining leave retrieved successfully',
            'data' => $leavePackagesWithRemaining,
        ], 200);
    }

    ////////////////leave Request Api///////////////




    public function edit($id)
    {
        $leave_packages =  LeavePackage::orderBy('id', 'DESC')->get();
        $emp_details =  EmpCompanyDetails::with('EmpPersonalDetails')->where('del', 0)->where('compeleted', 1)->orderBy('id', 'DESC')->get();
        $leaverquest = LeaveRequest::where('id', $id)->first();
        return view('LeaveRequest.edit', compact('leave_packages', 'emp_details', 'leaverquest'));
    }


    public function edit_leave_for_roster(Request $request)
    {

        $leave_rquest    =   LeaveRequest::where('id', $request->id)->first();

        $data['leave_rquest'] = $leave_rquest;

        return response()->json([
            'message' => 'Get Leave Successfully',
            'data'   => $data
        ], 200);
    }

    public function update(Request $request)
    {
        $id = $request->id;

        $validator = Validator::make($request->all(), [
            'employee' => 'required',
            'leave_package' => 'required',
            'start_date' => 'required|date',
            'end_date' => 'required|date|after_or_equal:start_date',
            'reason' => 'required',
            'full_day' => 'nullable',
            'start_time' => 'nullable',
            'end_time' => 'nullable'
        ], [
            // Custom validation messages
            'start_date.required' => 'Start Date is required.',
            'end_date.required' => 'End Date is required.',
            'end_date.after_or_equal' => 'The End Date must be a date after or equal to the Start Date.',
        ]);

        if ($validator->fails()) {
            return $this->handleValidationFailure($validator);
        } else {

            $leaveRequest = LeaveRequest::find($id);
            if (!$leaveRequest) {
                return $this->message('The Leave Request with the specified ID does not exist.', 404);
            }
            $userTable = $this->getUserTable();
            if ($userTable == "customer" && ($leaveRequest->workspace_id != auth()->user()->current_workspace_id || $leaveRequest->customer_id != auth()->user()->id)) {
                return $this->message('You do not have access to this Leave Request', 403);
            }
            if ($userTable == "emp" && ($leaveRequest->customer_id != auth()->user()->customer_id || $leaveRequest->workspace_id != auth()->user()->workspace_id)) {
                return $this->message('You do not have access to this Leave Request', 403);
            }

            $auth_id = 0;
            $workspace_id = 0;
            if ($userTable === "customer") {
                $auth_id = Auth::user()->id;
                $workspace_id = Auth::user()->current_workspace_id;
            }
            if ($userTable === "emp") {
                $auth_id = auth()->user()->customer_id;
                $workspace_id = auth()->user()->workspace_id;
            }
            $user = Auth::user();
            $validatedData = $validator->validated();
            $validatedData['applied_by'] = $auth_id;
            $leaveRequest->update([
                'employee_id' => $validatedData['employee'],
                'leave_package_id' => $validatedData['leave_package'],
                'from' => $validatedData['start_date'],
                'to' => $validatedData['end_date'],
                'reason' => $validatedData['reason'],
                'full_day' => $validatedData['full_day'] ?? 0,
                'start_time' => $validatedData['start_time'],
                'end_time' => $validatedData['end_time'],
                'customer_id' => $auth_id,
                'workspace_id' => $workspace_id,
            ]);
            $data = LeaveRequest::where('id', $id)->get();
            return $this->success($data, 'Leave Request Saved Successfully');
        }
    }

    public function destroy($id)
    {

        $LeaveRequest = LeaveRequest::find($id);
        if (!$LeaveRequest) {
            return $this->message('The Leave Request  with the specified ID does not exist.', 404);
        }
        $userTable = $this->getUserTable();
        if ($userTable == "customer" && ($LeaveRequest->workspace_id != auth()->user()->current_workspace_id || $LeaveRequest->customer_id != auth()->user()->id)) {
            return $this->message('You do not have access to this Leave Request', 403);
        }
        if ($userTable == "emp" && ($LeaveRequest->customer_id != auth()->user()->customer_id || $LeaveRequest->workspace_id != auth()->user()->workspace_id)) {
            return $this->message('You do not have access to this Leave Request', 403);
        }
        
        // Get leave package details for notification before deletion
        $leave_pkg = LeavePackage::where('id', $LeaveRequest->leave_package_id)->first();
        
        // Create notification for deleted leave request
        $this->save_notifications(
            'Leave Request Deleted',
            'Your leave request for ' . ($leave_pkg ? $leave_pkg->title : 'Leave') . ' with Start Date ' . date('M d, Y', strtotime($LeaveRequest->from)) . ' and End Date ' . date('M d, Y', strtotime($LeaveRequest->to)) . ' has been deleted.',
            Auth::user()->id,
            $LeaveRequest->employee_id,
            'leave_request_deleted',
            $LeaveRequest->customer_id,
            $LeaveRequest->workspace_id
        );
        
        LeaveRequest::where('id', $id)->delete();
        return response()->json([
            'message' => 'Leave Request Deleted Successfully'
        ], 200);
    }

    public function updateStatus(Request $request, $id)
    {
        $validator = Validator::make($request->all(), [
            'status' => 'required|integer',
        ]);
        if ($validator->fails()) {
            return $this->handleValidationFailure($validator);
        }

        $leaveRequest = LeaveRequest::find($id);
        if (!$leaveRequest) {
            return $this->message('The Leave Request  with the specified ID does not exist.', 404);
        }
        $userTable = $this->getUserTable();
        if ($userTable == "customer" && ($leaveRequest->workspace_id != auth()->user()->current_workspace_id || $leaveRequest->customer_id != auth()->user()->id)) {
            return $this->message('You do not have access to this Leave Request', 403);
        }
        if ($userTable == "emp" && ($leaveRequest->customer_id != auth()->user()->customer_id || $leaveRequest->workspace_id != auth()->user()->workspace_id)) {
            return $this->message('You do not have access to this Leave Request', 403);
        }

        // Update leave request status
        $leaveRequest->status = $request->input('status');
        $leaveRequest->approved_by = Auth::user()->id;
        $leaveRequest->save();

        // Get leave package and employee details
        $leave_pkg = LeavePackage::where('id', $leaveRequest->leave_package_id)->first();
        $emp_company = EmpCompanyDetails::where('id', $leaveRequest->employee_id)->first();
        $emp_detail = EmpPersonalDetails::where('emp_id', $leaveRequest->employee_id)->first();
        
        // Get customer_id and workspace_id for notifications
        $customer_id = null;
        $workspace_id = null;
        if ($userTable === "customer") {
            $customer_id = Auth::user()->id;
            $workspace_id = Auth::user()->current_workspace_id;
        } elseif ($userTable === "emp") {
            $customer_id = auth()->user()->customer_id;
            $workspace_id = auth()->user()->workspace_id;
        }

        // If status is approved (1) or rejected (2), send email notification
        if ($leaveRequest->status == 1 || $leaveRequest->status == 2) {
            // Set status configuration based on approval/rejection
            $status_config = [];
            if ($leaveRequest->status == 1) {
                $status_text = 'Approved';
                $status_config = [
                    'status_text' => 'Approved',
                    'color' => '#047857', // Green
                    'background' => 'linear-gradient(135deg, #ECFDF5 0%, #D1FAE5 100%)',
                    'icon' => '✅',
                    'message' => 'Your leave request has been approved.',
                ];
                
                // Create notification for customer about approved leave
                $this->save_notifications(
                    'Leave Request Approved - Customer Notification',
                    'Your Leave request for ' . $leave_pkg->title . ' with Start Date ' . date('M d, Y', strtotime($leaveRequest->from)) . ' and End Date ' . date('M d, Y', strtotime($leaveRequest->to)) . ' has been approved.', 
                    sender_type: config('constants.employee_types.customer'),
                    sender_id: Auth::user()->id,
                    receiver_type: config('constants.employee_types.employee'),
                    receiver_id: $leaveRequest->employee_id, // Send to customer
                    type: 'leave_request_approved',
                    customer_id: $customer_id,
                    workspace_id: $workspace_id
                );
            } else {
                $status_text = 'Rejected';
                $status_config = [
                    'status_text' => 'Rejected',
                    'color' => '#DC2626', // Red
                    'background' => 'linear-gradient(135deg, #FEF2F2 0%, #FEE2E2 100%)',
                    'icon' => '❌',
                    'message' => 'Your leave request has been rejected.',
                ];
                
                // Create notification for customer about rejected leave
                $this->save_notifications(
                    'Leave Request Rejected - Customer Notification',
                    'Your Leave request for ' . $leave_pkg->title . ' with Start Date ' . date('M d, Y', strtotime($leaveRequest->from)) . ' and End Date ' . date('M d, Y', strtotime($leaveRequest->to)) . ' has been rejected.',
                    config('constants.employee_types.customer'),
                    Auth::user()->id,
                    config('constants.employee_types.employee'),
                    $leaveRequest->employee_id, // Send to customer
                    'leave_request_rejected',
                    $customer_id,
                    $workspace_id
                );
            }

            // Format date range
            $fromDateFormatted = null;
            $toDateFormatted = null;

            $fromParsed = BaseModel::safeCarbonParse($leaveRequest->from, 'LeaveRequestController.updateStatus.from');
            $toParsed = BaseModel::safeCarbonParse($leaveRequest->to, 'LeaveRequestController.updateStatus.to');

            if ($fromParsed instanceof Carbon) {
                $fromDateFormatted = $fromParsed->format('d M, Y');
            }

            if ($toParsed instanceof Carbon) {
                $toDateFormatted = $toParsed->format('d M, Y');
            }

            if ($fromDateFormatted && $toDateFormatted) {
                $date_range = 'Start Date: ' . $fromDateFormatted . ', End Date: ' . $toDateFormatted;
            } else {
                Log::warning('Using raw dates for leave status email due to parsing failure', [
                    'leave_id' => $leaveRequest->id,
                    'from' => $leaveRequest->from,
                    'to' => $leaveRequest->to,
                ]);
                $date_range = 'Start Date: ' . $leaveRequest->from . ', End Date: ' . $leaveRequest->to;
            }

            // Email parameters
            $params = [
                'subject' => 'Leave Request ' . $status_text . ' | ' . env("APP_NAME"),
                'to' => $emp_company->employee_email,
                'msg' => view('Emails.leave-notification', [
                    'name' => $emp_detail->first_name . ' ' . $emp_detail->middle_name . ' ' . $emp_detail->last_name,
                    'subject' => 'Leave Request ' . $status_text,
                    'status_config' => $status_config,
                    'ticket_details' => [
                        'Leave Type' => $leave_pkg->title,
                        'Date Range' => $date_range,
                        'Status' => $status_text,
                        'Reason' => $leaveRequest->reason
                    ],
                    'message' => 'This is to inform you that your leave request has been ' . strtolower($status_text) . '.'
                ])->render()
            ];

            // Send email
            $emailSent = $this->SendInstantEmail($params);

            if ($emailSent) {
                return $this->message('Status updated successfully and notification email has been sent.', 200);
            } else {
                return $this->message('Status updated successfully but failed to send notification email.', 200);
            }
        } else {
            return $this->message('Status updated successfully', 200);
        }
    }

    public function leavereason($id)
    {
        $data = LeaveRequest::find($id);
        if (!$data) {
            return $this->message('The Leave Request with the specified ID does not exist.', 404);
        }
        $userTable = $this->getUserTable();
        if ($userTable == "customer" && ($data->workspace_id != auth()->user()->current_workspace_id || $data->customer_id != auth()->user()->id)) {
            return $this->message('You do not have access to this Leave Request', 403);
        }
        if ($userTable == "emp" && ($data->customer_id != auth()->user()->customer_id || $data->workspace_id != auth()->user()->workspace_id)) {
            return $this->message('You do not have access to this Leave Request', 403);
        }
        return response()->json([
            'data'   => $data
        ], 200);
    }

    public function remainingleave(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'employee_id' => 'required|integer',
            'leave_package_id' => 'required|integer',
        ]);

        if ($validator->fails()) {
            $error = $validator->errors()->first();
            return response()->json([
                'message' => $error

            ], 422);
        } else {
            $leave_request = LeaveRequest::where('leave_package_id', $request->leave_package_id)->where('employee_id', $request->employee_id)->first();

            if (!$leave_request) {
                return response()->json([
                    'message' => 'Leave request not found',
                ], 404);
            }

            $from = new \DateTime($leave_request->from);
            $to = new \DateTime($leave_request->to);

            $no_of_leaves = $from->diff($to)->days + 1;


            $_leave_pkg = LeavePackage::where('id', $request->leave_package_id)->first();

            if (!$_leave_pkg) {
                return response()->json([
                    'message' => 'Leave package not found',
                ], 404);
            }

            $total_pkg_no = $_leave_pkg->leave_number;

            $remaining_leave = $total_pkg_no - $no_of_leaves;
            return response()->json([
                'message' => 'Remaining Leave',
                'data'   => $remaining_leave
            ], 200);
        }
    }
    public function leave_view(Request $request)
    {
        $ids = $this->getCustomerAndWorkspaceIds();
        $id = $request->id;
        $leave_request = LeaveRequest::where('id', $id)->where('customer_id', $ids['customer_id'])->where('workspace_id', $ids['workspace_id'])->first();
        $leave_pgk = LeavePackage::where('id', $leave_request->leave_package_id)->where('customer_id', $ids['customer_id'])->where('workspace_id', $ids['workspace_id'])->first();
        $emp_details = EmpCompanyDetails::with('EmpPersonalDetails')->where('id', $leave_request->employee_id)->where('customer_id', $ids['customer_id'])->where('workspace_id', $ids['workspace_id'])->first();
        $all_pkgs = LeavePackage::whereRaw("FIND_IN_SET(?, employee_roles)", [$emp_details->accessRole->id])->where('customer_id', $ids['customer_id'])->where('workspace_id', $ids['workspace_id'])->get();

        $emp_role = LeaveRequest::where('leave_requests.id', $id)
            ->join('emp_company_details', 'leave_requests.employee_id', '=', 'emp_company_details.id')
            ->join('roles', 'emp_company_details.access_role', '=', 'roles.code')
            ->select('roles.*')
            ->first();

        $currentDate = Carbon::today();
        $today_on_leave_employes = DB::table('leave_requests')
            ->where('leave_requests.status', 1)
            ->where('leave_requests.customer_id', $ids['customer_id'])
            ->where('leave_requests.workspace_id', $ids['workspace_id'])
            ->whereDate('leave_requests.created_at', $currentDate)
            ->join('emp_company_details', 'leave_requests.employee_id', '=', 'emp_company_details.id')
            ->where('emp_company_details.customer_id', $ids['customer_id'])
            ->where('emp_company_details.workspace_id', $ids['workspace_id'])
            ->join('emp_personal_details', 'leave_requests.employee_id', '=', 'emp_personal_details.emp_id')
            ->join('roles', 'emp_company_details.access_role', '=', 'roles.code')
            ->select('roles.title', 'emp_personal_details.*')
            ->get();

        $data = [
            'leave_request'             => $leave_request,
            'leave_package'             => $leave_pgk,
            'employee_details'          => $emp_details,
            'all_packages_for_role'     => $all_pkgs,
            'employee_role'             => $emp_role,
            'today_on_leave_employees'  => $today_on_leave_employes,
        ];

        return response()->json([
            'success' => true,
            'data'    => $data,
        ], 200);
    }
    public function view($id)
    {
        try {
            $LeaveRequest = LeaveRequest::find($id);
            if (!$LeaveRequest) {
                return $this->message('The Leave Request with the specified ID does not exist.', 404);
            }
            $userTable = $this->getUserTable();
            if ($userTable == "customer" && ($LeaveRequest->workspace_id != auth()->user()->current_workspace_id || $LeaveRequest->customer_id != auth()->user()->id)) {
                return $this->message('You do not have access ', 403);
            }
            if ($userTable == "emp" && ($LeaveRequest->customer_id != auth()->user()->customer_id || $LeaveRequest->workspace_id != auth()->user()->workspace_id)) {
                return $this->message('You do not have access ', 403);
            }
            // Get leave request details
            $leave_request = LeaveRequest::where('id', $id)->first();

            // Get leave package with null safety
            $leave_pgk = null;
            if ($LeaveRequest->leave_package_id) {
                $leave_pgk = LeavePackage::where('id', $LeaveRequest->leave_package_id)->first();
            }
            // Get employee details with null safety
            $emp_details = null;
            if ($LeaveRequest->employee_id) {
                $emp_details = EmpCompanyDetails::withoutGlobalScope(\App\Scopes\NotDeletedScope::class)->with('EmpPersonalDetails')->where('id', $LeaveRequest->employee_id)->first();
            }
            // Get all packages with proper null checking
            $all_pkgs = null;
            
            if ($emp_details && $emp_details->accessRole && $emp_details->accessRole->id) {
                $all_pkgs = LeavePackage::whereRaw("FIND_IN_SET(?, employee_roles)", [$emp_details->accessRole->id])->where('customer_id', $LeaveRequest->customer_id)->where('workspace_id', $LeaveRequest->workspace_id)->get();
                $all_pkgs = $all_pkgs->map(function($package) use ($LeaveRequest) {
                    // Get all approved leave requests for this package
                    $approved_leaves = LeaveRequest::where('employee_id', $LeaveRequest->employee_id)
                        ->where('leave_package_id', $package->id)
                        ->where('status', 1) // Approved leaves only
                        ->get();
                    
                    // Calculate total leave days (not just count of requests)
                    $total_leave_days = 0;
                    foreach ($approved_leaves as $leave) {
                        $fromDate = BaseModel::safeCarbonParse($leave->from, 'leave-calculations.from');
                        $toDate = BaseModel::safeCarbonParse($leave->to, 'leave-calculations.to');
                        $total_leave_days += $fromDate->diffInDays($toDate) + 1; // +1 to include both start and End Dates
                    }
                    
                    $package->leave_count = $total_leave_days;
                    return $package;
                });
            } else {
                
                $all_pkgs = collect(); // Return empty collection instead of null
            }

            // Get employee role with null safety
            $emp_role = null;
            $emp_role = LeaveRequest::where('leave_requests.id', $id)
                ->join('emp_company_details', 'leave_requests.employee_id', '=', 'emp_company_details.id')
                ->join('roles', 'emp_company_details.access_role', '=', 'roles.code')
                ->select('roles.*')
                ->first();



            // Get today's employees on leave with error handling
            $today_on_leave_employes = collect();
            $currentDate = Carbon::today();
   


            // Calculate leave statistics for the employee
            $leave_calculations = null;
            if ($emp_details && $leave_pgk) {

                // Get total available leaves from the package
                $total_available_leaves = $leave_pgk->leave_number ?? 0;

                // Get all approved leave requests for this employee and package
                $approved_leaves = LeaveRequest::where('employee_id', $LeaveRequest->employee_id)
                    ->where('leave_package_id', $leave_pgk->id)
                    // ->where('status', 1) // 1 = approved status
                    ->get();

                                 // Calculate total used days
                 $total_used_days = 0;
                foreach ($approved_leaves as $leave) {
                    $fromDate = BaseModel::safeCarbonParse($leave->from, 'LeaveRequestController.leave_view.total_used.from');
                    $toDate = BaseModel::safeCarbonParse($leave->to, 'LeaveRequestController.leave_view.total_used.to');

                    if (!$fromDate instanceof Carbon || !$toDate instanceof Carbon) {
                        Log::warning('Skipping leave calculation due to unparsable dates', [
                            'from' => $leave->from,
                            'to' => $leave->to,
                            'context' => 'leave-calculations.total-used'
                        ]);
                        continue;
                    }

                    $total_used_days += $fromDate->diffInDays($toDate) + 1; // +1 to include both start and End Dates
                }

                // Calculate remaining leaves
                $remaining_leaves = $total_available_leaves - $total_used_days;

                $leave_calculations = [
                    'total_available_leaves' => $total_available_leaves,
                    'total_used_leaves' => $total_used_days,
                    'remaining_leaves' => $remaining_leaves,
                    'leave_package_title' => $leave_pgk->title ?? 'N/A'
                ];
            }

            $response_data = [
                'leave_request' => $leave_request,
                'emp_details' => $emp_details,
                'leave_pgk' => $leave_pgk,
                'all_pkgs' => $all_pkgs,
                'emp_role' => $emp_role,
                'leave_calculations' => $leave_calculations
            ];
            return response()->json($response_data);
        } catch (\Exception $e) {
            return $this->message('An error occurred while retrieving the leave request' . $e->getMessage(), 500);
        }
    }

    public function employee_pkg(Request $request, $id)
    {

        $emp_details = EmpCompanyDetails::where('id', $id)->first();
        $leave_packages    = LeavePackage::whereRaw("FIND_IN_SET(?, employee_roles)", [$emp_details->accessRole->id])->get();


        if (!$leave_packages) {
            return response()->json(['error' => 'Leave package not found'], 400);
        }


        return response()->json([
            'all_packages' => $leave_packages
        ], 200);
    }
    public function leave_count(Request $request, $id)
    {
        $leavePackage = LeavePackage::find($id);

        if (!$leavePackage) {
            return response()->json(['error' => 'Leave package not found'], 404);
        }

        $remaining_leave = $leavePackage->leave_number;
        $leave_title = $leavePackage->title;
        return response()->json([
            'remaining_leave' => $remaining_leave,
            'title_leave' => $leave_title
        ], 200);
    }

    public function leave_pkg(Request $request)
    {
        $leave = LeavePackage::all();

        if (!$leave) {
            return response()->json(['error' => 'Leave packages not found'], 404);
        }

        return response()->json([
            'message' => 'All Leave Packages',
            'data' => $leave
        ], 200);
    }



    public function leave_portal(Request $request)
    {
        $userTable = $this->getUserTable();
        $auth_id = 0;
        $workspace_id = 0;
        if ($userTable === "customer") {
            $auth_id = Auth::user()->id;
            $workspace_id = Auth::user()->current_workspace_id;
        }
        if ($userTable === "emp") {
            $auth_id = auth()->user()->customer_id;
            $workspace_id = auth()->user()->workspace_id;
        }
        $current_date = date('Y-m-d');
        // $all_emp = EmpCompanyDetails::where('status',1)->where('compeleted',1)->where('del',0)->count();
        $employee_ids = RosterAssign::join('emp_company_details', 'roster_assigns.assign_to', 'emp_company_details.id')
            ->where('emp_company_details.del', 0)->where('emp_company_details.status', 1)
            ->where('roster_assigns.schedule_date', $current_date)
            ->where('roster_assigns.customer_id', $auth_id) // Added filter
            ->where('roster_assigns.workspace_id', $workspace_id) // Added filter
            ->groupBy('roster_assigns.assign_to')
            ->pluck('roster_assigns.assign_to');
        $all_emp = count($employee_ids);
        $present_employee_ids = EmployeeAttendance::where('date', $current_date)
            ->wherein('employee_id', $employee_ids)
            ->where('customer_id', $auth_id) // Added filter
            ->where('workspace_id', $workspace_id) // Added filter
            ->groupBy('employee_id')
            ->pluck('employee_id')
            ->toArray();
        $present_employees = count($present_employee_ids);

        $leave_employee_ids = LeaveRequest::wherein('employee_id', $employee_ids)
            ->where('from', ">=", $current_date)
            ->where('status', 1)
            ->where('customer_id', $auth_id) // Added filter
            ->where('workspace_id', $workspace_id) // Added filter
            ->pluck('employee_id')
            ->toArray();
        $leave_employees = count($leave_employee_ids);
        return response()->json([
            'all_emp' => $all_emp,
            'leave_employees' => $leave_employees,
            'present_employees' => $present_employees,
        ]);
    }

    public function upcoming_leave(Request $request)
    {
        $userTable = $this->getUserTable();
        $query = LeaveRequest::query();
        if ($userTable === "customer") {
            $query->where('customer_id', Auth::user()->id)
                ->where('workspace_id', Auth::user()->current_workspace_id);
        }
        if ($userTable === "emp") {
            $query->where('customer_id', auth()->user()->customer_id) // Assuming customer_id links employee to projects
                ->where('workspace_id', auth()->user()->workspace_id);
        }
        if ($request->filled('filter')) {
            $query =  $this->filter(json_decode($request->filter, true), $query);
        }

        $query->with('LeavePackage')->with('EmpPersonalDetails');
        $query->orderBy('id', 'desc');
        if($request->filled('pagination') || !empty($request->filters)){
            $query_result = $this->searchFilterRecord($query, $request);
        }else{
            $query_result = $query->get();
        }
        
        $query_result;
        $i = 0;
        foreach ($query_result as $query_resul) {
            $from = BaseModel::safeCarbonParse($query_resul->from, 'LeaveRequestController.upcoming_leave.from');
            $to = BaseModel::safeCarbonParse($query_resul->to, 'LeaveRequestController.upcoming_leave.to');

            if (!($from instanceof Carbon) || !($to instanceof Carbon)) {
                Log::warning('Unable to compute upcoming leave duration due to unparsable dates', [
                    'leave_id' => $query_resul->id ?? null,
                    'from' => $query_resul->from,
                    'to' => $query_resul->to,
                ]);
                $days = 0;
            } else {
                $days = $to->diffInDays($from) + 1;
            }

            $query_result[$i]->total_day = $days;
            $i++;
        }
        $array_filter =  json_decode($request->filter, true);
        return $this->WithFilter($query_result, $array_filter, 'Get Upcoming Leave List Successfully');
    }


    public function approved_leave(Request $request)
    {
        $userTable = $this->getUserTable();
        $query = LeaveRequest::query();
        $query->where('status', '1');

        if ($userTable === "customer") {
            $query->where('customer_id', Auth::user()->id)
                ->where('workspace_id', Auth::user()->current_workspace_id);
            $customer_id = Auth::user()->id;
            $workspace_id = Auth::user()->current_workspace_id;
        }
        if ($userTable === "emp") {
            $query->where('customer_id', auth()->user()->customer_id)
                ->where('workspace_id', auth()->user()->workspace_id);
            $customer_id = auth()->user()->customer_id;
            $workspace_id = auth()->user()->workspace_id;
        }

        if ($request->filled('filter')) {
            $query = $this->filter(json_decode($request->filter, true), $query);
        }

        $query->with('LeavePackage')->with('EmpPersonalDetails');
        $query->orderBy('id', 'desc');
        if($request->filled('pagination') || !empty($request->filters)){
            $query_result = $this->searchFilterRecord($query, $request);
        }else{
            $query_result = $query->get();
        }
        $count = $query->count();
        $start = $request->input('from', 0);
        $query->offset($start)->limit(10);

        $i = 0;
        foreach ($query_result as $query_resul) {
            $from = BaseModel::safeCarbonParse($query_resul->from, 'LeaveRequestController.approved_leave.from');
            $to = BaseModel::safeCarbonParse($query_resul->to, 'LeaveRequestController.approved_leave.to');

            if (!($from instanceof Carbon) || !($to instanceof Carbon)) {
                Log::warning('Unable to compute approved leave duration due to unparsable dates', [
                    'leave_id' => $query_resul->id ?? null,
                    'from' => $query_resul->from,
                    'to' => $query_resul->to,
                ]);
                $days = 0;
            } else {
                $days = $to->diffInDays($from) + 1;
            }

            $query_result[$i]->total_day = $days;
            $i++;
        }

        // Get total employee count from EmpPersonalDetails
        $total_employees = EmpCompanyDetails::where('customer_id', $customer_id)
            ->where('workspace_id', $workspace_id)
            ->count();

        // Count unique employees with approved leave from the current result set
        $employees_with_approved_leave = $query_result->pluck('EmpPersonalDetails.emp_id')->unique()->count();

        // Add employee counts to the response
        $response_data = [
            'leave_requests' => $query_result,
            'total_employees' => $total_employees,
            'employees_with_approved_leave' => $employees_with_approved_leave,
            'total_leave_requests' => $count
        ];

        $array_filter = json_decode($request->filter, true);
        return $this->WithFilter($response_data, $array_filter, 'Get Leave Package List Successfully');
    }
    public function rejected_leave(Request $request)
    {
        $userTable = $this->getUserTable();
        $query = LeaveRequest::query();
        $query->where('status', '2');
        if ($userTable === "customer") {
            $query->where('customer_id', Auth::user()->id)
                ->where('workspace_id', Auth::user()->current_workspace_id);
        }
        if ($userTable === "emp") {
            $query->where('customer_id', auth()->user()->customer_id) // Assuming customer_id links employee to projects
                ->where('workspace_id', auth()->user()->workspace_id);
        }
        if ($request->filled('filter')) {
            $query =  $this->filter(json_decode($request->filter, true), $query);
        }
        $query->with('LeavePackage')->with('EmpPersonalDetails');
        $query->orderBy('id', 'desc');
        if($request->filled('pagination') || !empty($request->filters)){
            $query_result = $this->searchFilterRecord($query, $request);
        }else{
            $query_result = $query->get();
        }
        $count = $query->count();
        $start = $request->input('from', 0);
        $query->offset($start)->limit(10);


        $i = 0;

        foreach ($query_result as $query_resul) {
            $from = BaseModel::safeCarbonParse($query_resul->from, 'LeaveRequestController.rejected_leave.from');
            $to = BaseModel::safeCarbonParse($query_resul->to, 'LeaveRequestController.rejected_leave.to');

            if (!($from instanceof Carbon) || !($to instanceof Carbon)) {
                Log::warning('Unable to compute rejected leave duration due to unparsable dates', [
                    'leave_id' => $query_resul->id ?? null,
                    'from' => $query_resul->from,
                    'to' => $query_resul->to,
                ]);
                $days = 0;
            } else {
                $days = $to->diffInDays($from) + 1;
            }

            $query_result[$i]->total_day = $days;
            $i++;
        }


        $data = [
            'rejected_leaves' => $query_result
        ];
        $array_filter =  json_decode($request->filter, true);
        return $this->WithFilter($query_result, $array_filter, 'Get Rejected Leave List Successfully');
    }

    public function exipired_leave(Request $request)
    {
        $userTable = $this->getUserTable();
        $currentDate = Carbon::now();
        $query = LeaveRequest::query();
        $query->where('status', '3');
        if ($userTable === "customer") {
            $query->where('customer_id', Auth::user()->id)
                ->where('workspace_id', Auth::user()->current_workspace_id);
        }
        if ($userTable === "emp") {
            $query->where('customer_id', auth()->user()->customer_id) // Assuming customer_id links employee to projects
                ->where('workspace_id', auth()->user()->workspace_id);
        }
        if ($request->filled('filter')) {
            $query =  $this->filter(json_decode($request->filter, true), $query);
        }
        $query->with('LeavePackage')->with('EmpPersonalDetails');
        $query->orderBy('id', 'desc');
        if($request->filled('pagination') || !empty($request->filters)){
            $query_result = $this->searchFilterRecord($query, $request);
        }else{
            $query_result = $query->get();
        }
        $count = $query->count();
        $start = $request->input('from', 0);
        $query->offset($start)->limit(10);
        $i = 0;

        foreach ($query_result as $query_resul) {
            $from = BaseModel::safeCarbonParse($query_resul->from, 'LeaveRequestController.exipired_leave.from');
            $to = BaseModel::safeCarbonParse($query_resul->to, 'LeaveRequestController.exipired_leave.to');

            if (!($from instanceof Carbon) || !($to instanceof Carbon)) {
                Log::warning('Unable to compute expired leave duration due to unparsable dates', [
                    'leave_id' => $query_resul->id ?? null,
                    'from' => $query_resul->from,
                    'to' => $query_resul->to,
                ]);
                $days = 0;
            } else {
                $days = $to->diffInDays($from) + 1;
            }

            $query_result[$i]->total_day = $days;
            $i++;
        }
        $data = [
            'expired_leaves' => $query_result,
        ];
        $array_filter =  json_decode($request->filter, true);
        return $this->WithFilter($query_result, $array_filter, 'Get Exipired Leave List Successfully');
    }

    public function updateExpiredLeaveStatus()
    {
        try {
            // Calculate the cutoff date (90 days ago from today)
            $cutoffDate = Carbon::now()->subDays(90);
            
            // Get all leave requests that are older than 90 days and not already expired
            $expiredLeaves = LeaveRequest::where('created_at', '<', $cutoffDate)
                ->whereNotIn('status', [2, 3]) // Exclude rejected (2) and already expired (3) leaves
                ->get();

            $updatedCount = 0;
            $errors = [];

            foreach ($expiredLeaves as $leave) {
                try {
                    // Update the leave status to expired (3)
                    $leave->update(['status' => 3]);
                    
                    // Get leave package details for notification
                    $leave_pkg = LeavePackage::where('id', $leave->leave_package_id)->first();
                    
                    // Create notification for expired leave
                    $this->save_notifications(
                        'Leave Request Expired',
                        'Your leave request for ' . ($leave_pkg ? $leave_pkg->title : 'Leave') . ' with Start Date ' . date('M d, Y', strtotime($leave->from)) . ' and End Date ' . date('M d, Y', strtotime($leave->to)) . ' has expired.',
                        $leave->employee_id, // Use employee as sender since it's a system notification
                        $leave->employee_id,
                        'leave_request_expired',
                        $leave->customer_id,
                        $leave->workspace_id
                    );

                    $updatedCount++;

                } catch (\Exception $e) {
                    $errors[] = "Failed to update leave ID {$leave->id}: " . $e->getMessage();
                    Log::error("Failed to update expired leave status for ID {$leave->id}: " . $e->getMessage());
                }
            }

            // Log the overall process
            Log::info("Daily expired leave check completed", [
                'total_processed' => $expiredLeaves->count(),
                'successfully_updated' => $updatedCount,
                'errors' => count($errors),
                'cutoff_date' => $cutoffDate->format('Y-m-d H:i:s'),
                'current_time' => Carbon::now()->format('Y-m-d H:i:s')
            ]);

            // Return success response
            return $this->success('Daily expired leave check completed successfully',[
                'data' => [
                    'total_processed' => $expiredLeaves->count(),
                    'successfully_updated' => $updatedCount,
                    'errors' => count($errors),
                    'cutoff_date' => $cutoffDate->format('Y-m-d'),
                    'processed_at' => Carbon::now()->format('Y-m-d H:i:s')
                ]
            ]);

        } catch (\Exception $e) {
            Log::error("Daily expired leave check failed: " . $e->getMessage());
            
            return $this->error('Failed to process expired leave requests',[
                'error' => $e->getMessage()
            ]);
        }
    }

    public function update_expired_status()
    {
        $expiredLeaves = LeaveRequest::where('to', '<', Carbon::now())
            ->where('status', 0)
            ->where('status', '!=', 3)
            ->get();

        foreach ($expiredLeaves as $leave) {
            $leave->update(['status' => 3]);
            
            // Get leave package details for notification
            $leave_pkg = LeavePackage::where('id', $leave->leave_package_id)->first();
            
            // Create notification for expired leave
            $this->save_notifications(
                'Leave Request Expired',
                'Your leave request for ' . ($leave_pkg ? $leave_pkg->title : 'Leave') . ' with Start Date ' . date('M d, Y', strtotime($leave->from)) . ' and End Date ' . date('M d, Y', strtotime($leave->to)) . ' has expired.',
                $leave->employee_id, // Use employee as sender since it's a system notification
                $leave->employee_id,
                'leave_request_expired',
                $leave->customer_id,
                $leave->workspace_id
            );
        }
        return 0;
    }
}
