<?php

namespace App\Http\Controllers\API\v1;

use App\Http\Controllers\Controller;
use App\Models\Applicant;
use App\Models\AttendancePosting;
use App\Models\AttendancePostingDtls;
use App\Models\Employee;
use App\Models\EmployeeBenefits;
use App\Models\EmployeeLeave;
use App\Models\EmployeeWfh;
use App\Models\GeneratedReport;
use App\Models\Holiday;
use App\Models\Mrf;
use App\Models\Payroll;
use App\Models\PayrollOldRecords;
use App\Models\PayrollSummary;
use App\Models\PersonnelActionForm;
use App\Models\ProcessedAbsent;
use App\Models\ProcessedDailyWorkHours;
use App\Models\ProcessedLeave;
use App\Models\ProcessedNightDifferential;
use App\Models\ProcessedOb;
use App\Models\ProcessedOvertime;
use App\Models\ProcessedSchedule;
use App\Models\ProcessedTardiness;
use App\Models\ProcessedTimelog;
use App\Models\ProcessedUndertime;
use App\Models\Report;
use App\Models\ReportModule;
use App\Models\SalaryAndWages;
use App\Models\SharedReport;
use App\Models\User;
use App\Models\UserLog;
use App\Notifications\SendGeneratedReport;
use Carbon\Carbon;
use DateInterval;
use DatePeriod;
use DateTime;
use Illuminate\Http\Request;
use Illuminate\Http\Response;
use Illuminate\Support\Arr;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\Crypt;
use Illuminate\Support\Facades\Validator;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Notification;
use Illuminate\Support\Facades\Storage;
use Throwable;

class ReportController extends Controller
{
    public function index(){
        $report = ReportModule::with('category')->where('enabled', 1)->get();

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

    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' => 'generateMrfReport',
            '17' => 'generateApplicantReport',
            '18' => 'generatePafReport',
            '19' => 'generateOvertimeCompanyReport',
            '20' => 'generateLateApprovalReport',
            'changeScheduleReport' => 'generateChangeScheduleReport',
            'cwsListReport' => 'generateCwsListReport',
            'changeRestDay' => 'generateChangeRestDay',
            'timeAdjustment' => 'generateTimeAdjustment',
            'employeeTaxApplication' => 'generateTaxApplicationReport',
            'wfhMonitoring' => 'generatewfhMonitoringReport',
        ];

