<?php
/**
 * Salary Register - Excel-like Input Module
 * Allows direct data input in Excel format
 */

require_once __DIR__ . '/../../includes/init.php';
requireLogin();

$pageTitle = "Salary Register - Data Input";
$currentTab = 'salary_register';
$siteId = getCurrentSiteId();
$db = getDB();

// Get selected month or default to current
$selectedMonth = isset($_GET['month']) ? $_GET['month'] : date('F Y');

// Handle form submission
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['save_register'])) {
    try {
        $db->beginTransaction();
        
        foreach ($_POST['employees'] as $empId => $data) {
            // Check if record exists
            $checkStmt = $db->prepare("
                SELECT id FROM salary_register 
                WHERE employee_id = ? AND salary_month = ? AND site_id = ?
            ");
            $checkStmt->execute([$empId, $selectedMonth, $siteId]);
            $exists = $checkStmt->fetch();
            
            if ($exists) {
                // Update
                $updateStmt = $db->prepare("
                    UPDATE salary_register SET
                        basic_salary = ?, vda = ?, special_allowance = ?, other_allowance = ?,
                        hra = ?, present_days = ?, absent_days = ?, weekly_off = ?,
                        pf_amount = ?, esi_amount = ?, pt_amount = ?, total_deduction = ?,
                        provident_fund = ?, esic = ?, leave_amount = ?, holiday_amount = ?,
                        bonus = ?, uniform = ?, gratuity = ?, food_allowance = ?,
                        updated_at = NOW()
                    WHERE id = ?
                ");
                $updateStmt->execute([
                    $data['basic'] ?? 0,
                    $data['vda'] ?? 0,
                    $data['special_allowance'] ?? 0,
                    $data['other_allowance'] ?? 0,
                    $data['hra'] ?? 0,
                    $data['present_days'] ?? 0,
                    $data['absent_days'] ?? 0,
                    $data['weekly_off'] ?? 0,
                    $data['pf'] ?? 0,
                    $data['esi'] ?? 0,
                    $data['pt'] ?? 0,
                    $data['total_deduction'] ?? 0,
                    $data['provident_fund'] ?? 0,
                    $data['esic_rebill'] ?? 0,
                    $data['leave'] ?? 0,
                    $data['holiday'] ?? 0,
                    $data['bonus'] ?? 0,
                    $data['uniform'] ?? 0,
                    $data['gratuity'] ?? 0,
                    $data['food_allowance'] ?? 0,
                    $exists['id']
                ]);
            } else {
                // Insert
                $insertStmt = $db->prepare("
                    INSERT INTO salary_register (
                        site_id, employee_id, salary_month,
                        basic_salary, vda, special_allowance, other_allowance,
                        hra, present_days, absent_days, weekly_off,
                        pf_amount, esi_amount, pt_amount, total_deduction,
                        provident_fund, esic, leave_amount, holiday_amount,
                        bonus, uniform, gratuity, food_allowance,
                        created_at, updated_at
                    ) VALUES (
                        ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW()
                    )
                ");
                $insertStmt->execute([
                    $siteId, $empId, $selectedMonth,
                    $data['basic'] ?? 0,
                    $data['vda'] ?? 0,
                    $data['special_allowance'] ?? 0,
                    $data['other_allowance'] ?? 0,
                    $data['hra'] ?? 0,
                    $data['present_days'] ?? 0,
                    $data['absent_days'] ?? 0,
                    $data['weekly_off'] ?? 0,
                    $data['pf'] ?? 0,
                    $data['esi'] ?? 0,
                    $data['pt'] ?? 0,
                    $data['total_deduction'] ?? 0,
                    $data['provident_fund'] ?? 0,
                    $data['esic_rebill'] ?? 0,
                    $data['leave'] ?? 0,
                    $data['holiday'] ?? 0,
                    $data['bonus'] ?? 0,
                    $data['uniform'] ?? 0,
                    $data['gratuity'] ?? 0,
                    $data['food_allowance'] ?? 0
                ]);
            }
        }
        
        $db->commit();
        $successMessage = "Salary register data saved successfully!";
        header("Location: ?month=" . urlencode($selectedMonth) . "&saved=1");
        exit;
    } catch (Exception $e) {
        $db->rollBack();
        $errorMessage = "Error saving data: " . $e->getMessage();
    }
}

// Check for saved parameter
if (isset($_GET['saved'])) {
    $successMessage = "Salary register data saved successfully for " . $selectedMonth . "!";
}

// Get active employees
$employeesStmt = $db->prepare("
    SELECT 
        e.employee_id,
        e.employee_code,
        CONCAT(e.first_name, ' ', IFNULL(e.middle_name, ''), ' ', e.last_name) as full_name,
        d.designation_name,
        e.employment_type,
        ems.category,
        e.employment_status
    FROM employees e
    LEFT JOIN designations d ON e.designation_id = d.designation_id
    LEFT JOIN employee_master_sheet ems ON e.employee_id = ems.employee_id
    WHERE e.site_id = ? AND e.employment_status = 'active'
    ORDER BY e.employee_code
");
$employeesStmt->execute([$siteId]);
$employees = $employeesStmt->fetchAll();

// Get existing salary data
$salaryData = [];
foreach ($employees as $emp) {
    $salaryStmt = $db->prepare("
        SELECT * FROM salary_register 
        WHERE employee_id = ? AND salary_month = ? AND site_id = ?
    ");
    $salaryStmt->execute([$emp['employee_id'], $selectedMonth, $siteId]);
    $salaryData[$emp['employee_id']] = $salaryStmt->fetch() ?: [];
}

include __DIR__ . '/../../includes/header.php';
?>

<style>
.salary-register-container {
    background: white;
    padding: 20px;
    overflow-x: auto;
}

.register-header {
    background: #e8f4fd;
    padding: 20px;
    border-radius: 8px;
    margin-bottom: 20px;
    border-left: 4px solid #007bff;
}

.register-table {
    width: 100%;
    border-collapse: collapse;
    font-size: 12px;
    min-width: 2500px;
}

.register-table th {
    background: #000080;
    color: white;
    padding: 10px 5px;
    text-align: center;
    font-weight: bold;
    border: 1px solid #000060;
    position: sticky;
    top: 0;
    z-index: 10;
}

.register-table td {
    padding: 5px;
    border: 1px dotted #ccc;
    text-align: center;
}

.section-header {
    background: #000080 !important;
    color: white !important;
    font-weight: bold;
    font-size: 14px;
}

.section-header-employer {
    background: #696969 !important;
}

.section-header-attendance {
    background: #4169e1 !important;
}

.section-header-earned {
    background: #20b2aa !important;
}

.section-header-rebill {
    background: #8b4513 !important;
}

.register-table input[type="number"] {
    width: 100%;
    padding: 5px;
    border: 1px solid #ddd;
    text-align: right;
    font-size: 12px;
}

.register-table input[type="number"]:focus {
    outline: none;
    border-color: #007bff;
    background: #f0f8ff;
}

.register-table input[type="text"] {
    width: 100%;
    padding: 5px;
    border: 1px solid #ddd;
    font-size: 12px;
}

.employee-name {
    text-align: left !important;
    padding-left: 10px !important;
    white-space: nowrap;
}

.btn-save {
    background: linear-gradient(135deg, #28a745 0%, #20c997 100%);
    color: white;
    padding: 12px 30px;
    border: none;
    border-radius: 6px;
    font-size: 16px;
    font-weight: 600;
    cursor: pointer;
    box-shadow: 0 2px 8px rgba(40,167,69,0.3);
}

.btn-save:hover {
    transform: translateY(-2px);
    box-shadow: 0 4px 12px rgba(40,167,69,0.4);
}

.total-cell {
    background: #fff3cd !important;
    font-weight: bold;
}

.month-selector {
    padding: 10px 15px;
    border: 2px solid #007bff;
    border-radius: 6px;
    font-size: 14px;
    margin-right: 10px;
}

.calculated-cell {
    background: #e8f5e9;
    font-weight: 600;
}
</style>

<div class="salary-register-container">
    <?php include __DIR__ . '/_navigation.php'; ?>
    
    <!-- Header -->
    <div style="display: flex; justify-content: space-between; align-items: center; margin-bottom: 20px;">
        <h1 style="margin: 0;">📊 Salary Register - <?php echo htmlspecialchars($selectedMonth); ?></h1>
        <div>
            <select class="month-selector" onchange="window.location.href='?month='+this.value">
                <?php
                for ($i = -2; $i <= 2; $i++) {
                    $monthDate = date('F Y', strtotime("$i months"));
                    $selected = ($monthDate === $selectedMonth) ? 'selected' : '';
                    echo "<option value='$monthDate' $selected>$monthDate</option>";
                }
                ?>
            </select>
        </div>
    </div>
    
    <?php if (isset($successMessage)): ?>
        <div class="alert alert-success"><?php echo $successMessage; ?></div>
    <?php endif; ?>
    
    <?php if (isset($errorMessage)): ?>
        <div class="alert alert-error"><?php echo $errorMessage; ?></div>
    <?php endif; ?>
    
    <div class="register-header">
        <div style="display: grid; grid-template-columns: 1fr 1fr; gap: 20px;">
            <div>
                <strong>Site Name:</strong> <?php echo htmlspecialchars($_SESSION['site_name'] ?? 'Site'); ?><br>
                <strong>Month:</strong> <?php echo htmlspecialchars($selectedMonth); ?>
            </div>
            <div style="text-align: right;">
                <strong>Total Employees:</strong> <?php echo count($employees); ?><br>
                <strong>Status:</strong> <span style="color: #28a745;">● Data Entry Mode</span>
            </div>
        </div>
    </div>
    
    <form method="POST" id="salary-form">
        <table class="register-table">
            <thead>
                <!-- Section Headers -->
                <tr>
                    <th colspan="7" class="section-header">Details</th>
                    <th colspan="4" class="section-header-employer">Employer Structure</th>
                    <th colspan="4" class="section-header-attendance">Attendance</th>
                    <th colspan="6" class="section-header-earned">Earned Salary</th>
                    <th colspan="8" class="section-header-rebill">Rebill</th>
                </tr>
                
                <!-- Column Headers -->
                <tr>
                    <th style="width: 50px;">S.No</th>
                    <th style="width: 150px;">Employee Name</th>
                    <th style="width: 120px;">Designation</th>
                    <th style="width: 100px;">Employee Type</th>
                    <th style="width: 120px;">Category</th>
                    <th style="width: 80px;">Status</th>
                    <th style="width: 80px;">Total Earning</th>
                    
                    <th style="width: 80px;">Basic</th>
                    <th style="width: 80px;">VDA</th>
                    <th style="width: 80px;">Special Allow.</th>
                    <th style="width: 80px;">Other Allow.</th>
                    
                    <th style="width: 80px;">HRA</th>
                    <th style="width: 60px;">P</th>
                    <th style="width: 60px;">A</th>
                    <th style="width: 60px;">W/O</th>
                    
                    <th style="width: 80px;">Basic</th>
                    <th style="width: 80px;">HRA</th>
                    <th style="width: 80px;">Other Allow.</th>
                    <th style="width: 80px;">Total Earning</th>
                    <th style="width: 80px;">PF</th>
                    <th style="width: 80px;">ESI</th>
                    
                    <th style="width: 80px;">PF (12%)</th>
                    <th style="width: 80px;">ESIC (3.25%)</th>
                    <th style="width: 80px;">Leave</th>
                    <th style="width: 80px;">Holiday</th>
                    <th style="width: 80px;">Bonus</th>
                    <th style="width: 80px;">Uniform</th>
                    <th style="width: 80px;">Gratuity</th>
                    <th style="width: 80px;">Food Allow.</th>
                </tr>
            </thead>
            <tbody>
                <?php 
                $slNo = 1;
                foreach ($employees as $emp): 
                    $salary = $salaryData[$emp['employee_id']];
                    $empId = $emp['employee_id'];
                ?>
                <tr>
                    <td><?php echo $slNo++; ?></td>
                    <td class="employee-name"><?php echo htmlspecialchars($emp['full_name']); ?></td>
                    <td><?php echo htmlspecialchars($emp['designation_name'] ?? ''); ?></td>
                    <td><?php echo htmlspecialchars($emp['employment_type'] ?? 'FTC'); ?></td>
                    <td><?php echo htmlspecialchars($emp['category'] ?? 'Skilled'); ?></td>
                    <td><?php echo ucfirst($emp['employment_status']); ?></td>
                    <td class="calculated-cell" id="total_earning_<?php echo $empId; ?>">0</td>
                    
                    <!-- Employer Structure -->
                    <td>
                        <input type="number" name="employees[<?php echo $empId; ?>][basic]" 
                               value="<?php echo $salary['basic_salary'] ?? 15000; ?>" 
                               class="calc-field" data-emp="<?php echo $empId; ?>" step="0.01">
                    </td>
                    <td>
                        <input type="number" name="employees[<?php echo $empId; ?>][vda]" 
                               value="<?php echo $salary['vda'] ?? 0; ?>" 
                               class="calc-field" data-emp="<?php echo $empId; ?>" step="0.01">
                    </td>
                    <td>
                        <input type="number" name="employees[<?php echo $empId; ?>][special_allowance]" 
                               value="<?php echo $salary['special_allowance'] ?? 0; ?>" 
                               class="calc-field" data-emp="<?php echo $empId; ?>" step="0.01">
                    </td>
                    <td>
                        <input type="number" name="employees[<?php echo $empId; ?>][other_allowance]" 
                               value="<?php echo $salary['other_allowance'] ?? 3000; ?>" 
                               class="calc-field" data-emp="<?php echo $empId; ?>" step="0.01">
                    </td>
                    
                    <!-- Attendance -->
                    <td>
                        <input type="number" name="employees[<?php echo $empId; ?>][hra]" 
                               value="<?php echo $salary['hra'] ?? 0; ?>" 
                               class="calc-field" data-emp="<?php echo $empId; ?>" step="0.01">
                    </td>
                    <td>
                        <input type="number" name="employees[<?php echo $empId; ?>][present_days]" 
                               value="<?php echo $salary['present_days'] ?? 27; ?>" 
                               class="calc-field" data-emp="<?php echo $empId; ?>">
                    </td>
                    <td>
                        <input type="number" name="employees[<?php echo $empId; ?>][absent_days]" 
                               value="<?php echo $salary['absent_days'] ?? 0; ?>" 
                               class="calc-field" data-emp="<?php echo $empId; ?>">
                    </td>
                    <td>
                        <input type="number" name="employees[<?php echo $empId; ?>][weekly_off]" 
                               value="<?php echo $salary['weekly_off'] ?? 4; ?>" 
                               class="calc-field" data-emp="<?php echo $empId; ?>">
                    </td>
                    
                    <!-- Earned Salary -->
                    <td class="calculated-cell" id="earned_basic_<?php echo $empId; ?>">0</td>
                    <td class="calculated-cell" id="earned_hra_<?php echo $empId; ?>">0</td>
                    <td class="calculated-cell" id="earned_other_<?php echo $empId; ?>">0</td>
                    <td class="calculated-cell" id="earned_total_<?php echo $empId; ?>">0</td>
                    <td>
                        <input type="number" name="employees[<?php echo $empId; ?>][pf]" 
                               value="<?php echo $salary['pf_amount'] ?? 1440; ?>" step="0.01">
                    </td>
                    <td>
                        <input type="number" name="employees[<?php echo $empId; ?>][esi]" 
                               value="<?php echo $salary['esi_amount'] ?? 120; ?>" step="0.01">
                    </td>
                    
                    <!-- Rebill -->
                    <td class="calculated-cell" id="rebill_pf_<?php echo $empId; ?>">0</td>
                    <td class="calculated-cell" id="rebill_esic_<?php echo $empId; ?>">0</td>
                    <td>
                        <input type="number" name="employees[<?php echo $empId; ?>][leave]" 
                               value="<?php echo $salary['leave_amount'] ?? 205; ?>" step="0.01">
                    </td>
                    <td>
                        <input type="number" name="employees[<?php echo $empId; ?>][holiday]" 
                               value="<?php echo $salary['holiday_amount'] ?? 0; ?>" step="0.01">
                    </td>
                    <td>
                        <input type="number" name="employees[<?php echo $empId; ?>][bonus]" 
                               value="<?php echo $salary['bonus'] ?? 999.60; ?>" step="0.01">
                    </td>
                    <td>
                        <input type="number" name="employees[<?php echo $empId; ?>][uniform]" 
                               value="<?php echo $salary['uniform'] ?? 270; ?>" step="0.01">
                    </td>
                    <td>
                        <input type="number" name="employees[<?php echo $empId; ?>][gratuity]" 
                               value="<?php echo $salary['gratuity'] ?? 577; ?>" step="0.01">
                    </td>
                    <td>
                        <input type="number" name="employees[<?php echo $empId; ?>][food_allowance]" 
                               value="<?php echo $salary['food_allowance'] ?? 0; ?>" step="0.01">
                    </td>
                </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
        
        <div style="margin-top: 30px; padding: 20px; background: linear-gradient(135deg, #f093fb 0%, #f5576c 100%); border-radius: 8px; text-align: center;">
            <button type="submit" name="save_register" class="btn-save">
                ✓ Save Salary Register for <?php echo htmlspecialchars($selectedMonth); ?>
            </button>
        </div>
    </form>
</div>

<script>
// Auto-calculate earnings
document.addEventListener('DOMContentLoaded', function() {
    const calcFields = document.querySelectorAll('.calc-field');
    
    calcFields.forEach(field => {
        field.addEventListener('input', function() {
            calculateRow(this.dataset.emp);
        });
        
        // Initial calculation
        calculateRow(field.dataset.emp);
    });
    
    function calculateRow(empId) {
        const form = document.querySelector('form');
        const prefix = `employees[${empId}]`;
        
        // Get values
        const basic = parseFloat(form.querySelector(`[name="${prefix}[basic]"]`).value) || 0;
        const vda = parseFloat(form.querySelector(`[name="${prefix}[vda]"]`).value) || 0;
        const specialAllow = parseFloat(form.querySelector(`[name="${prefix}[special_allowance]"]`).value) || 0;
        const otherAllow = parseFloat(form.querySelector(`[name="${prefix}[other_allowance]"]`).value) || 0;
        const hra = parseFloat(form.querySelector(`[name="${prefix}[hra]"]`).value) || 0;
        const presentDays = parseInt(form.querySelector(`[name="${prefix}[present_days]"]`).value) || 0;
        const weeklyOff = parseInt(form.querySelector(`[name="${prefix}[weekly_off]"]`).value) || 0;
        
        // Calculate total earning
        const totalEarning = basic + vda + specialAllow + otherAllow;
        document.getElementById(`total_earning_${empId}`).textContent = totalEarning.toFixed(2);
        
        // Calculate earned salary based on attendance
        const totalDays = 31;
        const payDays = presentDays + weeklyOff;
        const perDayRate = totalEarning / totalDays;
        
        const earnedBasic = (basic / totalDays) * payDays;
        const earnedHra = (hra / totalDays) * payDays;
        const earnedOther = (otherAllow / totalDays) * payDays;
        const earnedTotal = earnedBasic + earnedHra + earnedOther;
        
        document.getElementById(`earned_basic_${empId}`).textContent = earnedBasic.toFixed(2);
        document.getElementById(`earned_hra_${empId}`).textContent = earnedHra.toFixed(2);
        document.getElementById(`earned_other_${empId}`).textContent = earnedOther.toFixed(2);
        document.getElementById(`earned_total_${empId}`).textContent = earnedTotal.toFixed(2);
        
        // Calculate rebill
        const rebillPF = earnedBasic * 0.12; // 12%
        const rebillESIC = totalEarning * 0.0325; // 3.25%
        
        document.getElementById(`rebill_pf_${empId}`).textContent = rebillPF.toFixed(2);
        document.getElementById(`rebill_esic_${empId}`).textContent = rebillESIC.toFixed(2);
    }
});
</script>

<?php include __DIR__ . '/../../includes/footer.php'; ?>
