<?php
// ====================================================================
// STAFF ATTENDANCE AUDIT LOG — Full trail of all scans
// ====================================================================

// ── Handle CSV Export ────────────────────────────────────────────────
if (isset($_GET['export']) && $_GET['export'] === 'csv') {
    $filter_date = $_GET['filter_date'] ?? '';
    $where = $filter_date ? "WHERE DATE(scan_at) = '" . mysqli_real_escape_string($connection, $filter_date) . "'" : '';

    $rows = mysqli_query($connection,
        "SELECT username, name, scanned_by, scan_at, ip_address,
                scan_lat, scan_lng, geo_distance_m, result, reason
         FROM staff_attendance_log {$where} ORDER BY scan_at DESC"
    );

    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="staff_attendance_log_' . date('Y-m-d') . '.csv"');
    $out = fopen('php://output', 'w');
    fputcsv($out, ['Username','Name','Scanned By','Time','IP Address','Latitude','Longitude','Distance(m)','Result','Reason']);
    while ($r = mysqli_fetch_assoc($rows)) {
        fputcsv($out, array_values($r));
    }
    fclose($out);
    exit;
}

// ── Ensure table exists ──────────────────────────────────────────────
mysqli_query($connection,
    "CREATE TABLE IF NOT EXISTS `staff_attendance_log` (
        `id`            INT AUTO_INCREMENT PRIMARY KEY,
        `username`      VARCHAR(100) NOT NULL,
        `name`          VARCHAR(200) NOT NULL,
        `scanned_by`    VARCHAR(100) NOT NULL DEFAULT 'system',
        `scan_at`       DATETIME NOT NULL,
        `ip_address`    VARCHAR(45) DEFAULT NULL,
        `scan_lat`      DECIMAL(10,7) DEFAULT NULL,
        `scan_lng`      DECIMAL(10,7) DEFAULT NULL,
        `geo_distance_m`INT DEFAULT NULL,
        `result`        ENUM('accepted','rejected','warned') DEFAULT 'accepted',
        `reason`        VARCHAR(255) DEFAULT NULL,
        INDEX idx_scan_at (scan_at),
        INDEX idx_username (username)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4"
);

// ── Filters ──────────────────────────────────────────────────────────
$filter_date   = $_GET['filter_date']   ?? date('Y-m-d');
$filter_result = $_GET['filter_result'] ?? '';
$filter_user   = $_GET['filter_user']   ?? '';

$where_parts = [];
if ($filter_date)   $where_parts[] = "DATE(scan_at) = '" . mysqli_real_escape_string($connection, $filter_date) . "'";
if ($filter_result) $where_parts[] = "result = '" . mysqli_real_escape_string($connection, $filter_result) . "'";
if ($filter_user)   $where_parts[] = "(username LIKE '%" . mysqli_real_escape_string($connection, $filter_user) . "%' OR name LIKE '%" . mysqli_real_escape_string($connection, $filter_user) . "%')";
$where_sql = $where_parts ? 'WHERE ' . implode(' AND ', $where_parts) : '';

$log_rows = mysqli_query($connection,
    "SELECT * FROM staff_attendance_log {$where_sql} ORDER BY scan_at DESC LIMIT 500"
);
$total = mysqli_num_rows($log_rows);

// ── Summary stats for today ──────────────────────────────────────────
$today_stats = mysqli_fetch_assoc(mysqli_query($connection,
    "SELECT
        COUNT(*) AS total,
        SUM(result='accepted') AS accepted,
        SUM(result='rejected') AS rejected,
        SUM(result='warned')   AS warned
     FROM staff_attendance_log WHERE DATE(scan_at) = CURDATE()"
)) ?: ['total'=>0,'accepted'=>0,'rejected'=>0,'warned'=>0];
?>

