<?php

namespace App\Http\Controllers\API\v1;

use App\Http\Controllers\Controller;
use App\Models\Employee;
use App\Models\EmployeeBenefits;
use App\Models\EmployeeLeave;
use Carbon\Carbon;
use DateInterval;
use DatePeriod;
use DateTime;
use Illuminate\Http\Request;
use Illuminate\Http\Response;
use Illuminate\Support\Facades\Validator;
use Illuminate\Support\Facades\DB;

class ReportController extends Controller
{
    public function generateReport(Request $request)
    {
        // dd($request->all());
        // Mapping module numbers to report generation methods
        $reportGenerators = [
            '1' => 'generateLeaveReport',
            '2' => 'generateOvertimeReport',
            '3' => 'generateMasterlistReport',
            '4' => 'generateSalariesReport',
            '5' => 'generateRolesReport',
            '6' => 'generateNoScheduleReport',
            '7' => 'generateAbsentReport',
            '8' => 'generateTardinessReport',
            '9' => 'generateBirthdaysReport',
            '10' => 'generateUndertimeReport',
            '11' => 'generateDTRReport',
            '12' => 'generateTravelReport',
            '13' => 'generateCompanyBenefitsReport',
            '14' => 'generateLeaveDetailed',
            '15' => 'generateContributionReport',
            '16' => 'generateOvertimeCompanyReport'
        ];

        $module = $request->modules;
        
        if (array_key_exists($module, $reportGenerators)) {
            $method = $reportGenerators[$module];
            return $this->$method($request);
        }

        return response()->json(['message' => 'You need to select a valid module']);
    }


