<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Models\LeaveApplication;
use App\Models\LeaveTypeMaster;
use App\Models\LeaveTrans;
use App\Models\FyearMaster;
use App\Models\ActionMaster;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

class LeaveApplicationController extends Controller
{
    // ==================== DYNAMIC STATUS HELPERS ====================

    /**
     * Get status ID dynamically from action_master
     */
    private function getStatusId($statusName)
    {
        return LeaveApplication::getStatusId($statusName);
    }

    /**
     * Get status name from ID
     */
    private function getStatusName($statusId)
    {
        return LeaveApplication::getStatusName($statusId);
    }

    /**
     * Check if a status is FINAL stage
     * FINAL = This status is NOT used as Previous_Status in any action_master record
     */
    private function isFinalStage($statusId)
    {
        if (!$statusId) return false;

        $isUsedAsPrevious = ActionMaster::where('Previous_Status', $statusId)->exists();

        // If NOT used as previous status, this is the FINAL stage
        return !$isUsedAsPrevious;
    }

    /**
     * Check if transaction should be created
     * ✅ Transaction create ONLY IF:
     * 1. Status actually changed
     * 2. AND new status is FINAL stage (not used as Previous_Status anywhere)
     */
    private function shouldCreateTransaction($oldStatus, $newStatus)
    {
        // If status hasn't changed, NO transaction
        if ($oldStatus == $newStatus) {
            return false;
        }

        // If new status is FINAL stage, create transaction
        return $this->isFinalStage($newStatus);
    }

    /**
     * Get transaction type based on status
     */
    private function getTransactionConfig($statusId)
    {
        if (!$statusId) return null;

        $statusName = $this->getStatusName($statusId);

        // Based on status name pattern
        if (in_array($statusName, ['APPROVED', 'HR APPROVED', 'FINAL APPROVED', 'LEAVE APPROVED'])) {
            return ['type' => 'DEBIT_APPLIED-D', 'mode' => 'D', 'action' => 'debit'];
        }

        if ($statusName == 'RECALLED') {
            return ['type' => 'DEBIT_RECALL-C', 'mode' => 'C', 'action' => 'recall'];
        }

        if ($statusName == 'CANCELLED') {
            return ['type' => 'DEBIT_CANCELLED-C', 'mode' => 'C', 'action' => 'cancel'];
        }

        if ($statusName == 'ENHANCED') {
            return ['type' => 'DEBIT_APPLIED-D', 'mode' => 'D', 'action' => 'enhance'];
        }

        return null;
    }

    // ==================== CRUD METHODS ====================

    /**
 * ✅ LIST (Pagination + Search + Filters)
 *
 * Query Parameters:
 * - per_page: Number of records per page (default: 10)
 * - search: Search by LA_Code, Reason, Lv_Cnt, Status_Remark, Employee Name
 * - status: Single status ID or comma-separated multiple status IDs (e.g., 25 or 25,33,32)
 * - emp_id: Filter by employee ID
 * - lt_id: Filter by leave type ID
 * - lv_mode: Filter by leave mode (L, R, C)
 * - from_date: Filter by from date (YYYY-MM-DD)
 * - to_date: Filter by to date (YYYY-MM-DD)
 * - hod_id: Filter by HOD ID
 */
public function index(Request $request)
{
    try {
        $perPage = $request->get('per_page', 10);
        $search  = $request->get('search');
        $status  = $request->get('status');
        $empId   = $request->get('emp_id');
        $ltId    = $request->get('lt_id');
        $lvMode  = $request->get('lv_mode');
        $fromDate = $request->get('from_date');
        $toDate   = $request->get('to_date');
        $hodId    = $request->get('hod_id');

        $query = LeaveApplication::with([
            'leaveType',
            'employee',
            'hod',
            'statusData',
            'rStatus'
        ]);

        // 🔍 Search
        if (!empty($search)) {
            $query->where(function ($q) use ($search) {
                $q->where('LA_Code', 'like', "%$search%")
                    ->orWhere('Reason', 'like', "%$search%")
                    ->orWhere('Lv_Cnt', 'like', "%$search%")
                    ->orWhere('Status_Remark', 'like', "%$search%")
                    ->orWhereHas('employee', function ($q2) use ($search) {
                        $q2->where('Emp_Name', 'like', "%$search%")
                            ->orWhere('Emp_Code', 'like', "%$search%");
                    });
            });
        }

        // ✅ Status Filter (Supports single or multiple comma-separated values)
        if (!empty($status)) {
            $statusArray = explode(',', $status);
            $statusArray = array_map('trim', $statusArray); // Remove spaces
            $statusArray = array_unique($statusArray);     // Remove duplicates

            if (count($statusArray) > 1) {
                $query->whereIn('Status', $statusArray);
            } else {
                $query->where('Status', $statusArray[0]);
            }
        }

        // ✅ Other Filters
        if (!empty($empId)) {
            $query->where('Emp_Id', $empId);
        }

        if (!empty($ltId)) {
            $query->where('LT_id', $ltId);
        }

        if (!empty($lvMode)) {
            $query->where('Lv_Mode', $lvMode);
        }

        if (!empty($hodId)) {
            $query->where('LA_HOD', $hodId);
        }

        if (!empty($fromDate)) {
            $query->where('Fr_Date', '>=', $fromDate);
        }

        if (!empty($toDate)) {
            $query->where('To_Date', '<=', $toDate);
        }

        $data = $query->orderBy('LA_id', 'desc')->paginate($perPage);

        return response()->json([
            'success' => true,
            'message' => 'Leave Application List',
            'data' => $data,
            'filters' => [
                'per_page' => $perPage,
                'search' => $search,
                'status' => $status,
                'emp_id' => $empId,
                'lt_id' => $ltId,
                'lv_mode' => $lvMode,
                'from_date' => $fromDate,
                'to_date' => $toDate,
                'hod_id' => $hodId,
            ]
        ]);
    } catch (\Exception $e) {
        Log::error('LeaveApplication index error: ' . 'Something went wrong. Please try again later.');
        return response()->json([
            'success' => false,
            'message' => 'Something went wrong. Please try again later.'
        ]);
    }
}
    /**
 * ✅ ALL DATA (No pagination) with Filters
 *
 * Query Parameters:
 * - search: Search by LA_Code, Reason, Lv_Cnt, Status_Remark, Employee Name
 * - status: Single status ID or comma-separated multiple status IDs (e.g., 25 or 25,33,32)
 * - emp_id: Filter by employee ID
 * - lt_id: Filter by leave type ID
 * - lv_mode: Filter by leave mode (L, R, C)
 * - from_date: Filter by from date (YYYY-MM-DD)
 * - to_date: Filter by to date (YYYY-MM-DD)
 * - hod_id: Filter by HOD ID
 */
public function all(Request $request)
{
    try {
        $search  = $request->get('search');
        $status  = $request->get('status');
        $empId   = $request->get('emp_id');
        $ltId    = $request->get('lt_id');
        $lvMode  = $request->get('lv_mode');
        $fromDate = $request->get('from_date');
        $toDate   = $request->get('to_date');
        $hodId    = $request->get('hod_id');

        $query = LeaveApplication::with([
            'leaveType',
            'employee',
            'hod',
            'statusData',
            'rStatus'
        ]);

        // 🔍 Search
        if (!empty($search)) {
            $query->where(function ($q) use ($search) {
                $q->where('LA_Code', 'like', "%$search%")
                    ->orWhere('Reason', 'like', "%$search%")
                    ->orWhere('Lv_Cnt', 'like', "%$search%")
                    ->orWhere('Status_Remark', 'like', "%$search%")
                    ->orWhereHas('employee', function ($q2) use ($search) {
                        $q2->where('Emp_Name', 'like', "%$search%")
                            ->orWhere('Emp_Code', 'like', "%$search%");
                    });
            });
        }

        // ✅ Status Filter (Supports single or multiple comma-separated values)
        if (!empty($status)) {
            $statusArray = explode(',', $status);
            $statusArray = array_map('trim', $statusArray); // Remove spaces
            $statusArray = array_unique($statusArray);     // Remove duplicates

            if (count($statusArray) > 1) {
                $query->whereIn('Status', $statusArray);
            } else {
                $query->where('Status', $statusArray[0]);
            }
        }

        // ✅ Other Filters
        if (!empty($empId)) {
            $query->where('Emp_Id', $empId);
        }

        if (!empty($ltId)) {
            $query->where('LT_id', $ltId);
        }

        if (!empty($lvMode)) {
            $query->where('Lv_Mode', $lvMode);
        }

        if (!empty($hodId)) {
            $query->where('LA_HOD', $hodId);
        }

        if (!empty($fromDate)) {
            $query->where('Fr_Date', '>=', $fromDate);
        }

        if (!empty($toDate)) {
            $query->where('To_Date', '<=', $toDate);
        }

        $data = $query->orderBy('LA_id', 'desc')->get();

        return response()->json([
            'success' => true,
            'total' => $data->count(),
            'data' => $data,
            'filters' => [
                'search' => $search,
                'status' => $status,
                'emp_id' => $empId,
                'lt_id' => $ltId,
                'lv_mode' => $lvMode,
                'from_date' => $fromDate,
                'to_date' => $toDate,
                'hod_id' => $hodId,
            ]
        ]);
    } catch (\Exception $e) {
        Log::error('LeaveApplication all error: ' . 'Something went wrong. Please try again later.');
        return response()->json([
            'success' => false,
            'message' => 'Something went wrong. Please try again later.'
        ]);
    }
}

