<?php

namespace App\Controllers\Salary;

use App\Controllers\Controller;
use App\Models\SalaryEmployee;
use App\Models\SalaryDeduction;
use App\Models\SalaryApprovalLog;
use App\Models\EmployeeDismissal; // Add this import
use App\Services\Salary\DeductionRuleService;
use App\Services\Salary\DismissalService; // Add this import
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use App\Traits\ApprovalPeriodTrait;
use Illuminate\Validation\ValidationException;
use Illuminate\Database\Eloquent\ModelNotFoundException;
use Exception;
use PDOException;
use Throwable;

class SalaryDeductionController extends Controller
{
    use ApprovalPeriodTrait;

    protected $deductionRuleService;

    // Custom error codes
    const ERROR_VALIDATION = 422;
    const ERROR_NOT_FOUND = 404;
    const ERROR_FORBIDDEN = 403;
    const ERROR_CONFLICT = 409;
    const ERROR_SERVER = 500;
    const ERROR_UNAUTHORIZED = 401;

    public function __construct(DeductionRuleService $deductionRuleService)
    {
        $this->deductionRuleService = $deductionRuleService;
    }

    public function index(Request $request)
    {
        try {
            $query = SalaryDeduction::with('employee');
            
            // Apply filters with validation
            if ($request->filled('search')) {
                $searchTerm = trim($request->search);
                if (strlen($searchTerm) < 2) {
                    Log::warning('Search term too short', ['search_term' => $searchTerm]);
                }
                $query->where(function($q) use ($searchTerm) {
                    $q->where('reason', 'like', '%' . $searchTerm . '%')
                    ->orWhereHas('employee', function($emp) use ($searchTerm) {
                        $emp->where('name', 'like', '%' . $searchTerm . '%');
                    });
                });
            }
            
            if ($request->filled('status')) {
                $validStatuses = ['pending', 'applied', 'cancelled', 'rejected', 'pending_dismissal', 'completed', 'dismissed'];
                if (!in_array($request->status, $validStatuses)) {
                    Log::warning('Invalid status filter', ['status' => $request->status]);
                } else {
                    $query->where('status', $request->status);
                }
            }
            
            if ($request->filled('type')) {
                $validTypes = ['penalty', 'loan', 'advance_recovery', 'loss', 'other'];
                if (!in_array($request->type, $validTypes)) {
                    Log::warning('Invalid type filter', ['type' => $request->type]);
                } else {
                    $query->where('type', $request->type);
                }
            }
            
            if ($request->filled('employee_id')) {
                if (!is_numeric($request->employee_id)) {
                    Log::warning('Invalid employee_id filter', ['employee_id' => $request->employee_id]);
                } else {
                    $query->where('employee_id', $request->employee_id);
                }
            }
            
            if ($request->filled('from_date')) {
                try {
                    $fromDate = date('Y-m-d', strtotime($request->from_date));
                    $query->whereDate('deduction_date', '>=', $fromDate);
                } catch (Exception $e) {
                    Log::error('Invalid from_date format', ['from_date' => $request->from_date, 'error' => $e->getMessage()]);
                }
            }
            
            if ($request->filled('to_date')) {
                try {
                    $toDate = date('Y-m-d', strtotime($request->to_date));
                    $query->whereDate('deduction_date', '<=', $toDate);
                } catch (Exception $e) {
                    Log::error('Invalid to_date format', ['to_date' => $request->to_date, 'error' => $e->getMessage()]);
                }
            }
            
            $deductions = $query->orderBy('created_at', 'desc')->paginate(15);
            
            // Calculate statistics with error handling
            try {
                $totalDeductions = SalaryDeduction::where('status', 'applied')->sum('amount') ?? 0;
                $pendingApprovals = SalaryDeduction::where('status', 'pending')->count() ?? 0;
                $pendingDismissals = SalaryDeduction::where('status', 'pending_dismissal')->count() ?? 0;
                $monthlyTotal = SalaryDeduction::where('status', 'applied')
                    ->whereMonth('deduction_date', now()->month)
                    ->sum('amount') ?? 0;
                $affectedEmployees = SalaryDeduction::where('status', 'applied')
                    ->distinct('employee_id')
                    ->count('employee_id') ?? 0;
            } catch (PDOException $e) {
                Log::error('Database error calculating statistics', [
                    'error' => $e->getMessage(),
                    'sql_state' => $e->getCode()
                ]);
                // Set default values
                $totalDeductions = $pendingApprovals = $pendingDismissals = $monthlyTotal = $affectedEmployees = 0;
            }
            
            $employees = SalaryEmployee::orderBy('name')->get();
            
            // Get approval info with error handling
            try {
                $approvalInfo = $this->getApprovalInfo();
            } catch (Exception $e) {
                Log::error('Failed to get approval info', ['error' => $e->getMessage()]);
                $approvalInfo = ['can_submit' => false, 'message' => 'Approval info unavailable'];
            }
            
            return view('salary.deductions.index', compact(
                'deductions', 
                'employees', 
                'totalDeductions', 
                'pendingApprovals',
                'pendingDismissals', 
                'monthlyTotal', 
                'affectedEmployees',
                'approvalInfo'
            ));
            
        } catch (PDOException $e) {
            Log::critical('Database connection error in index', [
                'error' => $e->getMessage(),
                'trace' => $e->getTraceAsString()
            ]);
            return back()->with('error', 'Database connection issue. Please try again later.');
            
        } catch (Exception $e) {
            Log::error('Unexpected error in index', [
                'error' => $e->getMessage(),
                'trace' => $e->getTraceAsString()
            ]);
            return view('salary.deductions.index')->with('error', 'An unexpected error occurred. Please refresh the page.');
        }
    }
    
    public function create()
    {
        try {
            // Check if user can submit (always true for admin/director)
            try {
                $submitCheck = $this->canSubmit();
                if (!$submitCheck['can_submit']) {
                    return redirect()->route('salary.deductions.index')
                        ->with('error', $submitCheck['message']);
                }
            } catch (Exception $e) {
                Log::error('Permission check failed', ['error' => $e->getMessage()]);
                return redirect()->route('salary.deductions.index')
                    ->with('error', 'Unable to verify permissions. Please try again.');
            }
            
            $employees = SalaryEmployee::where('status', 'active')->get();
            
            try {
                $approvalInfo = $this->getApprovalInfo();
            } catch (Exception $e) {
                Log::error('Failed to get approval info in create', ['error' => $e->getMessage()]);
                $approvalInfo = ['can_submit' => false, 'message' => 'Approval info temporarily unavailable'];
            }
            
            if ($employees->isEmpty()) {
                return redirect()->route('salary.deductions.index')
                    ->with('warning', 'No active employees found. Please add employees before creating deductions.');
            }
            
            return view('salary.deductions.create', compact('employees', 'approvalInfo'));
            
        } catch (PDOException $e) {
            Log::error('Database error loading deduction form: ' . $e->getMessage());
            return redirect()->route('salary.deductions.index')
                ->with('error', 'Database error. Please try again later.');
                
        } catch (Exception $e) {
            Log::error('Error loading deduction creation form: ' . $e->getMessage(), [
                'trace' => $e->getTraceAsString()
            ]);
            
            return redirect()->route('salary.deductions.index')
                ->with('error', 'Failed to load deduction form. Please try again.');
        }
    }
    
