<?php

namespace App\Http\Controllers\Traits;

use App\Models\Payroll;
use App\Models\Fine;
use App\Models\Salary;
use App\Models\Adminsettings;
use App\Models\BaseModel;
use App\Models\EmpCompanyDetails;
use App\Models\EmpPersonalDetails;
use App\Models\EmployeeAttendance;
use App\Models\Overtime;
use App\Models\LeaveRequest;
use App\Models\LeavePackage;
use App\Models\LeaveType;
use App\Models\PublicHoliday;
use App\Models\RosterAssign;
use App\Models\RosterTemplate;
use Carbon\Carbon;
use Carbon\CarbonPeriod;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\Log;

trait PayrollTrait
{
    use HelperTrait;

    /**
     * Get employees for the current customer and workspace
     */
    protected function getEmployees()
    {
        $ids = $this->getCustomerAndWorkspaceIds();
        return EmpCompanyDetails::with('EmpPersonalDetails')
            ->where('del', 0)
            ->where('compeleted', 1)
            ->where('status', 1)
            ->where('customer_id', $ids['customer_id'])
            ->where('workspace_id', $ids['workspace_id'])
            ->get();
    }

    /**
     * Calculate scheduled working hours for an employee
     */
    protected function calculateScheduledWorkingHours($employeeId, $startDate, $endDate)
    {
        $ids = $this->getCustomerAndWorkspaceIds();
        $totalMinutes = 0;
        $period = new \DatePeriod(
            new \DateTime($startDate),
            new \DateInterval('P1D'),
            (new \DateTime($endDate))->modify('+1 day')
        );
        foreach ($period as $date) {
            $dateStr = $date->format('Y-m-d');
            $rosterAssign = RosterAssign::where('assign_to', $employeeId)
                ->where('customer_id', $ids['customer_id'])
                ->where('workspace_id', $ids['workspace_id'])
                ->where('schedule_date', $dateStr)
                ->first();

            if ($rosterAssign) {
                $roster = RosterTemplate::where('id', $rosterAssign->roster_template_id)
                    ->where('customer_id', $ids['customer_id'])
                    ->where('workspace_id', $ids['workspace_id'])
                    ->first();
                if ($roster) {
                    $minutes = $roster->working_hours;
                    $totalMinutes += max(0, $minutes);
                }
            }
        }
        return $totalMinutes;
    }

    /**
     * Generate payslip for selected employees
     */
    protected function generatePayslipsForEmployees($request)
    {
        $validator = $this->payrollGenerateValidationRequest($request);
        if ($validator->fails()) {
            return $this->handleValidationFailure($validator);
        }
        $employeeIds = $request->employee_ids;
        $month = $request->month;
        $baseDate = BaseModel::safeCarbonParse($month . '-01', 'PayrollTrait generatePayslipsForEmployees month');
        if (!($baseDate instanceof Carbon)) {
            return $this->error('Invalid month supplied. Please use format YYYY-MM.', 422);
        }
        $date = $baseDate->copy()->startOfMonth();
        $ids = $this->getCustomerAndWorkspaceIds();
        try {
            $startDate = $date->toDateString();
            $endDate = $date->copy()->endOfMonth()->toDateString();
            $employees = EmpCompanyDetails::whereIn('id', $employeeIds)
                ->where('customer_id', $ids['customer_id'])
                ->where('workspace_id', $ids['workspace_id'])
                ->get();
            if ($employees->isEmpty()) {
                return $this->error('No valid employees found', 404);
            }
            $createdCount = 0;
            $createdPayslips = [];
            foreach ($employees as $employee) {
                try {
                    $payslipData = $this->calculatePayslipData($employee, $startDate, $endDate, $date);
                    
                    // VALIDATION: Ensure payslip data is valid
                    if (!is_array($payslipData) || !isset($payslipData['calculated_salary'])) {
                        Log::error("Invalid payslip data for employee {$employee->id}");
                        continue;
                    }
                    
                    // Check if payroll already exists
                    $existingPayroll = Payroll::where('employee_id', $employee->id)
                        ->where('pay_year', $date->year)
                        ->where('pay_month', $date->month)
                        ->where('customer_id', $ids['customer_id'])
                        ->where('workspace_id', $ids['workspace_id'])
                        ->first();
                if (!$existingPayroll) {
                    $payrollData = [
                        'employee_id' => $employee->id,
                        'pay_year' => $date->year,
                        'pay_month' => $date->month,
                        'basic_salary' => $payslipData['basic_salary'],
                        'working_hours' => $payslipData['working_minutes'],
                        'hours_spent' => $payslipData['total_minutes'], // Only includes paid time (actual work + overtime + paid leave + holidays)
                        'actual_working_hours' => $payslipData['actual_minutes'],
                        'overtime_hours' => $payslipData['overtime_minutes'],
                        'calculated_salary' => $payslipData['calculated_salary'], // Calculated from paid time only
                        'status' => 2,
                        'customer_id' => $ids['customer_id'],
                        'workspace_id' => $ids['workspace_id'],
                        'created_by' => Auth::id(),
                    ];
                    
                    // Add paid/unpaid leave data if columns exist in Payroll table
                    if (isset($payslipData['paid_leave_minutes'])) {
                        $payrollData['paid_leave_minutes'] = $payslipData['paid_leave_minutes'];
                        $payrollData['unpaid_leave_minutes'] = $payslipData['unpaid_leave_minutes'];
                        $payrollData['paid_leave_days'] = $payslipData['paid_leave_days'];
                        $payrollData['unpaid_leave_days'] = $payslipData['unpaid_leave_days'];
                    }
                    
                    $newPayroll = Payroll::create($payrollData);
                    $createdPayslips[] = $newPayroll->id;
                    $createdCount++;
                    
                    Log::info("Successfully created payroll for employee {$employee->id}", [
                        'payroll_id' => $newPayroll->id,
                        'calculated_salary' => $payslipData['calculated_salary'],
                        'paid_leave_minutes' => $payslipData['paid_leave_minutes'] ?? 0,
                        'unpaid_leave_minutes' => $payslipData['unpaid_leave_minutes'] ?? 0
                    ]);
                } else {
                    Log::info("Payroll already exists for employee {$employee->id} for {$date->year}-{$date->month}");
                }
                } catch (\Exception $e) {
                    Log::error("Failed to generate payslip for employee {$employee->id}: " . $e->getMessage());
                    // Continue with next employee instead of stopping the entire process
                    continue;
                }
            }
            return $this->success([
                'created_count' => $createdCount,
                'total_selected' => count($employeeIds),
                'created_payslip_ids' => $createdPayslips
            ], 'Payslips generated successfully');
        } catch (\Exception $e) {
            Log::error('Payslip generation failed: ' . $e->getMessage());
            return $this->error('An error occurred during payslip generation: ' . $e->getMessage(), 500);
        }
    }

