<?php
include 'conn.php'; // Include the database connection from conn.php

// Initialize variables
$message = '';
$message_type = ''; // 'success' or 'error'
$active_tab = isset($_GET['tab']) ? $_GET['tab'] : 'issue'; // Default to 'issue' tab
$used_batch_filter = isset($_GET['used_batch']) ? $_GET['used_batch'] : ''; // Batch filter for Used tab
$all_unused_batch_filter = isset($_GET['all_unused_batch']) ? $_GET['all_unused_batch'] : ''; // Batch filter for All Unused tab
$unused_issued_batch_filter = isset($_GET['unused_issued_batch']) ? $_GET['unused_issued_batch'] : ''; // Batch filter for Unused but Issued tab
$unused_unissued_batch_filter = isset($_GET['unused_unissued_batch']) ? $_GET['unused_unissued_batch'] : ''; // Batch filter for Unused and Unissued tab
$page = isset($_GET['page']) ? max(1, (int)$_GET['page']) : 1;
$per_page = 50;

// Fetch distinct batches once and store in array
$batches = [];
$batch_result = $conn->query("SELECT DISTINCT batch FROM IOTSIMCARDS WHERE batch IS NOT NULL ORDER BY batch ASC");
while ($batch_row = $batch_result->fetch_assoc()) {
    $batches[] = $batch_row['batch'];
}
$batch_result->close();

// Handle SIM issuance (Issue SIM Cards tab)
if ($_SERVER["REQUEST_METHOD"] == "POST" && $active_tab === 'issue') {
    $full_name = $_POST['technician'];
    $msisdn_input = $_POST['msisdn'];
    $msisdns = array_filter(array_map('trim', explode("\n", $msisdn_input))); // Split input by lines, trim whitespace

    // Fetch the phone_number for the selected full_name
    $stmt = $conn->prepare("SELECT phone_number FROM technician WHERE full_name = ?");
    $stmt->bind_param("s", $full_name);
    $stmt->execute();
    $result = $stmt->get_result();
    if ($result->num_rows > 0) {
        $row = $result->fetch_assoc();
        $technician_phone = $row['phone_number'];
    } else {
        $message = "Error: Selected technician not found.";
        $message_type = 'error';
        $stmt->close();
    }

    // Validate MSISDNs
    if (empty($message) && !empty($msisdns)) {
        foreach ($msisdns as $msisdn) {
            // Check if MSISDN is 9 or more digits
            if (!preg_match('/^\d{9,}$/', $msisdn)) {
                $message = "Error: MSISDN '$msisdn' must be 9 or more digits.";
                $message_type = 'error';
                break;
            }

            // Check if MSISDN exists in IOTSIMCARDS
            $stmt = $conn->prepare("SELECT MSISDN FROM IOTSIMCARDS WHERE MSISDN = ?");
            $stmt->bind_param("s", $msisdn);
            $stmt->execute();
            $result = $stmt->get_result();
            if ($result->num_rows == 0) {
                $message = "Error: MSISDN '$msisdn' does not exist in IOTSIMCARDS.";
                $message_type = 'error';
                $stmt->close();
                break;
            }
            $stmt->close();

            // Check if MSISDN is already allocated
            $stmt = $conn->prepare("SELECT msisdn FROM techallocations WHERE msisdn = ?");
            $stmt->bind_param("s", $msisdn);
            $stmt->execute();
            $result = $stmt->get_result();
            if ($result->num_rows > 0) {
                $message = "Error: MSISDN '$msisdn' is already allocated.";
                $message_type = 'error';
                $stmt->close();
                break;
            }
            $stmt->close();
        }
    } elseif (empty($msisdns)) {
        $message = "Error: No MSISDNs provided.";
        $message_type = 'error';
    }

    // Insert into techallocations if no errors
    if (empty($message)) {
        $success_count = 0;
        foreach ($msisdns as $msisdn) {
            $stmt = $conn->prepare("INSERT INTO techallocations (technician, msisdn) VALUES (?, ?)");
            $stmt->bind_param("ss", $technician_phone, $msisdn);
            if ($stmt->execute()) {
                $success_count++;
            } else {
                $message = "Error issuing MSISDN '$msisdn': " . $stmt->error;
                $message_type = 'error';
                $stmt->close();
                break;
            }
            $stmt->close();
        }
        if (empty($message) && $success_count > 0) {
            $message = "Successfully issued $success_count SIM card(s) to $full_name!";
            $message_type = 'success';
        }
    }
}