    public function store(Request $request)
    {
        try {
            // Check if user can submit
            try {
                $submitCheck = $this->canSubmit();
                if (!$submitCheck['can_submit']) {
                    Log::warning('Unauthorized deduction creation attempt', [
                        'user_id' => Auth::id(),
                        'ip' => $request->ip()
                    ]);
                    
                    if ($request->expectsJson()) {
                        return response()->json([
                            'success' => false,
                            'message' => $submitCheck['message']
                        ], self::ERROR_FORBIDDEN);
                    }
                    return back()->with('error', $submitCheck['message']);
                }
            } catch (Exception $e) {
                Log::error('Permission validation failed', ['error' => $e->getMessage()]);
                if ($request->expectsJson()) {
                    return response()->json([
                        'success' => false,
                        'message' => 'Unable to verify permissions. Please try again.'
                    ], self::ERROR_SERVER);
                }
                return back()->with('error', 'Unable to verify permissions. Please try again.');
            }
            
            // Validate input with custom error messages
            $validated = $request->validate([
                'employee_id' => 'required|exists:salary_employees,id',
                'reason' => 'required|string|max:255',
                'amount' => 'required|numeric|min:0.01|max:1000000',
                'type' => 'required|in:penalty,loan,advance_recovery,loss,other',
                'deduction_date' => 'required|date|before_or_equal:today',
                'description' => 'nullable|string|max:1000',
            ], [
                'employee_id.required' => 'Please select an employee.',
                'employee_id.exists' => 'The selected employee does not exist.',
                'reason.required' => 'Please provide a reason for the deduction.',
                'reason.max' => 'Reason cannot exceed 255 characters.',
                'amount.required' => 'Please enter an amount.',
                'amount.numeric' => 'Amount must be a number.',
                'amount.min' => 'Amount must be at least 0.01.',
                'amount.max' => 'Amount cannot exceed 1,000,000.',
                'type.required' => 'Please select a deduction type.',
                'type.in' => 'Invalid deduction type selected.',
                'deduction_date.required' => 'Please select a deduction date.',
                'deduction_date.before_or_equal' => 'Deduction date cannot be in the future.',
                'description.max' => 'Description cannot exceed 1000 characters.',
            ]);
            
            // Check if employee exists
            try {
                $employee = SalaryEmployee::find($validated['employee_id']);
                if (!$employee) {
                    throw new ModelNotFoundException('Employee not found with ID: ' . $validated['employee_id']);
                }
            } catch (ModelNotFoundException $e) {
                Log::error('Employee not found', ['employee_id' => $validated['employee_id']]);
                if ($request->expectsJson()) {
                    return response()->json([
                        'success' => false,
                        'message' => 'Selected employee does not exist.'
                    ], self::ERROR_NOT_FOUND);
                }
                return back()->withInput()->with('error', 'Selected employee does not exist.');
            }
            
            // Check if employee is active
            if ($employee->status !== 'active') {
                Log::warning('Attempt to create deduction for inactive employee', [
                    'employee_id' => $employee->id,
                    'status' => $employee->status
                ]);
                if ($request->expectsJson()) {
                    return response()->json([
                        'success' => false,
                        'message' => 'Cannot create deduction for inactive or terminated employee.'
                    ], self::ERROR_CONFLICT);
                }
                return back()->withInput()->with('error', 'Cannot create deduction for inactive or terminated employee.');
            }
            
            // Check for duplicate pending deductions
            $existingPending = SalaryDeduction::where('employee_id', $validated['employee_id'])
                ->where('reason', $validated['reason'])
                ->whereIn('status', ['pending', 'pending_dismissal'])
                ->where('created_at', '>=', now()->subDays(7))
                ->first();
                
            if ($existingPending) {
                Log::warning('Duplicate deduction attempt', [
                    'employee_id' => $validated['employee_id'],
                    'existing_id' => $existingPending->id
                ]);
                if ($request->expectsJson()) {
                    return response()->json([
                        'success' => false,
                        'message' => 'A similar deduction for this employee is already pending approval.'
                    ], self::ERROR_CONFLICT);
                }
                return back()->withInput()->with('warning', 'A similar deduction for this employee is already pending approval.');
            }
            
            // ==============================================
            // UPDATED VALIDATION WITH USER ROLE CONSIDERATION
            // ==============================================
            // Validate deduction amount with user role consideration
            try {
                // Use validateDeductionAmount with user parameter
                $amountValidation = $this->deductionRuleService->validateDeductionAmount(
                    $validated['amount'], 
                    Auth::user()
                );
                
                // Check if amount requires super deduction
                if (isset($amountValidation['requires_super_deduction']) && $amountValidation['requires_super_deduction']) {
                    // Store the validated data in session for the super deduction form
                    session()->flash('super_deduction_data', $validated);
                    
                    if ($request->expectsJson()) {
                        return response()->json([
                            'success' => false,
                            'requires_super_deduction' => true,
                            'auto_approve' => $amountValidation['auto_approve'] ?? false,
                            'requires_director_approval' => $amountValidation['requires_director_approval'] ?? false,
                            'message' => $amountValidation['message'],
                            'redirect_url' => route('salary.super-deductions.create')
                        ], self::ERROR_VALIDATION);
                    }
                    
                    // For web requests, redirect to super deduction form
                    return redirect()->route('salary.super-deductions.create')
                        ->with('warning', $amountValidation['message'])
                        ->withInput($validated);
                }
                
                // If validation failed for other reasons
                if (!$amountValidation['valid']) {
                    throw new Exception($amountValidation['message']);
                }
                
            } catch (Exception $e) {
                Log::error('Amount validation failed', [
                    'amount' => $validated['amount'],
                    'error' => $e->getMessage()
                ]);
                if ($request->expectsJson()) {
                    return response()->json([
                        'success' => false,
                        'message' => $e->getMessage()
                    ], self::ERROR_VALIDATION);
                }
                return back()->withInput()->with('error', $e->getMessage());
            }
            // ==============================================
            // END OF UPDATED VALIDATION
            // ==============================================
            
            DB::beginTransaction();
            
            try {
                // Process deduction rules
                try {
                    $ruleResult = $this->deductionRuleService->processDeductionRules(
                        $validated['amount'],
                        $employee,
                        $validated['reason'],
                        $validated['type']
                    );
                } catch (Exception $e) {
                    Log::error('Rule processing failed', [
                        'error' => $e->getMessage(),
                        'employee_id' => $employee->id
                    ]);
                    throw new Exception('Failed to process deduction rules: ' . $e->getMessage());
                }
                
                // Create deduction record
                $deduction = SalaryDeduction::create([
                    'employee_id' => $validated['employee_id'],
                    'reason' => $validated['reason'],
                    'amount' => $validated['amount'],
                    'type' => $validated['type'],
                    'deduction_date' => $validated['deduction_date'],
                    'description' => $validated['description'] ?? null,
                    'status' => $ruleResult['requires_dismissal'] ? 'pending_dismissal' : 'pending',
                    'deduction_type' => $ruleResult['deduction_type'],
                    'number_of_installments' => $ruleResult['number_of_months'],
                    'installment_amount' => $ruleResult['deduction_per_month'],
                    'requires_dismissal' => $ruleResult['requires_dismissal'],
                    'message' => $ruleResult['message']
                ]);
                
                if (!$deduction) {
                    throw new Exception('Failed to create deduction record in database.');
                }
                
                // Create approval log for deduction
                $approvalLog = SalaryApprovalLog::create([
                    'approvable_type' => SalaryDeduction::class,
                    'approvable_id' => $deduction->id,
                    'user_id' => Auth::id(),
                    'action' => 'requested',
                    'comments' => "Deduction created for approval. Amount: KES " . number_format($validated['amount'], 2),
                ]);
                
                if (!$approvalLog) {
                    Log::warning('Failed to create approval log', ['deduction_id' => $deduction->id]);
                }
                
                // Check if employee exceeds cumulative deduction limit (for dismissal)
                try {
                    $canAddCheck = $this->deductionRuleService->canAddDeduction($employee->id, $validated['amount']);
                    
                    if (isset($canAddCheck['requires_dismissal']) && $canAddCheck['requires_dismissal']) {
                        // Create dismissal request using the service
                        $dismissalService = new \App\Services\Salary\DismissalService();
                        $dismissal = $dismissalService->createDismissalFromDeduction($employee, $deduction, $canAddCheck);
                        
                        // Create approval log for the dismissal
                        SalaryApprovalLog::create([
                            'approvable_type' => EmployeeDismissal::class,
                            'approvable_id' => $dismissal->id,
                            'user_id' => Auth::id(),
                            'action' => 'dismissal_initiated',
                            'comments' => "Dismissal automatically initiated due to excessive deductions. Total: KES " . number_format($canAddCheck['total_after_add'], 2)
                        ]);
                        
                        Log::info('Dismissal initiated for employee', [
                            'employee_id' => $employee->id,
                            'deduction_id' => $deduction->id,
                            'dismissal_id' => $dismissal->id
                        ]);
                    }
                } catch (Exception $e) {
                    Log::error('Error creating dismissal request', [
                        'employee_id' => $employee->id,
                        'error' => $e->getMessage()
                    ]);
                    // Don't rollback, just log the error
                }
                
                DB::commit();
                
                $role = $this->isDirector() ? 'Director' : ($this->isAdmin() ? 'Admin' : 'User');
                $message = $ruleResult['requires_dismissal'] 
                    ? "⚠️ Deduction created by {$role}. Due to the amount (≥ 30,000), this requires HR review and possible dismissal letter."
                    : "✓ Deduction created successfully by {$role} and pending approval. " . $ruleResult['message'];
                
                Log::info('Deduction created successfully', [
                    'deduction_id' => $deduction->id,
                    'employee_id' => $employee->id,
                    'amount' => $validated['amount'],
                    'user_id' => Auth::id()
                ]);
                
                // Return JSON response for AJAX requests
                if ($request->expectsJson()) {
                    return response()->json([
                        'success' => true,
                        'message' => $message,
                        'deduction' => [
                            'id' => $deduction->id,
                            'amount' => $deduction->amount,
                            'formatted_amount' => number_format($deduction->amount, 2),
                            'status' => $deduction->status,
                            'requires_dismissal' => $deduction->requires_dismissal
                        ]
                    ]);
                }
                
                return redirect()->route('salary.deductions.index')
                    ->with('success', $message);
                
            } catch (PDOException $e) {
                DB::rollBack();
                Log::critical('Database error during deduction creation', [
                    'error' => $e->getMessage(),
                    'sql_state' => $e->getCode(),
                    'trace' => $e->getTraceAsString()
                ]);
                
                if ($request->expectsJson()) {
                    return response()->json([
                        'success' => false,
                        'message' => 'Database error occurred while saving deduction. Please try again.'
                    ], self::ERROR_SERVER);
                }
                throw new Exception('Database error occurred while saving deduction. Please try again.');
                
            } catch (Exception $e) {
                DB::rollBack();
                throw $e;
            }
            
        } catch (ValidationException $e) {
            Log::warning('Validation failed in store', [
                'errors' => $e->errors(),
                'input' => array_keys($request->all())
            ]);
            
            if ($request->expectsJson()) {
                return response()->json([
                    'success' => false,
                    'message' => 'Validation failed',
                    'errors' => $e->errors()
                ], self::ERROR_VALIDATION);
            }
            return back()->withErrors($e->validator)->withInput();
            
        } catch (PDOException $e) {
            Log::critical('Database connection error in store', [
                'error' => $e->getMessage(),
                'trace' => $e->getTraceAsString()
            ]);
            
            if ($request->expectsJson()) {
                return response()->json([
                    'success' => false,
                    'message' => 'Database connection issue. Please try again later.'
                ], self::ERROR_SERVER);
            }
            return back()->withInput()->with('error', 'Database connection issue. Please try again later.');
            
        } catch (Exception $e) {
            Log::error('Failed to create deduction: ' . $e->getMessage(), [
                'request_data' => $request->except(['_token', 'password']),
                'trace' => $e->getTraceAsString()
            ]);
            
            if ($request->expectsJson()) {
                return response()->json([
                    'success' => false,
                    'message' => 'Failed to create deduction: ' . $e->getMessage()
                ], self::ERROR_SERVER);
            }
            
            return back()->withInput()->with('error', 'Failed to create deduction: ' . $e->getMessage());
        }
    }