    /**
     * Calculate payslip data for an employee
     */
    protected function calculatePayslipData($employee, $startDate, $endDate, $date)
    {
        $ids = $this->getCustomerAndWorkspaceIds();
        // Get attendance records
        $attendances = EmployeeAttendance::where('employee_id', $employee->id)
            ->where('customer_id', $ids['customer_id'])
            ->where('workspace_id', $ids['workspace_id'])
            ->whereBetween('date', [$startDate, $endDate])
            ->get();
            // Get overtime records
            $overtimeRecords = Overtime::where('employee_id', $employee->id)
            ->where('status', 'approved')
            ->where('customer_id', $ids['customer_id'])
            ->where('workspace_id', $ids['workspace_id'])
            ->whereBetween('date', [$startDate, $endDate])
            ->get();
            $actualMinutes = 0;
            $overtimeMinutes = 0;
            $totalMinutes = 0;
            // Get salary information first for daily wage calculation
            $salary = Salary::where('employee_id', $employee->id)
            ->where('customer_id', $ids['customer_id'])
            ->where('workspace_id', $ids['workspace_id'])
            ->where(function ($query) use ($startDate, $endDate) {
                $query->where('from', '<=', \App\Models\BaseModel::safeCarbonParse($endDate, 'PayrollTrait calculatePayslipData endDate')->format('Y-m-d'))
                ->where('to', '>=', \App\Models\BaseModel::safeCarbonParse($startDate, 'PayrollTrait calculatePayslipData startDate')->format('Y-m-d'));
            })
            ->first();
        $basicSalary = $salary ? $salary->basic_salary : 0;
        $workingMinutes = $salary ? $this->calculateScheduledWorkingHours($employee->id, $startDate, $endDate) : 0;
        // Validation: Ensure we have valid salary data
        if (!$salary || $basicSalary <= 0) {
            throw new \Exception("No valid salary found for employee ID: {$employee->id}");
        }

        // Calculate daily wage for late fine calculation
        $workingDays = RosterAssign::where('assign_to', $employee->id)
            ->where('customer_id', $ids['customer_id'])
            ->where('workspace_id', $ids['workspace_id'])
            ->whereBetween('schedule_date', [$startDate, $endDate])
            ->count();
        $dailyWage = ($workingDays > 0) ? ($basicSalary / $workingDays) : 0;
        // Calculate actual working hours and handle late fines
        foreach ($attendances as $attendance) {
            if ($attendance->check_in && $attendance->check_out) {
                $minutes = $attendance->working_hours;
                $actualMinutes += $minutes;
                $totalMinutes += $minutes;
                // Check for late check-in and create fine if needed
                // $this->processLateFine($employee->id, $attendance, $dailyWage, $ids);
            }
        }
        // Calculate overtime hours
        foreach ($overtimeRecords as $overtime) {
            if ($overtime->working_hours) {
                $overtimeMinutes += $overtime->working_hours;
                $totalMinutes += $overtime->working_hours;
            }
        }
        // Handle approved leave requests
        $approvedLeaves = LeaveRequest::where('employee_id', $employee->id)
            ->where('status', 1) // Only approved
            ->where('customer_id', $ids['customer_id'])
            ->where('workspace_id', $ids['workspace_id'])
            ->where(function ($query) use ($startDate, $endDate) {
                $query->whereBetween('from', [$startDate, $endDate])
                    ->orWhereBetween('to', [$startDate, $endDate])
                    ->orWhere(function ($q) use ($startDate, $endDate) {
                        $q->where('from', '<', $startDate)
                            ->where('to', '>', $endDate);
                    });
            })
            ->get();
        
        // Initialize paid and unpaid leave tracking
        $paidLeaveMinutes = 0;
        $unpaidLeaveMinutes = 0;
        $paidLeaveDays = 0;
        $unpaidLeaveDays = 0;
        
        foreach ($approvedLeaves as $leave) {
            try {
                $leavePkg = LeavePackage::find($leave->leave_package_id);
                if (!$leavePkg) {
                    Log::warning("Leave package not found for leave ID: {$leave->id}, employee ID: {$employee->id}");
                    continue;
                }
                
                if (!$leavePkg->leave_type_id) {
                    Log::warning("Leave package {$leavePkg->id} has no leave_type_id, employee ID: {$employee->id}");
                    continue;
                }
                
                $leaveType = LeaveType::find($leavePkg->leave_type_id);
                if (!$leaveType) {
                    Log::warning("Leave type not found for leave_type_id: {$leavePkg->leave_type_id}, employee ID: {$employee->id}");
                    continue;
                }
                
                if (!$leaveType->leave_hours || $leaveType->leave_hours <= 0) {
                    Log::warning("Leave type {$leaveType->id} has invalid leave_hours: {$leaveType->leave_hours}, employee ID: {$employee->id}");
                    continue;
                }
                
                // Always use Carbon for date math!
                $leaveFrom = \App\Models\BaseModel::safeCarbonParse($leave->from, 'PayrollTrait leave from');
                $leaveTo = \App\Models\BaseModel::safeCarbonParse($leave->to, 'PayrollTrait leave to');
                $periodStart = \App\Models\BaseModel::safeCarbonParse($startDate, 'PayrollTrait period start');
                $periodEnd = \App\Models\BaseModel::safeCarbonParse($endDate, 'PayrollTrait period end');
                
                if (!($leaveFrom instanceof \Carbon\Carbon) || !($leaveTo instanceof \Carbon\Carbon)) {
                    Log::warning("Failed to parse leave dates for leave ID: {$leave->id}, employee ID: {$employee->id}");
                    continue;
                }
                
                $leaveStart = $leaveFrom->greaterThan($periodStart) ? $leaveFrom : $periodStart;
                $leaveEnd = $leaveTo->lessThan($periodEnd) ? $leaveTo : $periodEnd;
                $days = $leaveStart->diffInDays($leaveEnd) + 1;
                
                // Defensive: if leave period is outside the payslip period, skip
                if ($days <= 0) {
                    continue;
                }
                
                $leaveMinutes = $days * $leaveType->leave_hours * 60;
                
                // CRITICAL: Check if leave package is paid or unpaid based on is_paid column
                // Default to unpaid if is_paid is null or not set
                $isPaid = isset($leavePkg->is_paid) ? (int)$leavePkg->is_paid : 0;
                
                if ($isPaid === 1) {
                    // PAID LEAVE: Include in salary calculation AND track separately
                    $totalMinutes += $leaveMinutes; // This affects salary calculation
                    $paidLeaveMinutes += $leaveMinutes;
                    $paidLeaveDays += $days;
                    
                    Log::info("Processed PAID leave: {$days} days, {$leaveMinutes} minutes for employee {$employee->id}");
                } else {
                    // UNPAID LEAVE: Do NOT include in salary calculation, but track separately
                    // $totalMinutes is NOT increased, so employee doesn't get paid for this time
                    $unpaidLeaveMinutes += $leaveMinutes;
                    $unpaidLeaveDays += $days;
                    
                    Log::info("Processed UNPAID leave: {$days} days, {$leaveMinutes} minutes for employee {$employee->id}");
                }
                
            } catch (\Exception $e) {
                Log::error("Error processing leave ID: {$leave->id} for employee {$employee->id}: " . $e->getMessage());
                continue; // Skip this leave and continue with others
            }
        }
        // Handle public holidays
        $holidays = PublicHoliday::where('customer_id', $ids['customer_id'])
            ->where('workspace_id', $ids['workspace_id'])
            ->where(function ($query) use ($startDate, $endDate) {
                $query->whereBetween('from', [$startDate, $endDate])
                    ->orWhereBetween('to', [$startDate, $endDate])
                    ->orWhere(function ($q) use ($startDate, $endDate) {
                        $q->where('from', '<', $startDate)
                            ->where('to', '>', $endDate);
                    });
            })->get();
            $holidayDates = [];
            foreach ($holidays as $holiday) {
                $holidayFrom = BaseModel::safeCarbonParse($holiday->from, 'PayrollTrait calculatePayslipData holiday from');
                $holidayTo = BaseModel::safeCarbonParse($holiday->to, 'PayrollTrait calculatePayslipData holiday to');

                if (!($holidayFrom instanceof Carbon) || !($holidayTo instanceof Carbon)) {
                    Log::warning("Skipping holiday {$holiday->id} due to unparseable dates", [
                        'from' => $holiday->from,
                        'to' => $holiday->to,
                    ]);
                    continue;
                }

                $period = CarbonPeriod::create($holidayFrom, $holidayTo);
                foreach ($period as $datePeriod) {
                    $holidayDates[] = $datePeriod->format('Y-m-d');
                }
            }
            $holidayDates = array_unique($holidayDates);
            // For each holiday, if not already present in attendance, add 8 hours (480 minutes)
            foreach ($holidayDates as $holidayDate) {
                $wasPresent = $attendances->where('date', $holidayDate)
                ->whereNotNull('check_in')
                ->whereNotNull('check_out')
                ->count() > 0;
                if (!$wasPresent) {
                    $totalMinutes += 480; // 8 hours
                }
            }
            // Calculate per-minute rate and total salary
            // IMPORTANT: $totalMinutes includes ONLY paid time:
            // - Actual working hours (check-in/check-out)
            // - Overtime hours  
            // - PAID leave hours (is_paid = 1)
            // - Public holiday hours
            // UNPAID leave hours are excluded from salary calculation
            $perMinuteRate = $workingMinutes > 0 ? $basicSalary / $workingMinutes : 0;
            $calculatedSalary = round($perMinuteRate * $totalMinutes);
            
            // VERIFICATION: Log detailed calculation breakdown for transparency
            Log::info("Payroll calculation for employee {$employee->id}:", [
                'basic_salary' => $basicSalary,
                'working_minutes_scheduled' => $workingMinutes,
                'actual_minutes_worked' => $actualMinutes,
                'overtime_minutes' => $overtimeMinutes,
                'paid_leave_minutes' => $paidLeaveMinutes,
                'unpaid_leave_minutes' => $unpaidLeaveMinutes,
                'holiday_minutes' => $totalMinutes - $actualMinutes - $overtimeMinutes - $paidLeaveMinutes,
                'total_paid_minutes' => $totalMinutes,
                'per_minute_rate' => $perMinuteRate,
                'calculated_salary' => $calculatedSalary,
                'paid_leave_days' => $paidLeaveDays,
                'unpaid_leave_days' => $unpaidLeaveDays
            ]);
        return [
            'basic_salary' => $basicSalary,
            'working_minutes' => $workingMinutes,
            'total_minutes' => $totalMinutes,
            'actual_minutes' => $actualMinutes,
            'overtime_minutes' => $overtimeMinutes,
            'calculated_salary' => $calculatedSalary,
            // New paid/unpaid leave data (preserving existing keys above)
            'paid_leave_minutes' => $paidLeaveMinutes,
            'unpaid_leave_minutes' => $unpaidLeaveMinutes,
            'paid_leave_days' => $paidLeaveDays,
            'unpaid_leave_days' => $unpaidLeaveDays,
            'total_leave_minutes' => $paidLeaveMinutes + $unpaidLeaveMinutes,
            'total_leave_days' => $paidLeaveDays + $unpaidLeaveDays,
        ];
    }

    
    private function getLateFineConfiguration($ids)
    {
        $settings = Adminsettings::where('customer_id', $ids['customer_id'])
            ->where('workspace', $ids['workspace_id'])
            ->whereIn('key', [
                'late_fine_start_time',
                'late_fine_tier_1_end_time',
                'late_fine_tier_1_percentage', 
                'late_fine_tier_1_description',
                'late_fine_tier_2_end_time',
                'late_fine_tier_2_percentage',
                'late_fine_tier_2_description', 
                'late_fine_tier_3_end_time',
                'late_fine_tier_3_percentage',
                'late_fine_tier_3_description'
            ])
            ->pluck('value', 'key')
            ->toArray();

        // Return default configuration if no settings found
        if (empty($settings)) {
            $defaultConfig = config('constants.late_fine_config');
            return [
                'fine_start_time' => $defaultConfig['fine_start_time'],
                'fine_tiers' => $defaultConfig['fine_tiers'] ?? []
            ];
        }

        // Build dynamic configuration from database settings
        $dynamicConfig = [
            'fine_start_time' => $settings['late_fine_start_time'] ?? "",
            'fine_tiers' => []
        ];

        // Build tier configuration dynamically
        for ($i = 1; $i <= 3; $i++) {
            $endTimeKey = "late_fine_tier_{$i}_end_time";
            $percentageKey = "late_fine_tier_{$i}_percentage";
            $descriptionKey = "late_fine_tier_{$i}_description";

            if (isset($settings[$percentageKey])) {
                $dynamicConfig['fine_tiers']["tier_{$i}"] = [
                    'end_time' => $settings[$endTimeKey] ?? null,
                    'percentage' => (float)$settings[$percentageKey],
                    'description' => $settings[$descriptionKey] ?? "Tier {$i} fine"
                ];
            }
        }

        return $dynamicConfig;
    }

