<?php
session_start();
include "includes/layout.php";
include __DIR__ . '/../database/search_log_config.php';

/* Get Top Searched Keywords That Are NOT Sponsored */

$query = "
    SELECT sl.keyword, COUNT(sl.keyword) as total_searches
    FROM search_logs sl
    LEFT JOIN sponsored_keywords sk 
        ON sl.keyword = sk.keyword
    WHERE sk.keyword IS NULL
    GROUP BY sl.keyword
    ORDER BY total_searches DESC
    LIMIT 20
";

$result = $log_conn->query($query);
?>

<h1>Auto Recommend Sponsored Keywords</h1>

<div class="card">
<div class="table-wrapper">

<table class="responsive-table">
<thead>
<tr>
<th>Keyword</th>
<th>Total Searches</th>
<th>Action</th>
</tr>
</thead>

<tbody>

<?php while($row = $result->fetch_assoc()): ?>
<tr>
<td><?= htmlspecialchars($row['keyword']) ?></td>
<td><?= $row['total_searches'] ?></td>
<td>
<a href="add_sponsored.php?keyword=<?= urlencode($row['keyword']) ?>" 
class="btn">
Make Sponsored
</a>
</td>
</tr>
<?php endwhile; ?>

</tbody>
</table>

</div>
</div>

<style>
.table-wrapper{
    overflow-x:auto;
}

.responsive-table{
    width:100%;
    border-collapse:collapse;
    min-width:600px;
}

.responsive-table th,
.responsive-table td{
    padding:12px;
    border-bottom:1px solid #eee;
}
</style>

<?php
$log_conn->close();
include "includes/footer.php";
?>