    /**
     * Approve deduction with role-based restrictions
     * - Admin: Only between 28th-7th
     * - Director: Anytime
     */
    public function approve(Request $request, $id)
    {
        try {
            // Validate request for JSON
            if (!$request->expectsJson()) {
                return response()->json([
                    'success' => false,
                    'message' => 'Invalid request format. JSON expected.'
                ], self::ERROR_VALIDATION);
            }
            
            // Check approval permissions
            try {
                $approvalCheck = $this->canApprove();
                if (!$approvalCheck['can_approve']) {
                    Log::warning('Unauthorized approval attempt', [
                        'user_id' => Auth::id(),
                        'deduction_id' => $id
                    ]);
                    return response()->json([
                        'success' => false,
                        'message' => $approvalCheck['message']
                    ], self::ERROR_FORBIDDEN);
                }
            } catch (Exception $e) {
                Log::error('Permission check failed for approval', ['error' => $e->getMessage()]);
                return response()->json([
                    'success' => false,
                    'message' => 'Unable to verify permissions. Please try again.'
                ], self::ERROR_SERVER);
            }
            
            // Find deduction with retry logic
            try {
                $deduction = SalaryDeduction::findOrFail($id);
            } catch (ModelNotFoundException $e) {
                Log::warning('Deduction not found for approval', ['deduction_id' => $id]);
                return response()->json([
                    'success' => false,
                    'message' => 'Deduction not found. It may have been deleted.'
                ], self::ERROR_NOT_FOUND);
            }
            
            // Validate deduction status
            if ($deduction->status !== 'pending') {
                $statusMessage = $this->getStatusMessage($deduction->status);
                Log::warning('Invalid status for approval', [
                    'deduction_id' => $id,
                    'current_status' => $deduction->status
                ]);
                return response()->json([
                    'success' => false,
                    'message' => "This deduction cannot be approved. Current status: {$statusMessage}"
                ], self::ERROR_CONFLICT);
            }
            
            // Check if deduction is too old (older than 60 days)
            if ($deduction->created_at < now()->subDays(60)) {
                Log::warning('Approval attempted on old deduction', [
                    'deduction_id' => $id,
                    'created_at' => $deduction->created_at
                ]);
                return response()->json([
                    'success' => false,
                    'message' => 'This deduction is over 60 days old and cannot be approved. Please create a new deduction.'
                ], self::ERROR_CONFLICT);
            }
            
            // Check if employee still exists and is active
            try {
                $employee = SalaryEmployee::find($deduction->employee_id);
                if (!$employee) {
                    Log::error('Employee not found for deduction approval', [
                        'deduction_id' => $id,
                        'employee_id' => $deduction->employee_id
                    ]);
                    return response()->json([
                        'success' => false,
                        'message' => 'Cannot approve deduction. Employee record not found.'
                    ], self::ERROR_NOT_FOUND);
                }
            } catch (Exception $e) {
                Log::error('Error fetching employee for approval', [
                    'deduction_id' => $id,
                    'error' => $e->getMessage()
                ]);
                return response()->json([
                    'success' => false,
                    'message' => 'Failed to verify employee status.'
                ], self::ERROR_SERVER);
            }
            
            if ($employee->status !== 'active') {
                Log::warning('Approval attempted for inactive employee', [
                    'deduction_id' => $id,
                    'employee_id' => $employee->id,
                    'employee_status' => $employee->status
                ]);
                return response()->json([
                    'success' => false,
                    'message' => 'Cannot approve deduction for inactive or terminated employee.'
                ], self::ERROR_CONFLICT);
            }
            
            // Check for existing approved deductions that might cause double deduction
            $alreadyApplied = SalaryDeduction::where('employee_id', $deduction->employee_id)
                ->where('status', 'applied')
                ->where('deduction_date', '>=', now()->startOfMonth())
                ->where('deduction_date', '<=', now()->endOfMonth())
                ->exists();
                
            if ($alreadyApplied && $deduction->type === 'penalty') {
                Log::warning('Multiple penalties for same month', [
                    'employee_id' => $deduction->employee_id,
                    'deduction_id' => $id
                ]);
                // Allow but log warning
            }
            
            // Determine approval level
            try {
                $isDirector = $this->isDirector();
                $isAdmin = $this->isAdmin();
                $approvalLevel = $isDirector ? 'director' : ($isAdmin ? 'admin' : 'unknown');
            } catch (Exception $e) {
                Log::error('Error determining approval level', ['error' => $e->getMessage()]);
                $approvalLevel = 'unknown';
            }
            
            DB::beginTransaction();
            
            try {
                $updateData = [
                    'status' => 'applied',
                    'approved_by' => Auth::id(),
                    'approved_at' => now(),
                    'approval_level' => $approvalLevel
                ];
                
                $updated = $deduction->update($updateData);
                
                if (!$updated) {
                    throw new Exception('Failed to update deduction status in database.');
                }
                
                $approvalLog = SalaryApprovalLog::create([
                    'approvable_type' => SalaryDeduction::class,
                    'approvable_id' => $deduction->id,
                    'user_id' => Auth::id(),
                    'action' => 'approved',
                    'comments' => $request->input('comments', "Deduction approved by " . ucfirst($approvalLevel)),
                ]);
                
                if (!$approvalLog) {
                    Log::warning('Failed to create approval log for approved deduction', [
                        'deduction_id' => $deduction->id
                    ]);
                }
                
                DB::commit();
                
                Log::info('Deduction approved successfully', [
                    'deduction_id' => $deduction->id,
                    'employee_id' => $deduction->employee_id,
                    'amount' => $deduction->amount,
                    'approved_by' => Auth::id(),
                    'approval_level' => $approvalLevel
                ]);
                
                return response()->json([
                    'success' => true,
                    'message' => 'Deduction approved successfully and will be applied to next payment.',
                    'deduction' => $deduction,
                    'approval_level' => $approvalLevel
                ]);
                
            } catch (PDOException $e) {
                DB::rollBack();
                Log::critical('Database error during approval', [
                    'error' => $e->getMessage(),
                    'deduction_id' => $id,
                    'sql_state' => $e->getCode()
                ]);
                throw new Exception('Database error occurred while approving deduction. Please try again.');
                
            } catch (Exception $e) {
                DB::rollBack();
                throw $e;
            }
            
        } catch (Exception $e) {
            Log::error('Error approving deduction: ' . $e->getMessage(), [
                'deduction_id' => $id,
                'trace' => $e->getTraceAsString()
            ]);
            
            return response()->json([
                'success' => false,
                'message' => 'Failed to approve deduction: ' . $e->getMessage()
            ], self::ERROR_SERVER);
        }
    }