    /**
     * Process late fine for an employee attendance
     */
    private function processLateFine($employeeId, $attendance, $dailyWage, $ids)
    {
        // Check if fine already exists for this date
        $existingFine = Fine::where('employee_id', $employeeId)
            ->where('date', $attendance->date)
            ->where('type', 0) // 0 = Fine
            ->where('customer_id', $ids['customer_id'])
            ->where('workspace_id', $ids['workspace_id'])
            ->first();
        
        if (!$existingFine) {
            // Get dynamic late fine configuration from AdminSettings
            $lateFineConfig = $this->getLateFineConfiguration($ids);
            $fineStartTime = $lateFineConfig['fine_start_time'] ?? '';
            $fineTiers = $lateFineConfig['fine_tiers'] ?? [];
            
            // Check for late check-in
            // Ensure we have clean date values before concatenation
            $attendanceDate = is_string($attendance->date) ? $attendance->date : $attendance->date->format('Y-m-d');
            
            // Validate required data before parsing
            if (empty($attendance->check_in) || empty($fineStartTime)) {
                return; // Skip processing if essential time data is missing
            }
            
            $checkInTime = \App\Models\BaseModel::safeCarbonParse($attendanceDate . ' ' . $attendance->check_in, 'PayrollTrait processLateFine checkInTime');
            $fineStartDateTime = \App\Models\BaseModel::safeCarbonParse($attendanceDate . ' ' . $fineStartTime, 'PayrollTrait processLateFine fineStartDateTime');
            
            // Ensure both parsing operations succeeded before proceeding
            if (!($checkInTime instanceof \Carbon\Carbon) || !($fineStartDateTime instanceof \Carbon\Carbon)) {
                \Illuminate\Support\Facades\Log::warning("ProcessLateFine: Failed to parse date/time values", [
                    'attendance_date' => $attendanceDate,
                    'check_in' => $attendance->check_in,
                    'fine_start_time' => $fineStartTime,
                    'employee_id' => $employeeId
                ]);
                return; // Skip processing if parsing failed
            }
            
            // Only process if check-in is after fine start time
            if ($checkInTime->gt($fineStartDateTime)) {
                $finePercent = 0;
                $tierDescription = '';
                
                // Check each tier to determine the fine percentage
                foreach ($fineTiers as $tierKey => $tier) {
                    $tierEndTime = $tier['end_time'];
                    $tierPercentage = $tier['percentage'] ?? 0;
                    $tierDesc = $tier['description'] ?? $tierKey;
                    
                    if ($tierEndTime === null || $tierEndTime === '') {
                        // Last tier - any time after previous tier
                        $finePercent = $tierPercentage;
                        $tierDescription = $tierDesc;
                        break;
                    } else {
                        $tierEndDateTime = \App\Models\BaseModel::safeCarbonParse($attendanceDate . ' ' . $tierEndTime, 'PayrollTrait processLateFine tierEndDateTime');
                        
                        // Ensure tier end time parsing succeeded before using it
                        if ($tierEndDateTime instanceof \Carbon\Carbon) {
                            if ($checkInTime->lte($tierEndDateTime)) {
                                $finePercent = $tierPercentage;
                                $tierDescription = $tierDesc;
                                break;
                            }
                        } else {
                            // Log warning if tier end time parsing failed
                            \Illuminate\Support\Facades\Log::warning("ProcessLateFine: Failed to parse tier end time", [
                                'tier_key' => $tierKey,
                                'tier_end_time' => $tierEndTime,
                                'attendance_date' => $attendanceDate,
                                'employee_id' => $employeeId
                            ]);
                            // Continue to next tier if parsing failed
                            continue;
                        }
                    }
                }
                
                // Create fine if percentage is greater than 0
                if ($finePercent > 0) {
                    $fineAmount = round(($finePercent / 100) * $dailyWage, 2);
                    Fine::create([
                        'employee_id' => $employeeId,
                        'date' => $attendance->date,
                        'fine_amount' => $fineAmount,
                        'type' => 0, // 0 = Fine
                        'fine_reason' => "Late check-in: {$checkInTime->format('g:i A')}, {$finePercent}% of daily wage ({$tierDescription})",
                        'customer_id' => $ids['customer_id'],
                        'workspace_id' => $ids['workspace_id'],
                        'created_by' => Auth::id(),
                    ]);
                }
            }
        }
    }