    /**
     * ✅ STORE - Create Leave Application (NO Transaction on submit)
     */
    public function store(Request $request)
    {
        try {
            DB::beginTransaction();

            $validated = $request->validate([
                'LA_Code' => 'nullable|string|max:20',
                'LT_id'   => 'required|exists:leave_type_master,LT_id',
                'Emp_Id'  => 'required|exists:employee_master,Emp_Id',
                'Fr_Date' => 'required|date',
                'To_Date' => 'required|date|after_or_equal:Fr_Date',
                'Tot_Days' => 'required|numeric|min:0.5',
                'Paid'    => 'required|numeric|min:0',
                'UnPaid'  => 'required|numeric|min:0',
                'FDt_hfDy' => 'nullable|in:FH,SH,FD',
                'TDt_hfDy' => 'nullable|in:FH,SH,FD',
                'Prx_HlDy' => 'nullable|boolean',
                'Sfx_HlDy' => 'nullable|boolean',
                'Lv_Mode' => 'nullable|in:L,R,C',
                'Reason'  => 'nullable|string|max:50',
                'Lv_Cnt'  => 'nullable|string|max:15',
                'Lv_Adr'  => 'nullable|string|max:100',
                'LA_HOD'  => 'nullable|exists:user_master,Usr_Id',
                'Status'  => 'nullable|exists:action_master,Act_Id',
                'R_Status' => 'nullable|exists:action_master,Act_Id',
                'Status_Remark' => 'nullable|string',
            ]);

            if (empty($validated['LA_Code'])) {
                $validated['LA_Code'] = LeaveApplication::generateCode();
            }

            // Set default Lv_Mode
            if (empty($validated['Lv_Mode'])) {
                $validated['Lv_Mode'] = 'L';
            }

            if (!empty($validated['LA_HOD'])) {
                $validated['LA_HDT'] = now();
            }

            // Default status: Initial Leave Entry
            if (empty($validated['Status'])) {
                $initialEntryId = $this->getStatusId('Initial Leave Entry');
                if (!$initialEntryId) {
                    $initialEntryId = $this->getStatusId('HOD PENDING');
                }
                $validated['Status'] = $initialEntryId;
            }

            $data = LeaveApplication::create($validated);

            // ❌ NO transaction on create (will be created when status reaches FINAL stage)

            DB::commit();

            return response()->json([
                'success' => true,
                'message' => 'Leave Application Created Successfully (Pending Approval - No Transaction Yet)',
                'data' => $data->load(['leaveType', 'employee', 'statusData']),
                'transaction_created' => false
            ]);
        } catch (\Exception $e) {
            DB::rollBack();
            Log::error('LeaveApplication store error: ' . 'Something went wrong. Please try again later.');
            return response()->json([
                'success' => false,
                'message' => 'Something went wrong. Please try again later.'
            ]);
        }
    }

    /**
     * ✅ SHOW - Get Single Record
     */
    public function show($id)
    {
        try {
            $data = LeaveApplication::with([
                'leaveType',
                'employee',
                'hod',
                'statusData',
                'rStatus'
            ])->find($id);

            if (!$data) {
                return response()->json([
                    'success' => false,
                    'message' => 'Record not found'
                ]);
            }

            $balance = $this->getEmployeeLeaveBalance($data->Emp_Id, $data->LT_id);

            return response()->json([
                'success' => true,
                'data' => $data,
                'current_balance' => $balance
            ]);
        } catch (\Exception $e) {
            Log::error('LeaveApplication show error: ' . 'Something went wrong. Please try again later.');
            return response()->json([
                'success' => false,
                'message' => 'Something went wrong. Please try again later.'
            ]);
        }
    }

    /**
     * ✅ UPDATE - Transaction ONLY when status reaches FINAL stage
     */
    public function update(Request $request, $id)
    {
        try {
            DB::beginTransaction();

            $data = LeaveApplication::find($id);

            if (!$data) {
                return response()->json([
                    'success' => false,
                    'message' => 'Record not found'
                ]);
            }

            $validated = $request->validate([
                'LA_Code' => 'sometimes|nullable|string|max:20',
                'LT_id'   => 'sometimes|nullable|exists:leave_type_master,LT_id',
                'Emp_Id'  => 'sometimes|nullable|exists:employee_master,Emp_Id',
                'Fr_Date' => 'sometimes|date',
                'To_Date' => 'sometimes|date|after_or_equal:Fr_Date',
                'Tot_Days' => 'sometimes|numeric|min:0',
                'Paid'    => 'sometimes|numeric|min:0',
                'UnPaid'  => 'sometimes|numeric|min:0',
                'FDt_hfDy' => 'nullable|in:FH,SH,FD',
                'TDt_hfDy' => 'nullable|in:FH,SH,FD',
                'Prx_HlDy' => 'nullable|boolean',
                'Sfx_HlDy' => 'nullable|boolean',
                'Lv_Mode' => 'nullable|in:L,R,C',
                'Reason'  => 'nullable|string|max:50',
                'Lv_Cnt'  => 'nullable|string|max:15',
                'Lv_Adr'  => 'nullable|string|max:100',
                'LA_HOD'  => 'nullable|exists:user_master,Usr_Id',
                'Status'  => 'nullable|exists:action_master,Act_Id',
                'R_Status' => 'nullable|exists:action_master,Act_Id',
                'Status_Remark' => 'nullable|string',
                'cancelled_days' => 'nullable|numeric|min:0',
                'cancelled_paid' => 'nullable|numeric|min:0',
                'cancelled_unpaid' => 'nullable|numeric|min:0',
                'new_fr_date' => 'nullable|date',
                'new_to_date' => 'nullable|date|after_or_equal:new_fr_date',
                'additional_days' => 'nullable|numeric|min:0',
                'additional_paid' => 'nullable|numeric|min:0',
                'additional_unpaid' => 'nullable|numeric|min:0',
            ]);

            $oldStatus = $data->Status;
            $newStatus = $validated['Status'] ?? $oldStatus;
            $statusChanged = ($oldStatus != $newStatus);

            // Get status names
            $newStatusName = $this->getStatusName($newStatus);
            $oldStatusName = $this->getStatusName($oldStatus);

            // ✅ Check if transaction should be created (FINAL stage)
            $shouldCreateTransaction = $this->shouldCreateTransaction($oldStatus, $newStatus);

            // ✅ Check for special cases (Recall/Cancel always create transaction)
            $isRecallOrCancel = in_array($newStatusName, ['RECALLED', 'CANCELLED']);

            // Handle LA_HOD timestamp
            if ($request->has('LA_HOD')) {
                $validated['LA_HDT'] = now();
            }

            // Auto set Lv_Mode based on status
            if ($statusChanged) {
                if ($newStatusName == 'RECALLED') {
                    $validated['Lv_Mode'] = 'R';
                } elseif ($newStatusName == 'CANCELLED') {
                    $validated['Lv_Mode'] = 'C';
                } else {
                    $validated['Lv_Mode'] = 'L';
                }
            } elseif (!$request->has('Lv_Mode')) {
                $validated['Lv_Mode'] = $data->Lv_Mode ?? 'L';
            }

            $transactionCreated = false;

            // ✅ MAIN LOGIC: Create transaction ONLY for FINAL stage
            if ($shouldCreateTransaction) {
                $this->createDebitTransaction($data, $newStatus);
                $transactionCreated = true;
                Log::info("Transaction created - Status {$newStatus} ({$newStatusName}) is FINAL stage (not used as Previous_Status)");
            }

            // ✅ SPECIAL CASE: Recall/Cancel always create transaction
            if ($isRecallOrCancel && $statusChanged) {
                if ($newStatusName == 'RECALLED') {
                    $this->createRecallTransaction($data, $newStatus);
                    $transactionCreated = true;
                    $validated['deleted_at'] = now();
                    Log::info("Recall transaction created for leave {$id}");
                } elseif ($newStatusName == 'CANCELLED') {
                    $cancelledDays = $request->cancelled_days ?? $data->Tot_Days;
                    $cancelledPaid = $request->cancelled_paid ?? $cancelledDays;
                    $cancelledUnpaid = $request->cancelled_unpaid ?? 0;

                    $this->createCancelTransaction($data, $cancelledDays, $cancelledPaid, $cancelledUnpaid, $newStatus);
                    $transactionCreated = true;

                    if ($cancelledDays < $data->Tot_Days) {
                        $validated['Tot_Days'] = $data->Tot_Days - $cancelledDays;
                        $validated['Paid'] = ($data->Paid ?? 0) - $cancelledPaid;
                        $validated['UnPaid'] = ($data->UnPaid ?? 0) - $cancelledUnpaid;
                    } else {
                        $validated['deleted_at'] = now();
                    }
                    Log::info("Cancel transaction created for leave {$id}");
                }
            }

            // Handle Enhance operation
            if ($statusChanged && $newStatusName == 'ENHANCED') {
                $additionalDays = $request->additional_days ?? 1;
                $additionalPaid = $request->additional_paid ?? $additionalDays;
                $additionalUnpaid = $request->additional_unpaid ?? 0;

                $this->createEnhanceTransaction($data, $additionalDays, $additionalPaid, $additionalUnpaid, $newStatus);
                $transactionCreated = true;

                $validated['Tot_Days'] = $data->Tot_Days + $additionalDays;
                $validated['Paid'] = ($data->Paid ?? 0) + $additionalPaid;
                $validated['UnPaid'] = ($data->UnPaid ?? 0) + $additionalUnpaid;

                if ($request->has('new_to_date')) {
                    $validated['To_Date'] = $request->new_to_date;
                }
                Log::info("Enhance transaction created for leave {$id}");
            }

            // Handle Forward operation (always creates transactions)
            if ($request->has('new_fr_date') && $request->has('new_to_date')) {
                $newTotDays = $this->calculateWorkingDays($request->new_fr_date, $request->new_to_date);
                $hodPendingId = $this->getStatusId('Initial Leave Entry');
                if (!$hodPendingId) $hodPendingId = $this->getStatusId('HOD PENDING');

                $forwardedId = $this->getStatusId('FORWARDED');
                if (!$forwardedId) $forwardedId = $newStatus;

                // Cancel original - creates transaction
                $this->createCancelTransaction($data, $data->Tot_Days, $data->Paid, $data->UnPaid, $forwardedId);

                // Generate unique code for forwarded leave
                $newCode = LeaveApplication::generateCode();

                $forwardedData = [
                    'LA_Code' => $newCode,
                    'LT_id' => $data->LT_id,
                    'Emp_Id' => $data->Emp_Id,
                    'Fr_Date' => $request->new_fr_date,
                    'To_Date' => $request->new_to_date,
                    'Tot_Days' => $newTotDays,
                    'Paid' => $data->Paid,
                    'UnPaid' => $data->UnPaid,
                    'FDt_hfDy' => $data->FDt_hfDy,
                    'TDt_hfDy' => $data->TDt_hfDy,
                    'Prx_HlDy' => $data->Prx_HlDy,
                    'Sfx_HlDy' => $data->Sfx_HlDy,
                    'Lv_Mode' => 'L',
                    'Reason' => $request->Status_Remark ?? 'Forwarded leave',
                    'Lv_Cnt' => $data->Lv_Cnt,
                    'Lv_Adr' => $data->Lv_Adr,
                    'Status' => $hodPendingId,
                    'Status_Remark' => "Forwarded from original leave ID: {$data->LA_id}",
                ];

                $forwardedApp = LeaveApplication::create($forwardedData);

                // Create debit transaction for forwarded leave
                $this->createDebitTransaction($forwardedApp, $hodPendingId);

                $validated['deleted_at'] = now();
                $data->update($validated);

                DB::commit();

                return response()->json([
                    'success' => true,
                    'message' => 'Leave forwarded successfully with transactions',
                    'data' => [
                        'original_leave' => $data,
                        'forwarded_leave' => $forwardedApp
                    ],
                    'transactions_created' => true
                ]);
            }

            // Update the record
            $data->update($validated);
            DB::commit();

            // Prepare response message
            $message = "";
            if ($statusChanged) {
                if ($shouldCreateTransaction) {
                    $message = "Status changed to FINAL stage ({$newStatusName}) - Transaction created";
                } else if ($isRecallOrCancel) {
                    $message = "Status changed to {$newStatusName} - Transaction created";
                } else {
                    $message = "Status changed to intermediate stage ({$newStatusName}) - No transaction yet";
                }
            } else {
                $message = "No status change - No transaction";
            }

            return response()->json([
                'success' => true,
                'message' => $message,
                'data' => $data,
                'status_changed' => $statusChanged,
                'old_status' => $oldStatus,
                'old_status_name' => $oldStatusName,
                'new_status' => $newStatus,
                'new_status_name' => $newStatusName,
                'is_final_stage' => $this->isFinalStage($newStatus),
                'is_used_as_previous' => ActionMaster::where('Previous_Status', $newStatus)->exists(),
                'transaction_created' => $transactionCreated
            ]);

        } catch (\Exception $e) {
            DB::rollBack();
            Log::error('LeaveApplication update error: ' . 'Something went wrong. Please try again later.');
            return response()->json([
                'success' => false,
                'message' => 'Something went wrong. Please try again later.'
            ]);
        }
    }


