<?php
include "dbconnect.php";

// Pagination
$limit = 10;
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$offset = ($page - 1) * $limit;

// Filters
$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'";
}

// Main Query
$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);

// 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>
    <meta name="viewport" content="width=device-width, initial-scale=1">

    <!-- Bootstrap + Icons -->
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <link href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css" rel="stylesheet">

    <style>
        body {
            background: linear-gradient(to right, #f2f6f9, #d9e2ec);
            font-family: 'Segoe UI', sans-serif;
            padding-top: 70px; /* Space for fixed logout button */
        }

        h3 {
            font-weight: 600;
            margin-bottom: 15px;
        }

        .marquee {
            background: #ffe6e6;
            padding: 10px;
            font-weight: bold;
            color: #b30000;
            border-radius: 5px;
        }

        .expired {
            background-color: #ff4d4d !important;
            color: white;
        }

        .expiring-soon {
            background-color: #fff3cd !important;
            color: #856404;
        }

        .safe {
            background-color: #d4edda !important;
            color: #155724;
        }

        .table th, .table td {
            vertical-align: middle;
            padding: 12px;
        }

        .btn-sm i {
            margin-right: 5px;
        }

        .search-form .form-control {
            border-radius: 5px;
        }

        .search-form button {
            border-radius: 5px;
        }

        @media (max-width: 768px) {
            .table-responsive {
                overflow-x: auto;
            }
        }

        .pagination .page-link {
            color: #007bff;
        }

        .pagination .active .page-link {
            background-color: #007bff;
            color: white;
            border-color: #007bff;
        }

        /* Logout button fixed top-right */
        .logout-btn {
            position: fixed;
            top: 15px;
            right: 15px;
            z-index: 1050;
            border-radius: 5px;
            font-weight: 600;
        }
    </style>
</head>
<body>

<!-- Logout Button -->
<a href="logout.php" class="btn btn-danger btn-sm logout-btn" title="Logout">
    <i class="fas fa-sign-out-alt"></i> Logout
</a>

<div class="container mt-4">
    <h3 class="text-center text-primary"><i class="fas fa-list-check"></i> Work Permit Approval List</h3>

    <!-- Marquee -->
    <div class="marquee mb-3">
        <marquee behavior="scroll" direction="left" scrollamount="6">
            <?php while ($row = $marqueeResult->fetch_assoc()) { ?>
                ⚠ <?= htmlspecialchars($row['name']) ?> (<?= htmlspecialchars($row['passport_no']) ?>) - Approval Date: <?= htmlspecialchars($row['approval_date']) ?> |
            <?php } ?>
        </marquee>
    </div>

    <!-- Search Form -->
    <form method="GET" class="row g-2 mb-3 search-form">
        <div class="col-md-4 col-12">
            <input type="text" name="search" class="form-control" placeholder="🔍 Passport or Mobile No" value="<?= isset($_GET['search']) ? htmlspecialchars($_GET['search']) : '' ?>">
        </div>
        <div class="col-md-3 col-6">
            <input type="date" name="start_date" class="form-control" value="<?= $_GET['start_date'] ?? '' ?>">
        </div>
        <div class="col-md-3 col-6">
            <input type="date" name="end_date" class="form-control" value="<?= $_GET['end_date'] ?? '' ?>">
        </div>
        <div class="col-md-2 col-12">
            <button type="submit" class="btn btn-success w-100"><i class="fas fa-filter"></i> Search</button>
        </div>
    </form>

    <!-- Table -->
    <div class="table-responsive">
        <table class="table table-bordered text-center table-hover">
            <thead class="table-dark">
                <tr>
                    <th>#</th>
                    <th>Name</th>
                    <th>Passport No</th>
                    <th>Mobile</th>
                    <th>Position</th>
                    <th>Country</th>
                    <th>Reference</th>
                    <th>Permit</th>
                    <th>Approval Date</th>
                    <th>File</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>
                                <i class="fas fa-download"></i> File
                            </a>
                        <?php } else { echo "N/A"; } ?>
                    </td>
                    <td><strong><?= $status_text ?></strong></td>
                    <td>
                        <a href="approvallistedit.php?id=<?= $row['id'] ?>" class="btn btn-warning btn-sm mb-1">
                            <i class="fas fa-edit"></i> Edit
                        </a>
                        <a href="approvallistdelete.php?id=<?= $row['id'] ?>" class="btn btn-danger btn-sm" onclick="return confirm('Delete this record?')">
                            <i class="fas fa-trash-alt"></i> Delete
                        </a>
                    </td>
                </tr>
                <?php }} else { echo "<tr><td colspan='12'>No records found.</td></tr>"; } ?>
            </tbody>
        </table>
    </div>

    <!-- Export & Print -->
    <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 me-2">
            <i class="fas fa-file-export"></i> Export CSV
        </a>
        <button onclick="window.print()" class="btn btn-secondary btn-sm">
            <i class="fas fa-print"></i> Print
        </button>
    </div>

    <!-- Pagination -->
    <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";
            $totalRows = $con->query($countQuery)->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>
