<?php
/**
 * Enterprise HRM ERP System - Payroll Register (Excel-like Salary Sheet)
 * Server-side calculations only
 */

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

$pageTitle = 'Payroll Register';
$currentModule = 'payroll';

$siteId = getCurrentSiteId();
$periodId = isset($_GET['period_id']) ? (int)$_GET['period_id'] : 0;
$selectedMonth = isset($_GET['month']) ? (int)$_GET['month'] : (int)date('m');
$selectedYear = isset($_GET['year']) ? (int)$_GET['year'] : (int)date('Y');

// Get payroll period
$payrollPeriod = null;
$payrollRecords = [];
$isLocked = false;

try {
    $db = getDB();
    
    if ($periodId > 0) {
        $stmt = $db->prepare("SELECT * FROM payroll_periods WHERE period_id = ? AND site_id = ?");
        $stmt->execute([$periodId, $siteId]);
        $payrollPeriod = $stmt->fetch();
        
        if ($payrollPeriod) {
            $selectedMonth = (int)date('m', strtotime($payrollPeriod['start_date']));
            $selectedYear = (int)date('Y', strtotime($payrollPeriod['start_date']));
            $isLocked = in_array($payrollPeriod['period_status'], ['locked', 'paid']);
            
            // Get payroll records
            $stmt = $db->prepare("
                SELECT pr.*, e.employee_code, e.first_name, e.last_name, e.middle_name,
                       d.department_name, des.designation_name
                FROM payroll_records pr
                INNER JOIN employees e ON pr.employee_id = e.employee_id
                LEFT JOIN departments d ON e.department_id = d.department_id
                LEFT JOIN designations des ON e.designation_id = des.designation_id
                WHERE pr.period_id = ? AND pr.site_id = ?
                ORDER BY e.employee_code
            ");
            $stmt->execute([$periodId, $siteId]);
            $payrollRecords = $stmt->fetchAll();
        }
    }
    
    // Get available periods
    $stmt = $db->prepare("
        SELECT * FROM payroll_periods
        WHERE site_id = ?
        ORDER BY start_date DESC
        LIMIT 12
    ");
    $stmt->execute([$siteId]);
    $periods = $stmt->fetchAll();
    
} catch (Exception $e) {
    error_log("Payroll register error: " . $e->getMessage());
    $periods = [];
}

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

<div class="card">
    <div class="card-header">
        <h2 class="card-title">Payroll Register (Salary Sheet)</h2>
        <div style="display: flex; gap: 10px; align-items: center;">
            <select id="periodSelect" class="form-control" style="width: 250px;" onchange="window.location.href='?period_id='+this.value">
                <option value="0">Select Payroll Period</option>
                <?php foreach ($periods as $p): ?>
                <option value="<?php echo $p['period_id']; ?>" <?php echo $periodId == $p['period_id'] ? 'selected' : ''; ?>>
                    <?php echo htmlspecialchars($p['period_name'] . ' (' . formatDate($p['start_date']) . ' to ' . formatDate($p['end_date']) . ')'); ?>
                </option>
                <?php endforeach; ?>
            </select>
            <?php if ($periodId > 0 && !$isLocked): ?>
            <a href="<?php echo BASE_URL; ?>/modules/payroll/calculate.php?period_id=<?php echo $periodId; ?>" 
               class="btn btn-primary">Calculate Payroll</a>
            <?php endif; ?>
            <?php if ($periodId > 0): ?>
            <a href="<?php echo BASE_URL; ?>/modules/payroll/export.php?period_id=<?php echo $periodId; ?>&format=excel" 
               class="btn btn-secondary">Export Excel</a>
            <?php endif; ?>
        </div>
    </div>
    
    <?php if ($isLocked): ?>
    <div class="alert alert-warning" style="margin-top: 20px;">
        <strong>⚠️ Locked:</strong> This payroll period is locked and cannot be modified.
    </div>
    <?php endif; ?>
    
    <?php if ($periodId > 0 && count($payrollRecords) > 0): ?>
    <!-- Excel-like Payroll Register -->
    <div style="margin-top: 20px; overflow-x: auto;">
        <table class="data-table excel-grid" style="min-width: 1500px;">
            <thead>
                <tr style="background: #f3f4f6;">
                    <th rowspan="2" style="vertical-align: middle;">Emp Code</th>
                    <th rowspan="2" style="vertical-align: middle;">Employee Name</th>
                    <th rowspan="2" style="vertical-align: middle;">Dept</th>
                    <th rowspan="2" style="vertical-align: middle;">Designation</th>
                    <th colspan="4" style="text-align: center;">Attendance</th>
                    <th colspan="3" style="text-align: center;">Earnings</th>
                    <th colspan="4" style="text-align: center;">Deductions</th>
                    <th rowspan="2" style="vertical-align: middle;">Net Salary</th>
                </tr>
                <tr style="background: #f3f4f6;">
                    <th style="text-align: center;">WD</th>
                    <th style="text-align: center;">P</th>
                    <th style="text-align: center;">A</th>
                    <th style="text-align: center;">L</th>
                    <th style="text-align: center;">Basic</th>
                    <th style="text-align: center;">HRA</th>
                    <th style="text-align: center;">Total Earn</th>
                    <th style="text-align: center;">PF</th>
                    <th style="text-align: center;">ESIC</th>
                    <th style="text-align: center;">TDS</th>
                    <th style="text-align: center;">Total Ded</th>
                </tr>
            </thead>
            <tbody>
                <?php 
                $totals = [
                    'working_days' => 0, 'present' => 0, 'absent' => 0, 'leave' => 0,
                    'basic' => 0, 'hra' => 0, 'total_earnings' => 0,
                    'pf' => 0, 'esic' => 0, 'tds' => 0, 'total_deductions' => 0, 'net_salary' => 0
                ];
                
                foreach ($payrollRecords as $pr): 
                    // Get payroll details
                    $stmt = $db->prepare("
                        SELECT sc.component_code, pd.amount
                        FROM payroll_details pd
                        INNER JOIN salary_components sc ON pd.component_id = sc.component_id
                        WHERE pd.payroll_id = ?
                    ");
                    $stmt->execute([$pr['payroll_id']]);
                    $details = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);
                    
                    $basic = $details['BASIC'] ?? 0;
                    $hra = $details['HRA'] ?? 0;
                    $pf = $details['PF'] ?? 0;
                    $esic = $details['ESIC'] ?? 0;
                    $tds = $details['TDS'] ?? 0;
                    
                    $totals['working_days'] += $pr['working_days'];
                    $totals['present'] += $pr['present_days'];
                    $totals['absent'] += $pr['absent_days'];
                    $totals['basic'] += $basic;
                    $totals['hra'] += $hra;
                    $totals['total_earnings'] += $pr['total_earnings'];
                    $totals['pf'] += $pf;
                    $totals['esic'] += $esic;
                    $totals['tds'] += $tds;
                    $totals['total_deductions'] += $pr['total_deductions'];
                    $totals['net_salary'] += $pr['net_salary'];
                ?>
                <tr>
                    <td><strong><?php echo htmlspecialchars($pr['employee_code']); ?></strong></td>
                    <td><?php echo htmlspecialchars(trim($pr['first_name'] . ' ' . ($pr['middle_name'] ?? '') . ' ' . $pr['last_name'])); ?></td>
                    <td><?php echo htmlspecialchars($pr['department_name'] ?? 'N/A'); ?></td>
                    <td><?php echo htmlspecialchars($pr['designation_name'] ?? 'N/A'); ?></td>
                    <td style="text-align: right;"><?php echo number_format($pr['working_days'], 1); ?></td>
                    <td style="text-align: right;"><?php echo number_format($pr['present_days'], 1); ?></td>
                    <td style="text-align: right;"><?php echo number_format($pr['absent_days'], 1); ?></td>
                    <td style="text-align: right;"><?php echo number_format($pr['working_days'] - $pr['present_days'] - $pr['absent_days'], 1); ?></td>
                    <td style="text-align: right;"><?php echo formatCurrency($basic); ?></td>
                    <td style="text-align: right;"><?php echo formatCurrency($hra); ?></td>
                    <td style="text-align: right;"><strong><?php echo formatCurrency($pr['total_earnings']); ?></strong></td>
                    <td style="text-align: right;"><?php echo formatCurrency($pf); ?></td>
                    <td style="text-align: right;"><?php echo formatCurrency($esic); ?></td>
                    <td style="text-align: right;"><?php echo formatCurrency($tds); ?></td>
                    <td style="text-align: right;"><?php echo formatCurrency($pr['total_deductions']); ?></td>
                    <td style="text-align: right;"><strong><?php echo formatCurrency($pr['net_salary']); ?></strong></td>
                </tr>
                <?php endforeach; ?>
                <tr style="background: #f3f4f6; font-weight: 600;">
                    <td colspan="4" style="text-align: right;"><strong>TOTAL:</strong></td>
                    <td style="text-align: right;"><strong><?php echo number_format($totals['working_days'], 1); ?></strong></td>
                    <td style="text-align: right;"><strong><?php echo number_format($totals['present'], 1); ?></strong></td>
                    <td style="text-align: right;"><strong><?php echo number_format($totals['absent'], 1); ?></strong></td>
                    <td style="text-align: right;"><strong><?php echo number_format($totals['leave'], 1); ?></strong></td>
                    <td style="text-align: right;"><strong><?php echo formatCurrency($totals['basic']); ?></strong></td>
                    <td style="text-align: right;"><strong><?php echo formatCurrency($totals['hra']); ?></strong></td>
                    <td style="text-align: right;"><strong><?php echo formatCurrency($totals['total_earnings']); ?></strong></td>
                    <td style="text-align: right;"><strong><?php echo formatCurrency($totals['pf']); ?></strong></td>
                    <td style="text-align: right;"><strong><?php echo formatCurrency($totals['esic']); ?></strong></td>
                    <td style="text-align: right;"><strong><?php echo formatCurrency($totals['tds']); ?></strong></td>
                    <td style="text-align: right;"><strong><?php echo formatCurrency($totals['total_deductions']); ?></strong></td>
                    <td style="text-align: right;"><strong><?php echo formatCurrency($totals['net_salary']); ?></strong></td>
                </tr>
            </tbody>
        </table>
    </div>
    <?php elseif ($periodId > 0): ?>
    <div class="table-empty" style="margin-top: 30px;">
        <div class="table-empty-icon">💰</div>
        <div class="table-empty-message">No payroll records found for this period</div>
        <div class="table-empty-submessage">
            <a href="<?php echo BASE_URL; ?>/modules/payroll/calculate.php?period_id=<?php echo $periodId; ?>" 
               class="btn btn-primary">Calculate Payroll</a>
        </div>
    </div>
    <?php else: ?>
    <div class="table-empty" style="margin-top: 30px;">
        <div class="table-empty-icon">💰</div>
        <div class="table-empty-message">Please select a payroll period to view register</div>
    </div>
    <?php endif; ?>
</div>

<style>
.excel-grid {
    font-size: 12px;
    border-collapse: collapse;
}
.excel-grid th,
.excel-grid td {
    padding: 6px 8px;
    border: 1px solid #d1d5db;
    white-space: nowrap;
}
.excel-grid thead th {
    background: #f3f4f6;
    font-weight: 600;
    position: sticky;
    top: 0;
    z-index: 10;
}
.excel-grid tbody tr:hover {
    background: #f9fafb;
}
</style>

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