// Fetch data only for the active tab
$used_result = null;
$all_unused_result = null;
$unused_issued_result = null;
$unused_unissued_result = null;
$total_pages = 0;
$total_items = 0;

if ($active_tab === 'used') {
    // Optimized Used SIM cards query (changed to INNER JOIN for IOTSIMCARDS)
    $used_sql = "SELECT 
                    CASE 
                        WHEN t.msisdn IS NOT NULL THEN t.msisdn 
                        ELSE i.MSISDN 
                    END AS msisdn,
                    CASE 
                        WHEN t.msisdn IS NOT NULL THEN tech.full_name 
                        ELSE r.TECHNICIAN 
                    END AS technician_name,
                    CASE 
                        WHEN t.msisdn IS NOT NULL THEN t.date_of_allocation 
                        ELSE r.INSTALLATION_DATE 
                    END AS date_used,
                    r.VEHICLE_REG,
                    i.batch
                 FROM record10 r
                 INNER JOIN IOTSIMCARDS i ON TRIM(LEADING '0' FROM REPLACE(r.tracker_number, ' ', '')) = i.MSISDN
                 LEFT JOIN techallocations t ON i.MSISDN = t.msisdn
                 LEFT JOIN technician tech ON t.technician = tech.phone_number";

    $used_count_sql = "SELECT COUNT(*) as total " . substr($used_sql, strpos($used_sql, 'FROM'));

    if ($used_batch_filter !== '' && $used_batch_filter !== 'all') {
        $used_sql .= " WHERE i.batch = ?";
        $used_count_sql .= " WHERE i.batch = ?";
    } else {
        $used_sql .= " WHERE 1=1"; // Placeholder for consistency
        $used_count_sql .= " WHERE 1=1";
    }

    $used_sql .= " LIMIT ? OFFSET ?";
    $offset = ($page - 1) * $per_page;

    // Get total count
    if ($used_batch_filter !== '' && $used_batch_filter !== 'all') {
        $stmt = $conn->prepare($used_count_sql);
        $stmt->bind_param("s", $used_batch_filter);
    } else {
        $stmt = $conn->prepare($used_count_sql);
    }
    $stmt->execute();
    $count_result = $stmt->get_result();
    $total_items = $count_result->fetch_assoc()['total'];
    $total_pages = ceil($total_items / $per_page);
    $stmt->close();

    // Get data
    if ($used_batch_filter !== '' && $used_batch_filter !== 'all') {
        $stmt = $conn->prepare($used_sql);
        $stmt->bind_param("sii", $used_batch_filter, $per_page, $offset);
    } else {
        $stmt = $conn->prepare($used_sql);
        $stmt->bind_param("ii", $per_page, $offset);
    }
    $stmt->execute();
    $used_result = $stmt->get_result();
    $stmt->close();
} elseif ($active_tab === 'all_unused') {
    // Optimized All unused SIM cards (using LEFT JOIN instead of NOT IN)
    $all_unused_sql = "SELECT i.MSISDN, i.IMSI, i.batch 
                       FROM IOTSIMCARDS i 
                       LEFT JOIN record10 r ON i.MSISDN = TRIM(LEADING '0' FROM REPLACE(r.tracker_number, ' ', ''))
                       WHERE r.tracker_number IS NULL";

    $all_unused_count_sql = "SELECT COUNT(*) as total " . substr($all_unused_sql, strpos($all_unused_sql, 'FROM'));

    if ($all_unused_batch_filter !== '' && $all_unused_batch_filter !== 'all') {
        $all_unused_sql .= " AND i.batch = ?";
        $all_unused_count_sql .= " AND i.batch = ?";
    }

    $all_unused_sql .= " LIMIT ? OFFSET ?";
    $offset = ($page - 1) * $per_page;

    // Get total count
    if ($all_unused_batch_filter !== '' && $all_unused_batch_filter !== 'all') {
        $stmt = $conn->prepare($all_unused_count_sql);
        $stmt->bind_param("s", $all_unused_batch_filter);
    } else {
        $stmt = $conn->prepare($all_unused_count_sql);
    }
    $stmt->execute();
    $count_result = $stmt->get_result();
    $total_items = $count_result->fetch_assoc()['total'];
    $total_pages = ceil($total_items / $per_page);
    $stmt->close();

    // Get data
    if ($all_unused_batch_filter !== '' && $all_unused_batch_filter !== 'all') {
        $stmt = $conn->prepare($all_unused_sql);
        $stmt->bind_param("sii", $all_unused_batch_filter, $per_page, $offset);
    } else {
        $stmt = $conn->prepare($all_unused_sql);
        $stmt->bind_param("ii", $per_page, $offset);
    }
    $stmt->execute();
    $all_unused_result = $stmt->get_result();
    $stmt->close();
} elseif ($active_tab === 'unused_issued') {
    // Optimized Unused but issued (using LEFT JOIN instead of NOT IN)
    $unused_issued_sql = "SELECT t.msisdn, tech.full_name, t.date_of_allocation, i.batch 
                          FROM techallocations t 
                          JOIN technician tech ON t.technician = tech.phone_number 
                          JOIN IOTSIMCARDS i ON t.msisdn = i.MSISDN
                          LEFT JOIN record10 r ON TRIM(LEADING '0' FROM t.msisdn) = TRIM(LEADING '0' FROM REPLACE(r.tracker_number, ' ', ''))
                          WHERE r.tracker_number IS NULL";

    $unused_issued_count_sql = "SELECT COUNT(*) as total " . substr($unused_issued_sql, strpos($unused_issued_sql, 'FROM'));

    if ($unused_issued_batch_filter !== '' && $unused_issued_batch_filter !== 'all') {
        $unused_issued_sql .= " AND i.batch = ?";
        $unused_issued_count_sql .= " AND i.batch = ?";
    }

    $unused_issued_sql .= " LIMIT ? OFFSET ?";
    $offset = ($page - 1) * $per_page;

    // Get total count
    if ($unused_issued_batch_filter !== '' && $unused_issued_batch_filter !== 'all') {
        $stmt = $conn->prepare($unused_issued_count_sql);
        $stmt->bind_param("s", $unused_issued_batch_filter);
    } else {
        $stmt = $conn->prepare($unused_issued_count_sql);
    }
    $stmt->execute();
    $count_result = $stmt->get_result();
    $total_items = $count_result->fetch_assoc()['total'];
    $total_pages = ceil($total_items / $per_page);
    $stmt->close();

    // Get data
    if ($unused_issued_batch_filter !== '' && $unused_issued_batch_filter !== 'all') {
        $stmt = $conn->prepare($unused_issued_sql);
        $stmt->bind_param("sii", $unused_issued_batch_filter, $per_page, $offset);
    } else {
        $stmt = $conn->prepare($unused_issued_sql);
        $stmt->bind_param("ii", $per_page, $offset);
    }
    $stmt->execute();
    $unused_issued_result = $stmt->get_result();
    $stmt->close();
} elseif ($active_tab === 'unused_unissued') {
    // Optimized Unused and unissued (using LEFT JOINs instead of NOT IN)
    $unused_unissued_sql = "SELECT i.MSISDN, i.IMSI, i.batch 
                            FROM IOTSIMCARDS i 
                            LEFT JOIN techallocations t ON i.MSISDN = t.msisdn
                            LEFT JOIN record10 r ON i.MSISDN = TRIM(LEADING '0' FROM REPLACE(r.tracker_number, ' ', ''))
                            WHERE t.msisdn IS NULL AND r.tracker_number IS NULL";

    $unused_unissued_count_sql = "SELECT COUNT(*) as total " . substr($unused_unissued_sql, strpos($unused_unissued_sql, 'FROM'));

    if ($unused_unissued_batch_filter !== '' && $unused_unissued_batch_filter !== 'all') {
        $unused_unissued_sql .= " AND i.batch = ?";
        $unused_unissued_count_sql .= " AND i.batch = ?";
    }

    $unused_unissued_sql .= " LIMIT ? OFFSET ?";
    $offset = ($page - 1) * $per_page;

    // Get total count
    if ($unused_unissued_batch_filter !== '' && $unused_unissued_batch_filter !== 'all') {
        $stmt = $conn->prepare($unused_unissued_count_sql);
        $stmt->bind_param("s", $unused_unissued_batch_filter);
    } else {
        $stmt = $conn->prepare($unused_unissued_count_sql);
    }
    $stmt->execute();
    $count_result = $stmt->get_result();
    $total_items = $count_result->fetch_assoc()['total'];
    $total_pages = ceil($total_items / $per_page);
    $stmt->close();

    // Get data
    if ($unused_unissued_batch_filter !== '' && $unused_unissued_batch_filter !== 'all') {
        $stmt = $conn->prepare($unused_unissued_sql);
        $stmt->bind_param("sii", $unused_unissued_batch_filter, $per_page, $offset);
    } else {
        $stmt = $conn->prepare($unused_unissued_sql);
        $stmt->bind_param("ii", $per_page, $offset);
    }
    $stmt->execute();
    $unused_unissued_result = $stmt->get_result();
    $stmt->close();
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>SIM Card Management</title>
    <script src="https://cdn.tailwindcss.com"></script>
    <style>
        body {
            background: linear-gradient(135deg, #1e3a8a 0%, #6b7280 100%);
            font-family: 'Inter', sans-serif;
        }
        .card {
            background: white;
            border-radius: 1rem;
            box-shadow: 0 10px 20px rgba(0, 0, 0, 0.15);
            transition: transform 0.3s ease, box-shadow 0.3s ease;
        }
        .card:hover {
            transform: translateY(-5px);
            box-shadow: 0 15px 30px rgba(0, 0, 0, 0.2);
        }
        .tab-button {
            background: linear-gradient(90deg, #4f46e5 0%, #7c3aed 100%);
            transition: background 0.3s ease, transform 0.2s ease;
        }
        .tab-button:hover {
            background: linear-gradient(90deg, #5b21b6 0%, #9333ea 100%);
            transform: translateY(-2px);
        }
        .tab-button.active {
            background: linear-gradient(90deg, #312e81 0%, #5b21b6 100%);
            transform: translateY(0);
        }
        .table-header {
            background: linear-gradient(90deg, #4f46e5 0%, #7c3aed 100%);
            position: sticky;
            top: 0;
            z-index: 10;
        }
        .table-row:hover {
            background-color: #f3f4f6;
        }
        .btn-primary {
            background: linear-gradient(90deg, #4f46e5 0%, #7c3aed 100%);
            transition: background 0.3s ease;
        }
        .btn-primary:hover {
            background: linear-gradient(90deg, #5b21b6 0%, #9333ea 100%);
        }
        textarea {
            resize: vertical;
            min-height: 120px;
            transition: border-color 0.3s ease;
        }
        .message {
            animation: slideIn 0.5s ease-out;
        }
        @keyframes slideIn {
            from { transform: translateY(-20px); opacity: 0; }
            to { transform: translateY(0); opacity: 1; }
        }
        .pagination a {
            color: white;
            padding: 8px 16px;
            text-decoration: none;
            background-color: #4f46e5;
            border-radius: 4px;
            margin: 0 4px;
        }
        .pagination a:hover {
            background-color: #5b21b6;
        }
        .pagination span {
            padding: 8px 16px;
            color: gray;
        }
    </style>
    <script>
        // JavaScript for tab switching
        function showTab(tabId) {
            document.querySelectorAll('.tab-content').forEach(content => content.classList.add('hidden'));
            document.querySelector(`#${tabId}`).classList.remove('hidden');
            document.querySelectorAll('.tab-button').forEach(button => button.classList.remove('active'));
            document.querySelector(`[data-tab="${tabId}"]`).classList.add('active');
        }
    </script>
</head>
<body class="min-h-screen flex items-center justify-center p-4" onload="showTab('<?php echo $active_tab; ?>')">
    <div class="card p-8 w-full max-w-5xl">
        <h1 class="text-3xl font-extrabold text-center text-gray-900 mb-6">SIM Card Management</h1>
        <!-- Return to Home Button -->
        <div class="mb-6">
            <a href="dashboard.php" class="btn-primary inline-block text-white py-2 px-4 rounded-lg font-medium focus:outline-none focus:ring-2 focus:ring-indigo-400">
                Return to Home
            </a>
        </div>

        <!-- Tabs -->
        <div class="flex space-x-2 mb-6">
            <button data-tab="issue" class="tab-button text-white py-2 px-4 rounded-lg font-medium focus:outline-none <?php echo $active_tab === 'issue' ? 'active' : ''; ?>" onclick="showTab('issue')">Issue SIM Cards</button>
            <button data-tab="used" class="tab-button text-white py-2 px-4 rounded-lg font-medium focus:outline-none <?php echo $active_tab === 'used' ? 'active' : ''; ?>" onclick="showTab('used')">Used SIM Cards</button>
            <button data-tab="all_unused" class="tab-button text-white py-2 px-4 rounded-lg font-medium focus:outline-none <?php echo $active_tab === 'all_unused' ? 'active' : ''; ?>" onclick="showTab('all_unused')">All Unused SIM Cards</button>
            <button data-tab="unused_issued" class="tab-button text-white py-2 px-4 rounded-lg font-medium focus:outline-none <?php echo $active_tab === 'unused_issued' ? 'active' : ''; ?>" onclick="showTab('unused_issued')">Unused but Issued SIM Cards</button>
            <button data-tab="unused_unissued" class="tab-button text-white py-2 px-4 rounded-lg font-medium focus:outline-none <?php echo $active_tab === 'unused_unissued' ? 'active' : ''; ?>" onclick="showTab('unused_unissued')">Unused and Unissued SIM Cards</button>
        </div>

        <!-- Issue SIM Cards Tab -->
        <div id="issue" class="tab-content <?php echo $active_tab !== 'issue' ? 'hidden' : ''; ?>">
            <!-- Display success or error message -->
            <?php if (!empty($message)): ?>
                <div class="message mb-6 p-4 rounded-lg text-white font-medium <?php echo $message_type === 'success' ? 'bg-green-500' : 'bg-red-500'; ?>">
                    <?php echo htmlspecialchars($message); ?>
                </div>
            <?php endif; ?>

            <form method="POST" action="?tab=issue" class="space-y-6">
                <!-- Technician Dropdown -->
                <div>
                    <label for="technician" class="block text-sm font-medium text-gray-700">Technician</label>
                    <select name="technician" id="technician" class="mt-1 block w-full border-gray-300 rounded-lg shadow-sm focus:ring-indigo-500 focus:border-indigo-500 text-gray-900 bg-gray-50 p-2" required>
                        <option value="">Select a Technician</option>
                        <?php
                        $result = $conn->query("SELECT full_name FROM technician ORDER BY full_name ASC");
                        if ($result->num_rows > 0) {
                            while ($row = $result->fetch_assoc()) {
                                echo "<option value='" . htmlspecialchars($row['full_name']) . "'>" . htmlspecialchars($row['full_name']) . "</option>";
                            }
                        } else {
                            echo "<option value='' disabled>No technicians available</option>";
                        }
                        $result->close();
                        ?>
                    </select>
                </div>

                <!-- MSISDN Textarea -->
                <div>
                    <label for="msisdn" class="block text-sm font-medium text-gray-700">MSISDN(s) (One per line, 9+ digits)</label>
                    <textarea name="msisdn" id="msisdn" class="mt-1 block w-full border-gray-300 rounded-lg shadow-sm focus:ring-indigo-500 focus:border-indigo-500 bg-gray-50 p-2" placeholder="Enter MSISDNs, one per line (e.g., 300002001010)" required></textarea>
                </div>

                <!-- Submit Button -->
                <div>
                    <button type="submit" class="btn-primary w-full text-white py-3 px-4 rounded-lg font-medium focus:outline-none focus:ring-2 focus:ring-indigo-400">
                        Issue SIM Card(s)
                    </button>
                </div>
            </form>
        </div>

        <!-- Used SIM Cards Tab -->
        <div id="used" class="tab-content <?php echo $active_tab !== 'used' ? 'hidden' : ''; ?>">
            <form method="GET" action="" class="mb-6">
                <input type="hidden" name="tab" value="used">
                <label for="used_batch" class="block text-sm font-medium text-gray-700 mb-2">Filter by Batch</label>
                <div class="flex items-center space-x-4">
                    <select name="used_batch" id="used_batch" class="block w-1/3 border-gray-300 rounded-lg shadow-sm focus:ring-indigo-500 focus:border-indigo-500 text-gray-900 bg-gray-50 p-2">
                        <option value="all" <?php echo $used_batch_filter === 'all' || $used_batch_filter === '' ? 'selected' : ''; ?>>All Batches</option>
                        <?php
                        foreach ($batches as $batch) {
                            echo "<option value='" . htmlspecialchars($batch) . "' " . ($used_batch_filter === $batch ? 'selected' : '') . ">" . htmlspecialchars($batch) . "</option>";
                        }
                        ?>
                    </select>
                    <button type="submit" class="btn-primary text-white py-2 px-4 rounded-lg font-medium focus:outline-none focus:ring-2 focus:ring-indigo-400">
                        Filter
                    </button>
                </div>
            </form>

            <?php if ($used_result && $used_result->num_rows > 0): ?>
                <div class="overflow-x-auto">
                    <table class="w-full text-left border-collapse">
                        <thead class="table-header text-white">
                            <tr>
                                <th class="p-4 font-medium">S/N</th>
                                <th class="p-4 font-medium">MSISDN</th>
                                <th class="p-4 font-medium">Technician</th>
                                <th class="p-4 font-medium">Vehicle Reg</th>
                                <th class="p-4 font-medium">Batch</th>
                                <th class="p-4 font-medium">Date Used</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php $serial_number = ($page - 1) * $per_page + 1; ?>
                            <?php while ($row = $used_result->fetch_assoc()): ?>
                                <tr class="table-row border-b border-gray-200">
                                    <td class="p-4 text-gray-700"><?php echo $serial_number++; ?></td>
                                    <td class="p-4 text-gray-700"><?php echo htmlspecialchars($row['msisdn']); ?></td>
                                    <td class="p-4 text-gray-700"><?php echo htmlspecialchars($row['technician_name'] ?? 'N/A'); ?></td>
                                    <td class="p-4 text-gray-700"><?php echo htmlspecialchars($row['VEHICLE_REG'] ?? 'N/A'); ?></td>
                                    <td class="p-4 text-gray-700"><?php echo htmlspecialchars($row['batch'] ?? 'N/A'); ?></td>
                                    <td class="p-4 text-gray-700"><?php echo htmlspecialchars($row['date_used'] ?? 'N/A'); ?></td>
                                </tr>
                            <?php endwhile; ?>
                        </tbody>
                    </table>
                </div>
            <?php else: ?>
                <div class="text-center text-gray-700 p-4">
                    No SIM cards are marked as used<?php echo $used_batch_filter && $used_batch_filter !== 'all' ? ' for the selected batch.' : '.'; ?>
                </div>
            <?php endif; ?>
            <?php if ($total_pages > 1): ?>
                <div class="pagination mt-4 text-center">
                    <?php if ($page > 1): ?>
                        <a href="?tab=used&page=<?php echo $page - 1; ?>&used_batch=<?php echo urlencode($used_batch_filter); ?>">Previous</a>
                    <?php endif; ?>
                    <span>Page <?php echo $page; ?> of <?php echo $total_pages; ?></span>
                    <?php if ($page < $total_pages): ?>
                        <a href="?tab=used&page=<?php echo $page + 1; ?>&used_batch=<?php echo urlencode($used_batch_filter); ?>">Next</a>
                    <?php endif; ?>
                </div>
            <?php endif; ?>
        </div>

        <!-- All Unused SIM Cards Tab -->
        <div id="all_unused" class="tab-content <?php echo $active_tab !== 'all_unused' ? 'hidden' : ''; ?>">
            <form method="GET" action="" class="mb-6">
                <input type="hidden" name="tab" value="all_unused">
                <label for="all_unused_batch" class="block text-sm font-medium text-gray-700 mb-2">Filter by Batch</label>
                <div class="flex items-center space-x-4">
                    <select name="all_unused_batch" id="all_unused_batch" class="block w-1/3 border-gray-300 rounded-lg shadow-sm focus:ring-indigo-500 focus:border-indigo-500 text-gray-900 bg-gray-50 p-2">
                        <option value="all" <?php echo $all_unused_batch_filter === 'all' || $all_unused_batch_filter === '' ? 'selected' : ''; ?>>All Batches</option>
                        <?php
                        foreach ($batches as $batch) {
                            echo "<option value='" . htmlspecialchars($batch) . "' " . ($all_unused_batch_filter === $batch ? 'selected' : '') . ">" . htmlspecialchars($batch) . "</option>";
                        }
                        ?>
                    </select>
                    <button type="submit" class="btn-primary text-white py-2 px-4 rounded-lg font-medium focus:outline-none focus:ring-2 focus:ring-indigo-400">
                        Filter
                    </button>
                </div>
            </form>

            <?php if ($all_unused_result && $all_unused_result->num_rows > 0): ?>
                <div class="overflow-x-auto">
                    <table class="w-full text-left border-collapse">
                        <thead class="table-header text-white">
                            <tr>
                                <th class="p-4 font-medium">S/N</th>
                                <th class="p-4 font-medium">MSISDN</th>
                                <th class="p-4 font-medium">IMSI</th>
                                <th class="p-4 font-medium">Batch</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php $serial_number = ($page - 1) * $per_page + 1; ?>
                            <?php while ($row = $all_unused_result->fetch_assoc()): ?>
                                <tr class="table-row border-b border-gray-200">
                                    <td class="p-4 text-gray-700"><?php echo $serial_number++; ?></td>
                                    <td class="p-4 text-gray-700"><?php echo htmlspecialchars($row['MSISDN']); ?></td>
                                    <td class="p-4 text-gray-700"><?php echo htmlspecialchars($row['IMSI']); ?></td>
                                    <td class="p-4 text-gray-700"><?php echo htmlspecialchars($row['batch'] ?? 'N/A'); ?></td>
                                </tr>
                            <?php endwhile; ?>
                        </tbody>
                    </table>
                </div>
            <?php else: ?>
                <div class="text-center text-gray-700 p-4">
                    No unused SIM cards found<?php echo $all_unused_batch_filter && $all_unused_batch_filter !== 'all' ? ' for the selected batch.' : '.'; ?>
                </div>
            <?php endif; ?>
            <?php if ($total_pages > 1): ?>
                <div class="pagination mt-4 text-center">
                    <?php if ($page > 1): ?>
                        <a href="?tab=all_unused&page=<?php echo $page - 1; ?>&all_unused_batch=<?php echo urlencode($all_unused_batch_filter); ?>">Previous</a>
                    <?php endif; ?>
                    <span>Page <?php echo $page; ?> of <?php echo $total_pages; ?></span>
                    <?php if ($page < $total_pages): ?>
                        <a href="?tab=all_unused&page=<?php echo $page + 1; ?>&all_unused_batch=<?php echo urlencode($all_unused_batch_filter); ?>">Next</a>
                    <?php endif; ?>
                </div>
            <?php endif; ?>
        </div>

        <!-- Unused but Issued SIM Cards Tab -->
        <div id="unused_issued" class="tab-content <?php echo $active_tab !== 'unused_issued' ? 'hidden' : ''; ?>">
            <form method="GET" action="" class="mb-6">
                <input type="hidden" name="tab" value="unused_issued">
                <label for="unused_issued_batch" class="block text-sm font-medium text-gray-700 mb-2">Filter by Batch</label>
                <div class="flex items-center space-x-4">
                    <select name="unused_issued_batch" id="unused_issued_batch" class="block w-1/3 border-gray-300 rounded-lg shadow-sm focus:ring-indigo-500 focus:border-indigo-500 text-gray-900 bg-gray-50 p-2">
                        <option value="all" <?php echo $unused_issued_batch_filter === 'all' || $unused_issued_batch_filter === '' ? 'selected' : ''; ?>>All Batches</option>
                        <?php
                        foreach ($batches as $batch) {
                            echo "<option value='" . htmlspecialchars($batch) . "' " . ($unused_issued_batch_filter === $batch ? 'selected' : '') . ">" . htmlspecialchars($batch) . "</option>";
                        }
                        ?>
                    </select>
                    <button type="submit" class="btn-primary text-white py-2 px-4 rounded-lg font-medium focus:outline-none focus:ring-2 focus:ring-indigo-400">
                        Filter
                    </button>
                </div>
            </form>

            <?php if ($unused_issued_result && $unused_issued_result->num_rows > 0): ?>
                <div class="overflow-x-auto">
                    <table class="w-full text-left border-collapse">
                        <thead class="table-header text-white">
                            <tr>
                                <th class="p-4 font-medium">S/N</th>
                                <th class="p-4 font-medium">MSISDN</th>
                                <th class="p-4 font-medium">Technician</th>
                                <th class="p-4 font-medium">Date Allocated</th>
                                <th class="p-4 font-medium">Batch</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php $serial_number = ($page - 1) * $per_page + 1; ?>
                            <?php while ($row = $unused_issued_result->fetch_assoc()): ?>
                                <tr class="table-row border-b border-gray-200">
                                    <td class="p-4 text-gray-700"><?php echo $serial_number++; ?></td>
                                    <td class="p-4 text-gray-700"><?php echo htmlspecialchars($row['msisdn']); ?></td>
                                    <td class="p-4 text-gray-700"><?php echo htmlspecialchars($row['full_name']); ?></td>
                                    <td class="p-4 text-gray-700"><?php echo htmlspecialchars($row['date_of_allocation']); ?></td>
                                    <td class="p-4 text-gray-700"><?php echo htmlspecialchars($row['batch'] ?? 'N/A'); ?></td>
                                </tr>
                            <?php endwhile; ?>
                        </tbody>
                    </table>
                </div>
            <?php else: ?>
                <div class="text-center text-gray-700 p-4">
                    No unused but issued SIM cards found<?php echo $unused_issued_batch_filter && $unused_issued_batch_filter !== 'all' ? ' for the selected batch.' : '.'; ?>
                </div>
            <?php endif; ?>
            <?php if ($total_pages > 1): ?>
                <div class="pagination mt-4 text-center">
                    <?php if ($page > 1): ?>
                        <a href="?tab=unused_issued&page=<?php echo $page - 1; ?>&unused_issued_batch=<?php echo urlencode($unused_issued_batch_filter); ?>">Previous</a>
                    <?php endif; ?>
                    <span>Page <?php echo $page; ?> of <?php echo $total_pages; ?></span>
                    <?php if ($page < $total_pages): ?>
                        <a href="?tab=unused_issued&page=<?php echo $page + 1; ?>&unused_issued_batch=<?php echo urlencode($unused_issued_batch_filter); ?>">Next</a>
                    <?php endif; ?>
                </div>
            <?php endif; ?>
        </div>

        <!-- Unused and Unissued SIM Cards Tab -->
        <div id="unused_unissued" class="tab-content <?php echo $active_tab !== 'unused_unissued' ? 'hidden' : ''; ?>">
            <form method="GET" action="" class="mb-6">
                <input type="hidden" name="tab" value="unused_unissued">
                <label for="unused_unissued_batch" class="block text-sm font-medium text-gray-700 mb-2">Filter by Batch</label>
                <div class="flex items-center space-x-4">
                    <select name="unused_unissued_batch" id="unused_unissued_batch" class="block w-1/3 border-gray-300 rounded-lg shadow-sm focus:ring-indigo-500 focus:border-indigo-500 text-gray-900 bg-gray-50 p-2">
                        <option value="all" <?php echo $unused_unissued_batch_filter === 'all' || $unused_unissued_batch_filter === '' ? 'selected' : ''; ?>>All Batches</option>
                        <?php
                        foreach ($batches as $batch) {
                            echo "<option value='" . htmlspecialchars($batch) . "' " . ($unused_unissued_batch_filter === $batch ? 'selected' : '') . ">" . htmlspecialchars($batch) . "</option>";
                        }
                        ?>
                    </select>
                    <button type="submit" class="btn-primary text-white py-2 px-4 rounded-lg font-medium focus:outline-none focus:ring-2 focus:ring-indigo-400">
                        Filter
                    </button>
                </div>
            </form>

            <?php if ($unused_unissued_result && $unused_unissued_result->num_rows > 0): ?>
                <div class="overflow-x-auto">
                    <table class="w-full text-left border-collapse">
                        <thead class="table-header text-white">
                            <tr>
                                <th class="p-4 font-medium">S/N</th>
                                <th class="p-4 font-medium">MSISDN</th>
                                <th class="p-4 font-medium">IMSI</th>
                                <th class="p-4 font-medium">Batch</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php $serial_number = ($page - 1) * $per_page + 1; ?>
                            <?php while ($row = $unused_unissued_result->fetch_assoc()): ?>
                                <tr class="table-row border-b border-gray-200">
                                    <td class="p-4 text-gray-700"><?php echo $serial_number++; ?></td>
                                    <td class="p-4 text-gray-700"><?php echo htmlspecialchars($row['MSISDN']); ?></td>
                                    <td class="p-4 text-gray-700"><?php echo htmlspecialchars($row['IMSI']); ?></td>
                                    <td class="p-4 text-gray-700"><?php echo htmlspecialchars($row['batch'] ?? 'N/A'); ?></td>
                                </tr>
                            <?php endwhile; ?>
                        </tbody>
                    </table>
                </div>
            <?php else: ?>
                <div class="text-center text-gray-700 p-4">
                    No unused and unissued SIM cards found<?php echo $unused_unissued_batch_filter && $unused_unissued_batch_filter !== 'all' ? ' for the selected batch.' : '.'; ?>
                </div>
            <?php endif; ?>
            <?php if ($total_pages > 1): ?>
                <div class="pagination mt-4 text-center">
                    <?php if ($page > 1): ?>
                        <a href="?tab=unused_unissued&page=<?php echo $page - 1; ?>&unused_unissued_batch=<?php echo urlencode($unused_unissued_batch_filter); ?>">Previous</a>
                    <?php endif; ?>
                    <span>Page <?php echo $page; ?> of <?php echo $total_pages; ?></span>
                    <?php if ($page < $total_pages): ?>
                        <a href="?tab=unused_unissued&page=<?php echo $page + 1; ?>&unused_unissued_batch=<?php echo urlencode($unused_unissued_batch_filter); ?>">Next</a>
                    <?php endif; ?>
                </div>
            <?php endif; ?>
        </div>
    </div>
</body>
</html>

<?php
$conn->close();
?>