        $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)
    {
        // dd($request->all());
        DB::enableQueryLog();

        $request->validate([
            'columns' => 'required|array',
            'from_date' => 'required|date',
            'to_date' => 'required|date',
            'leave_id' => 'nullable',
            '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') && $request->leave_id != 0) {
            $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");
        }

        if ($request->company_id > 0) {
            $query->where('employees.company_id', $request->company_id);
        }

        if ($request->dealer_id > 0) {
            $query->where('employees.dealer_id', $request->dealer_id);
        }

        if ($request->division_id > 0) {
            $query->where('employees.division_id', $request->division_id);
        }

        if ($request->department_id > 0) {
            $query->where('employees.department_id', $request->department_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();

        // dd(DB::getQueryLog());
        // Return the response as JSON
        return response()->json([
            'data' => $leaves,
            'total_hours' => $query->sum('hours'),
            'total_days' => $query->sum('days'),
        ]);
    }

    public function generateChangeScheduleReport($request)
    {
        DB::enableQueryLog();
        
        $request->validate([
            'start_date' => 'required|date',
            'end_date' => 'required|date',
        ]);

        $query = DB::table('employee_change_schedules')
            ->join('employees', 'employee_change_schedules.employee_id', '=', 'employees.employee_id')
            ->join('employees as recommending', 'employee_change_schedules.recommending_id', '=', 'recommending.employee_id', 'left')
            ->join('employees as approving', 'employee_change_schedules.approver_id', '=', 'approving.employee_id', 'left')
            ->select('employee_change_schedules.employee_id', 'employees.firstname', 'employees.lastname', 'employee_change_schedules.status', 'employee_change_schedules.dayoff', 'employee_change_schedules.from_time', 'employee_change_schedules.to_time',
            'recommending.firstname as recommending_firstname', 'recommending.lastname as recommending_lastname', 'approving.firstname as approving_firstname', 'approving.lastname as approving_lastname')
            ->where('employee_change_schedules.status', 2)
            ->whereNull('employee_change_schedules.deleted_at');

        $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();
        }
        
        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);
        }

        if ($request->department_id) {
            $query->leftJoin('departments', 'departments.id', '=', 'employees.department_id');
            $query->where('employees.department_id', $request->department_id);
        }

        if ($request->filled('date')) {
            $fromDate = Carbon::parse($request->start_date)->startOfDay();
            $toDate = Carbon::parse($request->end_date)->endOfDay();

            $query->whereBetween('employee_change_schedules.date', [$fromDate, $toDate]);
        }
        
        return response()->json([
            'data' => $query->get()
        ]);
    }

    public function generateCwsListReport($request)
    {
        DB::enableQueryLog();
        
        $request->validate([
            'start_date' => 'required|date',
            'end_date' => 'required|date',
        ]);

        $query = DB::table('employee_schedules')
            ->join('employees', 'employee_schedules.employee_id', '=', 'employees.employee_id')
            ->join('schedule_shifts', 'employee_schedules.schedule_id', '=', 'schedule_shifts.id')
            ->select('schedule_shifts.name', 'schedule_shifts.from_time', 'schedule_shifts.to_time', 'schedule_shifts.day_off', 'schedule_shifts.cws_from_time'
            , 'schedule_shifts.cws_to_time', 'schedule_shifts.cws_day', 'employees.firstname', 'employees.lastname', 'employees.employee_id', 'schedule_shifts.day_off'
            , 'schedule_shifts.cws_day', 'employee_schedules.effectivity_date')
            ->whereNotNull('schedule_shifts.cws_day');

        // dd(DB::getQueryLog());

        $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();
        }
        
        if ($request->filled('resign_employee')) {
            $query->whereNotNull('employees.date_resign');
        } else {
            $query->whereNull('employees.date_resign');
        }

        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);
        }

        if ($request->department_id) {
            $query->leftJoin('departments', 'departments.id', '=', 'employees.department_id');
            $query->where('employees.department_id', $request->department_id);
        }

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

            if($request->filled('date') == 1){
                $query->whereBetween('employee_schedules.date_from', [$fromDate, $toDate]);
            } else {
                $query->whereBetween('employee_schedules.effectivity_date', [$fromDate, $toDate]);
            }
        }
        
        return response()->json([
            'data' => $query->get()
        ]);
    }

    public function generateChangeRestDay($request)
    {
        DB::enableQueryLog();
        
        $request->validate([
            'start_date' => 'required|date',
            'end_date' => 'required|date',
        ]);

        $query = DB::table('employee_change_schedules')
            ->join('employees', 'employee_change_schedules.employee_id', '=', 'employees.employee_id')
            ->join('employees as recommending', 'employee_change_schedules.recommending_id', '=', 'recommending.employee_id')
            ->join('employees as approving', 'employee_change_schedules.approver_id', '=', 'approving.employee_id')
            ->select('employee_change_schedules.employee_id', 'employees.firstname', 'employees.lastname', 'employee_change_schedules.status', 'employee_change_schedules.dayoff', 'employee_change_schedules.from_time', 'employee_change_schedules.to_time',
            'recommending.firstname as recommending_firstname', 'recommending.lastname as recommending_lastname', 'approving.firstname as approving_firstname', 'approving.lastname as approving_lastname', 'employee_change_schedules.date')
            ->where('employee_change_schedules.dayoff', 1)
            ->where('employee_change_schedules.status', 2);

        $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();
        }
        
        if ($request->filled('resign_employee')) {
            $query->whereNotNull('employees.date_resign');
        } else {
            $query->whereNull('employees.date_resign');
        }

        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);
        }

        if ($request->department_id) {
            $query->leftJoin('departments', 'departments.id', '=', 'employees.department_id');
            $query->where('employees.department_id', $request->department_id);
        }

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

            $query->whereBetween('employee_change_schedules.date', [$fromDate, $toDate]);
        }
        
        return response()->json([
            'data' => $query->get()
        ]);
    }
    
    public function generateTimeAdjustment($request)
    {
        DB::enableQueryLog();
        
        $request->validate([
            'start_date' => 'required|date',
            'end_date' => 'required|date',
        ]);

        $query = DB::table('employee_time_adjustments')
            ->join('employees', 'employee_time_adjustments.employee_id', '=', 'employees.employee_id')
            ->join('employees as recommending', 'employee_time_adjustments.recommending_id', '=', 'recommending.employee_id')
            ->join('employees as approving', 'employee_time_adjustments.approver_id', '=', 'approving.employee_id')
            ->select('employee_time_adjustments.employee_id', 'employees.firstname', 'employees.lastname', 'employee_time_adjustments.status', 'employee_time_adjustments.in_out', 'employee_time_adjustments.time',
            'recommending.firstname as recommending_firstname', 'recommending.lastname as recommending_lastname', 'approving.firstname as approving_firstname', 'approving.lastname as approving_lastname', 'employee_time_adjustments.date');

        $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();
        }

        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);
        }

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

            $query->whereBetween('employee_time_adjustments.date', [$fromDate, $toDate]);
        }
        
        return response()->json([
            'data' => $query->get()
        ]);
    }

    public function generateOvertimeReport($request) {
        
        // Validate the request
        DB::enableQueryLog();
    
        $request->validate([
            'columns' => 'required|array',
            'from_date' => 'required|date',
            'to_date' => 'required|date',
            'charge_to_id' => 'nullable', // Renamed for clarity
            '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_id')) {
            $query->where('employee_overtimes.charge_to', $request->charge_to_id);
        }

        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;

        // Build the select array properly
        $selectColumns = [];

        // Always include basic employee info
        $selectColumns[] = DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as fullname");
        $selectColumns[] = 'employees.employee_id';

        if ($request->company_id > 0) {
            $query->where('employees.company_id', $request->company_id);
        }

        if ($request->dealer_id > 0) {
            $query->where('employees.dealer_id', $request->dealer_id);
        }

        if ($request->division_id > 0) {
            $query->where('employees.division_id', $request->division_id);
        }

        if ($request->department_id > 0) {
            $query->where('employees.department_id', $request->department_id);
        }

        // Track which joins we've already added to avoid duplicates
        $joinedTables = [];

        // Handle column transformations
        foreach ($selectedColumns as $column) {
            switch ($column) {
                case 'status':
                    $selectColumns[] = 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
                    ");
                    break;
                    
                case 'charge_to':
                case 'company_name':
                    $selectColumns[] = 'companies.name as company_name';
                    $selectColumns[] = 'companies.id as company_id';
                    break;
                    
                case 'meal':
                    $selectColumns[] = DB::raw("CASE WHEN employee_overtimes.meal = 0 THEN 'No' ELSE 'Yes' END as meal");
                    break;
                    
                case 'transportation':
                    $selectColumns[] = DB::raw("CASE WHEN employee_overtimes.transportation = 0 THEN 'No' ELSE 'Yes' END as transportation");
                    break;
                    
                case 'description':
                    $selectColumns[] = 'employee_overtimes.description as description';
                    break;
                    
                case 'created_at':
                    $selectColumns[] = 'employee_overtimes.created_at as created_at';
                    break;
                    
                case 'recommending_id':
                case 'recommender':
                    if (!in_array('recommending_employee', $joinedTables)) {
                        $query->leftJoin('employees as recommending_employee', 'employee_overtimes.recommending_id', '=', 'recommending_employee.employee_id');
                        $joinedTables[] = 'recommending_employee';
                    }
                    $selectColumns[] = DB::raw("CONCAT(recommending_employee.firstname, ' ', recommending_employee.lastname) as recommender_name");
                    $selectColumns[] = 'employee_overtimes.recommending_id as recommender_id';
                    break;
                    
                case 'approver_id':
                case 'approver':
                    if (!in_array('approver_employee', $joinedTables)) {
                        $query->leftJoin('employees as approver_employee', 'employee_overtimes.approver_id', '=', 'approver_employee.employee_id');
                        $joinedTables[] = 'approver_employee';
                    }
                    $selectColumns[] = DB::raw("CONCAT(approver_employee.firstname, ' ', approver_employee.lastname) as approver_name");
                    $selectColumns[] = 'employee_overtimes.approver_id as approver_id';
                    break;
                    
                case 'date_from':
                    $selectColumns[] = 'employee_overtimes.date_from';
                    break;
                    
                case 'date_to':
                    $selectColumns[] = 'employee_overtimes.date_to';
                    break;
                    
                case 'hours':
                    $selectColumns[] = 'employee_overtimes.hours';
                    break;
                    
                default:
                    // For any other columns, include them as-is from employee_overtimes table
                    $selectColumns[] = "employee_overtimes.{$column}";
                    break;
            }
        }

        // Apply the select - Laravel will handle the DB::raw expressions properly
        $query->select($selectColumns);

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

        // Calculate total hours separately to avoid grouping issues
        $totalHoursQuery = DB::table('employee_overtimes')
            ->join('employees', 'employee_overtimes.employee_id', '=', 'employees.employee_id')
            ->leftjoin('companies', 'employee_overtimes.charge_to', '=', 'companies.id');

        // Apply the same filters for total calculation
        if ($request->filled('charge_to_id')) {
            $totalHoursQuery->where('employee_overtimes.charge_to', $request->charge_to_id);
        }

        if ($request->filled('status')) {
            $totalHoursQuery->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();
            
            $totalHoursQuery->whereBetween('employee_overtimes.date_from', [$fromDate, $toDate]);
            $totalHoursQuery->whereBetween('employee_overtimes.date_to', [$fromDate, $toDate]);
        }

        $totalHoursSum = $totalHoursQuery->sum('employee_overtimes.hours');

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

    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', 'left')
            ->whereNull('employees.date_resign');

        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 ($request->company_id) {
                $query->where('employees.company_id', $request->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 ($request->dealer_id) {
                $query->where('employees.companydealer_id_id', $request->dealer_id);
            }
        }

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

        if (in_array('employees.employment_status_id', $selectedColumns)) {
            $query->leftJoin('employment_types', 'employment_types.id', '=', 'employees.employment_status_id');
            $transformedColumns[] = DB::raw("employment_types.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.office_id', $selectedColumns)) {
            $query->leftJoin('offices', 'offices.id', '=', 'employees.office_id');
            $transformedColumns[] = DB::raw("offices.name as office_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();

        // 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 $request)
    {
        // Enable query logging for debugging
        DB::enableQueryLog();

        // Validate the request
        $request->validate([
            'columns' => 'required|array',
        ]);

        // Start building 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('permissions', 'role_permissions.permission_id', '=', 'permissions.id')
            ->join('modules', 'role_permissions.module_id', '=', 'modules.id')
            ->join('menus', 'modules.menu_id', '=', 'menus.id')
            ->where('modules.system_id', 3)
            ->where('menus.enabled', 1)
            ->where('users.employee_id', '!=', 'sadmin')
            ->groupBy('roles.id', 'roles.name', 'modules.id', 'modules.name', 'menus.system_id', 'employees.firstname', 'employees.lastname', '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);
        }

        // Selected columns from the request
        $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("modules.description as menu_description");
        }

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

        // Add the GROUP_CONCAT for permissions
        $transformedColumns[] = DB::raw("GROUP_CONCAT(DISTINCT permissions.name ORDER BY permissions.id SEPARATOR ', ') as menu_permission");

        // Filter out unwanted columns
        $excludedColumns = ['description', 'created_at'];
        $selectedColumns = array_diff($selectedColumns, $excludedColumns);

        // Add transformed columns to the query
        $query->select(array_merge($transformedColumns));

        $query->orderBy('employees.lastname', 'ASC');
        $query->orderBy('employees.employee_id', 'ASC');

        // Execute the query and get results
        $roles = $query->get();

        // Optional: Uncomment this to view the executed SQL query
        // dd(DB::getQueryLog());

        // Return the results 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) {
        set_time_limit(300); // Prevents timeout
        
        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)
                                ->whereNull('date_resign')
                                ->get();
        } else {
            $employeeList = Employee::where('enabled', 1)
            ->whereNull('date_resign');

            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->time_keeping_id); 
                $schedule = $employeeAttendance->getSchedule($formattedDate, $employee->employee_id);
                $holiday = $employeeAttendance->getHoliday($formattedDate, $employee, $schedule);
                $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 generatewfhMonitoringReport($request) {
        set_time_limit(300); // Prevents timeout

        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)
                                ->whereNull('date_resign')
                                ->get();
        } else {
            DB::enableQueryLog();
            $employeeList = Employee::where('enabled', 1)
            ->whereNull('date_resign');

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

            if ($request->dealer_id && $request->dealer_id != "null") {
                $employeeList->where('dealer_id', $request->dealer_id);
            }

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

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

            // dd($request->all());
            // dd(DB::getQueryLog());
        }

        $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) {
                
                // dd($period);
                $formattedDate = $date->format('Y-m-d');

                $employee_id = $request->employee_id;
                $isWFH = 0;
                $schedule = $employeeAttendance->getSchedule($formattedDate, $employee->employee_id);

                if($schedule) {
                    $daysWFHArray = explode(',', $schedule->schedule->wfh);
                    $dayOfWeek = date('l', strtotime($formattedDate));
                    $isWFH = in_array($dayOfWeek, $daysWFHArray) ? 1 : 0;
                }

                // echo $employee->employee_id;
                // GET WFH
                $wfhRecord = EmployeeWfh::where([
                    'employee_id'=> $employee->employee_id,
                    'date' => $formattedDate,
                    ])->first();

                if($wfhRecord) {
                    $employeeData['wfhDate'][] = $formattedDate;

                    $userLog = UserLog::where('employee_id', $employee->employee_id)
                        ->whereDate('created_at', $formattedDate)
                        ->get();

                    $employeeData['log_dates'][] = $userLog;
                }
            }

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

        // dd($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);
                $leave = $employeeAttendance->getLeave($formattedDate, $employee->employee_id);
                $halfDayLeave = $leave && $leave->half_day == 1;
                $wholeDayLeave = $leave && $leave->hours >= 8;
                $ob = $employeeAttendance->getOB($formattedDate, $employee->employee_id);
                $offsetAvailment = $employeeAttendance->getOffsetAvailment($formattedDate, $employee->employee_id);
                $tardiness = $employeeAttendance->getTardiness($timelogs, $schedule, $halfDayLeave, $ob, $offsetAvailment, $employee->classification_id, $wholeDayLeave);
                

                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 generateThirteenthMonthPay(Request $request) {
        $year = $request->year;
        $companyId = $request->company_id;
        $dealerId = $request->dealer_id;

        // Define the 13th month pay period: Dec (previous year) to Nov (selected year)
        $startPeriod = \Carbon\Carbon::create($year - 1, 12, 1)->startOfMonth();
        $endPeriod = \Carbon\Carbon::create($year, 11, 30)->endOfMonth();

        // Get all payroll records within the 13th month pay period
        $query = PayrollOldRecords::with(['employee', 'company', 'dealership'])
            ->where(function($q) use ($startPeriod, $endPeriod) {
                $q->whereBetween('start_date', [$startPeriod, $endPeriod])
                ->orWhereBetween('end_date', [$startPeriod, $endPeriod])
                ->orWhere(function($q2) use ($startPeriod, $endPeriod) {
                    // Include records that span across the period
                    $q2->where('start_date', '<=', $startPeriod)
                        ->where('end_date', '>=', $endPeriod);
                });
            });

        if ($companyId) {
            $query->where('company_id', $companyId);
        }

        if ($dealerId) {
            $query->where('dealer_id', $dealerId);
        }

        $payrollRecords = $query->get();

        // Group by employee
        $employeePayrolls = $payrollRecords->groupBy('employee_id');

        $reportData = [];

        foreach ($employeePayrolls as $employeeId => $records) {
            $firstRecord = $records->first();
            
            // Calculate totals
            $totalBasicPay = 0;
            $totalTardiness = 0;
            $totalAbsent = 0;
            $uniqueMonths = [];

            foreach ($records as $record) {
                $recordStart = \Carbon\Carbon::parse($record->start_date);
                $recordEnd = \Carbon\Carbon::parse($record->end_date);
                
                // Only include records within the 13th month pay period
                if ($recordStart->between($startPeriod, $endPeriod) || 
                    $recordEnd->between($startPeriod, $endPeriod)) {
                    
                    // Sum up basic pay
                    $totalBasicPay += floatval($record->basic_pay);
                    
                    // Sum up deductions (tardiness and absent)
                    $totalTardiness += floatval($record->tardiness ?? 0);
                    $totalAbsent += floatval($record->absent ?? 0);
                    
                    // Track unique months for months rendered calculation
                    // Count months from Dec (prev year) to Nov (selected year)
                    $monthKey = $recordStart->format('Y-m');
                    
                    // Only count if within the 13th month period
                    if ($recordStart->between($startPeriod, $endPeriod) && 
                        !in_array($monthKey, $uniqueMonths)) {
                        $uniqueMonths[] = $monthKey;
                    }
                }
            }

            // Calculate months rendered (number of unique months worked in the period)
            $monthsRendered = count($uniqueMonths);
            
            // Total deductions (tardiness + absent)
            $totalDeductions = $totalTardiness + $totalAbsent;
            
            // Subtract deductions from total basic pay
            $adjustedBasicPay = $totalBasicPay - $totalDeductions;
            
            // 13th month pay formula: Adjusted Basic Pay / 12
            $thirteenthMonthPay = $adjustedBasicPay / 12;

            $reportData[] = [
                'employee_id' => $employeeId,
                'name' => $firstRecord->name,
                'dealer_id' => $firstRecord->dealer_id,
                'company_name' => $firstRecord->company->name ?? 'N/A',
                'dealership_name' => $firstRecord->dealership->name ?? 'N/A',
                'period' => 'Dec ' . ($year - 1) . ' - Nov ' . $year,
                'months_rendered' => $monthsRendered,
                'total_basic_pay' => number_format($totalBasicPay, 2, '.', ''),
                'total_deductions' => number_format($totalDeductions, 2, '.', ''),
                'thirteenth_month_pay' => number_format($thirteenthMonthPay, 2, '.', ''),
                'breakdown' => [
                    'tardiness' => number_format($totalTardiness, 2, '.', ''),
                    'absent' => number_format($totalAbsent, 2, '.', ''),
                ]
            ];
        }

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

    public function generateAlphalist1604C(Request $request) {
        $year = $request->params['year'];
        $companyId = $request->params['company_id'];
        $dealerId = $request->params['dealer_id'];

        // Get all payroll records for the specified year
        $query = PayrollOldRecords::with(['employee', 'company', 'dealership', 'employee.employment_type'])
            ->whereYear('end_date', $year)
            ->orWhereYear('start_date', $year);

        if ($companyId) {
            $query->where('company_id', $companyId);
        }

        if ($dealerId) {
            $query->where('dealer_id', $dealerId);
        }

        $payrollRecords = $query->get();

        // Group by employee
        $employeePayrolls = $payrollRecords->groupBy('employee_id');

        $reportData = [];
        $seqNo = 1;
        
        foreach ($employeePayrolls as $employeeId => $records) {
            $firstRecord = $records->first();
            $employee = $firstRecord['employee'];
            $employment_type = $firstRecord['employee']['employment_type'] ?? '';

            // Initialize totals
            $totalBasicPay = 0;
            $totalTardiness = 0;
            $totalAbsent = 0;
            $totalWithholdingTax = 0;
            $totalSSS = 0;
            $totalPhilHealth = 0;
            $totalPagibig = 0;
            $totalOtherDeductions = 0;
            $totalBenefits = 0;
            $uniqueMonths = [];
            
            // Track employment period
            $employmentStart = null;
            $employmentEnd = null;

            foreach ($records as $record) {
                // Sum up compensation
                $totalBasicPay += floatval($record->basic_pay);
                
                // Sum up deductions
                $totalTardiness += floatval($record->tardiness ?? 0);
                $totalAbsent += floatval($record->absent ?? 0);
                
                // Sum up withholding tax (Column 15b - Present Employer Tax Withheld)
                $totalWithholdingTax += floatval($record->w_tax ?? 0);
                
                // Sum up government contributions (Column 7d - SSS, GSIS, PHIC, HDMF)
                $totalSSS += floatval($record->sss ?? 0);
                $totalPhilHealth += floatval($record->p_health ?? 0);
                $totalPagibig += floatval($record->p_ibig ?? 0);
                
                // Sum up benefits (if available in your database)
                $totalBenefits += floatval($record->benefit ?? 0);
                
                // Other deductions
                $totalOtherDeductions += floatval($record->deduc ?? 0);
                
                // Track employment dates

                $startDate = \Carbon\Carbon::parse($employee->date_hired ?? now());
                $endDate = $employee && $employee->date_resign
                    ? \Carbon\Carbon::parse($employee->date_resign)
                    : null;

                if ($employmentStart === null || $startDate->lt($employmentStart)) {
                    $employmentStart = $startDate;
                }

                if ($employmentEnd === null) {
                    $employmentEnd = $endDate ?? false;
                }
                
                // Track unique months for calculation
                if ($startDate->year == $year) {
                    $monthKey = $startDate->format('Y-m');
                    if (!in_array($monthKey, $uniqueMonths)) {
                        $uniqueMonths[] = $monthKey;
                    }
                }
            }

            // Calculate months rendered
            $monthsRendered = count($uniqueMonths);
            
            // Total attendance deductions
            $totalAttendanceDeductions = $totalTardiness + $totalAbsent;
            
            // Column 7a - Gross Compensation Income (Present Employer)
            $grossCompensationIncome = $totalBasicPay;
            
            // Column 7d - SSS, GSIS, PHIC, HDMF Contributions (employee share only)
            $governmentContributions = $totalSSS + $totalPhilHealth + $totalPagibig;
            
            // Column 7g - Basic Salary (net of contributions)
            $basicSalaryNet = $totalBasicPay - $governmentContributions - $totalAttendanceDeductions;
            
            // Calculate 13th month pay: Total Basic Pay / 12
            $adjustedBasicPay = $totalBasicPay - ($totalTardiness + $totalAbsent);
            $thirteenthMonthPayTotal = $adjustedBasicPay > 0 ? $adjustedBasicPay / 12 : 0;
            
            // Column 7b - Non-Taxable 13th Month Pay & Other Benefits (up to 90,000)
            $nonTaxable13thMonth = min($thirteenthMonthPayTotal + $totalBenefits, 90000);
            
            // Column 7h - Taxable 13th Month Pay & Other Benefits (excess over 90,000)
            $taxable13thMonth = max(($thirteenthMonthPayTotal + $totalBenefits) - 90000, 0);
            
            // Column 7c - De Minimis Benefits (assumed 0 if not tracked separately)
            $deMinimis = 0;
            
            // Column 7e - Salaries (250,000 and below) - for minimum wage earners
            $salariesExempt = 0; // Set this based on your business logic
            
            // Column 7f - Total Non-Taxable/Exempt Compensation (Present Employer)
            $totalNonTaxable = $nonTaxable13thMonth + $deMinimis + $governmentContributions + $salariesExempt;
            
            // Column 7i - Salaries and Other Forms of Compensation (Taxable)
            $salariesTaxable = $basicSalaryNet;
            
            // Column 7j - Total Taxable Compensation Income (Present Employer)
            $totalTaxableCompensation = $basicSalaryNet + $taxable13thMonth;
            
            // Column 13 - Total Taxable Compensation Income (Present & Previous Employer)
            // Assuming no previous employer for now
            $totalTaxableAllEmployers = $totalTaxableCompensation;
            
            // Column 14 - Tax Due (January to December)
            // This should be calculated based on tax table, for now use total withholding
            $taxDue = $totalWithholdingTax;
            
            // Column 15a - Tax Withheld (Previous Employer)
            $taxWithheldPrevious = 0;
            
            // Column 15b - Tax Withheld (Present Employer)
            $taxWithheldPresent = $totalWithholdingTax;
            
            // Column 16 - 5% Tax Credit (PERA Act of 2008)
            $taxCredit = 0;
            
            // Column 17a - Amount Withheld and Paid for in December
            $amountWithheldDecember = $taxDue - ($taxWithheldPrevious + $taxWithheldPresent) - $taxCredit;
            
            // Column 17b - Over Withheld Tax Refunded to Employee
            $overWithheldRefund = ($taxWithheldPrevious + $taxWithheldPresent + $taxCredit) - $taxDue;
            
            // Column 18 - Amount of Tax Withheld as Adjusted
            $taxWithheldAdjusted = $taxWithheldPrevious + $taxWithheldPresent + $taxCredit + $amountWithheldDecember;
            if ($overWithheldRefund > 0) {
                $taxWithheldAdjusted = $taxWithheldPrevious + $taxWithheldPresent + $taxCredit - $overWithheldRefund;
            }
            
            // Column 19 - Substituted Filing
            $substitutedFiling = 'No'; // Default, set based on your criteria

            // Format employee name
            $lastName = $employee->lastname ?? '';
            $firstName = $employee->firstname ?? '';
            $middleName = $employee->middlename ?? '';

            $reportData[] = [
                // Column 1 - Sequence Number
                'seq_no' => $seqNo++,
                'fullname' => $record->name,
                
                // Column 2 - Name of Employees
                'last_name' => $lastName,
                'first_name' => $firstName,
                'middle_name' => $middleName,
                'name' => trim($firstName . ' ' . $middleName . ' ' . $lastName),
                
                // Column 3 - Nationality/Resident (for foreigners only)
                'nationality' => $employee->nationality ?? '',
                
                // Column 4 - Current Employment Status
                'employment_status' => $employment_type['name'] ?? 'N/A', // R=Regular, C=Casual, etc.
                
                // Column 5 - Period of Employment
                'employment_from' => $employmentStart ? $employmentStart->format('m/d') : '',
                'employment_to' => $employmentEnd
                    ? \Carbon\Carbon::parse($employmentEnd)->format('m/d')
                    : '',
                
                // Column 6 - Reason of Separation
                'separation_reason' => '', // T=Terminated, TR=Transferred, R=Retirement, D=Death
                
                // PRESENT EMPLOYER
                // Column 7a
                'gross_compensation_income' => number_format($grossCompensationIncome, 2, '.', ''),
                
                // NON-TAXABLE/EXEMPT
                // Column 7b
                'non_taxable_13th_month' => number_format($nonTaxable13thMonth, 2, '.', ''),
                // Column 7c
                'de_minimis_benefits' => number_format($deMinimis, 2, '.', ''),
                // Column 7d
                'government_contributions' => number_format($governmentContributions, 2, '.', ''),
                // Column 7e
                'salaries_exempt' => number_format($salariesExempt, 2, '.', ''),
                // Column 7f
                'total_non_taxable' => number_format($totalNonTaxable, 2, '.', ''),
                
                // TAXABLE
                // Column 7g
                'basic_salary_net' => number_format($basicSalaryNet, 2, '.', ''),
                // Column 7h
                'taxable_13th_month' => number_format($taxable13thMonth, 2, '.', ''),
                // Column 7i
                'salaries_taxable' => number_format($salariesTaxable, 2, '.', ''),
                // Column 7j
                'total_taxable_compensation_present' => number_format($totalTaxableCompensation, 2, '.', ''),
                
                // PREVIOUS EMPLOYER (Columns 8-12 - all zeros if no previous employer)
                'previous_employer_tin' => '',
                'previous_employment_status' => '',
                'previous_employment_from' => '',
                'previous_employment_to' => '',
                'previous_separation_reason' => '',
                'previous_gross_compensation' => '0.00',
                'previous_non_taxable_13th' => '0.00',
                'previous_de_minimis' => '0.00',
                'previous_govt_contributions' => '0.00',
                'previous_salaries_exempt' => '0.00',
                'previous_total_non_taxable' => '0.00',
                'previous_basic_salary_net' => '0.00',
                'previous_taxable_13th' => '0.00',
                'previous_salaries_taxable' => '0.00',
                'previous_total_taxable' => '0.00',
                
                // Column 13
                'total_taxable_all_employers' => number_format($totalTaxableAllEmployers, 2, '.', ''),
                
                // Column 14
                'tax_due' => number_format($taxDue, 2, '.', ''),
                
                // Column 15a
                'tax_withheld_previous' => number_format($taxWithheldPrevious, 2, '.', ''),
                
                // Column 15b
                'tax_withheld_present' => number_format($taxWithheldPresent, 2, '.', ''),
                
                // Column 16
                'tax_credit' => number_format($taxCredit, 2, '.', ''),
                
                // Column 17a
                'amount_withheld_december' => number_format(max($amountWithheldDecember, 0), 2, '.', ''),
                
                // Column 17b
                'over_withheld_refund' => number_format(max($overWithheldRefund, 0), 2, '.', ''),
                
                // Column 18
                'tax_withheld_adjusted' => number_format($taxWithheldAdjusted, 2, '.', ''),
                
                // Column 19
                'substituted_filing' => $substitutedFiling,
                
                // Additional Info (not in BIR format but useful)
                'employee_id' => $employeeId,
                'employee_tin' => $employee->tin ?? '',
                'dealer_id' => $firstRecord->dealer_id,
                'company_id' => $firstRecord->company_id,
                'company_name' => $firstRecord->company->name ?? 'N/A',
                'dealership_name' => $firstRecord->dealership->name ?? 'N/A',
                'months_rendered' => $monthsRendered,
                
                // Breakdown for debugging/reference
                'breakdown' => [
                    'total_basic_pay' => number_format($totalBasicPay, 2, '.', ''),
                    'tardiness' => number_format($totalTardiness, 2, '.', ''),
                    'absent' => number_format($totalAbsent, 2, '.', ''),
                    'sss' => number_format($totalSSS, 2, '.', ''),
                    'philhealth' => number_format($totalPhilHealth, 2, '.', ''),
                    'pagibig' => number_format($totalPagibig, 2, '.', ''),
                    'benefits' => number_format($totalBenefits, 2, '.', ''),
                    'withholding_tax' => number_format($totalWithholdingTax, 2, '.', ''),
                    'thirteenth_month_total' => number_format($thirteenthMonthPayTotal, 2, '.', ''),
                ]
            ];
        }

        // Sort alphabetically by last name, then first name (required for BIR Alphalist)
        usort($reportData, function($a, $b) {
            $lastNameCompare = strcmp($a['last_name'], $b['last_name']);
            if ($lastNameCompare !== 0) {
                return $lastNameCompare;
            }
            return strcmp($a['first_name'], $b['first_name']);
        });

        // Re-sequence after sorting
        foreach ($reportData as $index => &$data) {
            $data['seq_no'] = $index + 1;
        }

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

    public function generateAgeDistributionByGender(Request $request) {
        
        $endYear = now()->year;
        $startYear = $endYear - 6;
        $years = range($startYear, $endYear);

        $data = collect();

        $totalHiredPerYear = [];

        foreach ($years as $year) {
            // Total hired by this year (including those who have resigned)
            $totalHired = DB::table('employees')
                ->whereYear('date_hired', '<=', $year)
                ->count();

            $totalHiredPerYear[$year] = $totalHired;

            // Query for active employees by gender
            $query = DB::table('personal_information')
                ->join('employees', 'personal_information.employee_id', '=', 'employees.employee_id')
                ->select(
                    DB::raw("'$year' as year"),
                    'personal_information.gender',
                    DB::raw("ROUND(AVG(TIMESTAMPDIFF(YEAR, personal_information.birth_date, '$year-12-31'))) as avg_age"),
                    DB::raw('COUNT(*) as total')
                )
                ->whereNotNull('personal_information.birth_date')
                ->whereNotNull('personal_information.gender')
                ->whereNull('employees.date_resign')
                ->whereYear('employees.date_hired', '<=', $year)
                ->groupBy('personal_information.gender')
                ->get();

            $data = $data->merge($query);
        }

        $grouped = $data->groupBy('year')->map(function ($items, $year) use ($totalHiredPerYear) {
            $male = $items->firstWhere('gender', 'M');
            $female = $items->firstWhere('gender', 'F');

            $maleTotal = $male?->total ?? 0;
            $femaleTotal = $female?->total ?? 0;
            $totalActive = $maleTotal + $femaleTotal;

            $genderRatioMale = $totalActive > 0 ? round(($maleTotal / $totalActive) * 100, 2) : 0;
            $genderRatioFemale = $totalActive > 0 ? round(($femaleTotal / $totalActive) * 100, 2) : 0;

            $retentionRate = ($totalHiredPerYear[$year] ?? 0) > 0
                ? round(($totalActive / $totalHiredPerYear[$year]) * 100, 2)
                : 0;

            return [
                'year' => $year,
                'male_avg_age' => $male?->avg_age ?? 0,
                'female_avg_age' => $female?->avg_age ?? 0,
                'male_total' => $maleTotal,
                'female_total' => $femaleTotal,
                'gender_ratio_male_percent' => $genderRatioMale,
                'gender_ratio_female_percent' => $genderRatioFemale,
                'retention_rate_percent' => $retentionRate,
            ];
        })->values();

        // Compute summary
        $averageMaleAge = $grouped->avg('male_avg_age');
        $averageFemaleAge = $grouped->avg('female_avg_age');

        $summary = [
            'average_gender_ratio' => [
                'male_percent' => round($grouped->avg('gender_ratio_male_percent'), 2),
                'female_percent' => round($grouped->avg('gender_ratio_female_percent'), 2),
            ],
            'average_retention_rate_percent' => round($grouped->avg('retention_rate_percent'), 2),
            'average_age' => [
                'male_avg_age' => round($averageMaleAge, 2),
                'female_avg_age' => round($averageFemaleAge, 2),
                'overall_avg_age' => round(($averageMaleAge + $averageFemaleAge) / 2, 2),
            ],
        ];

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

    public function generateMrfReport($request) {
        $mrfList = Mrf::with([
            'company',
            'jobtitle',
            'employmenttype',
            'requestor',
            'recommendor',
            'endorser',
            'approver',
            'employee',
            'nature',
            'job_classification'
        ])->where('enabled', 1);

        if ($request->status_id) {
            $mrfList->where('status', $request->status_id);
        }

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

        if ($request->job_title_id) {
            $mrfList->where('job_title', $request->job_title_id);
        }

        if ($request->job_classification_id) {
            $mrfList->where('classification', $request->job_classification_id);
        }

        if ($request->type_id) {
            $mrfList->where('nature_of_request', $request->type_id);
        }

        if ($request->filled('start_date') && $request->filled('end_date')) {
            $fromDate = Carbon::parse($request->start_date)->startOfDay();
            $toDate = Carbon::parse($request->end_date)->endOfDay();
    
            $mrfList->whereBetween('mrf.created_at', [$fromDate, $toDate]);
        }

        $mrfList = $mrfList->get();

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

    public function generatePafReport($request) {

        DB::enableQueryLog();

        $data = PersonnelActionForm::with([
            'employee',
            'type_of_movement', 
            'requestor',
            'recommendor',
            'endorser',
            'approver',
            'unit',
        ])->select('personnel_action_forms.id', 'jt_prev.name as prev_position_name', 'jt_next.name as next_position_name',
        'personnel_action_forms.*')
        ->join('job_titles as jt_prev', 'personnel_action_forms.position_id', '=', 'jt_prev.id')
        ->join('job_titles as jt_next', 'personnel_action_forms.position' , '=', 'jt_next.id');
        
        if ($request->employee_id) {
            $data->whereHas('employee', function ($query) use ($request) {
                $query->where('employee_id', $request->employee_id);
            });
        }

        if ($request->status_id) {
            $data->where('status', $request->status_id);
        }

        if ($request->company_id) {
            $data->whereHas('employee', function ($query) use ($request) {
                $query->where('company_id', $request->company_id);
            });
            $data->where('company_id', $request->company_id);
        }

        if ($request->company_id) {
            $data->whereHas('employee', function ($query) use ($request) {
                $query->where('classification_id', $request->job_classification_id);
            });
        }

        if ($request->dealer_id) {
            $data->whereHas('employee', function ($query) use ($request) {
                $query->where('dealer_id', $request->dealer_id);
            });
        }

        if ($request->division_id) {
            $data->whereHas('employee', function ($query) use ($request) {
                $query->where('division_id', $request->division_id);
            });
        }

        if ($request->department_id) {
            $data->whereHas('employee', function ($query) use ($request) {
                $query->where('department_id', $request->department_id);
            });
        }

        if ($request->section_id) {
            $data->whereHas('employee', function ($query) use ($request) {
                $query->where('section_id', $request->section_id);
            });
        }

        if ($request->filled('start_date') && $request->filled('end_date')) {
            $fromDate = Carbon::parse($request->start_date)->startOfDay();
            $toDate = Carbon::parse($request->end_date)->endOfDay();
    
            $data->whereBetween('personnel_action_forms.created_at', [$fromDate, $toDate]);
        }

        // dd(DB::getQueryLog());
        
        $mrfList = $data->get();

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

    public function generateApplicantReport($request) {
        // dd($request->priceRange[0]);
        DB::enableQueryLog();
        $mrfList = Applicant::with([
            'mrf'
        ])->where('enabled', 1);

        if ($request->status_id) {
            $mrfList->where('status', $request->status_id);
        }
        
        if ($request->job_title_id) {
            $mrfList->whereHas('mrf', function ($query) use ($request) {
                $query->where('job_title', $request->job_title_id);
            });
        }

        $mrfList->whereBetween('last_salary', [$request->priceRange[0], $request->priceRange[1]]);

        $mrfList = $mrfList->get();

        // dd(DB::getQueryLog());

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

    public function generateUndertimeReport($request) 
    {
        $startDate = new DateTime($request->start_date);
        $endDate = new DateTime($request->end_date);
        $daysDiff = $startDate->diff($endDate)->days;
        
        if ($daysDiff > 93) {
            return response()->json(['error' => 'Date range too large. Maximum 3 months allowed.'], 400);
        }

        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 {
            $query = Employee::select('employee_id', 'firstname', 'lastname')->where('enabled', 1);
            if ($request->company_id) $query->where('company_id', $request->company_id);
            if ($request->dealer_id) $query->where('dealer_id', $request->dealer_id);
            $employees = $query->get();
        }
        
        // if ($employees->isEmpty()) return response()->json(['data' => []]);
        // if ($employees->count() > 500) {
        //     return response()->json(['error' => 'Too many employees selected. Maximum 500 allowed.'], 400);
        // }

        $dates = [];
        $current = clone $startDate;
        while ($current < $endDate) {
            $dates[] = $current->format('Y-m-d');
            $current->modify('+1 day');
        }

        $employeeIds = $employees->pluck('employee_id')->toArray();
        $employeeAttendance = new EmployeeAttendanceController();
        
        $wfhRecords = EmployeeWfh::select('employee_id', 'date')
            ->whereNotNull('attachment')
            ->whereIn('employee_id', $employeeIds)
            ->whereIn('date', $dates)
            ->where('status', 2)
            ->where('validated', 1)
            ->get()
            ->groupBy(['employee_id', 'date']);

        $workSuspensions = [];
        foreach ($dates as $date) {
            $workSuspensions[$date] = $employeeAttendance->getWorkSuspension($date);
        }

        $data = [];
        foreach ($employees as $employee) {
            $employeeData = [];
            
            foreach ($dates as $formattedDate) {
                $timelogs = $employeeAttendance->getTimelog($formattedDate, $employee->employee_id);
                $schedule = $employeeAttendance->getSchedule($formattedDate, $employee->employee_id);
                $ob = $employeeAttendance->getOB($formattedDate, $employee->employee_id);
                $workSuspension = $workSuspensions[$formattedDate] ?? null;
                $wfhRecord = isset($wfhRecords[$employee->employee_id][$formattedDate]) ? 
                            $wfhRecords[$employee->employee_id][$formattedDate]->first() : null;
                $leave = $employeeAttendance->getLeave($formattedDate, $employee->employee_id);
                $offsetAvailment = $employeeAttendance->getOffsetAvailment($formattedDate, $employee->employee_id);
                $halfDayLeave = $leave && $leave->half_day == 1;
                
                $undertime = $employeeAttendance->getUndertime(
                    $timelogs, $schedule, $workSuspension, $formattedDate, 
                    $ob, $offsetAvailment, $wfhRecord, $halfDayLeave
                );
                
                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'));

        $employeeQuery = 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',
            ])
            ->where('enabled', 1);

        if ($request->filled('employee_id') && is_array($request->employee_id)) {
            $employeeQuery->whereIn('employee_id', $request->employee_id);
        } else {
            if ($request->filled('company_id')) {
                $employeeQuery->where('company_id', $request->company_id);
            }
            if ($request->filled('dealer_id')) {
                $employeeQuery->where('dealer_id', $request->dealer_id);
            }
        }

        $results = [];
        $employeeQuery->chunk(100, function ($employees) use (&$results) {
            foreach ($employees as $employee) {
                $results[] = $employee;
            }
        });

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

    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.id', 'c.name', 'c.code'); 

        if ($request->company_id > 0) {
            $employeeCountPerCompany->where('c.id', $request->company_id);
        }

        $employeeCountPerCompany = $employeeCountPerCompany->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 = [];

        // dd($request->all());
        $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');

            $months = Arr::wrap($request->month);
            $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(',', $months) . ')')
                ->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(',', $months) . ')')
                ->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(',', $months) . ')')
                ->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]);
    }

    private function getPreviousCutOff($company_id,$payroll_date)
    {
        // var_dump($payroll_date);
        $salary_date = Carbon::parse($payroll_date)->format('j');
        // var_dump($salary_date);
        $cut_off = \App\Models\CutoffSetting::where(fn($q) => $q->where('company_id', $company_id)->orWhereNull('company_id'))
            ->where('salary_day','!=', $salary_date)
            ->first();
        // echo'<pre>';print_r($cut_off);echo'<pre>';
        $start_month = Carbon::parse($payroll_date)->format('Y-m');
        if($salary_date < $cut_off->salary_day){
            $start_month = Carbon::parse($start_month . '-01')->subMonth()->format('Y-m');
        }

        // var_dump('start month '.$start_month);
        // Adjust the logic based on the cutoff days
        if ($cut_off->day_start > $cut_off->day_end) {
            // When day_start is greater than day_end, start_month should be the previous month
          
            $start_month = Carbon::parse($start_month . '-01')->subMonth()->format('Y-m');
            $end_month = Carbon::parse($start_month . '-01')->addMonth()->format('Y-m');
        } else {
            // Otherwise, use the current month for both start and end
            $end_month = $start_month;
        }

        // Construct and format day_start and day_end with Y-m-d
        $day_start_date = Carbon::parse($start_month . '-' . $cut_off->day_start);
        $day_end_date = Carbon::parse($end_month . '-' . $cut_off->day_end);
        // dd(['start_month'=> $start_month,'end_month'=> $end_month,'day_start' => $day_start_date,'day_end' => $day_end_date]);
        // Generate array of dates from start to end
        $date_range = [];
        $current_date = $day_start_date->copy();

        while ($current_date->lte($day_end_date)) {
            $date_range[] = $current_date->format('Y-m-d');
            $current_date->addDay(); // Increment by 1 day
        }

        return [
            'cut_off' => $cut_off->cut_off,
            'start_date' => $day_start_date->format('Y-m-d'),
            'end_date' => $day_end_date->format('Y-m-d'),
            'date_range' => $date_range
        ];
    }

    private function generateLateApprovalReport(Request $request)
    {
        $cut_off ='';
        $cut_off = $this->getPreviousCutOff($request->selectedCompanyId,$request->payroll_date);
        // echo'<pre>';print_r( $cut_off['date_range']);echo'</pre>';
        if ($request->type == 1) {
            $data = \App\Models\EmployeeLeave::
                join('payroll_summaries as ps', fn($join) => $join->on(
                    'ps.employee_id', '=', 'employee_leaves.employee_id'
                ))
                ->join('employees', 'employee_leaves.employee_id', '=', 'employees.employee_id')
                ->join('employees as approver', 'employee_leaves.approver_id', '=', 'approver.employee_id')
                ->join('leave_types', 'employee_leaves.leave_id', '=', 'leave_types.id')
                ->join('leave_reasons', 'employee_leaves.leave_reason_id', '=', 'leave_reasons.id')
                ->join('companies','employee_leaves.company_id','companies.id')
                ->join('dealerships','employee_leaves.dealer_id','dealerships.id')
                ->where('employee_leaves.posted',0)
                ->where('employee_leaves.status', 2)
                ->whereIn('employee_leaves.date_from', $cut_off['date_range'])
                ->whereIn('employee_leaves.date_to',  $cut_off['date_range']);
            // if ($request->payroll_date) {
            //     // Apply payroll_date filter if provided
            //     // $data = $data->whereIn('employee_leaves.payroll_date', $request->payroll_date);
            // }
            if ($request->employee_id > 0) {
                $data = $data->where('employee_leaves.employee_id', $request->employee_id);
            }

            if ($request->selectedCompanyId > 0) {
                $data = $data->where('employee_leaves.company_id', $request->selectedCompanyId);
            }

            if ($request->dealer_id > 0) {
                $data = $data->where('employee_leaves.dealership_id', $request->dealership_id);
            }

            $data = $data->select(
                DB::raw('companies.name as company'),
                DB::raw('dealerships.name as dealer'),
                DB::raw('employees.employee_id'),
                DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as name"),
                DB::raw("CONCAT(approver.firstname, ' ', approver.lastname) as approver_name"),
                DB::raw('leave_types.name as leavetype'),
                DB::raw('leave_reasons.name as reason'),
                DB::raw('employee_leaves.date_from'),
                DB::raw('employee_leaves.date_to'),
                DB::raw('employee_leaves.hours'),
                DB::raw('employee_leaves.approved_at')
            )
            ->groupBy(
                DB::raw('companies.name'),
                DB::raw('dealerships.name'),
                DB::raw('employees.employee_id'),
                DB::raw("CONCAT(employees.firstname, ' ', employees.lastname)"),
                DB::raw("CONCAT(approver.firstname, ' ', approver.lastname)"),
                DB::raw('leave_types.name'),
                DB::raw('leave_reasons.name'),
                'employee_leaves.date_from',
                'employee_leaves.date_to',
                'employee_leaves.hours',
                DB::raw('employee_leaves.approved_at')
            )
            ->get();
                
            return response()->json(['data' => $data]);
        }else if($request->type ==2){
            $data = \App\Models\EmployeeOb::
    
                join('employees', 'employee_obs.employee_id', '=', 'employees.employee_id')
                ->join('employees as approver', 'employee_obs.approver_id', '=', 'approver.employee_id')
                ->join('companies','employee_obs.company_id','companies.id')
                ->join('dealerships','employee_obs.dealer_id','dealerships.id')
                ->where('employee_obs.posted',0)
                ->where('employee_obs.status', 2)
                ->whereIn('employee_obs.date_from', $cut_off['date_range'])
                ->whereIn('employee_obs.date_to',  $cut_off['date_range']);
            if ($request->employee_id > 0) {
                $data = $data->where('employee_obs.employee_id', $request->employee_id);
            }

            if ($request->selectedCompanyId > 0) {
                $data = $data->where('employee_obs.company_id', $request->selectedCompanyId);
            }

            if ($request->dealership_id > 0) {
                $data = $data->where('employee_obs.dealer_id', $request->dealership_id);
            }

            $data = $data->select(
                DB::raw('companies.name as company'),
                DB::raw('dealerships.name as dealer'),
                DB::raw('employees.employee_id'),
                DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as name"),
                DB::raw("CONCAT(approver.firstname, ' ', approver.lastname) as approver_name"),
                DB::raw('TIMESTAMPDIFF(HOUR, employee_obs.from_time, employee_obs.to_time) as hours'),
                'employee_obs.date_from',
                'employee_obs.date_to',
                'employee_obs.description',
                'employee_obs.approved_at',
                

            )
            ->groupBy(
                DB::raw('companies.name'),
                DB::raw('dealerships.name'),
                DB::raw('employees.employee_id'),
                DB::raw("CONCAT(employees.firstname, ' ', employees.lastname)"),
                DB::raw("CONCAT(approver.firstname, ' ', approver.lastname)"),
                DB::raw('TIMESTAMPDIFF(HOUR, employee_obs.from_time, employee_obs.to_time)'),
                'employee_obs.date_from',
                'employee_obs.date_to',
                'employee_obs.description',
                'employee_obs.approved_at',
            )
            ->get();

            return response()->json(['data' => $data]);
        }else if($request->type ==3){
            $data = \App\Models\EmployeeOvertime::
                join('employees', 'employee_overtimes.employee_id', '=', 'employees.employee_id')
                ->join('employees as approver', 'employee_overtimes.approver_id', '=', 'approver.employee_id')
                ->join('companies','employee_overtimes.company_id','companies.id')
                ->join('dealerships','employee_overtimes.dealer_id','dealerships.id')
                ->where('employee_overtimes.posted',0)
                ->where('employee_overtimes.status', 2);
                // ->whereIn('employee_overtimes.date_from', $cut_off['date_range'])
                // ->whereIn('employee_overtimes.date_to',  $cut_off['date_range']);
            if ($request->employee_id > 0) {
                $data = $data->where('employee_overtimes.employee_id', $request->employee_id);
            }
            if ($request->selectedCompanyId > 0) {
                $data = $data->where('employee_overtimes.company_id', $request->selectedCompanyId);
            }

            if ($request->dealership_id > 0) {
                $data = $data->where('employee_overtimes.dealer_id', $request->dealership_id);
            }

            $data = $data->select(
                DB::raw('companies.name as company'),
                DB::raw('dealerships.name as dealer'),
                DB::raw('employees.employee_id'),
                DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as name"),
                DB::raw("CONCAT(approver.firstname, ' ', approver.lastname) as approver_name"),
                'employee_overtimes.date_from',
                'employee_overtimes.date_to',
                'employee_overtimes.hours',
                'employee_overtimes.description',
                'employee_overtimes.approved_at',
            )
            ->groupBy(
                DB::raw('companies.name'),
                DB::raw('dealerships.name'),
                DB::raw('employees.employee_id'),
                DB::raw("CONCAT(employees.firstname, ' ', employees.lastname)"),
                DB::raw("CONCAT(approver.firstname, ' ', approver.lastname)"),
                'employee_overtimes.date_from',
                'employee_overtimes.date_to',
                'employee_overtimes.description',
                'employee_overtimes.approved_at',
                'employee_overtimes.hours',
            )
            ->get();

            return response()->json(['data' => $data]);
        }else if($request->type ==4){
            $data = \App\Models\EmployeeTimeAdjustment::
                join('employees', 'employee_time_adjustments.employee_id', '=', 'employees.employee_id')
                ->join('employees as approver', 'employee_time_adjustments.approver_id', '=', 'approver.employee_id')
                ->join('companies','employee_time_adjustments.company_id','companies.id')
                ->join('dealerships','employee_time_adjustments.dealer_id','dealerships.id')
                ->where('employee_time_adjustments.posted',0)
                ->where('employee_time_adjustments.status', 2)
                ->whereIn('employee_time_adjustments.date', $cut_off['date_range']);
            if ($request->employee_id > 0) {
                $data = $data->where('employee_time_adjustments.employee_id', $request->employee_id);
            }
            if ($request->selectedCompanyId > 0) {
                $data = $data->where('employee_time_adjustments.company_id', $request->selectedCompanyId);
            }

            if ($request->dealership_id > 0) {
                $data = $data->where('employee_time_adjustments.dealer_id', $request->dealership_id);
            }

            $data = $data->select(
                DB::raw('companies.name as company'),
                DB::raw('dealerships.name as dealer'),
                DB::raw('employees.employee_id'),
                DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as name"),
                DB::raw("CONCAT(approver.firstname, ' ', approver.lastname) as approver_name"),
                'employee_time_adjustments.date',
                'employee_time_adjustments.description',
                'employee_time_adjustments.approved_at',
                DB::raw("IF(in_out = 1, 'In', 'Out') as `In/Out`")    

            )
            ->groupBy(
                DB::raw('companies.name'),
                DB::raw('dealerships.name'),
                DB::raw('employees.employee_id'),
                DB::raw("CONCAT(employees.firstname, ' ', employees.lastname)"),
                DB::raw("CONCAT(approver.firstname, ' ', approver.lastname)"),
                'employee_time_adjustments.date',
                'employee_time_adjustments.description',
                'employee_time_adjustments.approved_at',
                DB::raw("IF(in_out = 1, 'In', 'Out')")
            )
            ->get();

            return response()->json(['data' => $data]);
        }else if($request->type == 5){
            $data = \App\Models\EmployeeChangeSchedule::
                join('employees', 'employee_change_schedules.employee_id', '=', 'employees.employee_id')
                ->join('employees as approver', 'employee_change_schedules.approver_id', '=', 'approver.employee_id')
                ->join('companies','employee_change_schedules.company_id','companies.id')
                ->join('dealerships','employee_change_schedules.dealer_id','dealerships.id')
                ->where('employee_change_schedules.posted',0)
                ->where('employee_change_schedules.status', 2)
                ->whereIn('employee_change_schedules.date', $cut_off['date_range']);
                // ->whereIn('employee_overtimes.date_to',  $cut_off['date_range']);
            if ($request->employee_id > 0) {
                $data = $data->where('employee_change_schedules.employee_id', $request->employee_id);
            }  

            if ($request->selectedCompanyId > 0) {
                $data = $data->where('employee_change_schedules.company_id', $request->selectedCompanyId);
            }

            if ($request->dealership_id > 0) {
                $data = $data->where('employee_change_schedules.dealer_id', $request->dealership_id);
            }

            $data = $data->select(
                DB::raw('companies.name as company'),
                DB::raw('dealerships.name as dealer'),
                DB::raw('employees.employee_id'),
                DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as name"),
                DB::raw("CONCAT(approver.firstname, ' ', approver.lastname) as approver_name"),
                DB::raw('(TIMESTAMPDIFF(HOUR, employee_change_schedules.from_time, employee_change_schedules.to_time) +1) as hours'),
                'employee_change_schedules.date',
                'employee_change_schedules.description',
                'employee_change_schedules.approved_at',
            )
            ->groupBy(
                DB::raw('companies.name'),
                DB::raw('dealerships.name'),
                DB::raw('employees.employee_id'),
                DB::raw("CONCAT(employees.firstname, ' ', employees.lastname)"),
                DB::raw("CONCAT(approver.firstname, ' ', approver.lastname)"),
                'employee_change_schedules.date',
                'employee_change_schedules.description',
                'employee_change_schedules.approved_at',
                 DB::raw('(TIMESTAMPDIFF(HOUR, employee_change_schedules.from_time, employee_change_schedules.to_time) +1)')
            )
            ->get();
            return response()->json(['data' => $data]);
        }else if($request->type == 6){
            $data = \App\Models\EmployeeOffset::
                join('employees', 'employee_offsets.employee_id', '=', 'employees.employee_id')
                ->join('employees as approver', 'employee_offsets.approver_id', '=', 'approver.employee_id')
                ->join('companies','employee_offsets.company_id','companies.id')
                ->join('dealerships','employee_offsets.dealer_id','dealerships.id')
                ->where('employee_offsets.posted',0)
                ->where('employee_offsets.status', 2);
                // ->whereIn('employee_offsets.date_from', $cut_off['date_range'])
                // ->whereIn('employee_offsets.date_to',  $cut_off['date_range']);
            if ($request->employee_id > 0) {
                $data = $data->where('employee_offsets.employee_id', $request->employee_id);
            }      
            
            if ($request->selectedCompanyId > 0) {
                $data = $data->where('employee_offsets.company_id', $request->selectedCompanyId);
            }

            if ($request->dealership_id > 0) {
                $data = $data->where('employee_offsets.dealership_id', $request->dealership_id);
            }

            $data = $data->select(
                DB::raw('companies.name as company'),
                DB::raw('dealerships.name as dealer'),
                DB::raw('employees.employee_id'),
                DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as name"),
                DB::raw("CONCAT(approver.firstname, ' ', approver.lastname) as approver_name"),
                'employee_offsets.date_from',
                'employee_offsets.date_to',
                'employee_offsets.hours',
                'employee_offsets.description',
                'employee_offsets.approved_at',
            )
            ->groupBy(
                DB::raw('companies.name'),
                DB::raw('dealerships.name'),
                DB::raw('employees.employee_id'),
                DB::raw("CONCAT(employees.firstname, ' ', employees.lastname)"),
                DB::raw("CONCAT(approver.firstname, ' ', approver.lastname)"),
                'employee_offsets.date_from',
                'employee_offsets.date_to',
                'employee_offsets.description',
                'employee_offsets.approved_at',
                'employee_offsets.hours',
            )
            ->get();

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

    public function generateAttendancePostingReport(Request $request)
    {
        // Determine reference number and cut-off dates
        if ($request->company_id > 0 && empty($request->dealership_id)) {
            $ref_no = AttendancePosting::where('payroll_date', $request->payroll_date)
                ->pluck('reference_no');

            $cut_off = AttendancePosting::where('payroll_date', $request->payroll_date)
                ->select('start_date', 'end_date')
                ->first();

            if (!$cut_off) {
                return response()->json([
                    'status' => false,
                    'message' => 'No Cut-Off Date',
                ], Response::HTTP_INTERNAL_SERVER_ERROR);
            }

            $employee_ids = AttendancePostingDtls::where('company_id', $request->company_id)
                ->whereIn('reference_no', $ref_no)
                ->pluck('employee_id')
                ->toArray();
        } else {
            $cut_off = AttendancePosting::where('reference_no', $request->reference_no)
                ->select('start_date', 'end_date')
                ->first();

            if (!$cut_off) {
                return response()->json([
                    'status' => false,
                    'message' => 'No Cut-Off Date',
                ], Response::HTTP_INTERNAL_SERVER_ERROR);
            }

            $employee_ids = AttendancePostingDtls::where('reference_no', $request->reference_no)
                ->pluck('employee_id')
                ->toArray();
        }

        $start_date = $cut_off->start_date;
        $end_date = $cut_off->end_date;

        // Get employees data
        if ($request->employee_id) {
            $employees = Employee::with('salary_and_wages')
                ->where('employee_id', $request->employee_id)
                ->select('employee_id', 'firstname', 'lastname', 'work_location_id')
                ->orderBy('lastname')
                ->get();
        } else {
            $employees = Employee::with('salary_and_wages')
                ->whereIn('employee_id', $employee_ids)
                ->select('employee_id', 'firstname', 'lastname', 'work_location_id')
                ->orderBy('lastname')
                ->get();
        }

        $data = [];
        
        // Preload all schedules for all employees to minimize DB queries
        $employee_schedules = ProcessedSchedule::whereIn('employee_id', $employees->pluck('employee_id'))
            ->whereDate('payroll_date', $request->payroll_date)
            ->orderBy('date', 'ASC')
            ->get()
            ->groupBy('employee_id');
            
        // Preload all dates for which we'll need data
        $all_dates = [];
        foreach ($employee_schedules as $emp_schedules) {
            foreach ($emp_schedules as $schedule) {
                $all_dates[] = $schedule->date;
            }
        }
        $all_dates = array_unique($all_dates);
        
        // Preload all required data for the date range
        $processed_timelogs = ProcessedTimelog::whereIn('employee_id', $employees->pluck('employee_id'))
            ->whereIn(DB::raw('DATE(date)'), $all_dates)
            ->get()
            ->groupBy(function($item) {
                return $item->employee_id . '_' . $item->date . '_' . $item->in_out;
            });
            
        $processed_obs = ProcessedOb::whereIn('employee_id', $employees->pluck('employee_id'))
            ->whereIn(DB::raw('DATE(date)'), $all_dates)
            ->get()
            ->groupBy(function($item) {
                return $item->employee_id . '_' . $item->date;
            });
            
        $processed_tardiness = ProcessedTardiness::whereIn('employee_id', $employees->pluck('employee_id'))
            ->whereIn(DB::raw('DATE(date)'), $all_dates)
            ->get()
            ->groupBy(function($item) {
                return $item->employee_id . '_' . $item->date;
            });
            
        $processed_undertime = ProcessedUndertime::whereIn('employee_id', $employees->pluck('employee_id'))
            ->whereIn(DB::raw('DATE(date)'), $all_dates)
            ->get()
            ->groupBy(function($item) {
                return $item->employee_id . '_' . $item->date;
            });
            
        $processed_night_diff = ProcessedNightDifferential::whereIn('employee_id', $employees->pluck('employee_id'))
            ->whereIn(DB::raw('DATE(date)'), $all_dates)
            ->get()
            ->groupBy(function($item) {
                return $item->employee_id . '_' . $item->date;
            });
            
        $processed_overtime = ProcessedOvertime::whereIn('employee_id', $employees->pluck('employee_id'))
            ->whereIn(DB::raw('DATE(date)'), $all_dates)
            ->get()
            ->groupBy(function($item) {
                return $item->employee_id . '_' . $item->date;
            });
            
        $processed_absent = ProcessedAbsent::whereIn('employee_id', $employees->pluck('employee_id'))
            ->whereIn(DB::raw('DATE(date)'), $all_dates)
            ->get()
            ->groupBy(function($item) {
                return $item->employee_id . '_' . $item->date;
            });
            
        $processed_daily_work = ProcessedDailyWorkHours::whereIn('employee_id', $employees->pluck('employee_id'))
            ->whereIn(DB::raw('DATE(date)'), $all_dates)
            ->get()
            ->groupBy(function($item) {
                return $item->employee_id . '_' . $item->date;
            });
            
        $processed_leave = ProcessedLeave::whereIn('employee_id', $employees->pluck('employee_id'))
            ->whereIn(DB::raw('DATE(date)'), $all_dates)
            ->get()
            ->groupBy(function($item) {
                return $item->employee_id . '_' . $item->date;
            });

        // Preload holidays
        $holidays = Holiday::leftJoin('dealerships', 'holidays.dealership_id', '=', 'dealerships.id')
            ->where('holidays.enabled', 1)
            ->where(function($query) use ($start_date, $end_date) {
                $query->whereRaw('? BETWEEN holidays.from_date AND holidays.to_date', [$start_date])
                    ->orWhereRaw('? BETWEEN holidays.from_date AND holidays.to_date', [$end_date])
                    ->orWhere(function($q) use ($start_date, $end_date) {
                        $q->where('holidays.recurring', 1);
                    });
            })
            ->select('holidays.*', 'dealerships.locality_id')
            ->get();
            
        // Process employee data
        foreach ($employees as $employee) {
            $data[$employee->employee_id]['start_date'] = date('m/d/Y', strtotime($start_date));
            $data[$employee->employee_id]['end_date'] = date('m/d/Y', strtotime($end_date));
            $data[$employee->employee_id]['name'] = $employee->lastname . ', ' . $employee->firstname;
            $data[$employee->employee_id]['employee_id'] = $employee->employee_id;
            $data[$employee->employee_id]['payroll_rate'] = $employee->salary_and_wages->payroll_rate ?? 'N/A';
            
            $schedules = $employee_schedules[$employee->employee_id] ?? collect();
            
            foreach ($schedules as $schedule) {
                $schedule_date = $schedule->date;
                $date_key = $employee->employee_id . '_' . $schedule_date;
                
                $data[$employee->employee_id]['day'][] = Carbon::parse($schedule_date)->format('D');
                $data[$employee->employee_id]['date'][] = date('m/d/Y', strtotime($schedule_date));
                $data[$employee->employee_id]['shift'][] = ($schedule->schedule_in ? $schedule->schedule_in : '-') . ' - ' . ($schedule->schedule_out ? $schedule->schedule_out : '-');
                
                // Time In / Out
                $time_in_key = $date_key . '_I';
                $time_out_key = $date_key . '_O';
                
                $time_in = isset($processed_timelogs[$time_in_key]) ? $processed_timelogs[$time_in_key][0]->time : null;
                $time_out = isset($processed_timelogs[$time_out_key]) ? $processed_timelogs[$time_out_key][0]->time : null;
                
                $ob = $processed_obs[$date_key] ?? null;
                $ob_time_in = $ob ? $ob[0]->from_time : null;
                $ob_time_out = $ob ? $ob[0]->to_time : null;
                
                if ($ob_time_in) {
                    if ($time_in) {
                        if (new DateTime($ob_time_in) < new DateTime($time_in)) {
                            $time_in = $ob_time_in;
                        }
                    } else {
                        $time_in = $ob_time_in;
                    }
                }
                
                if ($ob_time_out) {
                    if ($time_out) {
                        if (new DateTime($ob_time_out) > new DateTime($time_out)) {
                            $time_out = $ob_time_out;
                        }
                    } else {
                        $time_out = $ob_time_out;
                    }
                    
                    if (new DateTime($ob_time_out) < new DateTime('5:00:00')) {
                        $time_out = $ob_time_out;
                    }
                }
                
                $data[$employee->employee_id]['time_in_out'][] = ($time_in ? $time_in : '-') . ' - ' . ($time_out ? $time_out : '-');
                
                // Tardiness
                $tardiness = $processed_tardiness[$date_key] ?? null;
                $tardiness_mins = $tardiness ? $tardiness[0]->no_of_minutes : 0;
                $tardinessHrs = $this->getHoursAndMinutes($tardiness_mins);
                $data[$employee->employee_id]['tardiness_mins'][] = $tardiness_mins ? $tardinessHrs : 0;
                
                // Undertime
                $undertime = $processed_undertime[$date_key] ?? null;
                $undertime_mins = $undertime ? $undertime[0]->no_of_minutes : 0;
                $undertimeHrs = $this->getHoursAndMinutes($undertime_mins);
                $data[$employee->employee_id]['undertime_mins'][] = $undertime_mins ? $undertimeHrs : 0;
                
                // Night Differential
                $night_diff = $processed_night_diff[$date_key] ?? null;
                $nd_hrs = $night_diff ? $night_diff[0]->no_of_hours : 0;
                $nightDiffHrs = $this->getHoursAndMinutes($nd_hrs);
                $data[$employee->employee_id]['nd_hrs'][] = $nd_hrs ? $nightDiffHrs : 0;
                
                // Overtime
                $overtime = $processed_overtime[$date_key] ?? null;
                $overtime_hrs = $overtime ? $overtime[0]->no_of_hours : 0;
                $overtimeHrs = $this->getHoursAndMinutes($overtime_hrs * 60);
                $data[$employee->employee_id]['overtime_hrs'][] = $overtime_hrs ? $overtimeHrs : 0;
                
                // Absent
                $absent = $processed_absent[$date_key] ?? null;
                $absent_days = $absent ? $absent[0]->no_of_days : 0;
                
                // Duty hours
                $daily_work = $processed_daily_work[$date_key] ?? null;
                $duty_hours = $daily_work ? $daily_work[0]->work_hours : null;
                $duty_hours_value = isset($duty_hours) && $duty_hours !== null ? $duty_hours : (number_format($schedule->duty_hours, 2) ?? 0);

                // Absent
                $data[$employee->employee_id]['absent_days'][] = $absent_days ? number_format($absent_days * 8, 2) : 0;

                $duty_hours = ($absent_days && ($absent_days == 1)) ? 0 : $duty_hours_value;
                $dutyHrs = $this->getHoursAndMinutes($duty_hours * 60);
                $data[$employee->employee_id]['duty_hours'][] = $duty_hours ? $dutyHrs : 0;
                
                // Leave
                $leave = $processed_leave[$date_key] ?? null;
                // dd($leave);
                $leave_hrs = $leave ? ($leave[0]->without_pay == 0 ? $leave[0]->no_of_hours : 0 ) : 0;
                $data[$employee->employee_id]['leave_hrs'][] = $leave_hrs ? $leave_hrs : 0;

                // Check for holiday
                $schedule_date_obj = Carbon::parse($schedule_date);
                $holiday = $holidays->first(function($h) use ($schedule_date_obj, $employee) {
                    $from_date = Carbon::parse($h->from_date);
                    $to_date = Carbon::parse($h->to_date);
                    
                    return ($schedule_date_obj->between($from_date, $to_date) || 
                        ($h->recurring == 1 && $schedule_date_obj->format('m-d') == $from_date->format('m-d'))) &&
                        ($h->locality_id == $employee->work_location_id || $h->locality_id === null);
                });
                
                $data[$employee->employee_id]['type'][] = $holiday 
                    ? ($holiday->holiday_type == 1 ? 'RH' : 
                        ($holiday->holiday_type == 2 ? 'SH' : 
                        ($holiday->holiday_type == 3 && $holiday->locality_id == $employee->work_location_id ? 'LH' : ($schedule->restday == 1 ? 'REST' : 'REG'))))
                    : ($schedule->restday == 1 ? 'REST' : 'REG');
            }
        }

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

    public function generateEmployeeTaxApplicationReport(Request $request)
    {
        $data = Employee::with([
                'salary_and_wages' => function ($query) { $query->select('employee_id', 'tax_application', 'salary_rate'); }, 
                'position' => function ($query) { $query->select('id', 'name'); },
                'dealership' => function ($query) { $query->select('id', 'name'); }
            ])
            ->where('company_id', $request->company_id)
            ->whereNull('date_resign');

        if($request->employee_id > 0){
            $data = $data->whereIn('employee_id', $request->employee_id);
        }

        if ($request->dealer_id > 0) {
            $data = $data->where('dealer_id', $request->dealer_id);
        }
        
        $filters = [
            'division_id' => 'division_id',
            'department_id' => 'department_id',
            'position_id' => 'position_id'
        ]; 
        
        foreach ($filters as $column => $param) {
            if ($request->$param > 0) {
                $data = $data->where($column, $request->$param);
            }
        }

        $data = $data->select('employee_id', 'firstname', 'lastname', 'position_id', 'dealer_id')->get();

        return response()->json($data);
    }
    
    public function generateEmployeePagibig(Request $request)
    {
        $data = PayrollSummary::with([
            'personal_info' => function ($query) { $query->select('employee_id', 'pagibig', 'birth_date'); },
            'employee' => function ($query) { 
                $query->select('employee_id', 'firstname', 'lastname'); 
            }
        ])
        ->join('employees', 'payroll_summaries.employee_id', '=', 'employees.employee_id')
        ->join('dealerships', 'payroll_summaries.dealership_id', '=', 'dealerships.id')
        ->where('employees.company_id', $request->company_id)
        ->whereYear('payroll_date', $request->year)
        ->whereMonth('payroll_date', $request->month);
        
        if($request->employee_id > 0){
            $data = $data->whereIn('payroll_summaries.employee_id', $request->employee_id);
        }

        if ($request->dealer_id > 0) {
            $data = $data->where('payroll_summaries.dealership_id', $request->dealer_id);
        }
        
        $filters = [
            'employees.division_id' => 'division_id',
            'employees.department_id' => 'department_id',
            'employees.position_id' => 'position_id'
        ]; 
        
        foreach ($filters as $column => $param) {
            if ($request->$param > 0) {
                $data = $data->where($column, $request->$param);
            }
        }

        $data = $data->selectRaw('payroll_summaries.employee_id, SUM(pagibig_contribution) as pagibig_contribution, SUM(pagibig_contribution_employer) as pagibig_contribution_employer, dealerships.name as dealer_name')
            ->groupBy('dealerships.name', 'payroll_summaries.employee_id', 'employees.lastname', 'employees.firstname')
            ->orderBy('employees.lastname', 'asc')
            ->get();

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

    public function generateEmployeePhilhealth(Request $request)
    {
        // dd($request->all());

        $data = PayrollSummary::with([
                'personal_info' => function ($query) { $query->select('employee_id', 'philhealth', 'birth_date'); } ,
                'employee' => function ($query) { $query->select('employee_id', 'firstname', 'lastname'); },
                'employee.salary_and_wages' => function ($query) { $query->select('employee_id', 'salary_rate'); }
            ])
            ->join('employees', 'payroll_summaries.employee_id', '=', 'employees.employee_id')
            ->join('dealerships', 'payroll_summaries.dealership_id', '=', 'dealerships.id')
            ->where('employees.company_id', $request->company_id)
            ->whereYear('payroll_date', $request->year)
            ->whereMonth('payroll_date', $request->month);

        if($request->employee_id > 0){
            $data = $data->whereIn('payroll_summaries.employee_id', $request->employee_id);
        }

        if ($request->dealer_id > 0) {
            $data = $data->where('payroll_summaries.dealership_id', $request->dealer_id);
        }

        $filters = [
            'employees.division_id' => 'division_id',
            'employees.department_id' => 'department_id',
            'employees.position_id' => 'position_id'
        ]; 
        
        foreach ($filters as $column => $param) {
            if ($request->$param > 0) {
                $data = $data->where($column, $request->$param);
            }
        }

        $data = $data->selectRaw('payroll_summaries.employee_id, SUM(philhealth_contribution) as total_philhealth, dealerships.name as dealer_name' )
            ->groupBy('dealerships.name', 'payroll_summaries.employee_id', 'employees.lastname', 'employees.firstname')
            ->orderBy('employees.lastname', 'asc')
            ->get();

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

    public function generateMonitoringAttendance(Request $request)
    {
        DB::enableQueryLog();

        // dd($request->all());
        $employees = Employee::where('company_id', $request->company_id)->whereNull('date_resign');

        if(($request->dealer_id != NULL || $request->dealer_id != '') && $request->dealer_id){
            $employees->where('dealer_id', $request->dealer_id);
        }

        if($request->division_id != NULL || $request->division_id != ''){
            $employees->where('division_id', $request->division_id);
        }

        if($request->department_id != NULL || $request->department_id != ''){
            $employees->where('department_id', $request->department_id);
        }

        if($request->position_id != NULL || $request->position_id != ''){
            $employees->where('position_id', $request->position_id);
        }

        if($request->employee_id != NULL || $request->employee_id != ''){
            $employees->where('employee_id', $request->employee_id);
        }

        $employee_ids = $employees->get();
        $employees = $employees->pluck('employee_id');
        // dd(DB::getQueryLog());
        $employee_leave = EmployeeLeave::with('leave_type', 'employee')->whereIn('employee_id', $employees)
            ->where(function($query) use ($request) {
                // Check if leave period overlaps with requested year/month
                $query->where(function($q) use ($request) {
                    // Leave starts in the requested year
                    $q->whereYear('date_from', $request->year);
                    
                    // If month is specified, also check month
                    if ($request->month) {
                        $q->whereMonth('date_from', $request->month);
                    }
                })
                ->orWhere(function($q) use ($request) {
                    // Leave ends in the requested year
                    $q->whereYear('date_to', $request->year);
                    
                    // If month is specified, also check month
                    if ($request->month) {
                        $q->whereMonth('date_to', $request->month);
                    }
                });
            })
            ->get();

            // TARDINESS

            $employeeAttendance = new EmployeeAttendanceController();

            $start_date = new DateTime();
            $start_date->setDate($request->year, $request->month, 1);
            $start_date->setTime(0, 0, 0);

            $last_day = date('t', mktime(0, 0, 0, $request->month, 1, $request->year));
            $end_date = new DateTime($request->year . '-' . str_pad($request->month, 2, '0', STR_PAD_LEFT) . '-' . $last_day);

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

            $data = [];

            foreach ($employee_ids as $employee) {
                
                $employeeData = []; 
                $processedAbsent = [];
                $processedTardiness = [];
                $processedUndertime = [];

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

                    if ($leaves = ProcessedLeave::with('leave_type')->where('date', $formattedDate)->where('employee_id', $employee['employee_id'])->get()) {
                        if ($leaves->isNotEmpty()) {
                            $processedAbsent[] = $leaves;
                        }
                    }

                    if ($tardiness = ProcessedTardiness::where('date', $formattedDate)->where('employee_id', $employee['employee_id'])->get()) {
                        if ($tardiness->isNotEmpty()) {
                            $processedTardiness[] = $tardiness;
                        }
                    }

                    if ($undertimes = ProcessedUndertime::where('date', $formattedDate)->where('employee_id', $employee['employee_id'])->get()) {
                        if ($undertimes->isNotEmpty()) {
                            $processedUndertime[] = $undertimes;
                        }
                    }
                }

                $data[] = [
                    'employee_id' => $employee->employee_id,
                    'firstname' => $employee->firstname,
                    'lastname' => $employee->lastname,
                    'absences' => $processedAbsent,
                    'tardiness' => $processedTardiness,
                    'undertimes' => $processedUndertime,
                ];
            }
        
        // dd($data);
        return response()->json([
            'data' => $data,
        ]);
    }
    
    public function generateHdmfLoan(Request $request)
    {
        $employee_ids = PayrollSummary::where('company_id', $request->company_id)
            ->whereMonth('payroll_date', $request->month)
            ->whereYear('payroll_date', $request->year);

        if ($request->dealer_id > 0) {
            $employee_ids = $employee_ids->where('dealership_id', $request->dealer_id);
        }

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

        $employees = Employee::with([
                        'salary_and_wages' => function ($query) { $query->select('employee_id', 'payroll_rate'); },
                        'company' => function ($query) { $query->select('id', 'name'); },
                        'dealership' => function ($query) { $query->select('id', 'name'); },
                        'personal_information' => function ($query) { $query->select('employee_id', 'tin', 'philhealth', 'sss', 'pagibig', 'birth_date'); },
                    ])
                    ->whereIn('employee_id', $employee_ids)
                    ->select('employee_id', 'firstname', 'lastname', 'work_location_id', 'company_id', 'dealer_id')
                    ->orderBy('lastname');

        $filters = [
            'dealer_id' => 'dealer_id',
            'division_id' => 'division_id',
            'department_id' => 'department_id',
            'position_id' => 'position_id'
        ]; 
        
        foreach ($filters as $column => $param) {
            if ($request->$param > 0) {
                $employees = $employees->where($column, $request->$param);
            }
        }

        $data = [];

        if($request->employee_id){
            $employees = $employees->whereIn('employee_id', $request->employee_id);
        }

        $employees = $employees->get();

        $groupedPayrolls = [];

        foreach ($employees as $employee) {
            $payrolls = PayrollSummary::where('employee_id', $employee->employee_id)
            ->whereMonth('payroll_date', $request->month)
            ->whereYear('payroll_date', $request->year)
                ->get();

            foreach ($payrolls as $payroll) {
                if (!isset($payroll->details['loans']) || !is_array($payroll->details['loans'])) {
                    continue;
                }

                foreach ($payroll->details['loans'] as $adjustment) {
                    $payrollItemName = $adjustment['loan_type_name'] ?? 'Unknown';

                    $entry = [
                        'id' => $adjustment['id'] ?? null,
                        'employee_id' => $employee->employee_id,
                        'lastname' => $employee->lastname,
                        'firstname' => $employee->firstname,
                        'middlename' => $employee->middlename,
                        'birth_date' => $employee->personal_information->birth_date,
                        'company' => $employee->company->name,
                        'dealer' => $employee->dealership->name,
                        'amount' => $adjustment['amount'] ?? 0,
                        'date_from' => $adjustment['date_from'] ?? null,
                        'date_to' => $adjustment['date_to'] ?? null,
                        'loan_id' => $adjustment['loan_type_id'] ?? null,
                        'tin' => str_replace('-', '', $employee->personal_information->tin) ?? '',
                        'philhealth' => str_replace('-', '', $employee->personal_information->philhealth) ?? '',
                        'sss' => str_replace('-', '', $employee->personal_information->sss) ?? '',
                        'pagibig' => str_replace('-', '', $employee->personal_information->pagibig) ?? ''
                    ];

                    $groupedPayrolls[$payrollItemName]['items'][] = $entry;

                    if (!isset($groupedPayrolls[$payrollItemName]['total'])) {
                        $groupedPayrolls[$payrollItemName]['total'] = 0;
                    }
                    $groupedPayrolls[$payrollItemName]['total'] += $entry['amount'];
                }
            }
        }

        $groupedPayrolls = array_filter($groupedPayrolls, function($loanTypeGroup) use ($request) {
            foreach ($loanTypeGroup['items'] as $item) {
                if (in_array($item['loan_id'], $request->loan_id)) {
                    return true;
                }
            }
            return false;
        });

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

    public function generateEmployeeSSS(Request $request)
    {
        $data = PayrollSummary::with([
            'personal_info' => function ($query) { $query->select('employee_id', 'sss'); },
            'employee' => function ($query) { 
                $query->select('employee_id', 'firstname', 'lastname'); 
            }
        ])
        ->join('employees', 'payroll_summaries.employee_id', '=', 'employees.employee_id')
        ->join('dealerships', 'payroll_summaries.dealership_id', '=', 'dealerships.id')
        ->where('employees.company_id', $request->company_id)
        ->whereYear('payroll_date', $request->year)
        ->whereMonth('payroll_date', $request->month)
        ->whereNull('employees.date_resign');
        
        if($request->employee_id > 0){
            $data = $data->whereIn('payroll_summaries.employee_id', $request->employee_id);
        }

        if ($request->dealer_id > 0) {
            $data = $data->where('payroll_summaries.dealership_id', $request->dealer_id);
        }
        $filters = [
            'employees.division_id' => 'division_id',
            'employees.department_id' => 'department_id',
            'employees.position_id' => 'position_id'
        ]; 
        
        foreach ($filters as $column => $param) {
            if ($request->$param > 0) {
                $data = $data->where($column, $request->$param);
            }
        }
        
        $data = $data->selectRaw('payroll_summaries.employee_id, SUM(sss_employee_share) as sss_employee_share, SUM(sss_employer_share) as sss_employer_share, dealerships.name as dealer_name')
            ->groupBy('dealerships.name', 'payroll_summaries.employee_id', 'employees.lastname', 'employees.firstname')
            ->orderBy('employees.lastname', 'asc')
            ->get();
        
        return response()->json($data);
    }

    public function generateEmployeeFlags(Request $request)
    {
        $data = Employee::with(['salary_and_wages', 'position', 'dealership'])->select('employee_id', 'firstname', 'lastname', 'position_id', 'dealer_id')
                    ->whereNull('date_resign')
                    ->where('company_id', $request->company_id);

        if($request->employee_id > 0){
            $data = $data->whereIn('employee_id', $request->employee_id);
        }
        
        if ($request->dealer_id > 0) {
            $data = $data->where('dealership_id', $request->dealer_id);
        }

        $filters = [
            'division_id' => 'division_id',
            'department_id' => 'department_id',
            'position_id' => 'position_id'
        ]; 
        
        foreach ($filters as $column => $param) {
            if ($request->$param > 0) {
                $data = $data->where($column, $request->$param);
            }
        }

        $data = $data->get();

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

    public function generateEmployeeDeductions(Request $request)
    {
        $payroll = Payroll::where('company_id', $request->company_id)
            ->where('payroll_date', $request->cutoff_id);

        if ($request->dealer_id > 0) {
            $payroll = $payroll->where('dealership_id', $request->dealer_id);
        }

        $payroll = $payroll->first();

        if ($payroll) {
            $start_date = $payroll->cut_off_start;
            $end_date = $payroll->cut_off_end;
        } else {
            return response()->json([
                'status' => false,
                'message' => 'No Cut-Off Date',
            ], Response::HTTP_INTERNAL_SERVER_ERROR);
        }

        $employee_ids = PayrollSummary::where('company_id', $request->company_id)
                ->where('payroll_date', $request->cutoff_id);

        if ($request->dealer_id > 0) {
            $employee_ids = $employee_ids->where('dealership_id', $request->dealer_id);
        }

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

        $employees = Employee::with([
                        'salary_and_wages' => function ($query) { $query->select('employee_id', 'payroll_rate'); },
                        'company' => function ($query) { $query->select('id', 'name'); },
                        'dealership' => function ($query) { $query->select('id', 'name'); },
                    ])
                    ->whereIn('employee_id', $employee_ids)
                    ->select('employee_id', 'firstname', 'lastname', 'work_location_id', 'company_id', 'dealer_id')
                    ->orderBy('lastname');

        $filters = [
            'dealer_id' => 'dealer_id',
            'division_id' => 'division_id',
            'department_id' => 'department_id',
            'position_id' => 'position_id'
        ]; 
        
        foreach ($filters as $column => $param) {
            if ($request->$param > 0) {
                $employees = $employees->where($column, $request->$param);
            }
        }

        $employees = $employees->get();

        $data = [];

        if($request->employee_id){
            $employees = Employee::whereIn('employee_id', $request->employee_id)->select('employee_id', 'firstname', 'lastname', 'work_location_id')->orderBy('lastname')->get();
        }

        $groupedPayrolls = [];

        foreach ($employees as $employee) {
            $payrolls = PayrollSummary::where('employee_id', $employee->employee_id)
                ->whereDate('payroll_date', $request->cutoff_id)
                ->get();
            

            foreach ($payrolls as $payroll) {
                if (empty($payroll->details['payroll_item']) 
                    && empty($payroll->details['adjustments'])) {
                    continue;
                }

                if(!empty($payroll->details['adjustments'])) {
                    foreach ($payroll->details['adjustments'] as $adjustment) {
                        if($adjustment['add_less'] == 2){
                            $payrollItemName = $adjustment['payroll_item_name'] ?? 'Unknown';
                            
                            $entry = [
                                'id' => $adjustment['id'] ?? null,
                                'employee_id' => $employee->employee_id,
                                'employee_name' => "{$employee->lastname}, {$employee->firstname}",
                                'dealer' => $employee->dealership->name,
                                'amount' => $adjustment['amount'] ?? 0,
                                'date_from' => $adjustment['date_from'] ?? null,
                                'date_to' => $adjustment['date_to'] ?? null,
                                'add_less' => $adjustment['add_less'] ?? null,
                                'payroll_item_id' => $adjustment['payroll_item_id'] ?? null,
                            ];

                            $groupedPayrolls[$payrollItemName]['items'][] = $entry;

                            if (!isset($groupedPayrolls[$payrollItemName]['total'])) {
                                $groupedPayrolls[$payrollItemName]['total'] = 0;
                            }
                            $groupedPayrolls[$payrollItemName]['total'] += $entry['amount'];
                        }
                    }
                }

                if(!empty($payroll->details['payroll_item'])) {
                    foreach ($payroll->details['payroll_item'] as $adjustment) {

                        if (!isset($adjustment['payroll_item']['add_less']) || $adjustment['payroll_item']['add_less'] != 1 || $adjustment['payroll_item']['add_less'] != NULL) {
                            continue; // Skip if taxable is missing or not 1
                        }

                        $payrollItemName = $adjustment['payroll_item_name'] ?? 'Unknown';

                        // Prepare the data entry
                        $entry = [
                            'id' => $adjustment['id'] ?? null,
                            'employee_id' => $employee->employee_id,
                            'employee_name' => "{$employee->lastname}, {$employee->firstname}",
                            'dealer' => $employee->dealership->name,
                            'amount' => $adjustment['amount'] ?? 0,
                            'date_from' => $adjustment['date_from'] ?? null,
                            'date_to' => $adjustment['date_to'] ?? null,
                            'add_less' => $adjustment['add_less'] ?? null,
                            'payroll_item_id' => $adjustment['payroll_item_id'] ?? null,
                        ];

                        // Group by payroll_item_name
                        $groupedPayrolls[$payrollItemName][] = $entry;
                    }
                }
            }
        }

        // Display the grouped payroll data
        // echo '<pre>';
        // print_r(['data' => $groupedPayrolls]);
        // echo '</pre>';
        
        // dd($groupedPayrolls);
        return response()->json(['attendance' => $groupedPayrolls]);
    }

    public function generateEmployeeAdjustments(Request $request)
    {
        $payroll = Payroll::where('company_id', $request->company_id)
            ->where('payroll_date', $request->cutoff_id);

        if ($request->dealer_id > 0) {
            $payroll = $payroll->where('dealership_id', $request->dealer_id);
        }

        $payroll = $payroll->first();

        // dd($payroll);

        // $payroll = Payroll::where('reference_no', $request->reference_no)
        //     ->first();
        
        if ($payroll) {
            $start_date = $payroll->cut_off_start;
            $end_date = $payroll->cut_off_end;
        } else {
            return response()->json([
                'status' => false,
                'message' => 'No Cut-Off Date',
            ], Response::HTTP_INTERNAL_SERVER_ERROR);
        }

        $employee_ids = PayrollSummary::where('company_id', $request->company_id)
                ->where('payroll_date', $request->cutoff_id);

        if ($request->dealer_id > 0) {
            $employee_ids = $employee_ids->where('dealership_id', $request->dealer_id);
        }

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

        $employees = Employee::with([
                        'salary_and_wages' => function ($query) { $query->select('employee_id', 'payroll_rate'); },
                        'company' => function ($query) { $query->select('id', 'name'); },
                        'dealership' => function ($query) { $query->select('id', 'name'); },
                    ])
                    ->whereIn('employee_id', $employee_ids)
                    ->select('employee_id', 'firstname', 'lastname', 'work_location_id', 'company_id', 'dealer_id')
                    ->orderBy('lastname');

        $filters = [
            'dealer_id' => 'dealer_id',
            'division_id' => 'division_id',
            'department_id' => 'department_id',
            'position_id' => 'position_id'
        ]; 
        
        foreach ($filters as $column => $param) {
            if ($request->$param > 0) {
                $employees = $employees->where($column, $request->$param);
            }
        }

        $employees = $employees->get();

        $data = [];

        if($request->employee_id){
            $employees = Employee::whereIn('employee_id', $request->employee_id)->select('employee_id', 'firstname', 'lastname', 'work_location_id')->orderBy('lastname')->get();
        }

        $groupedPayrolls = [];

        // dd($employees);
        foreach ($employees as $employee) {
            $payrolls = PayrollSummary::where('employee_id', $employee->employee_id)
                ->whereDate('payroll_date', $request->cutoff_id)
                ->get();

            foreach ($payrolls as $payroll) {
                if (!isset($payroll->details['adjustments']) || !is_array($payroll->details['adjustments'])) {
                    continue;
                }

                foreach ($payroll->details['adjustments'] as $adjustment) {
                    if($adjustment['add_less'] == '' || $adjustment['add_less'] == null){
                        $payrollItemName = $adjustment['payroll_item_name'] ?? 'Unknown';
                        
                        $entry = [
                            'id' => $adjustment['id'] ?? null,
                            'employee_id' => $employee->employee_id,
                            'employee_name' => "{$employee->lastname}, {$employee->firstname}",
                            'dealer' => $employee->dealership->name,
                            'amount' => $adjustment['amount'] ?? 0,
                            'date_from' => $adjustment['date_from'] ?? null,
                            'date_to' => $adjustment['date_to'] ?? null,
                            'add_less' => $adjustment['add_less'] ?? null,
                            'payroll_item_id' => $adjustment['payroll_item_id'] ?? null,
                        ];

                        $groupedPayrolls[$payrollItemName]['items'][] = $entry;

                        if (!isset($groupedPayrolls[$payrollItemName]['total'])) {
                            $groupedPayrolls[$payrollItemName]['total'] = 0;
                        }
                        $groupedPayrolls[$payrollItemName]['total'] += $entry['amount'];
                    }
                }
            }
        }
        
        if($request->payroll_item_id){
            $groupedPayrolls = array_filter($groupedPayrolls, function($payrollItemGroup) use ($request) {
                // For each loan type group, check if any of its items have a matching loan_id
                foreach ($payrollItemGroup['items'] as $item) {
                    if (in_array($item['payroll_item_id'], $request['payroll_item_id'])) {
                        return true; // Keep this loan type if at least one item matches
                    }
                }
                return false; // Remove this loan type if no items match
            });
        }

        // dd($groupedPayrolls);
        return response()->json($groupedPayrolls);
    }

    public function generateEmployeeLoans(Request $request)
    {
        $payroll = Payroll::where('company_id', $request->company_id)
            ->where('payroll_date', $request->cutoff_id);

        if ($request->dealer_id > 0) {
            $payroll = $payroll->where('dealership_id', $request->dealer_id);
        }

        $payroll = $payroll->first();

        if ($payroll) {
            $start_date = $payroll->cut_off_start;
            $end_date = $payroll->cut_off_end;
        } else {
            return response()->json([
                'status' => false,
                'message' => 'No Cut-Off Date',
            ], Response::HTTP_INTERNAL_SERVER_ERROR);
        }

        $employee_ids = PayrollSummary::where('company_id', $request->company_id)
                ->where('payroll_date', $request->cutoff_id);

        if ($request->dealer_id > 0) {
            $employee_ids = $employee_ids->where('dealership_id', $request->dealer_id);
        }

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

        $employees = Employee::with([
                        'salary_and_wages' => function ($query) { $query->select('employee_id', 'payroll_rate'); },
                        'company' => function ($query) { $query->select('id', 'name'); },
                        'dealership' => function ($query) { $query->select('id', 'name'); },
                    ])
                    ->whereIn('employee_id', $employee_ids)
                    ->select('employee_id', 'firstname', 'lastname', 'work_location_id', 'company_id', 'dealer_id')
                    ->orderBy('lastname');

        $filters = [
            'dealer_id' => 'dealer_id',
            'division_id' => 'division_id',
            'department_id' => 'department_id',
            'position_id' => 'position_id'
        ]; 
        
        foreach ($filters as $column => $param) {
            if ($request->$param > 0) {
                $employees = $employees->where($column, $request->$param);
            }
        }

        $employees = $employees->get();
        
        $data = [];

        if($request->employee_id){
            $employees = Employee::whereIn('employee_id', $request->employee_id)->select('employee_id', 'firstname', 'lastname', 'work_location_id')->orderBy('lastname')->get();
        }

        $groupedPayrolls = [];

        foreach ($employees as $employee) {
            $payrolls = PayrollSummary::where('employee_id', $employee->employee_id)
                ->whereDate('payroll_date', $request->cutoff_id)
                ->get();

            foreach ($payrolls as $payroll) {
                if (!isset($payroll->details['loans']) || !is_array($payroll->details['loans'])) {
                    continue;
                }

                foreach ($payroll->details['payment_contributions'] as $payment_contributions) {
                    $payrollItemName = $payment_contributions['contribution_name'] ?? 'Unknown';

                    // Prepare the data entry
                    $entry = [
                        'id' => $payment_contributions['id'] ?? null,
                        'employee_id' => $employee->employee_id,
                        'employee_name' => "{$employee->lastname}, {$employee->firstname}",
                        'dealer' => $employee->dealership->name,
                        'amount' => $payment_contributions['amount'] ?? 0,
                        'date_from' => $payment_contributions['date_from'] ?? null,
                        'date_to' => $payment_contributions['date_to'] ?? null,
                        'loan_id' => $payment_contributions['contribution_id'] ?? null
                    ];

                    $groupedPayrolls[$payrollItemName]['items'][] = $entry;

                    // Add to total amount for this payroll_item_name
                    if (!isset($groupedPayrolls[$payrollItemName]['total'])) {
                        $groupedPayrolls[$payrollItemName]['total'] = 0;
                    }
                    $groupedPayrolls[$payrollItemName]['total'] += $entry['amount'];
                }

                foreach ($payroll->details['loans'] as $adjustment) {
                    $payrollItemName = $adjustment['loan_type_name'] ?? 'Unknown';

                    // Prepare the data entry
                    $entry = [
                        'id' => $adjustment['id'] ?? null,
                        'employee_id' => $employee->employee_id,
                        'employee_name' => "{$employee->lastname}, {$employee->firstname}",
                        'dealer' => $employee->dealership->name,
                        'amount' => $adjustment['amount'] ?? 0,
                        'date_from' => $adjustment['date_from'] ?? null,
                        'date_to' => $adjustment['date_to'] ?? null,
                        'loan_id' => $adjustment['loan_type_id'] ?? null
                    ];

                    $groupedPayrolls[$payrollItemName]['items'][] = $entry;

                    // Add to total amount for this payroll_item_name
                    if (!isset($groupedPayrolls[$payrollItemName]['total'])) {
                        $groupedPayrolls[$payrollItemName]['total'] = 0;
                    }
                    $groupedPayrolls[$payrollItemName]['total'] += $entry['amount'];
                }
            }
        }
        if($request->loan_id){
            $groupedPayrolls = array_filter($groupedPayrolls, function($loanTypeGroup) use ($request) {
                // For each loan type group, check if any of its items have a matching loan_id
                foreach ($loanTypeGroup['items'] as $item) {
                    if (in_array($item['loan_id'], $request['loan_id'])) {
                        return true; // Keep this loan type if at least one item matches
                    }
                }
                return false; // Remove this loan type if no items match
            });
        }

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

    public function generatePayrollSummary(Request $request)
    {
        $payroll = Payroll::where('company_id', $request->company_id)
            ->where('payroll_date', $request->cutoff_id);

        if ($request->dealer_id > 0) {
            $payroll = $payroll->where('dealership_id', $request->dealer_id);
        }

        $payroll = $payroll->first();

        if ($payroll) {
            $start_date = $payroll->cut_off_start;
            $end_date = $payroll->cut_off_end;
        } else {
            return response()->json([
                'status' => false,
                'message' => 'No Cut-Off Date',
            ], Response::HTTP_INTERNAL_SERVER_ERROR);
        }

        $employee_ids = PayrollSummary::where('company_id', $request->company_id)
                ->where('payroll_date', $request->cutoff_id);

        if ($request->dealer_id > 0) {
            $employee_ids = $employee_ids->where('dealership_id', $request->dealer_id);
        }

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

        $employees = Employee::with([
                        'salary_and_wages' => function ($query) { $query->select('employee_id', 'payroll_rate', 'tax_application'); },
                        'company' => function ($query) { $query->select('id', 'name'); },
                        'dealership' => function ($query) { $query->select('id', 'name'); },
                        'department' => function ($query) { $query->select('id', 'name'); },
                        'position' => function ($query) { $query->select('id', 'name'); },
                        'personal_information' => function ($query) { $query->select('employee_id', 'tin'); },
                    ])
                    ->whereIn('employee_id', $employee_ids)
                    ->select('employee_id', 'firstname', 'lastname', 'work_location_id', 'company_id', 'dealer_id', 'department_id', 'position_id')
                    ->orderBy('lastname');

        $filters = [
            'dealer_id' => 'dealer_id',
            'division_id' => 'division_id',
            'department_id' => 'department_id',
            'position_id' => 'position_id'
        ]; 
        
        foreach ($filters as $column => $param) {
            if ($request->$param > 0) {
                $employees = $employees->where($column, $request->$param);
            }
        }

        
        $data = [];

        if($request->employee_id){
            $employees = $employees->whereIn('employee_id', $request->employee_id)->orderBy('lastname');
        }

        $employees = $employees->get();

        $attendance_posting_ref = AttendancePosting::whereDate('payroll_date', $request->cutoff_id)
            ->where('company_id', $request->company_id);

        if ($request->dealer_id > 0) {
            $attendance_posting_ref = $attendance_posting_ref->where('dealership_id', $request->dealer_id);
        }

        $attendance_posting_ref = $attendance_posting_ref->select('reference_no')->get();

        $groupedPayrolls = [];

        foreach ($employees as $employee) {
            $payrolls = PayrollSummary::where('employee_id', $employee->employee_id)
                ->whereDate('payroll_date', $request->cutoff_id)
                ->get();

            $attendance = AttendancePostingDtls::whereIn('reference_no', $attendance_posting_ref)
                ->where('employee_id', $employee->employee_id)
                ->get();

                $groupedPayrolls[] = [
                    'employee' => $employee,
                    'payrolls' => $payrolls,
                    'attendance' => $attendance,
                ];

        }

        // dd($groupedPayrolls);

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

    public function getHoursAndMinutes($minutes){
        $hours = floor($minutes / 60);
        $remainingMinutes = $minutes % 60;
    
        // Ensure remaining minutes are always two digits
        $formattedMinutes = str_pad($remainingMinutes, 2, '0', STR_PAD_LEFT);
    
        return $hours . '.' . $formattedMinutes;
    }

    public function getSaveGenerated(Request $request) {
        // dd($request->all());
        $user = Auth::user();

        $report_id = $request->report_id;

        $data = GeneratedReport::with('shared_reports')
            ->where('report_id', $report_id)
            ->where(function($query) use ($user) {
                $query->where('created_by', $user->employee_id)
                    ->orWhereHas('shared_reports', function($q) use ($user) {
                        $q->where('shared_to', $user->employee_id);
                    });
            })
            ->get();

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

    public function sendGeneratedReport(Request $request)
    {
        $employeeIds = explode(',', $request->employee_id);
        $employeeIds = array_map('trim', $employeeIds);
        $employeeIds = array_filter($employeeIds); // Remove empty values

        $auth_user = Auth::user();
        $request['sender'] = $auth_user->name;

        // dd($request->all());

        $users = User::whereIn('employee_id', $employeeIds)->get();

        foreach($users as $user){
            if($user->email == null || $user->email == ''){
                return response()->json([
                    'message' => 'Email address of '.$user->last_name.', '. $user->first_name.' is empty. Please contact the HR',
                    'status' => 'false',
                ], 422);
            }
        }

        try {
            if ($users->count() > 0) {
                Notification::send($users, new SendGeneratedReport($request));
            } else {
                // Handle case where no valid users found
                Log::warning('No valid users found for employee IDs: ' . $request->employee_id);
            }

            return response()->json([
                'message' => 'Record Successfully added!',
                'status' => 'success',
            ],201);

        } catch (Throwable $e) {
            DB::connection()->rollback();
            return response()->json([
                'status'  => false,
                'message' => 'Failed to sent email. Please contact the administrator',
                'data'    => $e->getMessage()
            ], 422);
        }
        
    }

    public function shareGeneratedReport(Request $request)
    {
        
        // dd($request->all());
        $employeeIds = explode(',', $request->employee_id);
        $employeeIds = array_map('trim', $employeeIds);
        $employeeIds = array_filter($employeeIds); // Remove empty values

        try{
            foreach($employeeIds as $id){
            
                $shareReport = SharedReport::create([
                    'report_id' => $request->report_id,
                    'shared_to' => $id,
                    'employee_id' => Auth::user()->employee_id,
                ]);
            }
        } catch (Throwable $e) {
            DB::connection()->rollback();
            return response()->json([
                'status'  => false,
                'message' => 'Failed to sent email. Please contact the administrator',
                'data'    => $e->getMessage()
            ], 422);
        }

        return response()->json([
            'message' => 'Record Successfully added!',
            'status' => 'success',
        ],201);
        
    }

    public function saveGeneratedReport(Request $request) 
    {
        $formData = $request->except(['attachment']);
    
        $attachment = '';

        // Handle CSV attachment
        if($request->hasFile('csv_file')){

            $image = $request->file('csv_file');
            $attachmentName = time() . '_' . $image->getClientOriginalName();
            $path = "save_reports/" . $attachmentName;
            if (Storage::disk('local')->put($path, file_get_contents($image))) {
                $attachment = $attachmentName;
            }
        }

        $user = Auth::user();
        $filters = Arr::except($request->all(), ['modules', 'columns', 'reportData']);
        DB::flushQueryLog();

        $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' => 'generateMrfReport',
            '17' => 'generateApplicantReport',
            '18' => 'generatePafReport',
            '19' => 'generateOvertimeCompanyReport',
            '20' => 'generateLateApprovalReport',
            'changeScheduleReport' => 'generateChangeScheduleReport',
            'cwsListReport' => 'generateCwsListReport',
            'changeRestDay' => 'generateChangeRestDay',
            'timeAdjustment' => 'generateTimeAdjustment',
            'employeeTaxApplication' => 'generateTaxApplicationReport',
            'wfhMonitoring' => 'generatewfhMonitoringReport',
        ];

        $module = $request->modules;
        
        $method = '';

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

        $queries = DB::getQueryLog();
        
        // Function to interpolate bindings into SQL with proper quoting
        $getCompleteQuery = function($query) {
            $sql = $query['query'];
            $bindings = $query['bindings'];
            
            foreach ($bindings as $binding) {
                if (is_string($binding)) {
                    // Properly escape and quote strings (including dates)
                    $binding = "'" . str_replace("'", "''", $binding) . "'";
                } elseif (is_null($binding)) {
                    $binding = 'NULL';
                } elseif (is_bool($binding)) {
                    $binding = $binding ? '1' : '0';
                } elseif (is_numeric($binding)) {
                    // Keep numbers as-is
                    $binding = $binding;
                } else {
                    // For any other type, convert to string and quote
                    $binding = "'" . str_replace("'", "''", (string)$binding) . "'";
                }
                
                // Replace the first occurrence of ? with the binding
                $sql = preg_replace('/\?/', $binding, $sql, 1);
            }
            
            return $sql;
        };
        
        // Process all queries to get complete SQL
        $completeQueries = array_map(function($query) use ($getCompleteQuery) {
            return [
                'original' => $query,
                'complete_sql' => $getCompleteQuery($query)
            ];
        }, $queries);

        // Get the actual data from the JSON response
        $data = $result->getData(true); // true parameter returns as array
        
        
        // dd([
        //     'parent_id' => $request->setData['parent_id'],
        //     'report_id' => $request->setData['id'],
        //     'report_name' => $request->report_name ?? 'Untitled Report', // Provide default if null
        //     'attachment' => $attachment, // Provide default if null
        //     'columns' => json_encode($request->columns),
        //     'filters' => json_encode($filters),
        //     'created_by' => $user->employee_id,
        //     'query' => !empty($completeQueries) ? end($completeQueries)['complete_sql'] : null
        // ]);

        $final = [
            'parent_id' => $request->reportData['id'],
            'report_id' => $request->reportData['id'],
            'report_name' => $request->report_name ?? 'Untitled Report', // Provide default if null
            'attachment' => $attachment, // Provide default if null
            'columns' => json_encode($request->columns),
            'filters' => json_encode($filters),
            'created_by' => $user->employee_id,
            'query' => !empty($completeQueries) ? Crypt::encrypt(end($completeQueries)['complete_sql']) : null
        ];

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

        
        try {
            $data = GeneratedReport::create($final);

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

            return response()->json([
                'message' => 'Record Successfully added!',
                'status' => 'success',
            ],201);

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

    public function generateManpowerList(Request $request)
    {
        DB::enableQueryLog();

        $employees = Employee::with('department:id,name', 'position:id,name,salary_grade', 'employment_type:id,name', 'personal_information:employee_id,birth_date,civil_status,gender', 'personal_information.civil_status:id,name')
            ->where('company_id', $request->company_id);

        if (($request->dealer_id != NULL || $request->dealer_id != '') && $request->dealer_id) {
            $employees->where('dealer_id', $request->dealer_id);
        }

        if ($request->department_id != NULL || $request->department_id != '') {
            $employees->where('department_id', $request->department_id);
        }

        if (!$request->resign_employee) {
            $cutoffDate = Carbon::create($request->year, $request->month)->endOfMonth();
            
            $employees->whereNull('date_resign')
                    ->where('date_hired', '<=', $cutoffDate);
        } else {
            $cutoffDate = Carbon::create($request->year, $request->month)->endOfMonth();
            
            $employees->where('date_resign', '>=', $cutoffDate)->where('date_hired', '<=', $cutoffDate);
        }

        $employees = $employees->get();

        // dd($employees);
        return response()->json($employees);
    }

    public function destroy(int $id)
    {
        $data = GeneratedReport::find($id);
        if(!$data)
        {
            return response()->json(['message' => "Record not found!"],204);
        }
        DB::connection()->beginTransaction();
        $data->delete();
        DB::connection()->commit();
        return response()->json(['message' => "Record successfully deleted!"],201);
    }

    public function generateBenefitsAndOtherIncome(Request $request)
    {
        $payroll = Payroll::where('company_id', $request->company_id)
            ->where('payroll_date', $request->cutoff_id);

        if ($request->dealer_id > 0) {
            $payroll = $payroll->where('dealership_id', $request->dealer_id);
        }

        $payroll = $payroll->first();

        if ($payroll) {
            $start_date = $payroll->cut_off_start;
            $end_date = $payroll->cut_off_end;
        } else {
            return response()->json([
                'status' => false,
                'message' => 'No Cut-Off Date',
            ], Response::HTTP_INTERNAL_SERVER_ERROR);
        }

        $employee_ids = PayrollSummary::where('company_id', $request->company_id)
                ->where('payroll_date', $request->cutoff_id);

        if ($request->dealer_id > 0) {
            $employee_ids = $employee_ids->where('dealership_id', $request->dealer_id);
        }

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

        $employees = Employee::with([
                        'salary_and_wages' => function ($query) { $query->select('employee_id', 'payroll_rate', 'tax_application'); },
                        'company' => function ($query) { $query->select('id', 'name'); },
                        'dealership' => function ($query) { $query->select('id', 'name'); },
                        'department' => function ($query) { $query->select('id', 'name'); },
                        'position' => function ($query) { $query->select('id', 'name'); },
                        'personal_information' => function ($query) { $query->select('employee_id', 'tin'); },
                    ])
                    ->whereIn('employee_id', $employee_ids)
                    ->select('employee_id', 'firstname', 'lastname', 'work_location_id', 'company_id', 'dealer_id', 'department_id', 'position_id')
                    ->orderBy('lastname');

        $filters = [
            'dealer_id' => 'dealer_id',
            'division_id' => 'division_id',
            'department_id' => 'department_id',
            'position_id' => 'position_id'
        ]; 
        
        foreach ($filters as $column => $param) {
            if ($request->$param > 0) {
                $employees = $employees->where($column, $request->$param);
            }
        }

        
        $data = [];

        if($request->employee_id){
            $employees = $employees->whereIn('employee_id', $request->employee_id)->orderBy('lastname');
        }

        $employees = $employees->get();

        $attendance_posting_ref = AttendancePosting::whereDate('payroll_date', $request->cutoff_id)
            ->where('company_id', $request->company_id);

        if ($request->dealer_id > 0) {
            $attendance_posting_ref = $attendance_posting_ref->where('dealership_id', $request->dealer_id);
        }

        $attendance_posting_ref = $attendance_posting_ref->select('reference_no')->get();

        $groupedPayrolls = [];

        foreach ($employees as $employee) {
            $payrolls = PayrollSummary::where('employee_id', $employee->employee_id)
                ->whereDate('payroll_date', $request->cutoff_id)
                ->get();

            $attendance = AttendancePostingDtls::whereIn('reference_no', $attendance_posting_ref)
                ->where('employee_id', $employee->employee_id)
                ->get();

                $groupedPayrolls[] = [
                    'employee' => $employee,
                    'payrolls' => $payrolls,
                ];

        }

        // dd($groupedPayrolls);

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