   /**
 * ✅ BULK UPDATE - Update multiple leave applications at once
 *
 * PAYLOAD:
 * {
 *     "updates": [
 *         {
 *             "id": 14,
 *             "Status": 60,
 *             "Lv_Mode": "C",
 *             "LA_HOD": 1,
 *             "Status_Remark": "Rejected"
 *         },
 *         {
 *             "id": 15,
 *             "Lv_Mode": "R",
 *             "Status_Remark": "Marked as recall"
 *         }
 *     ]
 * }
 */
public function bulkUpdate(Request $request)
{
    try {
        DB::beginTransaction();

        $validated = $request->validate([
            'updates' => 'required|array|min:1',
            'updates.*.id' => 'required|exists:leave_application,LA_id',
            'updates.*.Status' => 'sometimes|nullable|exists:action_master,Act_Id',
            'updates.*.Lv_Mode' => 'sometimes|nullable|in:L,R,C',
            'updates.*.LA_HOD' => 'sometimes|nullable|exists:user_master,Usr_Id',
            'updates.*.R_Status' => 'sometimes|nullable|exists:action_master,Act_Id',
            'updates.*.Status_Remark' => 'sometimes|nullable|string',
            'updates.*.Tot_Days' => 'sometimes|nullable|numeric|min:0',
            'updates.*.Paid' => 'sometimes|nullable|numeric|min:0',
            'updates.*.UnPaid' => 'sometimes|nullable|numeric|min:0',
            'updates.*.cancelled_days' => 'sometimes|nullable|numeric|min:0',
            'updates.*.cancelled_paid' => 'sometimes|nullable|numeric|min:0',
            'updates.*.cancelled_unpaid' => 'sometimes|nullable|numeric|min:0',
            'updates.*.additional_days' => 'sometimes|nullable|numeric|min:0',
            'updates.*.additional_paid' => 'sometimes|nullable|numeric|min:0',
            'updates.*.additional_unpaid' => 'sometimes|nullable|numeric|min:0',
            'updates.*.new_fr_date' => 'sometimes|nullable|date',
            'updates.*.new_to_date' => 'sometimes|nullable|date|after_or_equal:new_fr_date',
        ]);

        $updates = $validated['updates'];
        $results = [];
        $successCount = 0;
        $failedCount = 0;

        foreach ($updates as $index => $updateData) {
            try {
                $leaveId = $updateData['id'];
                $leave = LeaveApplication::find($leaveId);

                if (!$leave) {
                    $results[] = [
                        'index' => $index,
                        'id' => $leaveId,
                        'success' => false,
                        'message' => 'Leave record not found'
                    ];
                    $failedCount++;
                    continue;
                }

                // Store old values for response
                $oldStatus = $leave->Status;
                $oldLvMode = $leave->Lv_Mode ?? 'L';
                $oldStatusName = $this->getStatusName($oldStatus);

                // Get new values
                $newStatus = $updateData['Status'] ?? $oldStatus;
                $statusChanged = ($oldStatus != $newStatus);
                $newStatusName = $this->getStatusName($newStatus);

                // Check if transaction should be created (FINAL stage)
                $shouldCreateTransaction = $this->shouldCreateTransaction($oldStatus, $newStatus);

                // Check for special cases
                $isRecallOrCancel = in_array($newStatusName, ['RECALLED', 'CANCELLED']);

                // Handle LA_HOD timestamp
                if (isset($updateData['LA_HOD'])) {
                    $updateData['LA_HDT'] = now();
                }

                // ✅ UPDATE Lv_Mode - Priority Order:
                // 1. If frontend sent Lv_Mode directly, use that
                // 2. Else if status changed, auto-set based on new status
                // 3. Else keep existing Lv_Mode (don't include in update)
                $lvModeUpdated = false;
                $newLvMode = $oldLvMode;

                if (isset($updateData['Lv_Mode'])) {
                    // Frontend sent Lv_Mode directly
                    $newLvMode = $updateData['Lv_Mode'];
                    $lvModeUpdated = ($newLvMode != $oldLvMode);
                    $updateData['Lv_Mode'] = $newLvMode;
                } elseif ($statusChanged) {
                    // Auto-set based on status change
                    if ($newStatusName == 'RECALLED') {
                        $newLvMode = 'R';
                        $lvModeUpdated = true;
                        $updateData['Lv_Mode'] = 'R';
                    } elseif ($newStatusName == 'CANCELLED') {
                        $newLvMode = 'C';
                        $lvModeUpdated = true;
                        $updateData['Lv_Mode'] = 'C';
                    } else {
                        $newLvMode = 'L';
                        $lvModeUpdated = ($newLvMode != $oldLvMode);
                        $updateData['Lv_Mode'] = 'L';
                    }
                }
                // If no Lv_Mode provided and no status change, keep existing (don't set in updateData)

                $transactionCreated = false;

                // Create transaction for FINAL stage
                if ($shouldCreateTransaction) {
                    $this->createDebitTransaction($leave, $newStatus);
                    $transactionCreated = true;
                }

                // Special case: Recall
                if ($isRecallOrCancel && $statusChanged) {
                    if ($newStatusName == 'RECALLED') {
                        $this->createRecallTransaction($leave, $newStatus);
                        $transactionCreated = true;
                        $updateData['deleted_at'] = now();
                    } elseif ($newStatusName == 'CANCELLED') {
                        $cancelledDays = $updateData['cancelled_days'] ?? $leave->Tot_Days;
                        $cancelledPaid = $updateData['cancelled_paid'] ?? $cancelledDays;
                        $cancelledUnpaid = $updateData['cancelled_unpaid'] ?? 0;

                        $this->createCancelTransaction($leave, $cancelledDays, $cancelledPaid, $cancelledUnpaid, $newStatus);
                        $transactionCreated = true;

                        if ($cancelledDays < $leave->Tot_Days) {
                            $updateData['Tot_Days'] = $leave->Tot_Days - $cancelledDays;
                            $updateData['Paid'] = ($leave->Paid ?? 0) - $cancelledPaid;
                            $updateData['UnPaid'] = ($leave->UnPaid ?? 0) - $cancelledUnpaid;
                        } else {
                            $updateData['deleted_at'] = now();
                        }
                    }
                }

                // Handle Enhance
                if ($statusChanged && $newStatusName == 'ENHANCED') {
                    $additionalDays = $updateData['additional_days'] ?? 1;
                    $additionalPaid = $updateData['additional_paid'] ?? $additionalDays;
                    $additionalUnpaid = $updateData['additional_unpaid'] ?? 0;

                    $this->createEnhanceTransaction($leave, $additionalDays, $additionalPaid, $additionalUnpaid, $newStatus);
                    $transactionCreated = true;

                    $updateData['Tot_Days'] = $leave->Tot_Days + $additionalDays;
                    $updateData['Paid'] = ($leave->Paid ?? 0) + $additionalPaid;
                    $updateData['UnPaid'] = ($leave->UnPaid ?? 0) + $additionalUnpaid;

                    if (isset($updateData['new_to_date'])) {
                        $updateData['To_Date'] = $updateData['new_to_date'];
                    }
                }

                // Handle Forward (creates new application)
                if (isset($updateData['new_fr_date']) && isset($updateData['new_to_date'])) {
                    $newTotDays = $this->calculateWorkingDays($updateData['new_fr_date'], $updateData['new_to_date']);
                    $hodPendingId = $this->getStatusId('Initial Leave Entry');
                    if (!$hodPendingId) $hodPendingId = $this->getStatusId('HOD PENDING');

                    $forwardedId = $this->getStatusId('FORWARDED');
                    if (!$forwardedId) $forwardedId = $newStatus;

                    // Cancel original
                    $this->createCancelTransaction($leave, $leave->Tot_Days, $leave->Paid, $leave->UnPaid, $forwardedId);

                    // Create forwarded application
                    $newCode = LeaveApplication::generateCode();

                    $forwardedData = [
                        'LA_Code' => $newCode,
                        'LT_id' => $leave->LT_id,
                        'Emp_Id' => $leave->Emp_Id,
                        'Fr_Date' => $updateData['new_fr_date'],
                        'To_Date' => $updateData['new_to_date'],
                        'Tot_Days' => $newTotDays,
                        'Paid' => $leave->Paid,
                        'UnPaid' => $leave->UnPaid,
                        'FDt_hfDy' => $leave->FDt_hfDy,
                        'TDt_hfDy' => $leave->TDt_hfDy,
                        'Prx_HlDy' => $leave->Prx_HlDy,
                        'Sfx_HlDy' => $leave->Sfx_HlDy,
                        'Lv_Mode' => 'L',
                        'Reason' => $updateData['Status_Remark'] ?? 'Forwarded leave',
                        'Lv_Cnt' => $leave->Lv_Cnt,
                        'Lv_Adr' => $leave->Lv_Adr,
                        'Status' => $hodPendingId,
                        'Status_Remark' => "Forwarded from original leave ID: {$leave->LA_id}",
                    ];

                    $forwardedApp = LeaveApplication::create($forwardedData);
                    $this->createDebitTransaction($forwardedApp, $hodPendingId);

                    $updateData['deleted_at'] = now();
                    $leave->update($updateData);

                    $results[] = [
                        'index' => $index,
                        'id' => $leaveId,
                        'success' => true,
                        'message' => 'Leave forwarded successfully',
                        'new_leave_id' => $forwardedApp->LA_id,
                        'new_leave_code' => $forwardedApp->LA_Code,
                        'transaction_created' => true,
                        'old_lv_mode' => $oldLvMode,
                        'new_lv_mode' => 'L',
                        'lv_mode_updated' => true
                    ];
                    $successCount++;
                    continue;
                }

                // Update the leave
                $leave->update($updateData);

                // Prepare response message
                $message = "";
                if ($statusChanged) {
                    if ($shouldCreateTransaction) {
                        $message = "Status changed to FINAL stage ({$newStatusName}) - Transaction created";
                    } elseif ($isRecallOrCancel) {
                        $message = "Status changed to {$newStatusName} - Transaction created";
                    } else {
                        $message = "Status changed to intermediate stage ({$newStatusName}) - No transaction yet";
                    }
                } else {
                    $message = "Updated - No status change";
                }

                if ($lvModeUpdated && !$statusChanged) {
                    $message .= " | Lv_Mode updated from '{$oldLvMode}' to '{$newLvMode}'";
                } elseif ($lvModeUpdated && $statusChanged) {
                    $message .= " | Lv_Mode auto-set to '{$newLvMode}'";
                }

                $results[] = [
                    'index' => $index,
                    'id' => $leaveId,
                    'success' => true,
                    'message' => $message,
                    'old_status' => $oldStatus,
                    'old_status_name' => $oldStatusName,
                    'new_status' => $newStatus,
                    'new_status_name' => $newStatusName,
                    'transaction_created' => $transactionCreated,
                    'old_lv_mode' => $oldLvMode,
                    'new_lv_mode' => $newLvMode,
                    'lv_mode_updated' => $lvModeUpdated
                ];

                $successCount++;

            } catch (\Exception $e) {
                $results[] = [
                    'index' => $index,
                    'id' => $updateData['id'] ?? null,
                    'success' => false,
                    'message' => 'Something went wrong. Please try again later.'
                ];
                $failedCount++;
            }
        }

        DB::commit();

        return response()->json([
            'success' => true,
            'message' => "Bulk update completed. Success: {$successCount}, Failed: {$failedCount}",
            'total_processed' => count($updates),
            'success_count' => $successCount,
            'failed_count' => $failedCount,
            'results' => $results
        ]);

    } catch (\Exception $e) {
        DB::rollBack();
        Log::error('LeaveApplication bulkUpdate error: ' . 'Something went wrong. Please try again later.');
        return response()->json([
            'success' => false,
            'message' => 'Something went wrong. Please try again later.'
        ]);
    }
}

