<?php

namespace App\Http\Controllers\Employee;

use App\Http\Controllers\Controller;
use App\Http\Repository\UtilityRepository;
use App\Models\Employee\SchEmployee;
use App\Models\Employee\SchEmployeeOffday;
use App\Models\Employee\SchEmployeeRoster;
use App\Models\Employee\SchEmployeeSchedule;
use App\Models\Employee\SchEmployeeService;
use App\Models\Employee\SchSalary;
use App\Models\Employee\SchShift;
use App\Models\Services\SchServiceGroup;
use App\Models\Services\SchServiceCategory;
use App\Models\Services\SchServices;
use App\Models\Settings\CmnBusinessHoliday;
use App\Models\Settings\CmnBusinessHour;
use App\Models\Settings\CmnCompany;
use App\Models\User;
use App\Models\UserManagement\SecUserBranch;
use Carbon\Carbon;
use SplFileObject;
use App\Models\UserManagement\SecUserRole;
use ErrorException;
use Exception;
use Illuminate\Http\Request;
use Illuminate\Support\Arr;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Validator;

class EmployeeController extends Controller
{
    public function __construct()
    {
        $this->middleware('auth');
    }

    public function employee()
    {
        $isMOGAdminOrSysAdmin = SecUserRole::where('sec_user_id', auth()->id())
                                ->where(function ($query) {
                                    $query->whereHas('role', function ($roleQuery) {
                                        $roleQuery->where('name', 'MOG Admin');
                                    })
                                    ->orWhereHas('user', function ($userQuery) {
                                        $userQuery->where('is_sys_adm', 1);
                                    });
                                })
                                ->exists();

        $userRole = auth()->user()->secUserRole->role->name; // Assuming you have a 'role' field in your users table
        return view('employee.employee',  compact('userRole', 'isMOGAdminOrSysAdmin'));
    }

    
    public function uploadRoster(Request $data)
    {
        DB::beginTransaction();
        try {
            // Validate the file input
            $validator = Validator::make($data->all(), [
                'upload_file' => 'required|mimes:csv,txt|max:2048',
            ]);

            if (!$validator->fails()) {
                $creatorId = auth()->id();
                $uploadFile = $data->file('upload_file');

                if ($uploadFile != null) {
                    $uploadFile = UtilityRepository::saveFileCustom($uploadFile, ['text/csv', 'application/vnd.ms-excel']);
                }
                //dd($uploadFile);
                // Get the full path of the saved file
                $fullFilePath = public_path($uploadFile);
                //dd($fullFilePath, $uploadFile->getRealPath(), 'test');
                $csvFile = new SplFileObject($uploadFile->getRealPath(), 'r');
                //dd($csvFile);
                $csvFile->setFlags(SplFileObject::READ_CSV | SplFileObject::SKIP_EMPTY | SplFileObject::READ_AHEAD);

                $csv = $csvFile->fgets();
                //dd($csv);
                $isFirstRow = true; // Flag to detect the first row

                // Initialize an array to store the roster data by days
                $rosterDataByDays = [];

                foreach ($csvFile as $row) {
                    if ($isFirstRow) {
                        $isFirstRow = false;
                        continue; // Skip the first row (header)
                    }

                    $monthMap = [
                        "January" => 1,
                        "February" => 2,
                        "March" => 3,
                        "April" => 4,
                        "May" => 5,
                        "June" => 6,
                        "July" => 7,
                        "August" => 8,
                        "September" => 9,
                        "October" => 10,
                        "November" => 11,
                        "December" => 12,
                    ];
                    
                    // Convert month name to number
                    $monthNumber = $row[0];
                    $month = $monthMap[$monthNumber] ?? null;
                    $year = $row[1];
                    $branchId = $row[2];
                    $employeeId = $row[3];

                    $schEmployeeId = SchEmployee::where('employee_id', $employeeId)->value('id');
                    $rosterData = $this->splitArrayAtIndex($row, 6);
                    //dd($rosterData);
                    $day = 1;
                    foreach ($rosterData as $value) {
                        $date = Carbon::createFromFormat('Y-m-d', $year . '-' . $month . '-' . $day)->format('Y-m-d');
                        $shiftName = SchShift::where('legends', $value)->value('name');
                        $shiftId = SchShift::where('legends', $value)->value('id');
                        $isOffday = !$shiftName;

                        $matchData = [
                            'sch_employee_id' => $schEmployeeId,
                            'start_date' => $date,
                            'end_date' => $date
                        ];

                        $updateData = [
                            'title' => $shiftName ? $shiftName : 'Off day',
                            'sch_shift_id' => $shiftId,
                            'is_offday' => $isOffday,
                            'created_by' => $creatorId,
                            'updated_by' => $creatorId,
                            'created_at' => now(),
                            'updated_at' => now()
                        ];

                        // Store the data in the array, grouped by date
                        $rosterDataByDays[$date][] = [
                            'matchData' => $matchData,
                            'updateData' => $updateData
                        ];

                        $day++;
                    }
                }
                //dd($rosterDataByDays);
                // Process each day in a separate transaction
                DB::enableQueryLog();
                DB::beginTransaction();
                try {
                    foreach ($rosterDataByDays as $date => $entries) {
                    
                        foreach ($entries as $entry) {
                            $q = SchEmployeeRoster::updateOrCreate($entry['matchData'], $entry['updateData']);
                        }
                        DB::commit();
                    }
                } catch (\Exception $e) {
                    DB::rollBack();
                    \Log::error('Error processing roster data for date ' . $date . ': ' . $e->getMessage());
                    return $this->apiResponse(['status' => '-1', 'data' => 'An error occurred while processing the roster data for date ' . $date . '.'], 500);
                }
            

                return $this->apiResponse(['status' => '1', 'data' => 'Roster data uploaded successfully.'], 200);
            } else {
                return $this->apiResponse(['status' => '0', 'data' => $validator->errors()], 400);
            }
        } catch (\Exception $e) {
            DB::rollBack();
            \Log::error('Error uploading roster: ' . $e->getMessage());
            return $this->apiResponse(['status' => '-1', 'data' => 'An error occurred while uploading the roster.'], 500);
        }
    }

