<?php
/**
 * Enterprise HRM ERP System - Statutory Management (PF, ESIC)
 * Excel-like Statutory Register
 */

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

$pageTitle = 'Statutory Management (PF & ESIC)';
$currentModule = 'statutory';

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

// Validate
if ($selectedMonth < 1 || $selectedMonth > 12) $selectedMonth = (int)date('m');
if ($selectedYear < 2020 || $selectedYear > 2100) $selectedYear = (int)date('Y');

// Get payroll period for the month
$periodId = null;
$statutoryData = [];
$summary = ['total_pf' => 0, 'total_esic' => 0, 'employee_count' => 0];

try {
    $db = getDB();
    
    // Get payroll period
    $stmt = $db->prepare("
        SELECT period_id FROM payroll_periods
        WHERE site_id = ? AND MONTH(start_date) = ? AND YEAR(start_date) = ?
        AND period_status IN ('calculated', 'approved', 'paid', 'locked')
        ORDER BY start_date DESC LIMIT 1
    ");
    $stmt->execute([$siteId, $selectedMonth, $selectedYear]);
    $periodId = $stmt->fetchColumn();
    
    if ($periodId) {
        // Get statutory data from payroll
        $stmt = $db->prepare("
            SELECT 
                pr.employee_id, pr.payroll_id,
                e.employee_code, e.first_name, e.last_name, e.middle_name,
                e.pf_number, e.esic_number, e.uan_number,
                d.department_name, des.designation_name,
                SUM(CASE WHEN sc.component_code = 'PF' AND sc.component_type = 'deduction' THEN pd.amount ELSE 0 END) as pf_amount,
                SUM(CASE WHEN sc.component_code = 'ESIC' AND sc.component_type = 'deduction' THEN pd.amount ELSE 0 END) as esic_amount,
                pr.gross_salary, pr.net_salary
            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
            LEFT JOIN payroll_details pd ON pr.payroll_id = pd.payroll_id
            LEFT JOIN salary_components sc ON pd.component_id = sc.component_id
            WHERE pr.period_id = ? AND pr.site_id = ?
            GROUP BY pr.employee_id, pr.payroll_id
            ORDER BY e.employee_code
        ");
        $stmt->execute([$periodId, $siteId]);
        $statutoryData = $stmt->fetchAll();
        
        // Calculate summary
        foreach ($statutoryData as $data) {
            $summary['total_pf'] += $data['pf_amount'];
            $summary['total_esic'] += $data['esic_amount'];
        }
        $summary['employee_count'] = count($statutoryData);
    }
    
} catch (Exception $e) {
    error_log("Statutory list error: " . $e->getMessage());
}

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

<div class="card">
    <div class="card-header">
        <h2 class="card-title">Statutory Management (PF & ESIC)</h2>
        <div style="display: flex; gap: 10px; align-items: center;">
            <select id="monthSelect" class="form-control" style="width: 120px;" onchange="updateMonthYear()">
                <?php for ($m = 1; $m <= 12; $m++): ?>
                <option value="<?php echo $m; ?>" <?php echo $selectedMonth == $m ? 'selected' : ''; ?>>
                    <?php echo date('F', mktime(0, 0, 0, $m, 1)); ?>
                </option>
                <?php endfor; ?>
            </select>
            <select id="yearSelect" class="form-control" style="width: 100px;" onchange="updateMonthYear()">
                <?php for ($y = date('Y') - 2; $y <= date('Y') + 1; $y++): ?>
                <option value="<?php echo $y; ?>" <?php echo $selectedYear == $y ? 'selected' : ''; ?>>
                    <?php echo $y; ?>
                </option>
                <?php endfor; ?>
            </select>
            <?php if ($periodId): ?>
            <a href="<?php echo BASE_URL; ?>/modules/statutory/export.php?period_id=<?php echo $periodId; ?>&format=excel" 
               class="btn btn-secondary">Export Excel</a>
            <?php endif; ?>
        </div>
    </div>
    
    <!-- Summary Cards -->
    <div style="display: grid; grid-template-columns: repeat(3, 1fr); gap: 15px; margin-top: 20px;">
        <div class="stat-card" style="background: #dbeafe; border-left: 4px solid #1e40af;">
            <div class="stat-label">Total PF</div>
            <div class="stat-value"><?php echo formatCurrency($summary['total_pf']); ?></div>
        </div>
        <div class="stat-card" style="background: #d1fae5; border-left: 4px solid #065f46;">
            <div class="stat-label">Total ESIC</div>
            <div class="stat-value"><?php echo formatCurrency($summary['total_esic']); ?></div>
        </div>
        <div class="stat-card" style="background: #fef3c7; border-left: 4px solid #92400e;">
            <div class="stat-label">Employees</div>
            <div class="stat-value"><?php echo number_format($summary['employee_count']); ?></div>
        </div>
    </div>
    
    <!-- Statutory Register Table -->
    <div style="margin-top: 30px;">
        <h3 style="margin-bottom: 15px;">Statutory Register - <?php echo date('F Y', mktime(0, 0, 0, $selectedMonth, 1, $selectedYear)); ?></h3>
        
        <?php if (count($statutoryData) > 0): ?>
        <div style="overflow-x: auto;">
            <table class="data-table excel-grid">
                <thead>
                    <tr>
                        <th>Emp Code</th>
                        <th>Employee Name</th>
                        <th>Department</th>
                        <th>PF Number</th>
                        <th>UAN</th>
                        <th>ESIC Number</th>
                        <th style="text-align: right;">Gross Salary</th>
                        <th style="text-align: right;">PF Amount</th>
                        <th style="text-align: right;">ESIC Amount</th>
                        <th style="text-align: right;">Net Salary</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($statutoryData as $data): ?>
                    <tr>
                        <td><strong><?php echo htmlspecialchars($data['employee_code']); ?></strong></td>
                        <td><?php echo htmlspecialchars(trim($data['first_name'] . ' ' . ($data['middle_name'] ?? '') . ' ' . $data['last_name'])); ?></td>
                        <td><?php echo htmlspecialchars($data['department_name'] ?? 'N/A'); ?></td>
                        <td><?php echo htmlspecialchars($data['pf_number'] ?? 'N/A'); ?></td>
                        <td><?php echo htmlspecialchars($data['uan_number'] ?? 'N/A'); ?></td>
                        <td><?php echo htmlspecialchars($data['esic_number'] ?? 'N/A'); ?></td>
                        <td style="text-align: right;"><?php echo formatCurrency($data['gross_salary']); ?></td>
                        <td style="text-align: right;"><?php echo formatCurrency($data['pf_amount']); ?></td>
                        <td style="text-align: right;"><?php echo formatCurrency($data['esic_amount']); ?></td>
                        <td style="text-align: right;"><strong><?php echo formatCurrency($data['net_salary']); ?></strong></td>
                    </tr>
                    <?php endforeach; ?>
                    <tr style="background: #f3f4f6; font-weight: 600;">
                        <td colspan="6" style="text-align: right;"><strong>TOTAL:</strong></td>
                        <td style="text-align: right;"><strong><?php echo formatCurrency(array_sum(array_column($statutoryData, 'gross_salary'))); ?></strong></td>
                        <td style="text-align: right;"><strong><?php echo formatCurrency($summary['total_pf']); ?></strong></td>
                        <td style="text-align: right;"><strong><?php echo formatCurrency($summary['total_esic']); ?></strong></td>
                        <td style="text-align: right;"><strong><?php echo formatCurrency(array_sum(array_column($statutoryData, 'net_salary'))); ?></strong></td>
                    </tr>
                </tbody>
            </table>
        </div>
        <?php else: ?>
        <div class="table-empty">
            <div class="table-empty-icon">📋</div>
            <div class="table-empty-message">No statutory data found for this month</div>
            <div class="table-empty-submessage">
                <p style="color: var(--text-secondary);">Please calculate payroll for this month first.</p>
            </div>
        </div>
        <?php endif; ?>
    </div>
</div>

<script>
function updateMonthYear() {
    const month = document.getElementById('monthSelect').value;
    const year = document.getElementById('yearSelect').value;
    window.location.href = '?month=' + month + '&year=' + year;
}
</script>

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

