easystream/f_core/f_classes/class.comments_enhanced.php

<?php
/**
 * Enhanced Comments Manager
 * Threading, reactions, mentions, moderation
 */

class VCommentsEnhanced {
    private $db;
    private $logger;
    
    public function __construct() {
        global $class_database;
        $this->db = $class_database;
        $this->logger = new VLogger('comments');
    }
    
    /**
     * Post a comment with threading support
     */
    public function postComment($video_id, $usr_id, $comment_text, $parent_comment_id = null) {
        $depth = 0;
        
        // Calculate depth if this is a reply
        if ($parent_comment_id) {
            $sql = "SELECT depth FROM db_comments WHERE comment_id = ?";
            $result = $this->db->execute($sql, [$parent_comment_id]);
            
            if ($result && $this->db->rowCount($result) > 0) {
                $row = $this->db->fetch($result);
                $depth = $row['depth'] + 1;
                
                // Limit nesting depth to prevent UI issues
                if ($depth > 5) {
                    $depth = 5;
                }
            }
        }
        
        // Insert comment
        $sql = "INSERT INTO db_comments 
                (video_id, usr_id, parent_comment_id, depth, comment, approved) 
                VALUES (?, ?, ?, ?, ?, 1)";
        
        $this->db->execute($sql, [$video_id, $usr_id, $parent_comment_id, $depth, $comment_text]);
        $comment_id = $this->db->lastInsertId();
        
        // Extract and save mentions
        $this->extractMentions($comment_id, $comment_text);
        
        $this->logger->info("Comment posted: $comment_id by user $usr_id");
        
        return $comment_id;
    }
    
    /**
     * Extract @mentions from comment text
     */
    private function extractMentions($comment_id, $text) {
        preg_match_all('/@(\w+)/', $text, $matches);
        
        if (empty($matches[1])) {
            return;
        }
        
        foreach (array_unique($matches[1]) as $username) {
            // Find user by username
            $sql = "SELECT usr_id FROM db_users WHERE usr_user = ?";
            $result = $this->db->execute($sql, [$username]);
            
            if ($result && $this->db->rowCount($result) > 0) {
                $row = $this->db->fetch($result);
                
                // Save mention
                $sql = "INSERT IGNORE INTO db_comment_mentions (comment_id, mentioned_user_id) 
                        VALUES (?, ?)";
                $this->db->execute($sql, [$comment_id, $row['usr_id']]);
                
                // Trigger notification (integrate with notification system)
                $this->notifyMention($comment_id, $row['usr_id']);
            }
        }
    }
    
    /**
     * Notify user they were mentioned
     */
    private function notifyMention($comment_id, $mentioned_user_id) {
        // Get comment details
        $sql = "SELECT c.video_id, c.usr_id, u.usr_user, v.file_title
                FROM db_comments c
                JOIN db_users u ON c.usr_id = u.usr_id
                JOIN db_videofiles v ON c.video_id = v.video_id
                WHERE c.comment_id = ?";
        
        $result = $this->db->execute($sql, [$comment_id]);
        
        if ($result && $this->db->rowCount($result) > 0) {
            $row = $this->db->fetch($result);
            
            // Create notification (if NotificationManager exists)
            if (class_exists('NotificationManager')) {
                $notif = new NotificationManager();
                $notif->create(
                    $mentioned_user_id,
                    'mention',
                    "{$row['usr_user']} mentioned you in a comment",
                    "on \"{$row['file_title']}\"",
                    "/watch?v={$row['video_id']}#comment-{$comment_id}"
                );
            }
        }
    }
    
    /**
     * Add reaction to comment
     */
    public function addReaction($comment_id, $usr_id, $reaction_type = 'like') {
        $sql = "INSERT INTO db_comment_reactions (comment_id, usr_id, reaction_type) 
                VALUES (?, ?, ?)
                ON DUPLICATE KEY UPDATE reaction_type = VALUES(reaction_type)";
        
        $this->db->execute($sql, [$comment_id, $usr_id, $reaction_type]);
        
        // Update counts
        $this->updateReactionCounts($comment_id);
        
        return true;
    }
    
