<?php
require('fpdf/fpdf.php');
include 'db.php';

// Get optional year filter
$year = $_GET['year'] ?? date('Y');

// Query: salaries by month
$sqlPayroll = "
    SELECT payroll_month, SUM(net_pay) AS total_net, COUNT(id) AS total_records
    FROM payrolls
    WHERE YEAR(STR_TO_DATE(payroll_month, '%Y-%m')) = ?
    GROUP BY payroll_month
    ORDER BY payroll_month
";
$stmt = $conn->prepare($sqlPayroll);
$stmt->bind_param("i", $year);
$stmt->execute();
$resPayroll = $stmt->get_result();

$payrollData = [];
while ($row = $resPayroll->fetch_assoc()) {
    $payrollData[$row['payroll_month']] = [
        'month' => $row['payroll_month'],
        'records' => $row['total_records'],
        'net' => $row['total_net'],
        'payments' => 0
    ];
}

// Query: payments (advances) by month
$sqlPayments = "
    SELECT DATE_FORMAT(payment_date,'%Y-%m') AS pay_month, SUM(amount) AS total_payment
    FROM payments
    WHERE YEAR(payment_date) = ?
    GROUP BY pay_month
";
$stmt2 = $conn->prepare($sqlPayments);
$stmt2->bind_param("i", $year);
$stmt2->execute();
$resPayments = $stmt2->get_result();

while ($row = $resPayments->fetch_assoc()) {
    $m = $row['pay_month'];
    if (!isset($payrollData[$m])) {
        $payrollData[$m] = [
            'month' => $m,
            'records' => 0,
            'net' => 0,
            'payments' => $row['total_payment']
        ];
    } else {
        $payrollData[$m]['payments'] = $row['total_payment'];
    }
}

// Sort by month
ksort($payrollData);

// Totals
$grand_salaries = 0;
$grand_payments = 0;
$grand_total = 0;
foreach ($payrollData as $m => $row) {
    $grand_salaries += $row['net'];
    $grand_payments += $row['payments'];
    $grand_total += $row['net'] + $row['payments'];
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Payroll Summary Report</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body class="container py-4">
    <h2 class="mb-4">📊 Payroll Payout Summary (<?= htmlspecialchars($year) ?>)</h2>

    <form method="get" class="row g-3 mb-3">
        <div class="col-md-3">
            <label class="form-label">Select Year</label>
            <input type="number" name="year" value="<?= htmlspecialchars($year) ?>" class="form-control">
        </div>
        <div class="col-md-3 align-self-end">
            <button type="submit" class="btn btn-primary">🔍 Filter</button>
        </div>
        <div class="col-md-3 align-self-end">
            <a href="?year=<?= htmlspecialchars($year) ?>&export=pdf" class="btn btn-danger">⬇ Download PDF</a>
        </div>
    </form>

    <table class="table table-bordered">
        <thead class="table-dark">
            <tr>
                <th>Month</th>
                <th>Employees</th>
                <th>Net Salaries (RM)</th>
                <th>Payments (RM)</th>
                <th>Total (RM)</th>
            </tr>
        </thead>
        <tbody>
            <?php if (!empty($payrollData)): ?>
                <?php foreach ($payrollData as $row): ?>
                <tr>
                    <td>
                        <a href="payroll_month_detail.php?month=<?= urlencode($row['month']) ?>&year=<?= urlencode($year) ?>" class="text-decoration-none fw-bold">
                            <?= htmlspecialchars($row['month']) ?>
                        </a>
                    </td>
                    <td class="text-center"><?= $row['records'] ?></td>
                    <td class="text-end">RM <?= number_format($row['net'],2) ?></td>
                    <td class="text-end">RM <?= number_format($row['payments'],2) ?></td>
                    <td class="text-end fw-bold">RM <?= number_format($row['net'] + $row['payments'],2) ?></td>
                </tr>
                <?php endforeach; ?>
                <tr class="table-secondary fw-bold">
                    <td class="text-end" colspan="2">Grand Totals</td>
                    <td class="text-end">RM <?= number_format($grand_salaries,2) ?></td>
                    <td class="text-end">RM <?= number_format($grand_payments,2) ?></td>
                    <td class="text-end">RM <?= number_format($grand_total,2) ?></td>
                </tr>
            <?php else: ?>
                <tr><td colspan="5" class="text-center">No records found</td></tr>
            <?php endif; ?>
        </tbody>
    </table>
</body>
</html>
