tinker_tickets/models/CommentModel.php

<?php

class CommentModel
{
    private $conn;

    public function __construct($conn)
    {
        $this->conn = $conn;
    }

    /**
     * Extract @mentions from comment text
     *
     * @param string $text Comment text
     * @return array Array of mentioned usernames
     */
    public function extractMentions($text)
    {
        $mentions = [];
        // Match @username patterns (alphanumeric, underscores, hyphens)
        if (preg_match_all('/@([a-zA-Z0-9_-]+)/', $text, $matches)) {
            $mentions = array_unique($matches[1]);
        }
        return $mentions;
    }

    /**
     * Get user IDs for mentioned usernames
     *
     * @param array $usernames Array of usernames
     * @return array Array of user records with user_id, username, display_name
     */
    public function getMentionedUsers($usernames)
    {
        if (empty($usernames)) {
            return [];
        }

        $placeholders = str_repeat('?,', count($usernames) - 1) . '?';
        $sql = "SELECT user_id, username, display_name FROM users WHERE username IN ($placeholders)";
        $stmt = $this->conn->prepare($sql);

        $types = str_repeat('s', count($usernames));
        $stmt->bind_param($types, ...$usernames);
        $stmt->execute();
        $result = $stmt->get_result();

        $users = [];
        while ($row = $result->fetch_assoc()) {
            $users[] = $row;
        }
        $stmt->close();

        return $users;
    }

    /**
     * Get total comment count for a ticket
     */
    public function getCommentCount(int $ticketId): int
    {
        $stmt = $this->conn->prepare(
            "SELECT COUNT(*) as total FROM ticket_comments WHERE ticket_id = ?"
        );
        $stmt->bind_param("i", $ticketId);
        $stmt->execute();
        $row = $stmt->get_result()->fetch_assoc();
        $stmt->close();
        return (int)($row['total'] ?? 0);
    }

    /**
     * @param int $ticketId
     * @param bool $threaded  Build nested reply structure (threading)
     * @param int  $limit     Max root-level comments to return (0 = all)
     * @param int  $offset    Root-level comment offset for pagination
     */
    public function getCommentsByTicketId($ticketId, $threaded = true, int $limit = 0, int $offset = 0)
    {
        $hasThreading = $this->hasThreadingSupport();

        // When paginating with threading we fetch root comments page first,
        // then pull all their replies in a second query.
        if ($hasThreading && $threaded && $limit > 0) {
            return $this->getThreadedCommentsPaged($ticketId, $limit, $offset);
        }

        if ($hasThreading) {
            $sql = "SELECT tc.*, u.display_name, u.username, tc.parent_comment_id, tc.thread_depth
                    FROM ticket_comments tc
                    LEFT JOIN users u ON tc.user_id = u.user_id
                    WHERE tc.ticket_id = ?
                    ORDER BY
                        CASE WHEN tc.parent_comment_id IS NULL THEN tc.created_at END DESC,
                        CASE WHEN tc.parent_comment_id IS NOT NULL THEN tc.created_at END ASC";
        } else {
            $sql = "SELECT tc.*, u.display_name, u.username
                    FROM ticket_comments tc
                    LEFT JOIN users u ON tc.user_id = u.user_id
                    WHERE tc.ticket_id = ?
                    ORDER BY tc.created_at DESC";
        }

        if ($limit > 0) {
            $sql .= " LIMIT ? OFFSET ?";
        }

        $stmt = $this->conn->prepare($sql);
        if ($limit > 0) {
            $stmt->bind_param("iii", $ticketId, $limit, $offset);
        } else {
            $stmt->bind_param("i", $ticketId);
        }
        $stmt->execute();
        $result = $stmt->get_result();

        $commentMap = [];
        while ($row = $result->fetch_assoc()) {
            if (!empty($row['display_name'])) {
                $row['display_name_formatted'] = $row['display_name'];
            } else {
                $row['display_name_formatted'] = $row['user_name'] ?? 'Unknown User';
            }
            $row['replies'] = [];
            $row['parent_comment_id'] = $row['parent_comment_id'] ?? null;
            $row['thread_depth'] = $row['thread_depth'] ?? 0;
            $commentMap[$row['comment_id']] = $row;
        }
        $stmt->close();

        // Build threaded structure if threading is enabled (no pagination — all loaded)
        if ($hasThreading && $threaded) {
            $rootComments = [];
            foreach ($commentMap as $id => $comment) {
                if ($comment['parent_comment_id'] === null) {
                    $rootComments[] = $this->buildCommentThread($comment, $commentMap);
                }
            }
            return $rootComments;
        }

        return array_values($commentMap);
    }