    /**
     * Get current late fine settings for a customer (useful for frontend to display current config)
     */
    protected function getCurrentLateFineSettings()
    {
        $ids = $this->getCustomerAndWorkspaceIds();
        return $this->getLateFineConfiguration($ids);
    }

    /**
     * Get payroll details with comprehensive information
     */
    protected function getPayrollDetails($payrollId)
    {
        $ids = $this->getCustomerAndWorkspaceIds();
        $payroll = Payroll::with(['employee','employeCom'])->
            where('id', $payrollId)
            ->where('customer_id', $ids['customer_id'])
            ->where('workspace_id', $ids['workspace_id'])
            ->first();
        if (!$payroll) {
            return $this->error('Payroll not found', 404);
        }

        // Add your logic here, directly on $payroll
        $totalFines = Fine::where('employee_id', $payroll->employee_id)
            ->where('customer_id', $ids['customer_id'])
            ->where('workspace_id', $ids['workspace_id'])
            ->whereYear('date', $payroll->pay_year)
            ->whereMonth('date', $payroll->pay_month)
            ->where('type', 0)
            ->sum('fine_amount');
        $payroll->total_fines = $totalFines;
        // Convert minutes fields to hours
        $payroll->working_hours = round($payroll->working_hours / 60, 2);
        $payroll->hours_spent = round($payroll->hours_spent / 60, 2);
        $payroll->actual_working_hours = round(floatval($payroll->actual_working_hours) / 60, 2);
        $payroll->overtime_hours = round(floatval($payroll->overtime_hours) / 60, 2);

        // Calculate period dates
        $periodBase = BaseModel::safeCarbonParse(sprintf('%04d-%02d-01', $payroll->pay_year, $payroll->pay_month), 'PayrollTrait getPayrollDetails period base');
        if (!($periodBase instanceof Carbon)) {
            return $this->error('Invalid payroll period detected for this record.', 500);
        }
        $startDate = $periodBase->copy()->startOfMonth();
        $endDate = $periodBase->copy()->endOfMonth();
        // Get fines and bonuses
        $fines = Fine::where('employee_id', $payroll->employee_id)
            ->where('customer_id', $ids['customer_id'])
            ->where('workspace_id', $ids['workspace_id'])
            ->whereYear('date', $payroll->pay_year)
            ->whereMonth('date', $payroll->pay_month)
            ->orderBy('date', 'desc')
            ->get();
        // Get overtime records
        $overtimeRecords = Overtime::where('employee_id', $payroll->employee_id)
            ->where('status', 'approved')
            ->where('customer_id', $ids['customer_id'])
            ->where('workspace_id', $ids['workspace_id'])
            ->whereBetween('date', [$startDate->toDateString(), $endDate->toDateString()])
            ->orderBy('date', 'desc')
            ->get();
        // Get salary information
        $salary = Salary::where('employee_id', $payroll->employee_id)
            ->where('customer_id', $ids['customer_id'])
            ->where('workspace_id', $ids['workspace_id'])
            ->where(function ($query) use ($startDate, $endDate) {
                $query->where('from', '<=', $endDate)
                      ->where('to', '>=', $startDate);
            })
            ->first();
        // Get holidays for the period
        $holidays = PublicHoliday::where('customer_id', $ids['customer_id'])
            ->where('workspace_id', $ids['workspace_id'])
            ->where(function ($query) use ($startDate, $endDate) {
                $query->whereBetween('from', [$startDate->toDateString(), $endDate->toDateString()])
                    ->orWhereBetween('to', [$startDate->toDateString(), $endDate->toDateString()])
                    ->orWhere(function ($q) use ($startDate, $endDate) {
                        $q->where('from', '<', $startDate->toDateString())
                            ->where('to', '>', $endDate->toDateString());
                    });
            })->get();
        // Calculate holiday dates
        $holidayDates = [];
        foreach ($holidays as $holiday) {
            $holidayFrom = BaseModel::safeCarbonParse($holiday->from, 'PayrollTrait getPayrollDetails holiday from');
            $holidayTo = BaseModel::safeCarbonParse($holiday->to, 'PayrollTrait getPayrollDetails holiday to');

            if (!($holidayFrom instanceof Carbon) || !($holidayTo instanceof Carbon)) {
                Log::warning("Skipping holiday {$holiday->id} in payroll details due to unparseable dates", [
                    'from' => $holiday->from,
                    'to' => $holiday->to,
                ]);
                continue;
            }

            $clampedFrom = $holidayFrom->greaterThan($startDate) ? $holidayFrom->copy() : $startDate->copy();
            $clampedTo = $holidayTo->lessThan($endDate) ? $holidayTo->copy() : $endDate->copy();

            if ($clampedFrom->gt($clampedTo)) {
                continue;
            }

            $period = CarbonPeriod::create($clampedFrom, $clampedTo);
            foreach ($period as $datePeriod) {
                $holidayDates[] = $datePeriod->format('Y-m-d');
            }
        }
        $holidayDates = array_unique($holidayDates);
        // Get scheduled working days from roster
        $scheduledDays = RosterAssign::where('assign_to', $payroll->employee_id)
            ->where('customer_id', $ids['customer_id'])
            ->where('workspace_id', $ids['workspace_id'])
            ->whereBetween('schedule_date', [$startDate->toDateString(), $endDate->toDateString()])
            ->pluck('schedule_date')
            ->toArray();
        // Remove holidays from scheduled working days
        $actualWorkingDays = array_diff($scheduledDays, $holidayDates);
        $totalWorkingDays = count($actualWorkingDays);
        $totalHolidays = count($holidayDates);
        // Get attendance records
        $attendances = EmployeeAttendance::where('employee_id', $payroll->employee_id)
            ->where('customer_id', $ids['customer_id'])
            ->where('workspace_id', $ids['workspace_id'])
            ->whereBetween('date', [$startDate->toDateString(), $endDate->toDateString()])
            ->orderBy('date', 'desc')
            ->get();
        // Calculate present days (on actual working days)
        $presentDays = EmployeeAttendance::where('employee_id', $payroll->employee_id)
            ->where('customer_id', $ids['customer_id'])
            ->where('workspace_id', $ids['workspace_id'])
            ->whereIn('date', $actualWorkingDays)
            ->whereNotNull('check_in')
            ->whereNotNull('check_out')
            ->distinct('date')
            ->count('date');
        // Calculate absent days
        $absentDays = $totalWorkingDays - $presentDays;
        // Calculate attendance rate
        $attendanceRate = $totalWorkingDays > 0 ? round(($presentDays / $totalWorkingDays) * 100, 1) : 0;
        // Get approved leave requests and calculate leave days/hours
        $approvedLeaves = LeaveRequest::where('employee_id', $payroll->employee_id)
            ->where('status', 1) // Only approved
            ->where('customer_id', $ids['customer_id'])
            ->where('workspace_id', $ids['workspace_id'])
            ->where(function ($query) use ($startDate, $endDate) {
                $query->whereBetween('from', [$startDate->toDateString(), $endDate->toDateString()])
                    ->orWhereBetween('to', [$startDate->toDateString(), $endDate->toDateString()])
                    ->orWhere(function ($q) use ($startDate, $endDate) {
                        $q->where('from', '<', $startDate->toDateString())
                            ->where('to', '>', $endDate->toDateString());
                    });
            })
            ->get();
        
        // Initialize all leave counters
        $approvedLeave = 0; // Keep existing for backward compatibility
        $approvedLeaveHours = 0; // Keep existing for backward compatibility
        $paidLeaveDays = 0;
        $paidLeaveHours = 0;
        $unpaidLeaveDays = 0;
        $unpaidLeaveHours = 0;
        
        foreach ($approvedLeaves as $leave) {
            try {
                $leavePackage = LeavePackage::find($leave->leave_package_id);
                if (!$leavePackage) {
                    Log::warning("Leave package not found for leave ID: {$leave->id} in getPayrollDetails");
                    continue;
                }
                
                if (!$leavePackage->leave_type_id) {
                    Log::warning("Leave package {$leavePackage->id} has no leave_type_id in getPayrollDetails");
                    continue;
                }
                
                $leaveType = LeaveType::find($leavePackage->leave_type_id);
                if (!$leaveType || !$leaveType->leave_hours || $leaveType->leave_hours <= 0) {
                    Log::warning("Invalid leave type for leave_type_id: {$leavePackage->leave_type_id} in getPayrollDetails");
                    continue;
                }
                
                // Calculate overlap days between leave and period
                $leaveFromParsed = \App\Models\BaseModel::safeCarbonParse($leave->from, 'PayrollTrait getPayrollDetails leave from');
                $leaveToParsed = \App\Models\BaseModel::safeCarbonParse($leave->to, 'PayrollTrait getPayrollDetails leave to');
                
                if (!($leaveFromParsed instanceof \Carbon\Carbon) || !($leaveToParsed instanceof \Carbon\Carbon)) {
                    Log::warning("Failed to parse leave dates for leave ID: {$leave->id} in getPayrollDetails");
                    continue;
                }
                
                $from = $leaveFromParsed->greaterThan($startDate) ? $leaveFromParsed : $startDate;
                $to = $leaveToParsed->lessThan($endDate) ? $leaveToParsed : $endDate;
                $days = $from->diffInDays($to) + 1;
                
                if ($days <= 0) {
                    continue; // Leave outside the period
                }
                
                $hours = $days * $leaveType->leave_hours;
                
                // Keep existing totals for backward compatibility
                $approvedLeave += $days;
                $approvedLeaveHours += $hours;
                
                // Separate paid and unpaid leave with safe checking
                $isPaid = isset($leavePackage->is_paid) ? (int)$leavePackage->is_paid : 0;
                
                if ($isPaid === 1) {
                    $paidLeaveDays += $days;
                    $paidLeaveHours += $hours;
                } else {
                    $unpaidLeaveDays += $days;
                    $unpaidLeaveHours += $hours;
                }
                
            } catch (\Exception $e) {
                Log::error("Error processing leave ID: {$leave->id} in getPayrollDetails: " . $e->getMessage());
                continue; // Skip this leave and continue
            }
        }
        // Calculate totals
        $totalFines = $fines->where('type', 0)->sum('fine_amount');
        $totalBonuses = $fines->where('type', 1)->sum('fine_amount');
        $adjustedSalary = $payroll->calculated_salary - $totalFines + $totalBonuses;
        return [
            'payroll' => $payroll,
            'fines' => $fines,
            'attendances' => $attendances,
            'overtime_records' => $overtimeRecords,
            'salary' => $salary,
            'holidays' => $holidays,
            'approved_leaves' => $approvedLeaves,
            'totals' => [
                'total_fines' => $totalFines,
                'total_bonuses' => $totalBonuses,
                'adjusted_salary' => $adjustedSalary,
            ],
            'attendance_stats' => [
                'total_working_days' => $totalWorkingDays,
                'total_holidays' => $totalHolidays,
                'present_days' => $presentDays,
                'absent_days' => $absentDays,
                'attendance_rate' => $attendanceRate,
                'approved_leave' => $approvedLeave, // Keep existing for backward compatibility
                'approved_leave_hours' => $approvedLeaveHours, // Keep existing for backward compatibility
                // New paid/unpaid leave breakdown
                'paid_leave_days' => $paidLeaveDays,
                'paid_leave_hours' => $paidLeaveHours,
                'unpaid_leave_days' => $unpaidLeaveDays,
                'unpaid_leave_hours' => $unpaidLeaveHours,
                'total_leave_days' => $paidLeaveDays + $unpaidLeaveDays,
                'total_leave_hours' => $paidLeaveHours + $unpaidLeaveHours,
            ]
        ];
    }