    public function generateLeaveReport($request)
    {
        DB::enableQueryLog();

        $request->validate([
            'columns' => 'required|array',
            'from_date' => 'required|date',
            'to_date' => 'required|date',
            'leave_id' => 'nullable|integer',
            'status' => 'nullable|string',
        ]);

        // Start the query
        $query = DB::table('employee_leaves')
            ->join('employees', 'employee_leaves.employee_id', '=', 'employees.employee_id')
            ->join('leave_types', 'employee_leaves.leave_id', '=', 'leave_types.id');

        // Apply filters
        if ($request->filled('leave_id')) {
            $query->where('employee_leaves.leave_id', $request->leave_id);
        }

        if ($request->filled('status')) {
            $query->where('employee_leaves.status', $request->status);
        }

        if ($request->filled('from_date') && $request->filled('to_date')) {
            $fromDate = Carbon::parse($request->from_date)->startOfDay();
            $toDate = Carbon::parse($request->to_date)->endOfDay();

            $query->whereBetween('employee_leaves.date_from', [$fromDate, $toDate])
                ->whereBetween('employee_leaves.date_to', [$fromDate, $toDate]);
        }

        // Set the selected columns
        $selectedColumns = $request->columns;

        // Prepare transformed columns
        $transformedColumns = [];

        // Dynamically add columns based on request
        if (in_array('status', $selectedColumns)) {
            $transformedColumns[] = DB::raw("
                CASE 
                    WHEN employee_leaves.status = 0 THEN 'Pending / For Recommendation' 
                    WHEN employee_leaves.status = 1 THEN 'For Approval' 
                    WHEN employee_leaves.status = 2 THEN 'Approved' 
                    ELSE 'Denied' 
                END as status
            ");
        }

        if (in_array('leave_id', $selectedColumns)) {
            $transformedColumns[] = 'leave_types.name as leave_id';
        }

        if (in_array('without_pay', $selectedColumns)) {
            $transformedColumns[] = DB::raw("CASE WHEN employee_leaves.without_pay = 0 THEN 'No' ELSE 'Yes' END as without_pay");
        }

        if (in_array('half_day', $selectedColumns)) {
            $transformedColumns[] = DB::raw("CASE WHEN employee_leaves.half_day = 0 THEN 'No' ELSE 'Yes' END as half_day");
        }

        if (in_array('description', $selectedColumns)) {
            $transformedColumns[] = 'employee_leaves.description as description';
        }

        if (in_array('created_at', $selectedColumns)) {
            $transformedColumns[] = 'employee_leaves.created_at as created_at';
        }

        if (in_array('recommending_id', $selectedColumns)) {
            $query->leftJoin('employees as recommending_employee', 'employee_leaves.recommending_id', '=', 'recommending_employee.employee_id');
            $transformedColumns[] = DB::raw("CONCAT(recommending_employee.firstname, ' ', recommending_employee.lastname) as recommending_id");
        }

        if (in_array('approver_id', $selectedColumns)) {
            $query->leftJoin('employees as approver_employee', 'employee_leaves.approver_id', '=', 'approver_employee.employee_id');
            $transformedColumns[] = DB::raw("CONCAT(approver_employee.firstname, ' ', approver_employee.lastname) as approver_id");
        }

        $transformedColumns[] = DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as fullname");
        $transformedColumns[] = 'employees.employee_id';

        // Remove 'description' from selectedColumns if present
        $selectedColumns = array_filter($selectedColumns, function ($column) {
            return $column !== 'description';
        });

        // Remove 'created_at' from selectedColumns if present
        $selectedColumns = array_filter($selectedColumns, function ($column) {
            return $column !== 'created_at';
        });
        
        // Merge selected columns with transformed columns
        $finalColumns = array_merge($selectedColumns, $transformedColumns);

        // Select the final columns
        $query->select($finalColumns);

        // Fetch the results
        $leaves = $query->get();

        // Return the response as JSON
        return response()->json([
            'data' => $leaves,
            'total_hours' => $query->sum('hours'),
            'total_days' => $query->sum('days'),
        ]);
    }
    
    public function generateOvertimeReport($request) {
        // dd($request->all());
        // Validate the request
        DB::enableQueryLog();
    
        $request->validate([
            'columns' => 'required|array',
            'from_date' => 'required|date',
            'to_date' => 'required|date',
            'charge_to' => 'nullable|integer',
            'status' => 'nullable|string',
        ]);
    
        // Start the query
        $query = DB::table('employee_overtimes')
            ->join('employees', 'employee_overtimes.employee_id', '=', 'employees.employee_id')
            ->leftjoin('companies', 'employee_overtimes.charge_to', '=', 'companies.id');
    
        // Apply filters
        if ($request->filled('charge_to')) {
            $query->where('employee_overtimes.charge_to', $request->charge_to);
        }
    
        if ($request->filled('status')) {
            $query->where('employee_overtimes.status', $request->status);
        }
    
        if ($request->filled('from_date') && $request->filled('to_date')) {
            $fromDate = Carbon::parse($request->from_date)->startOfDay();
            $toDate = Carbon::parse($request->to_date)->endOfDay();
    
            $query->whereBetween('employee_overtimes.date_from', [$fromDate, $toDate]);
            $query->whereBetween('employee_overtimes.date_to', [$fromDate, $toDate]);
        }
    
        // Set the selected columns
        $selectedColumns = $request->columns;
    
        // Prepare transformed columns
        $transformedColumns = [];
    
        // Conditionally include status if 'status' is in $columns
        if (in_array('status', $selectedColumns)) {
            $transformedColumns[] = DB::raw("
                CASE 
                    WHEN employee_overtimes.status = 0 THEN 'Pending / For Recommendation' 
                    WHEN employee_overtimes.status = 1 THEN 'For Approval' 
                    WHEN employee_overtimes.status = 2 THEN 'Approved' 
                    ELSE 'Denied' 
                END as status
            ");
        }
    
        // Handle transformed columns dynamically
        if (in_array('charge_to', $selectedColumns)) {
            $transformedColumns[] = 'companies.name as charge_to';
        }
    
        if (in_array('meal', $selectedColumns)) {
            $transformedColumns[] = DB::raw("CASE WHEN employee_overtimes.meal = 0 THEN 'No' ELSE 'Yes' END as meal");
        }
    
        if (in_array('transportation', $selectedColumns)) {
            $transformedColumns[] = DB::raw("CASE WHEN employee_overtimes.transportation = 0 THEN 'No' ELSE 'Yes' END as transportation");
        }
    
        if (in_array('description', $selectedColumns)) {
            $transformedColumns[] = DB::raw('employee_overtimes.description as description');
        }

        if (in_array('created_at', $selectedColumns)) {
            $transformedColumns[] = DB::raw('employee_overtimes.created_at as created_at');
        }
    
        // Conditionally join employees for recommending_id
        if (in_array('recommending_id', $selectedColumns)) {
            $query->leftJoin('employees as recommending_employee', 'employee_overtimes.recommending_id', '=', 'recommending_employee.employee_id');
            $transformedColumns[] = DB::raw("CONCAT(recommending_employee.firstname, ' ', recommending_employee.lastname) as recommending_id");
        }
    
        // Conditionally join employees for approver_id
        if (in_array('approver_id', $selectedColumns)) {
            $query->leftJoin('employees as approver_employee', 'employee_overtimes.approver_id', '=', 'approver_employee.employee_id');
            $transformedColumns[] = DB::raw("CONCAT(approver_employee.firstname, ' ', approver_employee.lastname) as approver_id");
        }
        
        $transformedColumns[] = DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as fullname");
        $transformedColumns[] = "employees.employee_id";
        
        // Remove 'description' from selectedColumns if present
        $selectedColumns = array_filter($selectedColumns, function ($column) {
            return $column !== 'description';
        });
        
        // Remove 'created_at' from selectedColumns if present
        $selectedColumns = array_filter($selectedColumns, function ($column) {
            return $column !== 'created_at';
        });
    
        // Merge selected columns with transformed columns
        $finalColumns = array_merge($selectedColumns, $transformedColumns);
    
        // Select the final columns
        $query->select($finalColumns);
    
        // Fetch the results
        $overtime = $query->get();
    
        // dd(DB::getQueryLog());
    
        // Return the response as JSON
        return response()->json([
            'data' => $overtime,
            'total_hours' => $query->sum('hours'), // This line can also be used if not grouping
        ]);
    }

    public function generateMasterlistReport($request) {
        DB::enableQueryLog();
    
        $request->validate([
            'columns' => 'required|array',
            'date_hired_from' => 'required|date',
            'date_hired_to' => 'required|date'
        ]);
    
        $query = DB::table('employees')
            ->join('personal_information', 'personal_information.employee_id', '=', 'employees.employee_id');
    
        if ($request->filled('date_hired_from') && $request->filled('date_hired_to')) {
            $fromDate = Carbon::parse($request->date_hired_from)->startOfDay();
            $toDate = Carbon::parse($request->date_hired_to)->endOfDay();
    
            $query->whereBetween('employees.date_hired', [$fromDate, $toDate]);
        }

        $personalInformationColumns = ['age', 'gender', 'religion', 'tin', 'philhealth', 'citizenship', 'sss', 'pagibig', 'civil_status', 'hair_color', 'blood_type', 'eye_color', 'height', 'weight']; 

        $selectedColumns = array_map(function($column) use ($personalInformationColumns) {
            if (in_array($column, $personalInformationColumns)) {
                return 'personal_information.' . $column;
            }
            return 'employees.' . $column;
        }, $request->columns);
    
        $transformedColumns = [];

        if (in_array('employees.company_id', $selectedColumns)) {
            $query->leftJoin('companies', 'companies.id', '=', 'employees.company_id');
            $transformedColumns[] = DB::raw("companies.name as company_id");
        }

        if (in_array('employees.position_id', $selectedColumns)) {
            $query->leftJoin('job_titles', 'job_titles.id', '=', 'employees.position_id');
            $transformedColumns[] = DB::raw("job_titles.name as position_id");
        }

        if (in_array('employees.dealer_id', $selectedColumns)) {
            $query->leftJoin('dealerships', 'dealerships.id', '=', 'employees.dealer_id');
            $transformedColumns[] = DB::raw("dealerships.name as dealer_id");
        }

        if (in_array('employees.employment_status_id', $selectedColumns)) {
            $query->leftJoin('job_classifications', 'job_classifications.id', '=', 'employees.employment_status_id');
            $transformedColumns[] = DB::raw("job_classifications.name as employment_status_id");
        }

        if (in_array('employees.group_id', $selectedColumns)) {
            $query->leftJoin('groups', 'groups.id', '=', 'employees.group_id');
            $transformedColumns[] = DB::raw("groups.name as group_id");
        }

        if (in_array('employees.division_id', $selectedColumns)) {
            $query->leftJoin('divisions', 'divisions.id', '=', 'employees.division_id');
            $transformedColumns[] = DB::raw("divisions.name as division_id");
        }

        if (in_array('employees.department_id', $selectedColumns)) {
            $query->leftJoin('departments', 'departments.id', '=', 'employees.department_id');
            $transformedColumns[] = DB::raw("departments.name as department_id");
        }

        if (in_array('employees.section_id', $selectedColumns)) {
            $query->leftJoin('sections', 'sections.id', '=', 'employees.section_id');
            $transformedColumns[] = DB::raw("sections.name as section_id");
        }

        if (in_array('employees.unit_id', $selectedColumns)) {
            $query->leftJoin('units', 'units.id', '=', 'employees.unit_id');
            $transformedColumns[] = DB::raw("units.name as unit_id");
        }
        
        $transformedColumns[] = DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as fullname");
        $transformedColumns[] = "employees.employee_id";
    
        // Merge selected columns with transformed columns
        $finalColumns = array_merge($selectedColumns, $transformedColumns);
    
        // Select the final columns
        $query->select($finalColumns);
    
        // Fetch the results
        $masterlist = $query->get();
        
        // dd($transformedColumns);
        // dd(DB::getQueryLog());
    
        // Return the response as JSON
        return response()->json([
            'data' => $masterlist,
        ]);
    }

    public function generateSalariesReport($request) {
        // Validate the request
        DB::enableQueryLog();
    
        $request->validate([
            'columns' => 'required|array',
            'salary_effectivity_date' => 'required|date',
        ]);
    
        // Start the query
        $query = DB::table('salary_and_wages')
            ->join('employees', 'salary_and_wages.employee_id', '=', 'employees.employee_id');
    
    
        if ($request->filled('salary_effectivity_date')) {
            $fromDate = Carbon::parse($request->salary_effectivity_date)->startOfDay();
    
            $query->where('salary_and_wages.salary_effectivity_date', '>=', $fromDate);
        }
    
        // Set the selected columns
        $selectedColumns = $request->columns;
    
        // Prepare transformed columns
        $transformedColumns = [];
        
        // Handle transformed columns dynamically
        if ($request->filled('payroll_rate')) {
            $query->where('salary_and_wages.payroll_rate', $request->payroll_rate);
        }
    
        if ($request->filled('salary_rate')) {
            $query->whereBetween('salary_and_wages.salary_rate', [$request->salary_rate['min'], $request->salary_rate['max']]);
        }

        if ($request->filled('payment_type')) {
            $query->where('salary_and_wages.payment_type', $request->payment_type);
        }
        
        $transformedColumns[] = DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as fullname");
        $transformedColumns[] = "employees.employee_id";

        // Remove 'description' from selectedColumns if present
        $selectedColumns = array_filter($selectedColumns, function ($column) {
            return $column !== 'description';
        });

        // Remove 'created_at' from selectedColumns if present
        $selectedColumns = array_filter($selectedColumns, function ($column) {
            return $column !== 'created_at';
        });
    
        // Merge selected columns with transformed columns
        $finalColumns = array_merge($selectedColumns, $transformedColumns);
    
        // Select the final columns
        $query->select($finalColumns);
    
        // Fetch the results
        $salaries = $query->get();
    
        // dd(DB::getQueryLog());
    
        // Return the response as JSON
        return response()->json([
            'data' => $salaries
        ]);
    }

    public function generateRolesReport($request) {
        // Validate the request
        DB::enableQueryLog();
    
        $request->validate([
            'columns' => 'required|array',
        ]);
    
        // Start the query
        $query = DB::table('employees')
            ->join('users', 'employees.employee_id', '=', 'users.employee_id')
            ->join('user_roles', 'users.id', '=', 'user_roles.user_id')
            ->join('roles', 'user_roles.role_id', '=', 'roles.id')
            ->join('role_permissions', 'roles.id', '=', 'role_permissions.role_id')
            ->join('modules', 'role_permissions.module_id', '=', 'modules.id')
            ->join('menus', 'modules.menu_id', '=', 'menus.id')
            ->where('menus.system_id', 3);
        
        // Set the selected columns
        $selectedColumns = $request->columns;

        // Prepare transformed columns
        $transformedColumns = [];
        
        if (in_array('roles_name', $selectedColumns)) {
            $transformedColumns[] = DB::raw("roles.name as roles_name");
        }

        if (in_array('menu_name', $selectedColumns)) {
            $transformedColumns[] = DB::raw("menus.name as menu_name");
        }

        if (in_array('menu_description', $selectedColumns)) {
            $transformedColumns[] = DB::raw("menus.description as menu_description");
        }
        
        $transformedColumns[] = DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as fullname");
        $transformedColumns[] = "employees.employee_id";

        // Remove 'description' from selectedColumns if present
        $selectedColumns = array_filter($selectedColumns, function ($column) {
            return $column !== 'description';
        });

        // Remove 'created_at' from selectedColumns if present
        $selectedColumns = array_filter($selectedColumns, function ($column) {
            return $column !== 'created_at';
        });
    
        // Merge selected columns with transformed columns
        // $finalColumns = array_merge($selectedColumns, $transformedColumns);
    
        // Select the final columns
        $query->select($transformedColumns);
    
        // Fetch the results
        $roles = $query->get();
    
        // dd(DB::getQueryLog());
    
        // Return the response as JSON
        return response()->json([
            'data' => $roles
        ]);
    }

    public function generateNoScheduleReport($request) {
        DB::enableQueryLog();
        
        $query = DB::table('employees')
            ->leftJoin('employee_schedules', 'employees.employee_id', '=', 'employee_schedules.employee_id')
            ->where('employee_schedules.employee_id', NULL)
            ->where('employees.enabled', 1);
        

        $transformedColumns = [];
        
        if ($request->company_id) {
            $query->leftJoin('companies', 'companies.id', '=', 'employees.company_id');
            $query->where('employees.company_id', $request->company_id);
        }

        if ($request->dealer_id) {
            $query->leftJoin('dealerships', 'dealerships.id', '=', 'employees.dealer_id');
            $query->where('employees.dealer_id', $request->dealer_id);
        }
        
        $transformedColumns[] = DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as fullname");
        $transformedColumns[] = "employees.employee_id";
    
        $query->select($transformedColumns);
        
        $noSchedule = $query->get();
        

        // Return the response as JSON
        return response()->json([
            'data' => $noSchedule
        ]);
    }

    public function generateAbsentReport($request) {
        DB::enableQueryLog();
        
        $employees = [];

        if ($request->filled('employee_id') && is_array($request->employee_id) && !empty($request->employee_id)) {
            // Fetch employees individually
            $employees = Employee::select('employee_id', 'firstname', 'lastname')
                                ->whereIn('employee_id', $request->employee_id)
                                ->get();
        } else {
            $employeeList = Employee::where('enabled', 1);

            if ($request->company_id) {
                $employeeList->where('company_id', $request->company_id);
            }

            if ($request->dealer_id) {
                $employeeList->where('dealer_id', $request->dealer_id);
            }

            // Get all employees that match the criteria
            $employees = $employeeList->select('employee_id', 'firstname', 'lastname')->get();
        }

        $employeeAttendance = new EmployeeAttendanceController();

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

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

        $data = [];

        foreach ($employees as $employee) {
            $employeeData = []; 

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

                $timelogs = $employeeAttendance->getTimelog($formattedDate, $employee->employee_id); 
                $schedule = $employeeAttendance->getSchedule($formattedDate, $employee->employee_id);
                $holiday = $employeeAttendance->getHoliday($formattedDate);
                $leave = $employeeAttendance->getLeave($formattedDate, $employee->employee_id);

                $absent = $employeeAttendance->addAbsenceData($employeeData, $timelogs, $leave, $holiday, $schedule, $formattedDate);

                if ($absent) {
                    $employeeData[] = $formattedDate;
                }
            }

            $data[] = [
                'employee_id' => $employee->employee_id,
                'firstname' => $employee->firstname,
                'lastname' => $employee->lastname,
                'absent' => $employeeData,
            ];
        }

        return response()->json([
            'data' => $data
        ]);
    }
    public function generateTardinessReport($request) {
        DB::enableQueryLog();
        
        $employees = [];

        if ($request->filled('employee_id') && is_array($request->employee_id) && !empty($request->employee_id)) {
            // Fetch employees individually
            $employees = Employee::select('employee_id', 'firstname', 'lastname')
                                ->whereIn('employee_id', $request->employee_id)
                                ->get();
        } else {
            $employeeList = Employee::where('enabled', 1);

            if ($request->company_id) {
                $employeeList->where('company_id', $request->company_id);
            }

            if ($request->dealer_id) {
                $employeeList->where('dealer_id', $request->dealer_id);
            }

            // Get all employees that match the criteria
            $employees = $employeeList->select('employee_id', 'firstname', 'lastname')->get();
        }

        $employeeAttendance = new EmployeeAttendanceController();

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

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

        $data = [];

        foreach ($employees as $employee) {
            $employeeData = []; 

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

                $timelogs = $employeeAttendance->getTimelog($formattedDate, $employee->employee_id); 
                $schedule = $employeeAttendance->getSchedule($formattedDate, $employee->employee_id);
                $tardiness = $employeeAttendance->getTardiness($timelogs, $schedule);
                

                if ($tardiness) {
                    $employeeData[] = $formattedDate.'|'.$tardiness;
                }
            }

            $data[] = [
                'employee_id' => $employee->employee_id,
                'firstname' => $employee->firstname,
                'lastname' => $employee->lastname,
                'tardiness' => $employeeData,
            ];
        }

        return response()->json([
            'data' => $data
        ]);
    }

