<?php
include('dbconnect.php');

$limit = 10;
$page = isset($_GET['page']) ? $_GET['page'] : 1;
$start_from = ($page - 1) * $limit;

$search_query = '';
if (isset($_GET['search'])) {
    $passport_no = $_GET['passport_no'];
    $search_query = " AND j.passport_no LIKE '%$passport_no%'";
}

$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
                  LIMIT $start_from, $limit";

$applicants_result = $con->query($applicants_sql);

$total_sql = "SELECT COUNT(*) FROM job_applicants j LEFT JOIN offer_letters ol ON j.id = ol.applicant_id 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 ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['applicant_id'])) {
    $applicant_id = $_POST['applicant_id'];
    $offer_status = $_POST['offer_status'];

    $offer_letter_file = NULL;
    if (isset($_FILES['offer_letter_file']) && $_FILES['offer_letter_file']['error'] == 0) {
        $upload_dir = 'uploads/offer_letters/';
        $file_name = basename($_FILES['offer_letter_file']['name']);
        $file_path = $upload_dir . $file_name;

        if (!file_exists($upload_dir)) {
            mkdir($upload_dir, 0777, true);
        }

        if (move_uploaded_file($_FILES['offer_letter_file']['tmp_name'], $file_path)) {
            $offer_letter_file = $file_path;
        }
    }

    $sql = "INSERT INTO offer_letters (applicant_id, offer_status, offer_letter_file)
            VALUES (?, ?, ?)
            ON DUPLICATE KEY UPDATE offer_status = ?, offer_letter_file = ?";

    if ($stmt = $con->prepare($sql)) {
        $stmt->bind_param("issss", $applicant_id, $offer_status, $offer_letter_file, $offer_status, $offer_letter_file);
        if ($stmt->execute()) {
            echo "<script>alert('Offer letter status updated successfully.');</script>";
        } else {
            echo "<script>alert('Error: " . $stmt->error . "');</script>";
        }
        $stmt->close();
    } else {
        echo "<script>alert('Error preparing query: " . $con->error . "');</script>";
    }
}

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 $search_query";
    $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.0">
    <style>
        body {
            margin: 0;
            font-family: 'Segoe UI', sans-serif;
            background-color: #eef1f5;
        }

        header {
            background-color: #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-color: #e74c3c;
            color: white;
            padding: 8px 14px;
            border: none;
            border-radius: 6px;
            font-size: 14px;
            cursor: pointer;
        }

        .logout-btn:hover {
            background-color: #c0392b;
        }

        .container {
            width: 95%;
            max-width: 1100px;
            margin: 30px auto;
            background-color: #fff;
            padding: 25px;
            border-radius: 10px;
            box-shadow: 0 4px 16px rgba(0, 0, 0, 0.1);
        }

        h2, h3 {
            text-align: center;
            margin-bottom: 20px;
        }

        .search-container,
        .form-container {
            margin: 20px 0;
            padding: 15px;
            background-color: #f7f7f7;
            border-radius: 8px;
        }

        input[type="text"],
        input[type="file"],
        select {
            width: 100%;
            padding: 10px;
            margin: 10px 0 15px;
            border: 1px solid #ccc;
            border-radius: 4px;
        }

        input[type="submit"], .edit-btn {
            padding: 10px 15px;
            background-color: #007bff;
            color: white;
            border: none;
            border-radius: 5px;
            cursor: pointer;
            font-weight: bold;
        }

        input[type="submit"]:hover, .edit-btn:hover {
            background-color: #0056b3;
        }

        table {
            width: 100%;
            border-collapse: collapse;
            margin-top: 15px;
        }

        table th, table td {
            padding: 12px;
            text-align: left;
            border-bottom: 1px solid #ddd;
        }

        table th {
            background-color: #007bff;
            color: white;
        }

        table tr:nth-child(even) {
            background-color: #f9f9f9;
        }

        .status-received { color: green; font-weight: bold; }
        .status-rejected { color: red; font-weight: bold; }

        .pagination {
            text-align: center;
            margin-top: 25px;
        }

        .pagination a {
            display: inline-block;
            padding: 6px 12px;
            margin: 0 4px;
            background-color: #007bff;
            color: white;
            text-decoration: none;
            border-radius: 4px;
        }

        .pagination a:hover {
            background-color: #0056b3;
        }

        .no-records {
            text-align: center;
            color: #555;
            font-style: italic;
        }

        @media screen and (max-width: 768px) {
            table, thead, tbody, th, td, tr {
                display: block;
            }

            table thead {
                display: none;
            }

            table td {
                position: relative;
                padding-left: 50%;
                border: none;
                border-bottom: 1px solid #eee;
            }

            table td::before {
                position: absolute;
                top: 12px;
                left: 12px;
                width: 45%;
                padding-right: 10px;
                font-weight: bold;
                white-space: nowrap;
            }

            table td:nth-of-type(1)::before { content: "Name"; }
            table td:nth-of-type(2)::before { content: "Passport No"; }
            table td:nth-of-type(3)::before { content: "Position"; }
            table td:nth-of-type(4)::before { content: "Job Country"; }
            table td:nth-of-type(5)::before { content: "Reference"; }
            table td:nth-of-type(6)::before { content: "Offer Status"; }
            table td:nth-of-type(7)::before { content: "Offer Letter File"; }
            table td:nth-of-type(8)::before { content: "Actions"; }
        }
    </style>