    /**
     * Create a dismissal request for employee who exceeds deduction limit
     */
    private function createDismissalRequest($employee, $deduction, $canAddCheck)
    {
        try {
            // Validate inputs
            if (!$employee || !$deduction) {
                throw new Exception('Invalid employee or deduction data for dismissal request.');
            }
            
            // Create dismissal request record
            Log::warning('Employee exceeds deduction limit - dismissal required', [
                'employee_id' => $employee->id,
                'employee_name' => $employee->name,
                'deduction_id' => $deduction->id,
                'deduction_amount' => $deduction->amount,
                'current_balance' => $canAddCheck['current_balance'] ?? 0,
                'total_after_add' => $canAddCheck['total_after_add'] ?? $deduction->amount,
                'action_required' => 'Immediate dismissal or full payment'
            ]);
            
            // Update employee status to flag for HR review
            try {
                $employee->update([
                    'needs_review' => true,
                    'review_reason' => 'Deduction exceeds maximum limit of KES 45,000',
                    'review_date' => now()
                ]);
            } catch (Exception $e) {
                Log::error('Failed to update employee review flag', [
                    'employee_id' => $employee->id,
                    'error' => $e->getMessage()
                ]);
                // Don't throw, just log the error
            }
            
            // Optionally: Queue notification to HR/Director
            // dispatch(new SendDismissalNotification($employee, $deduction));
            
        } catch (Exception $e) {
            Log::error('Error creating dismissal request', [
                'employee_id' => $employee->id ?? null,
                'deduction_id' => $deduction->id ?? null,
                'error' => $e->getMessage()
            ]);
            // Don't throw, just log the error
        }
    }
        
    
    public function show($id)
    {
        try {
            if (!is_numeric($id) || $id <= 0) {
                return response()->json([
                    'success' => false,
                    'message' => 'Invalid deduction ID provided.'
                ], self::ERROR_VALIDATION);
            }
            
            $deduction = SalaryDeduction::with(['employee', 'payment', 'schedule'])->findOrFail($id);
            
            // Add computed fields for response
            $deductionData = $deduction->toArray();
            $deductionData['formatted_amount'] = number_format($deduction->amount, 2);
            $deductionData['installment_info'] = null;
            
            if ($deduction->number_of_installments > 0) {
                try {
                    $paidInstallments = $deduction->schedule()->where('status', 'paid')->count();
                    $deductionData['installment_info'] = [
                        'total' => $deduction->number_of_installments,
                        'paid' => $paidInstallments,
                        'remaining' => $deduction->number_of_installments - $paidInstallments,
                        'per_installment' => number_format($deduction->installment_amount, 2)
                    ];
                } catch (Exception $e) {
                    Log::error('Error calculating installment info', [
                        'deduction_id' => $id,
                        'error' => $e->getMessage()
                    ]);
                    $deductionData['installment_info'] = ['error' => 'Unable to calculate installment details'];
                }
            }
            
            return response()->json([
                'success' => true,
                'data' => $deductionData
            ]);
            
        } catch (ModelNotFoundException $e) {
            Log::warning('Deduction not found: ' . $id);
            
            return response()->json([
                'success' => false,
                'message' => 'Deduction not found.'
            ], self::ERROR_NOT_FOUND);
            
        } catch (PDOException $e) {
            Log::error('Database error fetching deduction details', [
                'deduction_id' => $id,
                'error' => $e->getMessage()
            ]);
            
            return response()->json([
                'success' => false,
                'message' => 'Database error occurred while retrieving deduction details.'
            ], self::ERROR_SERVER);
            
        } catch (Exception $e) {
            Log::error('Error fetching deduction details: ' . $e->getMessage(), [
                'deduction_id' => $id,
                'trace' => $e->getTraceAsString()
            ]);
            
            return response()->json([
                'success' => false,
                'message' => 'Failed to retrieve deduction details.'
            ], self::ERROR_SERVER);
        }
    }
    
