<?php
include "dbconnect.php";

// Pagination settings
$limit = 10;
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$offset = ($page - 1) * $limit;

// Search & Date Filtering
$searchQuery = "";
$dateFilter = "";

if (!empty($_GET['search'])) {
    $search = $con->real_escape_string($_GET['search']);
    $searchQuery = "AND (ja.passport_no LIKE '%$search%' OR ja.mobile_no LIKE '%$search%')";
}

if (!empty($_GET['start_date']) && !empty($_GET['end_date'])) {
    $start_date = $con->real_escape_string($_GET['start_date']);
    $end_date = $con->real_escape_string($_GET['end_date']);
    $dateFilter = "AND wa.approval_date BETWEEN '$start_date' AND '$end_date'";
}

// Load Data with Approval Status
$sql = "SELECT ja.id, ja.name, ja.passport_no, ja.mobile_no, ja.position, ja.job_country, ja.reference, 
               wa.work_permit_received, wa.file_upload, wa.approval_date, 
               DATEDIFF(wa.approval_date, CURDATE()) AS days_left 
        FROM job_applicants ja
        LEFT JOIN work_permit_approval wa ON ja.id = wa.applicant_id
        WHERE 1 $searchQuery $dateFilter 
        ORDER BY wa.approval_date ASC 
        LIMIT $limit OFFSET $offset";

$result = $con->query($sql);

// Fetch Expiring Soon for Marquee
$marqueeQuery = "SELECT ja.name, ja.passport_no, wa.approval_date FROM job_applicants ja
                 LEFT JOIN work_permit_approval wa ON ja.id = wa.applicant_id
                 WHERE DATEDIFF(wa.approval_date, CURDATE()) BETWEEN 1 AND 15 
                 ORDER BY wa.approval_date ASC";
