@extends('layouts.horizontal', ['title' => 'Staff Deductions Report', 'topbarTitle' => 'Staff Deductions Report'])

@section('css')
@vite(['node_modules/datatables.net-bs5/css/dataTables.bootstrap5.min.css',
'node_modules/datatables.net-responsive-bs5/css/responsive.bootstrap5.min.css',
'node_modules/datatables.net-fixedcolumns-bs5/css/fixedColumns.bootstrap5.min.css',
'node_modules/datatables.net-fixedheader-bs5/css/fixedHeader.bootstrap5.min.css',
'node_modules/datatables.net-buttons-bs5/css/buttons.bootstrap5.min.css',
'node_modules/datatables.net-select-bs5/css/select.bootstrap5.min.css',
'node_modules/select2/dist/css/select2.min.css',
'node_modules/flatpickr/dist/flatpickr.min.css'])
<style>
    .card-background {
        background: #f8f9fa;
        border-radius: 10px;
        padding: 15px;
        margin-bottom: 15px;
        border: 1px solid #e9ecef;
    }
    .table-card {
        background: white;
        border-radius: 8px;
        overflow: hidden;
        box-shadow: 0 2px 4px rgba(0,0,0,0.05);
    }
    .table-card .table {
        margin-bottom: 0;
    }
    .table-card .table thead th {
        background-color: #f1f5f9;
        border-bottom: 2px solid #dee2e6;
        position: sticky;
        top: 0;
        z-index: 10;
    }
    .deduction-badge {
        padding: 6px 12px;
        font-size: 13px !important; 
        font-weight: 600;
        border-radius: 4px;
        margin: 3px;
        display: inline-block;
    }
    
   
    .deduction-breakdown {
        max-height: 150px;
        overflow-y: auto;
        font-size: 12px;
        padding: 8px;
        background-color: #f8f9fa;
        border-radius: 4px;
        border: 1px solid #e9ecef;
    }
    .total-row {
        background-color: #f8f9fa !important;
        font-weight: bold;
    }
    .amount-cell {
        text-align: right;
    }
    .staff-info-cell {
        background-color: #f8f9fa;
    }
    
   
    .badge-container {
        display: flex;
        flex-wrap: wrap;
        gap: 5px;
        margin-top: 5px;
    }
    
    .month-breakdown {
        font-size: 12px;
        color: #6c757d;
    }
    
    .business-selector-card {
        margin-bottom: 20px;
        border: 1px solid #dee2e6;
    }
    
    /* Form labels - made larger */
    .form-label {
        font-weight: 600;
        margin-bottom: 8px;
    }
    
    
    .table td, .table th {
        font-size: 14px !important; 
        vertical-align: middle !important;
    }
    
    /* Compact filter styling */
    .compact-filter-row {
        display: flex;
        align-items: center;
        gap: 15px;
        flex-wrap: wrap;
    }
    
    .filter-group {
        display: flex;
        align-items: center;
        gap: 8px;
    }
    
    .filter-group label {
        margin-bottom: 0;
        font-weight: 600;
        min-width: 120px;
        font-size: 14px !important; 
    }
    
    /* Select2 styling for better visibility */
    .select2-container .select2-selection {
        font-size: 14px !important;
        height: 38px !important;
    }
    
    .select2-container .select2-selection__rendered {
        line-height: 36px !important;
        font-size: 14px !important;
    }
    
    .select2-container .select2-selection__arrow {
        height: 36px !important;
    }
</style>
@endsection