    public function uploadEmployeeService(Request $data)
    {
        DB::beginTransaction();
        try {
            // Validate the file input
            $validator = Validator::make($data->all(), [
                'upload_file' => 'required|mimes:csv,txt|max:2048',
            ]);

            if (!$validator->fails()) {
                $creatorId = auth()->id();
                $uploadFile = $data->file('upload_file');

                if ($uploadFile != null) {
                    $uploadFile = UtilityRepository::saveFileCustom($uploadFile, ['text/csv', 'application/vnd.ms-excel']);
                }
                // dd($uploadFile);
                // Get the full path of the saved file
                $fullFilePath = public_path($uploadFile);
                // dd($fullFilePath, $uploadFile->getRealPath(), 'test');
                $csvFile = new SplFileObject($uploadFile->getRealPath(), 'r');
                // dd($csvFile);
                $csvFile->setFlags(SplFileObject::READ_CSV | SplFileObject::SKIP_EMPTY | SplFileObject::READ_AHEAD);

                $csv = $csvFile->fgets();
                // dd($csv);
                $isFirstRow = true; // Flag to detect the first row

                // Initialize an array to store the roster data by days
                $rosterDataByServices = [];
                $serviceData = [];

                foreach ($csvFile as $row) {

                    if ($isFirstRow) {
                        $services = array_slice($row, 3);
                        
                        foreach ($services as $service) {
                            $serviceData[] = SchServices::where('title', $service)->select('id')->first()->id;
                        }
                       
                        $isFirstRow = false;
                        continue; // Skip the first row (header)
                    }

                    // Ensure row has enough data
                    $requiredColumns = 3 + count($serviceData);
                    if (count($row) < $requiredColumns) {
                        continue;
                    }
                    
                    // Convert month name to number
                    $employeeId = $row[1];
                    // dd($employeeId);

                    $schEmployeeId = SchEmployee::where('employee_id', $employeeId)->value('id');
                    // dd($schEmployeeId);
                    $rosterData = $this->splitArrayAtIndex($row, 3);
                    // dd($rosterData);
                    // Fetch all service data once to avoid querying repeatedly
                    $allServices = SchServices::whereIn('id', $serviceData)->pluck('id', 'id'); // Create a map of [id => id]
                    
                    foreach ($rosterData as $key => $value) {
                        // Ensure the value is either 'Y' or 'N' before proceeding
                        if (strtolower($value) === 'y' || strtolower($value) === 'n') {
                            $schServiceId = $allServices[$serviceData[$key]] ?? null;
                            
                            if ($schServiceId) {
                                $matchData = [
                                    'sch_employee_id' => $schEmployeeId,
                                    'sch_service_id' => $schServiceId,
                                ];

                                $updateData = [
                                    'fees' => 0.0,
                                    'status' => $value === 'Y' ? 1 : 0, // Conditionally set status
                                    'created_by' => $creatorId,
                                    'updated_by' => $creatorId,
                                    'created_at' => now(),
                                    'updated_at' => now(),
                                ];

                                // Store the data in the array, grouped by employee ID
                                $rosterDataByServices[$employeeId][] = [
                                    'matchData' => $matchData,
                                    'updateData' => $updateData,
                                ];
                            }
                        }
                    }
                }
                
                
                $bulkInsertData = [];

                foreach ($rosterDataByServices as $employeeId => $entries) {
                    foreach ($entries as $entry) {
                        $bulkInsertData[] = array_merge($entry['matchData'], $entry['updateData']);
                    }
                }

                if (!empty($bulkInsertData)) {
                    $batchSize = 50;
                    $chunks = array_chunk($bulkInsertData, $batchSize);

                    foreach ($chunks as $chunk) {
                        SchEmployeeService::upsert(
                            $chunk, 
                            ['sch_employee_id', 'sch_service_id'], 
                            ['fees', 'status', 'updated_by', 'updated_at'] 
                        );
                    }
            
                    DB::commit(); 
                }

                return $this->apiResponse(['status' => '1', 'data' => 'Employee service data uploaded successfully. This pop-up will close shortly—please wait a moment.'], 200);
            } else {
                return $this->apiResponse(['status' => '0', 'data' => $validator->errors()], 400);
            }
        } catch (\Exception $e) {
            DB::rollBack();
            \Log::error('Error uploading roster: ' . $e->getMessage());
            return $this->apiResponse(['status' => '-1', 'data' => 'An error occurred while uploading the employee service.'], 500);
        }
    }

