<?php

namespace App\Http\Controllers\API\v1;

use App\Http\Controllers\Controller;
use App\Models\EmployeeLeave;
use App\Models\EmployeeLeaveCredits;
use App\Models\EmployeeOb;
use App\Models\EmployeeOffset;
use App\Models\EmployeeOffsetEarning;
use App\Models\EmployeeOvertime;
use App\Models\EmployeeTimeAdjustment;
use App\Models\EmployeeUndertime;
use App\Models\OffsetRemaining;
use Illuminate\Http\Request;
use Illuminate\Http\Response;
use Illuminate\Support\Facades\DB;
use Throwable;

class AttendanceRequestsMobileController extends Controller
{
    public function leave_request(Request $request)
    {
        $employee_id = $request->input('employee_id');

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

        $year = $request->input('year');
        $status = $request->input('status');

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

        $data = EmployeeLeave::with([
            'employee' => fn($employee) => $employee->select('employee_id', 'asa_user_id', 'firstname', 'lastname'),
            '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'),
        ])
        ->where(function ($query) use ($employee_id) {
            $query->where('recommending_id', $employee_id ? $employee_id : '')
                ->orWhere('approver_id', $employee_id ? $employee_id : '');
        })
        ->select(
            'id', 
            'employee_id', 
            'company_id', 
            'dealer_id', 
            'leave_id', 
            'half_day', 
            'days', 
            'date_from',
            'date_to',
            'meridiem',
            'hours', 
            'attachment', 
            'description', 
            'status', 
            'recommending_id', 
            'approver_id', 
            'approved_at'
        )
        ->where(function ($query) use ($employee_id) {
            $query->where(function ($subQuery) use ($employee_id) {
                $subQuery->where('recommending_id', $employee_id)
                        ->where('status', 0);
            })
            ->orWhere(function ($subQuery) use ($employee_id) {
                $subQuery->where('approver_id', $employee_id)
                        ->whereIn('status', [0, 1]);
            });
        });

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

        $data = $data->get();

        // Add first name and last name to each result
        foreach ($data as $leave) {
            $leave->fname = $leave->employee->firstname ?? null;
            $leave->lname = $leave->employee->lastname ?? null;

            // Load recommending and approving as single objects
            $leave->recommending = $leave->recommending()->select('employee_id', 'asa_user_id', 'firstname', 'lastname')->first();
            $leave->approving = $leave->approving()->select('employee_id', 'asa_user_id', 'firstname', 'lastname')->first();
        }

        $usedCredits = EmployeeLeave::where([
            'employee_leaves.employee_id' => $employee_id,
            'employee_leaves.enabled' => 1
        ])
        ->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')
        ->groupBy('leave_types.name')
        ->get();

        $pendingOrForApprovalCount = EmployeeLeave::where(function ($query) use ($employee_id) {
            $query->where('recommending_id', $employee_id ? $employee_id : '')
                ->orWhere('approver_id', $employee_id ? $employee_id : '');
        })->where(function ($query) use ($employee_id) {
            $query->where(function ($subQuery) use ($employee_id) {
                $subQuery->where('recommending_id', $employee_id)
                        ->where('status', 0);
            })
            ->orWhere(function ($subQuery) use ($employee_id) {
                $subQuery->where('approver_id', $employee_id)
                        ->whereIn('status', [0, 1]);
            });
        })->count();

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

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