@section('content')
<div class="container-fluid">
    <div class="row mb-4">
        <div class="col-sm-12">
            <div class="page-title-box d-flex align-items-center justify-content-between">
                <h2>Staff Deductions Report</h2>
                <div class="page-title-right d-flex align-items-center gap-3">
                    @php
                        $breadcrumb = getCurrentMenuBreadcrumb();
                    @endphp
                    @if(!empty($breadcrumb))
                        <nav aria-label="breadcrumb">
                            <ol class="breadcrumb mb-0 py-2">
                                @foreach($breadcrumb as $index => $item)
                                    @if($index === count($breadcrumb) - 1)
                                        <li class="breadcrumb-item active" aria-current="page">{{ $item }}</li>
                                    @else
                                        <li class="breadcrumb-item"><a href="javascript: void(0);">{{ $item }}</a></li>
                                    @endif
                                @endforeach
                            </ol>
                        </nav>
                    @endif
                </div>
            </div>
        </div>
    </div>

    <!-- BUSINESS SELECTOR SECTION - Placed at top separately like other reports -->
    @if($isHoUser)
    <div class="row mb-3">
        <div class="col-sm-12">
            <div class="card business-selector-card">
                <div class="card-body">
                    <div class="row align-items-center">
                        <div class="col-md-12">
                            <div class="d-flex align-items-center justify-content-between gap-3">
                                <div class="flex-grow-1">
                                    <label class="form-label">Select Business</label>
                                    <select class="form-control" id="businessSelector">
                                        <option value="">All Businesses (HO Accounts)</option>
                                        @foreach($businesses as $biz)
                                            <option value="{{ $biz->id_business }}" 
                                                {{ (session('business_id') == $biz->id_business) ? 'selected' : '' }}>
                                                {{ $biz->business_name }}
                                            </option>
                                        @endforeach
                                    </select>
                                </div>
                                <div>
                                    <button onclick="exportToExcel()" class="btn btn-outline-success">
                                        <iconify-icon icon="mdi:microsoft-excel"></iconify-icon> Export Excel
                                    </button>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
    @else
    <div class="row mb-3">
        <div class="col-sm-12">
            <div class="card">
                <div class="card-body">
                    <div class="d-flex justify-content-end">
                        <button onclick="exportToExcel()" class="btn btn-outline-success">
                            <iconify-icon icon="mdi:microsoft-excel"></iconify-icon> Export Excel
                        </button>
                    </div>
                </div>
            </div>
        </div>
    </div>
    @endif

    <!-- Filters Section -->
<div class="row mb-3">
    <div class="col-sm-12">
        <div class="card">
            <div class="card-body">
                <div class="row align-items-center">
                    <!-- Date Range: 4 columns -->
                    <div class="col-md-5">
                        <div class="mb-3">
                            <label class="form-label">Date Range</label>
                            <div class="input-group">
                                <input type="text" class="form-control" id="deductions_dateRange" name="deductions_date_range" placeholder="Select date range" readonly required>
                                <input type="hidden" id="deductions_start_date" name="deductions_start_date" value="{{ $startDate }}">
                                <input type="hidden" id="deductions_end_date" name="deductions_end_date" value="{{ $endDate }}">
                            </div>
                        </div>
                    </div>
                    
                    <!-- Staff: 4 columns -->
                    <div class="col-md-5">
                        <div class="mb-3">
                            <label class="form-label">Staff</label>
                            <select class="form-control select2" id="staffSelect" name="staff">
                                <option value="0">All Staff</option>
                                @foreach($staffList as $staffItem)
                                    <option value="{{ $staffItem->id_staff }}" 
                                        {{ $staffId == $staffItem->id_staff ? 'selected' : '' }}>
                                        {{ $staffItem->staff_fullname }}
                                    </option>
                                @endforeach
                            </select>
                        </div>
                    </div>
                    
                    <!-- Buttons: 4 columns -->
                    <div class="col-md-2">
                        <div class="mb-3">
                            <label class="form-label">&nbsp;</label>
                            <div class="d-flex gap-2 mt-1">
                                <button class="btn btn-sm btn-ghost-primary rounded-circle btn-icon" id="applyDateFilter" title="Search">
                                    <iconify-icon icon="mdi:magnify" width="30px"></iconify-icon>
                                </button>
                                <button class="btn btn-sm btn-ghost-primary rounded-circle btn-icon" id="resetDateFilter">
                                     <iconify-icon icon="mdi:refresh" width="30px"></iconify-icon>
                                </button>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

    <!-- Staff Deductions Report Table -->
    <div class="row">
        <div class="col-12">
            <div class="card table-card">
                <div class="card-body">
                    <div class="table-responsive">
                        <table id="staff_deductions_report_table" class="table table-bordered table-hover w-100">
                            <thead>
                                <tr>
                                    <th class="text-center staff-info-cell">Staff Name & ID</th>
                                    <th class="text-center">Business</th>
                                    <th class="text-center">Salary Type</th>
                                    <th class="text-center amount-cell">Basic Salary</th>
                                    <th class="text-center amount-cell">Loan Deduction</th>
                                    <th class="text-center amount-cell">Late Deduction</th>
                                    <th class="text-center amount-cell">Absent Deduction</th>
                                    <th class="text-center amount-cell">Tax Deduction</th>
                                    <th class="text-center amount-cell">Other Deductions</th>
                                    <th class="text-center amount-cell">Provident Fund</th>
                                    <th class="text-center amount-cell">Total Deductions</th>
                                    <th class="text-center">Deduction Breakdown</th>
                                </tr>
                            </thead>
                            <tbody id="deductionsDataBody">
                                <!-- Data will be loaded via JavaScript -->
                            </tbody>
                            <tfoot id="deductionsDataFooter">
                                <!-- Footer totals will be loaded via JavaScript -->
                            </tfoot>
                        </table>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

