<?php

namespace App\Http\Controllers;

namespace App\Http\Controllers\API\v1;

use App\Http\Controllers\Controller;
use App\Models\Employee;
use App\Models\EmployeeLeave;
use App\Models\EmployeeLeaveCredits;
use App\Models\EmployeeSchedule;
use App\Models\Holiday;
use App\Models\LeaveType;
use App\Models\User;
use App\Notifications\Leave;
use DateInterval;
use DatePeriod;
use DateTime;
use Exception;
use Illuminate\Http\Request;
use Illuminate\Http\Response;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\Crypt;
use Illuminate\Support\Facades\Validator;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Notification;
use Illuminate\Support\Facades\Storage;
use Illuminate\Validation\Rule;
use PHPUnit\Event\Code\Throwable;
use Illuminate\Support\Str;

class EmployeeLeaveController extends Controller
{

    /**
     * Display a listing of the resource.
     */
    public function index(Request $request)
    {
        //
        $employee = Auth::user();

        $keyword = $request->input('keyword', '');
        $perPage = $request->input('per_page', PHP_INT_MAX);
        $sortBy = $request->input('sortBy', '');
        $sortType = $request->input('sortType', '');

        $keyword = str_replace(" ", "%", $keyword);
        
        $data = EmployeeLeave::with([
            'leave_type' => fn($leave_type) => $leave_type->select('id', 'name', 'enabled'),
            'recommending' => fn($recommending) => $recommending->select('employee_id', 'asa_user_id', 'firstname', 'lastname'),
            'approving'=> fn($approving) => $approving->select('employee_id', 'asa_user_id', 'firstname', 'lastname'),
        ])
        ->select(
            'id',
            'employee_id',
            'company_id',
            'dealer_id',
            'leave_id',
            'half_day',
            'first_or_last_day_halfday_leave',
            'days',
            'date_from',
            'date_to',
            'meridiem',
            'hours',
            'hours_deducted',
            'without_pay',
            'credit_to_vl',
            'leave_reason_id',
            'attachment',
            'description',
            'denied_reason',
            'status',
            'validated',
            'recommending_id',
            'approver_id',
            'allow_higher_approval',
            'recommended_at',
            'approved_at',
            'created_at',
            'validated_at',
            'posted'
            )
        ->where(function ($query) use ($keyword) {
            if ($keyword) {
                $query->where('column_name', 'like', "%{$keyword}%");
            }
        });

        if ($employee->roles[0]['group_id'] != 1) {
            $data->where('employee_id', $employee->employee_id);
        }

        if (!empty($sortBy) && !empty($sortType)) {
            $data = $data->orderBy($sortBy, $sortType);
        }

        if ($request->date_created) {
            $dates = explode(' to ', $request->date_created); // Split into start and end date
            if (count($dates) == 2) {
                $data = $data->whereBetween('created_at', [$dates[0], $dates[1]]);
            }
        }

        if ($request->range) {
            $dates = explode(' to ', $request->range); // Split into start and end date
            if (count($dates) == 2) {
                $data = $data->whereBetween('date_from', [$dates[0], $dates[1]]);
            }
        }
        
        if ($request->status && is_array($request->status)) {
            $data = $data->where(function ($query) use ($request) {
                if (in_array('pending', $request->status)) {
                    $query->orWhere('status', 0);
                }
        
                if (in_array('validated', $request->status)) {
                    $query->orWhere(function ($q) {
                        $q->where('status', 2)->where('validated', 1);
                    });
                }
        
                if (in_array('denied', $request->status)) {
                    $query->orWhere('status', 99)
                        ->orWhere(function ($q) {
                            $q->where('status', 2)->where('validated', 99);
                        });

                }
        
                if (in_array('for_validation', $request->status)) {
                    $query->orWhere(function ($q) {
                        $q->where('status', 2)->where('validated', 0);
                    });
                }
            });
        }     
        
        $usedCredits = [];
        $withoutPayCredits = [];

        if ($employee->roles[0]['group_id'] != 1) {
            $usedCredits = EmployeeLeave::where([
                'employee_leaves.employee_id' => $employee->employee_id,
                'employee_leaves.enabled' => 1,
                'employee_leaves.without_pay'=> 0
            ])
            ->where('leave_id', '!=', '1')
            ->where('status', '!=', '99')
            ->join('leave_types', 'employee_leaves.leave_id', '=', 'leave_types.id')
            ->selectRaw('SUM(employee_leaves.days) as total, leave_types.name, leave_types.id')
            ->groupBy('leave_types.name', 'leave_types.id')
            ->get();

            $withoutPayCredits = EmployeeLeave::where([
                'employee_leaves.employee_id' => $employee->employee_id,
                'employee_leaves.enabled' => 1,
                'employee_leaves.without_pay'=> 1
            ])
            ->where('status', '2')
            ->join('leave_types', 'employee_leaves.leave_id', '=', 'leave_types.id')
            ->selectRaw('SUM(employee_leaves.days) as total, leave_types.name, leave_types.id')
            ->groupBy('leave_types.name', 'leave_types.id')
            ->get();
        }

        $data = $data->paginate($perPage);

        $response = [
            'data' => $data,
            'used_credits' => $usedCredits,
            'used_withoutpay' => $withoutPayCredits,
        ];

        return response()->json($response);
    }

