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

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

$search = isset($_GET['search']) ? trim($_GET['search']) : '';
$sort = isset($_GET['sort']) ? $_GET['sort'] : 'ASC';

if($sort != 'ASC' && $sort != 'DESC'){
    $sort = 'ASC';
}

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

if(!empty($search)){
    $where = "WHERE c.name LIKE ?";
    $params[] = "%$search%";
    $types .= "s";
}

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

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

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

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

/* ------------------------------
   Fetch Paginated Data
--------------------------------*/
$data_sql = "
SELECT c.*, a.name as associate_name
FROM clients c
LEFT JOIN associates a ON c.associate_id = a.id
$where
ORDER BY c.id $sort
LIMIT ?, ?
";

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

if(!empty($search)){
    $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 Clients</h5>
<a href="client_form.php" class="btn btn-success btn-sm">+ Add Client</a>
</div>

<div class="card-body">

<!-- SEARCH + SORT -->
<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 Client Name"
value="<?= htmlspecialchars($search) ?>">
</div>

<div class="col-md-3">
<select name="sort" class="form-select">
<option value="ASC" <?= $sort=='ASC'?'selected':'' ?>>Associate A-Z</option>
<option value="DESC" <?= $sort=='DESC'?'selected':'' ?>>Associate Z-A</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>Client Name</th>
<th>Associate</th>
<th>Contact Details</th>
<th>Certificates</th>
<th>Action</th>
</tr>
</thead>

<tbody>
<?php while($row=$result->fetch_assoc()): ?>
<tr>
<td><?= $row['id'] ?></td>
<td><?= $row['name'] ?></td>
<td><?= $row['associate_name'] ?></td>
<td><?= $row['address'] ?><br/><?= $row['city'] ?>, <?= $row['country'] ?><br/><?= 
$row['email'] ?><br/><?= $row['phone'] ?></td>
<td><?php
$count=$conn->query("SELECT COUNT(*) as total FROM certificates WHERE 
client_id=".$row['id'])->fetch_assoc()['total'];
?> 
<a href="client_certificates.php?client_id=<?= $row['id'] ?>" class="btn btn-sm 
btn-primary">
View (<?= $count ?>)
</a>
</td>
<td>
<a href="client_form.php?id=<?= $row['id'] ?>" class="btn btn-sm btn-info">Edit</a>
<a href="client_delete.php?id=<?= $row['id'] ?>"
class="btn btn-sm btn-danger"
onclick="return confirm('Delete this client?')">
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) ?>&sort=<?= $sort ?>">
<?= $i ?>
</a>
</li>
<?php endfor; ?>

</ul>
</nav>

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

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