<div id="loadingOverlay" style="display: none; position: fixed; top: 0; left: 0; width: 100%; height: 100%; background-color: rgba(0,0,0,0.5); z-index: 9999; display: flex; justify-content: center; align-items: center;">
    <div class="spinner-border text-light" role="status">
        <span class="visually-hidden">Loading...</span>
    </div>
</div>
@endsection

@section('scripts')
<script>
    let deductionsFlatpickr;
    
    $(document).ready(function() {
        initializeDatePicker();
        initializeSelect2();
        setupEventHandlers();
        loadDeductionsData();
    });

    function initializeDatePicker() {
        const startDate = new Date('{{ $startDate }}');
        const endDate = new Date('{{ $endDate }}');
        
        deductionsFlatpickr = flatpickr("#deductions_dateRange", {
            mode: "range",
            dateFormat: "Y-m-d",
            defaultDate: [startDate, endDate],
            onChange: function(selectedDates, dateStr, instance) {
                if (selectedDates.length === 2) {
                    updateDeductionsDateRangeFields(selectedDates[0], selectedDates[1]);
                } else if (selectedDates.length === 1) {
                    // Waiting for second date selection
                } else {
                    $('#deductions_dateRange').val('');
                    $('#deductions_start_date').val('');
                    $('#deductions_end_date').val('');
                }
            },
        });
        
        updateDeductionsDateRangeFields(startDate, endDate);
    }

    function updateDeductionsDateRangeFields(startDate, endDate) {
        const startStr = formatDate(startDate);
        const endStr = formatDate(endDate);
        
        $('#deductions_dateRange').val(startStr + ' to ' + endStr);
        $('#deductions_start_date').val(startStr);
        $('#deductions_end_date').val(endStr);
    }

    function formatDate(date) {
        const d = new Date(date);
        const year = d.getFullYear();
        const month = String(d.getMonth() + 1).padStart(2, '0');
        const day = String(d.getDate()).padStart(2, '0');
        return `${year}-${month}-${day}`;
    }

    function initializeSelect2() {
        // Business selector
        $('#businessSelector').select2({
            width: '100%',
            placeholder: 'Select Business'
        });
        
        // Staff selector
        $('#staffSelect').select2({
            width: '100%',
            placeholder: 'Select Staff'
        });
    }

    function setupEventHandlers() {
        // Apply filter button
        $('#applyDateFilter').on('click', function() {
            loadDeductionsData();
        });
        
        // Reset filter button
        $('#resetDateFilter').on('click', function() {
            const startDate = new Date('{{ $startDate }}');
            const endDate = new Date('{{ $endDate }}');
            
            deductionsFlatpickr.setDate([startDate, endDate], true);
            updateDeductionsDateRangeFields(startDate, endDate);
            
            loadDeductionsData();
        });
        
        // Business selector change handler
        $('#businessSelector').on('change', function() {
            loadStaffByBusiness();
        });
        
        // Staff selector change handler
        $('#staffSelect').on('change', function() {
            loadDeductionsData();
        });
    }
    
    function loadStaffByBusiness() {
        const businessId = $('#businessSelector').val();
        
        if (!businessId) {
            $('#staffSelect').empty().append('<option value="0">All Staff</option>');
            $('#staffSelect').select2({
                width: '100%',
                placeholder: 'Select Staff'
            });
            $('#staffSelect').val('0').trigger('change');
            loadDeductionsData();
            return;
        }
        
        showLoading();
        
        $.ajax({
            url: "{{ route('hrm.get_staff_by_business') }}",
            type: 'GET',
            data: {
                business_id: businessId
            },
            success: function(response) {
                if (response.success) {
                    const staffSelect = $('#staffSelect');
                    staffSelect.empty();
                    staffSelect.append('<option value="0">All Staff</option>');
                    
                    response.staff.forEach(function(staff) {
                        staffSelect.append(`<option value="${staff.id_staff}">${staff.staff_fullname}</option>`);
                    });
                    
                    staffSelect.select2({
                        width: '100%',
                        placeholder: 'Select Staff'
                    });

                    staffSelect.val('0').trigger('change');
                    loadDeductionsData();
                }
            },
            error: function(xhr, status, error) {
                console.error('Error loading staff:', error);
                alert('Error loading staff data');
            },
            complete: function() {
                hideLoading();
            }
        });
    }
    
    function loadDeductionsData() {
        const businessId = $('#businessSelector').val();
        const staffId = $('#staffSelect').val();
        const startDate = $('#deductions_start_date').val();
        const endDate = $('#deductions_end_date').val();
        
        if (!startDate || !endDate) {
            Swal.fire({
                title: 'Error',
                text: 'Please select a date range first',
                icon: 'error',
                customClass: {
                    confirmButton: "btn btn-danger"
                },
                buttonsStyling: false
            });
            return;
        }
        
        showLoading();
        
        $.ajax({
            url: "{{ route('hrm.get_staff_deductions_data') }}",
            type: 'POST',
            data: {
                business_id: businessId,
                staff_id: staffId,
                start_date: startDate,
                end_date: endDate,
                _token: "{{ csrf_token() }}"
            },
            success: function(response) {
                if (response.success) {
                    renderDeductionsTable(response.data, response.totals);
                } else {
                    $('#deductionsDataBody').html('<tr><td colspan="12" class="text-center text-danger">Error loading data: ' + response.message + '</td></tr>');
                    $('#deductionsDataFooter').empty();
                }
            },
            error: function(xhr, status, error) {
                console.error('Error loading data:', error);
                $('#deductionsDataBody').html('<tr><td colspan="12" class="text-center text-danger">Error loading data. Please try again.</td></tr>');
                $('#deductionsDataFooter').empty();
                
                if (xhr.responseJSON && xhr.responseJSON.error) {
                    Swal.fire({
                        title: 'Error',
                        text: xhr.responseJSON.error,
                        icon: 'error',
                        customClass: {
                            confirmButton: "btn btn-danger"
                        },
                        buttonsStyling: false
                    });
                }
            },
            complete: function() {
                hideLoading();
            }
        });
    }

    function formatNumber(num) {
        return parseFloat(num).toFixed(2).replace(/\d(?=(\d{3})+\.)/g, '$&,');
    }

    function createBadge(type, amount) {
        const badgeClass = {
            'loan_deduction': 'loan-badge',
            'late_deduction': 'late-badge',
            'absent_deduction': 'absent-badge',
            'tax_amount': 'tax-badge',
            'other_deduction': 'other-badge',
            'provident_fund_payable': 'pf-badge',
            'total': 'total-badge'
        }[type] || 'other-badge';
        
        const displayType = type.replace(/_/g, ' ').replace('payable', '').trim();
        return `<span class="deduction-badge ${badgeClass}">${displayType.charAt(0).toUpperCase() + displayType.slice(1)}: ${formatNumber(amount)}</span>`;
    }

    function renderDeductionsTable(data, totals) {
        const tbody = $('#deductionsDataBody');
        tbody.empty();
        
        if (!data || data.length === 0) {
            tbody.html('<tr><td colspan="12" class="text-center">No deductions data found for the selected criteria</td></tr>');
            return;
        }
        
        // Process all data in the view, not in backend
        data.forEach(function(staff) {
            const staffRow = $('<tr>').addClass('staff-info').appendTo(tbody);
            
            // Calculate totals from all deduction types
            const totalDeductions = 
                (parseFloat(staff.loan_deduction) || 0) +
                (parseFloat(staff.late_deduction) || 0) +
                (parseFloat(staff.absent_deduction) || 0) +
                (parseFloat(staff.tax_deduction) || 0) +
                (parseFloat(staff.other_deductions) || 0) +
                (parseFloat(staff.provident_fund) || 0);
            
            // Calculate monthly breakdowns in the view
            const breakdownHtml = createBreakdownHtml(staff.monthly_details || {});
            
            // Staff Name & ID
            $('<td>')
                .addClass('staff-info-cell')
                .html(`<div class="d-flex flex-column">
                          <span class="fw-bold text-primary mb-1">${staff.staff_name}</span>
                          <small class="text-muted">ID: ${staff.staff_id}</small>
                       </div>`)
                .appendTo(staffRow);
            
            // Business name
            $('<td>')
                .addClass('align-middle')
                .html(`<div class="fw-semibold">${staff.business_name}</div>`)
                .appendTo(staffRow);
            
            // Salary type - Using system badge colors
            let salaryTypeBadge = '';
            if (staff.salary_type === 'monthly') {
                salaryTypeBadge = 'badge bg-primary';
            } else if (staff.salary_type === 'daily') {
                salaryTypeBadge = 'badge bg-primary';
            } else if (staff.salary_type === 'hourly') {
                salaryTypeBadge = 'badge bg-primary';
            } else {
                salaryTypeBadge = 'badge bg-primary';
            }
            
            $('<td>')
                .addClass('align-middle text-center')
                .html(`<span class="${salaryTypeBadge} p-2 fw-bold">${staff.salary_type}</span>`)
                .appendTo(staffRow);
            
            // Basic salary
            $('<td>')
                .addClass('align-middle amount-cell')
                .html(formatNumber(staff.staff_salary))
                .appendTo(staffRow);
            
            // Loan deduction
            $('<td>')
                .addClass('align-middle amount-cell')
                .html(formatNumber(staff.loan_deduction))
                .appendTo(staffRow);
            
            // Late deduction
            $('<td>')
                .addClass('align-middle amount-cell')
                .html(formatNumber(staff.late_deduction))
                .appendTo(staffRow);
            
            // Absent deduction
            $('<td>')
                .addClass('align-middle amount-cell')
                .html(formatNumber(staff.absent_deduction))
                .appendTo(staffRow);
            
            // Tax deduction
            $('<td>')
                .addClass('align-middle amount-cell')
                .html(formatNumber(staff.tax_deduction))
                .appendTo(staffRow);
            
            // Other deductions
            $('<td>')
                .addClass('align-middle amount-cell')
                .html(formatNumber(staff.other_deductions))
                .appendTo(staffRow);
            
            // Provident fund
            $('<td>')
                .addClass('align-middle amount-cell')
                .html(formatNumber(staff.provident_fund))
                .appendTo(staffRow);
            
            // Total deductions
            $('<td>')
                .addClass('align-middle amount-cell fw-bold')
                .html(`<span class="total-badge">${formatNumber(totalDeductions)}</span>`)
                .appendTo(staffRow);
            
            // Deduction breakdown
            $('<td>')
                .addClass('align-middle')
                .html(breakdownHtml)
                .appendTo(staffRow);
        });
        
        // Calculate and render totals in the view
        renderFooterTotals(totals);
    }

    function createBreakdownHtml(monthlyDetails) {
        let html = '<div class="deduction-breakdown">';
        
        if (Object.keys(monthlyDetails).length === 0) {
            html += '<span class="text-muted">No deductions found</span>';
        } else {
            // Process each month in the view
            Object.entries(monthlyDetails).forEach(([month, data]) => {
                if (data.total > 0) {
                    html += '<div class="mb-2">';
                    html += '<strong>' + month + ':</strong> ' + formatNumber(data.total);
                    html += '<div class="small text-muted ps-2">';
                    
                    // Process each deduction type for this month
                    Object.entries(data).forEach(([type, amount]) => {
                        if (type !== 'total' && amount > 0) {
                            const displayType = type.replace(/_/g, ' ').replace('payable', '').trim();
                            html += '<div>' + displayType.charAt(0).toUpperCase() + displayType.slice(1) + ': ' + formatNumber(amount) + '</div>';
                        }
                    });
                    
                    html += '</div>';
                    html += '</div>';
                }
            });
        }
        
        html += '</div>';
        return html;
    }

    function renderFooterTotals(totals) {
        const tfoot = $('#deductionsDataFooter');
        tfoot.empty();
        
        const totalsRow = $('<tr>').addClass('total-row').appendTo(tfoot);
        
        $('<td>')
            .addClass('text-end fw-bold')
            .html('<div>GRAND TOTALS</div>')
            .attr('colspan', 3)
            .appendTo(totalsRow);
        
        // Basic salary (not totaled)
        $('<td>')
            .addClass('amount-cell')
            .html('-')
            .appendTo(totalsRow);
        
        // Loan deduction total
        $('<td>')
            .addClass('amount-cell')
            .html(formatNumber(totals.total_loan_deduction))
            .appendTo(totalsRow);
        
        // Late deduction total
        $('<td>')
            .addClass('amount-cell')
            .html(formatNumber(totals.total_late_deduction))
            .appendTo(totalsRow);
        
        // Absent deduction total
        $('<td>')
            .addClass('amount-cell')
            .html(formatNumber(totals.total_absent_deduction))
            .appendTo(totalsRow);
        
        // Tax deduction total
        $('<td>')
            .addClass('amount-cell')
            .html(formatNumber(totals.total_tax_deduction))
            .appendTo(totalsRow);
        
        // Other deductions total
        $('<td>')
            .addClass('amount-cell')
            .html(formatNumber(totals.total_other_deductions))
            .appendTo(totalsRow);
        
        // Provident fund total
        $('<td>')
            .addClass('amount-cell')
            .html(formatNumber(totals.total_provident_fund))
            .appendTo(totalsRow);
        
        // Grand total
        $('<td>')
            .addClass('amount-cell fw-bold')
            .html(`<span class="total-badge">${formatNumber(totals.grand_total)}</span>`)
            .appendTo(totalsRow);
        
        $('<td>').appendTo(totalsRow);
    }

    function exportToExcel() {
        const businessId = $('#businessSelector').val();
        const staffId = $('#staffSelect').val();
        const startDate = $('#deductions_start_date').val();
        const endDate = $('#deductions_end_date').val();
        
        if (!startDate || !endDate) {
            Swal.fire({
                title: 'Error',
                text: 'Please select a date range first',
                icon: 'error',
                customClass: {
                    confirmButton: "btn btn-danger"
                },
                buttonsStyling: false
            });
            return;
        }
        
        showLoading();
        
        $.ajax({
            url: "{{ route('hrm.get_staff_deductions_data') }}",
            type: 'POST',
            data: {
                business_id: businessId,
                staff_id: staffId,
                start_date: startDate,
                end_date: endDate,
                _token: "{{ csrf_token() }}"
            },
            success: function(response) {
                if (response.success) {
                    exportDataToExcelSimple(response.data, response.totals, startDate, endDate);
                }
            },
            error: function(xhr, status, error) {
                console.error('Error exporting data:', error);
                Swal.fire({
                    title: 'Error',
                    text: 'Error exporting data',
                    icon: 'error',
                    customClass: {
                        confirmButton: "btn btn-danger"
                    },
                    buttonsStyling: false
                });
            },
            complete: function() {
                hideLoading();
            }
        });
    }

    function exportDataToExcelSimple(data, totals, startDate, endDate) {
        try {
            // Create HTML table for export
            let html = '<table border="1">';
            
            // Add headers
            html += '<thead><tr>';
            html += '<th>Staff ID</th>';
            html += '<th>Staff Name</th>';
            html += '<th>Business</th>';
            html += '<th>Salary Type</th>';
            html += '<th>Basic Salary</th>';
            html += '<th>Loan Deduction</th>';
            html += '<th>Late Deduction</th>';
            html += '<th>Absent Deduction</th>';
            html += '<th>Tax Deduction</th>';
            html += '<th>Other Deductions</th>';
            html += '<th>Provident Fund</th>';
            html += '<th>Total Deductions</th>';
            html += '</tr></thead>';
            
            // Add data rows
            html += '<tbody>';
            data.forEach(function(staff) {
                const totalDeductions = 
                    (parseFloat(staff.loan_deduction) || 0) +
                    (parseFloat(staff.late_deduction) || 0) +
                    (parseFloat(staff.absent_deduction) || 0) +
                    (parseFloat(staff.tax_deduction) || 0) +
                    (parseFloat(staff.other_deductions) || 0) +
                    (parseFloat(staff.provident_fund) || 0);
                
                html += '<tr>';
                html += `<td>${staff.staff_id}</td>`;
                html += `<td>${staff.staff_name}</td>`;
                html += `<td>${staff.business_name}</td>`;
                html += `<td>${staff.salary_type}</td>`;
                html += `<td>${parseFloat(staff.staff_salary).toFixed(2)}</td>`;
                html += `<td>${parseFloat(staff.loan_deduction).toFixed(2)}</td>`;
                html += `<td>${parseFloat(staff.late_deduction).toFixed(2)}</td>`;
                html += `<td>${parseFloat(staff.absent_deduction).toFixed(2)}</td>`;
                html += `<td>${parseFloat(staff.tax_deduction).toFixed(2)}</td>`;
                html += `<td>${parseFloat(staff.other_deductions).toFixed(2)}</td>`;
                html += `<td>${parseFloat(staff.provident_fund).toFixed(2)}</td>`;
                html += `<td>${parseFloat(totalDeductions).toFixed(2)}</td>`;
                html += '</tr>';
            });
            html += '</tbody>';
            
            // Add totals row
            html += '<tfoot><tr>';
            html += `<td colspan="4" style="text-align:right;font-weight:bold;">GRAND TOTALS:</td>`;
            html += '<td>-</td>';
            html += `<td>${parseFloat(totals.total_loan_deduction).toFixed(2)}</td>`;
            html += `<td>${parseFloat(totals.total_late_deduction).toFixed(2)}</td>`;
            html += `<td>${parseFloat(totals.total_absent_deduction).toFixed(2)}</td>`;
            html += `<td>${parseFloat(totals.total_tax_deduction).toFixed(2)}</td>`;
            html += `<td>${parseFloat(totals.total_other_deductions).toFixed(2)}</td>`;
            html += `<td>${parseFloat(totals.total_provident_fund).toFixed(2)}</td>`;
            html += `<td>${parseFloat(totals.grand_total).toFixed(2)}</td>`;
            html += '</tr></tfoot>';
            
            html += '</table>';
            
            // Create and download the file
            const blob = new Blob([html], {type: 'application/vnd.ms-excel'});
            const url = URL.createObjectURL(blob);
            const link = document.createElement('a');
            link.download = `Staff_Deductions_Report_${startDate}_to_${endDate}.xls`;
            link.href = url;
            document.body.appendChild(link);
            link.click();
            document.body.removeChild(link);
            URL.revokeObjectURL(url);
            
        } catch (e) {
            console.error('Export error:', e);
            Swal.fire({
                title: 'Error',
                text: 'Error creating Excel file',
                icon: 'error',
                customClass: {
                    confirmButton: "btn btn-danger"
                },
                buttonsStyling: false
            });
        }
    }

    function showLoading() {
        $('#loadingOverlay').show();
    }
    
    function hideLoading() {
        $('#loadingOverlay').hide();
    }
</script>
@endsection