    public function getLeaveList(Request $request)
    {
        //
        DB::enableQueryLog();
        $employee = Auth::user();

        $keyword = $request->input('keyword', '');
        $perPage = $request->input('per_page', PHP_INT_MAX);
        $sortBy = $request->input('sortBy', '');
        $sortType = $request->input('sortType', '');
        $dealership_id = $request->input('dealership', null);

        $keyword = str_replace(" ", "%", $keyword);

        $data = EmployeeLeave::with([
            'leave_type' => fn($leave_type) => $leave_type->select('id', 'name', 'enabled'),
            'recommending' => fn($recommending) => $recommending->select('employee_id', 'asa_user_id', 'firstname', 'lastname'),
            'approving'=> fn($approving) => $approving->select('employee_id', 'asa_user_id', 'firstname', 'lastname'),
            'employee'=> fn($employee) => $employee->select('employee_id', 'firstname', 'lastname'),
        ])
        ->select(
            'id',
            'employee_id',
            'company_id',
            'dealer_id',
            'leave_id',
            'half_day',
            'first_or_last_day_halfday_leave',
            'days',
            'date_from',
            'date_to',
            'meridiem',
            'hours',
            'hours_deducted',
            'without_pay',
            'credit_to_vl',
            'leave_reason_id',
            'attachment',
            'description',
            'denied_reason',
            'status',
            'validated',
            'recommending_id',
            'approver_id',
            'allow_higher_approval',
            'recommended_at',
            'approved_at',
            'created_at',
            'posted'
            )
            ->where('status',2);    

        if($dealership_id) $data = $data->where('dealer_id', $dealership_id);
        
        if (!$request->date_created && !$request->range && !$request->status) {
            $data = $data->whereDate('created_at', '>=', now()->subDays(30));
        }

        if ($request->date_created) {
            $dates = explode(' to ', $request->date_created); // Split into start and end date
            if (count($dates) == 2) {
                $data = $data->whereBetween('created_at', [$dates[0], $dates[1]]);
            }
        }

        if ($request->range) {
            $dates = explode(' to ', $request->range); // Split into start and end date
            if (count($dates) == 2) {
                $data = $data->whereBetween('date_from', [$dates[0], $dates[1]]);
            }
        } 
        
        if ($request->status && is_array($request->status)) {
            $data = $data->where(function ($query) use ($request) {
                if (in_array('pending', $request->status)) {
                    $query->orWhere('status', 0);
                }
        
                if (in_array('validated', $request->status)) {
                    $query->orWhere(function ($q) {
                        $q->where('status', 2)->where('validated', 1);
                    });
                }
        
                if (in_array('denied', $request->status)) {
                    $query->orWhere('status', 99)
                        ->orWhere(function ($q) {
                            $q->where('status', 2)->where('validated', 99);
                        });

                }
        
                if (in_array('for_validation', $request->status)) {
                    $query->orWhere(function ($q) {
                        $q->where('status', 2)->where('validated', 0);
                    });
                }
            });
        }

        $data = $data->orderBy('validated', 'asc')->get();
        
        // dd(DB::getQueryLog());
        $response = [
            'data' => $data
        ];

        return response()->json($response);
    }