    public function cancel($id)
    {
        try {
            if (!is_numeric($id) || $id <= 0) {
                return response()->json([
                    'success' => false,
                    'message' => 'Invalid deduction ID provided.'
                ], self::ERROR_VALIDATION);
            }
            
            $deduction = SalaryDeduction::findOrFail($id);
            
            // Check if user has permission to cancel
            try {
                $hasPermission = $this->isAdmin() || $this->isDirector();
                if (!$hasPermission) {
                    Log::warning('Unauthorized cancellation attempt', [
                        'user_id' => Auth::id(),
                        'deduction_id' => $id
                    ]);
                    return response()->json([
                        'success' => false,
                        'message' => 'You do not have permission to cancel deductions.'
                    ], self::ERROR_FORBIDDEN);
                }
            } catch (Exception $e) {
                Log::error('Permission check failed for cancellation', ['error' => $e->getMessage()]);
                return response()->json([
                    'success' => false,
                    'message' => 'Unable to verify permissions.'
                ], self::ERROR_SERVER);
            }
            
            // Check if deduction can be cancelled
            if (!in_array($deduction->status, ['pending', 'pending_dismissal'])) {
                $statusMessage = $this->getStatusMessage($deduction->status);
                Log::warning('Attempt to cancel deduction with invalid status', [
                    'deduction_id' => $id,
                    'status' => $deduction->status
                ]);
                return response()->json([
                    'success' => false,
                    'message' => "Cannot cancel deduction with status: {$statusMessage}"
                ], self::ERROR_CONFLICT);
            }
            
            DB::beginTransaction();
            
            try {
                $cancelled = $deduction->update([
                    'status' => 'cancelled',
                    'message' => 'Deduction cancelled by user',
                    'cancelled_by' => Auth::id(),
                    'cancelled_at' => now()
                ]);
                
                if (!$cancelled) {
                    throw new Exception('Failed to update deduction status.');
                }
                
                // Log the cancellation
                $approvalLog = SalaryApprovalLog::create([
                    'approvable_type' => SalaryDeduction::class,
                    'approvable_id' => $deduction->id,
                    'user_id' => Auth::id(),
                    'action' => 'cancelled',
                    'comments' => 'Deduction cancelled by ' . (Auth::user()->name ?? Auth::id())
                ]);
                
                if (!$approvalLog) {
                    Log::warning('Failed to create cancellation log', ['deduction_id' => $id]);
                }
                
                DB::commit();
                
                Log::info('Deduction cancelled', [
                    'deduction_id' => $deduction->id,
                    'cancelled_by' => Auth::id()
                ]);
                
                return response()->json([
                    'success' => true,
                    'message' => 'Deduction cancelled successfully.'
                ]);
                
            } catch (PDOException $e) {
                DB::rollBack();
                Log::critical('Database error during cancellation', [
                    'error' => $e->getMessage(),
                    'deduction_id' => $id
                ]);
                throw new Exception('Database error occurred while cancelling deduction.');
                
            } catch (Exception $e) {
                DB::rollBack();
                throw $e;
            }
            
        } catch (ModelNotFoundException $e) {
            return response()->json([
                'success' => false,
                'message' => 'Deduction not found.'
            ], self::ERROR_NOT_FOUND);
            
        } catch (Exception $e) {
            Log::error('Error cancelling deduction: ' . $e->getMessage(), [
                'deduction_id' => $id,
                'trace' => $e->getTraceAsString()
            ]);
            
            return response()->json([
                'success' => false,
                'message' => 'Failed to cancel deduction: ' . $e->getMessage()
            ], self::ERROR_SERVER);
        }
    }
    
    public function reject(Request $request, $id)
    {
        try {
            $request->validate([
                'reason' => 'nullable|string|max:500'
            ]);
            
            if (!is_numeric($id) || $id <= 0) {
                return response()->json([
                    'success' => false,
                    'message' => 'Invalid deduction ID provided.'
                ], self::ERROR_VALIDATION);
            }
            
            // Check if user has permission to reject
            try {
                $hasPermission = $this->isAdmin() || $this->isDirector();
                if (!$hasPermission) {
                    Log::warning('Unauthorized rejection attempt', [
                        'user_id' => Auth::id(),
                        'deduction_id' => $id
                    ]);
                    return response()->json([
                        'success' => false,
                        'message' => 'You do not have permission to reject deductions.'
                    ], self::ERROR_FORBIDDEN);
                }
            } catch (Exception $e) {
                Log::error('Permission check failed for rejection', ['error' => $e->getMessage()]);
                return response()->json([
                    'success' => false,
                    'message' => 'Unable to verify permissions.'
                ], self::ERROR_SERVER);
            }
            
            $deduction = SalaryDeduction::findOrFail($id);
            
            // Check if deduction can be rejected
            if (!in_array($deduction->status, ['pending', 'pending_dismissal'])) {
                $statusMessage = $this->getStatusMessage($deduction->status);
                return response()->json([
                    'success' => false,
                    'message' => "Cannot reject deduction with status: {$statusMessage}"
                ], self::ERROR_CONFLICT);
            }
            
            DB::beginTransaction();
            
            try {
                $rejectionReason = $request->input('reason', 'No reason provided');
                
                $rejected = $deduction->update([
                    'status' => 'rejected',
                    'rejected_by' => Auth::id(),
                    'rejected_at' => now(),
                    'rejection_reason' => $rejectionReason
                ]);
                
                if (!$rejected) {
                    throw new Exception('Failed to update deduction status.');
                }
                
                // Log the rejection
                $approvalLog = SalaryApprovalLog::create([
                    'approvable_type' => SalaryDeduction::class,
                    'approvable_id' => $deduction->id,
                    'user_id' => Auth::id(),
                    'action' => 'rejected',
                    'comments' => "Deduction rejected. Reason: {$rejectionReason}"
                ]);
                
                if (!$approvalLog) {
                    Log::warning('Failed to create rejection log', ['deduction_id' => $id]);
                }
                
                DB::commit();
                
                Log::info('Deduction rejected', [
                    'deduction_id' => $deduction->id,
                    'rejected_by' => Auth::id(),
                    'reason' => $rejectionReason
                ]);
                
                return response()->json([
                    'success' => true,
                    'message' => 'Deduction rejected successfully.'
                ]);
                
            } catch (PDOException $e) {
                DB::rollBack();
                Log::critical('Database error during rejection', [
                    'error' => $e->getMessage(),
                    'deduction_id' => $id
                ]);
                throw new Exception('Database error occurred while rejecting deduction.');
                
            } catch (Exception $e) {
                DB::rollBack();
                throw $e;
            }
            
        } catch (ValidationException $e) {
            return response()->json([
                'success' => false,
                'message' => 'Validation failed',
                'errors' => $e->errors()
            ], self::ERROR_VALIDATION);
            
        } catch (ModelNotFoundException $e) {
            return response()->json([
                'success' => false,
                'message' => 'Deduction not found.'
            ], self::ERROR_NOT_FOUND);
            
        } catch (Exception $e) {
            Log::error('Error rejecting deduction: ' . $e->getMessage(), [
                'deduction_id' => $id,
                'trace' => $e->getTraceAsString()
            ]);
            
            return response()->json([
                'success' => false,
                'message' => 'Failed to reject deduction: ' . $e->getMessage()
            ], self::ERROR_SERVER);
        }
    }