    /**
     * Remove reaction from comment
     */
    public function removeReaction($comment_id, $usr_id, $reaction_type) {
        $sql = "DELETE FROM db_comment_reactions 
                WHERE comment_id = ? AND usr_id = ? AND reaction_type = ?";
        
        $this->db->execute($sql, [$comment_id, $usr_id, $reaction_type]);
        
        // Update counts
        $this->updateReactionCounts($comment_id);
        
        return true;
    }
    
    /**
     * Update reaction counts cache
     */
    private function updateReactionCounts($comment_id) {
        $sql = "INSERT INTO db_comment_reaction_counts (comment_id, like_count, heart_count, laugh_count, thinking_count, sad_count, angry_count, total_count)
                SELECT 
                    comment_id,
                    SUM(CASE WHEN reaction_type = 'like' THEN 1 ELSE 0 END),
                    SUM(CASE WHEN reaction_type = 'heart' THEN 1 ELSE 0 END),
                    SUM(CASE WHEN reaction_type = 'laugh' THEN 1 ELSE 0 END),
                    SUM(CASE WHEN reaction_type = 'thinking' THEN 1 ELSE 0 END),
                    SUM(CASE WHEN reaction_type = 'sad' THEN 1 ELSE 0 END),
                    SUM(CASE WHEN reaction_type = 'angry' THEN 1 ELSE 0 END),
                    COUNT(*)
                FROM db_comment_reactions
                WHERE comment_id = ?
                GROUP BY comment_id
                ON DUPLICATE KEY UPDATE
                    like_count = VALUES(like_count),
                    heart_count = VALUES(heart_count),
                    laugh_count = VALUES(laugh_count),
                    thinking_count = VALUES(thinking_count),
                    sad_count = VALUES(sad_count),
                    angry_count = VALUES(angry_count),
                    total_count = VALUES(total_count)";
        
        $this->db->execute($sql, [$comment_id]);
    }
    
    /**
     * Get threaded comments for a video
     */
    public function getThreadedComments($video_id, $limit = 50, $offset = 0, $sort = 'top') {
        $order_by = match($sort) {
            'top' => 'COALESCE(rc.total_count, 0) DESC, c.timestamp DESC',
            'newest' => 'c.timestamp DESC',
            'oldest' => 'c.timestamp ASC',
            default => 'c.timestamp DESC'
        };
        
        // Get top-level comments first
        $sql = "SELECT 
                    c.*,
                    u.usr_user,
                    u.avatar_url,
                    u.usr_verified,
                    COALESCE(rc.like_count, 0) as like_count,
                    COALESCE(rc.heart_count, 0) as heart_count,
                    COALESCE(rc.laugh_count, 0) as laugh_count,
                    COALESCE(rc.total_count, 0) as total_reactions,
                    (SELECT COUNT(*) FROM db_comments WHERE parent_comment_id = c.comment_id) as reply_count
                FROM db_comments c
                JOIN db_users u ON c.usr_id = u.usr_id
                LEFT JOIN db_comment_reaction_counts rc ON c.comment_id = rc.comment_id
                WHERE c.video_id = ? 
                    AND c.parent_comment_id IS NULL 
                    AND c.approved = 1
                ORDER BY c.is_pinned DESC, {$order_by}
                LIMIT ? OFFSET ?";
        
        $result = $this->db->execute($sql, [$video_id, $limit, $offset]);
        
        if (!$result) {
            return [];
        }
        
        $comments = [];
        while ($row = $this->db->fetch($result)) {
            $row['replies'] = $this->getReplies($row['comment_id'], 3); // Get first 3 replies
            $comments[] = $row;
        }
        
        return $comments;
    }
    