    public function generateBirthdaysReport($request) {
        
        
        $employeeList = Employee::where('employees.enabled', 1)
                            ->join('personal_information', 'employees.employee_id', '=', 'personal_information.employee_id');

        if ($request->company_id) {
            $employeeList->where('company_id', $request->company_id);
        }

        if ($request->dealer_id) {
            $employeeList->where('dealer_id', $request->dealer_id);
        }

        $employees = $employeeList->select('employees.employee_id', 'firstname', 'lastname', 'birth_date')
                        ->whereRaw('MONTH(birth_date) IN (' . implode(',', $request->month) . ')')
                        ->get();

        return response()->json([
            'data' => $employees
        ]);
    }

    public function generateUndertimeReport($request) {
        DB::enableQueryLog();
        
        $employees = [];

        if ($request->filled('employee_id') && is_array($request->employee_id) && !empty($request->employee_id)) {
            
            $employees = Employee::select('employee_id', 'firstname', 'lastname')
                                ->whereIn('employee_id', $request->employee_id)
                                ->get();
        } else {
            $employeeList = Employee::where('enabled', 1);

            if ($request->company_id) {
                $employeeList->where('company_id', $request->company_id);
            }

            if ($request->dealer_id) {
                $employeeList->where('dealer_id', $request->dealer_id);
            }

            // Get all employees that match the criteria
            $employees = $employeeList->select('employee_id', 'firstname', 'lastname')->get();
        }

        $employeeAttendance = new EmployeeAttendanceController();

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

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

        $data = [];

        foreach ($employees as $employee) {
            $employeeData = []; 

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

                $timelogs = $employeeAttendance->getTimelog($formattedDate, $employee->employee_id); 
                $schedule = $employeeAttendance->getSchedule($formattedDate, $employee->employee_id);
                $undertime = $employeeAttendance->getUndertime($timelogs, $schedule);
                
                if ($undertime) {
                    $employeeData[] = $formattedDate.'|'.$undertime;
                }
            }

            $data[] = [
                'employee_id' => $employee->employee_id,
                'firstname' => $employee->firstname,
                'lastname' => $employee->lastname,
                'undertime' => $employeeData,
            ];
        }