    /**
     * ✅ DELETE - Soft Delete
     */
    public function destroy($id)
    {
        try {
            $data = LeaveApplication::find($id);

            if (!$data) {
                return response()->json([
                    'success' => false,
                    'message' => 'Record not found'
                ]);
            }

            $data->delete();

            return response()->json([
                'success' => true,
                'message' => 'Deleted successfully'
            ]);
        } catch (\Exception $e) {
            Log::error('LeaveApplication destroy error: ' . 'Something went wrong. Please try again later.');
            return response()->json([
                'success' => false,
                'message' => 'Something went wrong. Please try again later.'
            ]);
        }
    }



        // ==================== LEAVE CANCELLATION ENTRY ====================

    /**
     * ✅ LEAVE CANCELLATION ENTRY
     * Manual cancellation of leave by HR/Admin (without application)
     *
     * PAYLOAD:
     * {
     *     "LT_id": 1,
     *     "Emp_Id": 72,
     *     "FY_Id": 3,
     *     "cancelled_days": 2.5,
     *     "cancelled_paid": 2.5,
     *     "cancelled_unpaid": 0,
     *     "cancellation_date": "2026-06-01",
     *     "Status_Remark": "Leave cancelled due to emergency work",
     *     "Status": 65
     * }
     */
    public function leaveCancellationEntry(Request $request)
    {
        try {
            DB::beginTransaction();

            $validated = $request->validate([
                'LT_id' => 'required|exists:leave_type_master,LT_id',
                'Emp_Id' => 'required|exists:employee_master,Emp_Id',
                'FY_Id' => 'required|exists:fyear_master,FY_Id',
                'cancelled_days' => 'required|numeric|min:0.5',
                'cancelled_paid' => 'nullable|numeric|min:0',
                'cancelled_unpaid' => 'nullable|numeric|min:0',
                'cancellation_date' => 'required|date',
                'Status_Remark' => 'nullable|string',
                'Status' => 'nullable|exists:action_master,Act_Id',
            ]);

            $cancelledPaid = $validated['cancelled_paid'] ?? $validated['cancelled_days'];
            $cancelledUnpaid = $validated['cancelled_unpaid'] ?? 0;

            $statusId = $validated['Status'] ?? $this->getStatusId('CANCELLED');

            // Get financial year if not provided
            $fyId = $validated['FY_Id'];
            $fy = FyearMaster::find($fyId);

            if (!$fy) {
                $fy = $this->getCurrentFinancialYear();
                $fyId = $fy ? $fy->FY_Id : null;
            }

            // Check current balance before cancellation
            $currentBalance = $this->getEmployeeLeaveBalance($validated['Emp_Id'], $validated['LT_id']);

            if ($currentBalance < $validated['cancelled_days']) {
                return response()->json([
                    'success' => false,
                    'message' => "Cannot cancel. Employee has only {$currentBalance} days available, but trying to cancel {$validated['cancelled_days']} days.",
                    'data' => [
                        'available_balance' => $currentBalance,
                        'requested_cancellation' => $validated['cancelled_days']
                    ]
                ], 422);
            }

            // Create cancellation transaction
            $transaction = LeaveTrans::create([
                'LT_id' => $validated['LT_id'],
                'LT_Date' => $validated['cancellation_date'],
                'Emp_Id' => $validated['Emp_Id'],
                'FY_Id' => $fyId,
                'Tr_Type' => 'DEBIT_CANCELLED-C',
                'Tr_Date' => now(),
                'Tr_Days' => $validated['cancelled_days'],
                'Paid' => $cancelledPaid,
                'UnPaid' => $cancelledUnpaid,
                'Tr_Mode' => 'C',
                'Tr_HDT' => now(),
                'Status' => $statusId,
                'created_at' => now(),
                'updated_at' => now(),
            ]);

            DB::commit();

            // Calculate new balance
            $newBalance = $currentBalance - $validated['cancelled_days'];

            return response()->json([
                'success' => true,
                'message' => 'Leave cancellation entry created successfully',
                'data' => [
                    'transaction' => $transaction,
                    'leave_type_id' => $validated['LT_id'],
                    'employee_id' => $validated['Emp_Id'],
                    'cancelled_days' => $validated['cancelled_days'],
                    'cancelled_paid' => $cancelledPaid,
                    'cancelled_unpaid' => $cancelledUnpaid,
                    'previous_balance' => $currentBalance,
                    'new_balance' => $newBalance,
                    'status_id' => $statusId
                ]
            ]);

        } catch (\Exception $e) {
            DB::rollBack();
            Log::error('Leave cancellation entry error: ' . 'Something went wrong. Please try again later.');
            return response()->json([
                'success' => false,
                'message' => 'Something went wrong. Please try again later.'
            ]);
        }
    }

