<?php
/**
 * Enterprise HRM ERP System - Payroll Register (EXACT EXCEL REPLICATION)
 * All formulas and calculations match Excel file exactly
 */

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

$pageTitle = 'Salary Register';
$currentModule = 'payroll';
$currentTab = 'salary_register';

$siteId = getCurrentSiteId();
$selectedMonth = isset($_GET['month']) ? $_GET['month'] : date('Y-m');
$selectedYearNum = (int)explode('-', $selectedMonth)[0];
$selectedMonthNum = (int)explode('-', $selectedMonth)[1];

// Get days in month (like Excel formula)
$daysInMonth = cal_days_in_month(CAL_GREGORIAN, $selectedMonthNum, $selectedYearNum);

try {
    $db = getDB();
    
    // Get Site Name (from Home sheet reference)
    $stmt = $db->prepare("SELECT site_name FROM sites WHERE site_id = ?");
    $stmt->execute([$siteId]);
    $site = $stmt->fetch();
    $siteName = $site ? $site['site_name'] : 'Base Office';
    
    // Get Payroll Period
    $stmt = $db->prepare("
        SELECT * FROM payroll_periods 
        WHERE site_id = ? 
        AND MONTH(start_date) = ? 
        AND YEAR(start_date) = ?
        LIMIT 1
    ");
    $stmt->execute([$siteId, $selectedMonthNum, $selectedYearNum]);
    $payrollPeriod = $stmt->fetch();
    
    // Get all employees for this site
    $stmt = $db->prepare("
        SELECT e.*, d.department_name, des.designation_name,
               ess.basic_salary, ess.vda, ess.special_allowance, ess.other_allowance,
               ess.hra, ess.transport_allowance
        FROM employees e
        LEFT JOIN departments d ON e.department_id = d.department_id
        LEFT JOIN designations des ON e.designation_id = des.designation_id
        LEFT JOIN employee_salary_structure ess ON e.employee_id = ess.employee_id 
            AND ess.structure_status = 'active'
        WHERE e.site_id = ? AND e.employment_status = 'active'
        ORDER BY e.employee_code
    ");
    $stmt->execute([$siteId]);
    $employees = $stmt->fetchAll();
    
    // Get attendance data for the month
    $startDate = date('Y-m-01', strtotime("$selectedYearNum-$selectedMonthNum-01"));
    $endDate = date('Y-m-t', strtotime($startDate));
    
    $attendanceMap = [];
    if (!empty($employees)) {
        $empIds = array_column($employees, 'employee_id');
        $placeholders = implode(',', array_fill(0, count($empIds), '?'));
        
        // Get attendance with status counts
        $stmt = $db->prepare("
            SELECT employee_id, attendance_status, COUNT(*) as count
            FROM attendance
            WHERE employee_id IN ($placeholders) 
            AND attendance_date >= ? AND attendance_date <= ?
            GROUP BY employee_id, attendance_status
        ");
        $params = array_merge($empIds, [$startDate, $endDate]);
        $stmt->execute($params);
        $attendanceData = $stmt->fetchAll();
        
        // Build attendance map
        foreach ($attendanceData as $att) {
            $empId = $att['employee_id'];
            if (!isset($attendanceMap[$empId])) {
                $attendanceMap[$empId] = [
                    'P' => 0,  // Present
                    'H' => 0,  // Half Day
                    'CL' => 0, // Casual Leave
                    'PL' => 0, // Privileged Leave
                    'SL' => 0, // Sick Leave
                    'A' => 0,  // Absent
                    'WO' => 0  // Week Off
                ];
            }
            
            // Map attendance_status to Excel codes
            $status = $att['attendance_status'];
            if ($status == 'present') $attendanceMap[$empId]['P'] = $att['count'];
            elseif ($status == 'half_day') $attendanceMap[$empId]['H'] = $att['count'];
            elseif ($status == 'absent') $attendanceMap[$empId]['A'] = $att['count'];
            elseif ($status == 'on_leave') {
                // Check leave type from leave_applications
                $stmt2 = $db->prepare("
                    SELECT lt.leave_code 
                    FROM leave_applications la
                    INNER JOIN leave_types lt ON la.leave_type_id = lt.leave_type_id
                    WHERE la.employee_id = ? 
                    AND DATE(la.start_date) <= ? 
                    AND DATE(la.end_date) >= ?
                    AND la.application_status = 'approved'
                    LIMIT 1
                ");
                $stmt2->execute([$empId, $att['attendance_date'], $att['attendance_date']]);
                $leave = $stmt2->fetch();
                if ($leave) {
                    $code = strtoupper($leave['leave_code']);
                    if ($code == 'CL') $attendanceMap[$empId]['CL'] += $att['count'];
                    elseif ($code == 'PL') $attendanceMap[$empId]['PL'] += $att['count'];
                    elseif ($code == 'SL') $attendanceMap[$empId]['SL'] += $att['count'];
                }
            }
        }
        
        // Get week offs (Sundays/Saturdays based on site settings)
        $weekOffDay = getSetting('WEEK_OFF_DAY', 0); // 0=Sunday, 6=Saturday
        for ($day = 1; $day <= $daysInMonth; $day++) {
            $date = date('Y-m-d', strtotime("$selectedYearNum-$selectedMonthNum-$day"));
            $dayOfWeek = (int)date('w', strtotime($date));
            
            if ($dayOfWeek == $weekOffDay) {
                foreach ($empIds as $empId) {
                    if (!isset($attendanceMap[$empId])) {
                        $attendanceMap[$empId] = ['P' => 0, 'H' => 0, 'CL' => 0, 'PL' => 0, 'SL' => 0, 'A' => 0, 'WO' => 0];
                    }
                    $attendanceMap[$empId]['WO']++;
                }
            }
        }
    }
    
    // Process each employee with Excel formulas
    $payrollData = [];
    $totals = [
        'pf_employee' => 0, 'esic_employee' => 0, 'professional_tax' => 0,
        'total_deductions' => 0, 'net_salary' => 0,
        'pf_employer' => 0, 'esic_employer' => 0, 'leave_provision' => 0,
        'bonus' => 0, 'food_allowance' => 0, 'total_rebill' => 0, 'earned_ctc' => 0
    ];
    
    foreach ($employees as $emp) {
        $empId = $emp['employee_id'];
        $att = $attendanceMap[$empId] ?? ['P' => 0, 'H' => 0, 'CL' => 0, 'PL' => 0, 'SL' => 0, 'A' => 0, 'WO' => 0];
        
        // Excel Formula: P8 = COUNTIF(I8:AM8,"P") - Present days
        $presentDays = $att['P'];
        
        // Excel Formula: AT8 = COUNTIF(K8:AM8,"A") - Absent days
        $absentDays = $att['A'];
        
        // Excel Formula: AU8 = COUNTIF(I8:AM8,"WO") - Week Off
        $weekOff = $att['WO'];
        
        // Excel Formula: S8 = P8+R8 (Present + Week Off = Pay Days)
        $payDays = $presentDays + $weekOff;
        
        // Salary components (from employee_salary_structure)
        $basic = (float)($emp['basic_salary'] ?? 0);
        $vda = (float)($emp['vda'] ?? 0);
        $specialAllowance = (float)($emp['special_allowance'] ?? 0);
        $otherAllowance = (float)($emp['other_allowance'] ?? 0);
        $hra = (float)($emp['hra'] ?? 0);
        
        // Excel Formula: N8 = I8+J8+K8+L8+M8 (Sum of all allowances)
        $totalStructure = $basic + $vda + $specialAllowance + $otherAllowance + ($hra ?? 0);
        
        // Excel Formula: V8 = ROUND(M8/$E$5*$S8,0) - Special Allowance prorated
        $earnedSpecialAllowance = round(($specialAllowance / $daysInMonth) * $payDays, 0);
        
        // Excel Formula: W8 = ROUND(L8/$E$5*$S8,0) - Other Allowance prorated
        $earnedOtherAllowance = round(($otherAllowance / $daysInMonth) * $payDays, 0);
        
        // Excel Formula: U8 = Basic (fixed, but can be prorated if needed)
        $earnedBasic = $basic; // Can be prorated: round(($basic / $daysInMonth) * $payDays, 0);
        
        // Excel Formula: X8 = VDA (usually fixed)
        $earnedVDA = $vda;
        
        // Excel Formula: Y8 = SUM(U8:X8) - Gross Salary
        $grossSalary = $earnedBasic + $earnedVDA + $earnedSpecialAllowance + $earnedOtherAllowance;
        
        // Excel Formula: Z8 = IF(U8=0,0,ROUND(IF(U8>15000,15000*12%,U8*12%),0)) - PF Employee
        $pfEmployee = 0;
        if ($earnedBasic > 0) {
            $pfBase = min($earnedBasic, 15000);
            $pfEmployee = round($pfBase * 0.12, 0);
        }
        
        // Excel Formula: AA8 = ROUND(IF((Y8)<21000,(Y8)*0.75%, 0),0) - ESIC Employee
        $esicEmployee = 0;
        if ($grossSalary < 21000) {
            $esicEmployee = round($grossSalary * 0.0075, 0);
        }
        
        // Excel Formula: AB8 = IF(Y8<13304,0,IF(Y8<25000,125,200)) - Professional Tax
        $professionalTax = 0;
        if ($grossSalary >= 13304) {
            $professionalTax = $grossSalary < 25000 ? 125 : 200;
        }
        
        // Excel Formula: AC8 = SUM(Z8:AB8) - Total Deductions Employee
        $totalDeductionsEmployee = $pfEmployee + $esicEmployee + $professionalTax;
        
        // Excel Formula: AD8 = Y8-AC8 - Net Salary
        $netSalary = $grossSalary - $totalDeductionsEmployee;
        
        // Excel Formula: AF8 = IF(U8=0,0,ROUND(IF(U8>15000,15000*12%,U8*12%),0)) - PF Employer
        $pfEmployer = $pfEmployee; // Same calculation
        
        // Excel Formula: AG8 = ROUND(IF(Y8=0,0,IF(Y8>21000,0,Y8*3.25%)),0) - ESIC Employer
        $esicEmployer = 0;
        if ($grossSalary > 0 && $grossSalary <= 21000) {
            $esicEmployer = round($grossSalary * 0.0325, 0);
        }
        
        // Excel Formula: AH8 = ROUND(Y8/26*4/12,0) - Leave Provision
        $leaveProvision = round(($grossSalary / 26) * (4/12), 0);
        
        // Excel Formula: AJ8 = ROUND(U8/26*15/12,0) - Bonus
        $bonus = 0;
        if ($earnedBasic > 0) {
            $bonus = round(($earnedBasic / 26) * (15/12), 0);
        }
        
        // Excel Formula: AM8 = Food Allowance (if exists, else 0)
        $foodAllowance = 0;
        
        // Excel Formula: AN8 = SUM(AF8:AM8) - Total Rebill
        $totalRebill = $pfEmployer + $esicEmployer + $leaveProvision + $bonus + $foodAllowance;
        
        // Excel Formula: AP8 = Y8+AN8 - Earned CTC
        $earnedCTC = $grossSalary + $totalRebill;
        
        // Excel Formula: AR8 = Y8/$E$5 - Per Day Wages
        $perDayWages = $grossSalary / $daysInMonth;
        
        $payrollData[] = [
            'employee' => $emp,
            'present_days' => $presentDays,
            'absent_days' => $absentDays,
            'week_off' => $weekOff,
            'pay_days' => $payDays,
            'basic' => $earnedBasic,
            'vda' => $earnedVDA,
            'special_allowance' => $earnedSpecialAllowance,
            'other_allowance' => $earnedOtherAllowance,
            'gross_salary' => $grossSalary,
            'pf_employee' => $pfEmployee,
            'esic_employee' => $esicEmployee,
            'professional_tax' => $professionalTax,
            'total_deductions' => $totalDeductionsEmployee,
            'net_salary' => $netSalary,
            'pf_employer' => $pfEmployer,
            'esic_employer' => $esicEmployer,
            'leave_provision' => $leaveProvision,
            'bonus' => $bonus,
            'food_allowance' => $foodAllowance,
            'total_rebill' => $totalRebill,
            'earned_ctc' => $earnedCTC,
            'per_day_wages' => $perDayWages
        ];
        
        // Accumulate totals
        $totals['pf_employee'] += $pfEmployee;
        $totals['esic_employee'] += $esicEmployee;
        $totals['professional_tax'] += $professionalTax;
        $totals['total_deductions'] += $totalDeductionsEmployee;
        $totals['net_salary'] += $netSalary;
        $totals['pf_employer'] += $pfEmployer;
        $totals['esic_employer'] += $esicEmployer;
        $totals['leave_provision'] += $leaveProvision;
        $totals['bonus'] += $bonus;
        $totals['food_allowance'] += $foodAllowance;
        $totals['total_rebill'] += $totalRebill;
        $totals['earned_ctc'] += $earnedCTC;
    }
    
} catch (Exception $e) {
    error_log("Salary Register error: " . $e->getMessage());
    $payrollData = [];
    $totals = [];
    $siteName = 'Base Office';
    $daysInMonth = 31;
}

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

<style>
.excel-register {
    font-family: Calibri, Arial, sans-serif;
    font-size: 11px;
}

.excel-register table {
    border-collapse: collapse;
    width: 100%;
    min-width: 2000px;
}

.excel-register th,
.excel-register td {
    border: 1px solid #000;
    padding: 4px 6px;
    text-align: center;
    white-space: nowrap;
}

.excel-register th {
    background: #1F3864;
    color: #FFF;
    font-weight: bold;
    font-size: 12px;
}

.excel-register .header-blue {
    background: #1F3864;
    color: #FFF;
}

.excel-register .header-brown {
    background: #C55A11;
    color: #FFF;
}

.excel-register .summary-row {
    background: #DEEAF6;
    font-weight: bold;
}

.excel-register .text-left {
    text-align: left;
}

.excel-register .text-right {
    text-align: right;
}

.excel-register input[type="text"],
.excel-register input[type="number"] {
    width: 100%;
    border: none;
    background: transparent;
    text-align: center;
    font-size: 11px;
}
</style>

<div class="excel-register" style="padding: 20px; background: #f5f5f5;">
    <!-- Header Section (like Excel) -->
    <div style="margin-bottom: 20px;">
        <table style="width: auto; border: none;">
            <tr>
                <td style="border: none; padding: 5px;"><strong>Site Name:</strong></td>
                <td style="border: 1px solid #000; padding: 5px; background: #FFF;"><?php echo htmlspecialchars($siteName); ?></td>
                <td style="border: none; padding: 5px; width: 50px;"></td>
                <td style="border: none; padding: 5px;"><strong>Month:</strong></td>
                <td style="border: 1px solid #000; padding: 5px; background: #FFF;"><?php echo date('M-y', strtotime("$selectedYearNum-$selectedMonthNum-01")); ?></td>
                <td style="border: none; padding: 5px; width: 50px;"></td>
                <td style="border: none; padding: 5px;"><strong>Days:</strong></td>
                <td style="border: 1px solid #000; padding: 5px; background: #FFF;"><?php echo $daysInMonth; ?></td>
            </tr>
        </table>
    </div>
    
    <!-- Main Salary Register Table (Excel Structure) -->
    <div style="overflow-x: auto;">
        <table>
            <thead>
                <!-- Row 6: Main Headers -->
                <tr>
                    <th rowspan="2" class="header-blue">Sl No</th>
                    <th rowspan="2" class="header-blue">Employee Name</th>
                    <th rowspan="2" class="header-blue">Designation</th>
                    <th rowspan="2" class="header-blue">Employee Type</th>
                    <th rowspan="2" class="header-blue">Category</th>
                    <th rowspan="2" class="header-blue">Status</th>
                    <th colspan="4" class="header-blue">Employer Structure</th>
                    <th colspan="3" class="header-blue">Attendance</th>
                    <th colspan="5" class="header-blue">Earned Salary</th>
                    <th colspan="3" class="header-brown">Net Salary</th>
                    <th colspan="8" class="header-brown">Rebill</th>
                    <th rowspan="2" class="header-blue">Total</th>
                    <th rowspan="2" class="header-blue">Earned CTC</th>
                    <th rowspan="2" class="header-blue">Per day Wages</th>
                </tr>
                <!-- Row 7: Sub Headers -->
                <tr>
                    <!-- Employer Structure -->
                    <th class="header-blue">Basic</th>
                    <th class="header-blue">VDA</th>
                    <th class="header-blue">Special Allowance</th>
                    <th class="header-blue">Other Allowance</th>
                    
                    <!-- Attendance -->
                    <th class="header-blue">P</th>
                    <th class="header-blue">A</th>
                    <th class="header-blue">WO</th>
                    
                    <!-- Earned Salary -->
                    <th class="header-blue">Basic</th>
                    <th class="header-blue">VDA</th>
                    <th class="header-blue">Special Allowance</th>
                    <th class="header-blue">Other Allowance</th>
                    <th class="header-blue">Gross</th>
                    
                    <!-- Net Salary -->
                    <th class="header-brown">PF</th>
                    <th class="header-brown">ESIC</th>
                    <th class="header-brown">Prof Tax</th>
                    
                    <!-- Rebill -->
                    <th class="header-brown">PF</th>
                    <th class="header-brown">ESIC</th>
                    <th class="header-brown">Leave Provision</th>
                    <th class="header-brown">Bonus</th>
                    <th class="header-brown">Food Allowance</th>
                    <th class="header-brown">Other</th>
                    <th class="header-brown">Other</th>
                    <th class="header-brown">Total Rebill</th>
                </tr>
            </thead>
            <tbody>
                <?php 
                $rowNum = 8; // Excel row numbers start from 8
                foreach ($payrollData as $index => $row): 
                    $emp = $row['employee'];
                ?>
                <tr>
                    <td style="text-align: center;"><?php echo $index + 1; ?></td>
                    <td class="text-left"><?php echo htmlspecialchars(trim($emp['first_name'] . ' ' . ($emp['middle_name'] ?? '') . ' ' . $emp['last_name'])); ?></td>
                    <td class="text-left"><?php echo htmlspecialchars($emp['designation_name'] ?? 'N/A'); ?></td>
                    <td style="text-align: center;"><?php echo htmlspecialchars($emp['employment_type'] ?? 'FTC'); ?></td>
                    <td style="text-align: center;"><?php echo htmlspecialchars($emp['employee_category'] ?? 'Highly Skilled'); ?></td>
                    <td style="text-align: center;"><?php echo htmlspecialchars($emp['employment_status'] ?? 'Active'); ?></td>
                    
                    <!-- Employer Structure -->
                    <td class="text-right"><?php echo number_format($emp['basic_salary'] ?? 0, 2); ?></td>
                    <td class="text-right"><?php echo number_format($emp['vda'] ?? 0, 2); ?></td>
                    <td class="text-right"><?php echo number_format($emp['special_allowance'] ?? 0, 2); ?></td>
                    <td class="text-right"><?php echo number_format($emp['other_allowance'] ?? 0, 2); ?></td>
                    
                    <!-- Attendance -->
                    <td class="text-right"><?php echo $row['present_days']; ?></td>
                    <td class="text-right"><?php echo $row['absent_days']; ?></td>
                    <td class="text-right"><?php echo $row['week_off']; ?></td>
                    
                    <!-- Earned Salary -->
                    <td class="text-right"><?php echo number_format($row['basic'], 2); ?></td>
                    <td class="text-right"><?php echo number_format($row['vda'], 2); ?></td>
                    <td class="text-right"><?php echo number_format($row['special_allowance'], 2); ?></td>
                    <td class="text-right"><?php echo number_format($row['other_allowance'], 2); ?></td>
                    <td class="text-right"><strong><?php echo number_format($row['gross_salary'], 2); ?></strong></td>
                    
                    <!-- Net Salary (Deductions) -->
                    <td class="text-right"><?php echo number_format($row['pf_employee'], 2); ?></td>
                    <td class="text-right"><?php echo number_format($row['esic_employee'], 2); ?></td>
                    <td class="text-right"><?php echo number_format($row['professional_tax'], 2); ?></td>
                    
                    <!-- Rebill (Employer Contributions) -->
                    <td class="text-right"><?php echo number_format($row['pf_employer'], 2); ?></td>
                    <td class="text-right"><?php echo number_format($row['esic_employer'], 2); ?></td>
                    <td class="text-right"><?php echo number_format($row['leave_provision'], 2); ?></td>
                    <td class="text-right"><?php echo number_format($row['bonus'], 2); ?></td>
                    <td class="text-right"><?php echo number_format($row['food_allowance'], 2); ?></td>
                    <td class="text-right">0.00</td>
                    <td class="text-right">0.00</td>
                    <td class="text-right"><strong><?php echo number_format($row['total_rebill'], 2); ?></strong></td>
                    
                    <!-- Totals -->
                    <td class="text-right"></td>
                    <td class="text-right"><strong><?php echo number_format($row['earned_ctc'], 2); ?></strong></td>
                    <td class="text-right"><?php echo number_format($row['per_day_wages'], 2); ?></td>
                </tr>
                <?php 
                $rowNum++;
                endforeach; 
                ?>
                
                <!-- Summary Row (Row 13 in Excel) -->
                <?php if (!empty($payrollData)): ?>
                <tr class="summary-row">
                    <td colspan="16" class="text-right"><strong>TOTAL:</strong></td>
                    <td class="text-right"><strong><?php echo number_format($totals['pf_employee'], 2); ?></strong></td>
                    <td class="text-right"><strong><?php echo number_format($totals['esic_employee'], 2); ?></strong></td>
                    <td class="text-right"><strong><?php echo number_format($totals['professional_tax'], 2); ?></strong></td>
                    <td class="text-right"><strong><?php echo number_format($totals['pf_employer'], 2); ?></strong></td>
                    <td class="text-right"><strong><?php echo number_format($totals['esic_employer'], 2); ?></strong></td>
                    <td class="text-right"><strong><?php echo number_format($totals['leave_provision'], 2); ?></strong></td>
                    <td class="text-right"><strong><?php echo number_format($totals['bonus'], 2); ?></strong></td>
                    <td class="text-right"><strong><?php echo number_format($totals['food_allowance'], 2); ?></strong></td>
                    <td class="text-right"><strong>0.00</strong></td>
                    <td class="text-right"><strong>0.00</strong></td>
                    <td class="text-right"><strong><?php echo number_format($totals['total_rebill'], 2); ?></strong></td>
                    <td class="text-right"></td>
                    <td class="text-right"><strong><?php echo number_format($totals['net_salary'], 2); ?></strong></td>
                    <td class="text-right"><strong><?php echo number_format($totals['earned_ctc'], 2); ?></strong></td>
                    <td class="text-right"></td>
                </tr>
                <?php endif; ?>
            </tbody>
        </table>
    </div>
</div>

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