    /**
     * Get pending deductions ready for current month's payment
     */
    public function getPendingForPayment($employeeId)
    {
        try {
            if (!is_numeric($employeeId) || $employeeId <= 0) {
                return response()->json([
                    'success' => false,
                    'message' => 'Invalid employee ID provided.'
                ], self::ERROR_VALIDATION);
            }
            
            $employee = SalaryEmployee::findOrFail($employeeId);
            
            $pendingDeductions = SalaryDeduction::where('employee_id', $employeeId)
                ->where('status', 'applied')
                ->with('schedule')
                ->get();
            
            $totalAmount = 0;
            $deductionsList = [];
            
            foreach ($pendingDeductions as $deduction) {
                try {
                    if ($deduction->number_of_installments > 0) {
                        $pendingSchedule = $deduction->schedule()
                            ->where('status', 'pending')
                            ->where('scheduled_date', '<=', now()->endOfMonth())
                            ->first();
                        
                        if ($pendingSchedule) {
                            $totalAmount += $pendingSchedule->amount;
                            $deductionsList[] = [
                                'id' => $deduction->id,
                                'reason' => $deduction->reason,
                                'amount' => $pendingSchedule->amount,
                                'formatted_amount' => number_format($pendingSchedule->amount, 2),
                                'installment' => $pendingSchedule->installment_number . '/' . $pendingSchedule->total_installments,
                                'type' => $deduction->type,
                                'due_date' => $pendingSchedule->scheduled_date->format('Y-m-d')
                            ];
                        }
                    } else {
                        $totalAmount += $deduction->amount;
                        $deductionsList[] = [
                            'id' => $deduction->id,
                            'reason' => $deduction->reason,
                            'amount' => $deduction->amount,
                            'formatted_amount' => number_format($deduction->amount, 2),
                            'installment' => 'Full',
                            'type' => $deduction->type,
                            'due_date' => $deduction->deduction_date
                        ];
                    }
                } catch (Exception $e) {
                    Log::error('Error processing deduction for payment', [
                        'deduction_id' => $deduction->id,
                        'error' => $e->getMessage()
                    ]);
                    // Skip this deduction but continue processing others
                    continue;
                }
            }
            
            return response()->json([
                'success' => true,
                'employee' => [
                    'id' => $employee->id,
                    'name' => $employee->name,
                    'position' => $employee->position ?? 'N/A'
                ],
                'total_deductions' => number_format($totalAmount, 2),
                'deductions' => $deductionsList,
                'count' => count($deductionsList),
                'currency' => 'KES'
            ]);
            
        } catch (ModelNotFoundException $e) {
            Log::warning('Employee not found for pending payment check', ['employee_id' => $employeeId]);
            return response()->json([
                'success' => false,
                'message' => 'Employee not found.'
            ], self::ERROR_NOT_FOUND);
            
        } catch (PDOException $e) {
            Log::error('Database error fetching pending deductions', [
                'employee_id' => $employeeId,
                'error' => $e->getMessage()
            ]);
            return response()->json([
                'success' => false,
                'message' => 'Database error occurred. Please try again.'
            ], self::ERROR_SERVER);
            
        } catch (Exception $e) {
            Log::error('Error getting pending deductions: ' . $e->getMessage(), [
                'employee_id' => $employeeId,
                'trace' => $e->getTraceAsString()
            ]);
            return response()->json([
                'success' => false,
                'message' => 'Failed to retrieve pending deductions: ' . $e->getMessage()
            ], self::ERROR_SERVER);
        }
    }
    
    /**
     * Validate deduction amount against maximum limit
     */
    public function validateDeductionAmount($amount, $user = null)
    {
        try {
            $maxAmount = 45000;
            
            // Check if user is admin or director (they can bypass limit)
            $isAdminOrDirector = false;
            if ($user) {
                if (method_exists($user, 'hasRole')) {
                    $isAdminOrDirector = $user->hasRole('admin') || $user->hasRole('director');
                }
            }
            
            if (!is_numeric($amount)) {
                return [
                    'valid' => false,
                    'message' => 'Amount must be a numeric value.'
                ];
            }
            
            if ($amount <= 0) {
                return [
                    'valid' => false,
                    'message' => 'Amount must be greater than zero.'
                ];
            }
            
            if ($amount > $maxAmount && !$isAdminOrDirector) {
                return [
                    'valid' => false,
                    'message' => "Deduction amount of KES " . number_format($amount, 2) . 
                                " exceeds the maximum allowed single deduction of KES " . 
                                number_format($maxAmount, 2) . 
                                ". Please contact HR or Administration to process this deduction."
                ];
            }
            
            return [
                'valid' => true,
                'message' => 'Amount validation passed.'
            ];
            
        } catch (Exception $e) {
            Log::error('Error validating deduction amount', [
                'amount' => $amount,
                'error' => $e->getMessage()
            ]);
            
            return [
                'valid' => false,
                'message' => 'Error validating amount: ' . $e->getMessage()
            ];
        }
    }

    /**
     * Get pending deductions for an employee
     */
    public function getEmployeePendingDeductions($employeeId)
    {
        try {
            if (!is_numeric($employeeId) || $employeeId <= 0) {
                return response()->json([
                    'success' => false,
                    'message' => 'Invalid employee ID provided.'
                ], self::ERROR_VALIDATION);
            }
            
            $employee = SalaryEmployee::findOrFail($employeeId);
            
            $pendingDeductions = SalaryDeduction::where('employee_id', $employeeId)
                ->whereIn('status', ['pending', 'applied'])
                ->with('schedule')
                ->get();
            
            $totalPendingAmount = $pendingDeductions->sum('amount');
            $nextInstallments = [];
            
            foreach ($pendingDeductions as $deduction) {
                try {
                    if ($deduction->number_of_installments > 0) {
                        $nextSchedule = $deduction->schedule()
                            ->where('status', 'pending')
                            ->where('scheduled_date', '>=', now())
                            ->orderBy('scheduled_date')
                            ->first();
                        
                        if ($nextSchedule) {
                            $nextInstallments[] = [
                                'deduction_id' => $deduction->id,
                                'reason' => $deduction->reason,
                                'amount' => number_format($nextSchedule->amount, 2),
                                'due_date' => $nextSchedule->scheduled_date->format('Y-m-d'),
                                'installment_number' => $nextSchedule->installment_number
                            ];
                        }
                    }
                } catch (Exception $e) {
                    Log::error('Error processing next installment', [
                        'deduction_id' => $deduction->id,
                        'error' => $e->getMessage()
                    ]);
                    continue;
                }
            }
            
            return response()->json([
                'success' => true,
                'employee' => [
                    'id' => $employee->id,
                    'name' => $employee->name,
                    'position' => $employee->position ?? 'N/A'
                ],
                'total_pending_amount' => number_format($totalPendingAmount, 2),
                'deductions_count' => $pendingDeductions->count(),
                'next_installments' => $nextInstallments,
                'next_installments_count' => count($nextInstallments),
                'deductions' => $pendingDeductions->map(function($deduction) {
                    return [
                        'id' => $deduction->id,
                        'reason' => $deduction->reason,
                        'amount' => number_format($deduction->amount, 2),
                        'type' => $deduction->type,
                        'status' => $deduction->status,
                        'date' => $deduction->deduction_date,
                        'installments' => $deduction->number_of_installments > 0 ? 
                            $deduction->installment_amount . ' x ' . $deduction->number_of_installments : 'Full'
                    ];
                })
            ]);
            
        } catch (ModelNotFoundException $e) {
            Log::warning('Employee not found for pending deductions', ['employee_id' => $employeeId]);
            return response()->json([
                'success' => false,
                'message' => 'Employee not found.'
            ], self::ERROR_NOT_FOUND);
            
        } catch (PDOException $e) {
            Log::error('Database error fetching pending deductions', [
                'employee_id' => $employeeId,
                'error' => $e->getMessage()
            ]);
            return response()->json([
                'success' => false,
                'message' => 'Database error occurred. Please try again.'
            ], self::ERROR_SERVER);
            
        } catch (Exception $e) {
            Log::error('Error fetching employee pending deductions: ' . $e->getMessage(), [
                'employee_id' => $employeeId,
                'trace' => $e->getTraceAsString()
            ]);
            
            return response()->json([
                'success' => false,
                'message' => 'Failed to fetch pending deductions: ' . $e->getMessage()
            ], self::ERROR_SERVER);
        }
    }
    
