<?php
require_once("public/connection.php");
require_once("public/functions.php");

session_start();

/* ============================
   ACCESS CONTROL
============================ */
if (!isset($_SESSION['staff'])) {
    $_SESSION['msg'] = "Please login to view staff attendance history.";
    $_SESSION['type'] = "danger";
    header("Location: login.php");
    exit;
}

/* ============================
   FETCH CURRENT TERM & SESSION
============================ */
$cs = $connection->query("SELECT current_term, current_session FROM current_season LIMIT 1");
$current_season = $cs ? $cs->fetch_assoc() : [];

$term_default    = $current_season['current_term']   ?? 'First Term';
$session_default = $current_season['current_session'] ?? date("Y") . "/" . (date("Y") + 1);

/* ============================
   FILTER VALUES
============================ */
$from_date      = $_POST['from_date'] ?? '';
$to_date        = $_POST['to_date'] ?? '';
$term_filter    = $_POST['filter_term'] ?? $term_default;
$session_filter = $_POST['filter_session'] ?? $session_default;

/* ============================
   BUILD QUERY
============================ */
$sql = "SELECT 
            sa.*, 
            s.username, 
            s.name 
        FROM staff_attendance sa
        LEFT JOIN staff s ON sa.staff_id = s.id
        WHERE sa.term = ? AND sa.session = ?";

$params = [$term_filter, $session_filter];
$types  = "ss";

if (!empty($from_date) && !empty($to_date)) {
    $sql .= " AND sa.date BETWEEN ? AND ?";
    $params[] = $from_date;
    $params[] = $to_date;
    $types   .= "ss";
}

$sql .= " ORDER BY sa.date DESC, s.name ASC";

$stmt = $connection->prepare($sql);
$stmt->bind_param($types, ...$params);
$stmt->execute();
$result = $stmt->get_result();
?>

<!-- ============================
     PAGE TITLE
============================= -->
<div class="page-title">
    <div>
        <h1><i class="fa fa-users"></i> Staff Attendance History</h1>
        <p>View and filter staff attendance records</p>
    </div>
</div>

<div class="card">

    <!-- ============================
         FILTER PANEL
    ============================= -->
    <div class="panel panel-primary">
        <div class="panel-heading">
            <h4 class="text-center">Filter Attendance</h4>
        </div>
        <div class="panel-body">
            <form method="POST" class="row g-3">

                <div class="col-md-3">
                    <label>From Date</label>
                    <input type="date" name="from_date" class="form-control"
                           value="<?= htmlspecialchars($from_date ?? '') ?>">
                </div>

                <div class="col-md-3">
                    <label>To Date</label>
                    <input type="date" name="to_date" class="form-control"
                           value="<?= htmlspecialchars($to_date ?? '') ?>">
                </div>

                <div class="col-md-3">
                    <label>Term</label>
                    <select name="filter_term" class="form-control">
                        <?php
                        $terms = ["First Term", "Second Term", "Third Term"];
                        foreach ($terms as $t) {
                            $selected = ($t === $term_filter) ? "selected" : "";
                            echo "<option $selected>$t</option>";
                        }
                        ?>
                    </select>
                </div>

                <div class="col-md-3">
                    <label>Session</label>
                    <select name="filter_session" class="form-control">
                        <?php
                        $sessions = [$session_default, date("Y") . "/" . (date("Y") + 1)];
                        foreach ($sessions as $s) {
                            $selected = ($s === $session_filter) ? "selected" : "";
                            echo "<option $selected>$s</option>";
                        }
                        ?>
                    </select>
                </div>

                <div class="col-md-12 mt-3">
                    <button type="submit" class="btn btn-primary btn-sm w-100">
                        Filter Records
                    </button>
                </div>

            </form>
        </div>
    </div>

    <!-- ============================
         TABLE PANEL
    ============================= -->
    <div class="panel panel-primary mt-3">
        <div class="panel-heading">
            <h4 class="text-center">Staff Attendance Records</h4>
        </div>
        <div class="panel-body">

            <div class="table-responsive">
                <table class="table table-bordered table-hover">
                    <thead class="table-primary">
                        <tr>
                            <th>#</th>
                            <th>Staff Name</th>
                            <th>Username</th>
                            <th>Date</th>
                            <th>Morning Roll</th>
                            <th>Time (AM)</th>
                            <th>Afternoon Roll</th>
                            <th>Time (PM)</th>
                            <th>Marked By</th>
                        </tr>
                    </thead>
                    <tbody>

                    <?php
                    if ($result->num_rows > 0) {
                        $i = 1;
                        while ($row = $result->fetch_assoc()) {

                            /* ============================
                               FETCH MARKER NAME
                            ============================= */
                            $marker_name = '-';
                            if (!empty($row['marked_by'])) {
                                $stmt2 = $connection->prepare(
                                    "SELECT name FROM staff WHERE id = ? LIMIT 1"
                                );
                                $stmt2->bind_param("i", $row['marked_by']);
                                $stmt2->execute();
                                $mr = $stmt2->get_result();
                                if ($mr->num_rows > 0) {
                                    $marker_name = $mr->fetch_assoc()['name'] ?? '-';
                                }
                            }

                            echo "<tr>
                                <td>{$i}</td>
                                <td>" . htmlspecialchars($row['name'] ?? '-') . "</td>
                                <td>" . htmlspecialchars($row['username'] ?? '-') . "</td>
                                <td>" . htmlspecialchars($row['date'] ?? '-') . "</td>
                                <td>" . ($row['morning_roll'] ?? '-') . "</td>
                                <td>" . ($row['time_morning'] ?? '-') . "</td>
                                <td>" . ($row['afternoon_roll'] ?? '-') . "</td>
                                <td>" . ($row['time_afternoon'] ?? '-') . "</td>
                                <td>" . htmlspecialchars($marker_name) . "</td>
                            </tr>";
                            $i++;
                        }
                    } else {
                        echo "<tr>
                            <td colspan='9' class='text-center text-danger'>
                                No attendance records found.
                            </td>
                        </tr>";
                    }
                    ?>

                    </tbody>
                </table>
            </div>

        </div>
    </div>

</div>