    // ==================== LEAVE CARRY FORWARD ENTRY ====================

    /**
     * ✅ LEAVE CARRY FORWARD ENTRY
     * Year-end processing to carry forward remaining balance to next financial year
     *
     * PAYLOAD:
     * {
     *     "from_fy_id": 2,
     *     "to_fy_id": 3,
     *     "emp_id": 72,
     *     "lt_id": 1,
     *     "auto_carry": true
     * }
     */
   public function leaveCarryForwardEntry(Request $request)
{
    try {
        DB::beginTransaction();

        $validated = $request->validate([
            'from_fy_id' => 'required|exists:fyear_master,FY_Id',
            'to_fy_id' => 'required|exists:fyear_master,FY_Id',
            'emp_id' => 'nullable|exists:employee_master,Emp_Id',
            'lt_id' => 'nullable|exists:leave_type_master,LT_id',
            'auto_carry' => 'boolean',
        ]);

        $fromFyId = $validated['from_fy_id'];
        $toFyId = $validated['to_fy_id'];
        $empId = $validated['emp_id'] ?? null;
        $ltId = $validated['lt_id'] ?? null;

        $fromFy = FyearMaster::find($fromFyId);
        $toFy = FyearMaster::find($toFyId);

        // ✅ FIXED: Calculate NET BALANCE, not total credits
        $query = LeaveTrans::select(
                'Emp_Id',
                'LT_id',
                DB::raw('SUM(CASE WHEN Tr_Mode = "C" THEN Tr_Days ELSE 0 END) as total_credits'),
                DB::raw('SUM(CASE WHEN Tr_Mode = "D" THEN Tr_Days ELSE 0 END) as total_debits')
            )
            ->where('FY_Id', $fromFyId)
            ->groupBy('Emp_Id', 'LT_id');

        if ($empId) {
            $query->where('Emp_Id', $empId);
        }
        if ($ltId) {
            $query->where('LT_id', $ltId);
        }

        $balances = $query->get();

        $carriedForward = [];
        $lapsed = [];

        foreach ($balances as $balance) {
            // ✅ CORRECT: Calculate net balance
            $netBalance = $balance->total_credits - $balance->total_debits;

            // ✅ Skip if no balance or negative
            if ($netBalance <= 0) {
                continue;
            }

            $leaveType = LeaveTypeMaster::find($balance->LT_id);
            $maxCarryForward = $leaveType->Max_CfAl ?? 0;
            $isCarryForwardAllowed = $leaveType->Is_CryFw ?? false;

            if (!$isCarryForwardAllowed || $maxCarryForward == 0) {
                // If carry forward not allowed, all days lapse
                if ($netBalance > 0) {
                    $lapseTrans = LeaveTrans::create([
                        'LT_id' => $balance->LT_id,
                        'LT_Date' => $fromFy->Ed_Date,
                        'Emp_Id' => $balance->Emp_Id,
                        'FY_Id' => $fromFyId,
                        'Tr_Type' => 'LAPSE-D',
                        'Tr_Date' => now(),
                        'Tr_Days' => $netBalance,
                        'Paid' => 0,
                        'UnPaid' => $netBalance,
                        'Tr_Mode' => 'D',
                        'Tr_HDT' => now(),
                        'Status' => $this->getStatusId('APPROVED'),
                        'created_at' => now(),
                        'updated_at' => now(),
                    ]);

                    $lapsed[] = [
                        'emp_id' => $balance->Emp_Id,
                        'lt_id' => $balance->LT_id,
                        'leave_type' => $leaveType->LT_name,
                        'lapsed_days' => $netBalance,
                        'reason' => 'Carry forward not allowed for this leave type',
                        'lapse_transaction_id' => $lapseTrans->LTr_id
                    ];
                }
                continue;
            }

            // ✅ CORRECT: Carry forward based on NET BALANCE
            $carryForwardDays = min($netBalance, $maxCarryForward);
            $lapseDays = $netBalance - $carryForwardDays;

            // Create CARRY_FORWARD transaction
            if ($carryForwardDays > 0) {
                $carryForwardTrans = LeaveTrans::create([
                    'LT_id' => $balance->LT_id,
                    'LT_Date' => $fromFy->Ed_Date,
                    'Emp_Id' => $balance->Emp_Id,
                    'FY_Id' => $fromFyId,
                    'Tr_Type' => 'CARRY_FORWARD-D',
                    'Tr_Date' => now(),
                    'Tr_Days' => $carryForwardDays,
                    'Paid' => $carryForwardDays,
                    'UnPaid' => 0,
                    'Tr_Mode' => 'D',
                    'Tr_HDT' => now(),
                    'Status' => $this->getStatusId('APPROVED'),
                    'created_at' => now(),
                    'updated_at' => now(),
                ]);

                // Create OPENING_CREDIT transaction in new FY
                $openingCreditTrans = LeaveTrans::create([
                    'LT_id' => $balance->LT_id,
                    'LT_Date' => $toFy->St_Date,
                    'Emp_Id' => $balance->Emp_Id,
                    'FY_Id' => $toFyId,
                    'Tr_Type' => 'OPENING_CREDIT-C',
                    'Tr_Date' => now(),
                    'Tr_Days' => $carryForwardDays,
                    'Paid' => $carryForwardDays,
                    'UnPaid' => 0,
                    'Tr_Mode' => 'C',
                    'Tr_HDT' => now(),
                    'Status' => $this->getStatusId('APPROVED'),
                    'created_at' => now(),
                    'updated_at' => now(),
                ]);

                $carriedForward[] = [
                    'emp_id' => $balance->Emp_Id,
                    'lt_id' => $balance->LT_id,
                    'leave_type' => $leaveType->LT_name,
                    'carry_forward_days' => $carryForwardDays,
                    'net_balance' => $netBalance,
                    'max_allowed' => $maxCarryForward,
                    'carry_forward_transaction_id' => $carryForwardTrans->LTr_id,
                    'opening_credit_transaction_id' => $openingCreditTrans->LTr_id
                ];
            }

            // Create LAPSE transaction for expired days
            if ($lapseDays > 0) {
                $lapseTrans = LeaveTrans::create([
                    'LT_id' => $balance->LT_id,
                    'LT_Date' => $fromFy->Ed_Date,
                    'Emp_Id' => $balance->Emp_Id,
                    'FY_Id' => $fromFyId,
                    'Tr_Type' => 'LAPSE-D',
                    'Tr_Date' => now(),
                    'Tr_Days' => $lapseDays,
                    'Paid' => 0,
                    'UnPaid' => $lapseDays,
                    'Tr_Mode' => 'D',
                    'Tr_HDT' => now(),
                    'Status' => $this->getStatusId('APPROVED'),
                    'created_at' => now(),
                    'updated_at' => now(),
                ]);

                $lapsed[] = [
                    'emp_id' => $balance->Emp_Id,
                    'lt_id' => $balance->LT_id,
                    'leave_type' => $leaveType->LT_name,
                    'lapsed_days' => $lapseDays,
                    'net_balance' => $netBalance,
                    'carried_days' => $carryForwardDays,
                    'reason' => $carryForwardDays > 0 ? 'Exceeded maximum carry forward limit' : 'No carry forward allowed',
                    'lapse_transaction_id' => $lapseTrans->LTr_id
                ];
            }
        }

        DB::commit();

        return response()->json([
            'success' => true,
            'message' => "Carry forward completed. " . count($carriedForward) . " records carried forward (" . array_sum(array_column($carriedForward, 'carry_forward_days')) . " days), " . count($lapsed) . " records lapsed (" . array_sum(array_column($lapsed, 'lapsed_days')) . " days)",
            'data' => [
                'from_fy' => $fromFy->FY_Code ?? $fromFy->FY_Name,
                'from_fy_id' => $fromFyId,
                'to_fy' => $toFy->FY_Code ?? $toFy->FY_Name,
                'to_fy_id' => $toFyId,
                'processed_date' => now(),
                'total_employees_processed' => count($carriedForward) + count($lapsed),
                'total_carried_forward_days' => array_sum(array_column($carriedForward, 'carry_forward_days')),
                'total_lapsed_days' => array_sum(array_column($lapsed, 'lapsed_days')),
                'carried_forward' => $carriedForward,
                'lapsed' => $lapsed
            ]
        ]);

    } catch (\Exception $e) {
        DB::rollBack();
        Log::error('Leave carry forward error: ' . 'Something went wrong. Please try again later.');
        return response()->json([
            'success' => false,
            'message' => 'Something went wrong. Please try again later.'
        ]);
    }
}