    public function getLeaveCredits(Request $request)
    {

        if ($request->input('employee_id')) {
            try {
                $employee_id = Crypt::decrypt($request->input('employee_id'));
            } catch (\Illuminate\Contracts\Encryption\DecryptException $e) {
                $employee_id = $employee_id;
            }
            
            $employeeRecord = Employee::where('employee_id', $employee_id)->first();
        } else {
            $user = Auth::user();
            $employee_id = $user->employees->employee_id;
            $employeeRecord = Employee::where('employee_id', $employee_id)->first();
        }

        $data = EmployeeLeaveCredits::with([
            'leave_type' => fn($query) => $query->select('id', 'name'),
            'employee' => fn($employee) => $employee->select('employee_id', 'employment_status_id')
        ])
        ->where([
            'employee_id' => $request->id ? $request->id : $employee_id,
            'enabled' => 1
        ])
        ->select(
            'employee_leave_credits.employee_id',
            'employee_leave_credits.leave_type_id',
            'employee_leave_credits.remaining',
            'employee_leave_credits.hours'
        )
        ->get();

        // Define the leave types and their default values
        $leaveTypesToCheck = [
            2 => ['name' => 'Vacation Leave', 'remaining' => 0, 'hours' => 0], // Leave type 2
            3 => ['name' => 'Sick Leave', 'remaining' => 0, 'hours' => 0]  // Leave type 3
        ];

        // Get the existing leave_type_ids from the data
        $existingLeaveTypes = $data->pluck('leave_type_id')->toArray();

        
        // Check if any required leave types (2 or 3) are missing
        foreach ($leaveTypesToCheck as $leaveTypeId => $defaultValues) {
            if (!in_array($leaveTypeId, $existingLeaveTypes)) {
                // Create a new instance of EmployeeLeaveCredits model with default values
                $newCredit = new EmployeeLeaveCredits([
                    'employee_id' => $employee_id, // Assuming $employee_id is defined
                    'leave_type_id' => $leaveTypeId,
                    'remaining' => $defaultValues['remaining'],
                    'hours' => $defaultValues['hours']
                ]);

                // Create a new LeaveType model to associate with the new credit
                $leaveType = new LeaveType([
                    'id' => $leaveTypeId,
                    'name' => $defaultValues['name']
                ]);

                // Set the relationship properly using the model
                $newCredit->setRelation('leave_type', $leaveType);

                // Add the new model to the collection
                $data->push($newCredit);
            }
        }

        // Now $data will contain both leave_type_id 2 and 3, with their respective names

        // dd($employeeRecord->employment_status_id != 2);
        
        // If no records are found, return the same structure with empty values
        if ($data->isEmpty() || $employeeRecord->employment_status_id != 1) {
            $leaveData = LeaveType::where('enabled', 1)->get();

            $emptyData = [];

            foreach($leaveData as $data){
                $emptyData[] =
                    [
                        'employee_id' => $employee_id,
                        'leave_type_id' => '',
                        'remaining' => '0',
                        'hours' => '0',
                        'leave_type' => [
                            'id' => $data['id'],
                            'name' => $data['name']
                        ]
                    ];
            }
            
            // dd($data);
            return response()->json($emptyData);
        }

        return response()->json($data);

    }