    public function createEmployee(Request $data)
    {
        DB::beginTransaction();
        try {
            $validator = Validator::make($data->toArray(), [
                'full_name' => ['required', 'string', 'max:200'],
            ]);

            if (!$validator->fails()) {

                $creatorId =  auth()->id();
                $employeeImage = $data->image_url;
                if ($employeeImage != null) {
                    $employeeImage = UtilityRepository::saveFile($employeeImage, ['image/png', 'image/png', 'image/jpg', 'image/jpeg']);
                } else {
                    $employeeImage = 'img/user_default.jpeg';
                }

                $idCardImage = $data->id_card;
                if ($idCardImage != null) {
                    $idCardImage = UtilityRepository::saveFile($idCardImage, ['image/png', 'image/png', 'image/jpg', 'image/jpeg']);
                }
                $passportImage = $data->passport;
                if ($passportImage != null) {
                    $passportImage = UtilityRepository::saveFile($passportImage, ['image/png', 'image/png', 'image/jpg', 'image/jpeg']);
                }

                $employee = SchEmployee::create([
                    "full_name" => $data->full_name,
                    "employee_id" => $data->employee_id,
                    "email_address" => "test@gmail.com",
                    "country_code" => $data->country_code,
                    "contact_no" => $data->contact_no,
                    "cmn_branch_id" => $data->cmn_branch_id,
                    "hrm_department_id" => UtilityRepository::emptyToNull($data->hrm_department_id),
                    "hrm_designation_id" => UtilityRepository::emptyToNull($data->hrm_designation_id),
                    "user_id" => null,
                    "gender" => $data->gender,
                    "dob" => UtilityRepository::emptyToNull($data->dob),
                    "specialist" => $data->specialist,
                    "present_address" => $data->present_address,
                    "permanent_address" => $data->permanent_address,
                    "note" => $data->note,
                    "salary" => 1,
                    "commission" => $data->commission??0,
                    "pay_commission_based_on" => $data->pay_commission_based_on??0,
                    "target_service_amount" => $data->target_service_amount??0,
                    "image_url" => $employeeImage,
                    "id_card" => $idCardImage,
                    "passport" => $passportImage,
                    "status" => $data->status,
                    "created_by" => $creatorId
                ]);
                $employeeId = $employee->id;

                foreach ($data->service as $item) {
                    if (Arr::exists($item, 'sch_service_id') && $item['emp_service_id'] > 0 && $data->status == 1) {
                        SchEmployeeService::where('id', $item['emp_service_id'])->update([
                            'fees' => $item['fees']??0,
                            'status' => 1,
                            "updated_by" => $creatorId
                        ]);
                    } else if ((Arr::exists($item, 'sch_service_id') == false && $item['emp_service_id'] > 0) || $data->status == 0) {
                        SchEmployeeService::where('id', $item['emp_service_id'])->update([
                            'fees' => $item['fees']??0,
                            'status' => 0,
                            "updated_by" => $creatorId
                        ]);
                    } else if (Arr::exists($item, 'sch_service_id') && $item['emp_service_id'] < 1 && $data->status == 1) {
                        SchEmployeeService::create([
                            'sch_employee_id' => $employeeId,
                            'sch_service_id' => $item['sch_service_id'],
                            'fees' => $item['fees']??0,
                            'status' => 1,
                            "created_by" => $creatorId
                        ]);
                    }
                }

                DB::commit();
                return $this->apiResponse(['status' => '1', 'data' => ['employee_id' => $employeeId]], 200);
            }
            return $this->apiResponse(['status' => '500', 'data' => $validator->errors()], 400);
        } catch (ErrorException $ex) {
            return  $this->apiResponse(['status' => '-501', 'data' => $ex->getMessage()], 400);
        } catch (Exception $ex) {
            DB::rollBack();
            return $this->apiResponse(['status' => '501', 'data' => $ex], 400);
        }
    }

    public function updateEmployee(Request $data)
    {
        DB::beginTransaction();
        try {
            $validator = Validator::make($data->toArray(), [
                'full_name' => ['required', 'string', 'max:200'],

            ]);

            if (!$validator->fails()) {

                $creatorId =  auth()->id();
                $employeeImage = $data->image_url;
                if ($employeeImage != null) {
                    $employeeImage = UtilityRepository::saveFile($employeeImage, ['image/png', 'image/png', 'image/jpg', 'image/jpeg']);
                }

                $idCardImage = $data->id_card;
                if ($idCardImage != null) {
                    $idCardImage = UtilityRepository::saveFile($idCardImage, ['image/png', 'image/png', 'image/jpg', 'image/jpeg']);
                }
                $passportImage = $data->passport;
                if ($passportImage != null) {
                    $passportImage = UtilityRepository::saveFile($passportImage, ['image/png', 'image/png', 'image/jpg', 'image/jpeg']);
                }

                $readyForUpdate = [
                    "full_name" => $data->full_name,
                    "employee_id" => $data->employee_id,
                    "email_address" => "test@gmail.com",
                    "country_code" => $data->country_code,
                    "contact_no" => $data->contact_no,
                    "cmn_branch_id" => $data->cmn_branch_id,
                    "hrm_department_id" => UtilityRepository::emptyToNull($data->hrm_department_id),
                    "hrm_designation_id" => UtilityRepository::emptyToNull($data->hrm_designation_id),
                    "user_id" => null,
                    "gender" => $data->gender,
                    "dob" => UtilityRepository::emptyToNull($data->dob),
                    "specialist" => $data->specialist,
                    "present_address" => $data->present_address,
                    "permanent_address" => $data->permanent_address,
                    "note" => $data->note,
                    "status" => $data->status,
                    "updated_by" => $creatorId,
                    "salary" => 1,
                    "commission" => $data->commission??0,
                    "pay_commission_based_on" => $data->pay_commission_based_on??0,
                    "target_service_amount" => $data->target_service_amount??0,
                ];
                if ($employeeImage != null)
                    $readyForUpdate["image_url"] = $employeeImage;

                if ($idCardImage != null)
                    $readyForUpdate["id_card"] = $idCardImage;

                if ($passportImage != null)
                    $readyForUpdate["passport"] = $passportImage;

                $employee = SchEmployee::find($data['id']);
                $employee->fill($readyForUpdate);
                $employee->save(); // This triggers the updating event

                foreach ($data->service as $item) {
                    if (Arr::exists($item, 'sch_service_id') && $item['emp_service_id'] > 0 && $data->status == 1) {
                        SchEmployeeService::where('id', $item['emp_service_id'])->update([
                            'fees' => $item['fees']??0,
                            'status' => 1,
                            "updated_by" => $creatorId
                        ]);
                    } else if ((Arr::exists($item, 'sch_service_id') == false && $item['emp_service_id'] > 0) || $data->status == 0) {
                        SchEmployeeService::where('id', $item['emp_service_id'])->update([
                            'fees' => $item['fees']??0,
                            'status' => 0,
                            "updated_by" => $creatorId
                        ]);
                    } else if (Arr::exists($item, 'sch_service_id') && $item['emp_service_id'] < 1 && $data->status == 1) {
                        SchEmployeeService::create([
                            'sch_employee_id' => $data['id'],
                            'sch_service_id' => $item['sch_service_id'],
                            'fees' => $item['fees']??0,
                            'status' => 1,
                            "created_by" => $creatorId
                        ]);
                    }
                }

                // foreach ($data->business as $item) {
                //     $employeeSchedule = SchEmployeeSchedule::where('sch_employee_id', $data['id'])->where("day", $item['day'])->first();
                //     if ($employeeSchedule != null) {
                //         //update
                //         $employeeSchedule->update([
                //             "start_time" => $item['start_time'],
                //             "end_time" => $item['end_time'],
                //             "break_start_time" => $item['break_start_time'],
                //             "break_end_time" => $item['break_end_time'],
                //             "is_off_day" => Arr::exists($item, 'is_off_day') ? $item['is_off_day'] : 0,
                //             "updated_by" => $creatorId,
                //         ]);
                //     } else {
                //         //insert
                //         SchEmployeeSchedule::create([
                //             "sch_employee_id" => $data['id'],
                //             "day" => $item['day'],
                //             "start_time" => $item['start_time'],
                //             "end_time" => $item['end_time'],
                //             "break_start_time" => $item['break_start_time'],
                //             "break_end_time" => $item['break_end_time'],
                //             "is_off_day" => Arr::exists($item, 'is_off_day') ? $item['is_off_day'] : 0,
                //             "created_by" => $creatorId
                //         ]);
                //     }
                // }
                DB::commit();
                return $this->apiResponse(['status' => '1', 'data' => ''], 200);
            }
            return $this->apiResponse(['status' => '500', 'data' => $validator->errors()], 400);
        } catch (ErrorException $ex) {
            return  $this->apiResponse(['status' => '-501', 'data' => $ex->getMessage()], 400);
        } catch (Exception $ex) {
            DB::rollBack();
            return $this->apiResponse(['status' => '501', 'data' => $ex], 400);
        }
    }