    /**
     * ✅ GET CARRY FORWARD ELIGIBILITY
     * Check which employees are eligible for carry forward
     *
     * PAYLOAD:
     * {
     *     "from_fy_id": 2,
     *     "emp_id": 72,
     *     "lt_id": 1
     * }
     */
    public function getCarryForwardEligibility(Request $request)
    {
        try {
            $validated = $request->validate([
                'from_fy_id' => 'required|exists:fyear_master,FY_Id',
                'emp_id' => 'nullable|exists:employee_master,Emp_Id',
                'lt_id' => 'nullable|exists:leave_type_master,LT_id',
            ]);

            $fromFyId = $validated['from_fy_id'];
            $empId = $validated['emp_id'] ?? null;
            $ltId = $validated['lt_id'] ?? null;

            $query = LeaveTrans::select(
                    'Emp_Id',
                    'LT_id',
                    DB::raw('SUM(CASE WHEN Tr_Mode = "C" THEN Tr_Days ELSE 0 END) as total_credits'),
                    DB::raw('SUM(CASE WHEN Tr_Mode = "D" THEN Tr_Days ELSE 0 END) as total_debits')
                )
                ->where('FY_Id', $fromFyId)
                ->groupBy('Emp_Id', 'LT_id');

            if ($empId) {
                $query->where('Emp_Id', $empId);
            }
            if ($ltId) {
                $query->where('LT_id', $ltId);
            }

            $balances = $query->get();
            $eligibility = [];

            foreach ($balances as $balance) {
                $remainingDays = $balance->total_credits - $balance->total_debits;

                if ($remainingDays <= 0) {
                    continue;
                }

                $leaveType = LeaveTypeMaster::find($balance->LT_id);
                $maxCarryForward = $leaveType->Max_CfAl ?? 999;
                $isCarryForwardAllowed = $leaveType->Is_CryFw ?? true;

                $employee = \App\Models\EmployeeMaster::find($balance->Emp_Id);

                $eligibility[] = [
                    'emp_id' => $balance->Emp_Id,
                    'employee_name' => $employee ? $employee->Emp_Name : null,
                    'lt_id' => $balance->LT_id,
                    'leave_type' => $leaveType ? $leaveType->LT_name : null,
                    'remaining_days' => $remainingDays,
                    'max_carry_forward_allowed' => $maxCarryForward,
                    'carry_forward_allowed' => $isCarryForwardAllowed,
                    'eligible_days' => $isCarryForwardAllowed ? min($remainingDays, $maxCarryForward) : 0,
                    'will_lapse' => $isCarryForwardAllowed ? max(0, $remainingDays - $maxCarryForward) : $remainingDays,
                ];
            }

            return response()->json([
                'success' => true,
                'data' => [
                    'from_fy_id' => $fromFyId,
                    'total_eligible' => count($eligibility),
                    'eligibility_list' => $eligibility
                ]
            ]);

        } catch (\Exception $e) {
            Log::error('Get carry forward eligibility error: ' . 'Something went wrong. Please try again later.');
            return response()->json([
                'success' => false,
                'message' => 'Something went wrong. Please try again later.'
            ]);
        }
    }

    /**
     * ✅ RESTORE LEAVE
     */
    public function restoreLeave(Request $request, $id)
    {
        try {
            DB::beginTransaction();

            $data = LeaveApplication::withTrashed()->find($id);

            if (!$data) {
                return response()->json([
                    'success' => false,
                    'message' => 'Record not found'
                ]);
            }

            $validated = $request->validate([
                'Status' => 'nullable|exists:action_master,Act_Id',
                'Status_Remark' => 'nullable|string',
            ]);

            $data->restore();

            $updateData = [
                'deleted_at' => null,
                'updated_at' => now(),
            ];

            if (!empty($validated['Status'])) {
                $updateData['Status'] = $validated['Status'];
            }

            if (!empty($validated['Status_Remark'])) {
                $updateData['Status_Remark'] = $validated['Status_Remark'];
            }

            $data->update($updateData);

            DB::commit();

            return response()->json([
                'success' => true,
                'message' => 'Leave restored successfully',
                'data' => $data->load(['statusData'])
            ]);
        } catch (\Exception $e) {
            DB::rollBack();
            Log::error('LeaveApplication restore error: ' . 'Something went wrong. Please try again later.');
            return response()->json([
                'success' => false,
                'message' => 'Something went wrong. Please try again later.'
            ]);
        }
    }

    /**
     * ✅ FORCE DELETE
     */
    public function forceDeleteLeave($id)
    {
        try {
            $data = LeaveApplication::withTrashed()->find($id);

            if (!$data) {
                return response()->json([
                    'success' => false,
                    'message' => 'Record not found'
                ]);
            }

            $data->forceDelete();

            return response()->json([
                'success' => true,
                'message' => 'Leave permanently deleted'
            ]);
        } catch (\Exception $e) {
            Log::error('LeaveApplication forceDelete error: ' . 'Something went wrong. Please try again later.');
            return response()->json([
                'success' => false,
                'message' => 'Something went wrong. Please try again later.'
            ]);
        }
    }

    /**
     * ✅ GET LEAVE BALANCE
     */
    public function getBalance(Request $request)
    {
        try {
            $validated = $request->validate([
                'emp_id' => 'required|exists:employee_master,Emp_Id',
                'lt_id' => 'required|exists:leave_type_master,LT_id',
            ]);

            $balance = $this->getEmployeeLeaveBalance($validated['emp_id'], $validated['lt_id']);

            return response()->json([
                'success' => true,
                'data' => [
                    'emp_id' => $validated['emp_id'],
                    'lt_id' => $validated['lt_id'],
                    'available_balance' => $balance
                ]
            ]);
        } catch (\Exception $e) {
            Log::error('LeaveApplication getBalance error: ' . 'Something went wrong. Please try again later.');
            return response()->json([
                'success' => false,
                'message' => 'Something went wrong. Please try again later.'
            ]);
        }
    }

    /**
     * ✅ GET TRANSACTION HISTORY
     */
    public function getTransactionHistory($id)
    {
        try {
            $leave = LeaveApplication::find($id);

            if (!$leave) {
                return response()->json([
                    'success' => false,
                    'message' => 'Leave record not found'
                ]);
            }

            $transactions = LeaveTrans::where('Emp_Id', $leave->Emp_Id)
                ->where('LT_id', $leave->LT_id)
                ->orderBy('created_at', 'desc')
                ->get();

            return response()->json([
                'success' => true,
                'data' => [
                    'leave' => $leave,
                    'transactions' => $transactions
                ]
            ]);
        } catch (\Exception $e) {
            Log::error('LeaveApplication getTransactionHistory error: ' . 'Something went wrong. Please try again later.');
            return response()->json([
                'success' => false,
                'message' => 'Something went wrong. Please try again later.'
            ]);
        }
    }

    // ==================== PRIVATE HELPER METHODS ====================

    private function getEmployeeLeaveBalance($empId, $ltId)
    {
        $credits = LeaveTrans::where('Emp_Id', $empId)
            ->where('LT_id', $ltId)
            ->where('Tr_Mode', 'C')
            ->sum('Tr_Days');

        $debits = LeaveTrans::where('Emp_Id', $empId)
            ->where('LT_id', $ltId)
            ->where('Tr_Mode', 'D')
            ->sum('Tr_Days');

        return $credits - $debits;
    }

    private function getCurrentFinancialYear()
    {
        $currentDate = date('Y-m-d');

        $fy = FyearMaster::where('St_Date', '<=', $currentDate)
            ->where('Ed_Date', '>=', $currentDate)
            ->where('Status', 'ACTIVE')
            ->first();

        if (!$fy) {
            $fy = FyearMaster::where('Status', 'ACTIVE')
                ->orderBy('FY_Id', 'desc')
                ->first();
        }

        return $fy;
    }