    public function store(Request $request)
    {
        $credits_from = 0;
        $credits_to = 0;

        $without_pay = ($request->without_pay && $request->without_pay !== 'null' && $request->without_pay !== 'false' && trim($request->without_pay) !== '' ? 1 : 0);
        $credit_to_vl = ($request->credit_to_vl && $request->credit_to_vl !== 'null' && $request->credit_to_vl !== 'false' && trim($request->credit_to_vl) !== '' ? 1 : 0);

        $allow_higher_approval = ($request->allow_higher_approval && $request->allow_higher_approval !== 'null' && $request->allow_higher_approval !== 'false' && trim($request->allow_higher_approval) !== '' ? 1 : 0);

        $user = Auth::user();
        
        $hours_deducted = 0;

        if($without_pay != 1){
            $leave_credits = EmployeeLeaveCredits::where([
                'employee_id' => $user->employees->employee_id,
                'enabled' => 1,
                'leave_type_id' => $credit_to_vl ? 2 : $request->leave_id
            ])->first();

            if($leave_credits)
            {
                // $remaining_leave = $leave_credits->remaining - $request->days;

                if(floor($request->hours) > $leave_credits->hours){
                    $remaining_hours = $leave_credits->hours - floor($leave_credits->hours);
                    $remaining_leave = $remaining_hours / 8;

                    $hours_deducted = floor($leave_credits->hours);
                } else {
                    $remaining_leave = $leave_credits->remaining - $request->days;
                    $remaining_hours = $leave_credits->hours - ($request->days * 8);

                    $hours_deducted = $request->days * 8;
                }

                $credits_from = $leave_credits->remaining;
                $credits_to = $remaining_leave;

                try {

                    DB::connection()->beginTransaction();

                    $leave_credits->remaining = $remaining_leave;
                    $leave_credits->hours = $remaining_hours;

                    $leave_credits->save();

                    DB::connection()->commit();

                } catch(Throwable $e) {
                    DB::rollBack();
                    return response()->json([
                        'status' => false,
                        'message' => 'Unable to process request. Please try again.',
                        'data' => $e->getMessage()
                    ], Response::HTTP_INTERNAL_SERVER_ERROR);
                }
            }
        }

        $validator = Validator::make($request->all(), [
            'leave_id' => 'required',
            'half_day' => 'required',
            'date_from' => 'required|date',
            'date_to' => 'required|date',
        ]);

        if ($validator->fails()) {
            return response()->json([
                'status' => false,
                'message' => $validator->errors()->first(),
                'errors' => $validator->errors()
            ], Response::HTTP_UNPROCESSABLE_ENTITY);
        }

        DB::beginTransaction();

        try {
            $attachmentName = $this->handleAttachment($request);

            $status = EmployeeLeave::create([
                'leave_id' => $request->leave_id,
                'without_pay' => $without_pay,
                'credit_to_vl' => $credit_to_vl,
                'employee_id' => $user->employee_id,
                'company_id' => $user->employees->company_id,
                'credits_from' => $credits_from,
                'credits_to' => $credits_to,
                'dealer_id' => $user->employees->dealer_id,
                'half_day' => $request->half_day,
                'date_from' => $request->date_from,
                'date_to' => $request->date_to,
                'days' => $request->days,
                'hours' => $request->hours,
                'hours_deducted' => $hours_deducted,                
                'first_or_last_day_halfday_leave' => $request->first_or_last_day_halfday_leave,
                'meridiem' => $request->meridiem,
                'allow_higher_approval' => $allow_higher_approval,
                'leave_reason_id' => $request->leave_reason_id,
                'description' => $request->description != 'null' ? $request->description : NULL,
                'attachment' => $attachmentName,
                'recommending_id' => $request->recommending_id[0],
                'approver_id' => $request->approver_id[0] ?? $request->recommending_id[0],
                'status' => 0,
            ]);

            DB::commit();

            $status->load(['employee', 'leave_type']);
            
            $emailTo = $this->getEmployeeEmailData($request->recommending_id);

            // dd($emailTo);

            try{
                if($emailTo != ''){
                    Notification::send($emailTo, new Leave($status));
                }
            } catch (Exception $e) {
                // Log the error or handle it as needed
                // For this example, we'll just ignore email sending failures
            }

            // dd('EXIT');
            // dd($emailTo);
            return response()->json([
                'status' => true,
                'message' => 'Saved successfully!',
                'data' => $status
            ], Response::HTTP_CREATED);

        } catch(Throwable $e) {
            DB::rollBack();
            return response()->json([
                'status' => false,
                'message' => 'Unable to process request. Please try again.',
                'data' => $e->getMessage()
            ], Response::HTTP_INTERNAL_SERVER_ERROR);
        }
    }

    public function getHolidaysAndDaysOff(Request $request)
    {
        $user = Auth::user();

        $scheduleController = new EmployeeAttendanceController();

        $start_date = new DateTime($request->date_from);
        $end_date = new DateTime($request->date_to);
        $end_date->modify('+1 day');

        $period = new DatePeriod($start_date, new DateInterval('P1D'), $end_date);

        $hoursTotal = 0;

        $length = iterator_count($period);
        $index = 0;
        $halfdayHours = 0;

        foreach ($period as $date) {
            $formattedDate = $date->format('Y-m-d');

            $schedule = $scheduleController->getSchedule($formattedDate, $user->employee_id);
            $holiday = $scheduleController->getHoliday($formattedDate,$user->employees,$schedule);
            
            // dd($schedule->schedule->flexible);

            $dayOfWeek = date('l', strtotime($formattedDate));
            if ($schedule) {
                $daysArray = explode(',', $schedule->schedule->day_off); // Convert string to array
            } else {
                $daysArray = []; // Or any default value
            }

            if(!$holiday && !in_array($dayOfWeek, $daysArray)){

                $daysCWSArray = explode(',', $schedule->schedule->cws_day);

                if(in_array($dayOfWeek, $daysCWSArray)){
                    $fromTime = new DateTime($schedule->schedule['cws_from_time']);
                    $toTime = new DateTime($schedule->schedule['cws_to_time']);
                } else {
                    $fromTime = new DateTime($schedule->schedule['from_time']);
                    $toTime = new DateTime($schedule->schedule['to_time']);
                }

                $interval = $fromTime->diff($toTime);

                $hours = $interval->h + ($interval->days * 24); // Calculate total hours
                $hoursMinusOne = $hours - 1;

                if($request->half_day == 1){
                    if($index == $length - 1){
                        $hoursMinusOne = $hoursMinusOne / 2;
                        $halfdayHours = $hoursMinusOne;
                    }
                } else if ($request->half_day == 2) {
                    if($index == 0){
                        $hoursMinusOne = $hoursMinusOne / 2;
                        $halfdayHours = $hoursMinusOne;
                    }
                }

                $schedule->schedule->flexible ? $hoursMinusOne = 8  : '';

                $hoursTotal += $hoursMinusOne;
            }

            $index++;
        }

        // dd($hoursTotal);

        return response()->json(['hoursTotal' => $hoursTotal, 'halfdayHours' => $halfdayHours]);
    }


