✏ EDIT: check_notifications.php

<?php
// Endpoint to return new booking/enquiry notifications for admin panel
session_start();
require_once __DIR__ . '/../includes/config.php';

header('Content-Type: application/json');
// Prevent caching of responses to ensure fresh data on each request
header('Cache-Control: no-cache, must-revalidate');
header('Expires: 0');

// Only allow authenticated admin users
if (empty($_SESSION['admin_user'])) {
    echo json_encode(['error' => 'Unauthorized']);
    exit;
}

// Define tables to monitor and their display names/links
$tables = [
    'appointments' => ['title' => 'Appointment', 'url' => 'manage_appointments.php'],
    'service_bookings' => ['title' => 'Service Booking', 'url' => 'manage_service_bookings.php'],
    'parts_bookings' => ['title' => 'Parts Request', 'url' => 'manage_parts_bookings.php'],
    'enquiries' => ['title' => 'Enquiry', 'url' => 'manage_enquiries.php'],
];

$data = ['counts' => [], 'items' => []];

foreach ($tables as $table => $info) {
    // Check if the table exists to avoid errors
    $checkRes = $conn->query("SHOW TABLES LIKE '$table'");
    if ($checkRes && $checkRes->num_rows > 0) {
        // Determine if the table has an is_viewed column; if not, we still count all records as new
        $hasViewCol = false;
        $colRes = $conn->query("SHOW COLUMNS FROM `$table` LIKE 'is_viewed'");
        if ($colRes && $colRes->num_rows > 0) {
            $hasViewCol = true;
        }

// Determine an appropriate display name column for this table.  Some tables may not have a
        // `name` field.  We inspect the column list and select the first available candidate
        // from a list of common field names.  If none match we fall back to the record ID as
        // the name.
        $displayField = 'id';
        $candidateFields = ['name','customer_name','full_name','part_name','service_name','title'];
        $colsRes = $conn->query("SHOW COLUMNS FROM `$table`");
        $availableFields = [];
        if ($colsRes) {
            while ($c = $colsRes->fetch_assoc()) {
                $availableFields[] = $c['Field'];
            }
        }
        foreach ($candidateFields as $cand) {
            if (in_array($cand, $availableFields)) {
                $displayField = $cand;
                break;
            }
        }

// Build the SELECT query.  Filter by is_viewed if the column exists, otherwise return all records.
        if ($hasViewCol) {
            $sql = "SELECT id, `$displayField` as display_name, created_at FROM `$table` WHERE (is_viewed = 0 OR is_viewed IS NULL) ORDER BY created_at DESC";
        } else {
            $sql = "SELECT id, `$displayField` as display_name, created_at FROM `$table` ORDER BY created_at DESC";
        }
        $res = $conn->query($sql);
        $count = 0;
        $items = [];
        if ($res) {
            $count = $res->num_rows;
            $idsToMark = [];
            while ($row = $res->fetch_assoc()) {
                $idsToMark[] = (int)$row['id'];
                $items[] = [
                    'id' => $row['id'],
                    'type' => $info['title'],
                    'name' => $row['display_name'],
                    'created_at' => $row['created_at'],
                    // Link to view this record in its management page
                    'url' => $info['url'] . '?id=' . $row['id']
                ];
            }
            // Mark these notifications as viewed so they don't trigger again if the column exists
            if ($hasViewCol && !empty($idsToMark)) {
                $idsStr = implode(',', array_map('intval', $idsToMark));
                $conn->query("UPDATE `$table` SET is_viewed = 1 WHERE id IN ($idsStr)");
            }
        }
        $data['counts'][$table] = $count;
        // Merge into global items list
        $data['items'] = array_merge($data['items'], $items);
    } else {
        $data['counts'][$table] = 0;
    }
}

echo json_encode($data);
exit;

[✗] CANCEL