    public function deleteEmployee(Request $data)
    {
        DB::beginTransaction();

        try {
            $employee = SchEmployee::find($data->id);

            if (!$employee) {
                return $this->apiResponse(['status' => '404', 'message' => 'Employee not found'], 404);
            }

            SchEmployeeService::where('sch_employee_id', $data->id)->delete();

            SchEmployeeSchedule::where('sch_employee_id', $data->id)->delete();

            SchEmployeeRoster::where('sch_employee_id', $data->id)->delete();

            $employee->delete();

            DB::commit();

            return $this->apiResponse(['status' => '1', 'message' => 'Employee and related records deleted successfully'], 200);
        } catch (Exception $ex) {
            DB::rollBack();

            return $this->apiResponse(['status' => '501', 'message' => 'Failed to delete employee', 'error' => $ex->getMessage()], 400);
        }
    }

    public function saveOrUpdateEmployeeOffDay(Request $data)
    {
        try {
            $insertedId = $data->id;
            if ($insertedId == "wh")
                return $this->apiResponse(['status' => '505', 'data' => 'You are not allow to update.'], 400);

            $validator = Validator::make($data->toArray(), [
                'title' => ['required', 'string', 'max:200'],
                'start_date' => ['required', 'date'],
                'end_date' => ['required', 'date'],
            ]);

            if (!$validator->fails()) {
                if ($data->id == null || $data->id == "") {
                    $data['created_by'] = auth()->id();
                    $data['id'] = null;
                    $resp = SchEmployeeOffday::create($data->all());
                    $insertedId = $resp->id;
                } else {
                    $data['updated_by'] = auth()->id();
                    SchEmployeeOffday::where('id', $data->id)->update($data->all());
                }

                $rtr = SchEmployeeOffday::where('id', $insertedId)->select('id', 'title', 'start_date', 'end_date')->first();
                return $this->apiResponse(['status' => '1', 'data' => $rtr], 200);
            }
            return $this->apiResponse(['status' => '500', 'data' => $validator->errors()], 400);
        } catch (Exception $ex) {
            if ($ex->errorInfo[2] == "Column 'sch_employee_id' cannot be null")
                return $this->apiResponse(['status' => '505', 'data' => 'Need to save employee first.'], 400);
            return $this->apiResponse(['status' => '501', 'data' => $ex], 400);
        }
    }

    public function saveOrUpdateEmployeeRoster(Request $data)
    {
        try {
            $insertedId = $data->id;
            if ($insertedId == "wh")
                return $this->apiResponse(['status' => '505', 'data' => 'You are not allow to update.'], 400);

            $validator = Validator::make($data->toArray(), [
                'start_date' => ['required', 'date'],
                'end_date' => ['required', 'date'],
            ]);

            if (!$validator->fails()) {
                if ($data->id == null || $data->id == "") {
                    $data['created_by'] = auth()->id();
                    $data['id'] = null;
                    if ($data->roster_type == 1) {
                        $data['title'] = SchShift::where('id', $data->shifts_dropdown)->pluck('name')->first();
                        $data['is_offday'] = false;
                        $data['sch_shift_id'] = $data['shifts_dropdown'];
                    }elseif ($data->roster_type == 2) {
                        $data['is_offday'] = true;
                        $data['sch_shift_id'] = null;
                    }
                    unset($data['roster_type']);
                    unset($data['shifts_dropdown']);
                    $resp = SchEmployeeRoster::create($data->all());
                    $insertedId = $resp->id;
                } else {
                    $data['updated_by'] = auth()->id();
                    if ($data->roster_type == 1) {
                        $data['title'] = SchShift::where('id', $data->shifts_dropdown)->pluck('name')->first();
                        $data['is_offday'] = false;
                        $data['sch_shift_id'] = $data['shifts_dropdown'];
                    }elseif ($data->roster_type == 2) {
                        $data['is_offday'] = true;
                        $data['sch_shift_id'] = null;
                    }
                    unset($data['roster_type']);
                    unset($data['shifts_dropdown']);
                    SchEmployeeRoster::where('id', $data->id)->update($data->all());
                }

                $rtr = SchEmployeeRoster::where('id', $insertedId)->select('id', 'title', 'start_date', 'end_date', 'is_offday')->first();
                $colorCode = SchShift::where('name', $rtr->title)->value('color_code');

                $rtr->color_code = $colorCode ? $colorCode : '#A0B46C'; //Default color for off days

                return $this->apiResponse(['status' => '1', 'data' => $rtr], 200);
            }
            return $this->apiResponse(['status' => '500', 'data' => $validator->errors()], 400);
        } catch (Exception $ex) {
            if ($ex->errorInfo[2] == "Column 'sch_employee_id' cannot be null")
                return $this->apiResponse(['status' => '505', 'data' => 'Need to save employee first.'], 400);
            return $this->apiResponse(['status' => '501', 'data' => $ex], 400);
        }
    }