    private function createDebitTransaction($leaveApp, $statusId)
    {
        $fy = $this->getCurrentFinancialYear();

        if ($fy) {
            LeaveTrans::create([
                'LT_id' => $leaveApp->LT_id,
                'LT_Date' => $leaveApp->Fr_Date,
                'Emp_Id' => $leaveApp->Emp_Id,
                'FY_Id' => $fy->FY_Id,
                'Tr_Type' => 'DEBIT_APPLIED-D',
                'Tr_Date' => now(),
                'Tr_Days' => $leaveApp->Tot_Days,
                'Paid' => $leaveApp->Paid,
                'UnPaid' => $leaveApp->UnPaid,
                'Tr_Mode' => 'D',
                'Tr_HDT' => now(),
                'Status' => $statusId,
                'created_at' => now(),
                'updated_at' => now(),
            ]);

            Log::info("Debit transaction created for leave ID: {$leaveApp->LA_id}");
        }
    }

    private function createRecallTransaction($leaveApp, $statusId)
    {
        $fy = $this->getCurrentFinancialYear();

        if ($fy) {
            LeaveTrans::create([
                'LT_id' => $leaveApp->LT_id,
                'LT_Date' => $leaveApp->Fr_Date,
                'Emp_Id' => $leaveApp->Emp_Id,
                'FY_Id' => $fy->FY_Id,
                'Tr_Type' => 'DEBIT_RECALL-C',
                'Tr_Date' => now(),
                'Tr_Days' => $leaveApp->Tot_Days,
                'Paid' => $leaveApp->Paid,
                'UnPaid' => $leaveApp->UnPaid,
                'Tr_Mode' => 'C',
                'Tr_HDT' => now(),
                'Status' => $statusId,
                'created_at' => now(),
                'updated_at' => now(),
            ]);

            Log::info("Recall transaction created for leave ID: {$leaveApp->LA_id}");
        }
    }

    private function createCancelTransaction($leaveApp, $cancelledDays, $cancelledPaid, $cancelledUnpaid, $statusId)
    {
        $fy = $this->getCurrentFinancialYear();

        if ($fy) {
            LeaveTrans::create([
                'LT_id' => $leaveApp->LT_id,
                'LT_Date' => $leaveApp->Fr_Date,
                'Emp_Id' => $leaveApp->Emp_Id,
                'FY_Id' => $fy->FY_Id,
                'Tr_Type' => 'DEBIT_CANCELLED-C',
                'Tr_Date' => now(),
                'Tr_Days' => $cancelledDays,
                'Paid' => $cancelledPaid,
                'UnPaid' => $cancelledUnpaid,
                'Tr_Mode' => 'C',
                'Tr_HDT' => now(),
                'Status' => $statusId,
                'created_at' => now(),
                'updated_at' => now(),
            ]);

            Log::info("Cancel transaction created for leave ID: {$leaveApp->LA_id}");
        }
    }

    private function createEnhanceTransaction($leaveApp, $additionalDays, $additionalPaid, $additionalUnpaid, $statusId)
    {
        $fy = $this->getCurrentFinancialYear();

        if ($fy) {
            LeaveTrans::create([
                'LT_id' => $leaveApp->LT_id,
                'LT_Date' => $leaveApp->Fr_Date,
                'Emp_Id' => $leaveApp->Emp_Id,
                'FY_Id' => $fy->FY_Id,
                'Tr_Type' => 'DEBIT_APPLIED-D',
                'Tr_Date' => now(),
                'Tr_Days' => $additionalDays,
                'Paid' => $additionalPaid,
                'UnPaid' => $additionalUnpaid,
                'Tr_Mode' => 'D',
                'Tr_HDT' => now(),
                'Status' => $statusId,
                'created_at' => now(),
                'updated_at' => now(),
            ]);

            Log::info("Enhance transaction created for leave ID: {$leaveApp->LA_id}");
        }
    }

    private function calculateWorkingDays($startDate, $endDate)
    {
        $start = new \DateTime($startDate);
        $end = new \DateTime($endDate);
        $end->modify('+1 day');

        $interval = new \DateInterval('P1D');
        $period = new \DatePeriod($start, $interval, $end);

        $workingDays = 0;
        foreach ($period as $date) {
            if ($date->format('N') < 6) {
                $workingDays++;
            }
        }

        return $workingDays;
    }


        // ==================== LEAVE ENCASHMENT METHODS ====================

    /**
 * ✅ GET ENCASHMENT ELIGIBILITY
 * Check which employees are eligible for leave encashment
 *
 * PAYLOAD (Query Params):
 * GET /api/leave-encashment/eligibility?emp_id=72&fy_id=3&lt_id=2
 */
public function getEncashmentEligibility(Request $request)
{
    try {
        $validated = $request->validate([
            'emp_id' => 'nullable|exists:employee_master,Emp_Id',
            'fy_id' => 'nullable|exists:fyear_master,FY_Id',
            'lt_id' => 'nullable|exists:leave_type_master,LT_id',
        ]);

        $empId = $validated['emp_id'] ?? null;
        $fyId = $validated['fy_id'] ?? null;
        $ltId = $validated['lt_id'] ?? null;

        // Get current financial year if not specified
        if (!$fyId) {
            $currentFy = $this->getCurrentFinancialYear();
            $fyId = $currentFy ? $currentFy->FY_Id : null;
        }

        if (!$fyId) {
            return response()->json([
                'success' => false,
                'message' => 'No active financial year found'
            ], 404);
        }

        // Query to get leave balances
        $query = LeaveTrans::select(
                'Emp_Id',
                'LT_id',
                DB::raw('SUM(CASE WHEN Tr_Mode = "C" THEN Tr_Days ELSE 0 END) as total_credits'),
                DB::raw('SUM(CASE WHEN Tr_Mode = "D" THEN Tr_Days ELSE 0 END) as total_debits')
            )
            ->where('FY_Id', $fyId)
            ->groupBy('Emp_Id', 'LT_id');

        if ($empId) {
            $query->where('Emp_Id', $empId);
        }
        if ($ltId) {
            $query->where('LT_id', $ltId);
        }

        $balances = $query->get();
        $eligibility = [];

        foreach ($balances as $balance) {
            $remainingDays = $balance->total_credits - $balance->total_debits;

            if ($remainingDays <= 0) {
                continue;
            }

            // Get leave type details
            $leaveType = LeaveTypeMaster::find($balance->LT_id);

            if (!$leaveType) {
                continue;
            }

            // Get employee details for service days calculation
            $employee = \App\Models\EmployeeMaster::find($balance->Emp_Id);

            // Calculate employee service days
            $serviceDays = 0;
            $serviceEligibility = false;
            $minServiceDays = $leaveType->Min_Service_Days ?? 0;
            $joiningDate = null;

            if ($employee && $employee->created_at) {
                $joiningDate = $employee->created_at;
                $serviceDays = now()->diffInDays($joiningDate);
                $serviceEligibility = ($minServiceDays == 0) || ($serviceDays >= $minServiceDays);
            } else {
                // If no joining date, assume eligible (or set based on business rule)
                $serviceEligibility = true;
            }

            // Check if encashment is allowed for this leave type
            $isEncashAllowed = $leaveType->Is_Encash ?? false;
            $maxEncashDays = $leaveType->Max_Encash_Days ?? 0;

            // Calculate encashable days
            $encashableDays = 0;
            $remarks = [];

            if (!$isEncashAllowed) {
                $remarks[] = "Encashment not allowed for this leave type";
            } elseif (!$serviceEligibility) {
                $remarks[] = "Employee not eligible yet. Needs {$minServiceDays} days of service. Current service: {$serviceDays} days";
            } else {
                if ($maxEncashDays > 0) {
                    $encashableDays = min($remainingDays, $maxEncashDays);
                    if ($remainingDays > $maxEncashDays) {
                        $remarks[] = "Only {$maxEncashDays} days can be encashed (max limit). {$remainingDays} days available";
                    } else {
                        $remarks[] = "Eligible for encashment";
                    }
                } else {
                    $encashableDays = $remainingDays;
                    $remarks[] = "Eligible for encashment (unlimited)";
                }
            }

            // Calculate estimated amount
            $dailyRate = $this->getEmployeeDailyRate($balance->Emp_Id);
            $estimatedAmount = $encashableDays * $dailyRate;

            // Get employee joining date
            $joiningDateFormat = $joiningDate ? $joiningDate->format('Y-m-d') : null;

            $eligibility[] = [
                'emp_id' => $balance->Emp_Id,
                'employee_name' => $employee ? $employee->Emp_Name : null,
                'employee_code' => $employee ? $employee->Emp_Code : null,
                'joining_date' => $joiningDateFormat,
                'service_days' => $serviceDays,
                'service_years' => round($serviceDays / 365, 2),
                'min_service_days_required' => $minServiceDays,
                'service_eligibility' => $serviceEligibility,
                'lt_id' => $balance->LT_id,
                'leave_type' => $leaveType->LT_name,
                'leave_type_code' => $leaveType->LT_code,
                'remaining_days' => $remainingDays,
                'encashment_allowed' => $isEncashAllowed,
                'max_encash_days' => $maxEncashDays,
                'encashable_days' => $encashableDays,
                'daily_rate' => $dailyRate,
                'estimated_amount' => $estimatedAmount,
                'remarks' => implode(' | ', $remarks),
                'can_encash' => ($isEncashAllowed && $serviceEligibility && $encashableDays > 0)
            ];
        }

        // Sort by encashable days descending
        usort($eligibility, function($a, $b) {
            return $b['encashable_days'] <=> $a['encashable_days'];
        });

        return response()->json([
            'success' => true,
            'data' => [
                'fy_id' => $fyId,
                'total_eligible' => count($eligibility),
                'total_encashable_days' => array_sum(array_column($eligibility, 'encashable_days')),
                'total_estimated_amount' => array_sum(array_column($eligibility, 'estimated_amount')),
                'eligibility_list' => $eligibility
            ]
        ]);

    } catch (\Exception $e) {
        Log::error('Get encashment eligibility error: ' . 'Something went wrong. Please try again later.');
        return response()->json([
            'success' => false,
            'message' => 'Something went wrong. Please try again later.'
        ]);
    }
}