    public function handleAttachment(Request $request)
    {
        if ($request->hasFile('attachment')) {
            $image = $request->file('attachment');
            $attachmentName = time() . '_' . $image->getClientOriginalName();
            $path = "employee-leave/" . $attachmentName;
            if (Storage::disk('local')->put($path, file_get_contents($image))) {
                return $attachmentName;
            }
            throw new Exception('Unable to process attachment.');
        }
        return '';
    }

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

        $data = EmployeeLeave::where('id', $id)->first();

        $user = Auth::user();

        $credits_from = 0;
        $credits_to = 0;

        $hours_deducted = 0;

        $without_pay = ($request->without_pay && $request->without_pay !== 'null' && $request->without_pay !== 'false' && trim($request->without_pay) !== '' ? 1 : 0);
        $credit_to_vl = ($request->credit_to_vl && $request->credit_to_vl !== 'null' && $request->credit_to_vl !== 'false' && trim($request->credit_to_vl) !== '' ? 1 : 0);

        $allow_higher_approval = ($request->allow_higher_approval && $request->allow_higher_approval !== 'null' && $request->allow_higher_approval !== 'false' && trim($request->allow_higher_approval) !== '' ? 1 : 0);

        if($data->leave_id != $request->leave_id){

            $change_leave_credits = EmployeeLeaveCredits::where([
                'employee_id' => $user->employees->employee_id,
                'enabled' => 1,
                'leave_type_id' => $data->leave_id
            ])->first();

            if($change_leave_credits){
                try {

                    DB::connection()->beginTransaction();

                    $change_leave_credits->remaining = ($data->hours_deducted + $change_leave_credits->hours) / 8;
                    $change_leave_credits->hours = $data->hours_deducted + $change_leave_credits->hours;

                    $change_leave_credits->save();

                    DB::connection()->commit();

                } catch(Throwable $e) {
                    DB::rollBack();
                    return response()->json([
                        'status' => false,
                        'message' => 'Unable to process request. Please try again.',
                        'data' => $e->getMessage()
                    ], Response::HTTP_INTERNAL_SERVER_ERROR);
                }
            }
        } 