</head>
<body>

<header>
    <h2>Offer Letter Management</h2>
    <form method="POST" action="logout.php">
        <button class="logout-btn">Logout</button>
    </form>
</header>

<div class="container">
    <div class="search-container">
        <form method="GET" action="">
            <label for="passport_no">Search by Passport No:</label>
            <input type="text" name="passport_no" placeholder="Enter passport number" value="<?php echo isset($_GET['passport_no']) ? $_GET['passport_no'] : ''; ?>">
            <input type="submit" name="search" value="Search">
        </form>
    </div>

    <form method="POST" action="">
        <input type="submit" name="export" value="Export Data as CSV">
    </form>

    <table>
        <thead>
            <tr>
                <th>Name</th>
                <th>Passport No</th>
                <th>Position</th>
                <th>Job Country</th>
                <th>Reference</th>
                <th>Offer Status</th>
                <th>Offer Letter</th>
                <th>Actions</th>
            </tr>
        </thead>
        <tbody>
            <?php if ($applicants_result->num_rows > 0): ?>
                <?php while ($row = $applicants_result->fetch_assoc()): ?>
                <tr>
                    <td><?= $row['name']; ?></td>
                    <td><?= $row['passport_no']; ?></td>
                    <td><?= $row['position']; ?></td>
                    <td><?= $row['job_country']; ?></td>
                    <td><?= $row['reference']; ?></td>
                    <td class="<?= $row['offer_status'] == 'Received' ? 'status-received' : ($row['offer_status'] == 'Rejected' ? 'status-rejected' : ''); ?>">
                        <?= $row['offer_status'] ?: 'Pending'; ?>
                    </td>
                    <td>
                        <?= $row['offer_letter_file'] ? "<a href='{$row['offer_letter_file']}' target='_blank'>Download</a>" : 'Not Uploaded'; ?>
                    </td>
                    <td>
                        <a href="?edit=<?= $row['id']; ?>"><button class="edit-btn">Edit</button></a>
                    </td>
                </tr>
                <?php endwhile; ?>
            <?php else: ?>
                <tr><td colspan="8" class="no-records">No records found</td></tr>
            <?php endif; ?>
        </tbody>
    </table>

    <div class="pagination">
        <?php for ($i = 1; $i <= $total_pages; $i++): ?>
            <a href="?page=<?= $i; ?>"><?= $i; ?></a>
        <?php endfor; ?>
    </div>

    <?php
    if (isset($_GET['edit'])) {
        $edit_id = $_GET['edit'];
        $applicant_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 j.id = $edit_id";
        $applicant_result = $con->query($applicant_sql);
        $applicant = $applicant_result->fetch_assoc();
    ?>
    <div class="form-container">
        <h3>Edit Offer Letter Status</h3>
        <form method="POST" action="" enctype="multipart/form-data">
            <input type="hidden" name="applicant_id" value="<?= $applicant['id']; ?>">

            <label for="offer_status">Offer Status:</label>
            <select name="offer_status" required>
                <option value="Received" <?= $applicant['offer_status'] == 'Received' ? 'selected' : ''; ?>>Received</option>
                <option value="Rejected" <?= $applicant['offer_status'] == 'Rejected' ? 'selected' : ''; ?>>Rejected</option>
                <option value="Pending" <?= $applicant['offer_status'] == 'Pending' ? 'selected' : ''; ?>>Pending</option>
            </select>

            <label for="offer_letter_file">Upload New Offer Letter (optional):</label>
            <input type="file" name="offer_letter_file" accept="application/pdf">

            <input type="submit" value="Update Status">
        </form>
    </div>
    <?php } ?>
</div>

</body>
</html>
