<?php

namespace App\Http\Controllers;

use App\General\GeoLoacation;
use App\Models\EmpCompanyDetails;
use App\Models\Sites;
use App\Models\LeaveRequest;
use App\Models\EmployeeAttendance;
use App\Models\Overtime;
use App\Models\EmployeeBreak;
use App\Models\RosterTemplate;
use App\Models\RosterAssign;
use App\Models\AttendanceListChangeCol;
use App\Models\User;
use App\Models\BaseModel;
use App\Models\SiteDocument;
use App\Models\SiteDocumentSignature;
use App\Models\Role;
use Illuminate\Support\Facades\Response;
use Illuminate\Support\Facades\Validator;
use Illuminate\Support\Facades\Auth;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

use Illuminate\Http\Request;


class AttendanceController extends Controller
{
    public function __construct()
    {
        $this->middleware('Permissions:Attendance Maintain')->only(['create', 'edit']);
    }

    public function index(Request $request)
    {
        $query = EmployeeAttendance::query();
        $query = $this->applyCustomerWorkspaceFilter($query);
        $query->with(['empPersonalDetails', 'EmpCompanyDetails', 'Sites', 'empTeamsMembers', 'breaks']);
        $query->latest('id');
        if ($request->filled('search')) {
            $searchTerm = $request->search;
            $query->where(function ($q) use ($searchTerm) {
                // Apply name search using helper
                $q->where(function ($nameQuery) use ($searchTerm) {
                    $this->applyNameSearch($nameQuery, $searchTerm, 'empPersonalDetails');
                })
                // Search in site title
                ->orWhereHas('sites', function ($subquery) use ($searchTerm) {
                    $subquery->where('title', 'like', '%' . $searchTerm . '%');
                })
                // Search in date (format: Y-m-d)
                ->orWhere('date', 'like', '%' . $searchTerm . '%')
                // Search in team (through team members)
                ->orWhereHas('empTeamsMembers', function ($subquery) use ($searchTerm) {
                    $subquery->whereHas('empTeamsList', function ($teamQuery) use ($searchTerm) {
                        $teamQuery->where('title', 'like', '%' . $searchTerm . '%');
                    });
                });
            });
        }   
        return $this->withCount($query, 'Get Attendance List Successfully');
    }


    public function single_employee_attendance(Request $request)
    {
        // Validate the incoming request inputs.
        $request->validate([
            'to'      => 'required|date',
            'from'    => 'required|date|before_or_equal:to',
            'site_id' => 'nullable'
        ]);

        // Retrieve input values.
        $toDate   = $request->input('to');
        $fromDate = $request->input('from');
        $siteid   = $request->input('site_id');

        // Get the authenticated user's employee id.
        $empid = $request->user()->id;
        
        // Retrieve attendance data conditionally based on site_id.
        if ($siteid) {
            $attendance = EmployeeAttendance::where('site_id', $siteid)
                ->where('employee_id', $empid)
                ->whereBetween('date', [$fromDate, $toDate])
                ->with('RosterAssign.RosterTemplate', 'sites')
                ->with('breaks')
                ->orderBy('date', 'desc')
                ->get();
        } else {
            $attendance = EmployeeAttendance::where('employee_id', $empid)
                ->whereBetween('date', [$fromDate, $toDate])
                ->with('RosterAssign.RosterTemplate', 'sites')
                ->with('breaks')
                ->orderBy('date', 'desc')
                ->get();
        }

        // Return the attendance results.
        return $this->success($attendance, 'Get Employee Attendance List Successfully');
    }


    /**
     * Get roster times for an employee on a specific date
     */
    private function getRosterTimes($employeeId, $date)
    {
        $roster = DB::table('roster_assigns')
            ->join('roster_templates', 'roster_assigns.roster_template_id', '=', 'roster_templates.id')
            ->where('roster_assigns.assign_to', $employeeId)
            ->where('roster_assigns.schedule_date', $date)
            ->select('roster_templates.start_time', 'roster_templates.end_time')
            ->first();
            
        return $roster;
    }

    /**
     * Validate attendance times against roster times
     */
    private function validateAttendanceTimes($checkIn, $checkOut, $rosterStartTime, $rosterEndTime)
    {
        $checkInTime = Carbon::parse($checkIn);
        $checkOutTime = $checkOut ? Carbon::parse($checkOut) : null;
        $rosterStart = Carbon::parse($rosterStartTime);
        $rosterEnd = Carbon::parse($rosterEndTime);

        // Check if check-in is within roster times
        if ($checkInTime->lt($rosterStart) || $checkInTime->gt($rosterEnd)) {
            return [
                'valid' => false,
                'message' => "Check-in time ({$checkInTime->format('H:i')}) is outside roster schedule ({$rosterStart->format('H:i')} - {$rosterEnd->format('H:i')}). Please adjust the time to be within roster hours."
            ];
        }

        // Check if check-out is within roster times (if provided)
        if ($checkOutTime && ($checkOutTime->lt($rosterStart) || $checkOutTime->gt($rosterEnd))) {
            return [
                'valid' => false,
                'message' => "Check-out time ({$checkOutTime->format('H:i')}) is outside roster schedule ({$rosterStart->format('H:i')} - {$rosterEnd->format('H:i')}). Please adjust the time to be within roster hours."
            ];
        }

        return ['valid' => true];
    }

    /**
     * Check if employee has already checked in for the date
     */
    private function hasAlreadyCheckedIn($employeeId, $date, $checkIn = null, $checkOut = null, $excludeId = null)
    {
        $query = EmployeeAttendance::where('employee_id', $employeeId)
            ->where('date', $date)
            ->whereNotNull('check_in');
        
        if (!is_null($checkIn)) {
            $query->whereTime('check_in', $checkIn);
        }

        if (!is_null($checkOut)) {
            $query->whereTime('check_out', $checkOut);
        }
            
        if ($excludeId) {
            $query->where('id', '!=', $excludeId);
        }
        
        return $query->exists();
    }

    public function create(Request $request)
    {
        $get_employes =  EmpCompanyDetails::with('EmpPersonalDetails')->orderBy('id', 'DESC')->get();
        $sites = Sites::where('del', 0)->where('active', 1)->get();
        return view('Attendance.create', compact('get_employes', 'sites'));
    }

