<?php
include 'config.php';

$sql = "SELECT d.id, d.title, u.username, MAX(ds.status) as status
        FROM document_status ds
        JOIN documents d ON ds.document_id = d.id
        JOIN users u ON ds.employee_id = u.id
        GROUP BY ds.document_id, ds.employee_id";
$result = $conn->query($sql);

$output = "";
while ($row = $result->fetch_assoc()) {
    $statusBadge = "";
    switch ($row['status']) {
        case 'Pending':
            $statusBadge = '<span class="badge bg-warning text-dark">Pending</span>';
            break;
        case 'Approved':
            $statusBadge = '<span class="badge bg-success">Approved</span>';
            break;
        case 'Rejected':
            $statusBadge = '<span class="badge bg-danger">Rejected</span>';
            break;
        case 'Downloaded':
            $statusBadge = '<span class="badge bg-info text-dark">Downloaded</span>';
            break;
        default:
            $statusBadge = '<span class="badge bg-secondary">Unknown</span>';
            break;
    }

    $output .= "<tr>";
    $output .= "<td>" . htmlspecialchars($row['title'], ENT_QUOTES, 'UTF-8') . "</td>";
    $output .= "<td>" . htmlspecialchars($row['username'], ENT_QUOTES, 'UTF-8') . "</td>";
    $output .= "<td>" . $statusBadge . "</td>";
    $output .= "<td><a href='download_document.php?id=" . $row['id'] . "' class='btn btn-success btn-sm'>Download</a></td>";
    $output .= "</tr>";
}

echo $output;
?>