    /**
     * Bulk approve pending deductions
     */
    public function bulkApprove(Request $request)
    {
        try {
            $request->validate([
                'deduction_ids' => 'required|array|min:1|max:100',
                'deduction_ids.*' => 'exists:salary_deductions,id'
            ], [
                'deduction_ids.required' => 'Please select at least one deduction to approve.',
                'deduction_ids.min' => 'Please select at least one deduction to approve.',
                'deduction_ids.max' => 'Cannot approve more than 100 deductions at once.',
                'deduction_ids.*.exists' => 'One or more selected deductions do not exist.'
            ]);
            
            // Check if user has permission for bulk approval
            try {
                $hasPermission = $this->isAdmin() || $this->isDirector();
                if (!$hasPermission) {
                    Log::warning('Unauthorized bulk approval attempt', ['user_id' => Auth::id()]);
                    return response()->json([
                        'success' => false,
                        'message' => 'You do not have permission to perform bulk approvals.'
                    ], self::ERROR_FORBIDDEN);
                }
            } catch (Exception $e) {
                Log::error('Permission check failed for bulk approval', ['error' => $e->getMessage()]);
                return response()->json([
                    'success' => false,
                    'message' => 'Unable to verify permissions.'
                ], self::ERROR_SERVER);
            }
            
            $deductionIds = $request->deduction_ids;
            $approvedCount = 0;
            $failedIds = [];
            $failedReasons = [];
            
            DB::beginTransaction();
            
            try {
                foreach ($deductionIds as $id) {
                    try {
                        $deduction = SalaryDeduction::find($id);
                        
                        if (!$deduction) {
                            $failedIds[] = $id;
                            $failedReasons[$id] = 'Deduction not found';
                            continue;
                        }
                        
                        if ($deduction->status !== 'pending') {
                            $failedIds[] = $id;
                            $failedReasons[$id] = "Status is {$deduction->status}, not pending";
                            continue;
                        }
                        
                        // Check employee status
                        $employee = SalaryEmployee::find($deduction->employee_id);
                        if (!$employee || $employee->status !== 'active') {
                            $failedIds[] = $id;
                            $failedReasons[$id] = 'Employee inactive or not found';
                            continue;
                        }
                        
                        $deduction->update([
                            'status' => 'applied',
                            'approved_by' => Auth::id(),
                            'approved_at' => now()
                        ]);
                        
                        SalaryApprovalLog::create([
                            'approvable_type' => SalaryDeduction::class,
                            'approvable_id' => $deduction->id,
                            'user_id' => Auth::id(),
                            'action' => 'approved',
                            'comments' => 'Bulk approval by ' . (Auth::user()->name ?? Auth::id())
                        ]);
                        
                        $approvedCount++;
                        
                    } catch (Exception $e) {
                        $failedIds[] = $id;
                        $failedReasons[$id] = $e->getMessage();
                        Log::error('Error in bulk approval for individual deduction', [
                            'deduction_id' => $id,
                            'error' => $e->getMessage()
                        ]);
                        continue;
                    }
                }
                
                DB::commit();
                
                $message = "Successfully approved {$approvedCount} deduction(s).";
                if (!empty($failedIds)) {
                    $message .= " Failed to approve " . count($failedIds) . " deduction(s).";
                }
                
                Log::info('Bulk approval completed', [
                    'approved_count' => $approvedCount,
                    'failed_count' => count($failedIds),
                    'failed_ids' => $failedIds,
                    'approved_by' => Auth::id()
                ]);
                
                return response()->json([
                    'success' => true,
                    'message' => $message,
                    'approved_count' => $approvedCount,
                    'failed_count' => count($failedIds),
                    'failed_ids' => $failedIds,
                    'failed_reasons' => $failedReasons
                ]);
                
            } catch (PDOException $e) {
                DB::rollBack();
                Log::critical('Database error in bulk approval', [
                    'error' => $e->getMessage(),
                    'deduction_ids' => $deductionIds
                ]);
                throw new Exception('Database error occurred during bulk approval.');
                
            } catch (Exception $e) {
                DB::rollBack();
                throw $e;
            }
            
        } catch (ValidationException $e) {
            return response()->json([
                'success' => false,
                'message' => 'Validation failed',
                'errors' => $e->errors()
            ], self::ERROR_VALIDATION);
            
        } catch (Exception $e) {
            Log::error('Error in bulk approval: ' . $e->getMessage(), [
                'trace' => $e->getTraceAsString()
            ]);
            
            return response()->json([
                'success' => false,
                'message' => 'Failed to process bulk approval: ' . $e->getMessage()
            ], self::ERROR_SERVER);
        }
    }
    
    /**
     * Export deductions to CSV
     */
    public function export(Request $request)
    {
        try {
            $query = SalaryDeduction::with('employee');
            
            // Apply filters similar to index
            if ($request->filled('status')) {
                $query->where('status', $request->status);
            }
            
            if ($request->filled('type')) {
                $query->where('type', $request->type);
            }
            
            if ($request->filled('from_date')) {
                $query->whereDate('deduction_date', '>=', $request->from_date);
            }
            
            if ($request->filled('to_date')) {
                $query->whereDate('deduction_date', '<=', $request->to_date);
            }
            
            // Limit export to prevent memory issues
            $deductions = $query->orderBy('created_at', 'desc')->limit(10000)->get();
            
            if ($deductions->isEmpty()) {
                return response()->json([
                    'success' => false,
                    'message' => 'No deductions found to export.'
                ], self::ERROR_NOT_FOUND);
            }
            
            $filename = 'deductions_export_' . date('Y-m-d_His') . '.csv';
            
            $headers = [
                'Content-Type' => 'text/csv',
                'Content-Disposition' => 'attachment; filename="' . $filename . '"',
                'Cache-Control' => 'private, max-age=0, must-revalidate',
                'Pragma' => 'public'
            ];
            
            $callback = function() use ($deductions) {
                try {
                    $file = fopen('php://output', 'w');
                    if ($file === false) {
                        throw new Exception('Unable to create output stream.');
                    }
                    
                    fputs($file, "\xEF\xBB\xBF"); // UTF-8 BOM
                    
                    // Headers
                    fputcsv($file, [
                        'ID', 'Employee Name', 'Employee ID', 'Reason', 'Amount (KES)', 'Type',
                        'Deduction Date', 'Status', 'Installments', 'Installment Amount',
                        'Requires Dismissal', 'Created At', 'Approved At', 'Description'
                    ]);
                    
                    // Data rows
                    foreach ($deductions as $deduction) {
                        try {
                            fputcsv($file, [
                                $deduction->id,
                                $deduction->employee->name ?? 'N/A',
                                $deduction->employee->employee_id ?? 'N/A',
                                $deduction->reason,
                                number_format($deduction->amount, 2),
                                ucfirst($deduction->type),
                                $deduction->deduction_date,
                                ucfirst($deduction->status),
                                $deduction->number_of_installments ?: 'Single',
                                $deduction->installment_amount ? number_format($deduction->installment_amount, 2) : 'N/A',
                                $deduction->requires_dismissal ? 'Yes' : 'No',
                                $deduction->created_at,
                                $deduction->approved_at ?? 'N/A',
                                $deduction->description ?? ''
                            ]);
                        } catch (Exception $e) {
                            Log::error('Error writing deduction to CSV', [
                                'deduction_id' => $deduction->id,
                                'error' => $e->getMessage()
                            ]);
                            // Skip this row but continue with others
                            continue;
                        }
                    }
                    
                    fclose($file);
                    
                } catch (Exception $e) {
                    Log::error('Error in CSV export callback', [
                        'error' => $e->getMessage(),
                        'trace' => $e->getTraceAsString()
                    ]);
                    echo "Error generating export: " . $e->getMessage();
                }
            };
            
            Log::info('Deduction export initiated', [
                'user_id' => Auth::id(),
                'filters' => $request->all(),
                'record_count' => $deductions->count()
            ]);
            
            return response()->stream($callback, 200, $headers);
            
        } catch (PDOException $e) {
            Log::error('Database error exporting deductions: ' . $e->getMessage(), [
                'trace' => $e->getTraceAsString()
            ]);
            
            return response()->json([
                'success' => false,
                'message' => 'Database error occurred while exporting.'
            ], self::ERROR_SERVER);
            
        } catch (Exception $e) {
            Log::error('Error exporting deductions: ' . $e->getMessage(), [
                'trace' => $e->getTraceAsString()
            ]);
            
            return response()->json([
                'success' => false,
                'message' => 'Failed to export deductions: ' . $e->getMessage()
            ], self::ERROR_SERVER);
        }
    }
    
