<?php
require_once 'includes/auth.php';
require_role(['admin', 'inspector']);

require_once 'db.php';

// Fetch filter parameters if set
$client   = $_GET['client'] ?? '';
$category = $_GET['category'] ?? '';
$status   = $_GET['status'] ?? '';

$sql = "SELECT * FROM qry_jobs_master WHERE 1=1";
$params = [];

if (!empty($client)) {
    $sql .= " AND Client_Name LIKE ?";
    $params[] = "%$client%";
}
if (!empty($category)) {
    $sql .= " AND Category = ?";
    $params[] = $category;
}
if (!empty($status)) {
    $sql .= " AND Approval = ?";
    $params[] = $status;
}

$sql .= " ORDER BY ID DESC";

$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$jobs = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Set headers to force Excel file download
$filename = "Liftek_Inspection_Register_" . date('Y-m-d') . ".xls";
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=\"$filename\"");
header("Pragma: no-cache");
header("Expires: 0");

?>
<table border="1">
    <thead>
        <tr style="background-color: #2c3e50; color: #ffffff; font-weight: bold;">
            <th>Job No</th>
            <th>Issue Date</th>
            <th>Client Name</th>
            <th>Category</th>
            <th>Item Name</th>
            <th>Serial Number</th>
            <th>Inspector</th>
            <th>Status</th>
        </tr>
    </thead>
    <tbody>
        <?php foreach ($jobs as $j): ?>
            <tr>
                <td><?= htmlspecialchars($j['Job_No']); ?></td>
                <td><?= htmlspecialchars($j['Issue_Date'] ?? '-'); ?></td>
                <td><?= htmlspecialchars($j['Client_Name'] ?? '-'); ?></td>
                <td><?= htmlspecialchars($j['Category'] ?? '-'); ?></td>
                <td><?= htmlspecialchars($j['Item_Name'] ?? '-'); ?></td>
                <td>'<?= htmlspecialchars($j['s_n'] ?? '-'); ?></td>
                <td><?= htmlspecialchars($j['Engineer'] ?? 'Abdulla Qasim'); ?></td>
                <td style="font-weight: bold; color: <?= strtolower($j['Approval']) === 'pass' ? 'green' : 'red'; ?>;">
                    <?= strtoupper(htmlspecialchars($j['Approval'] ?? 'PENDING')); ?>
                </td>
            </tr>
        <?php endforeach; ?>
    </tbody>
</table>