        if($request->leave_id != 1){

            if($data->leave_id != $request->leave_id){

                $leave_credits = EmployeeLeaveCredits::where([
                    'employee_id' => $user->employees->employee_id,
                    'enabled' => 1,
                    'leave_type_id' => $request->leave_id
                ])->first();

                if(floor($request->hours) > $leave_credits->hours){
                    $remaining_hours = $leave_credits->hours - floor($leave_credits->hours);
                    $remaining_leave = $remaining_hours / 8;

                    $hours_deducted = floor($leave_credits->hours);
                } else {
                    $remaining_leave = $leave_credits->remaining - $request->days;
                    $remaining_hours = $leave_credits->hours - ($request->days * 8);

                    $hours_deducted = $request->days * 8;
                }

                $credits_from = $leave_credits->remaining;
                $credits_to = $remaining_leave;

                try {

                    DB::connection()->beginTransaction();

                    $leave_credits->remaining = $remaining_leave;
                    $leave_credits->hours = $remaining_hours;

                    $leave_credits->save();

                    DB::connection()->commit();

                } catch(Throwable $e) {
                    DB::rollBack();
                    return response()->json([
                        'status' => false,
                        'message' => 'Unable to process request. Please try again.',
                        'data' => $e->getMessage()
                    ], Response::HTTP_INTERNAL_SERVER_ERROR);
                }
    
                
            } else {
                $leave_credits = EmployeeLeaveCredits::where([
                    'employee_id' => $user->employees->employee_id,
                    'enabled' => 1,
                    'leave_type_id' => $request->leave_id
                ])->first();
    
                if($leave_credits){
                    // if($data->hours_deducted != 0){
                        $hours = $request->hours;
                        $totalHoursWithRemainingLeave = $data->hours_deducted + $leave_credits->hours;
                        
                        // dd($data->hours_deducted. ' - '.$hours);
                        if($data->hours_deducted >= $hours){
                            $remaining_hours = $totalHoursWithRemainingLeave - $hours;
                            $remaining_leave = $remaining_hours / 8;
                            $hours_deducted = $hours;
                        } else {
                            $remaining_hours = $totalHoursWithRemainingLeave - $data->hours_deducted;
                            $remaining_leave = $remaining_hours / 8;
                            if($data->hours_deducted >= $hours){
                                $hours_deducted = $hours;
                            } else {
                                $hours_deducted = $data->hours_deducted;
                            }
                        }
                    // }
    
                    $credits_from = $leave_credits->remaining;
                    $credits_to = $remaining_leave;
    
                    // dd($hours_deducted);
                    // if($request->days > $data->days){
                    //     $temp_remaining_days = $request->days - $data->days;
    
                    //     if($temp_remaining_days <= $leave_credits->remaining){
                    //         $remaining_leave = $leave_credits->remaining - $temp_remaining_days;
                    //     } else {
                    //         return response()->json([
                    //             'status' => false,
                    //             'message' => 'Unable to process request. Please try again.'
                    //         ], Response::HTTP_INTERNAL_SERVER_ERROR);
                    //     }
                    // } else if ($request->days < $data->days){
                    //     $temp_remaining_days = $request->days - $data->days;
    
                    //     $remaining_leave = $temp_remaining_days + $leave_credits->remaining;
                    // } else {
                    //     // dd($leave_credits->remaining);
                    //     $remaining_leave = $leave_credits->remaining;
                    // }
    
                    // $credits_from = $leave_credits->remaining;
                    // $credits_to = $remaining_leave;
    
                    // $remaining_hours = $leave_credits->remaining - ($remaining_leave * 8);
    
                    // dd($remaining_hours);
    
                    try {
    
                        DB::connection()->beginTransaction();
    
                        $leave_credits->remaining = $remaining_leave;
                        $leave_credits->hours = $remaining_hours;
    
                        $leave_credits->save();
    
                        DB::connection()->commit();
    
                    } catch(Throwable $e) {
                        DB::rollBack();
                        return response()->json([
                            'status' => false,
                            'message' => 'Unable to process request. Please try again.',
                            'data' => $e->getMessage()
                        ], Response::HTTP_INTERNAL_SERVER_ERROR);
                    }
                }
            }
        }

        $validator = Validator::make($request->all(), [
            'leave_id' => "required",
            'half_day' => "required",
            'date_from' => "required|date",
            'date_to' => "required|date",
        ]);

        /** If validation fails return with error message */
        if ($validator->fails()) {
            $errors = $validator->errors();
            $first = $errors->getMessages();
            return response()->json([
                'status'  => false,
                'message' => (reset($first))[0],
                'errors'  => $errors
            ], Response::HTTP_UNPROCESSABLE_ENTITY);
        }

        $attachment_name = $request->attachment;

        if($data->attachment != $request->attachment)
        {
            $attachment_file_path = 'employee-leave/' . $data->attachment;
            if(Storage::exists($attachment_file_path))
            {
                Storage::delete($attachment_file_path);
            }

            if($request->hasFile('attachment')) {
                $image      = $request->file('attachment');
                $attachment_name  = time().'_'.$image->getClientOriginalName();
                $path       = "employee-leave/".$attachment_name;
                if(!Storage::disk('local')->put($path, file_get_contents($image)))
                {
                    return response()->json(['message'=> "Failed to upload attachment"],304);
                }
            }
            else
            {
                $attachment_name = null;
            }
        }

        DB::connection()->beginTransaction();

