<?php
include('dbconnect.php');

$limit = 10;
$page = isset($_GET['page']) ? max(1, intval($_GET['page'])) : 1;
$start_from = ($page - 1) * $limit;

$search_query = '';
$passport_no = '';
if (isset($_GET['search'])) {
    $passport_no = $con->real_escape_string($_GET['passport_no']);
    $search_query = " AND j.passport_no LIKE '%$passport_no%'";
}

// Handle Delete request via GET param ?delete=applicant_id
if (isset($_GET['delete'])) {
    $delete_id = intval($_GET['delete']);

    // First, get offer_letter_file to delete the file physically
    $stmt = $con->prepare("SELECT offer_letter_file FROM offer_letters WHERE applicant_id = ?");
    $stmt->bind_param("i", $delete_id);
    $stmt->execute();
    $res = $stmt->get_result();
    if ($row = $res->fetch_assoc()) {
        if (!empty($row['offer_letter_file']) && file_exists($row['offer_letter_file'])) {
            unlink($row['offer_letter_file']);
        }
    }
    $stmt->close();

    // Delete the record
    $stmt = $con->prepare("DELETE FROM offer_letters WHERE applicant_id = ?");
    $stmt->bind_param("i", $delete_id);
    $stmt->execute();
    $stmt->close();

    header("Location: offer_letters.php?deleted=1");
    exit;
}

$applicants_sql = "SELECT j.id, j.name, j.passport_no, j.position, j.job_country, j.reference, ol.offer_status, ol.offer_letter_file 
                  FROM job_applicants j 
                  LEFT JOIN offer_letters ol ON j.id = ol.applicant_id
                  WHERE 1=1 $search_query
                  ORDER BY j.name ASC
                  LIMIT $start_from, $limit";

$applicants_result = $con->query($applicants_sql);

$total_sql = "SELECT COUNT(*) FROM job_applicants j WHERE 1=1 $search_query";
$total_result = $con->query($total_sql);
$total_rows = $total_result->fetch_row()[0];
$total_pages = ceil($total_rows / $limit);

