<?php
/**
 * Enterprise HRM ERP System - Payroll Reports
 */

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

$pageTitle = 'Payroll Reports';
$currentModule = 'reports';

$siteId = getCurrentSiteId();
$periodId = isset($_GET['period_id']) ? (int)$_GET['period_id'] : 0;
$dateFrom = $_GET['date_from'] ?? date('Y-m-01', strtotime('-1 month'));
$dateTo = $_GET['date_to'] ?? date('Y-m-t', strtotime('-1 month'));

try {
    $db = getDB();
    
    // Get payroll periods for filter
    $stmt = $db->prepare("
        SELECT period_id, period_name, period_code, start_date, end_date 
        FROM payroll_periods 
        WHERE site_id = ?
        ORDER BY start_date DESC
        LIMIT 12
    ");
    $stmt->execute([$siteId]);
    $periods = $stmt->fetchAll();
    
    // Overall Statistics
    $stats = [];
    
    // Build where clause
    $where = "pr.site_id = ?";
    $params = [$siteId];
    
    if ($periodId > 0) {
        $where .= " AND pr.period_id = ?";
        $params[] = $periodId;
    } else {
        // Filter by date range if no specific period
        $where .= " AND pp.start_date >= ? AND pp.end_date <= ?";
        $params[] = $dateFrom;
        $params[] = $dateTo;
    }
    
    // Total Records
    $stmt = $db->prepare("
        SELECT COUNT(*) 
        FROM payroll_records pr
        INNER JOIN payroll_periods pp ON pr.period_id = pp.period_id
        WHERE {$where}
    ");
    $stmt->execute($params);
    $stats['total_records'] = $stmt->fetchColumn();
    
    // Total Gross Salary
    $stmt = $db->prepare("
        SELECT COALESCE(SUM(gross_salary), 0) 
        FROM payroll_records pr
        INNER JOIN payroll_periods pp ON pr.period_id = pp.period_id
        WHERE {$where}
    ");
    $stmt->execute($params);
    $stats['total_gross'] = $stmt->fetchColumn();
    
    // Total Deductions
    $stmt = $db->prepare("
        SELECT COALESCE(SUM(total_deductions), 0) 
        FROM payroll_records pr
        INNER JOIN payroll_periods pp ON pr.period_id = pp.period_id
        WHERE {$where}
    ");
    $stmt->execute($params);
    $stats['total_deductions'] = $stmt->fetchColumn();
    
    // Total Net Salary
    $stmt = $db->prepare("
        SELECT COALESCE(SUM(net_salary), 0) 
        FROM payroll_records pr
        INNER JOIN payroll_periods pp ON pr.period_id = pp.period_id
        WHERE {$where}
    ");
    $stmt->execute($params);
    $stats['total_net'] = $stmt->fetchColumn();
    
    // Average Salary
    $stmt = $db->prepare("
        SELECT COALESCE(AVG(net_salary), 0) 
        FROM payroll_records pr
        INNER JOIN payroll_periods pp ON pr.period_id = pp.period_id
        WHERE {$where}
    ");
    $stmt->execute($params);
    $stats['avg_salary'] = $stmt->fetchColumn();
    
    // Status Breakdown
    $stmt = $db->prepare("
        SELECT payroll_status, COUNT(*) as count
        FROM payroll_records pr
        INNER JOIN payroll_periods pp ON pr.period_id = pp.period_id
        WHERE {$where}
        GROUP BY payroll_status
    ");
    $stmt->execute($params);
    $statusBreakdown = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);
    
    // Period-wise Summary
    $periodWhere = "pp.site_id = ?";
    $periodParams = [$siteId];
    if ($periodId > 0) {
        $periodWhere .= " AND pp.period_id = ?";
        $periodParams[] = $periodId;
    } else {
        $periodWhere .= " AND pp.start_date >= ? AND pp.end_date <= ?";
        $periodParams[] = $dateFrom;
        $periodParams[] = $dateTo;
    }
    
    $stmt = $db->prepare("
        SELECT 
            pp.period_code,
            pp.period_name,
            COUNT(pr.payroll_id) as employees,
            COALESCE(SUM(pr.gross_salary), 0) as total_gross,
            COALESCE(SUM(pr.total_deductions), 0) as total_deductions,
            COALESCE(SUM(pr.net_salary), 0) as total_net
        FROM payroll_periods pp
        LEFT JOIN payroll_records pr ON pp.period_id = pr.period_id
        WHERE {$periodWhere}
        GROUP BY pp.period_id, pp.period_code, pp.period_name
        ORDER BY pp.start_date DESC
    ");
    $stmt->execute($periodParams);
    $periodSummary = $stmt->fetchAll();
    
    // Department-wise Payroll
    $stmt = $db->prepare("
        SELECT 
            d.department_name,
            COUNT(DISTINCT pr.employee_id) as employees,
            COALESCE(SUM(pr.gross_salary), 0) as total_gross,
            COALESCE(SUM(pr.total_deductions), 0) as total_deductions,
            COALESCE(SUM(pr.net_salary), 0) as total_net,
            COALESCE(AVG(pr.net_salary), 0) as avg_salary
        FROM payroll_records pr
        INNER JOIN employees e ON pr.employee_id = e.employee_id
        INNER JOIN payroll_periods pp ON pr.period_id = pp.period_id
        LEFT JOIN departments d ON e.department_id = d.department_id
        WHERE {$where}
        GROUP BY d.department_id, d.department_name
        ORDER BY total_net DESC
    ");
    $stmt->execute($params);
    $deptPayroll = $stmt->fetchAll();
    
} catch (Exception $e) {
    error_log("Payroll report error: " . $e->getMessage());
    $stats = ['total_records' => 0, 'total_gross' => 0, 'total_deductions' => 0, 'total_net' => 0, 'avg_salary' => 0];
    $statusBreakdown = [];
    $periodSummary = [];
    $deptPayroll = [];
    $periods = [];
}

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

<div class="card">
    <div class="card-header">
        <h2 class="card-title">Payroll Reports & Analytics</h2>
        <div style="display: flex; gap: 10px;">
            <a href="<?php echo BASE_URL; ?>/modules/reports/index.php" class="btn btn-secondary">Back to Reports</a>
            <button onclick="exportReport('excel')" class="btn btn-primary">Export Excel</button>
            <button onclick="exportReport('pdf')" class="btn btn-primary">Export PDF</button>
        </div>
    </div>
    
    <!-- Filters -->
    <div style="padding: 20px; background: #f8fafc; border-radius: 8px; margin-bottom: 20px;">
        <form method="GET" style="display: grid; grid-template-columns: repeat(3, 1fr); gap: 15px; align-items: end;">
            <div class="form-group">
                <label class="form-label">Payroll Period</label>
                <select name="period_id" class="form-control" onchange="this.form.submit()">
                    <option value="0">All Periods</option>
                    <?php foreach ($periods as $period): ?>
                    <option value="<?php echo $period['period_id']; ?>" <?php echo $periodId == $period['period_id'] ? 'selected' : ''; ?>>
                        <?php echo htmlspecialchars($period['period_name']); ?>
                    </option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="form-group">
                <label class="form-label">From Date</label>
                <input type="date" name="date_from" class="form-control" value="<?php echo htmlspecialchars($dateFrom); ?>" onchange="this.form.submit()">
            </div>
            <div class="form-group">
                <label class="form-label">To Date</label>
                <input type="date" name="date_to" class="form-control" value="<?php echo htmlspecialchars($dateTo); ?>" onchange="this.form.submit()">
            </div>
        </form>
    </div>
    
    <!-- Statistics Cards -->
    <div style="display: grid; grid-template-columns: repeat(4, 1fr); gap: 20px; margin-bottom: 30px;">
        <div class="card" style="padding: 20px; text-align: center; background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); color: white;">
            <div style="font-size: 28px; font-weight: 600;"><?php echo formatCurrency($stats['total_gross']); ?></div>
            <div style="margin-top: 5px; opacity: 0.9;">Total Gross Salary</div>
        </div>
        <div class="card" style="padding: 20px; text-align: center; background: linear-gradient(135deg, #ee0979 0%, #ff6a00 100%); color: white;">
            <div style="font-size: 28px; font-weight: 600;"><?php echo formatCurrency($stats['total_deductions']); ?></div>
            <div style="margin-top: 5px; opacity: 0.9;">Total Deductions</div>
        </div>
        <div class="card" style="padding: 20px; text-align: center; background: linear-gradient(135deg, #11998e 0%, #38ef7d 100%); color: white;">
            <div style="font-size: 28px; font-weight: 600;"><?php echo formatCurrency($stats['total_net']); ?></div>
            <div style="margin-top: 5px; opacity: 0.9;">Total Net Salary</div>
        </div>
        <div class="card" style="padding: 20px; text-align: center; background: linear-gradient(135deg, #f093fb 0%, #f5576c 100%); color: white;">
            <div style="font-size: 28px; font-weight: 600;"><?php echo formatCurrency($stats['avg_salary']); ?></div>
            <div style="margin-top: 5px; opacity: 0.9;">Average Salary</div>
        </div>
    </div>
    
    <!-- Additional Stats -->
    <div class="card" style="margin-bottom: 30px; padding: 20px;">
        <h3 style="margin-bottom: 15px;">Payroll Summary</h3>
        <div style="display: grid; grid-template-columns: repeat(3, 1fr); gap: 20px;">
            <div>
                <div style="font-size: 24px; font-weight: 600; color: var(--primary-color);">
                    <?php echo number_format($stats['total_records']); ?>
                </div>
                <div style="margin-top: 5px; color: var(--text-secondary);">Total Records</div>
            </div>
            <div>
                <div style="font-size: 24px; font-weight: 600; color: var(--success-color);">
                    <?php echo number_format($statusBreakdown['paid'] ?? 0); ?>
                </div>
                <div style="margin-top: 5px; color: var(--text-secondary);">Paid</div>
            </div>
            <div>
                <div style="font-size: 24px; font-weight: 600; color: var(--warning-color);">
                    <?php echo number_format($statusBreakdown['draft'] ?? 0); ?>
                </div>
                <div style="margin-top: 5px; color: var(--text-secondary);">Pending</div>
            </div>
        </div>
    </div>
    
    <!-- Period-wise Summary -->
    <?php if (count($periodSummary) > 0): ?>
    <div class="card" style="margin-bottom: 30px;">
        <h3 style="margin-bottom: 20px; padding-bottom: 10px; border-bottom: 1px solid var(--border-color);">Period-wise Summary</h3>
        <table class="data-table">
            <thead>
                <tr>
                    <th>Period</th>
                    <th>Employees</th>
                    <th>Gross Salary</th>
                    <th>Deductions</th>
                    <th>Net Salary</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($periodSummary as $period): ?>
                <tr>
                    <td>
                        <strong><?php echo htmlspecialchars($period['period_code']); ?></strong><br>
                        <small><?php echo htmlspecialchars($period['period_name']); ?></small>
                    </td>
                    <td><?php echo number_format($period['employees']); ?></td>
                    <td><?php echo formatCurrency($period['total_gross']); ?></td>
                    <td style="color: var(--error-color);"><?php echo formatCurrency($period['total_deductions']); ?></td>
                    <td style="color: var(--success-color); font-weight: 600;"><?php echo formatCurrency($period['total_net']); ?></td>
                </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </div>
    <?php endif; ?>
    
    <!-- Department-wise Payroll -->
    <?php if (count($deptPayroll) > 0): ?>
    <div class="card">
        <h3 style="margin-bottom: 20px; padding-bottom: 10px; border-bottom: 1px solid var(--border-color);">Department-wise Payroll</h3>
        <table class="data-table">
            <thead>
                <tr>
                    <th>Department</th>
                    <th>Employees</th>
                    <th>Total Gross</th>
                    <th>Total Deductions</th>
                    <th>Total Net</th>
                    <th>Avg Salary</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($deptPayroll as $dept): ?>
                <tr>
                    <td><strong><?php echo htmlspecialchars($dept['department_name'] ?? 'N/A'); ?></strong></td>
                    <td><?php echo number_format($dept['employees']); ?></td>
                    <td><?php echo formatCurrency($dept['total_gross']); ?></td>
                    <td style="color: var(--error-color);"><?php echo formatCurrency($dept['total_deductions']); ?></td>
                    <td style="color: var(--success-color); font-weight: 600;"><?php echo formatCurrency($dept['total_net']); ?></td>
                    <td><?php echo formatCurrency($dept['avg_salary']); ?></td>
                </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </div>
    <?php endif; ?>
</div>

<script>
function exportReport(format) {
    const params = new URLSearchParams(window.location.search);
    params.append('export', format);
    window.location.href = '<?php echo BASE_URL; ?>/modules/reports/export_payroll.php?' + params.toString();
}
</script>

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