<style>
    .log-filter-bar { background:#fff; padding:18px 20px; border-radius:12px; margin-bottom:20px; box-shadow:0 2px 8px rgba(0,0,0,.07); }
    .stat-pill { display:inline-flex; align-items:center; gap:8px; padding:10px 18px; border-radius:30px; font-weight:700; font-size:14px; margin-right:10px; }
    .pill-total    { background:#ede9fe; color:#5b21b6; }
    .pill-accept   { background:#d1fae5; color:#065f46; }
    .pill-reject   { background:#fee2e2; color:#991b1b; }
    .pill-warn     { background:#fef3c7; color:#92400e; }
    .badge-accept  { background:#d1fae5; color:#065f46; border-radius:12px; padding:2px 10px; font-size:11px; font-weight:700; }
    .badge-reject  { background:#fee2e2; color:#991b1b; border-radius:12px; padding:2px 10px; font-size:11px; font-weight:700; }
    .badge-warn    { background:#fef3c7; color:#92400e; border-radius:12px; padding:2px 10px; font-size:11px; font-weight:700; }
    .geo-chip { display:inline-flex; align-items:center; gap:4px; font-size:11px; color:#374151; }
    .table thead th { background:#1e3a8a; color:#fff; font-size:12px; white-space:nowrap; }
    .table td { font-size:12px; vertical-align:middle; }
    .btn-export { background:#10b981; color:#fff; border:none; border-radius:8px; padding:9px 18px; font-weight:600; }
    .btn-export:hover { background:#059669; color:#fff; }
</style>

<div class="page-title">
  <div>
    <h1><i class="fa fa-shield-alt"></i> Staff Attendance Audit Log</h1>
    <p>Full trail of every scan — who, when, where, and whether it was accepted</p>
  </div>
</div>

<!-- ── TODAY STATS ── -->
<div class="mb-3">
    <span class="stat-pill pill-total"><i class="fa fa-list"></i> Today: <?= $today_stats['total'] ?> scans</span>
    <span class="stat-pill pill-accept"><i class="fa fa-check-circle"></i> Accepted: <?= $today_stats['accepted'] ?></span>
    <span class="stat-pill pill-warn"><i class="fa fa-exclamation-triangle"></i> Warned: <?= $today_stats['warned'] ?></span>
    <span class="stat-pill pill-reject"><i class="fa fa-times-circle"></i> Rejected: <?= $today_stats['rejected'] ?></span>
</div>

<!-- ── FILTERS ── -->
<div class="log-filter-bar">
    <form method="GET" action="" class="row g-3 align-items-end">
        <div class="col-md-3">
            <label class="form-label" style="font-size:12px;font-weight:600;">Date</label>
            <input type="date" name="filter_date" class="form-control" value="<?= htmlspecialchars($filter_date) ?>">
        </div>
        <div class="col-md-2">
            <label class="form-label" style="font-size:12px;font-weight:600;">Result</label>
            <select name="filter_result" class="form-control">
                <option value="">All</option>
                <option value="accepted" <?= $filter_result==='accepted'?'selected':'' ?>>Accepted</option>
                <option value="warned"   <?= $filter_result==='warned'  ?'selected':'' ?>>Warned</option>
                <option value="rejected" <?= $filter_result==='rejected'?'selected':'' ?>>Rejected</option>
            </select>
        </div>
        <div class="col-md-3">
            <label class="form-label" style="font-size:12px;font-weight:600;">Search Staff</label>
            <input type="text" name="filter_user" class="form-control" placeholder="Name or username" value="<?= htmlspecialchars($filter_user) ?>">
        </div>
        <div class="col-md-4 d-flex gap-2">
            <button type="submit" class="btn btn-primary btn-sm" style="border-radius:8px;padding:9px 18px;font-weight:600;">
                <i class="fa fa-filter"></i> Filter
            </button>
            <a href="staff_attendance_log" class="btn btn-secondary btn-sm" style="border-radius:8px;padding:9px 18px;font-weight:600;">
                <i class="fa fa-times"></i> Clear
            </a>
            <a href="?export=csv&filter_date=<?= urlencode($filter_date) ?>" class="btn-export btn btn-sm">
                <i class="fa fa-file-csv"></i> Export CSV
            </a>
        </div>
    </form>
</div>

<!-- ── LOG TABLE ── -->
<div class="card" style="padding:0;overflow:hidden;">
    <div class="table-responsive">
        <table class="table table-hover mb-0" id="auditTable">
            <thead>
                <tr>
                    <th>#</th>
                    <th>Staff</th>
                    <th>Scanned By</th>
                    <th>Scan Time</th>
                    <th>IP Address</th>
                    <th>GPS Location</th>
                    <th>Distance</th>
                    <th>Result</th>
                    <th>Reason</th>
                </tr>
            </thead>
            <tbody>
                <?php if ($total > 0):
                    $i = 0;
                    mysqli_data_seek($log_rows, 0);
                    while ($row = mysqli_fetch_assoc($log_rows)):
                        $i++;
                        $result_badge = match($row['result']) {
                            'accepted' => '<span class="badge-accept"><i class="fa fa-check-circle"></i> Accepted</span>',
                            'rejected' => '<span class="badge-reject"><i class="fa fa-times-circle"></i> Rejected</span>',
                            'warned'   => '<span class="badge-warn"><i class="fa fa-exclamation-triangle"></i> Warned</span>',
                            default    => $row['result']
                        };
                        $geo_chip = '';
                        if ($row['scan_lat'] && $row['scan_lng']) {
                            $maps_url = "https://www.google.com/maps?q={$row['scan_lat']},{$row['scan_lng']}";
                            $geo_chip = "<a href='{$maps_url}' target='_blank' class='geo-chip text-info'>
                                <i class='fa fa-map-marker-alt'></i> {$row['scan_lat']}, {$row['scan_lng']}
                            </a>";
                        } else {
                            $geo_chip = '<span class="text-muted" style="font-size:11px;">Not captured</span>';
                        }
                        $dist = $row['geo_distance_m'] !== null ? $row['geo_distance_m'] . 'm' : '—';
                    ?>
                    <tr>
                        <td><?= $i ?></td>
                        <td>
                            <b><?= htmlspecialchars($row['name']) ?></b><br>
                            <small class="text-muted"><?= htmlspecialchars($row['username']) ?></small>
                        </td>
                        <td><?= htmlspecialchars($row['scanned_by']) ?></td>
                        <td><?= date('d M Y H:i:s', strtotime($row['scan_at'])) ?></td>
                        <td><code style="font-size:11px;"><?= htmlspecialchars($row['ip_address'] ?? '—') ?></code></td>
                        <td><?= $geo_chip ?></td>
                        <td>
                            <?php if ($row['geo_distance_m'] !== null): ?>
                                <span style="color:<?= $row['geo_distance_m'] > 300 ? '#dc2626' : '#059669'; ?>;font-weight:700;">
                                    <?= $dist ?>
                                </span>
                            <?php else: ?>—<?php endif; ?>
                        </td>
                        <td><?= $result_badge ?></td>
                        <td style="max-width:200px;font-size:11px;"><?= htmlspecialchars($row['reason'] ?? '—') ?></td>
                    </tr>
                    <?php endwhile; ?>
                <?php else: ?>
                    <tr>
                        <td colspan="9" class="text-center text-muted py-4">
                            <i class="fa fa-inbox fa-2x mb-2 d-block"></i>
                            No scan records found for the selected filters.
                        </td>
                    </tr>
                <?php endif; ?>
            </tbody>
        </table>
    </div>
</div>
<small class="text-muted mt-2 d-block">Showing <?= $total ?> records (max 500 per page)</small>