    public function overtime_request(Request $request)
    {
        //
        $employee_id = $request->input('employee_id');
        
        $keyword = $request->input('keyword', '');
        $perPage = $request->input('per_page',PHP_INT_MAX);
        $sortBy = $request->input('sortBy', '');
        $sortType = $request->input('sortType', '');

        $year = $request->input('year');
        $status = $request->input('status');

        $data = EmployeeOvertime::with([
            'employee' => fn($employee) => $employee->select('employee_id', 'asa_user_id', 'firstname', 'lastname'),
            // 'recommending' => fn($recommending) => $recommending->select('employee_id', 'asa_user_id', 'firstname', 'lastname'),
            // 'approving'=> fn($approving) => $approving->select('employee_id', 'asa_user_id', 'firstname', 'lastname'),
            ])
            ->where(function ($query) use ($employee_id) {
                $query->where('recommending_id', $employee_id ? $employee_id : '')
                    ->orWhere('approver_id', $employee_id ? $employee_id : '');
            })
            ->select(
                'id', 
                'employee_id', 
                'company_id', 
                'dealer_id', 
                'date_from',
                'date_to',
                'from_time',
                'to_time',
                'hours', 
                'charge_to', 
                'attachment', 
                'description', 
                'meal', 
                'transportation', 
                'status', 
                'recommending_id', 
                'approver_id', 
                'approved_at'
            )
            ->where(function ($query) use ($employee_id) {
                $query->where(function ($subQuery) use ($employee_id) {
                    $subQuery->where('recommending_id', $employee_id)
                            ->where('status', 0);
                })
                ->orWhere(function ($subQuery) use ($employee_id) {
                    $subQuery->where('approver_id', $employee_id)
                            ->whereIn('status', [0, 1]);
                });
            });

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

        $data = $data->get();

        // Add first name and last name to each result
        foreach ($data as $overtime) {
            $overtime->fname = $overtime->employee->firstname ?? null;
            $overtime->lname = $overtime->employee->lastname ?? null;

            // Load recommending and approving as single objects
            $overtime->recommending = $overtime->recommending()->select('employee_id', 'asa_user_id', 'firstname', 'lastname')->first();
            $overtime->approving = $overtime->approving()->select('employee_id', 'asa_user_id', 'firstname', 'lastname')->first();
        }
        
        $pendingOrForApprovalCount = EmployeeOvertime::where(function ($query) use ($employee_id) {
            $query->where('recommending_id', $employee_id ? $employee_id : '')
                ->orWhere('approver_id', $employee_id ? $employee_id : '');
        })->where(function ($query) use ($employee_id) {
            $query->where(function ($subQuery) use ($employee_id) {
                $subQuery->where('recommending_id', $employee_id)
                        ->where('status', 0);
            })
            ->orWhere(function ($subQuery) use ($employee_id) {
                $subQuery->where('approver_id', $employee_id)
                        ->whereIn('status', [0, 1]);
            });
        })->count();

        $response = [
            'data' => $data,
            'pendingOrForApprovalCount' => $pendingOrForApprovalCount,
        ];

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

    public function undertime_request(Request $request)
    {
        //
        $employee_id = $request->input('employee_id');

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

        $year = $request->input('year');
        $status = $request->input('status');

        $data = EmployeeUndertime::with([
            'employee' => fn($employee) => $employee->select('employee_id', 'asa_user_id', 'firstname', 'lastname'),
            // 'recommending' => fn($recommending) => $recommending->select('employee_id', 'asa_user_id', 'firstname', 'lastname'),
            // 'approving'=> fn($approving) => $approving->select('employee_id', 'asa_user_id', 'firstname', 'lastname'),
            ])
            ->where(function ($query) use ($employee_id) {
                $query->where('recommending_id', $employee_id ? $employee_id : '')
                    ->orWhere('approver_id', $employee_id ? $employee_id : '');
            })
            ->select(
                'id', 
                'employee_id', 
                'company_id', 
                'dealer_id', 
                'date',
                'from_time',
                'to_time',
                'hours', 
                'attachment', 
                'description', 
                'status', 
                'recommending_id', 
                'approver_id', 
                'approved_at'
            )
            ->where(function ($query) use ($employee_id) {
                $query->where(function ($subQuery) use ($employee_id) {
                    $subQuery->where('recommending_id', $employee_id)
                            ->where('status', 0);
                })
                ->orWhere(function ($subQuery) use ($employee_id) {
                    $subQuery->where('approver_id', $employee_id)
                            ->whereIn('status', [0, 1]);
                });
            });

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

        $data = $data->get();

        // Add first name and last name to each result
        foreach ($data as $undertime) {
            $undertime->fname = $undertime->employee->firstname ?? null;
            $undertime->lname = $undertime->employee->lastname ?? null;

            // Load recommending and approving as single objects
            $undertime->recommending = $undertime->recommending()->select('employee_id', 'asa_user_id', 'firstname', 'lastname')->first();
            $undertime->approving = $undertime->approving()->select('employee_id', 'asa_user_id', 'firstname', 'lastname')->first();
        }

        $pendingOrForApprovalCount = EmployeeUndertime::where(function ($query) use ($employee_id) {
            $query->where('recommending_id', $employee_id ? $employee_id : '')
                ->orWhere('approver_id', $employee_id ? $employee_id : '');
        })->where(function ($query) use ($employee_id) {
            $query->where(function ($subQuery) use ($employee_id) {
                $subQuery->where('recommending_id', $employee_id)
                        ->where('status', 0);
            })
            ->orWhere(function ($subQuery) use ($employee_id) {
                $subQuery->where('approver_id', $employee_id)
                        ->whereIn('status', [0, 1]);
            });
        })->count();

        $response = [
            'data' => $data,
            'pendingOrForApprovalCount' => $pendingOrForApprovalCount,
        ];

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

    public function travel_request(Request $request)
    {
        //

        $employee_id = $request->input('employee_id');

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

        $year = $request->input('year');
        $status = $request->input('status');

        $data = EmployeeOb::with([
            'employee' => fn($employee) => $employee->select('employee_id', 'asa_user_id', 'firstname', 'lastname'),
            // 'recommending' => fn($recommending) => $recommending->select('employee_id', 'asa_user_id', 'firstname', 'lastname'),
            // 'approving'=> fn($approving) => $approving->select('employee_id', 'asa_user_id', 'firstname', 'lastname'),
            ])
            ->where(function ($query) use ($employee_id) {
                $query->where('recommending_id', $employee_id ? $employee_id : '')
                    ->orWhere('approver_id', $employee_id ? $employee_id : '');
            })
            ->select(
                'id', 
                'employee_id', 
                'company_id', 
                'dealer_id', 
                'date_from',
                'date_to',
                'from_time',
                'to_time',
                'attachment', 
                'description', 
                'destination', 
                'status', 
                'recommending_id', 
                'approver_id', 
                'approved_at'
            )
            ->where(function ($query) use ($employee_id) {
                $query->where(function ($subQuery) use ($employee_id) {
                    $subQuery->where('recommending_id', $employee_id)
                            ->where('status', 0);
                })
                ->orWhere(function ($subQuery) use ($employee_id) {
                    $subQuery->where('approver_id', $employee_id)
                            ->whereIn('status', [0, 1]);
                });
            });

        // $data->where(['employee_id' => $employee_id]);

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

        $data = $data->get();

        // Add first name and last name to each result
        foreach ($data as $travel) {
            $travel->fname = $travel->employee->firstname ?? null;
            $travel->lname = $travel->employee->lastname ?? null;

            // Load recommending and approving as single objects
            $travel->recommending = $travel->recommending()->select('employee_id', 'asa_user_id', 'firstname', 'lastname')->first();
            $travel->approving = $travel->approving()->select('employee_id', 'asa_user_id', 'firstname', 'lastname')->first();
        }

        $pendingOrForApprovalCount = EmployeeOb::where(function ($query) use ($employee_id) {
            $query->where('recommending_id', $employee_id ? $employee_id : '')
                ->orWhere('approver_id', $employee_id ? $employee_id : '');
        })->where(function ($query) use ($employee_id) {
            $query->where(function ($subQuery) use ($employee_id) {
                $subQuery->where('recommending_id', $employee_id)
                        ->where('status', 0);
            })
            ->orWhere(function ($subQuery) use ($employee_id) {
                $subQuery->where('approver_id', $employee_id)
                        ->whereIn('status', [0, 1]);
            });
        })->count();

        $response = [
            'data' => $data,
            'pendingOrForApprovalCount' => $pendingOrForApprovalCount,
        ];

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

    public function offset_availment_request(Request $request)
    {
        //
        $employee_id = $request->input('employee_id');

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

        $year = $request->input('year');
        $status = $request->input('status');

        $data = EmployeeOffset::with([
            'employee' => fn($employee) => $employee->select('employee_id', 'asa_user_id', 'firstname', 'lastname'),
            // 'recommending' => fn($recommending) => $recommending->select('employee_id', 'asa_user_id', 'firstname', 'lastname'),
            // 'approving'=> fn($approving) => $approving->select('employee_id', 'asa_user_id', 'firstname', 'lastname'),
            ])
            ->where(function ($query) use ($employee_id) {
                $query->where('recommending_id', $employee_id ? $employee_id : '')
                    ->orWhere('approver_id', $employee_id ? $employee_id : '');
            })
            ->select(
                'id', 
                'employee_id', 
                'company_id', 
                'dealer_id', 
                'date_from',
                'date_to',
                'days',
                'hours',
                'attachment', 
                'description', 
                'status', 
                'recommending_id', 
                'approver_id', 
                'approved_at',
            )
            ->where(function ($query) use ($employee_id) {
                $query->where(function ($subQuery) use ($employee_id) {
                    $subQuery->where('recommending_id', $employee_id)
                            ->where('status', 0);
                })
                ->orWhere(function ($subQuery) use ($employee_id) {
                    $subQuery->where('approver_id', $employee_id)
                            ->whereIn('status', [0, 1]);
                });
            });

        $availableOffset = '';
        $usedOffset = '';

        // $data->where(['employee_id' => $employee_id]);

        $availableOffset = OffsetRemaining::where('employee_id', '=', $employee_id)
        ->pluck('remaining');

        $usedOffset = EmployeeOffset::select(DB::raw('SUM(hours) as totalOffset'))
            ->where('employee_id', '=', $employee_id)
            ->where('status', '!=', '99')
            ->where('enabled', '=', '1')
            ->first();

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

        $data = $data->get();

        // Add first name and last name to each result
        foreach ($data as $availment) {
            $availment->fname = $availment->employee->firstname ?? null;
            $availment->lname = $availment->employee->lastname ?? null;

            // Load recommending and approving as single objects
            $availment->recommending = $availment->recommending()->select('employee_id', 'asa_user_id', 'firstname', 'lastname')->first();
            $availment->approving = $availment->approving()->select('employee_id', 'asa_user_id', 'firstname', 'lastname')->first();
        }

        $pendingOrForApprovalCount = EmployeeOffset::where(function ($query) use ($employee_id) {
            $query->where('recommending_id', $employee_id ? $employee_id : '')
                ->orWhere('approver_id', $employee_id ? $employee_id : '');
        })->where(function ($query) use ($employee_id) {
            $query->where(function ($subQuery) use ($employee_id) {
                $subQuery->where('recommending_id', $employee_id)
                        ->where('status', 0);
            })
            ->orWhere(function ($subQuery) use ($employee_id) {
                $subQuery->where('approver_id', $employee_id)
                        ->whereIn('status', [0, 1]);
            });
        })->count();

        $response = [
            'data' => $data,
            'available_offset' => $availableOffset,
            'offset_used' => $usedOffset,
            'pendingOrForApprovalCount' => $pendingOrForApprovalCount,
        ];

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

    public function offset_earning_request(Request $request)
    {
        $employee_id = $request->input('employee_id');
        
        $keyword = $request->input('keyword', '');
        $perPage = $request->input('per_page',PHP_INT_MAX);
        $sortBy = $request->input('sortBy', '');
        $sortType = $request->input('sortType', '');

        $year = $request->input('year');
        $status = $request->input('status');
        
        $data = EmployeeOffsetEarning::with([
            'employee' => fn($employee) => $employee->select('employee_id', 'asa_user_id', 'firstname', 'lastname'),
            // 'recommending' => fn($recommending) => $recommending->select('employee_id', 'asa_user_id', 'firstname', 'lastname'),
            // 'approving'=> fn($approving) => $approving->select('employee_id', 'asa_user_id', 'firstname', 'lastname'),
            ])
            ->where(function ($query) use ($employee_id) {
                $query->where('recommending_id', $employee_id ? $employee_id : '')
                    ->orWhere('approver_id', $employee_id ? $employee_id : '');
            })
            ->select(
                'id', 
                'employee_id', 
                'company_id', 
                'dealer_id', 
                'date',
                'hours',
                'from_time',
                'to_time',
                'attachment', 
                'description', 
                'status', 
                'recommending_id', 
                'approver_id', 
                'approved_at',
            )
            ->where(function ($query) use ($employee_id) {
                $query->where(function ($subQuery) use ($employee_id) {
                    $subQuery->where('recommending_id', $employee_id)
                            ->where('status', 0);
                })
                ->orWhere(function ($subQuery) use ($employee_id) {
                    $subQuery->where('approver_id', $employee_id)
                            ->whereIn('status', [0, 1]);
                });
            });

        // $data->where(['employee_id' => $employee_id]);

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

        $data = $data->get();

        // Add first name and last name to each result
        foreach ($data as $earning) {
            $earning->fname = $earning->employee->firstname ?? null;
            $earning->lname = $earning->employee->lastname ?? null;

            // Load recommending and approving as single objects
            $earning->recommending = $earning->recommending()->select('employee_id', 'asa_user_id', 'firstname', 'lastname')->first();
            $earning->approving = $earning->approving()->select('employee_id', 'asa_user_id', 'firstname', 'lastname')->first();
        }

        $availableOffset = [];
        $offsetRemaining = [];

        $availableOffset = EmployeeOffsetEarning::where([
            'employee_id' => $employee_id,
            'enabled' => 1
        ])
        ->where('status', '=', '2')
        ->selectRaw('SUM(hours) as total')
        ->groupBy('employee_offset_earnings.hours')
        ->first();

        $offsetRemaining = OffsetRemaining::where([
            'employee_id' => $employee_id,
            'enabled' => 1
        ])
        ->select('remaining')
        ->first();

        $pendingOrForApprovalCount = EmployeeOffsetEarning::where(function ($query) use ($employee_id) {
            $query->where('recommending_id', $employee_id ? $employee_id : '')
                ->orWhere('approver_id', $employee_id ? $employee_id : '');
        })->where(function ($query) use ($employee_id) {
            $query->where(function ($subQuery) use ($employee_id) {
                $subQuery->where('recommending_id', $employee_id)
                        ->where('status', 0);
            })
            ->orWhere(function ($subQuery) use ($employee_id) {
                $subQuery->where('approver_id', $employee_id)
                        ->whereIn('status', [0, 1]);
            });
        })->count();

        $response = [
            'data' => $data,
            'available_offset' => $availableOffset,
            'offset_remaining' => $offsetRemaining,
            'pendingOrForApprovalCount' => $pendingOrForApprovalCount,
        ];

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

    public function time_adjustment(Request $request)
    {
        $employee_id = $request->input('employee_id');

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

        $year = $request->input('year');
        $status = $request->input('status');

        $data = EmployeeTimeAdjustment::with([
            'employee' => fn($employee) => $employee->select('employee_id', 'asa_user_id', 'firstname', 'lastname'),
            // 'recommending' => fn($recommending) => $recommending->select('employee_id', 'asa_user_id', 'firstname', 'lastname'),
            // 'approving'=> fn($approving) => $approving->select('employee_id', 'asa_user_id', 'firstname', 'lastname'),
            ])
            ->where(function ($query) use ($employee_id) {
                $query->where('recommending_id', $employee_id ? $employee_id : '')
                    ->orWhere('approver_id', $employee_id ? $employee_id : '');
            })
            ->select(
                'id', 
                'employee_id', 
                'company_id', 
                'dealer_id', 
                'date',
                'in_out',
                'time',
                'attachment', 
                'description', 
                'status', 
                'recommending_id', 
                'approver_id', 
                'approved_at'
            )
            ->where(function ($query) use ($employee_id) {
                $query->where(function ($subQuery) use ($employee_id) {
                    $subQuery->where('recommending_id', $employee_id)
                            ->where('status', 0);
                })
                ->orWhere(function ($subQuery) use ($employee_id) {
                    $subQuery->where('approver_id', $employee_id)
                            ->whereIn('status', [0, 1]);
                });
            });

        // $data->where(['employee_id' => $employee_id]);

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

        $data = $data->get();

        // Add first name and last name to each result
        foreach ($data as $time_adjustment) {
            $time_adjustment->fname = $time_adjustment->employee->firstname ?? null;
            $time_adjustment->lname = $time_adjustment->employee->lastname ?? null;

            // Load recommending and approving as single objects
            $time_adjustment->recommending = $time_adjustment->recommending()->select('employee_id', 'asa_user_id', 'firstname', 'lastname')->first();
            $time_adjustment->approving = $time_adjustment->approving()->select('employee_id', 'asa_user_id', 'firstname', 'lastname')->first();
        }

        $pendingOrForApprovalCount = EmployeeTimeAdjustment::where(function ($query) use ($employee_id) {
            $query->where('recommending_id', $employee_id ? $employee_id : '')
                ->orWhere('approver_id', $employee_id ? $employee_id : '');
        })->where(function ($query) use ($employee_id) {
            $query->where(function ($subQuery) use ($employee_id) {
                $subQuery->where('recommending_id', $employee_id)
                        ->where('status', 0);
            })
            ->orWhere(function ($subQuery) use ($employee_id) {
                $subQuery->where('approver_id', $employee_id)
                        ->whereIn('status', [0, 1]);
            });
        })->count();

        $response = [
            'data' => $data,
            'pendingOrForApprovalCount' => $pendingOrForApprovalCount,
        ];

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

    public function offset_lists(Request $request)
    {
        $employee_id = $request->input('employee_id');

        $year = $request->input('year');
        
        $data = EmployeeOffsetEarning::with([
            'employee' => fn($employee) => $employee->select('employee_id', 'asa_user_id', 'firstname', 'lastname')
            ])
            ->select(
                'id', 
                'employee_id', 
                'company_id', 
                'dealer_id', 
                'date',
                'from_time',
                'to_time',
                'hours',
                'attachment', 
                'description', 
                'status', 
                'recommending_id', 
                'approver_id', 
                'approved_at',
            )
            ->where('employee_offset_earnings.employee_id', $employee_id);

        if(!empty($year)){
            $data->whereYear('date', $year);
        } else {
            $year = date('Y');
            $data->whereYear('date', $year);
        }

        $data = $data->get();

        $totalHours = EmployeeOffsetEarning::where('employee_id', $employee_id)
            ->where('status', '!=', 99)
            ->whereYear('date', !empty($year) ? $year : date('Y'))
            ->sum('hours');

        $offsetRemaining = OffsetRemaining::where([
            'employee_id' => $employee_id,
            'enabled' => 1
        ])
        ->select('remaining')
        ->first();

        $response = [
            'data' => $data,
            'offset_remaining' => $offsetRemaining,
            'total_hours' => $totalHours
        ];

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

    public function offset_availment_lists(Request $request)
    {
        $employee_id = $request->input('employee_id');

        $year = $request->input('year');
        
        $data = EmployeeOffset::with([
            'employee' => fn($employee) => $employee->select('employee_id', 'asa_user_id', 'firstname', 'lastname')
            ])
            ->select(
                'id', 
                'employee_id', 
                'company_id', 
                'dealer_id', 
                'date_from',
                'date_to',
                'from_time',
                'to_time',
                'hours',
                'attachment', 
                'description', 
                'status', 
                'recommending_id', 
                'approver_id', 
                'approved_at',
            )
            ->where('employee_offsets.employee_id', $employee_id);

            if(!empty($year)){
                $data->whereYear('date_from', $year);
                $data->orWhereYear('date_to', $year);
            } else {
                $year = date('Y');
                $data->whereYear('date_from', $year);
                $data->orWhereYear('date_to', $year);
            }
    
            $data = $data->get();
    
            $totalHours = EmployeeOffset::where('employee_id', $employee_id)
                ->where('status', '!=', 99)
                ->whereYear('date_from', !empty($year) ? $year : date('Y'))
                ->orWhereYear('date_to', !empty($year) ? $year : date('Y'))
                ->sum('hours');

        $offsetRemaining = OffsetRemaining::where([
            'employee_id' => $employee_id,
            'enabled' => 1
        ])
        ->select('remaining')
        ->first();

        $response = [
            'data' => $data,
            'offset_remaining' => $offsetRemaining,
            'total_hours' => $totalHours
        ];

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