query("SELECT COUNT(*) as c FROM survey_sites")->fetch_assoc()['c']; $ranked_surveys = $conn->query("SELECT COUNT(*) as c FROM survey_sites WHERE rank IS NOT NULL")->fetch_assoc()['c']; $pending_reviews = $conn->query("SELECT COUNT(*) as c FROM survey_reviews WHERE is_approved = 0")->fetch_assoc()['c']; $total_reviews = $conn->query("SELECT COUNT(*) as c FROM survey_reviews WHERE is_approved = 1")->fetch_assoc()['c']; $new_tickets = $conn->query("SELECT COUNT(*) as c FROM contact_messages WHERE ticket_status = 'new'")->fetch_assoc()['c']; $open_tickets = $conn->query("SELECT COUNT(*) as c FROM contact_messages WHERE ticket_status IN ('new','in_progress')")->fetch_assoc()['c']; // Newsletter subscribers table may not exist yet — handle gracefully $active_subs = 0; $today_subs = 0; $subs_check = $conn->query("SHOW TABLES LIKE 'newsletter_subscribers'"); if ($subs_check && $subs_check->num_rows > 0) { $active_subs = $conn->query("SELECT COUNT(*) as c FROM newsletter_subscribers WHERE is_active = 1")->fetch_assoc()['c']; $today_subs = $conn->query("SELECT COUNT(*) as c FROM newsletter_subscribers WHERE DATE(created_at) = CURDATE()")->fetch_assoc()['c']; } // Recent activity $recent_reviews = $conn->query("SELECT r.reviewer_name, r.rating, r.created_at, s.title FROM survey_reviews r JOIN survey_sites s ON r.survey_id = s.id WHERE r.is_approved = 0 ORDER BY r.created_at DESC LIMIT 5"); $recent_tickets = $conn->query("SELECT id, name, email, ticket_status, created_at FROM contact_messages ORDER BY created_at DESC LIMIT 5"); $page_title = 'Admin Dashboard — PaidSurveyHub'; include 'includes/header.php'; ?>