    public function updateOffdayByMove(Request $data)
    {
        try {
            $insertedId = $data->id;
            if ($insertedId == "wh")
                return $this->apiResponse(['status' => '505', 'data' => 'You are not allow to update.'], 400);

            $validator = Validator::make($data->toArray(), [
                'title' => ['required', 'string', 'max:200'],
                'start_date' => ['required', 'date'],
                'end_date' => ['required', 'date'],
            ]);

            if (!$validator->fails()) {
                $offday = SchEmployeeOffday::where('id', $data->id)->first();
                $offday->start_date = $data->start_date;
                $offday->end_date = $data->end_date;
                $offday->updated_by = auth()->id();
                return $this->apiResponse(['status' => '1', 'data' => ''], 200);
            }
            return $this->apiResponse(['status' => '500', 'data' => $validator->errors()], 400);
        } catch (Exception $ex) {
            return $this->apiResponse(['status' => '501', 'data' => $ex], 400);
        }
    }

    public function getEmployeeCalendar(Request $data)
    {
        try {

            $start_date = $data->year . '-01-01';
            $end_date = $data->year . '-12-31';

            //employee offday
            $employeeOffDay = SchEmployeeOffday::where('sch_employee_id', $data->sch_employee_id)
            ->where('start_date', '<=', $end_date)->where('end_date', '>=', $start_date)
            ->selectRaw(
                "id,
                start_date as start,
                end_date as end,
                title"
            )->get();

            $businessHoliday = CmnBusinessHoliday::where('cmn_branch_id', $data->cmn_branch_id)
            ->where('start_date', '<=', $end_date)->where('end_date', '>=', $start_date)
            ->selectRaw(
                "id,
                start_date as start,
                end_date as end,
                title"
            )->get();

            $businessHours = CmnBusinessHour::where('is_off_day', 1)->where('cmn_branch_id', $data->cmn_branch_id)->select('day')->get();
            $weeklyHoliday = array();
            while (strtotime($start_date) <= strtotime($end_date)) {
                $timestamp = strtotime($start_date);
                $day = date('w', $timestamp);
                foreach ($businessHours as $val) {
                    if ($day == $val['day']) {
                        $weeklyHoliday[] = [
                            'id' => 'wh',
                            "title" => "Weekly Holiday",
                            "start" => $start_date,
                            "end" => $start_date
                        ];
                    }
                }
                $start_date = date("Y-m-d", strtotime("+1 days", strtotime($start_date)));
            }

            return $this->apiResponse([
                'status' => '1',
                'data' =>
                [
                    'employeeOffday' => $employeeOffDay,
                    'weeklyHoliday' => $weeklyHoliday,
                    'businessHoliday' => $businessHoliday
                ]
            ], 200);
        } catch (Exception $ex) {
            return $this->apiResponse(['status' => '501', 'data' => $ex], 400);
        }
    }

    public function getEmployeeCalendarRoster(Request $data)
    {
        try {

            $start_date = $data->year . '-01-01';
            $end_date = $data->year . '-12-31';

            //employee offday
            $employeeRoster = SchEmployeeRoster::where('sch_employee_id', $data->sch_employee_id)
                                ->where('start_date', '<=', $end_date)
                                ->where('end_date', '>=', $start_date)
                                ->leftJoin('sch_shifts', 'sch_employee_rosters.sch_shift_id', '=', 'sch_shifts.id')
                                ->selectRaw(
                                    "sch_employee_rosters.id,
                                    sch_employee_rosters.start_date as start,
                                    sch_employee_rosters.end_date as end,
                                    sch_employee_rosters.title,
                                    sch_shifts.color_code" // Replace column_name with the actual column you want to select
                                )->get();

            $businessHoliday = CmnBusinessHoliday::where('cmn_branch_id', $data->cmn_branch_id)
            ->where('start_date', '<=', $end_date)->where('end_date', '>=', $start_date)
            ->selectRaw(
                "id,
                start_date as start,
                end_date as end,
                title"
            )->get();

            $businessHours = CmnBusinessHour::where('is_off_day', 1)->where('cmn_branch_id', $data->cmn_branch_id)->select('day')->get();
            $weeklyHoliday = array();
            while (strtotime($start_date) <= strtotime($end_date)) {
                $timestamp = strtotime($start_date);
                $day = date('w', $timestamp);
                foreach ($businessHours as $val) {
                    if ($day == $val['day']) {
                        $weeklyHoliday[] = [
                            'id' => 'wh',
                            "title" => "Weekly Holiday",
                            "start" => $start_date,
                            "end" => $start_date
                        ];
                    }
                }
                $start_date = date("Y-m-d", strtotime("+1 days", strtotime($start_date)));
            }

            return $this->apiResponse([
                'status' => '1',
                'data' =>
                [
                    'employeeRoster' => $employeeRoster,
                    'weeklyHoliday' => $weeklyHoliday,
                    'businessHoliday' => $businessHoliday
                ]
            ], 200);
        } catch (Exception $ex) {
            return $this->apiResponse(['status' => '501', 'data' => $ex], 400);
        }
    }