    /**
     * Helper method to get readable status message
     */
    private function getStatusMessage($status)
    {
        $statusMessages = [
            'pending' => 'Pending',
            'pending_dismissal' => 'Pending Dismissal Review',
            'applied' => 'Already Applied',
            'completed' => 'Completed',
            'cancelled' => 'Cancelled',
            'rejected' => 'Rejected',
            'dismissed' => 'Dismissed',
            'dismissal_processed' => 'Dismissal Processed'
        ];
        
        return $statusMessages[$status] ?? ucfirst($status);
    }
    
    /**
     * Get deduction summary statistics
     */
    public function getSummary()
    {
        try {
            $summary = [
                'total_deductions' => number_format(SalaryDeduction::sum('amount') ?? 0, 2),
                'pending_total' => number_format(SalaryDeduction::where('status', 'pending')->sum('amount') ?? 0, 2),
                'applied_total' => number_format(SalaryDeduction::where('status', 'applied')->sum('amount') ?? 0, 2),
                'completed_total' => number_format(SalaryDeduction::where('status', 'completed')->sum('amount') ?? 0, 2),
                'cancelled_total' => number_format(SalaryDeduction::where('status', 'cancelled')->sum('amount') ?? 0, 2),
                'pending_dismissal_count' => SalaryDeduction::where('status', 'pending_dismissal')->count() ?? 0,
                'dismissed_total' => number_format(SalaryDeduction::where('status', 'dismissed')->sum('amount') ?? 0, 2),
                'active_installments' => SalaryDeduction::where('number_of_installments', '>', 0)
                    ->whereIn('status', ['applied', 'pending'])
                    ->count() ?? 0,
                'by_type' => SalaryDeduction::select('type', DB::raw('SUM(amount) as total'))
                    ->groupBy('type')
                    ->get()
                    ->map(function($item) {
                        return [
                            'type' => ucfirst($item->type),
                            'total' => number_format($item->total, 2)
                        ];
                    }),
                'last_updated' => now()->toDateTimeString(),
                'currency' => 'KES'
            ];
            
            return response()->json([
                'success' => true,
                'data' => $summary
            ]);
            
        } catch (PDOException $e) {
            Log::error('Database error fetching deduction summary: ' . $e->getMessage());
            
            return response()->json([
                'success' => false,
                'message' => 'Database error occurred while fetching summary.'
            ], self::ERROR_SERVER);
            
        } catch (Exception $e) {
            Log::error('Error fetching deduction summary: ' . $e->getMessage(), [
                'trace' => $e->getTraceAsString()
            ]);
            
            return response()->json([
                'success' => false,
                'message' => 'Failed to fetch summary statistics: ' . $e->getMessage()
            ], self::ERROR_SERVER);
        }
    }

    /**
     * Get employee's current deduction summary including total balance
     */
    public function getEmployeeSummary($employeeId)
    {
        try {
            if (!is_numeric($employeeId) || $employeeId <= 0) {
                return response()->json([
                    'success' => false,
                    'message' => 'Invalid employee ID provided.'
                ], self::ERROR_VALIDATION);
            }
            
            $employee = SalaryEmployee::findOrFail($employeeId);
            
            try {
                $currentBalance = $this->deductionRuleService->getEmployeePendingBalance($employeeId);
            } catch (Exception $e) {
                Log::error('Error getting employee balance', [
                    'employee_id' => $employeeId,
                    'error' => $e->getMessage()
                ]);
                $currentBalance = 0;
            }
            
            $pendingDeductions = SalaryDeduction::where('employee_id', $employeeId)
                ->whereIn('status', ['applied', 'pending'])
                ->get();
            
            $maxAllowed = DeductionRuleService::MAX_CUMULATIVE_DEDUCTION_AMOUNT;
            $remainingCapacity = max(0, $maxAllowed - $currentBalance);
            
            return response()->json([
                'success' => true,
                'employee' => [
                    'id' => $employee->id,
                    'name' => $employee->name,
                    'position' => $employee->position ?? 'N/A',
                    'status' => $employee->status
                ],
                'current_balance' => number_format($currentBalance, 2),
                'max_allowed' => number_format($maxAllowed, 2),
                'remaining_capacity' => number_format($remainingCapacity, 2),
                'percentage_used' => $maxAllowed > 0 ? round(($currentBalance / $maxAllowed) * 100, 2) : 0,
                'is_over_limit' => $currentBalance > $maxAllowed,
                'requires_dismissal' => $currentBalance > $maxAllowed,
                'pending_deductions_count' => $pendingDeductions->count(),
                'pending_deductions' => $pendingDeductions->map(function($deduction) {
                    return [
                        'id' => $deduction->id,
                        'reason' => $deduction->reason,
                        'amount' => number_format($deduction->amount, 2),
                        'type' => $deduction->type,
                        'status' => $deduction->status,
                        'date' => $deduction->deduction_date,
                        'installments' => $deduction->number_of_installments > 0 ? 
                            $deduction->installment_amount . ' x ' . $deduction->number_of_installments : 'Full'
                    ];
                }),
                'currency' => 'KES'
            ]);
            
        } catch (ModelNotFoundException $e) {
            Log::warning('Employee not found for summary', ['employee_id' => $employeeId]);
            return response()->json([
                'success' => false,
                'message' => 'Employee not found.'
            ], self::ERROR_NOT_FOUND);
            
        } catch (PDOException $e) {
            Log::error('Database error fetching employee summary', [
                'employee_id' => $employeeId,
                'error' => $e->getMessage()
            ]);
            return response()->json([
                'success' => false,
                'message' => 'Database error occurred. Please try again.'
            ], self::ERROR_SERVER);
            
        } catch (Exception $e) {
            Log::error('Error fetching employee summary: ' . $e->getMessage(), [
                'employee_id' => $employeeId,
                'trace' => $e->getTraceAsString()
            ]);
            return response()->json([
                'success' => false,
                'message' => $e->getMessage()
            ], self::ERROR_SERVER);
        }
    }
}