<?php
require_once 'pdo_config.php';

header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=SF2_Attendance_Report.xls");
header("Pragma: no-cache");
header("Expires: 0");

$grade = $_POST['grade'] ?? '';
$month = $_POST['month'] ?? '';
$status = $_POST['status'] ?? '';

$query = "
    SELECT s.lrn, s.name, s.sex, s.birthday, s.contact, 
           c.am_in, c.am_out, c.pm_in, c.pm_out
    FROM students s
    LEFT JOIN checkins c 
        ON s.lrn = c.lrn 
        AND DATE_FORMAT(c.scan_date, '%Y-%m') = :month
    WHERE s.grade = :grade
";

if ($status === 'present') {
    $query .= " AND c.am_in IS NOT NULL AND c.am_out IS NOT NULL AND c.pm_in IS NOT NULL AND c.pm_out IS NOT NULL";
} elseif ($status === 'absent') {
    $query .= " AND (c.am_in IS NULL OR c.am_out IS NULL OR c.pm_in IS NULL OR c.pm_out IS NULL)";
}

$stmt = $pdo->prepare($query);
$stmt->execute(['grade' => $grade, 'month' => $month]);
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Excel Table Start
echo "<table border='1'>";
echo "<tr>
    <th>LRN</th>
    <th>Name</th>
    <th>Sex</th>
    <th>Birthday</th>
    <th>Contact</th>
    <th>AM IN</th>
    <th>AM OUT</th>
    <th>PM IN</th>
    <th>PM OUT</th>
    <th>Status</th>
</tr>";

foreach ($data as $row) {
    $isPresent = !empty($row['am_in']) && !empty($row['am_out']) && !empty($row['pm_in']) && !empty($row['pm_out']);
    echo "<tr>";
    echo "<td>{$row['lrn']}</td>";
    echo "<td>{$row['name']}</td>";
    echo "<td>{$row['sex']}</td>";
    echo "<td>{$row['birthday']}</td>";
    echo "<td>{$row['contact']}</td>";
    echo "<td>" . ($row['am_in'] ?: '--') . "</td>";
    echo "<td>" . ($row['am_out'] ?: '--') . "</td>";
    echo "<td>" . ($row['pm_in'] ?: '--') . "</td>";
    echo "<td>" . ($row['pm_out'] ?: '--') . "</td>";
    echo "<td>" . ($isPresent ? 'Present' : 'Absent') . "</td>";
    echo "</tr>";
}

echo "</table>";
exit;
?>