    /**
     * Paginated threaded comments: fetch one page of root comments + all their replies.
     */
    private function getThreadedCommentsPaged(int $ticketId, int $limit, int $offset): array
    {
        // Page of root comments
        $rootSql = "SELECT tc.*, u.display_name, u.username
                    FROM ticket_comments tc
                    LEFT JOIN users u ON tc.user_id = u.user_id
                    WHERE tc.ticket_id = ? AND tc.parent_comment_id IS NULL
                    ORDER BY tc.created_at DESC
                    LIMIT ? OFFSET ?";
        $stmt = $this->conn->prepare($rootSql);
        $stmt->bind_param("iii", $ticketId, $limit, $offset);
        $stmt->execute();
        $rootResult = $stmt->get_result();
        $stmt->close();

        $commentMap = [];
        $rootIds    = [];
        while ($row = $rootResult->fetch_assoc()) {
            $row['display_name_formatted'] = $row['display_name'] ?: ($row['user_name'] ?? 'Unknown User');
            $row['replies']           = [];
            $row['parent_comment_id'] = null;
            $row['thread_depth']      = 0;
            $commentMap[$row['comment_id']] = $row;
            $rootIds[] = $row['comment_id'];
        }

        if (empty($rootIds)) {
            return [];
        }

        // All replies for these root comments (up to 3 levels deep)
        $placeholders = implode(',', array_fill(0, count($rootIds), '?'));
        $replySql = "SELECT tc.*, u.display_name, u.username
                     FROM ticket_comments tc
                     LEFT JOIN users u ON tc.user_id = u.user_id
                     WHERE tc.ticket_id = ?
                       AND tc.parent_comment_id IN ($placeholders)
                       AND tc.parent_comment_id IS NOT NULL
                     ORDER BY tc.created_at ASC";
        $replyStmt = $this->conn->prepare($replySql);
        $types = 'i' . str_repeat('i', count($rootIds));
        $replyStmt->bind_param($types, $ticketId, ...$rootIds);
        $replyStmt->execute();
        $replyResult = $replyStmt->get_result();
        $replyStmt->close();

        while ($row = $replyResult->fetch_assoc()) {
            $row['display_name_formatted'] = $row['display_name'] ?: ($row['user_name'] ?? 'Unknown User');
            $row['replies']      = [];
            $row['thread_depth'] = $row['thread_depth'] ?? 1;
            $commentMap[$row['comment_id']] = $row;
        }

        $rootComments = [];
        foreach ($rootIds as $rid) {
            if (isset($commentMap[$rid])) {
                $rootComments[] = $this->buildCommentThread($commentMap[$rid], $commentMap);
            }
        }
        return $rootComments;
    }

    /**
     * Check if threading columns exist
     */
    private function hasThreadingSupport()
    {
        static $hasSupport = null;
        if ($hasSupport !== null) {
            return $hasSupport;
        }

        $result = $this->conn->query("SHOW COLUMNS FROM ticket_comments LIKE 'parent_comment_id'");
        $hasSupport = ($result && $result->num_rows > 0);
        return $hasSupport;
    }

    /**
     * Recursively build comment thread
     */
    private function buildCommentThread($comment, &$allComments)
    {
        $comment['replies'] = [];
        foreach ($allComments as $c) {
            if (
                (int)$c['parent_comment_id'] === (int)$comment['comment_id']
                && isset($allComments[$c['comment_id']])
            ) {
                $comment['replies'][] = $this->buildCommentThread($c, $allComments);
            }
        }
        // Sort replies by date ascending
        usort($comment['replies'], function ($a, $b) {
            return strtotime($a['created_at']) - strtotime($b['created_at']);
        });
        return $comment;
    }

    /**
     * Get flat list of comments (for backward compatibility)
     */
    public function getCommentsByTicketIdFlat($ticketId)
    {
        return $this->getCommentsByTicketId($ticketId, false);
    }