        try {
            $data->leave_id = $request->leave_id;
            $data->without_pay = $without_pay;
            $data->credit_to_vl = $credit_to_vl;
            $data->employee_id = $user->employee_id;
            $data->half_day = $request->half_day;
            $data->first_or_last_day_halfday_leave = $request->first_or_last_day_halfday_leave;
            $data->date_from = $request->date_from;
            $data->date_to = $request->date_to;
            $data->days = $request->days;
            $data->hours = $request->hours;
            $data->hours_deducted = $hours_deducted;
            $data->meridiem = $request->meridiem;
            $data->credits_from = $credits_from;
            $data->credits_to = $credits_to;
            $data->leave_reason_id = $request->leave_reason_id;
            $data->allow_higher_approval = $allow_higher_approval;
            $data->description = $request->description == "null" ? NULL : $request->description;
            // $data->recommending_id = $request->recommending_id;
            // $data->approver_id = $request->approver_id;
            $data->recommending_id = $request->recommending_id[0];
            $data->approver_id = $request->approver_id[0] ?? $request->recommending_id[0];
            $data->attachment = $attachment_name;
            $data->save();

            DB::connection()->commit();

            return response()->json([
                'status'  => true,
                'message' => 'Saved successfully!',
                'data'    => $data
            ],201);

        } catch (Throwable $e) {

            DB::connection()->rollback();
            return response()->json([
                'status'  => false,
                'message' => 'Unable to process request. Please try again.',
                'data'    => $e->getMessage()
            ]);
        }
    }

    public function destroy(int $id)
    {
        $data = EmployeeLeave::find($id);

        if($data->without_pay != 1){
            $leave_credits = EmployeeLeaveCredits::where([
                'employee_id' => $data->employee_id,
                'enabled' => 1,
                'leave_type_id' => $data->credit_to_vl ? 2 : $data->leave_id
            ])->first();

            $remaining_leave = $leave_credits->remaining + ($data->hours_deducted / 8);
            $remaining_hours = $leave_credits->hours + $data->hours_deducted;

            // dd($remaining_leave);

            try {

                DB::connection()->beginTransaction();

                $leave_credits->remaining = $remaining_leave;
                $leave_credits->hours = $remaining_hours;

                $leave_credits->save();

                DB::connection()->commit();

            } catch(Throwable $e) {
                DB::rollBack();
                return response()->json([
                    'status' => false,
                    'message' => 'Unable to process request. Please try again.',
                    'data' => $e->getMessage()
                ], Response::HTTP_INTERNAL_SERVER_ERROR);
            }
        }

        if(!$data)
        {
            return response()->json(['message' => "Record not found!"],204);
        }

        DB::connection()->beginTransaction();

        $data->delete();

        DB::connection()->commit();

        return response()->json(['message' => "Record successfully deleted!"], 200);
    }

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

        $data = EmployeeLeave::where('id', $id)->first();

        $data->attachment = NULL;

        $data->save();

        DB::connection()->commit();

        $attachment_file_path = 'employee-leave/' . $request->attachment;
        if(Storage::exists($attachment_file_path))
        {
            Storage::delete($attachment_file_path);
        }

        return response()->json([
            'status'  => true,
            'message' => 'Saved successfully!',
            'data'    => $data
        ],201);
    }

    public function getLeaveMandatoryList(Request $request)
    {
        //
        DB::enableQueryLog();
        $employee = Auth::user();

        $keyword = $request->input('keyword', '');
        $perPage = $request->input('per_page', PHP_INT_MAX);
        $sortBy = $request->input('sortBy', '');
        $sortType = $request->input('sortType', '');
        $dealership_id = $request->input('dealership', null);

        $keyword = str_replace(" ", "%", $keyword);

        $data = EmployeeLeave::with([
            'leave_type' => fn($leave_type) => $leave_type->select('id', 'name', 'enabled'),
            'recommending' => fn($recommending) => $recommending->select('employee_id', 'asa_user_id', 'firstname', 'lastname'),
            'approving'=> fn($approving) => $approving->select('employee_id', 'asa_user_id', 'firstname', 'lastname'),
            'employee'=> fn($employee) => $employee->select('employee_id', 'firstname', 'lastname'),
        ])
        ->select(
            'id',
            'employee_id',
            'company_id',
            'dealer_id',
            'leave_id',
            'half_day',
            'first_or_last_day_halfday_leave',
            'days',
            'date_from',
            'date_to',
            'meridiem',
            'hours',
            'hours_deducted',
            'without_pay',
            'credit_to_vl',
            'leave_reason_id',
            'attachment',
            'description',
            'denied_reason',
            'status',
            'validated',
            'recommending_id',
            'approver_id',
            'allow_higher_approval',
            'recommended_at',
            'approved_at',
            'created_at',
            'posted'
            )
            ->where('status',2)
            ->where('leave_id', 10);    

        if($dealership_id) $data = $data->where('dealer_id', $dealership_id);
        
        if (!$request->date_created && !$request->range && !$request->status) {
            $data = $data->whereDate('created_at', '>=', now()->subDays(30));
        }

        if ($request->date_created) {
            $dates = explode(' to ', $request->date_created); // Split into start and end date
            if (count($dates) == 2) {
                $data = $data->whereBetween('created_at', [$dates[0], $dates[1]]);
            }
        }

        if ($request->range) {
            $dates = explode(' to ', $request->range); // Split into start and end date
            if (count($dates) == 2) {
                $data = $data->whereBetween('date_from', [$dates[0], $dates[1]]);
            }
        } 
        
        if ($request->status && is_array($request->status)) {
            $data = $data->where(function ($query) use ($request) {
                if (in_array('pending', $request->status)) {
                    $query->orWhere('status', 0);
                }
        
                if (in_array('validated', $request->status)) {
                    $query->orWhere(function ($q) {
                        $q->where('status', 2)->where('validated', 1);
                    });
                }
        
                if (in_array('denied', $request->status)) {
                    $query->orWhere('status', 99)
                        ->orWhere(function ($q) {
                            $q->where('status', 2)->where('validated', 99);
                        });

                }
        
                if (in_array('for_validation', $request->status)) {
                    $query->orWhere(function ($q) {
                        $q->where('status', 2)->where('validated', 0);
                    });
                }
            });
        }

        $data = $data->get();
        
        // dd(DB::getQueryLog());
        $response = [
            'data' => $data
        ];

        return response()->json($response);
    }

    public function createMandatory(Request $request)
    {
        // Validate the request
        $validator = Validator::make($request->all(), [
            'employee_id' => 'required|array',
            'employee_id.*' => 'exists:employees,employee_id',
            'dates' => 'required|string',
        ]);

        if ($validator->fails()) {
            return response()->json([
                'status' => false,
                'message' => $validator->errors()->first(),
                'errors' => $validator->errors()
            ], Response::HTTP_UNPROCESSABLE_ENTITY);
        }

        // Get employees
        $employees = Employee::select('employee_id', 'company_id', 'dealer_id')
            ->whereIn('employee_id', $request->employee_id)
            ->whereNull('date_resign')
            ->get();

        if ($employees->isEmpty()) {
            return response()->json([
                'status' => false,
                'message' => 'No valid employees found.'
            ], Response::HTTP_NOT_FOUND);
        }

        $data = [];

        // Build the data array
        foreach (explode(",", $request->dates) as $date) {
            foreach ($employees as $employee) {
                // Create a new request for getHolidaysAndDaysOff
                $hoursRequest = new \Illuminate\Http\Request([
                    'employee_id' => $employee->employee_id,
                    'date_from' => $date,
                    'date_to' => $date,
                ]);

                $hours = $this->getHolidaysAndDaysOff($hoursRequest);

                // Skip if no hours calculated
                if (!$hours->original['hoursTotal']) continue;

                $data[] = [
                    'employee_id' => $employee->employee_id,
                    'company_id' => $employee->company_id,
                    'dealer_id' => $employee->dealer_id,
                    'leave_id' => 10,
                    'half_day' => $request->half_day == 1,
                    'without_pay' => 1,
                    'meridiem' => $request->meridiem,
                    'days' => $request->half_day == 1 ? ($hours->original['hoursTotal'] / 2) / 8 : $hours->original['hoursTotal'] / 8,
                    'hours' => $request->half_day == 1 ? $hours->original['hoursTotal'] / 2 : $hours->original['hoursTotal'],
                    'hours_deducted' => 0,
                    'credits_from' => 0,
                    'credits_to' => 0,
                    'date_from' => $date,
                    'date_to' => $date,
                    'status' => 2,
                    'validated' => 1,
                    'leave_reason_id' => 15,
                    'recommending_id' => 0,
                    'approver_id' => 0,
                    'created_at' => now(),
                    'updated_at' => now(),
                ];
            }
        }

        // Check if there's data to insert
        if (empty($data)) {
            return response()->json([
                'status' => false,
                'message' => 'No valid leave records to create. All selected dates may be holidays or days off.'
            ], Response::HTTP_BAD_REQUEST);
        }

        DB::beginTransaction();

        try {
            // Bulk insert the mandatory leaves
            EmployeeLeave::insert($data);

            DB::commit();

            return response()->json([
                'status' => true,
                'message' => 'Mandatory leaves created successfully!',
                'data' => [
                    'count' => count($data),
                    'records' => $data
                ]
            ], Response::HTTP_CREATED);

        } catch (Throwable $e) {
            DB::rollBack();
            return response()->json([
                'status' => false,
                'message' => 'Unable to process request. Please try again.',
                'data' => $e->getMessage()
            ], Response::HTTP_INTERNAL_SERVER_ERROR);
        }
    }

    public function getEmployeeEmailData($id){
        return User::whereIn('employee_id', $id)->get();
    }
}
