댓글 시스템: 중첩 댓글 + 멘션

1. Adjacency List (parent_id)
   ✅ 단순
   ❌ N+1 또는 재귀 쿼리

2. Materialized Path (path 컬럼)
   ✅ 트리 쿼리 빠름
   ❌ depth 제한 권장 (3~5)

3. Nested Set
   ✅ 트리 쿼리 가장 빠름
   ❌ 추가/삭제 비용 큼

CREATE TABLE comments (
  id UUID PRIMARY KEY,
  post_id UUID NOT NULL,
  user_id UUID NOT NULL,
  parent_id UUID REFERENCES comments(id),
  path LTREE NOT NULL,           -- '1.2.5.10' (id 경로)
  depth INT NOT NULL DEFAULT 0,
  content TEXT NOT NULL,
  likes_count INT DEFAULT 0,
  replies_count INT DEFAULT 0,
  status VARCHAR DEFAULT 'published',
  created_at TIMESTAMP DEFAULT NOW(),
  edited_at TIMESTAMP,
  deleted_at TIMESTAMP
);

CREATE INDEX ON comments USING GIST (path);
CREATE INDEX ON comments(post_id, created_at DESC);


-- 트리거로 path 자동
CREATE FUNCTION set_comment_path() RETURNS trigger AS $$
DECLARE
  parent_path LTREE;
BEGIN
  IF NEW.parent_id IS NULL THEN
    NEW.path := NEW.id::text::ltree;
    NEW.depth := 0;
  ELSE
    SELECT path, depth INTO parent_path, NEW.depth FROM comments WHERE id = NEW.parent_id;
    NEW.path := parent_path || NEW.id::text::ltree;
    NEW.depth := NEW.depth + 1;

    IF NEW.depth > 5 THEN
      RAISE EXCEPTION 'Max depth exceeded';
    END IF;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 게시물 모든 댓글 (계층 구조 보존)
SELECT * FROM comments
WHERE post_id = ?
  AND deleted_at IS NULL
ORDER BY path;
-- ltree GiST가 path 정렬 빠름


-- 특정 댓글의 모든 자식 (1단계만)
SELECT * FROM comments
WHERE parent_id = ?
ORDER BY created_at DESC
LIMIT 10;


-- 모든 후손 (깊은 트리)
SELECT * FROM comments
WHERE path <@ (SELECT path FROM comments WHERE id = ?);

async function createComment(userId: string, postId: string, content: string, parentId?: string) {
  // 1. 멘션 추출 (@username)
  const mentions = [...content.matchAll(/@(\w+)/g)].map(m => m[1]);
  const mentionedUsers = await db.user.findMany({
    where: { handle: { in: mentions } },
    select: { id: true, handle: true },
  });

  // 2. 댓글 생성
  const comment = await db.$transaction(async (tx) => {
    const c = await tx.comment.create({
      data: {
        postId, userId, parentId, content,
      },
      include: { user: true },
    });

    // 게시물 댓글 수 증가
    await tx.post.update({
      where: { id: postId },
      data: { commentsCount: { increment: 1 } },
    });

    // 부모 댓글 답글 수 증가
    if (parentId) {
      await tx.comment.update({
        where: { id: parentId },
        data: { repliesCount: { increment: 1 } },
      });
    }

    return c;
  });

  // 3. 알림
  const post = await db.post.findUnique({ where: { id: postId } });

  // 게시물 작성자에게 (자기 자신 제외)
  if (post!.userId !== userId && !mentions.includes(post!.userId)) {
    await notify({
      userId: post!.userId,
      type: 'comment',
      title: '새 댓글',
      body: `${comment.user.name}: ${content.slice(0, 80)}`,
      link: `/posts/${postId}#comment-${comment.id}`,
    });
  }

  // 부모 댓글 작성자에게
  if (parentId) {
    const parent = await db.comment.findUnique({ where: { id: parentId } });
    if (parent && parent.userId !== userId) {
      await notify({
        userId: parent.userId,
        type: 'reply',
        title: '답글',
        body: `${comment.user.name}: ${content.slice(0, 80)}`,
        link: `/posts/${postId}#comment-${comment.id}`,
      });
    }
  }

  // 멘션된 사용자에게
  for (const mu of mentionedUsers) {
    if (mu.id === userId) continue;
    await notify({
      userId: mu.id,
      type: 'mention',
      title: '언급됨',
      body: `${comment.user.name}님이 회원님을 멘션했습니다`,
      link: `/posts/${postId}#comment-${comment.id}`,
    });
  }

  // 4. 실시간 (게시물 페이지에 있는 사용자에게)
  io.to(`post:${postId}`).emit('comment:new', comment);

  return comment;
}

function Comment({ comment, depth = 0 }: { comment: Comment; depth?: number }) {
  const [showReplies, setShowReplies] = useState(depth < 2);
  const [replies, setReplies] = useState<Comment[]>([]);

  const loadReplies = async () => {
    const res = await fetch(`/api/comments/${comment.id}/replies`);
    setReplies(await res.json());
  };

  return (
    <div className={`pl-${depth * 4}`}>
      <div className="flex gap-3">
        <Avatar src={comment.user.avatarUrl} />
        <div className="flex-1">
          <p>
            <strong>{comment.user.name}</strong>
            <time className="text-sm text-gray-500 ml-2">
              {formatDistanceToNow(comment.createdAt)} 전
            </time>
          </p>
          <CommentContent content={comment.content} />
          <div className="flex gap-3 mt-2">
            <LikeButton commentId={comment.id} />
            <button onClick={() => setReplyingTo(comment.id)}>답글</button>
          </div>
        </div>
      </div>

      {comment.repliesCount > 0 && depth < 5 && (
        <>
          {!showReplies && (
            <button onClick={() => { setShowReplies(true); loadReplies(); }}>
              답글 {comment.repliesCount}개 보기
            </button>
          )}
          {showReplies && replies.map(r => (
            <Comment key={r.id} comment={r} depth={depth + 1} />
          ))}
        </>
      )}
    </div>
  );
}

import { useState, useEffect } from 'react';

function CommentInput() {
  const [text, setText] = useState('');
  const [mentionQuery, setMentionQuery] = useState<string | null>(null);
  const [suggestions, setSuggestions] = useState<User[]>([]);

  useEffect(() => {
    if (!mentionQuery) return setSuggestions([]);
    fetch(`/api/users/search?q=${mentionQuery}&limit=5`)
      .then(r => r.json())
      .then(setSuggestions);
  }, [mentionQuery]);

  const handleChange = (e: React.ChangeEvent<HTMLTextAreaElement>) => {
    setText(e.target.value);
    const cursor = e.target.selectionStart;
    const before = e.target.value.slice(0, cursor);
    const match = before.match(/@(\w*)$/);
    setMentionQuery(match?.[1] ?? null);
  };

  const insertMention = (user: User) => {
    const cursor = textareaRef.current!.selectionStart;
    const before = text.slice(0, cursor).replace(/@\w*$/, `@${user.handle} `);
    const after = text.slice(cursor);
    setText(before + after);
    setMentionQuery(null);
  };

  return (
    <div className="relative">
      <textarea ref={textareaRef} value={text} onChange={handleChange} />
      {suggestions.length > 0 && (
        <ul className="absolute bg-white shadow-lg">
          {suggestions.map(u => (
            <li key={u.id} onClick={() => insertMention(u)}>
              <Avatar src={u.avatarUrl} /> @{u.handle}
            </li>
          ))}
        </ul>
      )}
    </div>
  );
}