    /**
     * ✅ PROCESS LEAVE ENCASHMENT
     *
     * PAYLOAD:
     * {
     *     "emp_id": 72,
     *     "lt_id": 1,
     *     "fy_id": 3,
     *     "encash_days": 10,
     *     "daily_rate": 1000,
     *     "total_amount": 10000,
     *     "payment_reference": "ENC/2026/001",
     *     "remarks": "Year-end encashment"
     * }
     */
    public function processEncashment(Request $request)
    {
        try {
            DB::beginTransaction();

            $validated = $request->validate([
                'emp_id' => 'required|exists:employee_master,Emp_Id',
                'lt_id' => 'required|exists:leave_type_master,LT_id',
                'fy_id' => 'required|exists:fyear_master,FY_Id',
                'encash_days' => 'required|numeric|min:0.5',
                'daily_rate' => 'nullable|numeric|min:0',
                'total_amount' => 'nullable|numeric|min:0',
                'payment_reference' => 'nullable|string|max:50',
                'payment_date' => 'nullable|date',
                'remarks' => 'nullable|string',
                'Status' => 'nullable|exists:action_master,Act_Id',
            ]);

            $empId = $validated['emp_id'];
            $ltId = $validated['lt_id'];
            $fyId = $validated['fy_id'];
            $encashDays = $validated['encash_days'];

            // Get current balance
            $currentBalance = $this->getEmployeeLeaveBalance($empId, $ltId);

            if ($currentBalance < $encashDays) {
                return response()->json([
                    'success' => false,
                    'message' => "Insufficient balance. Available: {$currentBalance} days, Requested: {$encashDays} days",
                    'data' => [
                        'available_balance' => $currentBalance,
                        'requested_encashment' => $encashDays
                    ]
                ], 422);
            }

            // Get leave type to check encashment limits
            $leaveType = LeaveTypeMaster::find($ltId);

            if (!$leaveType || !$leaveType->Is_Encash) {
                return response()->json([
                    'success' => false,
                    'message' => "Encashment not allowed for this leave type: {$leaveType->LT_name}",
                    'data' => [
                        'leave_type' => $leaveType ? $leaveType->LT_name : 'Unknown',
                        'encashment_allowed' => $leaveType ? $leaveType->Is_Encash : false
                    ]
                ], 422);
            }

            // Check max encashment limit
            $maxEncashDays = $leaveType->Max_Encash_Days ?? 0;
            if ($maxEncashDays > 0 && $encashDays > $maxEncashDays) {
                return response()->json([
                    'success' => false,
                    'message' => "Encashment days exceed maximum limit. Max allowed: {$maxEncashDays} days",
                    'data' => [
                        'max_allowed' => $maxEncashDays,
                        'requested' => $encashDays
                    ]
                ], 422);
            }

            // Calculate amounts
            $dailyRate = $validated['daily_rate'] ?? $this->getEmployeeDailyRate($empId);
            $totalAmount = $validated['total_amount'] ?? ($encashDays * $dailyRate);
            $paymentDate = $validated['payment_date'] ?? now();
            $statusId = $validated['Status'] ?? $this->getStatusId('APPROVED');

            // Get financial year
            $fy = FyearMaster::find($fyId);

            // ✅ CREATE ENCASHMENT TRANSACTION
            $transaction = LeaveTrans::create([
                'LT_id' => $ltId,
                'LT_Date' => $paymentDate,
                'Emp_Id' => $empId,
                'FY_Id' => $fyId,
                'Tr_Type' => 'ENCASHMENT-D',
                'Tr_Date' => now(),
                'Tr_Days' => $encashDays,
                'Paid' => $encashDays,
                'UnPaid' => 0,
                'Tr_Mode' => 'D',  // Debit - reduces balance
                'Tr_HDT' => now(),
                'Status' => $statusId,
                'created_at' => now(),
                'updated_at' => now(),
            ]);

            // Optional: Create encashment record in separate table (if you have one)
            // $this->createEncashmentRecord($validated, $transaction, $totalAmount);

            // Update leave application status if needed
            // Find any pending leave applications for this employee and mark as encashed

            DB::commit();

            // Calculate new balance
            $newBalance = $currentBalance - $encashDays;

            // Get employee details
            $employee = \App\Models\EmployeeMaster::find($empId);

            return response()->json([
                'success' => true,
                'message' => "Leave encashment processed successfully for {$encashDays} days",
                'data' => [
                    'transaction' => $transaction,
                    'employee' => [
                        'id' => $empId,
                        'name' => $employee ? $employee->Emp_Name : null,
                        'code' => $employee ? $employee->Emp_Code : null,
                    ],
                    'leave_type' => [
                        'id' => $ltId,
                        'name' => $leaveType->LT_name,
                    ],
                    'encashment_details' => [
                        'encash_days' => $encashDays,
                        'daily_rate' => $dailyRate,
                        'total_amount' => $totalAmount,
                        'payment_date' => $paymentDate,
                        'payment_reference' => $validated['payment_reference'] ?? null,
                    ],
                    'balance_details' => [
                        'previous_balance' => $currentBalance,
                        'encashed_days' => $encashDays,
                        'new_balance' => $newBalance,
                    ],
                    'remarks' => $validated['remarks'] ?? null,
                    'status_id' => $statusId
                ]
            ]);

        } catch (\Exception $e) {
            DB::rollBack();
            Log::error('Process encashment error: ' . 'Something went wrong. Please try again later.');
            return response()->json([
                'success' => false,
                'message' => 'Something went wrong. Please try again later.'
            ]);
        }
    }

    /**
     * ✅ GET ENCASHMENT HISTORY
     *
     * GET /api/leave-encashment/history/72
     */
    public function getEncashmentHistory($empId)
    {
        try {
            $transactions = LeaveTrans::where('Emp_Id', $empId)
                ->where('Tr_Type', 'ENCASHMENT-D')
                ->orderBy('created_at', 'desc')
                ->get();

            $totalEncashed = $transactions->sum('Tr_Days');
            $totalAmount = 0; // Calculate if you have amount stored

            $history = [];
            foreach ($transactions as $trans) {
                $leaveType = LeaveTypeMaster::find($trans->LT_id);
                $employee = \App\Models\EmployeeMaster::find($empId);

                $history[] = [
                    'transaction_id' => $trans->LTr_id,
                    'encash_date' => $trans->LT_Date,
                    'leave_type' => $leaveType ? $leaveType->LT_name : null,
                    'encash_days' => $trans->Tr_Days,
                    'paid_days' => $trans->Paid,
                    'status' => $this->getStatusName($trans->Status),
                    'created_at' => $trans->created_at,
                ];
            }

            return response()->json([
                'success' => true,
                'data' => [
                    'emp_id' => $empId,
                    'employee_name' => $employee ? $employee->Emp_Name : null,
                    'total_encashed_days' => $totalEncashed,
                    'total_encashments' => $transactions->count(),
                    'history' => $history
                ]
            ]);

        } catch (\Exception $e) {
            Log::error('Get encashment history error: ' . 'Something went wrong. Please try again later.');
            return response()->json([
                'success' => false,
                'message' => 'Something went wrong. Please try again later.'
            ]);
        }
    }

    // ==================== ENCASHMENT HELPER METHODS ====================

    /**
     * Get employee daily rate for encashment calculation
     */
    private function getEmployeeDailyRate($empId)
    {
        // Get employee monthly salary
        $employee = \App\Models\EmployeeMaster::find($empId);

        // Default daily rate calculation (monthly salary / 30)
        // You can customize this based on your payroll structure
        $monthlySalary = $employee->Monthly_Salary ?? 30000; // Default if not set

        return round($monthlySalary / 30, 2);
    }

    /**
     * Get encashment remark based on eligibility
     */
    private function getEncashmentRemark($isEncashAllowed, $remainingDays, $maxEncashDays)
    {
        if (!$isEncashAllowed) {
            return "Encashment not allowed for this leave type";
        }

        if ($maxEncashDays > 0 && $remainingDays > $maxEncashDays) {
            return "Only {$maxEncashDays} days can be encashed (max limit). {$remainingDays} days available";
        }

        return "Eligible for encashment";
    }
}
