<?php
session_start();
date_default_timezone_set('Asia/Manila');
$today = date('Y-m-d');
$currentTime = date('H:i');

// DB connection
$conn = new mysqli("localhost", "root", "", "school_checkin");
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Get filters
$grade = isset($_GET['grade']) && $_GET['grade'] !== '' ? $_GET['grade'] : 'All';
$from = $_GET['from'] ?? '';
$to = $_GET['to'] ?? '';
$session_filter = $_GET['session'] ?? 'All';

// Determine date being checked
$check_date = (!empty($from) && !empty($to)) ? $from : $today;
$check_day = date('l', strtotime($check_date));

// Skip weekends
if ($check_day === 'Saturday' || $check_day === 'Sunday') {
    echo "<h2>No Class on " . date('l, F d, Y', strtotime($check_date)) . "</h2>";
    exit;
}

// Holiday check
$holidayCheck = $conn->query("SELECT * FROM holidays WHERE date = '$check_date'");
if ($holidayCheck && $holidayCheck->num_rows > 0) {
    $holiday = $holidayCheck->fetch_assoc();
    echo "<h2>No classes on " . date('F d, Y', strtotime($check_date)) . "<br><em>({$holiday['description']})</em></h2>";
    exit;
}

// Students query
$whereGrade = ($grade && $grade !== 'All') ? "WHERE grade = '$grade'" : "";
$studentSQL = "SELECT * FROM students $whereGrade ORDER BY 
    CASE 
        WHEN grade='Nursery' THEN 0
        WHEN grade='Kindergarten' THEN 1
        WHEN grade='Grade 1' THEN 2
        WHEN grade='Grade 2' THEN 3
        WHEN grade='Grade 3' THEN 4
        WHEN grade='Grade 4' THEN 5
        WHEN grade='Grade 5' THEN 6
        WHEN grade='Grade 6' THEN 7
        ELSE 99
    END, last_name, first_name";
$students = $conn->query($studentSQL);

// Checkins query
$dateClause = (!empty($from) && !empty($to)) ? "scan_date BETWEEN '$from' AND '$to'" : "scan_date = '$check_date'";
$whereCheckGrade = ($grade && $grade !== 'All') ? "AND grade = '$grade'" : "";
$checkSQL = "SELECT * FROM checkins WHERE $dateClause $whereCheckGrade";
$checkins = $conn->query($checkSQL);

