Server : Apache/2 System : Linux server-15-235-50-60 5.15.0-164-generic #174-Ubuntu SMP Fri Nov 14 20:25:16 UTC 2025 x86_64 User : gositeme ( 1004) PHP Version : 8.2.29 Disable Function : exec,system,passthru,shell_exec,proc_close,proc_open,dl,popen,show_source,posix_kill,posix_mkfifo,posix_getpwuid,posix_setpgid,posix_setsid,posix_setuid,posix_setgid,posix_seteuid,posix_setegid,posix_uname Directory :  /home/gositeme/domains/soundstudiopro.com/public_html/api/

Upload File : current_dir [ Writeable ] document_root [ Writeable ]

<?php
/**
 * Get Notification Count API
 * Returns the total count of unread notifications for the current user
 */

header('Content-Type: application/json');
session_start();
require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/../includes/security.php';

// Check authentication
if (!isset($_SESSION['user_id'])) {
    echo json_encode(['success' => false, 'message' => 'Authentication required']);
    exit;
}

$user_id = $_SESSION['user_id'];
$pdo = getDBConnection();

if (!$pdo) {
    echo json_encode(['success' => false, 'message' => 'Database connection failed']);
    exit;
}

// Create notification_reads table if it doesn't exist
try {
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS notification_reads (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            notification_type ENUM('friend_request', 'like', 'comment', 'artist_rating', 'track_rating', 'follow') NOT NULL,
            notification_id INT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY unique_notification (user_id, notification_type, notification_id),
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
            INDEX idx_user_type (user_id, notification_type)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");
} catch (PDOException $e) {
    // Table might already exist, that's okay
}

// Create notification_clear_state table and fetch last cleared timestamp
$last_cleared_at = null;
try {
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS notification_clear_state (
            user_id INT PRIMARY KEY,
            last_cleared_at TIMESTAMP NULL DEFAULT NULL,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");
    
    $stmt = $pdo->prepare("SELECT last_cleared_at FROM notification_clear_state WHERE user_id = ?");
    $stmt->execute([$user_id]);
    $last_cleared_at = $stmt->fetchColumn() ?: null;
} catch (PDOException $e) {
    error_log("Error preparing notification_clear_state (count): " . $e->getMessage());
}

try {
    // Count unread friend requests (excluding read ones)
    $stmt = $pdo->prepare("
        SELECT COUNT(*) 
        FROM user_friends uf
        LEFT JOIN notification_reads nr ON nr.user_id = ? AND nr.notification_type = 'friend_request' AND nr.notification_id = uf.id
        WHERE uf.friend_id = ? AND uf.status = 'pending' AND nr.id IS NULL
        AND (? IS NULL OR uf.created_at > ?)
    ");
    $stmt->execute([$user_id, $user_id, $last_cleared_at, $last_cleared_at]);
    $friend_requests = (int)$stmt->fetchColumn();
    
    // Count unread likes on user's tracks (excluding user's own likes and read ones)
    $stmt = $pdo->prepare("
        SELECT COUNT(*) 
        FROM track_likes tl 
        JOIN music_tracks mt ON tl.track_id = mt.id 
        LEFT JOIN notification_reads nr ON nr.user_id = ? AND nr.notification_type = 'like' AND nr.notification_id = tl.id
        WHERE mt.user_id = ? AND tl.user_id != ? AND nr.id IS NULL
        AND (? IS NULL OR tl.created_at > ?)
    ");
    $stmt->execute([$user_id, $user_id, $user_id, $last_cleared_at, $last_cleared_at]);
    $likes = (int)$stmt->fetchColumn();
    
    // Count unread comments on user's tracks (excluding user's own comments and read ones)
    $stmt = $pdo->prepare("
        SELECT COUNT(*) 
        FROM track_comments tc 
        JOIN music_tracks mt ON tc.track_id = mt.id 
        LEFT JOIN notification_reads nr ON nr.user_id = ? AND nr.notification_type = 'comment' AND nr.notification_id = tc.id
        WHERE mt.user_id = ? AND tc.user_id != ? AND nr.id IS NULL
        AND (? IS NULL OR tc.created_at > ?)
    ");
    $stmt->execute([$user_id, $user_id, $user_id, $last_cleared_at, $last_cleared_at]);
    $comments = (int)$stmt->fetchColumn();
    
    // Count unread artist ratings
    $stmt = $pdo->prepare("
        SELECT COUNT(*) 
        FROM artist_ratings ar
        LEFT JOIN notification_reads nr ON nr.user_id = ? AND nr.notification_type = 'artist_rating' AND nr.notification_id = ar.id
        WHERE ar.artist_id = ? AND ar.user_id != ? AND nr.id IS NULL
        AND (? IS NULL OR ar.updated_at > ?)
    ");
    $stmt->execute([$user_id, $user_id, $user_id, $last_cleared_at, $last_cleared_at]);
    $artist_ratings = (int)$stmt->fetchColumn();
    
    // Count unread track ratings
    $stmt = $pdo->prepare("
        SELECT COUNT(*) 
        FROM track_ratings tr
        JOIN music_tracks mt ON tr.track_id = mt.id 
        LEFT JOIN notification_reads nr ON nr.user_id = ? AND nr.notification_type = 'track_rating' AND nr.notification_id = tr.id
        WHERE mt.user_id = ? AND tr.user_id != ? AND nr.id IS NULL
        AND (? IS NULL OR tr.updated_at > ?)
    ");
    $stmt->execute([$user_id, $user_id, $user_id, $last_cleared_at, $last_cleared_at]);
    $track_ratings = (int)$stmt->fetchColumn();
    
    // Count unread follows
    $stmt = $pdo->prepare("
        SELECT COUNT(*) 
        FROM user_follows uf
        LEFT JOIN notification_reads nr ON nr.user_id = ? AND nr.notification_type = 'follow' AND nr.notification_id = uf.id
        WHERE uf.following_id = ? AND uf.follower_id != ? AND nr.id IS NULL
        AND (? IS NULL OR uf.created_at > ?)
    ");
    $stmt->execute([$user_id, $user_id, $user_id, $last_cleared_at, $last_cleared_at]);
    $follows = (int)$stmt->fetchColumn();
    
    // Count unread purchase notifications
    $purchase_notifications = 0;
    try {
        require_once __DIR__ . '/../utils/artist_notifications.php';
        $purchase_notifications = getArtistPurchaseNotificationsCount($user_id);
    } catch (Exception $e) {
        error_log("Error counting purchase notifications: " . $e->getMessage());
    }
    
    // Get ticket sale notifications count
    $ticket_notifications = 0;
    try {
        require_once __DIR__ . '/../utils/artist_notifications.php';
        $ticket_notifications = getOrganizerTicketNotificationsCount($user_id);
    } catch (Exception $e) {
        error_log("Error counting ticket notifications: " . $e->getMessage());
    }
    
    $total_count = $friend_requests + $likes + $comments + $artist_ratings + $track_ratings + $follows + $purchase_notifications + $ticket_notifications;
    
    echo json_encode([
        'success' => true,
        'unread_count' => $total_count,
        'breakdown' => [
            'friend_requests' => $friend_requests,
            'likes' => $likes,
            'comments' => $comments,
            'artist_ratings' => $artist_ratings,
            'track_ratings' => $track_ratings,
            'follows' => $follows,
            'purchase_notifications' => $purchase_notifications,
            'ticket_notifications' => $ticket_notifications
        ]
    ]);
} catch (PDOException $e) {
    error_log("Error getting notification count: " . $e->getMessage());
    echo json_encode(['success' => false, 'message' => 'Database error occurred']);
}