    public function deleteEmployeeOffday(Request $data)
    {
        try {
            if ($data->id != null) {
                $offday = SchEmployeeRoster::find($data->id);
                if ($offday) {
                    $resp = $offday->delete();
                }
                return $this->apiResponse(['status' => '1', 'data' => $resp], 200);
            }
            return $this->apiResponse(['status' => '0', 'data' => ''], 200);
        } catch (Exception $ex) {
            return $this->apiResponse(['status' => '501', 'data' => $ex], 400);
        }
    }

public function getEmployeeServices(Request $data)
{
    try {
        $empId = $data->sch_employee_id;

        // Get all service groups
        $groups = SchServiceGroup::select('id', 'name')->get();

        // Get all service categories with their respective group names
        $categories = SchServiceCategory::select('id', 'name', 'group_id')->get();

        // Iterate through each category and add a 'group_name' key
        foreach ($categories as &$cval) {
            foreach ($groups as $group) {
                if ($group->id == $cval->group_id) {
                    $cval['group_name'] = $group->name;
                    break;
                }
            }

            $cval['service'] = SchServices::where('sch_service_category_id', $cval->id)
                ->whereNotIn('id', SchEmployeeService::where('sch_employee_id', $empId)
                    ->where('sch_service_category_id', $cval->id)
                    ->select('sch_service_id'))
                ->selectRaw(
                    "id,
                    title,
                    title_slug,
                    0 as status,
                    0 as emp_service_id,
                    price as fees"
                )->union(SchEmployeeService::join('sch_services', 'sch_employee_services.sch_service_id', '=', 'sch_services.id')
                    ->where('sch_employee_id', $empId)
                    ->where('sch_services.sch_service_category_id', $cval->id)
                    ->selectRaw(
                        "sch_services.id as id,
                        sch_services.title,
                        sch_services.title_slug,
                        sch_employee_services.status,
                        sch_employee_services.id as emp_service_id,
                        sch_employee_services.fees"
                    ))->get();
        }

        return $this->apiResponse(['status' => '1', 'data' => $categories], 200);
    } catch (Exception $ex) {
        return $this->apiResponse(['status' => '501', 'data' => $ex], 400);
    }
}


    public function getEmployee()
    {
        try {
            $is_branch = SecUserBranch::where('user_id', auth()->id())->get();

            $isAdmin = User::where('is_sys_adm', 1)->exists();

            if ($is_branch->isNotEmpty()) {
                $branches = $is_branch->pluck('cmn_branch_id')->toArray();
            }


            $data = SchEmployee::join('cmn_branches', 'sch_employees.cmn_branch_id', '=', 'cmn_branches.id')->select(
                'sch_employees.id',
                'sch_employees.user_id',
                'sch_employees.image_url',
                'sch_employees.employee_id',
                'sch_employees.cmn_branch_id',
                'sch_employees.full_name',
                'sch_employees.email_address',
                'sch_employees.country_code',
                'sch_employees.contact_no',
                'sch_employees.present_address',
                'sch_employees.permanent_address',
                'sch_employees.gender',
                'sch_employees.dob',
                'sch_employees.hrm_department_id',
                'sch_employees.hrm_designation_id',
                'sch_employees.specialist',
                'sch_employees.note',
                'sch_employees.status',
                'cmn_branches.name as branch',
                'cmn_branches.name_slug as branch_slug',
                'sch_employees.salary',
                'sch_employees.commission',
                'sch_employees.pay_commission_based_on',
                'sch_employees.target_service_amount',
                'sch_employees.id_card',
                'sch_employees.passport'
            );
            
            if ($is_branch->isNotEmpty()) {
                $data = $data->whereIn('cmn_branch_id', $branches);
            }

            $data = $data->get();
            return $this->apiResponse(['status' => '1', 'data' => $data], 200);
        } catch (Exception $qx) {
            return $this->apiResponse(['status' => '403', 'data' => $qx->getMessage()], 400);
        }
    }

    public function getBranchEmployee(Request $data)
    {
        try {

            $branchId = $data->branchId;
            
            // $data = SchEmployee::where('cmn_branch_id', '=', $branchId)->where('status', '=', 1)->get();

            $query = DB::table('sch_employees')
                ->join('sch_employee_services', 'sch_employees.id', '=', 'sch_employee_services.sch_employee_id')
                ->join('cmn_branches', 'sch_employees.cmn_branch_id', '=', 'cmn_branches.id')
                ->where('sch_employees.status', 1)
                ->where('sch_employee_services.status', 1)
                ->select(
                    'sch_employees.*',
                    'cmn_branches.branch_code',
                    DB::raw('GROUP_CONCAT(sch_employee_services.sch_service_id) as service_ids')
                )
                ->groupBy(
                    'sch_employees.id', 
                    'sch_employees.full_name', 
                    'sch_employees.image_url', 
                    'sch_employees.employee_id', 
                    'sch_employees.cmn_branch_id', 
                    'sch_employees.email_address', 
                    'sch_employees.country_code', 
                    'sch_employees.contact_no',
                    'sch_employees.present_address',
                    'sch_employees.permanent_address',
                    'sch_employees.gender',
                    'sch_employees.dob',
                    'sch_employees.hrm_department_id',
                    'sch_employees.hrm_designation_id',
                    'sch_employees.user_id',
                    'sch_employees.specialist',
                    'sch_employees.note',
                    'sch_employees.salary',
                    'sch_employees.commission',
                    'sch_employees.pay_commission_based_on',
                    'sch_employees.target_service_amount',
                    'sch_employees.id_card',
                    'sch_employees.passport',
                    'sch_employees.status',
                    'sch_employees.status_changed_at',
                    'sch_employees.created_by',
                    'sch_employees.updated_by',
                    'sch_employees.created_at',
                    'sch_employees.updated_at',
                    'cmn_branches.branch_code'
                );

            // Apply branch filter only if a specific branch is selected
            if (!empty($branchId)) {
                $query->where('sch_employees.cmn_branch_id', $branchId);
            }

            $data = $query->orderBy('sch_employees.cmn_branch_id', 'asc')->get();

            $data = $data->map(function ($item) {
                $item->service_ids = $item->service_ids ? array_map('intval', explode(',', $item->service_ids)) : [];
                return $item;
            });

            return $this->apiResponse(['status' => '1', 'data' => $data], 200);
        } catch (Exception $qx) {
            return $this->apiResponse(['status' => '403', 'data' => $qx], 400);
        }
    }