$marqueeResult = $con->query($marqueeQuery);
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Work Permit Approval List</title>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css">
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
  <style>
    body {
        background: linear-gradient(135deg, #667eea, #764ba2);
        font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
        color: #333;
        min-height: 100vh;
        margin: 0;
        padding: 20px 0;
    }

    .container {
        max-width: 1200px;
        margin: auto;
        background-color: #fff;
        padding: 25px 30px;
        border-radius: 12px;
        box-shadow: 0 10px 30px rgba(0, 0, 0, 0.1);
    }

    h3.text-primary {
        color: #4e73df;
        font-weight: 700;
        margin-bottom: 25px;
        text-align: center;
        font-size: 28px;
    }

    marquee {
        font-weight: 700;
        color: #dc3545;
        font-size: 16px;
        margin-bottom: 25px;
    }

    form.row.g-2.mb-3 {
        gap: 15px;
        margin-bottom: 30px;
    }

    form input[type="text"],
    form input[type="date"] {
        border: 2px solid #4e73df;
        border-radius: 8px;
        padding: 10px 15px;
        font-size: 15px;
        transition: border-color 0.3s ease;
    }

    form input[type="text"]:focus,
    form input[type="date"]:focus {
        border-color: #1cc88a;
        outline: none;
        box-shadow: 0 0 8px rgba(28, 200, 138, 0.3);
    }

    form button.btn-success {
        background-color: #1cc88a;
        border: none;
        font-weight: 600;
        border-radius: 8px;
        padding: 10px 0;
        font-size: 16px;
        transition: background-color 0.3s ease;
    }

    form button.btn-success:hover {
        background-color: #17a673;
    }

    .table-container {
        overflow-x: auto;
    }

    table.table {
        width: 100%;
        border-collapse: separate;
        border-spacing: 0;
        font-size: 14px;
        min-width: 900px;
    }

    table.table th,
    table.table td {
        padding: 14px 18px;
        text-align: center;
        vertical-align: middle;
        border-bottom: 1px solid #e3e6f0;
    }

    table.table thead th {
        background-color: #4e73df;
        color: #fff;
        font-weight: 700;
        position: sticky;
        top: 0;
        z-index: 1;
    }

    /* Row status colors */
    .expired {
        background-color: #f8d7da !important;
        color: #721c24 !important;
    }

    .expiring-soon {
        background-color: #fff3cd !important;
        color: #856404 !important;
    }

    .safe {
        background-color: #d1ecf1 !important;
        color: #0c5460 !important;
    }

    /* Buttons */
    .btn-info {
        background-color: #36b9cc;
        border: none;
        border-radius: 6px;
        padding: 6px 12px;
        font-size: 13px;
        transition: background-color 0.3s ease;
    }

    .btn-info:hover {
        background-color: #2c9faf;
    }

    .btn-warning {
        background-color: #f6c23e;
        border: none;
        border-radius: 6px;
        padding: 6px 12px;
        font-size: 13px;
        transition: background-color 0.3s ease;
        color: #212529;
    }

    .btn-warning:hover {
        background-color: #d4a429;
        color: #fff;
    }

    .btn-primary {
        background-color: #4e73df;
        border: none;
        border-radius: 6px;
        padding: 8px 16px;
        font-weight: 600;
        font-size: 15px;
        transition: background-color 0.3s ease;
        color: #fff;
    }

    .btn-primary:hover {
        background-color: #2e59d9;
    }

    .btn-secondary {
        background-color: #858796;
        border: none;
        border-radius: 6px;
        padding: 8px 16px;
        font-size: 15px;
        color: #fff;
        transition: background-color 0.3s ease;
    }

    .btn-secondary:hover {
        background-color: #6c757d;
    }

    /* Pagination */
    .pagination {
        justify-content: center;
        margin-top: 20px;
        gap: 8px;
    }

    .pagination .page-item.active .page-link {
        background-color: #4e73df;
        border-color: #4e73df;
        color: #fff;
        font-weight: 700;
    }

    .pagination .page-link {
        color: #4e73df;
        border-radius: 6px;
        padding: 8px 14px;
        border: 1px solid #dee2e6;
        transition: background-color 0.3s ease, color 0.3s ease;
    }

    .pagination .page-link:hover {
        background-color: #4e73df;
        color: #fff;
        border-color: #4e73df;
    }

    /* Responsive adjustments */
    @media (max-width: 1200px) {
        .container {
            padding: 20px 20px;
        }

        table.table {
            min-width: 700px;
        }
    }

    @media (max-width: 768px) {
        h3.text-primary {
            font-size: 24px;
        }

        .table-container {
            overflow-x: scroll;
        }

        table.table {
            min-width: 600px;
            font-size: 13px;
        }

        form.row.g-2.mb-3 {
            flex-wrap: wrap;
            gap: 10px;
        }

        form .col-md-4,
        form .col-md-3,
        form .col-md-2 {
            flex: 1 1 100%;
            max-width: 100%;
        }

        .pagination {
            flex-wrap: wrap;
            gap: 5px;
        }
    }

    @media (max-width: 480px) {
        h3.text-primary {
            font-size: 20px;
        }

        table.table th, table.table td {
            padding: 10px 8px;
        }

        .btn-info, .btn-warning, .btn-primary, .btn-secondary {
            padding: 6px 10px;
            font-size: 12px;
        }

        form button.btn-success {
            font-size: 14px;
            padding: 10px 0;
        }
    }
</style>

</head>


<body>

<div class="container mt-4">
    <div class="d-flex justify-content-end mb-3">
        <a href="logout.php" class="btn btn-danger btn-sm">Logout</a>
    </div>

    <h3 class="text-center text-primary">Work Permit Approval List</h3>
    <marquee>
        <?php while ($row = $marqueeResult->fetch_assoc()) { ?>
            <span>⚠ <?= htmlspecialchars($row['name']) ?> (<?= htmlspecialchars($row['passport_no']) ?>) - Approval Date: <?= htmlspecialchars($row['approval_date']) ?> | </span>
        <?php } ?>
    </marquee>

    <form method="GET" class="row g-2 mb-3">
        <div class="col-md-4">
            <input type="text" name="search" class="form-control" placeholder="Search Passport/Mobile" value="<?= isset($_GET['search']) ? htmlspecialchars($_GET['search']) : '' ?>">
        </div>
        <div class="col-md-3">
            <input type="date" name="start_date" class="form-control" value="<?= isset($_GET['start_date']) ? $_GET['start_date'] : '' ?>">
        </div>
        <div class="col-md-3">
            <input type="date" name="end_date" class="form-control" value="<?= isset($_GET['end_date']) ? $_GET['end_date'] : '' ?>">
        </div>
        <div class="col-md-2">
            <button type="submit" class="btn btn-success w-100">Search</button>
        </div>
    </form>

    <div class="table-container">
        <table class="table table-bordered text-center">
            <thead class="table-dark">
                <tr>
                    <th>#</th>
                    <th>Name</th>
                    <th>Passport No</th>
                    <th>Mobile No</th>
                    <th>Position</th>
                    <th>Job Country</th>
                    <th>Reference</th>
                    <th>Work Permit Status</th>
                    <th>Approval Date</th>
                    <th>File Upload</th>
                    <th>Status</th>
                    <th>Actions</th>
                </tr>
            </thead>
            <tbody>
                <?php 
                $sr = $offset + 1;
                if ($result->num_rows > 0) {
                    while ($row = $result->fetch_assoc()) { 
                        $days_left = $row['days_left'];
                        $status_class = "safe";
                        $status_text = "Safe";

                        if ($row['approval_date'] === NULL) {
                            $status_class = "expired";
                            $status_text = "No Approval Date";
                        } elseif ($days_left <= 0) {
                            $status_class = "expired";
                            $status_text = "Expired";
                        } elseif ($days_left <= 45) {
                            $status_class = "expiring-soon";
                            $status_text = "Expiring Soon";
                        }
                ?>
                <tr class="<?= $status_class ?>">
                    <td><?= $sr++ ?></td>
                    <td><?= htmlspecialchars($row['name']) ?></td>
                    <td><?= htmlspecialchars($row['passport_no']) ?></td>
                    <td><?= htmlspecialchars($row['mobile_no']) ?></td>
                    <td><?= htmlspecialchars($row['position']) ?></td>
                    <td><?= htmlspecialchars($row['job_country']) ?></td>
                    <td><?= htmlspecialchars($row['reference']) ?></td>
                    <td><?= htmlspecialchars($row['work_permit_received']) ?></td>
                    <td><?= htmlspecialchars($row['approval_date'] ?? 'N/A') ?></td>
                    <td>
                        <?php if ($row['file_upload']) { ?>
                            <a href="uploads/<?= basename($row['file_upload']) ?>" class="btn btn-info btn-sm" download>Download</a>
                        <?php } else { ?>
                            N/A
                        <?php } ?>
                    </td>
                    <td><?= $status_text ?></td>
                    <td>
                        <a href="approvallistedit.php?id=<?= $row['id'] ?>" class="btn btn-warning btn-sm">Edit</a>
                       

                    </td>
                </tr>
                <?php 
                    }
                } else {
                    echo "<tr><td colspan='12'>No records found.</td></tr>";
                }
                ?>
            </tbody>
        </table>
    </div>

    <!-- Export and Print Button -->
    <div class="text-center mb-3">
        <a href="export.php?search=<?= urlencode($_GET['search'] ?? '') ?>&start_date=<?= urlencode($_GET['start_date'] ?? '') ?>&end_date=<?= urlencode($_GET['end_date'] ?? '') ?>" class="btn btn-primary btn-sm">Export to CSV</a>
        <button onClick="window.print()" class="btn btn-secondary btn-sm">Print</button>
    </div>

    <nav>
        <ul class="pagination justify-content-center">
            <?php
            $countQuery = "SELECT COUNT(*) AS total FROM job_applicants ja 
                           LEFT JOIN work_permit_approval wa ON ja.id = wa.applicant_id
                           WHERE 1 $searchQuery $dateFilter";
            $countResult = $con->query($countQuery);
            $totalRows = $countResult->fetch_assoc()['total'];
            $totalPages = ceil($totalRows / $limit);

            for ($i = 1; $i <= $totalPages; $i++) {
                $active = ($i == $page) ? 'active' : '';
                echo "<li class='page-item $active'><a class='page-link' href='?page=$i'>$i</a></li>";
            }
            ?>
        </ul>
    </nav>
</div>
</body>
</html>
