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

/* ===============================
   Fetch Associates with Counts
=================================*/

$sql = "
SELECT 
a.*,

-- Client Count
(SELECT COUNT(*) 
 FROM clients c 
 WHERE c.associate_id = a.id) AS total_clients,

-- Certificate Count
(SELECT COUNT(*) 
 FROM certificates cert
 LEFT JOIN clients c2 ON cert.client_id = c2.id
 WHERE c2.associate_id = a.id) AS total_certificates

FROM associates a
ORDER BY a.id DESC
";

$result = $conn->query($sql);
?>

<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 Associates</h5>
<a href="associate_form.php" class="btn btn-success btn-sm">+ Add Associate</a>
</div>

<div class="card-body">

<div class="table-responsive">
<table class="table table-bordered table-hover align-middle">
<thead class="table-light">
<tr>
<th>ID</th>
<th>Name</th>
<th>Email</th>
<th>Clients</th>
<th>Certificates</th>
<th>Status</th>
<th>Action</th>
</tr>
</thead>

<tbody>
<?php while($row=$result->fetch_assoc()): ?>
<tr>
<td><?= $row['id'] ?></td>
<td><?= $row['name'] ?></td>
<td><?= $row['email'] ?></td>

<td>
<span class="badge bg-primary">
<?= $row['total_clients'] ?>
</span>
<a href="associate_clients.php?id=<?= $row['id'] ?>" 
class="btn btn-sm btn-outline-secondary ms-2">
View
</a>
</td>

<td>
<span class="badge bg-info text-dark">
<?= $row['total_certificates'] ?>
</span>
</td>

<td>
<?php
if($row['status']=='active')
echo "<span class='badge bg-success'>Active</span>";
else
echo "<span class='badge bg-danger'>Inactive</span>";
?>
</td>

<td>
<a href="associate_form.php?id=<?= $row['id'] ?>" 
class="btn btn-sm btn-warning">Edit</a>
</td>

</tr>
<?php endwhile; ?>
</tbody>
</table>
</div>

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

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