    public function getEmployeeService(Request $data)
    {
        try {
            $employeeId = $data->employeeId;
            $data = SchEmployeeSchedule::select(
                'id',
                'sch_employee_id',
                'day',
                'start_time',
                'end_time',
                'break_start_time',
                'break_end_time',
                'is_off_day'
            )->where("sch_employee_id", $employeeId)->get();
            if ($data->count() < 1) {
                $data = CmnBusinessHour::selectRaw(
                    '"" as id,
                    "0" as sch_employee_id,
                    day,
                    start_time,
                    end_time,
                    is_off_day,
                    "" as break_start_time,
                    "" as break_end_time'
                )->where('cmn_branch_id', SchEmployee::where('id', $employeeId)->select('cmn_branch_id')->first()->cmn_branch_id)->get();
            }
            return $this->apiResponse(['status' => '1', 'data' => $data], 200);
        } catch (Exception $qx) {
            return $this->apiResponse(['status' => '403', 'data' => $qx], 400);
        }
    }

    public function salaries(Request $request){
        return view('employee.salaries',['employees' => SchEmployee::get()]);
    }

    public function salariesLoad(Request $request, $return = false)
    {
        try {

            $employeeId = $request->sch_employee_id;
            $employeeQuery = DB::table('sch_employees')
            ->select('sch_employees.id as sch_employee_id',
                'sch_employees.commission',
                'sch_employees.salary',
                'sch_employees.full_name',
                'sch_employees.employee_id',
                'sch_employees.target_service_amount',
                'sch_employees.pay_commission_based_on',
                DB::raw('case sch_employees.pay_commission_based_on
                    when 1 then
                    if(sum(sch_service_bookings.service_amount) >= sch_employees.target_service_amount,TRUNCATE((sch_employees.salary/100)*sch_employees.commission,2),0)
                    when 2 then
                    if(sum(sch_service_bookings.service_amount) >= sch_employees.target_service_amount,TRUNCATE((sum(sch_service_bookings.service_amount)/100)*sch_employees.commission,2),0)
                    end as commission_amount
                    '),
                DB::raw('count(sch_service_bookings.sch_employee_id) total_service'),
                DB::raw('sum(sch_service_bookings.service_amount) total_service_amount')
            )
            ->where('sch_service_bookings.date','LIKE',$request->year.'-'.$request->month.'-%')
            ->leftJoin('sch_service_bookings','sch_service_bookings.sch_employee_id','=','sch_employees.id')
            ->groupBy(
                'sch_employees.id',
                'sch_employees.commission',
                'sch_employees.salary',
                'sch_employees.full_name',
                'sch_employees.employee_id',
                'sch_employees.target_service_amount',
                'sch_employees.pay_commission_based_on'
            );

            if($employeeId)
                $employeeQuery->where('sch_employees.id',$employeeId);

            $data = $employeeQuery->get();
            if($return)
                return $data;
            return $this->apiResponse(['status' => '1', 'data' => $data], 200);
        } catch (Exception $qx) {
            return $this->apiResponse(['status' => '403', 'data' => $qx], 400);
        }
    }

    public function salariesDownload(Request $request){
        $salaryQuery = SchSalary::query();

        $salaryQuery->where('year',$request->input('year'))->where('month',$request->month);
        if($request->sch_employee_id)
            $salaryQuery->where('sch_employee_id', $request->input('sch_employee_id'));

        $data = $salaryQuery->with('employee.branch')->get()->groupBy('employee.cmn_branch_id');

        $mpdf = new \Mpdf\Mpdf([
            'mode' => 'utf-8',
            'format' => 'A4-L',
            'default_font' => 'dejavusans',
            'setAutoTopMargin' => 'stretch',
            'setAutoBottomMargin' => 'stretch',
        ]);
        $mpdf->AddPageByArray([            
            'margin-left' => 10,
            'margin-right' => 10,
            'margin-top' => 10,
            'margin-bottom' => 10,
        ]);

        $mpdf->SetTitle('Employee Salary Report');
        $mpdf->WriteHTML(view('employee.salaries_pdf', ['salaries' => $data,'company_info' => CmnCompany::first(),'input' => $request->all()]));
        $mpdf->Output('Employee Salary Report_'.now()->format('YmdHis').'.pdf','I');
    }

    public function salariesStore(Request $request){
        $request->validate([
            'data.*.sch_employee_id' => 'required|integer', 
            'data.*.addition' => 'required|numeric', 
            'data.*.deduction' => 'required|numeric',
            'data.*.is_paid' => 'required|integer|in:1,0',
            'sch_employee_id' => 'nullable|integer',
            'year' => 'required|integer',
            'month' => 'required|in:01,02,03,04,05,06,07,08,09,10,11,12',
        ]);
        try{
            DB::beginTransaction();

            $salaries = $this->salariesLoad($request,true);
            foreach($request->data as $item){
                $employee = $salaries->where('sch_employee_id',$item['sch_employee_id'])->first();
                if(!$employee)
                    continue;
                $salary = SchSalary::updateOrCreate([
                    'sch_employee_id' => $employee->sch_employee_id,
                    'year' => $request['year'],
                    'month' => $request['month'],
                ],[
                    'sch_employee_id' => $employee->sch_employee_id,
                    'year' => $request['year'],
                    'month' => $request['month'],
                    'basic_salary' => $employee->salary,
                    'total_service' => $employee->total_service,
                    'total_service_amount' => $employee->total_service_amount,
                    'commission' => $employee->commission,
                    'commission_amount' => $employee->commission_amount,
                    'pay_commission_based_on' => $employee->pay_commission_based_on,
                    'addition' => $item['addition'],
                    'deduction' => $item['deduction'],
                    'total_salary' => $item['addition'] + $employee->commission_amount + $employee->salary,
                    'netpay' => ($item['addition'] + $employee->commission_amount + $employee->salary) - $item['deduction'],
                    'is_paid' => $item['is_paid'],
                    'paid_at' => ($item['is_paid']) ? date('Y-m-d') : null,
                    'updated_by' => auth()->id(),
                ]);
            }

            DB::commit();
        }catch(\Exception $e){
            if(env('APP_DEBUG'))
                dd($e);
            DB::rollBack();
            return $this->apiResponse(['status' => '422', 'data' => $e->getMessage()], 422);
        }

        return $this->apiResponse(['status' => '1', 'data' => "Success"], 200);
    }

    public function salariesDelete(Request $request){
        $request->validate([
            'data' => 'required|array',
            'data.*' => 'required|integer',
        ]);
        try{
            DB::beginTransaction();

            SchSalary::whereIn('id',$request->data)->delete();

            DB::commit();
        }catch(\Exception $e){
            if(env('APP_DEBUG'))
                dd($e);
            DB::rollBack();
            return $this->apiResponse(['status' => '422', 'data' => $e->getMessage()], 422);
        }

        return $this->apiResponse(['status' => '1', 'data' => "Success"], 200);
    }

    public function salariesProcessed(Request $request){
        $salaryQuery = SchSalary::query();

        $salaryQuery
        ->select(
            'sch_salaries.*',
            'sch_employees.commission',
            'sch_employees.salary',
            'sch_employees.full_name',
            'sch_employees.employee_id',
            'sch_employees.target_service_amount',
            'sch_employees.pay_commission_based_on'
        )
        ->join('sch_employees','sch_salaries.sch_employee_id','=','sch_employees.id')
        ->where('year',$request->input('year'))->where('month',$request->month);
        if($request->sch_employee_id)
            $salaryQuery->where('sch_employee_id', $request->input('sch_employee_id'));
        
        try{
            return $this->apiResponse(['status' => '1', 'data' => $salaryQuery->get()], 200);
        } catch (Exception $qx) {
            return $this->apiResponse(['status' => '403', 'data' => $qx], 400);
        }
    }
    
    public function shifts()
    {
        return view('employee.shifts');
    }

    public function createShift(Request $data)
    {
        DB::beginTransaction();
        try {
            $validator = Validator::make($data->toArray(), [
                'shift_name' => ['required', 'string', 'max:250'],
            ]);

            if (!$validator->fails()) {

                $shift = SchShift::create([
                    "name" => $data->shift_name,
                    "shift_start" => $data->shift_start_time,
                    "shift_end" => $data->shift_end_time,
                    "status" => $data->status,
                    "legends" => $data->legend_name,
                    "color_code" => $data->color_code,
                ]);

                DB::commit();

                $shift = SchShift::find($shift->id);

                return $this->apiResponse(['status' => '1', 'data' => ['shift' => $shift]], 200);
            }
            return $this->apiResponse(['status' => '500', 'data' => $validator->errors()], 400);
        } catch (ErrorException $ex) {
            return  $this->apiResponse(['status' => '-501', 'data' => $ex->getMessage()], 400);
        } catch (Exception $ex) {
            DB::rollBack();
            return $this->apiResponse(['status' => '501', 'data' => $ex], 400);
        }
    }

    public function updateShift(Request $data)
    {
        DB::beginTransaction();
        try {
            $validator = Validator::make($data->toArray(), [
                'shift_name' => ['required', 'string', 'max:250'],
            ]);

            if (!$validator->fails()) {

                $readyForUpdate = [
                    "name" => $data->shift_name,
                    "shift_start" => $data->shift_start_time,
                    "shift_end" => $data->shift_end_time,
                    "status" => $data->status,
                    "legends" => $data->legend_name,
                    "color_code" => $data->color_code,
                ];

                SchShift::where('id', $data['id'])->update($readyForUpdate);

                DB::commit();

                return $this->apiResponse(['status' => '1', 'data' => ''], 200);
            }
            return $this->apiResponse(['status' => '500', 'data' => $validator->errors()], 400);
        } catch (ErrorException $ex) {
            return  $this->apiResponse(['status' => '-501', 'data' => $ex->getMessage()], 400);
        } catch (Exception $ex) {
            DB::rollBack();
            return $this->apiResponse(['status' => '501', 'data' => $ex], 400);
        }
    }

    public function deleteShift(Request $data)
    {

        DB::beginTransaction();
        
        try {
            $validator = Validator::make($data->toArray(), [
                'id' => 'required|integer'
            ]);


            if (!$validator->fails()) {

                // Get the shift ID from the request
                $shiftId = $data->input('id');

                $isInUse = SchEmployeeRoster::where('sch_shift_id', $shiftId)
                    ->join('sch_shifts', 'sch_employee_rosters.sch_shift_id', '=', 'sch_shifts.id')
                    ->where('sch_shifts.name', '=', DB::raw('sch_employee_rosters.title'))
                    ->exists();


                if ($isInUse) {
                    return $this->apiResponse(['status' => '500', 'data' => 'Cannot delete shift because it is in use in employee rosters.'], 400);
                } else {
                    // Delete the shift
                    SchEmployeeRoster::where('sch_shift_id', $shiftId)->delete();
                    SchShift::where('id', $shiftId)->delete();

                    DB::commit();
                }

                return $this->apiResponse(['status' => '1', 'data' => ''], 200);
            }
            return $this->apiResponse(['status' => '500', 'data' => $validator->errors()], 400);
        } catch (ErrorException $ex) {
            return  $this->apiResponse(['status' => '-501', 'data' => $ex->getMessage()], 400);
        } catch (Exception $ex) {
            DB::rollBack();
            return $this->apiResponse(['status' => '501', 'data' => $ex], 400);
        }
    }
    
    public function getEmployeeShifts()
    {
        try {
            $data = SchShift::all();
            return $this->apiResponse(['status' => '1', 'data' => $data], 200);
        } catch (Exception $qx) {
            return $this->apiResponse(['status' => '403', 'data' => $qx], 400);
        }
    }

    public function getEmployeeRosterType()

    {
        $data = collect([array('id'=> 1, 'name'=> 'Shifts'), array('id'=> 2, 'name'=> 'Off Day')]);

        return $this->apiResponse(['status' => '1', 'data' => $data], 200);
    }

    public function getEmployeeShiftList()

    {
        try {
            $data = SchShift::select(
                'id',
                'name'
            )->get();
            return $this->apiResponse(['status' => '1', 'data' => $data], 200);
        } catch (Exception $qx) {
            return $this->apiResponse(['status' => '403', 'data' => $qx], 400);
        }
    }

    public function splitArrayAtIndex($arr, $index) {
        $part1 = array_slice($arr, 0, $index); // Elements before index
        $part2 = array_slice($arr, $index);    // Elements from index onwards
        return $part2;
    }

}
