$id]);
db_exec('UPDATE listings SET published=:p WHERE id=:id', ['p'=>$cur?0:1,'id'=>$id]);
}
if ($do === 'toggle_featured' && $id) {
$cur = (int)db_value('SELECT featured FROM listings WHERE id=:id', ['id'=>$id]);
db_exec('UPDATE listings SET featured=:f WHERE id=:id', ['f'=>$cur?0:1,'id'=>$id]);
}
if ($do === 'delete' && $id) {
// Remove logo file if present
$logo = db_value('SELECT logo_path FROM listings WHERE id=:id', ['id'=>$id]);
if ($logo) {
$path = __DIR__ . '/../' . ltrim($logo, '/');
if (file_exists($path)) @unlink($path);
}
db_exec('DELETE FROM listings WHERE id=:id', ['id'=>$id]);
header('Location: listings.php?msg=deleted'); exit;
}
header('Location: listings.php?'.http_build_query($_GET)); exit;
}
$q = trim($_GET['q'] ?? '');
$tier = $_GET['tier'] ?? '';
$cat = $_GET['cat'] ?? '';
$status = $_GET['status'] ?? '';
$featured = $_GET['featured'] ?? '';
$where = ['1=1'];
$params = [];
if ($q !== '') {
$where[] = '(l.name LIKE :q1 OR l.description LIKE :q2 OR l.address LIKE :q3)';
$params['q1'] = '%'.$q.'%';
$params['q2'] = '%'.$q.'%';
$params['q3'] = '%'.$q.'%';
}
if ($tier && in_array($tier, ['Bronze','Silver','Gold','Platinum','Diamond'], true)) {
$where[] = 'l.tier = :tier'; $params['tier'] = $tier;
}
if ($cat !== '') {
$where[] = 'l.id IN (SELECT listing_id FROM listing_categories WHERE category_slug = :cat)';
$params['cat'] = $cat;
}
if ($status === 'published') $where[] = 'l.published = 1';
if ($status === 'unpublished') $where[] = 'l.published = 0';
if ($status === 'unclaimed') $where[] = 'l.member_id IS NULL';
if ($featured === '1') $where[] = 'l.featured = 1';
$listings = db_all(
"SELECT l.*, m.email AS member_email, m.business_name AS member_biz,
c.name AS category_name,
(SELECT GROUP_CONCAT(c2.name ORDER BY lc.is_primary DESC, c2.name SEPARATOR ', ')
FROM listing_categories lc
JOIN categories c2 ON c2.slug = lc.category_slug
WHERE lc.listing_id = l.id) AS all_categories,
(SELECT COUNT(*) FROM metrics_pageviews
WHERE listing_id=l.id
AND viewed_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)) AS views_30d
FROM listings l
LEFT JOIN members m ON m.id = l.member_id
LEFT JOIN categories c ON c.slug = l.category_slug
WHERE ".implode(' AND ', $where)."
ORDER BY FIELD(l.tier,'Diamond','Platinum','Gold','Silver','Bronze'), l.name
LIMIT 500",
$params
);
$counts = [
'all' => (int)db_value('SELECT COUNT(*) FROM listings'),
'published' => (int)db_value('SELECT COUNT(*) FROM listings WHERE published=1'),
'unpublished' => (int)db_value('SELECT COUNT(*) FROM listings WHERE published=0'),
'unclaimed' => (int)db_value('SELECT COUNT(*) FROM listings WHERE member_id IS NULL'),
'featured' => (int)db_value('SELECT COUNT(*) FROM listings WHERE featured=1'),
];
$categories = db_all('SELECT slug, name FROM categories ORDER BY sort_order, name');
?>