if (isset($_POST['export'])) {
    $export_sql = "SELECT j.name, j.passport_no, j.position, j.job_country, j.reference, ol.offer_status, ol.offer_letter_file
                   FROM job_applicants j 
                   LEFT JOIN offer_letters ol ON j.id = ol.applicant_id 
                   WHERE 1=1 $search_query
                   ORDER BY j.name ASC";
    $export_result = $con->query($export_sql);

    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="offer_letters.csv"');
    $output = fopen('php://output', 'w');
    fputcsv($output, ['Name', 'Passport No', 'Position', 'Job Country', 'Reference', 'Offer Status', 'Offer Letter File']);
    while ($row = $export_result->fetch_assoc()) {
        fputcsv($output, $row);
    }
    fclose($output);
    exit;
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<title>Offer Letter Management</title>
<meta name="viewport" content="width=device-width, initial-scale=1" />
<!-- Font Awesome CDN for icons -->
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css" />
<style>
/* Your existing styles plus responsive improvements */
body {
    margin: 0;
    font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
    background: #eef1f5;
}

header {
    background: #2c3e50;
    color: white;
    padding: 15px 20px;
    display: flex;
    justify-content: space-between;
    align-items: center;
    flex-wrap: wrap;
}

header h2 {
    margin: 0;
    font-size: 22px;
}

.logout-btn {
    background: #e74c3c;
    border: none;
    color: white;
    padding: 8px 14px;
    border-radius: 6px;
    font-size: 14px;
    cursor: pointer;
}
.logout-btn:hover { background: #c0392b; }

.container {
    max-width: 1100px;
    margin: 30px auto;
    background: #fff;
    padding: 25px;
    border-radius: 10px;
    box-shadow: 0 4px 16px rgb(0 0 0 / 0.1);
}

.search-container,
.export-container {
    margin-bottom: 20px;
}

input[type=text], select {
    width: 100%;
    max-width: 300px;
    padding: 10px;
    margin: 5px 10px 10px 0;
    border: 1px solid #ccc;
    border-radius: 4px;
    font-size: 16px;
}

input[type=submit], button.edit-btn, button.delete-btn {
    padding: 8px 12px;
    border: none;
    border-radius: 5px;
    font-weight: 600;
    cursor: pointer;
    font-size: 14px;
}

input[type=submit] {
    background: #007bff;
    color: #fff;
}
input[type=submit]:hover { background: #0056b3; }

button.edit-btn {
    background: #17a2b8;
    color: white;
    margin-right: 5px;
}
button.edit-btn:hover {
    background: #117a8b;
}

button.delete-btn {
    background: #dc3545;
    color: white;
}
button.delete-btn:hover {
    background: #a71d2a;
}

table {
    width: 100%;
    border-collapse: collapse;
    margin-top: 10px;
}

th, td {
    padding: 12px 8px;
    border-bottom: 1px solid #ddd;
    text-align: left;
    vertical-align: middle;
}

th {
    background: #007bff;
    color: white;
    font-weight: 600;
}

.status-received { color: green; font-weight: bold; }
.status-rejected { color: red; font-weight: bold; }
.status-pending { color: gray; font-weight: bold; }

.pagination {
    margin-top: 25px;
    text-align: center;
}
.pagination a {
    display: inline-block;
    padding: 6px 12px;
    margin: 0 4px;
    background: #007bff;
    color: white;
    border-radius: 4px;
    text-decoration: none;
}
.pagination a:hover {
    background: #0056b3;
}

/* Responsive Table */
@media (max-width: 768px) {
    table, thead, tbody, th, td, tr {
        display: block;
    }
    thead tr { display: none; }
    tr {
        margin-bottom: 15px;
        border: 1px solid #ccc;
        border-radius: 6px;
        padding: 10px;
        background: #fefefe;
    }
    td {
        border: none;
        padding-left: 50%;
        position: relative;
        text-align: left;
        white-space: normal;
    }
    td::before {
        position: absolute;
        left: 15px;
        top: 12px;
        font-weight: bold;
        white-space: nowrap;
        color: #333;
    }
    td:nth-of-type(1)::before { content: "Name"; }
    td:nth-of-type(2)::before { content: "Passport No"; }
    td:nth-of-type(3)::before { content: "Position"; }
    td:nth-of-type(4)::before { content: "Job Country"; }
    td:nth-of-type(5)::before { content: "Reference"; }
    td:nth-of-type(6)::before { content: "Offer Status"; }
    td:nth-of-type(7)::before { content: "Offer Letter"; }
    td:nth-of-type(8)::before { content: "Actions"; }
}
</style>
</head>
<body>

<header>
    <h2><i class="fa-solid fa-file-contract"></i> Offer Letter Management</h2>
    <form method="POST" action="logout.php" aria-label="Logout Form">
        <button class="logout-btn" type="submit"><i class="fa-solid fa-right-from-bracket"></i> Logout</button>
    </form>
</header>

<div class="container" role="main">

    <div class="search-container" role="search">
        <form method="GET" action="" aria-label="Search Applicants by Passport Number">
            <input type="text" name="passport_no" placeholder="Enter passport number" value="<?= htmlspecialchars($passport_no); ?>" aria-label="Passport Number">
            <input type="submit" name="search" value="Search">
        </form>
    </div>

    <div class="export-container">
        <form method="POST" action="">
            <input type="hidden" name="passport_no" value="<?= htmlspecialchars($passport_no); ?>">
            <input type="submit" name="export" value="Export Data as CSV" aria-label="Export Data as CSV">
        </form>
    </div>

    <table role="table" aria-label="Applicants and Offer Letter Status">
        <thead>
            <tr>
                <th scope="col">Name</th>
                <th scope="col">Passport No</th>
                <th scope="col">Position</th>
                <th scope="col">Job Country</th>
                <th scope="col">Reference</th>
                <th scope="col">Offer Status</th>
                <th scope="col">Offer Letter</th>
                <th scope="col">Actions</th>
            </tr>
        </thead>
        <tbody>
        <?php if ($applicants_result->num_rows > 0): ?>
            <?php while ($row = $applicants_result->fetch_assoc()): ?>
            <tr>
                <td><?= htmlspecialchars($row['name']); ?></td>
                <td><?= htmlspecialchars($row['passport_no']); ?></td>
                <td><?= htmlspecialchars($row['position']); ?></td>
                <td><?= htmlspecialchars($row['job_country']); ?></td>
                <td><?= htmlspecialchars($row['reference']); ?></td>
                <td class="<?=
                    strtolower($row['offer_status']) === 'received' ? 'status-received' :
                    (strtolower($row['offer_status']) === 'rejected' ? 'status-rejected' : 'status-pending');
                ?>">
                    <?= $row['offer_status'] ?: 'Pending'; ?>
                </td>
                <td>
                    <?php if ($row['offer_letter_file'] && file_exists($row['offer_letter_file'])): ?>
                        <a href="<?= htmlspecialchars($row['offer_letter_file']); ?>" target="_blank" rel="noopener noreferrer"><i class="fa-solid fa-file-pdf"></i> Download</a>
                    <?php else: ?>
                        Not Uploaded
                    <?php endif; ?>
                </td>
                <td>
                    <a href="offerlateredit.php?id=<?= $row['id']; ?>">
                        <button class="edit-btn" title="Edit Offer Letter"><i class="fa-solid fa-pen-to-square"></i> Edit</button>
                    </a>
                    <form method="GET" action="" onsubmit="return confirm('Are you sure you want to delete the offer letter for this applicant?');" style="display:inline;">
                        <input type="hidden" name="delete" value="<?= $row['id']; ?>">
                        <button type="submit" class="delete-btn" title="Delete Offer Letter"><i class="fa-solid fa-trash"></i> Delete</button>
                    </form>
                </td>
            </tr>
            <?php endwhile; ?>
        <?php else: ?>
            <tr><td colspan="8" style="text-align:center; font-style: italic;">No records found</td></tr>
        <?php endif; ?>
        </tbody>
    </table>

    <nav class="pagination" aria-label="Pagination Navigation">
        <?php for ($i = 1; $i <= $total_pages; $i++): ?>
            <a href="?page=<?= $i; ?>&passport_no=<?= urlencode($passport_no); ?>"><?= $i; ?></a>
        <?php endfor; ?>
    </nav>

</div>

</body>
</html>