    /**
     * Send payslip email
     */
    protected function sendPayslipEmail($payrollId)
    {
        $payroll = Payroll::find($payrollId);
        if (!$payroll || !$payroll->employee) {
            return $this->error('Payroll or employee not found', 404);
        }
        $email = $payroll->employeCom->employee_email ?? $payroll->employeCom->email ?? null;
        if (!$email) {
            return $this->error('No email address found for this employee', 400);
        }
        try {
            $subject = 'Payslip Receipt - ' . date('M Y', mktime(0, 0, 0, $payroll->pay_month, 1, $payroll->pay_year));
            $params = [
                "subject" => $subject . " | " . env("APP_NAME"),
                "to" => $email,
                "msg" => view("Emails.payslip_receipt", [
                    'subject' => $subject,
                    'payroll' => $payroll,
                    'employee' => $payroll->employee,
                    'employeeCom' => $payroll->employeeCom,
                    'pdf_path' => $payroll->pdf_path,
                    'pay_month' => $payroll->pay_month,
                    'pay_year' => $payroll->pay_year,
                    'calculated_salary' => $payroll->calculated_salary,
                    'basic_salary' => $payroll->basic_salary,
                    'working_hours' => $payroll->working_hours,
                    'hours_spent' => $payroll->hours_spent,
                    'overtime_hours' => $payroll->overtime_hours,
                    'actual_working_hours' => $payroll->actual_working_hours,
                ])->render(),
            ];
            $emailSent = $this->SendInstantEmail($params);
            return $this->message('Payslip sent successfully to ' . $email);
        } catch (\Exception $e) {
            Log::error('Email sending failed: ' . $e->getMessage());
            return $this->error('Failed to send email: ' . $e->getMessage(), 500);
        }
    }
}
