<?php
include "dbconnect.php";  // Include your database connection file

// Pagination settings
$limit = 10;
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$offset = ($page - 1) * $limit;

// Search and Date Filter
$searchQuery = "";
$search = "";
$from_date = "";
$to_date = "";

if (!empty($_GET['search'])) {
    $search = $con->real_escape_string($_GET['search']);
    $searchQuery .= " AND (ja.id LIKE '%$search%' OR mr.medical_date LIKE '%$search%')";
}

if (!empty($_GET['from_date']) && !empty($_GET['to_date'])) {
    $from_date = $_GET['from_date'];
    $to_date = $_GET['to_date'];
    $searchQuery .= " AND mr.medical_date BETWEEN '$from_date' AND '$to_date'";
}

// Get paginated data
$sql = "SELECT ja.*, mr.medical_date, mr.medical_exp_date, mr.medical_upload, mr.sleep
        FROM job_applicants ja
        LEFT JOIN medical_records mr ON ja.id = mr.applicant_id
        WHERE 1 $searchQuery
        LIMIT $limit OFFSET $offset";
$result = $con->query($sql);

// Count total rows
$countQuery = "SELECT COUNT(*) AS total FROM job_applicants ja
               LEFT JOIN medical_records mr ON ja.id = mr.applicant_id
               WHERE 1 $searchQuery";
$countResult = $con->query($countQuery);
$totalRows = $countResult->fetch_assoc()['total'];
$totalPages = ceil($totalRows / $limit);
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Medical Records List</title>
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <style>
        body {
            background: linear-gradient(to right, #ff9966, #ff5e62);
            font-family: 'Segoe UI', sans-serif;
        }

        .container {
            margin-top: 40px;
            margin-bottom: 60px;
            background: white;
            padding: 30px;
            border-radius: 12px;
            box-shadow: 0 10px 25px rgba(0, 0, 0, 0.1);
        }

        .table th {
            background-color: #007bff;
            color: white;
        }

        .logout-btn {
            position: fixed;
            top: 20px;
            right: 20px;
            background-color: #dc3545;
            color: white;
            padding: 10px 20px;
            border-radius: 25px;
            text-decoration: none;
        }

        .logout-btn:hover {
            background-color: #c82333;
        }

        @media (max-width: 768px) {
            .logout-btn {
                top: 10px;
                right: 10px;
                font-size: 12px;
                padding: 8px 14px;
            }

            .container {
                padding: 20px 15px;
            }
        }
    </style>
</head>
<body>

<a href="logout.php" class="logout-btn">Logout</a>

<div class="container">
    <h3 class="text-center text-primary">Medical Records List</h3>

    <!-- Search & Date Filter Form -->
    <form method="GET" class="row g-2 mb-4 justify-content-center">
        <div class="col-md-3 col-sm-6">
            <input type="text" name="search" class="form-control" placeholder="Search by ID or Date" value="<?= htmlspecialchars($search) ?>">
        </div>
        <div class="col-md-2 col-sm-6">
            <input type="date" name="from_date" class="form-control" value="<?= htmlspecialchars($from_date) ?>">
        </div>
        <div class="col-md-2 col-sm-6">
            <input type="date" name="to_date" class="form-control" value="<?= htmlspecialchars($to_date) ?>">
        </div>
        <div class="col-md-2 col-sm-6">
            <button type="submit" class="btn btn-success w-100">Filter</button>
        </div>
    </form>

    <!-- Table -->
    <div class="table-responsive">
        <table class="table table-bordered text-center align-middle">
            <thead>
                <tr>
                    <th>#</th>
                    <th>Applicant ID</th>
                    <th>Medical Date</th>
                    <th>Medical Expiry</th>
                    <th>Document</th>
                    <th>Sleep Details</th>
                </tr>
            </thead>
            <tbody>
                <?php 
                $sr = $offset + 1;
                if ($result->num_rows > 0) {
                    while ($row = $result->fetch_assoc()) {
                        $applicant_id = $row['id'];
                        $medical_date = $row['medical_date'] ?? 'N/A';
                        $medical_exp_date = $row['medical_exp_date'] ?? 'N/A';
                        $medical_upload = trim($row['medical_upload'] ?? '');
                        $sleep = !empty($row['sleep']) ? htmlspecialchars($row['sleep']) : 'No';

                        $uploadPath = (strpos($medical_upload, 'uploads/') === false && !empty($medical_upload)) 
                            ? 'uploads/' . $medical_upload 
                            : $medical_upload;
                        ?>
                        <tr>
                            <td><?= $sr++ ?></td>
                            <td><?= htmlspecialchars($applicant_id) ?></td>
                            <td><?= htmlspecialchars($medical_date) ?></td>
                            <td><?= htmlspecialchars($medical_exp_date) ?></td>
                            <td>
                                <?php if (!empty($medical_upload) && file_exists($uploadPath)) { ?>
                                    <a href="<?= htmlspecialchars($uploadPath) ?>" class="btn btn-sm btn-success" download>Download</a>
                                <?php } else { ?>
                                    <span class="text-danger">Not Available</span>
                                <?php } ?>
                            </td>
                            <td><?= nl2br($sleep) ?></td>
                        </tr>
                    <?php }
                } else {
                    echo '<tr><td colspan="6">No records found.</td></tr>';
                } ?>
            </tbody>
        </table>
    </div>

    <!-- Pagination -->
    <nav>
        <ul class="pagination justify-content-center">
            <?php
            $searchParam = !empty($search) ? "&search=" . urlencode($search) : '';
            $dateParams = (!empty($from_date) && !empty($to_date)) ? "&from_date=$from_date&to_date=$to_date" : '';
            for ($i = 1; $i <= $totalPages; $i++) {
                $active = ($i == $page) ? 'active' : '';
                echo "<li class='page-item $active'><a class='page-link' href='?page=$i$searchParam$dateParams'>$i</a></li>";
            }
            ?>
        </ul>
    </nav>
</div>

</body>
</html>