// Preload checkins
$logs = [];
while ($row = $checkins->fetch_assoc()) {
    $logs[$row['lrn']] = $row;
}
?>
<!DOCTYPE html>
<html>
<head>
    <title>Absent Students - SDAESI</title>
    <style>
        body { font-family: Arial; background: #121212; color: #fff; padding: 30px; text-align: center;}
        table { margin: 0 auto 30px auto; border-collapse: collapse; width: 90%; background: #1e1e1e;}
        th, td { padding: 12px; border: 1px solid #444;}
        th { background: #bb2d3b; color: #fff;}
        .btn-back { margin-bottom: 15px; padding: 8px 16px; background: #495057; color: #fff; text-decoration: none; border-radius: 5px;}
        .btn-back:hover { background: #343a40;}
        select, input[type="date"], button { padding: 6px; margin: 5px; border-radius: 4px; border: none;}
        button { background-color: #198754; color: #fff; cursor: pointer;}
        button:hover { background-color: #157347;}
        .logo-container { margin-bottom: 15px; text-align: center; }
        .logo-img { width: 110px; height: 110px; border-radius: 50%; box-shadow: 0 2px 8px #0006; background: #fff; border: 3px solid #fff; object-fit: cover; }
        .btn-reset { margin-left: 10px; background: #bb2d3b; color: #fff; border: none; padding: 6px 20px; border-radius: 4px; cursor: pointer;}
        .btn-reset:hover { background: #a71d2a;}
    </style>
</head>
<body>
    <div class="logo-container">
        <img src="logo.png" alt="SDAESI Logo" class="logo-img">
    </div>
    <h1>SDAESI - Absent Students</h1>
    <a href="dashboard.php" class="btn-back">← Back to Dashboard</a>
    <form method="get" style="display:inline;">
        <label>Grade:</label>
        <select name="grade">
            <option value="All" <?= $grade == 'All' ? 'selected' : '' ?>>-- All Grades --</option>
            <option value="Nursery" <?= $grade == 'Nursery' ? 'selected' : '' ?>>Nursery</option>
            <option value="Kindergarten" <?= $grade == 'Kindergarten' ? 'selected' : '' ?>>Kindergarten</option>
            <?php for ($g = 1; $g <= 6; $g++): ?>
                <option value="Grade <?= $g ?>" <?= $grade == "Grade $g" ? 'selected' : '' ?>>Grade <?= $g ?></option>
            <?php endfor; ?>
        </select>
        <label>From:</label>
        <input type="date" name="from" value="<?= htmlspecialchars($from) ?>">
        <label>To:</label>
        <input type="date" name="to" value="<?= htmlspecialchars($to) ?>">
        <label>Session:</label>
        <select name="session">
            <option value="All" <?= $session_filter == 'All' ? 'selected' : '' ?>>All</option>
            <option value="AM" <?= $session_filter == 'AM' ? 'selected' : '' ?>>AM</option>
            <option value="PM" <?= $session_filter == 'PM' ? 'selected' : '' ?>>PM</option>
        </select>
        <button type="submit">Filter</button>
    </form>
    <form method="get" style="display:inline;">
        <button type="submit" class="btn-reset">Reset</button>
    </form>
    <h4>
        <?php 
        if (!empty($from) && !empty($to)) {
            echo "Showing absents from <strong>" . date('F d, Y', strtotime($from)) . "</strong> to <strong>" . date('F d, Y', strtotime($to)) . "</strong>";
        } else {
            echo "Showing absents for today: <strong>" . date('F d, Y', strtotime($check_date)) . "</strong>";
        }
        ?>
    </h4>
    <?php
    $cutoff = '18:30';
    if ($currentTime < $cutoff && empty($from) && empty($to)) {
        echo "<h3>Absent list will appear after 6:30 PM.</h3>";
    } else {
    ?>
    <table>
        <tr>
            <th>LRN</th>
            <th>Name</th>
            <th>Grade</th>
            <th>Session</th>
            <th>AM OUT</th>
            <th>PM OUT</th>
            <th>Status</th>
        </tr>
        <?php
        $hasAbsent = false;
        while ($row = $students->fetch_assoc()) {
            $lrn = $row['lrn'];
            $session = $row['session'] ?? '';
            $grade = $row['grade'];
            $session = $row['schedule'];
                
            // Standardize session for comparison
            if ($session == 'AM') $session = 'Morning';
            if ($session == 'PM') $session = 'Afternoon';

            $am_in = $logs[$lrn]['am_in'] ?? '';
            $am_out = $logs[$lrn]['am_out'] ?? '';
            $pm_in = $logs[$lrn]['pm_in'] ?? '';
            $pm_out = $logs[$lrn]['pm_out'] ?? '';

            // Modified absent logic - special handling for Nursery and Kindergarten
            $isAbsent = false;
            if ($grade == 'Nursery' || $grade == 'Kindergarten') {
                // Nursery/Kindergarten - different students per session
                if ($session == 'Morning') {
                    // Morning batch students - check AM session only
                    $isAbsent = empty($am_in) && empty($am_out);
                } else if ($session == 'Afternoon') {
                    // Afternoon batch students - check PM session only
                    $isAbsent = empty($pm_in) && empty($pm_out);
                }
            } else {
                // Grade 1-6 - same students, full day
                $isAbsent = empty($am_in) && empty($am_out) && empty($pm_in) && empty($pm_out);
            }

            // Filter by session if needed
            $show = false;
            if ($isAbsent) {
                if ($session_filter == 'AM' && $session == 'Morning') $show = true;
                else if ($session_filter == 'PM' && $session == 'Afternoon') $show = true;
                else if ($session_filter == 'All') $show = true;
            }

            if ($show) {
                $hasAbsent = true;
                $fullname = strtoupper($row['last_name']) . ', ' . ucwords(strtolower($row['first_name'] . ' ' . $row['middle_name']));
                
                // Display logic for check marks
                if ($grade == 'Nursery' || $grade == 'Kindergarten') {
                    $am_status = ($session == 'Morning') ? ($am_out ? '✅' : '❌') : 'N/A';
                    $pm_status = ($session == 'Afternoon') ? ($pm_out ? '✅' : '❌') : 'N/A';
                } else {
                    $am_status = ($am_out ? '✅' : '❌');
                    $pm_status = ($pm_out ? '✅' : '❌');

                }
                
                echo "<tr>
                    <td>{$lrn}</td>
                    <td>{$fullname}</td>
                    <td>{$row['grade']}</td>
                    <td>{$session}</td>
                    <td>{$am_status}</td>
                    <td>{$pm_status}</td>
                    <td>Absent</td>
                </tr>";

                // -------- SF2 LOGIC TO SYNC ABSENT TO SF2 SUMMARY --------
                // Compute values for SF2 absent fields
                $sf2_date = $check_date;
                $sf2_am_absent = ($am_status == '❌') ? 1 : 0;
                $sf2_pm_absent = ($pm_status == '❌') ? 1 : 0;
                $sf2_encoded_by = $_SESSION['login'] ?? $_SESSION['username'] ?? 'absent.php';

                // Insert or update SF2 table for absent student
                $sf2_stmt = $conn->prepare(
                    "INSERT INTO sf2 (lrn, date, am_absent, pm_absent, encoded_by, timestamp)
                     VALUES (?, ?, ?, ?, ?, NOW())
                     ON DUPLICATE KEY UPDATE am_absent = VALUES(am_absent), pm_absent = VALUES(pm_absent), encoded_by = VALUES(encoded_by), timestamp = NOW()"
                );
                $sf2_stmt->bind_param("ssiss", $lrn, $sf2_date, $sf2_am_absent, $sf2_pm_absent, $sf2_encoded_by);
                $sf2_stmt->execute();
                $sf2_stmt->close();
                // --------------------------------------------------------
            }
        }
        if (!$hasAbsent) {
            echo "<tr><td colspan='7'>🎉 All students were present or scanned at least once!</td></tr>";
        }
        $conn->close();
        ?>
    </table>
    <?php
    }
    ?>
</body>
</html>