<?php
include("../includes/db_connection.php");
include("../includes/auth.php");
include("partials/header.php");

$limit = 100;
$page = isset($_GET['page']) ? max(1,intval($_GET['page'])) : 1;
$start = ($page - 1) * $limit;

$search = isset($_GET['search']) ? trim($_GET['search']) : '';
$status_filter = isset($_GET['status']) ? $_GET['status'] : 'all';

/* ---------------------------
   Build WHERE conditions
----------------------------*/
$where = "WHERE 1=1";
$params = [];
$types = "";

if(!empty($search)){
    $where .= " AND cert.number LIKE ?";
    $params[] = "%$search%";
    $types .= "s";
}

if($status_filter != 'all'){
    $where .= " AND cert.status = ?";
    $params[] = $status_filter;
    $types .= "s";
}

/* ---------------------------
   Count total records
----------------------------*/
$count_sql = "
SELECT COUNT(*) as total
FROM certificates cert
LEFT JOIN clients c ON cert.client_id=c.id
LEFT JOIN associates a ON c.associate_id=a.id
$where
";

$stmt = $conn->prepare($count_sql);

if(!empty($params)){
    $stmt->bind_param($types, ...$params);
}

$stmt->execute();
$total = $stmt->get_result()->fetch_assoc()['total'];
$pages = ceil($total / $limit);

/* ---------------------------
   Fetch paginated records
----------------------------*/
$data_sql = "
SELECT cert.*, 
c.name as client_name,
a.name as associate_name
FROM certificates cert
LEFT JOIN clients c ON cert.client_id=c.id
LEFT JOIN associates a ON c.associate_id=a.id
$where
ORDER BY cert.id DESC
LIMIT ?, ?
";

$stmt = $conn->prepare($data_sql);

if(!empty($params)){
    $types .= "ii";
    $params[] = $start;
    $params[] = $limit;
    $stmt->bind_param($types, ...$params);
}else{
    $stmt->bind_param("ii", $start, $limit);
}

$stmt->execute();
$result = $stmt->get_result();
?>

<div class="container-fluid py-4">
<div class="card shadow">

<div class="card-header bg-dark text-white d-flex justify-content-between 
align-items-center">
<h5 class="mb-0">Manage Certificates</h5>
<a href="certificate_form.php" class="btn btn-success btn-sm">+ Add Certificate</a>
</div>

<div class="card-body">

<!-- SEARCH + FILTER -->
<form method="GET" class="row g-2 mb-3">

<div class="col-md-4">
<input type="text" name="search" class="form-control"
placeholder="Search by Certificate No"
value="<?= htmlspecialchars($search) ?>">
</div>

<div class="col-md-3">
<select name="status" class="form-select">
<option value="all" <?= $status_filter=='all'?'selected':'' ?>>All Status</option>
<option value="Active" <?= $status_filter=='Active'?'selected':'' ?>>Active</option>
<option value="Suspended" <?= $status_filter=='Suspended'?'selected':'' 
?>>Suspended</option>
<option value="Withdrawn" <?= $status_filter=='Withdrawn'?'selected':'' 
?>>Withdrawn</option>
</select>
</div>

<div class="col-md-2">
<button class="btn btn-primary w-100">Filter</button>
</div>

</form>

<div class="table-responsive">
<table class="table table-bordered table-hover align-middle">
<thead class="table-light">
<tr>
<th>ID</th>
<th>Certificate No</th>
<th>Client</th>
<th>Associate</th>
<th>Dates</th>
<th>Status</th>
<th>Action</th>
</tr>
</thead>

<tbody>
<?php while($row=$result->fetch_assoc()): ?>
<tr>
<td><?= $row['id'] ?></td>
<td><?= $row['number'] ?></td>
<td><?= $row['client_name'] ?></td>
<td><?= $row['associate_name'] ?></td>
<td><?= $row['rdate'] ?><br/><?= $row['edate'] ?></td>

<td>
<?php
if($row['status']=='Active')
echo "<span class='badge bg-success'>Active</span>";
elseif($row['status']=='Suspended')
echo "<span class='badge bg-warning text-dark'>Suspended</span>";
elseif($row['status']=='Withdrawn')
echo "<span class='badge bg-danger'>Withdrawn</span>";
?>
</td>

<td>
<a href="certificate_form.php?id=<?= $row['id'] ?>" class="btn btn-sm btn-info">Edit</a>
<a href="certificate_delete.php?id=<?= $row['id'] ?>" 
class="btn btn-sm btn-danger"
onclick="return confirm('Are you sure you want to delete this certificate?');">
Delete
</a>
</td>
</tr>
<?php endwhile; ?>
</tbody>
</table>
</div>

<!-- PAGINATION -->
<nav>
<ul class="pagination justify-content-center">
<?php for($i=1;$i<=$pages;$i++): ?>
<li class="page-item <?= ($i==$page)?'active':'' ?>">
<a class="page-link"
href="?page=<?= $i ?>&search=<?= urlencode($search) ?>&status=<?= $status_filter ?>">
<?= $i ?>
</a>
</li>
<?php endfor; ?>
</ul>
</nav>

</div>
</div>
</div>

<?php include("partials/footer.php"); ?>