        return response()->json([
            'data' => $data
        ]);
    }

    public function generateDTRReport($request) {
        $end_date = date('Y-m-d', strtotime($request->end_date . '+1 day'));

        $employees = [];

        if ($request->filled('employee_id') && is_array($request->employee_id) && !empty($request->employee_id)) {
            
            $employeeList = Employee::select('employee_id', 'firstname', 'lastname', 'company_id', 'dealer_id')->with([
                'timelog' => function ($query) use ($request, $end_date) {
                    $query->whereBetween('datetimelog', [$request->start_date, $end_date]);
                },
                'company:id,name',
                'dealership:id,name'
            ])
            ->whereIn('employee_id', $request->employee_id)
            ->where(['enabled' => 1]);

            // $employees = Employee::select('employee_id', 'firstname', 'lastname')
            //                     ->whereIn('employee_id', $request->employee_id)
            //                     ->get();
        } else {
            $employeeList = Employee::select('employee_id', 'firstname', 'lastname')->with([
                'timelog' => function ($query) use ($request, $end_date) {
                    $query->whereBetween('datetimelog', [$request->start_date, $end_date]);
                },
                'company',
                'department'
            ])->where(['enabled' => 1]);

            // $employeeList = Employee::where('enabled', 1);

            if ($request->company_id) {
                $employeeList->where('company_id', $request->company_id);
            }

            if ($request->dealer_id) {
                $employeeList->where('dealer_id', $request->dealer_id);
            }

            // Get all employees that match the criteria
            $employees = $employeeList->select('employee_id', 'firstname', 'lastname')->get();
        }

        $data = $employeeList->get();

        return response()->json([
            'data' => $data
        ]);
    }

    public function generateTravelReport($request) {
        DB::enableQueryLog();
    
        $request->validate([
            'columns' => 'required|array',
            'from_date' => 'required|date',
            'to_date' => 'required|date',
            'status' => 'nullable|string',
        ]);
    
        // Start the query
        $query = DB::table('employee_obs')
            ->join('employees', 'employee_obs.employee_id', '=', 'employees.employee_id');
        
        if ($request->company_id) {
            $query->where('employees.company_id', $request->company_id);
        }

        if ($request->dealer_id) {
            $query->where('employees.dealer_id', $request->dealer_id);
        }
    
        if ($request->filled('status')) {
            $query->where('employee_obs.status', $request->status);
        }
    
        if ($request->filled('from_date') && $request->filled('to_date')) {
            $fromDate = Carbon::parse($request->from_date)->startOfDay();
            $toDate = Carbon::parse($request->to_date)->endOfDay();
    
            $query->whereBetween('employee_obs.date_from', [$fromDate, $toDate]);
        }
        
        // Set the selected columns
        $selectedColumns = $request->columns;
    
        // Prepare transformed columns
        $transformedColumns = [];
    
        // Conditionally include status if 'status' is in $columns
        if (in_array('status', $selectedColumns)) {
            $transformedColumns[] = DB::raw("
                CASE 
                    WHEN employee_obs.status = 0 THEN 'Pending / For Recommendation' 
                    WHEN employee_obs.status = 1 THEN 'For Approval' 
                    WHEN employee_obs.status = 2 THEN 'Approved' 
                    ELSE 'Denied' 
                END as status
            ");
        }
        
        if (in_array('date_from', $selectedColumns)) {
            $transformedColumns[] = DB::raw('employee_obs.date_from as date_from');
        }

        if (in_array('date_to', $selectedColumns)) {
            $transformedColumns[] = DB::raw('employee_obs.date_to as date_to');
        }

        if (in_array('description', $selectedColumns)) {
            $transformedColumns[] = DB::raw('employee_obs.description as description');
        }

        if (in_array('created_at', $selectedColumns)) {
            $transformedColumns[] = DB::raw('employee_obs.created_at as created_at');
        }
    
        // Conditionally join employees for recommending_id
        if (in_array('recommending_id', $selectedColumns)) {
            $query->leftJoin('employees as recommending_employee', 'employee_obs.recommending_id', '=', 'recommending_employee.employee_id');
            $transformedColumns[] = DB::raw("CONCAT(recommending_employee.firstname, ' ', recommending_employee.lastname) as recommending_id");
        }
    
        // Conditionally join employees for approver_id
        if (in_array('approver_id', $selectedColumns)) {
            $query->leftJoin('employees as approver_employee', 'employee_obs.approver_id', '=', 'approver_employee.employee_id');
            $transformedColumns[] = DB::raw("CONCAT(approver_employee.firstname, ' ', approver_employee.lastname) as approver_id");
        }
        
        $transformedColumns[] = DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as fullname");
        $transformedColumns[] = "employees.employee_id";
        
        $selectedColumns = array_filter($selectedColumns, function ($column) {
            return $column !== 'date_from';
        });
        
        $selectedColumns = array_filter($selectedColumns, function ($column) {
            return $column !== 'date_to';
        });

        $selectedColumns = array_filter($selectedColumns, function ($column) {
            return $column !== 'description';
        });
        
        // Remove 'created_at' from selectedColumns if present
        $selectedColumns = array_filter($selectedColumns, function ($column) {
            return $column !== 'created_at';
        });
    
        // Merge selected columns with transformed columns
        $finalColumns = array_merge($selectedColumns, $transformedColumns);
    
        // Select the final columns
        $query->select($finalColumns);
    
        // Fetch the results
        $ob = $query->get();
    
        // dd(DB::getQueryLog());
    
        // Return the response as JSON
        return response()->json([
            'data' => $ob,
        ]);
    }

    public function generateCompanyBenefitsReport($request) 
    {
        $data = [];

        $employeeCountPerCompany = DB::table('employees as e')
            ->join('companies as c', 'e.company_id', '=', 'c.id')
            ->select('c.id', 'c.name', 'c.code', DB::raw('COUNT(e.company_id) as employee_count_per_company'))
            ->where('e.enabled', 1)
            ->groupBy('c.name', 'e.company_id')
            ->get();
        
        foreach($employeeCountPerCompany as $company){
            $data[$company->id]['company_id'] = $request->id;
            $data[$company->id]['company_name'] = $company->name;
            $data[$company->id]['company_code'] = $company->code;
            $data[$company->id]['employee_count'] = $company->employee_count_per_company;

            $employeeQuery = Employee::select('employee_id')->where('company_id', $request->id);

            $employee_ids = $employeeQuery->pluck('employee_id');

            $vacation_counts = EmployeeLeave::where('leave_id', 2)
                ->whereIn('employee_id', $employee_ids)
                ->selectRaw('
                    SUM(CASE WHEN without_pay = 0 THEN days ELSE 0 END) as total_vl_wp,
                    SUM(CASE WHEN without_pay = 1 THEN days ELSE 0 END) as total_vl_wop
                ')
                ->whereRaw('MONTH(date_from) IN (' . implode(',', $request->month) . ')')
                ->where('status', 2)
                ->first();
            
            // TOTAL FOR VACATION LEAVE 
            $data[$company->id]['total_vl_wp'] = $vacation_counts->total_vl_wp;
            $data[$company->id]['total_vl_wop'] = $vacation_counts->total_vl_wop;

            $sick_counts = EmployeeLeave::where('leave_id', 3)
                ->whereIn('employee_id', $employee_ids)
                ->selectRaw('
                    SUM(CASE WHEN without_pay = 0 THEN days ELSE 0 END) as total_sl_wp,
                    SUM(CASE WHEN without_pay = 1 THEN days ELSE 0 END) as total_sl_wop
                ')
                ->whereRaw('MONTH(date_from) IN (' . implode(',', $request->month) . ')')
                ->where('status', 2)
                ->first();
            
            // TOTAL FOR SICK LEAVE 
            $data[$company->id]['total_sl_wp'] = $sick_counts->total_sl_wp;
            $data[$company->id]['total_sl_wop'] = $sick_counts->total_sl_wop;

            // TOTAL BEREAVEMENT LEAVE
            $data[$company->id]['total_bl_wop'] = 0;

            $total_amount_of_benefits = DB::table('employees as e')
                ->join('employee_benefits as eb', 'e.employee_id', '=', 'eb.employee_id')
                ->selectRaw('
                    SUM(CASE WHEN benefits_type_id = 3 THEN eb.amount ELSE 0 END) as meal_total,
                    SUM(CASE WHEN benefits_type_id = 2 THEN eb.amount ELSE 0 END) as transpo_total,
                    SUM(CASE WHEN benefits_type_id = 5 THEN eb.amount ELSE 0 END) as transpo_subsidy_total,
                    SUM(CASE WHEN benefits_type_id = 17 THEN eb.amount ELSE 0 END) as transpo_allowance_total,
                    SUM(CASE WHEN benefits_type_id = 18 THEN eb.amount ELSE 0 END) as company_car_total,
                    SUM(CASE WHEN benefits_type_id = 19 THEN eb.amount ELSE 0 END) as gas_allowance_total,
                    SUM(CASE WHEN benefits_type_id = 20 THEN eb.amount ELSE 0 END) as parking_total,
                    SUM(CASE WHEN benefits_type_id = 21 THEN eb.amount ELSE 0 END) as company_uniform_total,
                    SUM(CASE WHEN benefits_type_id = 22 THEN eb.amount ELSE 0 END) as sm_uniform_total,
                    SUM(CASE WHEN benefits_type_id = 23 THEN eb.amount ELSE 0 END) as hmo_total,
                    SUM(CASE WHEN benefits_type_id = 24 THEN eb.amount ELSE 0 END) as first_aid_total,
                    SUM(CASE WHEN benefits_type_id = 25 THEN eb.amount ELSE 0 END) as ligpai_total,
                    SUM(CASE WHEN benefits_type_id = 26 THEN eb.amount ELSE 0 END) as calamity_assistance_total,
                    SUM(CASE WHEN benefits_type_id = 27 THEN eb.amount ELSE 0 END) as bereavement_assistance_total,
                    SUM(CASE WHEN benefits_type_id = 28 THEN eb.amount ELSE 0 END) as intereset_fee_total,
                    SUM(CASE WHEN benefits_type_id = 29 THEN eb.amount ELSE 0 END) as laptop_prog_total,
                    SUM(CASE WHEN benefits_type_id = 30 THEN eb.amount ELSE 0 END) as company_outing_total,
                    SUM(CASE WHEN benefits_type_id = 31 THEN eb.amount ELSE 0 END) as com_allowance_total,
                    SUM(CASE WHEN benefits_type_id = 32 THEN eb.amount ELSE 0 END) as stay_in_total,
                    SUM(CASE WHEN benefits_type_id = 33 THEN eb.amount ELSE 0 END) as shuttle_service_total
                ')
                ->where('e.enabled', 1)
                ->where('e.company_id', $request->id)
                ->whereMonth('eb.date_from', $request->month)
                ->first();

            foreach($total_amount_of_benefits as $key => $amount_of_benefits){
                $data[$company->id][$key] = $amount_of_benefits;
            }
        }

        return response()->json([
            'data' => array_values($data)
        ]);
        
    }

    public function generateLeaveDetailed($request) 
    {
        $data = [];

        $employeeCountPerCompany = DB::table('employees as e')
            ->join('companies as c', 'e.company_id', '=', 'c.id')
            ->select('c.id', 'c.name', 'c.code', DB::raw('COUNT(e.company_id) as employee_count_per_company'))
            ->where('e.enabled', 1)
            ->groupBy('c.name', 'e.company_id')
            ->get();
        
        foreach($employeeCountPerCompany as $company){
            $data[$company->id]['company_id'] = $request->id;
            $data[$company->id]['company_name'] = $company->name;
            $data[$company->id]['company_code'] = $company->code;
            $data[$company->id]['employee_count'] = $company->employee_count_per_company;

            $employeeQuery = Employee::select('employee_id')->where('company_id', $request->id);

            $employee_ids = $employeeQuery->pluck('employee_id');

            $vacation_counts = EmployeeLeave::where('leave_id', 2)
                ->whereIn('employee_id', $employee_ids)
                ->selectRaw('
                    SUM(CASE WHEN without_pay = 0 THEN days ELSE 0 END) as total_vl_wp,
                    SUM(CASE WHEN without_pay = 1 THEN days ELSE 0 END) as total_vl_wop
                ')
                ->whereRaw('MONTH(date_from) IN (' . implode(',', $request->month) . ')')
                ->where('status', 2)
                ->first();
            
            // TOTAL FOR VACATION LEAVE 
            $data[$company->id]['total_vl_wp'] = $vacation_counts->total_vl_wp;
            $data[$company->id]['total_vl_wop'] = $vacation_counts->total_vl_wop;

            $sick_counts = EmployeeLeave::where('leave_id', 3)
                ->whereIn('employee_id', $employee_ids)
                ->selectRaw('
                    SUM(CASE WHEN without_pay = 0 THEN days ELSE 0 END) as total_sl_wp,
                    SUM(CASE WHEN without_pay = 1 THEN days ELSE 0 END) as total_sl_wop
                ')
                ->whereRaw('MONTH(date_from) IN (' . implode(',', $request->month) . ')')
                ->where('status', 2)
                ->first();
            
            // TOTAL FOR SICK LEAVE 
            $data[$company->id]['total_sl_wp'] = $sick_counts->total_sl_wp;
            $data[$company->id]['total_sl_wop'] = $sick_counts->total_sl_wop;
            
            // TOTAL BEREAVEMENT LEAVE
            $data[$company->id]['total_bl_wop'] = 0;

            $vacation_reason_counts = EmployeeLeave::whereIn('employee_id', $employee_ids)
                ->selectRaw('
                    COUNT(CASE WHEN leave_reason_id = 1 THEN 1 ELSE NULL END) as total_personal_vl,
                    COUNT(CASE WHEN leave_reason_id = 2 THEN 1 ELSE NULL END) as total_fr_vl,
                    COUNT(CASE WHEN leave_reason_id = 3 THEN 1 ELSE NULL END) as total_rl_vl,
                    COUNT(CASE WHEN leave_reason_id = 4 THEN 1 ELSE NULL END) as total_emergency_vl,
                    COUNT(CASE WHEN leave_reason_id = 5 THEN 1 ELSE NULL END) as total_others_vl,
                    COUNT(CASE WHEN leave_reason_id = 6 THEN 1 ELSE NULL END) as total_cd_sl,
                    COUNT(CASE WHEN leave_reason_id = 7 THEN 1 ELSE NULL END) as total_msp_sl,
                    COUNT(CASE WHEN leave_reason_id = 8 THEN 1 ELSE NULL END) as total_rd_sl,
                    COUNT(CASE WHEN leave_reason_id = 9 THEN 1 ELSE NULL END) as total_eent_sl,
                    COUNT(CASE WHEN leave_reason_id = 10 THEN 1 ELSE NULL END) as total_idi_sl,
                    COUNT(CASE WHEN leave_reason_id = 11 THEN 1 ELSE NULL END) as total_hf_sl,
                    COUNT(CASE WHEN leave_reason_id = 12 THEN 1 ELSE NULL END) as total_or_sl,
                    COUNT(CASE WHEN leave_reason_id = 13 THEN 1 ELSE NULL END) as total_dr_sl,
                    COUNT(CASE WHEN leave_reason_id = 14 THEN 1 ELSE NULL END) as total_others_sl
                ')
                ->whereRaw('MONTH(date_from) IN (' . implode(',', $request->month) . ')')
                ->where('status', 2)
                ->first();

            // TOTAL FOR VACATION LEAVE REASON 
            $data[$company->id]['total_personal_vl'] = $vacation_reason_counts->total_personal_vl;
            $data[$company->id]['total_fr_vl'] = $vacation_reason_counts->total_fr_vl;
            $data[$company->id]['total_rl_vl'] = $vacation_reason_counts->total_rl_vl;
            $data[$company->id]['total_emergency_vl'] = $vacation_reason_counts->total_emergency_vl;
            $data[$company->id]['total_others_vl'] = $vacation_reason_counts->total_others_vl;

            $data[$company->id]['total_cd_sl'] = $vacation_reason_counts->total_cd_sl;
            $data[$company->id]['total_msp_sl'] = $vacation_reason_counts->total_msp_sl;
            $data[$company->id]['total_rd_sl'] = $vacation_reason_counts->total_rd_sl;
            $data[$company->id]['total_eent_sl'] = $vacation_reason_counts->total_eent_sl;
            $data[$company->id]['total_idi_sl'] = $vacation_reason_counts->total_idi_sl;
            $data[$company->id]['total_hf_sl'] = $vacation_reason_counts->total_hf_sl;
            $data[$company->id]['total_or_sl'] = $vacation_reason_counts->total_or_sl;
            $data[$company->id]['total_dr_sl'] = $vacation_reason_counts->total_dr_sl;
            $data[$company->id]['total_others_sl'] = $vacation_reason_counts->total_others_sl;


        }

        return response()->json([
            'data' => array_values($data)
        ]);
    }

    public function generateContributionReport($request) {
        // Validate the request
        DB::enableQueryLog();
    
        $request->validate([
            'columns' => 'required|array',
            'date_from' => 'required|date',
            'date_to' => 'required|date',
        ]);
    
        // Start the query
        $query = DB::table('payroll_summaries')
            ->join('employees', 'payroll_summaries.employee_id', '=', 'employees.employee_id');
    
    
        if ($request->filled('date_from')) {
            $fromDate = Carbon::parse($request->date_from)->startOfDay();
            $toDate = Carbon::parse($request->date_to)->startOfDay();
    
            $query->where('payroll_summaries.payroll_date', '>=', $fromDate)->where('payroll_summaries.payroll_date', '<=', $toDate);
        }
        
        if ($request->company_id) {
            $query->where('payroll_summaries.company_id', $request->company_id);
        }
        
        // Set the selected columns
        $selectedColumns = $request->columns;
    
        // Prepare transformed columns
        $transformedColumns = [];
        
        // Handle transformed columns dynamically
        // if ($request->filled('payroll_rate')) {
        //     $query->where('salary_and_wages.payroll_rate', $request->payroll_rate);
        // }
    
        // if ($request->filled('salary_rate')) {
        //     $query->whereBetween('salary_and_wages.salary_rate', [$request->salary_rate['min'], $request->salary_rate['max']]);
        // }

        // if ($request->filled('payment_type')) {
        //     $query->where('salary_and_wages.payment_type', $request->payment_type);
        // }
        
        $transformedColumns[] = DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as fullname");
        $transformedColumns[] = "employees.employee_id";

        // Remove 'description' from selectedColumns if present
        $selectedColumns = array_filter($selectedColumns, function ($column) {
            return $column !== 'description';
        });

        // Remove 'created_at' from selectedColumns if present
        $selectedColumns = array_filter($selectedColumns, function ($column) {
            return $column !== 'created_at';
        });
    
        // Merge selected columns with transformed columns
        $finalColumns = array_merge($selectedColumns, $transformedColumns);
    
        // Select the final columns
        $query->select($finalColumns);
    
        // Fetch the results
        $salaries = $query->get();
    
        // dd(DB::getQueryLog());
    
        // Return the response as JSON
        return response()->json([
            'data' => $salaries
        ]);
    }

    private function generateOvertimeCompanyReport($request){
        // dd($request->all());
        $departments = DB::table('departments')
        ->when($request->department_id, fn($query) => 
            $query->where('id', $request->department_id)
        )
        ->get();

        $result =[];
        foreach($departments as $department){
            DB::enableQueryLog();
            $data = \App\Models\ProcessedOvertime::
            join('payroll_summaries as ps', fn($join) => $join->on(
                'ps.employee_id','=','processed_overtimes.employee_id'
            ))
            ->join('payrolls','ps.payroll_date','=','payrolls.payroll_date')
            ->join('employees','processed_overtimes.employee_id','=','employees.employee_id')
            ->leftJoin('departments','processed_overtimes.department_id','=','departments.id')
            ->where('processed_overtimes.department_id',$department->id);
            // whereBetween('date',[$validated['from_date'], $validated['to_date']])
            if($request->payroll_date){
                $data = $data->whereIn('processed_overtimes.payroll_date',$request->payroll_date);
            }
       
            if($request->selectedCompanyId > 0 ){
                $data =  $data->where('processed_overtimes.company_id',$request->selectedCompanyId);
            }
            if($request->dealership_id > 0){
                $data = $data->where('processed_overtimes.dealership_id',$request->dealership_id);
            }
          
            $data = $data->select(
     
                DB::raw("CONCAT(employees.firstname,' ',employees.lastname) as name"),
                // 'processed_overtimes.employee_id',
                DB::raw('departments.name as departments'),
                DB::raw('SUM(ps.overtime_total) as total_ot_pay'),
                DB::raw('SUM(ps.holiday_total) as holiday_total'),
                DB::raw('SUM(ps.restday_total) as dayOff_pay'),
                DB::raw('SUM(ps.night_diff_total) as night_diff_pay'),
                DB::raw('SUM(processed_overtimes.no_of_hours) as total_over_times'),
                DB::raw('COUNT(DISTINCT processed_overtimes.date) as frequency'),
                DB::raw('SUM(processed_overtimes.meal_amount) as total_meal'),
                DB::raw('SUM(processed_overtimes.transportation) as total_transpo'),
                DB::raw(
                    '(
                        SUM(ps.holiday_total) + 
                        SUM(ps.restday_total)  +
                        SUM(ps.night_diff_total) +
                        SUM(processed_overtimes.meal_amount) +
                        SUM(processed_overtimes.transportation)
                    ) as row_total'
                )
                )
            ->groupBy(
                // 'employee_id',
                'departments.name',
                DB::raw("CONCAT(employees.firstname, ' ', employees.lastname)")
                )
            ->get();
                // dd(DB::getQueryLog());
            if($data->isNotEmpty()){
                $result[$department->name] = $data;
            }
        }
        return response()->json(['data'=> $result]);
    }
}