    public function addComment($ticketId, $commentData, $userId = null)
    {
        // Check if threading is supported
        $hasThreading = $this->hasThreadingSupport();

        // Set default username (kept for backward compatibility)
        $username = $commentData['user_name'] ?? 'User';
        $markdownEnabled = isset($commentData['markdown_enabled']) && $commentData['markdown_enabled'] ? 1 : 0;
        $commentText = $commentData['comment_text'];
        $parentCommentId = $commentData['parent_comment_id'] ?? null;
        $threadDepth = 0;

        // Calculate thread depth if replying to a comment
        if ($hasThreading && $parentCommentId) {
            $parentComment = $this->getCommentById($parentCommentId);
            if ($parentComment) {
                $threadDepth = min(($parentComment['thread_depth'] ?? 0) + 1, 3); // Max depth of 3
            }
        }

        if ($hasThreading) {
            $sql = "INSERT INTO ticket_comments (ticket_id, user_id, user_name, comment_text, markdown_enabled, parent_comment_id, thread_depth)
                    VALUES (?, ?, ?, ?, ?, ?, ?)";
            $stmt = $this->conn->prepare($sql);
            $stmt->bind_param(
                "sissiii",
                $ticketId,
                $userId,
                $username,
                $commentText,
                $markdownEnabled,
                $parentCommentId,
                $threadDepth
            );
        } else {
            $sql = "INSERT INTO ticket_comments (ticket_id, user_id, user_name, comment_text, markdown_enabled)
                    VALUES (?, ?, ?, ?, ?)";
            $stmt = $this->conn->prepare($sql);
            $stmt->bind_param(
                "sissi",
                $ticketId,
                $userId,
                $username,
                $commentText,
                $markdownEnabled
            );
        }

        if ($stmt->execute()) {
            $commentId = $this->conn->insert_id;

            return [
                'success' => true,
                'comment_id' => $commentId,
                'user_name' => $username,
                'created_at' => date('M d, Y H:i'),
                'markdown_enabled' => $markdownEnabled,
                'comment_text' => $commentText,
                'parent_comment_id' => $parentCommentId,
                'thread_depth' => $threadDepth
            ];
        } else {
            return [
                'success' => false,
                'error' => $this->conn->error
            ];
        }
    }

    /**
     * Get a single comment by ID
     */
    public function getCommentById($commentId)
    {
        $sql = "SELECT tc.*, u.display_name, u.username
                FROM ticket_comments tc
                LEFT JOIN users u ON tc.user_id = u.user_id
                WHERE tc.comment_id = ?";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("i", $commentId);
        $stmt->execute();
        $result = $stmt->get_result();
        return $result->fetch_assoc();
    }

    /**
     * Update an existing comment
     * Only the comment owner or an admin can update
     */
    public function updateComment($commentId, $commentText, $markdownEnabled, $userId, $isAdmin = false)
    {
        // First check if user owns this comment or is admin
        $comment = $this->getCommentById($commentId);

        if (!$comment) {
            return ['success' => false, 'error' => 'Comment not found'];
        }

        if ($comment['user_id'] !== (int)$userId && !$isAdmin) {
            return ['success' => false, 'error' => 'You do not have permission to edit this comment'];
        }

        // Check if updated_at column exists
        $hasUpdatedAt = false;
        $colCheck = $this->conn->query("SHOW COLUMNS FROM ticket_comments LIKE 'updated_at'");
        if ($colCheck && $colCheck->num_rows > 0) {
            $hasUpdatedAt = true;
        }

        if ($hasUpdatedAt) {
            $sql = "UPDATE ticket_comments SET comment_text = ?, markdown_enabled = ?, updated_at = NOW() WHERE comment_id = ?";
        } else {
            $sql = "UPDATE ticket_comments SET comment_text = ?, markdown_enabled = ? WHERE comment_id = ?";
        }

        $stmt = $this->conn->prepare($sql);
        $markdownInt = $markdownEnabled ? 1 : 0;
        $stmt->bind_param("sii", $commentText, $markdownInt, $commentId);

        if ($stmt->execute()) {
            return [
                'success' => true,
                'comment_id' => $commentId,
                'comment_text' => $commentText,
                'markdown_enabled' => $markdownInt,
                'updated_at' => $hasUpdatedAt ? date('M d, Y H:i') : null
            ];
        } else {
            return ['success' => false, 'error' => $this->conn->error];
        }
    }

    /**
     * Delete a comment
     * Only the comment owner or an admin can delete
     */
    public function deleteComment($commentId, $userId, $isAdmin = false)
    {
        // First check if user owns this comment or is admin
        $comment = $this->getCommentById($commentId);

        if (!$comment) {
            return ['success' => false, 'error' => 'Comment not found'];
        }

        if ($comment['user_id'] !== (int)$userId && !$isAdmin) {
            return ['success' => false, 'error' => 'You do not have permission to delete this comment'];
        }

        $ticketId = $comment['ticket_id'];

        $sql = "DELETE FROM ticket_comments WHERE comment_id = ?";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("i", $commentId);

        if ($stmt->execute()) {
            return [
                'success' => true,
                'comment_id' => $commentId,
                'ticket_id' => $ticketId
            ];
        } else {
            return ['success' => false, 'error' => $this->conn->error];
        }
    }
}