    public function store(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'employee' => 'required',
            'site' => 'required',
            'check_in' => 'required|date_format:H:i',
            'check_out' => 'nullable|date_format:H:i|after:check_in',
            'date' => 'required|date',
            'working_hours' => 'nullable',
            'break_in' => ['nullable', 'array'],
            'break_out' => ['nullable', 'array'],
            'break_in.*' => ['nullable'],
            'break_out.*' => ['nullable', 'after_or_equal:break_in.*'],
        ], [
            'break_out.*.after_or_equal' => 'The break_out must be after or equal to break_in.',
            'check_out.after' => 'The check out time must be after the check in time.',
        ]);

        if ($validator->fails()) {
            $error = $validator->errors()->first();
            return $this->message($error, 422);
        }
        $ids = $this->getCustomerAndWorkspaceIds();
        $customer_id = $ids['customer_id'];
        $workspace_id = $ids['workspace_id'];
        $validatedData = $validator->validated();
        // new validation
        $userTable = $this->getUserTable();
        if ($userTable === "customer") {
            $employeeDetails = EmpCompanyDetails::where('id', $validatedData['employee'])
                ->where('customer_id', $customer_id)
                ->where('workspace_id', $workspace_id)
                ->first();
            if (!$employeeDetails) {
                return $this->message('Unauthorized access to this employee.', 403);
            }
        }
        if ($userTable === "emp") {
            $employeeDetails = EmpCompanyDetails::where('id', $validatedData['employee'])
                ->where('customer_id', $customer_id)
                ->where('workspace_id', $workspace_id)
                ->first();
            if (!$employeeDetails) {
                return $this->message('Unauthorized access to this employee.', 403);
            }
        }
        $date = $this->parseDateString($validatedData['date']);
        if (isset($validatedData['date'])) {
            $dateForRosterCheck = $this->parseDateString($validatedData['date']);
        } else {
            $dateForRosterCheck = null;
        }
        // new validation end
        $roster_assigns =   DB::table('roster_assigns')
            // ->join('roster_templates', 'roster_assigns.roster_template_id', '=', 'roster_templates.id')
            ->where('assign_to', $validatedData['employee'])
            ->where('schedule_date', $dateForRosterCheck)
            ->count();
        if ($roster_assigns == 0) {
            return $this->message('Roaster not found', 404);
        }

        // Get roster times for validation
        $rosterTimes = $this->getRosterTimes($validatedData['employee'], $dateForRosterCheck);
        if (!$rosterTimes) {
            return $this->message('Roster template not found for the selected date', 404);
        }

        // Validate attendance times against roster times
        $timeValidation = $this->validateAttendanceTimes(
            $validatedData['check_in'],
            $validatedData['check_out'] ?? null,
            $rosterTimes->start_time,
            $rosterTimes->end_time
        );

        if (!$timeValidation['valid']) {
            return $this->message($timeValidation['message'], 422);
        }

        // Check if employee has already checked in for this date and time range
        if ($this->hasAlreadyCheckedIn(
            $validatedData['employee'],
            $date,
            $validatedData['check_in'],
            $validatedData['check_out'] ?? null
        )) {
            return $this->message(
                'Employee already has an attendance record for the same date and time. Cannot mark attendance again with identical timings.',
                422
            );
        }
        
        $check_leave = LeaveRequest::where('employee_id', $request->employee)->where('status', 1)->where('from', ">=", $date)
            ->where('to', "<=", $date)->exists();
        if ($check_leave) {
            return $this->message('Employee is on leave. You are not allowed to check-in', 422);
        }

        // Check for overlapping attendance records
        $checkIn = Carbon::parse($validatedData['check_in']);
        $checkOut = Carbon::parse($validatedData['check_out']);
        
        // Find existing attendance records for the same employee and date
        $existingAttendances = EmployeeAttendance::where('employee_id', $validatedData['employee'])
            ->where('date', $date)
            ->get();

        foreach ($existingAttendances as $existingAttendance) {
            // Skip if either check_in or check_out is null
            if (!$existingAttendance->check_in || !$existingAttendance->check_out) {
                continue;
            }
            
            $existingCheckIn = Carbon::parse($existingAttendance->check_in);
            $existingCheckOut = Carbon::parse($existingAttendance->check_out);
            
            // Check if the new attendance overlaps with existing one
            if ($this->timeRangesOverlap($checkIn, $checkOut, $existingCheckIn, $existingCheckOut)) {
                return $this->message(
                    'Attendance overlap detected. You already have an attendance record from ' . 
                    $existingCheckIn->format('H:i') . ' to ' . $existingCheckOut->format('H:i') . 
                    ' for this date. Please choose a different time period.', 
                    422
                );
            }
        }
        // Calculate total working hours
        $checkIn = Carbon::parse($validatedData['check_in']);
        $checkOut = Carbon::parse($validatedData['check_out']);
        $totalHours = $checkOut->diffInMinutes($checkIn);
        // Calculate total breaks
        $totalBreaks = 0;
        $totalWorkingHours = 0;
        if (isset($validatedData['break_in'])) {
            foreach ($validatedData['break_in'] as $key => $breakIn) {
                if (isset($validatedData['break_out'][$key])) {
                    $breakInTime = Carbon::parse($breakIn);
                    $breakOutTime = Carbon::parse($validatedData['break_out'][$key]);
                    $totalBreaks += $breakOutTime->diffInMinutes($breakInTime);
                }
            }
        }
        $customerId = $userTable == "customer" ? auth()->id() : $employeeDetails->customer_id;

        // Calculate total working hours with breaks
        $totalWorkingHours = $totalHours - $totalBreaks;
        $insertedRecord = EmployeeAttendance::create([
            'employee_id' => $validatedData['employee'],
            'customer_id' => $customerId,
            'workspace_id' => $employeeDetails->workspace_id,
            'site_id' => $validatedData['site'],
            'check_in' => $validatedData['check_in'],
            'working_hours' => $validatedData['check_out'] ? $totalWorkingHours : '',
            'check_out' => $validatedData['check_out'],
            'date' => $validatedData['date'],
            'added_by' => $userTable == "customer" ? 0 : auth()->id(),
        ]);
        $id = $insertedRecord->id;
        if (isset($validatedData['break_in'])) {
            if (is_array($validatedData['break_in'])) {
                foreach ($validatedData['break_in'] as $key => $breakIn) {
                    // Skip if either break_in or break_out is null
                    if ($breakIn !== null && isset($validatedData['break_out'][$key])) {
                        $breakOut = $validatedData['break_out'][$key];

                        EmployeeBreak::create([
                            'emp_attendance_id' => $id,
                            'break_in' => $breakIn,
                            'break_out' => $breakOut,
                        ]);
                    }
                }
            } else {
                // Handle the case where 'break_in' and 'break_out' are single values
                $breakOut = isset($validatedData['break_out'][0]) ? $validatedData['break_out'][0] : null;
                // Skip if either break_in or break_out is null
                if ($validatedData['break_in'][0] !== null) {
                    EmployeeBreak::create([
                        'emp_attendance_id' => $id,
                        'break_in' => $validatedData['break_in'][0],
                        'break_out' => $breakOut,
                    ]);
                }
            }
        }
        return $this->success($insertedRecord, 'Attendance Saved Successfully');
    }

    public function getEmployeeAttendance($id)
    {
        $get_employes =  EmpCompanyDetails::with('EmpPersonalDetails')->orderBy('id', 'DESC')->get();
        $sites = Sites::where('del', 0)->where('active', 1)->get();
        $emp_attend = EmployeeAttendance::where('id', $id)->first();
        $userTable = $this->getUserTable();
        if (!$emp_attend) {
            return $this->message('Record not found', 404);
        }
        if ($userTable == "customer" && ($emp_attend->workspace_id != auth()->user()->current_workspace_id || $emp_attend->customer_id != auth()->user()->id)) {
            return $this->message('You do not have access to this project', 403);
        }
        if ($userTable == "emp" && ($emp_attend->customer_id != auth()->user()->customer_id || $emp_attend->workspace_id != auth()->user()->workspace_id)) {
            return $this->message('You do not have access to this project', 403);
        }
        $emp_breaks = EmployeeBreak::where('emp_attendance_id', $id)->get();
        $data = [
            'attendance' => $emp_attend,
            'breaks' => $emp_breaks,
            'employees' => $get_employes,
            'sites' => $sites,
        ];
        return $this->success($data, 'Attendance get Successfully');
        // return view('Attendance.edit', compact('get_employes', 'sites', 'emp_attend', 'emp_breaks'));
    }

    public function update(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'employee' => 'required',
            'site' => 'required',
            'check_in' => 'required|date_format:H:i',
            'check_out' => 'nullable|date_format:H:i|after:check_in',
            'date' => 'required|date',
            'status' => 'nullable',
            'break_in' => ['nullable', 'array'],
            'break_out' => ['nullable', 'array'],
            'break_in.*' => ['nullable'],
            'break_out.*' => ['nullable', 'after_or_equal:break_in.*'],
        ], [
            'break_out.*.after_or_equal' => 'The break_out must be after or equal to break_in.',
        ]);
        if ($validator->fails()) {
            $error = $validator->errors()->first();
            return $this->message($error, 422);
        }
        $userTable = $this->getUserTable();
        $employeeAttendance = EmployeeAttendance::find($request->id);
        if (!$employeeAttendance) {
            return $this->message('Record not found', 404);
        }
        $validatedData = $validator->validated();
        if ($userTable == "customer" && ($employeeAttendance->workspace_id != auth()->user()->current_workspace_id || $employeeAttendance->customer_id != auth()->user()->id)) {
            return $this->message('You do not have access to this project', 403);
        }
        if ($userTable == "emp" && ($employeeAttendance->customer_id != auth()->user()->customer_id || $employeeAttendance->workspace_id != auth()->user()->workspace_id)) {
            return $this->message('You do not have access to this project', 403);
        }

        // Check if roster exists
        $dateForRosterCheck = $this->parseDateStringupdate($validatedData['date']);
        $roster_assigns = DB::table('roster_assigns')
            ->where('assign_to', $validatedData['employee'])
            ->where('schedule_date', $dateForRosterCheck)
            ->count();
        if ($roster_assigns == 0) {
            return $this->message('Roaster not found', 404);
        }

        // Get roster times for validation
        $rosterTimes = $this->getRosterTimes($validatedData['employee'], $dateForRosterCheck);
        if (!$rosterTimes) {
            return $this->message('Roster template not found for the selected date', 404);
        }

        // Validate attendance times against roster times
        $timeValidation = $this->validateAttendanceTimes(
            $validatedData['check_in'],
            $validatedData['check_out'] ?? null,
            $rosterTimes->start_time,
            $rosterTimes->end_time
        );

        if (!$timeValidation['valid']) {
            return $this->message($timeValidation['message'], 422);
        }

        // Check if employee has already checked in for this date and time range (excluding current record)
        $date = $this->parseDateStringupdate($validatedData['date']);
        if ($this->hasAlreadyCheckedIn(
            $validatedData['employee'],
            $date,
            $validatedData['check_in'],
            $validatedData['check_out'] ?? null,
            $request->id
        )) {
            return $this->message(
                'Employee already has an attendance record for the same date and time. Cannot mark attendance again with identical timings.',
                422
            );
        }

        $checkIn = Carbon::parse($validatedData['check_in']);
        $checkOut = Carbon::parse($validatedData['check_out']);
        $totalHours = $checkOut->diffInMinutes($checkIn);

        // Check for overlapping attendance records (excluding the current record being updated)
        $date = $this->parseDateStringupdate($validatedData['date']);
        
        // Debug: Log the values being checked
        Log::info('Update attendance overlap check', [
            'employee_id' => $validatedData['employee'],
            'date' => $date,
            'current_record_id' => $request->id,
            'new_check_in' => $validatedData['check_in'],
            'new_check_out' => $validatedData['check_out']
        ]);
        
        $existingAttendances = EmployeeAttendance::where('employee_id', $validatedData['employee'])
            ->where('date', $date)
            ->where('id', '!=', $request->id) // Exclude the current record being updated
            ->get();

        Log::info('Found existing attendances', [
            'count' => $existingAttendances->count(),
            'records' => $existingAttendances->map(function($att) {
                return [
                    'id' => $att->id,
                    'check_in' => $att->check_in,
                    'check_out' => $att->check_out
                ];
            })
        ]);

        foreach ($existingAttendances as $existingAttendance) {
            // Skip if either check_in or check_out is null
            if (!$existingAttendance->check_in || !$existingAttendance->check_out) {
                continue;
            }
            
            $existingCheckIn = Carbon::parse($existingAttendance->check_in);
            $existingCheckOut = Carbon::parse($existingAttendance->check_out);
            
            Log::info('Checking overlap', [
                'existing' => [
                    'id' => $existingAttendance->id,
                    'check_in' => $existingCheckIn->format('H:i'),
                    'check_out' => $existingCheckOut->format('H:i')
                ],
                'new' => [
                    'check_in' => $checkIn->format('H:i'),
                    'check_out' => $checkOut->format('H:i')
                ]
            ]);
            
            // Check if the updated attendance overlaps with existing one
            if ($this->timeRangesOverlap($checkIn, $checkOut, $existingCheckIn, $existingCheckOut)) {
                Log::info('Overlap detected!');
                return $this->message(
                    'Attendance overlap detected. You already have an attendance record from ' . 
                    $existingCheckIn->format('H:i') . ' to ' . $existingCheckOut->format('H:i') . 
                    ' for this date. Please choose a different time period.', 
                    422
                );
            }
        }
        // Calculate total breaks
        $totalBreaks = 0;
        $totalWorkingHours = 0;
        if (isset($validatedData['break_in'])) {
            foreach ($validatedData['break_in'] as $key => $breakIn) {
                if (isset($validatedData['break_out'][$key])) {
                    $breakInTime = Carbon::parse($breakIn);
                    $breakOutTime = Carbon::parse($validatedData['break_out'][$key]);
                    $totalBreaks += $breakOutTime->diffInMinutes($breakInTime);
                }
            }
        }
        $customerId = $userTable == "customer" ? auth()->id() : $employeeAttendance->customer_id;

        // Calculate total working hours with breaks
        $totalWorkingHours = $totalHours - $totalBreaks;
        $employeeAttendance->update([
            'employee_id' => $validatedData['employee'],
            'customer_id' => $customerId,
            'workspace_id' => $employeeAttendance->workspace_id,
            'site_id' => $validatedData['site'],
            'working_hours' => $validatedData['check_out'] ? $totalWorkingHours : '',
            'check_in' => $validatedData['check_in'],
            'check_out' => $validatedData['check_out'],
            'status' => $validatedData['status'] ?? 0,
            'date' => $validatedData['date'],
            'added_by' => $userTable == "customer" ? 0 : auth()->id(),
        ]);
        // Delete existing breaks
        $employeeAttendance->breaks()->delete();
        if (isset($validatedData['break_in'])) {
            foreach ($validatedData['break_in'] as $key => $breakIn) {
                $breakOut = isset($validatedData['break_out'][$key]) ? $validatedData['break_out'][$key] : null;
                EmployeeBreak::create([
                    'emp_attendance_id' => $employeeAttendance->id,
                    'break_in' => $breakIn,
                    'break_out' => $breakOut,
                ]);
            }
        }
        return $this->success($employeeAttendance, 'Attendance Updated Successfully');
    }

    public function destroy($id)
    {
        try {
            $attendance = EmployeeAttendance::find($id);
            if (!$attendance) {
                return $this->message('Attendance record not found.', 404);
            }
            $userTable = $this->getUserTable();
            if ($userTable === "customer") {
                $employeeDetails = EmpCompanyDetails::where('id', $attendance->employee_id)
                    ->where('customer_id', auth()->id())
                    ->where('workspace_id', auth()->user()->current_workspace_id)
                    ->first();
                if (!$employeeDetails) {
                    return $this->message('Unauthorized access to this attendance record.', 403);
                }
            }
            if ($userTable === "emp") {
                $employeeDetails = EmpCompanyDetails::where('id', $attendance->employee_id)
                    ->where('customer_id', auth()->user()->customer_id)
                    ->where('workspace_id', auth()->user()->workspace_id)
                    ->first();

                if (!$employeeDetails) {
                    return $this->message('Unauthorized access to this attendance record.', 403);
                }
            }
            EmployeeAttendance::where('id', $id)->delete();
            EmployeeBreak::where('emp_attendance_id', $id)->delete();
            return $this->message('Attendance Deleted Successfully');
        } catch (\Exception $e) {
            return $this->message('Failed to delete attendance. Error: ' . $e->getMessage(), 500);
        }
    }


    public function deletebreak($id)
    {
        try {
            // Fetch the break record
            $empbreak = EmployeeBreak::find($id);
            if (!$empbreak) {
                return $this->message('Break record not found.', 404);
            }
            $attendance = EmployeeAttendance::find($empbreak->emp_attendance_id);
            if (!$attendance) {
                return $this->message('Attendance record not found.', 404);
            }
            // Determine user type and validate access
            $userTable = $this->getUserTable();
            if ($userTable === "customer") {
                $employeeDetails = EmpCompanyDetails::where('id', $attendance->employee_id)
                    ->where('customer_id', auth()->id())
                    ->where('workspace_id', auth()->user()->current_workspace_id)
                    ->first();
                if (!$employeeDetails) {
                    return $this->message('Unauthorized access to this break record.', 403);
                }
            }
            if ($userTable === "emp") {
                $employeeDetails = EmpCompanyDetails::where('id', $attendance->employee_id)
                    ->where('customer_id', auth()->user()->customer_id)
                    ->where('workspace_id', auth()->user()->workspace_id)
                    ->first();
                if (!$employeeDetails) {
                    return $this->message('Unauthorized access to this break record.', 403);
                }
            }
            // Calculate and update working hours
            $breakIn = Carbon::parse($empbreak->break_in);
            $breakOut = Carbon::parse($empbreak->break_out);
            $totalBreakHours = $breakOut->diffInHours($breakIn);
            $totalWorkingHours = $attendance->working_hours + $totalBreakHours;
            $attendance->update(['working_hours' => $totalWorkingHours]);
            $empbreak->delete();
            return $this->message('Break deleted successfully');
        } catch (\Exception $e) {
            return $this->message('Failed to delete break. Error: ' . $e->getMessage(), 500);
        }
    }

    public function attendance_request(Request $request)
    {
        try {
            $query = EmployeeAttendance::query();
            $userTable = $this->getUserTable();
            if ($userTable === "customer") {
                $query->whereHas('EmpCompanyDetails', function ($q) {
                    $q->where('customer_id', auth()->id())
                        ->where('workspace_id', auth()->user()->current_workspace_id);
                });
            }
            if ($userTable === "emp") {
                $query->whereHas('EmpCompanyDetails', function ($q) {
                    $q->where('customer_id', auth()->user()->customer_id)
                        ->where('workspace_id', auth()->user()->workspace_id);
                });
            }
            if ($userTable !== "customer" && $userTable !== "emp") {
                return $this->message('Unauthorized access.', 403);
            }

            if ($request->filled('filter')) {
                $query = $this->filter_request(json_decode($request->filter, true), $query);
            }
            $query->with(['EmpPersonalDetails', 'EmpCompanyDetails', 'Sites', 'addedby', 'breaks', 'empTeamsMembers']);
            if($request->filled('pagination') || !empty($request->filters)){
                $query_result = $this->searchFilterRecord($query, $request);
            }else{
                $start = $request->input('from', 0);
                $query->offset($start)->limit(10);
                $query->orderBy('id', 'desc');
                $query_result = $query->get();
            }


            // Prepare filters
            $array_filter = json_decode($request->filter, true);
            $filters = [
                'first_name' => $array_filter['first_name'] ?? '',
                'middle_name' => $array_filter['middle_name'] ?? '',
                'last_name' => $array_filter['first_name'] ?? '',
                'employee_email' => $array_filter['last_name'] ?? '',
                'completed' => $array_filter['first_name'] ?? '',
                'status' => $array_filter['completed'] ?? '',
            ];
            return $this->withFilter($query_result, $filters, 'Get Attendance List Successfully');
        } catch (\Exception $e) {
            return $this->message('Failed to retrieve attendance list. Error: ' . $e->getMessage(), 500);
        }
    }


    public function filter_request($filters, $query)
    {
        foreach ($filters as $filterName => $filterValue) {
            if ($filterValue != null ||  $filterValue != "") {
                switch ($filterName) {
                    case 'first_name':
                        $filterValue  = explode(" ", $filterValue);

                        if (count($filterValue) == 1) {
                            $query->whereHas('empPersonalDetails', function ($subquery) use ($filterValue) {
                                $subquery->where('first_name', 'like', '%' . $filterValue[0] . '%');
                                $subquery->orWhere('middle_name', 'like', '%' . $filterValue[0] . '%');
                                $subquery->orWhere('last_name', 'like', '%' . $filterValue[0] . '%');
                            });
                        }

                        if (count($filterValue) == 2) {
                            $query->whereHas('empPersonalDetails', function ($subquery) use ($filterValue) {
                                $subquery->where('first_name', 'like', '%' . $filterValue[0] . '%');
                                $subquery->where('middle_name', 'like', '%' . $filterValue[1] . '%');
                                $subquery->where('last_name', 'like', '%' . $filterValue[1] . '%');
                            });
                        }

                        if (count($filterValue) == 3) {

                            $query->whereHas('empPersonalDetails', function ($subquery) use ($filterValue) {
                                $subquery->where('first_name', 'like', '%' . $filterValue[0] . '%');
                                $subquery->where('middle_name', 'like', '%' . $filterValue[1] . '%');
                                $subquery->where('last_name', 'like', '%' . $filterValue[2] . '%');
                            });
                        }

                        break;
                    case 'user_type':
                        $query->whereHas('empCompanyDetails', function ($subquery) use ($filterValue) {
                            $filterValue == 0 ? 0 : 1;
                            $subquery->where('user_type', 'like', '%' . $filterValue . '%');
                        });
                        break;
                    case 'first_name_add':

                        $filterValue  = explode(" ", $filterValue);
                        if (count($filterValue) == 1) {
                            $query->whereHas('addedby', function ($subquery) use ($filterValue) {
                                $subquery->where('first_name', 'like', '%' . $filterValue[0] . '%');
                            });
                        }

                        if (count($filterValue) == 2) {
                            $query->whereHas('addedby', function ($subquery) use ($filterValue) {
                                $subquery->where('first_name', 'like', '%' . $filterValue[0] . '%');
                                $subquery->where('last_name', 'like', '%' . $filterValue[1] . '%');
                            });
                        }

                        if (count($filterValue) == 3) {
                            $query->whereHas('addedby', function ($subquery) use ($filterValue) {
                                $subquery->where('first_name', 'like', '%' . $filterValue[0] . '%');
                                $subquery->where('middle_name', 'like', '%' . $filterValue[1] . '%');
                                $subquery->where('last_name', 'like', '%' . $filterValue[2] . '%');
                            });
                        }

                        break;
                    case 'title':
                        $query->whereHas('sites', function ($subquery) use ($filterValue) {
                            $subquery->where('title', 'like', '%' . $filterValue . '%');
                        });
                        break;
                    case 'Check_in':
                        $query->where('Check_in', 'like', '%' . $filterValue . '%');
                        break;
                    case 'Check_out':
                        $query->where('Check_out', 'like', '%' . $filterValue . '%');
                        break;
                    case 'working_hours':
                        $query->where('working_hours', 'like', '%' . $filterValue . '%');
                        break;
                    case 'status':
                        $filterValue =   $filterValue ? 1 : 0;
                        $query->where('status', 'like', '%' . $filterValue . '%');
                        break;
                    case 'date':
                        $filterValue = explode("/", $filterValue);
                        $query->whereBetween('date', [date("Y-m-d", strtotime(trim($filterValue[0], " "))), date("Y-m-d", strtotime(trim($filterValue[1], " ")))]);
                        break;
                }
            }
        }

        return $query;
    }

    public function updateStatus(Request $request)
    {
        $EmployeeAttendance = EmployeeAttendance::find($request->id);
        if (!$EmployeeAttendance) {
            return $this->message('Record not found', 404);
        }
        $userTable = $this->getUserTable();
        // Check for customer access
        if ($userTable === "customer") {
            $validAccess = $EmployeeAttendance->customer_id === auth()->id() &&
                $EmployeeAttendance->workspace_id === auth()->user()->current_workspace_id;

            if (!$validAccess) {
                return $this->message('Unauthorized access to this record.', 403);
            }
        }
        // Check for employee access
        if ($userTable === "emp") {
            $validAccess = $EmployeeAttendance->customer_id === auth()->user()->customer_id &&
                $EmployeeAttendance->workspace_id === auth()->user()->workspace_id;

            if (!$validAccess) {
                return $this->message('Unauthorized access to this record.', 403);
            }
        }
        if ($userTable !== "customer" && $userTable !== "emp") {
            return $this->message('Unauthorized access.', 403);
        }
        // Toggle the active status
        $EmployeeAttendance->status = $EmployeeAttendance->status == 1 ? 0 : 1;
        $EmployeeAttendance->save();

        return $this->success($EmployeeAttendance, 'Status Updated Successfully');
    }


    public function approved_all()
    {
        try {
            $userTable = $this->getUserTable();
            $query = EmployeeAttendance::query();
            if ($userTable === "customer") {
                $query->whereHas('EmpCompanyDetails', function ($q) {
                    $q->where('customer_id', auth()->id())
                        ->where('workspace_id', auth()->user()->current_workspace_id);
                });
            }
            if ($userTable === "emp") {
                $query->whereHas('EmpCompanyDetails', function ($q) {
                    $q->where('customer_id', auth()->user()->customer_id)
                        ->where('workspace_id', auth()->user()->workspace_id);
                });
            }
            if ($userTable !== "customer" && $userTable !== "emp") {
                return $this->message('Unauthorized access.', 403);
            }
            $updatedCount = $query->update(['status' => 1]);
            if ($updatedCount === 0) {
                return $this->message('No records found to approve.', 404);
            }
            return $this->success($updatedCount, 'All Requests Approved Successfully');
        } catch (\Exception $e) {
            return $this->message('Failed to approve requests. Error: ' . $e->getMessage(), 500);
        }
    }


    public function approveSelected(Request $request)
    {
        try {
            $selectedIds = $request->input('ids', []);
            if (empty($selectedIds)) {
                return $this->message('No IDs provided.', 422);
            }
            $userTable = $this->getUserTable();
            $query = EmployeeAttendance::whereIn('id', $selectedIds);
            if ($userTable === "customer") {
                $query->whereHas('EmpCompanyDetails', function ($q) {
                    $q->where('customer_id', auth()->id())
                        ->where('workspace_id', auth()->user()->current_workspace_id);
                });
            }
            if ($userTable === "emp") {
                $query->whereHas('EmpCompanyDetails', function ($q) {
                    $q->where('customer_id', auth()->user()->customer_id)
                        ->where('workspace_id', auth()->user()->workspace_id);
                });
            }
            if ($userTable !== "customer" && $userTable !== "emp") {
                return $this->message('Unauthorized access.', 403);
            }
            $updatedCount = $query->update(['status' => 1]);
            if ($updatedCount === 0) {
                return $this->message('No records found to approve.', 404);
            }
            return $this->message('Approved Selected Requests Successfully');
        } catch (\Exception $e) {
            return $this->message('Failed to approve selected requests. Error: ' . $e->getMessage(), 500);
        }
    }



    public function attendance_portal(Request $request)
    {
        $sites = Sites::where('del', 0)->where('active', 1)->get();
        return view('Attendance.attendanceRecord', compact('sites'));
    }

    public function attendance_portal_employee(Request $request)
    {
        $validator = Validator::make(
            $request->all(),
            [
                'from' => 'required',
                'to' => 'required|after_or_equal:from',
                'site_id' => 'nullable',
                'employee_name' => 'nullable'
            ],
            [
                "after_or_equal" => "To date must be bigger or equal to From"
            ]
        );
        if ($validator->fails()) {
            $error = $validator->errors()->first();
            return $this->message($error, 422);
        }
        $validatedData = $validator->validated();
        $userTable = $this->getUserTable();
        // Apply conditions based on user type
        if ($userTable === "customer") {
            $rosterAssignQuery = DB::table('roster_assigns')
                ->where('roster_assigns.customer_id', auth()->id())
                ->where('roster_assigns.workspace_id', auth()->user()->current_workspace_id);
        }

        if ($userTable === "emp") {
            $rosterAssignQuery = DB::table('roster_assigns')
                ->where('roster_assigns.customer_id', auth()->user()->customer_id)
                ->where('roster_assigns.workspace_id', auth()->user()->workspace_id);
        }

        if (!isset($rosterAssignQuery)) {
            return $this->message('Unauthorized access.', 403);
        }

        $required_minutes = $rosterAssignQuery->whereBetween('schedule_date', [$validatedData['from'], $validatedData['to']])
            ->leftjoin('roster_templates', 'roster_assigns.roster_template_id', 'roster_templates.id')
            ->sum('roster_templates.working_hours');

        $employee_attendances = EmployeeAttendance::whereBetween('date', [$validatedData['from'], $validatedData['to']]);
        // Add conditions for customer and emp to the main query
        if ($userTable === "customer") {
            $employee_attendances->whereHas('EmpCompanyDetails', function ($q) {
                $q->where('customer_id', auth()->id())
                    ->where('workspace_id', auth()->user()->current_workspace_id);
            });
        }

        if ($userTable === "emp") {
            $employee_attendances->whereHas('EmpCompanyDetails', function ($q) {
                $q->where('customer_id', auth()->user()->customer_id)
                    ->where('workspace_id', auth()->user()->workspace_id);
            });
        }

        if (!is_null($request->site_id)) {
            $employee_attendances->where('site_id', $validatedData['site_id']);
        }

        if ($request->employee_name) {
            $filterValue = explode(" ", $validatedData['employee_name']);
            $employee_attendances->whereHas('empPersonalDetails', function ($subquery) use ($filterValue) {
                if (count($filterValue) === 1) {
                    $subquery->where(function ($query) use ($filterValue) {
                        $query->where('first_name', 'like', '%' . $filterValue[0] . '%')
                            ->orWhere('middle_name', 'like', '%' . $filterValue[0] . '%')
                            ->orWhere('last_name', 'like', '%' . $filterValue[0] . '%');
                    });
                }

                if (count($filterValue) === 2) {
                    $subquery->where('first_name', 'like', '%' . $filterValue[0] . '%')
                        ->where(function ($query) use ($filterValue) {
                            $query->where('middle_name', 'like', '%' . $filterValue[1] . '%')
                                ->orWhere('last_name', 'like', '%' . $filterValue[1] . '%');
                        });
                }

                if (count($filterValue) === 3) {
                    $subquery->where('first_name', 'like', '%' . $filterValue[0] . '%')
                        ->where('middle_name', 'like', '%' . $filterValue[1] . '%')
                        ->where('last_name', 'like', '%' . $filterValue[2] . '%');
                }
            });
        }

        $working_minutes = (clone $employee_attendances)->sum('working_hours');
        $employee_count = (clone $employee_attendances)
            ->select(DB::raw('COUNT(DISTINCT employee_id) as count'))
            ->first();

        $employee_attendances_lists = (clone $employee_attendances)
            ->distinct()
            ->pluck('employee_id');

        $employee_attendances = EmployeeAttendance::query()
            ->join('emp_personal_details', 'employee_attendances.employee_id', '=', 'emp_personal_details.emp_id')
            ->join('emp_company_details', 'employee_attendances.employee_id', '=', 'emp_company_details.id')
            ->join('roles', 'emp_company_details.access_role', '=', 'roles.code')
            ->whereIn('employee_attendances.employee_id', $employee_attendances_lists)
            ->whereBetween('date', [$validatedData['from'], $validatedData['to']]);

            if (!is_null($request->site_id)) {
                $employee_attendances->where('employee_attendances.site_id', $validatedData['site_id']);
            }
            

        $employee_attendances->groupBy('employee_attendances.employee_id')
            ->select(
                'employee_attendances.employee_id as emp_id',
                DB::raw('MAX(emp_personal_details.first_name) as first_name'),
                DB::raw('MAX(emp_personal_details.middle_name) as middle_name'),
                DB::raw('MAX(emp_personal_details.last_name) as last_name'),
                DB::raw('MAX(emp_personal_details.image) as image'),
                DB::raw('MAX(emp_company_details.user_type) as user_type'),
                DB::raw('MAX(roles.title) as title')
            );
        if($request->filled('pagination') || !empty($request->filters)){
            $employee_attendances = $this->searchFilterRecord($employee_attendances, $request);
            // If searchFilterRecord was called, it returns paginated results, so we don't need to paginate again
            $query_result['employees_list'] = $employee_attendances;
            $count = $employee_attendances->total(); // Get total from paginated results
        } else {
            $count = $employee_count->count;
            $start = $request->input('page', 0);
            $employee_attendances->offset($start)->limit(10);
            $query_result['employees_list'] = $employee_attendances->get();
        }
        $query_result['workinghours'] = convertMinutesToHours($working_minutes);
        $query_result['requirehours'] = convertMinutesToHours($required_minutes);
        $query_result['employee'] = $count;

        return $this->success($query_result, 'Get Attendance List Successfully');
    }



    public function employe_attendance(Request $request)
    {
        $validator = Validator::make(
            $request->all(),
            [
                'to' => 'required|date',
                'from' => 'required|date|before_or_equal:to',
                'empid' => 'required',
            ],
            [
                'before_or_equal' => 'The from date must be before or equal to the to date.'
            ]
        );

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

        // Add conditions based on user type
        if ($userTable === "customer") {

            $attendanceQuery = EmployeeAttendance::whereHas('EmpCompanyDetails', function ($q) {
                $q->where('customer_id', auth()->id())
                    ->where('workspace_id', auth()->user()->current_workspace_id);
            });
        }
        if ($userTable === "emp") {
            $attendanceQuery = EmployeeAttendance::whereHas('EmpCompanyDetails', function ($q) {
                $q->where('customer_id', auth()->user()->customer_id)
                    ->where('workspace_id', auth()->user()->workspace_id);
            });
        }
        if (!$attendanceQuery->exists()) {
            return $this->message('Record not found', 404);
        };
        // Add additional conditions
        $attendanceQuery->where('employee_id', $validatedData['empid'])
            ->whereBetween('date', [$validatedData['from'], $validatedData['to']]);



        $data = $attendanceQuery
            ->with('RosterAssign.RosterTemplate', 'sites', 'breaks')
            ->get();
        
        if ($data->isEmpty()) {
            return $this->message('Record not found', 404);
        }

        // Get overtime data for the same employee and date range
        $overtimeQuery = Overtime::where('employee_id', $validatedData['empid'])
            ->whereBetween('date', [$validatedData['from'], $validatedData['to']]);

        // Apply same workspace/customer filters as attendance
        if ($userTable === "customer") {
            $overtimeQuery->where('customer_id', auth()->id())
                ->where('workspace_id', auth()->user()->current_workspace_id);
        }
        if ($userTable === "emp") {
            $overtimeQuery->where('customer_id', auth()->user()->customer_id)
                ->where('workspace_id', auth()->user()->workspace_id);
        }

        $overtime = $overtimeQuery->with('sites')->get();

        return $this->success([
            'attendance' => $data,
            'overtime' => $overtime
        ], 'Get Employee Attendance and Overtime List Successfully');
    }


    ////////////////Attendance Api///////////////

    public function check_roster(Request $request){
        $validator = Validator::make($request->all(), [
            'employee_id' => 'required|integer',
            'date' => 'required|date_format:Y-m-d',
        ]);

        if ($validator->fails()) {
            return $this->message($validator->errors()->first(), 422);
        }

        $validatedData = $validator->validated();

        $employeeId = $validatedData['employee_id'];
        $date = $validatedData['date'];

        // Validate that the requested employee_id matches the logged-in user's ID
        if (auth()->id() != $employeeId) {
            return $this->message('Unauthorized access to this employee\'s roster.', 403);
        }

        $userTable = $this->getUserTable();

        $employee = null;
        if ($userTable === 'emp') {
            $employee = EmpCompanyDetails::find($employeeId);
        } elseif ($userTable === 'customer') {
            $employee = User::find($employeeId);
        }

        if (!$employee) {
            return $this->message('Employee record not found.', 404);
        }

        $isAuthorized = false;
        if ($userTable === 'emp') {
            if ($employee->customer_id === auth()->user()->customer_id && $employee->workspace_id === auth()->user()->workspace_id) {
                $isAuthorized = true;
            }
        } elseif ($userTable === 'customer') {

            if ($employee->id === auth()->id() && $employee->current_workspace_id === auth()->user()->current_workspace_id) {
                 $isAuthorized = true;
             }
        } else {
             return $this->message('Unauthorized user type.', 403);
        }

        if (!$isAuthorized) {
            return $this->message('You are not authorized to access this employee\'s roster.', 403);
        }


        $roster = RosterAssign::where('assign_to', $employeeId)
                                ->where('schedule_date', $date)
                                ->with(['rosterTemplate', 'site'])
                                ->get();

        if ($roster && $roster->isNotEmpty()) {
            // Filter out entries where rosterTemplate is null after eager loading
            $filteredRosters = $roster->filter(function ($item) {
                return $item->rosterTemplate !== null;
            });

            if ($filteredRosters->isNotEmpty()) {
                 $roster = $filteredRosters->map(function ($item) {
                     $rosterTemplate = $item->rosterTemplate;
                     // Merge the schedule_date and site into the roster template data
                     if ($rosterTemplate) {
                         $rosterTemplate->schedule_date = $item->schedule_date;
                         $rosterTemplate->site = $item->site; // Add site relation
                     }
                     return $rosterTemplate;
                 });
                 return $this->success(['roster' => $roster], 'Rosters found successfully.');
            } else {
                 return $this->message('No roster templates found for the assigned rosters on the provided date.', 404);
            }

        } else {
            return $this->message('No roster found for this employee on the provided date.', 404);
        }
    }
    public function check_in(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'employee_id' => 'required',
            'longitude' => 'required',
            'latitude' => 'required',
            'date' => 'required|date',
            'check_in' => 'nullable|date_format:H:i',
            'check_out' => 'nullable|date_format:H:i|after:check_in',
            'site_id' => 'nullable',
        ]);

        if ($validator->fails()) {
            return $this->message($validator->errors()->first(), 422);
        }

        $validatedData = $validator->validated();
        $userTable = $this->getUserTable();
        if ($userTable === "customer") {
            $authorized = EmployeeAttendance::whereHas('EmpCompanyDetails', function ($q) use ($validatedData) {
                $q->where('customer_id', auth()->id())
                    ->where('workspace_id', auth()->user()->current_workspace_id)
                    ->where('employee_id', $validatedData['employee_id']);
            })->exists();

            if (!$authorized) {
                return response()->json(['message' => 'Unauthorized access to this employee.'], 403);
            }
        }
        if ($userTable === "emp") {
            $authorized = EmployeeAttendance::whereHas('EmpCompanyDetails', function ($q) use ($validatedData) {
                $q->where('customer_id', auth()->user()->customer_id)
                    ->where('workspace_id', auth()->user()->workspace_id)
                    ->where('employee_id', $validatedData['employee_id']);
            })->exists();

            if (!$authorized) {
                return response()->json(['message' => 'Unauthorized access to this employee.'], 403);
            }
        }
        $check_leave = LeaveRequest::where('employee_id', $request->employee_id)
            ->where('status', 1)
            ->whereBetween('from', [date('Y-m-d'), date('Y-m-d')])
            ->exists();
        if ($check_leave) {
            return response()->json([
                'message' => 'You are on leave. You are not allowed to check-in.'
            ], 422);
        }
        $site = Sites::whereRaw("CONVERT(longitude, CHAR) LIKE CONCAT('%', ?, '%')", [$validatedData['longitude']])
            ->whereRaw("CONVERT(latitude, CHAR) LIKE CONCAT('%', ?, '%')", [$validatedData['latitude']])
            ->select('id')
            ->first();

        $site_id = $site ? $site->id : null;
        $data = [
            'employee_id' => $validatedData['employee_id'],
            'longitude' => $validatedData['longitude'],
            'latitude' =>  $validatedData['latitude'],
            'date' => $validatedData['date'],
            'site_id' => $site_id,
            'customer_id' => $authorized->customer_id,
            'workspace_id' => $authorized->workspace_id
        ];
        if ($request->has('check_in')) {
            $data['check_in'] = $request->input('check_in');
        }
        if ($request->has('check_out')) {
            $data['check_out'] = $request->input('check_out');
        }
        $existingData = EmployeeAttendance::where('employee_id', $data['employee_id'])
            ->where('date', $data['date'])
            ->first();
        if ($request->has('check_out')) {
            if ($existingData && !$existingData->check_in) {
                return $this->message('Cannot check out without checking in first.', 422);
            }

            if ($existingData) {
                $checkIn = Carbon::parse($existingData->check_in);
                $checkOut = Carbon::parse($validatedData['check_out']);
                $totalHours = $checkOut->diffInMinutes($checkIn);
                $existingData->update([
                    'check_out' => $data['check_out'],
                    'working_hours' => $totalHours
                ]);
                $message = 'Check Out successfully';
            } else {
                return $this->message('Cannot check out without checking in first.', 422);
            }
        } else {
            if ($existingData) {
                $message = 'Check In already exists';
            } else {
                EmployeeAttendance::create($data);
                $message = 'Check In successfully';
            }
        }
        return $this->success($existingData, $message);
    }



    public function _check_in(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'employee_id' => 'required',
            'longitude' => 'required|numeric',
            'latitude' => 'required|numeric',
            'date' => 'nullable|date',
            'check_in' => 'date_format:H:i',
            'site_id' => 'nullable',
            'image' => 'nullable|mimes:jpeg,png,jpg,gif|max:7168',
        ]); 
        $currentTime = now()->format('H:i:s');
        $current_date = now()->format('Y-m-d');
        $validatedData['date'] = $current_date;

        if ($validator->fails()) {
            $error = $validator->errors()->first();
            return $this->message($error, 422);
        }
        $validatedData = $validator->validated();
        $userTable = $this->getUserTable();
        if ($userTable === "customer") {
            $employeeDetails = EmpCompanyDetails::where('id', $validatedData['employee_id'])
                ->where('customer_id', auth()->id())
                ->where('workspace_id', auth()->user()->current_workspace_id)
                ->first();
            if (!$employeeDetails) {
                return $this->message('Unauthorized access to this employee.', 403);
            }
        }
        if ($userTable === "emp") {
            $employeeDetails = EmpCompanyDetails::where('id', $validatedData['employee_id'])
                ->where('customer_id', auth()->user()->customer_id)
                ->where('workspace_id', auth()->user()->workspace_id)
                ->first();
            if (!$employeeDetails) {
                return $this->message('Unauthorized access to this employee.', 403);
            }
        }
        if (Auth::user()->id != $validatedData['employee_id']) {
            return $this->message('Invalid Request.', 422);
        }

        $check_roster = RosterAssign::join('roster_templates', 'roster_assigns.roster_template_id', 'roster_templates.id')
            ->where('roster_assigns.assign_to', Auth::user()->id)
            ->where('schedule_date', date('Y-m-d'))
            ->where('roster_templates.start_time', "<=", $currentTime)
            ->where('roster_templates.end_time', ">=", $currentTime)
            ->exists();

        if (!$check_roster) {
            $message = "Please clock in according to your scheduled roster time.";

            // Create notification for invalid roster time
            $notificationTitle = "Invalid Check-in Time";
            $notificationMessage = "Employee " . Auth::user()->name . " attempted to check in outside scheduled roster time at " . $currentTime;
            
            // // Send notification to customer/admin
            // $this->save_notifications(
            //     $notificationTitle,
            //     $notificationMessage,
            //     Auth::user()->id,
            //     $employeeDetails->customer_id,
            //     'attendance_invalid_time',
            //     $employeeDetails->customer_id,
            //     $employeeDetails->workspace_id
            // );

            /////////////////// ATTENDANCE LOGS ////////////////////
            $log = [
                'employee_id' => Auth::user()->id,
                'site_id' => null,
                'check_in' => $currentTime,
                'check_out' => null,
                'date' => formatDate($request->date),
                'longitude' => $validatedData['longitude'],
                'latitude' => $validatedData['latitude'],
                'message' => $message,
                'error_type' => 'invalid-roster-time',
                'request_type' => 'attendance',
                'customer_id' => $employeeDetails->customer_id,
                'workspace_id' => $employeeDetails->workspace_id
            ];
            storeAttendanceLogs($log);
            /////////////////// ATTENDANCE LOGS END ////////////////////
            return $this->message($message, 422);
        }
        $point = [
            'longitude' => (float) $validatedData['longitude'],
            'latitude' => (float) $validatedData['latitude'],
        ];
        $sites = Sites::where('customer_id', $employeeDetails->customer_id)->where('workspace_id', $employeeDetails->workspace_id)->where('active', 1)->where('del',0)->get();
        $foundSite = null;
        foreach ($sites as $site) {
            $center = [
                'longitude' => (float) $site->longitude,
                'latitude' => (float) $site->latitude,
            ];
            $radius = (float) $site->area_radius;
            if (GeoLoacation::isPointWithinRadius($point, $center, $radius)) {
                $foundSite = $site;
                break;
            }
        }
        $rosterAssign = RosterAssign::join('roster_templates', 'roster_assigns.roster_template_id', 'roster_templates.id')
        ->where('roster_assigns.assign_to', Auth::user()->id)
        ->where('schedule_date', date('Y-m-d'))
        ->where('roster_templates.start_time', "<=", $currentTime)
        ->where('roster_templates.end_time', ">=", $currentTime)
        ->where('roster_assigns.site_id', '!=', null)
        ->select('roster_assigns.site_id')->first();
        if (!$foundSite) {
            $data = [
                'employee_location' => $point,
                'all_sites' => $sites
            ];
            $assignedSite = Sites::where('id', $rosterAssign->site_id ?? null)->select('title')->first();
            $assignedSiteName = $assignedSite->title ?? 'any';
            $message = 'The provided location is outside the permitted radius of the '.$assignedSiteName.' site.';
            
            // Create notification for invalid location
            $notificationTitle = "Invalid Check-in Location";
            $notificationMessage = "Employee " . Auth::user()->name . " attempted to check in from outside permitted site radius. Location: " . $validatedData['latitude'] . ", " . $validatedData['longitude'];
            
            // Send notification to customer/admin
            // $this->save_notifications(
            //     $notificationTitle,
            //     $notificationMessage,
            //     Auth::user()->id,
            //     $employeeDetails->customer_id,
            //     'attendance_invalid_location',
            //     $employeeDetails->customer_id,
            //     $employeeDetails->workspace_id
            // );
            
            /////////////////// ATTENDANCE LOGS ////////////////////
            $log = [
                'employee_id' => Auth::user()->id,
                'site_id' => null,
                'check_in' => $currentTime,
                'check_out' => null,
                'date' => formatDate($request->date),
                'longitude' => $validatedData['longitude'],
                'latitude' => $validatedData['latitude'],
                'message' => $message,
                'error_type' => 'invalid-location',
                'request_type' => 'attendance',
                'customer_id' => $employeeDetails->customer_id,
                'workspace_id' => $employeeDetails->workspace_id
            ];
            storeAttendanceLogs($log);
            /////////////////// ATTENDANCE LOGS END ////////////////////
            return $this->message($message, 422);
        }

        
        if($rosterAssign && $foundSite->id != $rosterAssign->site_id)
        {
            $assignedSite = Sites::where('id', $rosterAssign->site_id)->select('title')->first();
            $attemptedSiteName = $foundSite->title ?? 'Unknown Site';
            $assignedSiteName = $assignedSite->title ?? 'Unknown Site';
            
            $message = 'You are not assigned to this site. You are trying to check in at: ' . $attemptedSiteName . '. Your assigned site is: ' . $assignedSiteName . '.';
            
            /////////////////// ATTENDANCE LOGS ////////////////////
            $log = [
                'employee_id' => Auth::user()->id,
                'site_id' => $rosterAssign->site_id,
                'check_in' => $currentTime,
                'check_out' => null,
                'date' => formatDate($request->date),
                'longitude' => $validatedData['longitude'],
                'latitude' => $validatedData['latitude'],
                'message' => $message,
                'error_type' => 'site-not-assigned',
                'request_type' => 'attendance',
                'customer_id' => $employeeDetails->customer_id,
                'workspace_id' => $employeeDetails->workspace_id
            ];
            storeAttendanceLogs($log);
            /////////////////// ATTENDANCE LOGS END ////////////////////
            return $this->message($message, 422);
        }
        $previousRecord = EmployeeAttendance::where('employee_id', Auth::user()->id)
            ->where('date', $validatedData['date'])
            ->latest()
            ->first();
        if ($previousRecord && !$previousRecord->check_out) {
            $message = 'You are already checked in at a site. Please check out first before checking in again.';

            // Create notification for duplicate check-in attempt
            $notificationTitle = "Duplicate Check-in Attempt";
            $notificationMessage = "Employee " . Auth::user()->name . " attempted to check in again while already checked in at site ID: " . $previousRecord->site_id;
            
            // Send notification to customer/admin
            // $this->save_notifications(
            //     $notificationTitle,
            //     $notificationMessage,
            //     Auth::user()->id,
            //     $employeeDetails->customer_id,
            //     'attendance_duplicate_checkin',
            //     $employeeDetails->customer_id,
            //     $employeeDetails->workspace_id
            // );

            /////////////////// ATTENDANCE LOGS ////////////////////
            $log = [
                'employee_id' => Auth::user()->id,
                'site_id' => $foundSite->id,
                'check_in' => $currentTime,
                'check_out' => null,
                'date' => formatDate($request->date),
                'longitude' => $validatedData['longitude'],
                'latitude' => $validatedData['latitude'],
                'message' => $message,
                'error_type' => 'already-check-in',
                'request_type' => 'attendance',
                'customer_id' => $employeeDetails->customer_id,
                'workspace_id' => $employeeDetails->workspace_id
            ];
            storeAttendanceLogs($log);
            /////////////////// ATTENDANCE LOGS END ////////////////////
            return $this->success($previousRecord->id, $message, 422);
        }

        // Check for required documents that need to be signed before check-in
        $requiredDocuments = $this->checkRequiredDocumentsForCheckIn(
            $foundSite->id,
            Auth::user()->id,
            $employeeDetails->customer_id,
            $employeeDetails->workspace_id
        );

        if (!empty($requiredDocuments)) {
            // Check-in interrupted - documents require signature
            $message = 'Please sign the required documents before checking in.';
            
            /////////////////// ATTENDANCE LOGS ////////////////////
            $log = [
                'employee_id' => Auth::user()->id,
                'site_id' => $foundSite->id,
                'check_in' => $currentTime,
                'check_out' => null,
                'date' => formatDate($request->date),
                'longitude' => $validatedData['longitude'],
                'latitude' => $validatedData['latitude'],
                'message' => $message,
                'error_type' => 'documents-require-signature',
                'request_type' => 'attendance',
                'customer_id' => $employeeDetails->customer_id,
                'workspace_id' => $employeeDetails->workspace_id
            ];
            storeAttendanceLogs($log);
            /////////////////// ATTENDANCE LOGS END ////////////////////
            $data = [
                'requires_signature' => true,
                'documents' => $requiredDocuments,
            ];
            return $this->message($data, 422);
        }

        // $validatedData['image'] = "";
        if ($request->hasFile('image')) {

            $validatedData['image'] = $this->handleFileImageUpload($request, 'AttendanceLog')['path'] ?? null;;
        }
        $data = [
            'employee_id' => Auth::user()->id,
            'date'       => $validatedData['date'],
            'check_in'   => $currentTime,
            'longitude'  => $validatedData['longitude'],
            'latitude'   => $validatedData['latitude'],
            'status'     => 1,
            'site_id'    => $foundSite->id,
            'added_by'   => Auth::user()->id,
            'image'      => $validatedData['image'],
            'customer_id' => $employeeDetails->customer_id,
            'workspace_id' => $employeeDetails->workspace_id
        ];

        $checkin = EmployeeAttendance::create($data);
        $message = 'Check-in Successfully';
        
        // Create notification for successful check-in
        $notificationTitle = "Check-in Successful";
        $notificationMessage = "Employee " . Auth::user()->name . " checked in at " . $foundSite->title . " at " . $currentTime;
        
        // Send notification to customer/admin
        // $this->save_notifications(
        //     $notificationTitle,
        //     $notificationMessage,
        //     Auth::user()->id,
        //     $employeeDetails->customer_id,
        //     'attendance_check_in',
        //     $employeeDetails->customer_id,
        //     $employeeDetails->workspace_id
        // );
        
        ///////////////// ATTENDANCE LOGS ////////////////////
        $log = [
            'employee_id' => Auth::user()->id,
            'site_id' => $foundSite->id,
            'check_in' => $currentTime,
            'check_out' => null,
            'date' => formatDate($request->date),
            'longitude' => $validatedData['longitude'],
            'latitude' => $validatedData['latitude'],
            'customer_id' => $employeeDetails->customer_id,
            'workspace_id' => $employeeDetails->workspace_id,
            'message' => $message,
            'error_type' => 'success',
            'request_type' => 'attendance'
        ];
        storeAttendanceLogs($log);
        /////////////////// ATTENDANCE LOGS END ////////////////////
        return $this->success($checkin, $message);
    }

    public function _check_out(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'checkin_id' => 'required',
            'check_out' => 'date_format:H:i',
            'longitude' => 'required|numeric',
            'latitude' => 'required|numeric',
            'date' => 'required|date',
        ]);
        $currentTime = now()->format('H:i:s');

        if ($validator->fails()) {
            $error = $validator->errors()->first();
            return $this->message($error, 422);
        }
        $validatedData = $validator->validated();
        $userTable = $this->getUserTable();
        if ($userTable === "customer") {
            $employeeDetails = EmpCompanyDetails::where('id', auth()->id())
                ->where('customer_id', auth()->id())
                ->where('workspace_id', auth()->user()->current_workspace_id)
                ->first();
            if (!$employeeDetails) {
                return $this->message('Unauthorized access to this employee.', 403);
            }
        }
        if ($userTable === "emp") {
            $employeeDetails = EmpCompanyDetails::where('id', auth()->user()->id)
                ->where('customer_id', auth()->user()->customer_id)
                ->where('workspace_id', auth()->user()->workspace_id)
                ->first();
            if (!$employeeDetails) {
                return $this->message('Unauthorized access to this employee.', 403);
            }
        }
        $attendanceRecord = EmployeeAttendance::where([
            ['id', $validatedData['checkin_id']],
            ['check_out', null]
        ])->first();

        if (!$attendanceRecord) {
            $attendanceRecordWithCheckout = EmployeeAttendance::where('id', $validatedData['checkin_id'])->where('check_out', '!=', null)->first();
            if ($attendanceRecordWithCheckout) {
                $message = 'Your shift has been auto logout from the system at ' . formatTime($attendanceRecordWithCheckout->check_out);
                $is_checkout = 1;
            } else {
                $message = 'Check-in required before check-out. Please complete your check-in before proceeding with check-out.';
                $is_checkout = 1;
            }
            /////////////////// ATTENDANCE LOGS ////////////////////
            $log = [
                'employee_id' => Auth::user()->id,
                'site_id' => null,
                'check_in' => null,
                'customer_id' => $employeeDetails->customer_id,
                'workspace_id' => $employeeDetails->workspace_id,
                'check_out' => $currentTime,
                'date' => formatDate($validatedData['date']),
                'longitude' => $validatedData['longitude'],
                'latitude' => $validatedData['latitude'],
                'message' => $message,
                'error_type' => 'check-in-missed',
                'request_type' => 'attendance'
            ];
            storeAttendanceLogs($log);
            /////////////////// ATTENDANCE LOGS END ////////////////////
            return $this->success($is_checkout, $message, 422);
        }

        //////////////////////
        $point = [
            'longitude' => (float) $validatedData['longitude'],
            'latitude' => (float) $validatedData['latitude'],
        ];

        $site = Sites::find($attendanceRecord->site_id);
        // return  $site;
        if (!$site) {
            $message = "The site you are trying to check out from is not available now.";
            /////////////////// ATTENDANCE LOGS ////////////////////
            $log = [
                'employee_id' => Auth::user()->id,
                'customer_id' => $employeeDetails->customer_id,
                'workspace_id' => $employeeDetails->workspace_id,
                'site_id' => $attendanceRecord->site_id,
                'check_in' => $attendanceRecord->check_in,
                'check_out' => $currentTime,
                'date' => formatDate($validatedData['date']),
                'longitude' => $validatedData['longitude'],
                'latitude' => $validatedData['latitude'],
                'message' => $message,
                'error_type' => 'site-not-available',
                'request_type' => 'attendance'
            ];
            storeAttendanceLogs($log);
            /////////////////// ATTENDANCE LOGS END ////////////////////
            return $this->message($message, 422);
        }

        $center = [
            'longitude' => (float) $site->longitude,
            'latitude' => (float) $site->latitude,
        ];
        $radius = (float) $site->area_radius;
        $allSites = Sites::where('customer_id', $employeeDetails->customer_id)->where('workspace_id', $employeeDetails->workspace_id)->where('active', 1)->where('del',0)->get();
        $data = [];
        $data = [
            'employee_location' => $point,
            'site_location' => $center,
            'site_radius' => $radius,
            'all_sites' => $allSites
        ];

        $foundSite = null;

        foreach ($allSites as $allSite) {
            $center = [
                'longitude' => (float) $allSite->longitude,
                'latitude' => (float) $allSite->latitude,
            ];
            $radius = (float) $allSite->area_radius;

            if (GeoLoacation::isPointWithinRadius($point, $center, $radius)) {
                $foundSite = $allSite;
                break;
            }
        }
        if (!$foundSite) {
            $message = 'You are trying to check out outside from the site. Your assigned site is: ' . $site->title . '.';

            /////////////////// ATTENDANCE LOGS ////////////////////
            $log = [
                'employee_id' => Auth::user()->id,
                'customer_id' => $employeeDetails->customer_id,
                'workspace_id' => $employeeDetails->workspace_id,
                'site_id' => $attendanceRecord->site_id,
                'check_in' => $attendanceRecord->check_in,
                'check_out' => $currentTime,
                'date' => formatDate($validatedData['date']),
                'longitude' => $validatedData['longitude'],
                'latitude' => $validatedData['latitude'],
                'message' => $message,
                'error_type' => 'site-area-invalid',
                'request_type' => 'attendance'
            ];
            storeAttendanceLogs($log);
            /////////////////// ATTENDANCE LOGS END ////////////////////
            return $this->message($message, 422);
        }

        if ($foundSite && $foundSite->id !== $site->id) {
            $message = 'You are trying to check out from the site: ' . $foundSite->title . '. Your assigned site is: ' . $site->title . '.';
            /////////////////// ATTENDANCE LOGS ////////////////////
            $log = [
                'employee_id' => Auth::user()->id,
                'customer_id' => $employeeDetails->customer_id,
                'workspace_id' => $employeeDetails->workspace_id,
                'site_id' => $attendanceRecord->site_id,
                'check_in' => $attendanceRecord->check_in,
                'check_out' => $currentTime,
                'date' => formatDate($validatedData['date']),
                'longitude' => $validatedData['longitude'],
                'latitude' => $validatedData['latitude'],
                'message' => $message,
                'error_type' => 'site-area-invalid',
                'request_type' => 'attendance'
            ];
            storeAttendanceLogs($log);
            /////////////////// ATTENDANCE LOGS END ////////////////////
            return $this->message($message, 422);
        }

        /////////////////////


        // $checkIn = Carbon::parse($attendanceRecord->check_in);
        // $checkOut = Carbon::parse($validatedData['check_out']);
        
        // Ensure we have clean date and time values before concatenation
        $checkInDate = is_string($attendanceRecord->date) ? $attendanceRecord->date : $attendanceRecord->date->format('Y-m-d');
        $checkOutDate = is_string($validatedData['date']) ? $validatedData['date'] : $validatedData['date']->format('Y-m-d');
        
        $checkIn = \App\Models\BaseModel::safeCarbonParse($checkInDate . ' ' . $attendanceRecord->check_in, 'AttendanceController checkIn concatenated');
        $checkOut = \App\Models\BaseModel::safeCarbonParse($checkOutDate . ' ' . $validatedData['check_out'], 'AttendanceController checkOut concatenated');

        $totalHours = $checkOut->diffInMinutes($checkIn);

        if ($checkOut->lte($checkIn)) {
            $message = 'Check-out time must come after check-in time.';
            /////////////////// ATTENDANCE LOGS ////////////////////
            $log = [
                'employee_id' => Auth::user()->id,
                'customer_id' => $employeeDetails->customer_id,
                'workspace_id' => $employeeDetails->workspace_id,
                'site_id' => $attendanceRecord->site_id,
                'check_in' => $attendanceRecord->check_in,
                'check_out' => $currentTime,
                'date' => formatDate($validatedData['date']),
                'longitude' => $validatedData['longitude'],
                'latitude' => $validatedData['latitude'],
                'message' => $message,
                'error_type' => 'check-out-time-invalid',
                'request_type' => 'attendance'
            ];
            storeAttendanceLogs($log);
            /////////////////// ATTENDANCE LOGS END ////////////////////
            return $this->message($message, 422);
        }

        $breaks = EmployeeBreak::where('emp_attendance_id', $attendanceRecord->id)->get();
        // Calculate total breaks
        $totalBreaks = 0;
        $totalWorkingHours = 0;
        if (isset($breaks)) {
            foreach ($breaks as $break) {
                $breakInTime = Carbon::parse($break->break_in);
                $breakOutTime = Carbon::parse($break->break_out);
                $totalBreaks += $breakOutTime->diffInMinutes($breakInTime);
            }
        }
        // Calculate total working hours with breaks
        $totalWorkingHours = $totalHours - $totalBreaks;

        // $checkIn = Carbon::parse($attendanceRecord->check_in);
        // $checkOut = Carbon::parse($validatedData['check_out']);
        // $totalHours = $checkOut->diffInMinutes($checkIn);

        $attendanceRecord->update([
            'check_out' => $currentTime,
            'working_hours' => $totalWorkingHours
        ]);

        $message = 'Checked Out at '. $currentTime;
        
        // Create notification for successful check-out
        $notificationTitle = "Check-out Successful";
        $notificationMessage = "Employee " . Auth::user()->name . " checked out at " . $foundSite->title . " at " . $currentTime . ". Total working hours: " . round($totalWorkingHours / 60, 2) . " hours";
        
        // Send notification to customer/admin
        $this->save_notifications(
            $notificationTitle,
                $notificationMessage,
                Auth::user()->id,
                $employeeDetails->customer_id,
                'attendance_check_out',
                $employeeDetails->customer_id,
                $employeeDetails->workspace_id
        );
        
        ///////////////// ATTENDANCE LOGS ////////////////////  
        $log = [
            'employee_id' => Auth::user()->id,
            'site_id' => $attendanceRecord->site_id,
            'customer_id' => $employeeDetails->customer_id,
            'workspace_id' => $employeeDetails->workspace_id,
            'check_in' => $attendanceRecord->check_in,
            'check_out' => $currentTime,
            'date' => formatDate($validatedData['date']),
            'longitude' => $validatedData['longitude'],
            'latitude' => $validatedData['latitude'],
            'message' => $message,
            'error_type' => 'success',
            'request_type' => 'attendance'
        ];
        /////////////////// ATTENDANCE LOGS END ////////////////////
        storeAttendanceLogs($log);
        return $this->success($attendanceRecord, $message);
    }


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

        if ($validator->fails()) {
            return $this->message($validator->errors()->first(), 422);
        }
        
        $validatedData = $validator->validated();
        $userTable = $this->getUserTable();
        
        if ($userTable !== "customer" && $userTable !== "emp") {
            return $this->message('Unauthorized access.', 403);
        }
        
        // Build the query to get the last attendance record for the employee
        $query = EmployeeAttendance::query();
        
        if ($userTable === "customer") {
            $query->whereHas('EmpCompanyDetails', function ($q) use ($validatedData) {
                $q->where('customer_id', auth()->id())
                    ->where('workspace_id', auth()->user()->current_workspace_id)
                    ->where('id', $validatedData['employee_id']);
            });
        }

        if ($userTable === "emp") {
            $query->whereHas('EmpCompanyDetails', function ($q) use ($validatedData) {
                $q->where('customer_id', auth()->user()->customer_id)
                    ->where('workspace_id', auth()->user()->workspace_id)
                    ->where('id', $validatedData['employee_id']);
            });
        }
        
        // Get the last attendance record for the employee
        $lastCheckin = $query->where('employee_id', $validatedData['employee_id'])
            ->latest()
            ->first();

        if (!$lastCheckin) {
            return $this->message('No attendance record found for this employee.', 200);
        }

        // Return the last attendance record with appropriate message
        if ($lastCheckin->check_in && !$lastCheckin->check_out) {
            return $this->success($lastCheckin, 'Employee is currently checked in');
        } elseif ($lastCheckin->check_in && $lastCheckin->check_out) {
            return $this->success($lastCheckin, 'Last attendance record - employee has checked out');
        } else {
            return $this->success($lastCheckin, 'Last attendance record');
        }
    }

    public function break_in(Request $request)
    {
        $currentTime = now()->format('H:i:s');
        $validator = Validator::make($request->all(), [
            'emp_attendance_id' => 'required|exists:employee_attendances,id',
            'break_in' => 'required|date_format:H:i',
            'date' => 'required|date',
        ]);
        if ($validator->fails()) {
            return $this->message($validator->errors()->first(), 422);
        }
        $validatedData = $validator->validated();
        $userTable = $this->getUserTable();
        $attendanceRecord = EmployeeAttendance::where('id', $validatedData['emp_attendance_id']);

        if ($userTable === "customer") {
            $attendanceRecord->where('customer_id', auth()->id())
                ->where('workspace_id', auth()->user()->current_workspace_id);
        }
        if ($userTable === "emp") {
            $attendanceRecord->where('customer_id', auth()->user()->customer_id)
                ->where('workspace_id', auth()->user()->workspace_id);
        }
        if ($userTable !== "customer" && $userTable !== "emp") {
            return $this->message('Unauthorized access ', 403);
        }
        $attendanceRecord = $attendanceRecord->first();
        if (!$attendanceRecord) {
            return $this->message('Attendance record not found ', 404);
        }
        $data = [
            'emp_attendance_id' => $validatedData['emp_attendance_id'],
            'break_in' => $currentTime,
            'date' => $validatedData['date'],
        ];
        $breakIn = EmployeeBreak::create($data);
        return $this->success($breakIn, 'Break In successfully');
    }


    public function last_break_in(Request $request)
    {
        // dd($request->all());
        $validator = Validator::make($request->all(), [
            'emp_attendance_id' => 'required|exists:employee_attendances,id',
        ]);
        if ($validator->fails()) {
            return $this->message($validator->errors()->first(), 422);
        }
        $validatedData = $validator->validated();
        $userTable = $this->getUserTable();
        $attendanceRecord = EmployeeAttendance::where('id', $validatedData['emp_attendance_id']);

        if ($userTable === "customer") {
            $attendanceRecord->where('customer_id', auth()->id())
                ->where('workspace_id', auth()->user()->current_workspace_id);
        }
        if ($userTable === "emp") {
            $attendanceRecord->where('customer_id', auth()->user()->customer_id)
                ->where('workspace_id', auth()->user()->workspace_id);
        }
        if ($userTable !== "customer" && $userTable !== "emp") {
            return $this->message('Unauthorized access.', 403);
        }
        $attendanceRecord = $attendanceRecord->first();
        if (!$attendanceRecord) {
            return $this->message('Attendance record not found', 200);
        }
        $lastBreakIn = EmployeeBreak::where('emp_attendance_id', $validatedData['emp_attendance_id'])
            ->latest()
            ->first();

        if ($lastBreakIn) {
            if (is_null($lastBreakIn->break_out)) {
                return $this->success($lastBreakIn, 'Last open break found.', 200);
            } else {
                return $this->message('No open break found.', 200);
            }
        }

        return $this->message('No break records found for this attendance.', 200);
    }


    public function break_out(Request $request)
    {
        $currentTime = now()->format('H:i:s');
        $validator = Validator::make($request->all(), [
            'breakin_id' => 'required|exists:employee_breaks,id',
            'break_out' => 'required|date_format:H:i',
        ]);
        if ($validator->fails()) {
            return $this->message($validator->errors()->first(), 404);
        }
        $validatedData = $validator->validated();
        $attendanceRecord = EmployeeBreak::find($validatedData['breakin_id']);
        if (!$attendanceRecord) {
            return $this->message(' Break-in No break found', 404);
        }
        $userTable = $this->getUserTable();
        $employeeAttendance = $attendanceRecord->employeeAttendance;

        if (!$employeeAttendance) {
            return $this->message('Associated attendance record not found', 404);
        }
        if (
            ($userTable === "customer" &&
                ($employeeAttendance->customer_id !== auth()->id() ||
                    $employeeAttendance->workspace_id !== auth()->user()->current_workspace_id)) ||
            ($userTable === "emp" &&
                ($employeeAttendance->customer_id !== auth()->user()->customer_id ||
                    $employeeAttendance->workspace_id !== auth()->user()->workspace_id))
        ) {
            return $this->message('Unauthorized access to this record', 403);
        }
        // dd($employeeAttendance);
        // Update the break-out time
        $attendanceRecord->update([
            'break_out' => $currentTime,
        ]);
        return $this->success($attendanceRecord, 'Break Out successfully');
    }

    public function checkin_history(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'employee_id' => 'required',
            'start_date' => 'required|date',
            'emp_attendance_id' => 'required|exists:employee_attendances,id',
        ]);

        if ($validator->fails()) {
            $error = $validator->errors()->first();
            return $this->message($error, 422);
        }

        $validatedData = $validator->validated();

        $userTable = $this->getUserTable();

        $empattendanceQuery = EmployeeAttendance::where('employee_id', $validatedData['employee_id'])
            ->where('date', $validatedData['start_date'])
            ->where(function ($query) use ($validatedData) {
                $query->whereHas('breaks', function ($innerQuery) use ($validatedData) {
                    $innerQuery->where('emp_attendance_id', $validatedData['emp_attendance_id']);
                })
                    ->orWhereDoesntHave('breaks');
            });

        // Apply conditions for customer and emp
        if ($userTable === "customer") {
            $empattendanceQuery->where('customer_id', auth()->id())
                ->where('workspace_id', auth()->user()->current_workspace_id);
        }

        if ($userTable === "emp") {
            $empattendanceQuery->where('customer_id', auth()->user()->customer_id)
                ->where('workspace_id', auth()->user()->workspace_id);
        }

        if ($userTable !== "customer" && $userTable !== "emp") {
            return $this->message('Unauthorized access.', 403);
        }

        $empattendance = $empattendanceQuery->with([
            'breaks' => function ($query) use ($validatedData) {
                $query->where('emp_attendance_id', $validatedData['emp_attendance_id']);
            },
            'sites' => function ($query) {
                $query->select('id', 'title', 'street_address');
            }
        ])
            ->latest('id')
            ->first();
        if ($empattendance) {
            // Convert to array to format times with seconds
            $attendanceData = $empattendance->toArray();
            
            // Get raw database values for check_in and check_out to preserve seconds
            $rawCheckIn = $empattendance->getRawOriginal('check_in');
            $rawCheckOut = $empattendance->getRawOriginal('check_out');
            
            // Format check_in with seconds (H:i:s) from raw database value
            if ($rawCheckIn) {
                try {
                    // The raw value should already be in H:i:s format from database
                    // If it's already in the correct format, use it directly
                    if (preg_match('/^(\d{1,2}):(\d{2}):(\d{2})$/', $rawCheckIn)) {
                        $attendanceData['check_in'] = $rawCheckIn;
                    } elseif (preg_match('/^(\d{1,2}):(\d{2})$/', $rawCheckIn, $matches)) {
                        // If it's missing seconds, add :00
                        $attendanceData['check_in'] = $rawCheckIn . ':00';
                    } else {
                        // Parse and format with seconds
                        $attendanceData['check_in'] = Carbon::parse($rawCheckIn)->format('H:i:s');
                    }
                } catch (\Exception $e) {
                    // Keep original formatted value if parsing fails
                }
            }
            
            // Format check_out with seconds (H:i:s) from raw database value
            if ($rawCheckOut) {
                try {
                    // The raw value should already be in H:i:s format from database
                    // If it's already in the correct format, use it directly
                    if (preg_match('/^(\d{1,2}):(\d{2}):(\d{2})$/', $rawCheckOut)) {
                        $attendanceData['check_out'] = $rawCheckOut;
                    } elseif (preg_match('/^(\d{1,2}):(\d{2})$/', $rawCheckOut, $matches)) {
                        // If it's missing seconds, add :00
                        $attendanceData['check_out'] = $rawCheckOut . ':00';
                    } else {
                        // Parse and format with seconds
                        $attendanceData['check_out'] = Carbon::parse($rawCheckOut)->format('H:i:s');
                    }
                } catch (\Exception $e) {
                    // Keep original formatted value if parsing fails
                }
            }
            
            return $this->success($attendanceData, 'User Check In History');
        }

        return $this->message('No records found for the specified date range', 404);
    }


    public function attendance_list(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'employee_id' => 'required|exists:employee_attendances,employee_id',
        ]);
        if ($validator->fails()) {
            return $this->message($validator->errors()->first(), 422);
        }
        $validatedData = $validator->validated();
        $userTable = $this->getUserTable();
        $firstDayOfPreviousMonth = Carbon::now()->subMonth()->startOfMonth();
        $lastDayOfPreviousMonth = Carbon::now()->subMonth()->endOfMonth();
        $empattendanceQuery = EmployeeAttendance::where('employee_id', $validatedData['employee_id'])
            ->whereBetween('date', [$firstDayOfPreviousMonth, $lastDayOfPreviousMonth]);
        if ($userTable === "customer") {
            $empattendanceQuery->where('customer_id', auth()->id())
                ->where('workspace_id', auth()->user()->current_workspace_id);
        }
        if ($userTable === "emp") {
            $empattendanceQuery->where('customer_id', auth()->user()->customer_id)
                ->where('workspace_id', auth()->user()->workspace_id);
        }
        if ($userTable !== "customer" && $userTable !== "emp") {
            return $this->message('Unauthorized access.', 403);
        }
        $empattendance = $empattendanceQuery->get();

        if ($empattendance->count() > 0) {
            return $this->success($empattendance, 'User Attendance List for the previous month');
        }
        return $this->message('No Record Found for the previous month.', 404);
    }

    ////////////////Attendance Api///////////////

    ///////////////// CRON JOB //////////////////
    public function autoCheckout(Request $request)
    {

        $date_from = Carbon::now()->subDays(2)->format('Y-m-d');
        $current_date = Carbon::now()->format('Y-m-d');
        $current_datetime = Carbon::now();

        $employee_ids = EmployeeAttendance::whereNotNull('check_in')
            ->whereNull('check_out')
            ->where('date', ">=", $date_from)
            ->selectRaw('id, employee_id, DATE(date) as date')
            ->get();

        Log::info(date('d-m-y H:i:s') . " Employees who did not checkout: " . $employee_ids);
        
        $update_attendance = $delete_attendance = $check = 0;
        $all_employee_id = [];
        
        foreach ($employee_ids as $value) {
            try {

                $attendanceDate = $value->date;
                
                if (is_string($attendanceDate)) {
                    try {
                        // Try to parse the date - it might be in d-m-Y format or already in Y-m-d
                        $parsedDate = Carbon::parse($attendanceDate);
                        $attendanceDate = $parsedDate->format('Y-m-d');
                    } catch (\Exception $e) {
                        // If parsing fails, try to get the raw date from database
                        $rawAttendance = EmployeeAttendance::where('id', $value->id)
                            ->selectRaw('DATE(date) as date')
                            ->first();
                        if ($rawAttendance && $rawAttendance->date) {
                            $attendanceDate = $rawAttendance->date;
                        } else {
                            Log::warning("Could not parse date for employee attendance ID: " . $value->id . ", date: " . $value->date);
                            continue;
                        }
                    }
                } else {
                    // If it's already a Carbon instance or DateTime, format it
                    $attendanceDate = Carbon::parse($attendanceDate)->format('Y-m-d');
                }
                
                // Use whereDate for proper date comparison (handles date format conversion automatically)
                $roster = RosterAssign::join('roster_templates', 'roster_assigns.roster_template_id', 'roster_templates.id')
                    ->where('roster_assigns.assign_to', $value->employee_id)
                    ->whereDate('roster_assigns.schedule_date', $attendanceDate)
                    ->whereNotNull('roster_templates.end_time')
                    ->select('roster_templates.end_time', 'roster_assigns.schedule_date')
                    ->first();

                // Log for debugging if roster not found
                if (!$roster) {
                    Log::warning("No roster found for employee ID: " . $value->employee_id . " on date: " . $attendanceDate . " (original date: " . $value->date . ")");
                }
                
                if ($roster && !empty($roster->end_time)) {
                    $employee_attendance = EmployeeAttendance::where('id', $value->id)->first();
                    
                    if (!$employee_attendance) {
                        Log::warning("Employee attendance not found for ID: " . $value->id);
                        continue;
                    }

                    $total_working_minutes = $this->calculateWorkingHours($employee_attendance, $roster->end_time);
                    
                    // Parse roster end_time to Carbon for proper comparison
                    $roster_end_time = \App\Models\BaseModel::safeCarbonParse($roster->end_time, 'autoCheckout roster_end_time');
                    if (!$roster_end_time instanceof \Carbon\Carbon) {
                        // Try to parse as time only
                        $roster_end_time = Carbon::parse($roster->end_time);
                    }

                    // Parse attendance date to Carbon for proper comparison
                    $attendance_date = Carbon::parse($attendanceDate);
                    $current_date_carbon = Carbon::parse($current_date);

                    // Check if attendance date is in the past OR if it's today and current time >= roster end time
                    if ($attendance_date->lt($current_date_carbon)) {
                        // Past date - always checkout
                        EmployeeAttendance::where('employee_id', $value->employee_id)
                            ->whereDate('date', $attendanceDate)
                            ->update([
                                'check_out' => formatTime($roster->end_time),
                                'working_hours' => $total_working_minutes,
                                'checkout_type' => 1
                            ]);
                        $update_attendance += 1;
                        $check = 1;
                        $all_employee_id[] = $value->employee_id;
                    } elseif ($attendance_date->isSameDay($current_datetime)) {
                        // Today - checkout only if current time >= roster end time
                        $roster_end_datetime = Carbon::parse($current_date . ' ' . $roster_end_time->format('H:i:s'));
                        
                        if ($current_datetime->gte($roster_end_datetime)) {
                            EmployeeAttendance::where('employee_id', $value->employee_id)
                                ->whereDate('date', $attendanceDate)
                                ->update([
                                    'check_out' => formatTime($roster->end_time),
                                    'working_hours' => $total_working_minutes,
                                    'checkout_type' => 1
                                ]);
                            $update_attendance += 1;
                            $check = 1;
                            $all_employee_id[] = $value->employee_id;
                        }
                    }
                } else {
                    // No roster found - delete the attendance record
                    EmployeeAttendance::where('employee_id', $value->employee_id)
                        ->whereDate('date', $attendanceDate)
                        ->delete();
                    $delete_attendance += 1;
                    $check = 1;
                }
            } catch (\Exception $e) {
                Log::error('Error processing auto checkout for employee ID ' . $value->employee_id . ' on date ' . $value->date . ': ' . $e->getMessage());
                continue;
            }
        }

        if (!empty($all_employee_id)) {
            $employee_details = EmpCompanyDetails::withoutGlobalScope(\App\Scopes\NotDeletedScope::class)
                ->whereIn('id', $all_employee_id)
                ->select('id', 'employee_email')
                ->get();

            Log::info(date('d-m-y H:i:s') . " Auto Checkout API run successfully. Employee details: " . $employee_details);
        }

        $data = [
            'total_updated_attendance' => $update_attendance,
            'total_deleted_attendance' => $delete_attendance,
        ];

        if (!$check) {
            return $this->message('Nothing to update and delete');
        }

        return $this->success($data, 'All employees checked-out successfully');
    }



    public function attendanceDownload(Request $request)
    {
        $query = EmployeeAttendance::with(['EmpPersonalDetails', 'EmpCompanyDetails', 'Sites', 'empTeamsMembers']);

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

        $attendances = $query->get();

        $cols = DB::table('attendance_list_change_cols')->pluck('col')->toArray();

        $columns = [
            'Employee',
            'Type',
            'Teams',
            'Site',
            'Check In',
            'Check Out',
            'Working Hours',
            'Date'
        ];

        $col_list[] = "Employee Id";
        $col_list[] = "External Id";
        $indexs = [];
        $i = 2;
        foreach ($columns as $column) {
            if (in_array($column, $cols)) {
                $col_list[] = $column;
            } else {
                $indexs[] = $i;
            }
            $i++;
        }

        $csvData[] = $col_list;

        $data_list  = [];


        $user_meta = '';
        foreach ($attendances as $attendance) {
            $team_list = "";
            foreach ($attendance->empTeamsMembers as $et) {
                $team_list = $et->empTeamsList->title . ",";
            }

            $user_meta =  DB::table('user_meta')->where('emp_id', $attendance->employee_id)->where('option', 'employee_payroll_or_external_id')->first();
            $data_list = [
                $attendance->employee_id,
                $user_meta ? $user_meta->value : '',
                $attendance->emppersonaldetails->first_name . ' ' . $attendance->emppersonaldetails->middle_name . ' ' . $attendance->emppersonaldetails->last_name,
                $attendance->empcompanydetails->user_type == 0 ?  'Internal' : 'External',
                $team_list  ? rtrim($team_list, ',') : "",
                $attendance->sites->title,
                $this->AM_PM($attendance->check_in),
                $attendance->check_out ? $this->AM_PM($attendance->check_out) : $attendance->check_out,
                $this->minutesToHours($attendance->working_hours),
                date("d-m-Y", strtotime($attendance->date))
            ];

            $csvData[] = array_values(array_diff_key($data_list, array_flip($indexs)));
        }

        $headers = [
            'Content-Type' => 'text/csv',
            'Content-Disposition' => 'attachment; filename="attendance.csv"',
        ];


        $callback = function () use ($csvData) {
            $file = fopen('php://output', 'w');
            foreach ($csvData as $row) {
                fputcsv($file, $row);
            }
            fclose($file);
        };

        return Response::stream($callback, 200, $headers);
    }


    function AM_PM($TIME)
    {
        $time = Carbon::parse($TIME);
        $time = $time->format('g:i A');
        $time = str_replace(' ', '', $time);
        $time = str_replace(':00', '', $time);
        return $time;
    }


    function minutesToHours($minutes)
    {
        $isNegative = $minutes < 0;
        $minutes = $minutes == '' ?  0 :   abs($minutes);

        $hours = floor($minutes / 60);
        $remainingMinutes = $minutes % 60;

        $result = ($isNegative ? '-' : '') . $hours . 'h:' . ($remainingMinutes < 10 ? '0' : '') . $remainingMinutes . 'm';

        return $result;
    }

    public function changeCol(Request $request)
    {
        $arr = [];
        $col = $request->col;
        if ($col) {
            DB::table('attendance_list_change_cols')->delete();
            for ($i = 0; $i < count($col); $i++) {
                $arr[$i]['col'] = $col[$i];
            }
            AttendanceListChangeCol::insert($arr);
        }
        return back();
    }

    public function calculateWorkingHours($employee_attendance, $check_out)
    {
        // Ensure we have clean date values before concatenation
        $attendanceDate = is_string($employee_attendance->date) ? $employee_attendance->date : $employee_attendance->date->format('Y-m-d');
        
        $checkIn = \App\Models\BaseModel::safeCarbonParse($attendanceDate . ' ' . $employee_attendance->check_in, 'AttendanceController generateLateFine checkIn');
        $checkOut = \App\Models\BaseModel::safeCarbonParse($attendanceDate . ' ' . $check_out, 'AttendanceController generateLateFine checkOut');

        $totalHours = $checkOut->diffInMinutes($checkIn);

        $breaks = EmployeeBreak::where('emp_attendance_id', $employee_attendance->id)->get();
        // Calculate total breaks
        $totalBreaks = 0;
        $totalWorkingHours = 0;
        if (isset($breaks)) {
            foreach ($breaks as $break) {
                if (is_null($break->break_out)) {
                    $break_update = EmployeeBreak::where('id', $break->id)->update(['break_out' => $check_out]);
                    $breakOutTime = Carbon::parse($check_out);
                } else {
                    $breakOutTime = Carbon::parse($break->break_out);
                }
                $breakInTime = Carbon::parse($break->break_in);
                $totalBreaks += $breakOutTime->diffInMinutes($breakInTime);
            }
        }
        // Calculate total working hours with breaks
        $totalWorkingHours = $totalHours - $totalBreaks;
        return $totalWorkingHours;
    }

    public function getAttendanceStats(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'month' => 'required|date_format:Y-m',
            'employee_id' => 'required|exists:emp_company_details,id'
        ]);

        if ($validator->fails()) {
            return $this->message($validator->errors()->first(), 422);
        }

        $validatedData = $validator->validated();
        $startDate = Carbon::parse($validatedData['month'])->startOfMonth();
        $endDate = Carbon::parse($validatedData['month'])->endOfMonth();

        $totalWorkingDays = $this->getWorkingDays($startDate, $endDate);

        $attendanceRecords = EmployeeAttendance::where('employee_id', $validatedData['employee_id'])
            ->whereBetween('date', [$startDate, $endDate])
            ->get();

        $leaveRequests = LeaveRequest::where('employee_id', $validatedData['employee_id'])
            ->where('status', 1) // 1 for approved leaves
            ->where(function($query) use ($startDate, $endDate) {
                $query->whereBetween('from', [$startDate, $endDate])
                      ->orWhereBetween('to', [$startDate, $endDate])
                      ->orWhere(function($q) use ($startDate, $endDate) {
                          $q->where('from', '<=', $startDate)
                            ->where('to', '>=', $endDate);
                      });
            })
            ->get();

        $stats = [
            'total_days' => $totalWorkingDays,
            'clocked_in' => 0,
            'on_leave' => 0,
            'late' => 0,
            'absent' => 0
        ];

        // Track unique dates that have been clocked in to avoid counting multiple clock-ins on the same day
        $clockedInDates = [];
        // Track the earliest check-in time for each date to determine if it was late
        $earliestCheckInByDate = [];

        foreach ($attendanceRecords as $record) {
            if ($record->status == 1) {
                // Get the date string to use as a unique key
                $dateKey = Carbon::parse($record->date)->format('Y-m-d');
                
                // Only count this date once (one clock-in per day)
                if (!isset($clockedInDates[$dateKey])) {
                    $clockedInDates[$dateKey] = true;
                    $stats['clocked_in']++;
                }

                // Track the earliest check-in time for this date to determine lateness
                // Use safe parsing for check_in time
                try {
                    $checkInTime = EmployeeAttendance::safeCarbonParse($record->check_in, 'check_in');
                    if ($checkInTime) {
                        // If we haven't seen a check-in for this date, or this one is earlier, store it
                        if (!isset($earliestCheckInByDate[$dateKey]) || 
                            $checkInTime->lt($earliestCheckInByDate[$dateKey])) {
                            $earliestCheckInByDate[$dateKey] = $checkInTime;
                        }
                    }
                } catch (\Exception $e) {
                    Log::warning("Failed to parse check_in time for attendance record {$record->id}: " . $e->getMessage());
                }
            }
        }

        // Count late arrivals based on the earliest check-in time for each day
        foreach ($earliestCheckInByDate as $dateKey => $checkInTime) {
            if ($checkInTime->format('H:i') > '09:00') {
                $stats['late']++;
            }
        }

        $leaveDays = 0;
        foreach ($leaveRequests as $leave) {
            try {
                $leaveStart = EmployeeAttendance::safeCarbonParse($leave->from, 'leave_from');
                $leaveEnd = EmployeeAttendance::safeCarbonParse($leave->to, 'leave_to');
                
                if (!$leaveStart || !$leaveEnd) {
                    continue; // Skip this leave if parsing fails
                }
                
                if ($leaveStart->lt($startDate)) {
                    $leaveStart = $startDate;
                }
                if ($leaveEnd->gt($endDate)) {
                    $leaveEnd = $endDate;
                }

                $currentDate = $leaveStart->copy();
                while ($currentDate <= $leaveEnd) {
                    if ($currentDate->dayOfWeek !== 0 && $currentDate->dayOfWeek !== 6) {
                        $leaveDays++;
                    }
                    $currentDate->addDay();
                }
            } catch (\Exception $e) {
                Log::warning("Failed to parse leave dates for leave request {$leave->id}: " . $e->getMessage());
            }
        }
        $stats['on_leave'] = $leaveDays;

        $stats['absent'] = $totalWorkingDays - ($stats['clocked_in'] + $stats['on_leave']);

        $stats['clocked_in_percentage'] = round(($stats['clocked_in'] / $totalWorkingDays) * 100, 2);
        $stats['on_leave_percentage'] = round(($stats['on_leave'] / $totalWorkingDays) * 100, 2);
        $stats['late_percentage'] = round(($stats['late'] / $totalWorkingDays) * 100, 2);
        $stats['absent_percentage'] = round(($stats['absent'] / $totalWorkingDays) * 100, 2);

        return $this->success($stats, 'Attendance statistics retrieved successfully');
    }

    /**
     * Check if two time ranges overlap
     */
    private function timeRangesOverlap($start1, $end1, $start2, $end2)
    {
        // Convert times to minutes for easier comparison
        $start1Minutes = $start1->hour * 60 + $start1->minute;
        $end1Minutes = $end1->hour * 60 + $end1->minute;
        $start2Minutes = $start2->hour * 60 + $start2->minute;
        $end2Minutes = $end2->hour * 60 + $end2->minute;
        
        // Check if ranges overlap
        // Range 1: start1 to end1
        // Range 2: start2 to end2
        // They overlap if: start1 < end2 AND start2 < end1
        return $start1Minutes < $end2Minutes && $start2Minutes < $end1Minutes;
    }
    private function parseDateStringupdate($dateString)
    {
        $parsed = BaseModel::safeCarbonParse($dateString, 'AttendanceController.parseDateString');

        if ($parsed instanceof Carbon) {
            return $parsed->format('Y-m-d');
        }

        Log::warning('Unable to parse date string for attendance controller', [
            'value' => $dateString,
        ]);

        throw new \Exception("Unable to parse date: {$dateString}. Supported formats include the system-configured formats.");
    }
    /**
     * Parse date string with automatic format detection
     */
    private function parseDateString($dateString)
    {
        // Try Y-m-d format first (what frontend sends)
        try {
            return Carbon::createFromFormat('Y-m-d', $dateString)->format('Y-m-d');
        } catch (\Exception $e) {
            // Try d-m-Y format (old format)
            try {
                return Carbon::createFromFormat('d-m-Y', $dateString)->format('Y-m-d');
            } catch (\Exception $e2) {
                // Try Carbon's automatic parsing as fallback
                try {
                    return Carbon::parse($dateString)->format('Y-m-d');
                } catch (\Exception $e3) {
                    throw new \Exception("Unable to parse date: {$dateString}. Supported formats: Y-m-d, d-m-Y");
                }
            }
        }
    }

    private function getWorkingDays($startDate, $endDate)
    {
        $workingDays = 0;
        $currentDate = $startDate->copy();

        while ($currentDate <= $endDate) {
            if ($currentDate->dayOfWeek !== 0 && $currentDate->dayOfWeek !== 6) {
                $workingDays++;
            }
            $currentDate->addDay();
        }

        return $workingDays;
    }
    private function checkRequiredDocumentsForCheckIn($siteId, $employeeId, $customerId, $workspaceId)
    {
        // Get employee's role ID
        $employee = EmpCompanyDetails::where('id', $employeeId)
            ->where('customer_id', $customerId)
            ->where('workspace_id', $workspaceId)
            ->first();

        if (!$employee || !$employee->access_role) {
            return [];
        }

        $role = Role::where('code', $employee->access_role)
            ->where('del', '0')
            ->first();

        $employeeRoleId = $role ? $role->id : null;

        // Get documents that require signature at check-in
        $documents = SiteDocument::where('site_id', $siteId)
            ->where('sign_requires', 1) // Signature required
            ->where('signature_timing', 0) // Must sign at check-in
            ->get();

        $requiredDocuments = [];

        foreach ($documents as $document) {
            // Check if document has role_ids defined
            if (!$document->role_ids || !is_array($document->role_ids) || count($document->role_ids) == 0) {
                continue;
            }

            // Check if employee's role matches any role in document's role_ids
            if (!$employeeRoleId || !in_array($employeeRoleId, $document->role_ids)) {
                continue;
            }

            // Only return documents that haven't been signed yet
            $hasSigned = SiteDocumentSignature::where('site_document_id', $document->id)
                ->where('employee_id', $employeeId)
                ->where('site_id', $siteId)
                ->exists();

            if (!$hasSigned) {
                $documentData = [
                    'id' => $document->id,
                    'title' => $document->title,
                    'file' => $document->file,
                    'document_type' => $document->document_type,
                    'site_id' => $document->site_id,
                    'sign_requires' => $document->sign_requires,
                    'is_signed_required' => 1, // Not signed yet - signature required
                ];

                $requiredDocuments[] = $documentData;
            }
        }

        return $requiredDocuments;
    }
}