    /**
     * Get replies to a comment
     */
    public function getReplies($parent_comment_id, $limit = 10) {
        $sql = "SELECT 
                    c.*,
                    u.usr_user,
                    u.avatar_url,
                    u.usr_verified,
                    COALESCE(rc.like_count, 0) as like_count,
                    COALESCE(rc.total_count, 0) as total_reactions
                FROM db_comments c
                JOIN db_users u ON c.usr_id = u.usr_id
                LEFT JOIN db_comment_reaction_counts rc ON c.comment_id = rc.comment_id
                WHERE c.parent_comment_id = ? AND c.approved = 1
                ORDER BY c.timestamp ASC
                LIMIT ?";
        
        $result = $this->db->execute($sql, [$parent_comment_id, $limit]);
        
        if (!$result) {
            return [];
        }
        
        return $this->db->resultsToArray($result);
    }
    
    /**
     * Pin/unpin comment
     */
    public function pinComment($comment_id, $video_owner_id) {
        // Verify ownership
        $sql = "SELECT v.usr_id 
                FROM db_comments c
                JOIN db_videofiles v ON c.video_id = v.video_id
                WHERE c.comment_id = ?";
        
        $result = $this->db->execute($sql, [$comment_id]);
        
        if (!$result || $this->db->rowCount($result) == 0) {
            return false;
        }
        
        $row = $this->db->fetch($result);
        
        if ($row['usr_id'] != $video_owner_id) {
            return false; // Not the video owner
        }
        
        // Unpin all other comments for this video first
        $sql = "UPDATE db_comments c
                JOIN (SELECT video_id FROM db_comments WHERE comment_id = ?) vid
                ON c.video_id = vid.video_id
                SET c.is_pinned = 0";
        $this->db->execute($sql, [$comment_id]);
        
        // Pin this comment
        $sql = "UPDATE db_comments SET is_pinned = 1 WHERE comment_id = ?";
        $this->db->execute($sql, [$comment_id]);
        
        return true;
    }
    
    /**
     * Edit comment
     */
    public function editComment($comment_id, $usr_id, $new_text) {
        // Verify ownership
        $sql = "SELECT usr_id FROM db_comments WHERE comment_id = ?";
        $result = $this->db->execute($sql, [$comment_id]);
        
        if (!$result || $this->db->rowCount($result) == 0) {
            return false;
        }
        
        $row = $this->db->fetch($result);
        
        if ($row['usr_id'] != $usr_id) {
            return false; // Not the comment owner
        }
        
        // Update comment
        $sql = "UPDATE db_comments 
                SET comment = ?, is_edited = 1, edited_at = NOW() 
                WHERE comment_id = ?";
        
        $this->db->execute($sql, [$new_text, $comment_id]);
        
        // Update mentions
        $this->db->execute("DELETE FROM db_comment_mentions WHERE comment_id = ?", [$comment_id]);
        $this->extractMentions($comment_id, $new_text);
        
        return true;
    }
    
    /**
     * Moderate comment
     */
    public function moderateComment($comment_id, $moderator_id, $action, $reason = null) {
        // Log moderation action
        $sql = "INSERT INTO db_comment_moderation (comment_id, moderator_id, action, reason) 
                VALUES (?, ?, ?, ?)";
        
        $this->db->execute($sql, [$comment_id, $moderator_id, $action, $reason]);
        
        // Apply action
        switch ($action) {
            case 'approve':
                $this->db->execute("UPDATE db_comments SET approved = 1 WHERE comment_id = ?", [$comment_id]);
                break;
            
            case 'reject':
                $this->db->execute("UPDATE db_comments SET approved = 0 WHERE comment_id = ?", [$comment_id]);
                break;
            
            case 'delete':
                $this->db->execute("DELETE FROM db_comments WHERE comment_id = ?", [$comment_id]);
                break;
        }
        
        $this->logger->info("Comment $comment_id moderated: $action by moderator $moderator_id");
        
        return true;
    }
}