summaryrefslogtreecommitdiff
path: root/lib/qna/service.ts
diff options
context:
space:
mode:
Diffstat (limited to 'lib/qna/service.ts')
-rw-r--r--lib/qna/service.ts1006
1 files changed, 1006 insertions, 0 deletions
diff --git a/lib/qna/service.ts b/lib/qna/service.ts
new file mode 100644
index 00000000..d9c877c6
--- /dev/null
+++ b/lib/qna/service.ts
@@ -0,0 +1,1006 @@
+"use server";
+
+import { revalidateTag, unstable_noStore } from "next/cache";
+import { getServerSession } from "next-auth/next";
+import { authOptions } from "@/app/api/auth/[...nextauth]/route";
+import db from "@/db/db";
+import { qna, qnaAnswer, qnaComments } from "@/db/schema/qna";
+import { qnaView, qnaAnswerView, qnaCommentView } from "@/db/schema";
+import {
+ eq,
+ desc,
+ asc,
+ and,
+ or,
+ ilike,
+ inArray,
+ gte,
+ lte,
+ isNull,
+ isNotNull,
+ count,
+ sql
+} from "drizzle-orm";
+import { unstable_cache } from "@/lib/unstable-cache";
+import { filterColumns } from "@/lib/filter-columns";
+import type {
+ GetQnaSchema,
+ CreateQnaSchema,
+ UpdateQnaSchema,
+ CreateAnswerSchema,
+ UpdateAnswerSchema,
+ CreateCommentSchema,
+ UpdateCommentSchema,
+} from "./validation";
+
+/* ================================================================
+ Helper Functions
+================================================================ */
+
+/**
+ * 인증된 사용자 정보 가져오기
+ */
+async function getAuthenticatedUser() {
+ const session = await getServerSession(authOptions);
+ if (!session?.user?.id) {
+ throw new Error("인증이 필요합니다.");
+ }
+ return parseInt(session.user.id);
+}
+
+/**
+ * 현재 사용자 ID 가져오기 (비로그인 허용)
+ */
+async function getCurrentUserId() {
+ try {
+ const session = await getServerSession(authOptions);
+ return session?.user?.id ? parseInt(session.user.id) : null;
+ } catch {
+ return null;
+ }
+}
+
+/* ================================================================
+ Repository Functions (트랜잭션 지원)
+================================================================ */
+
+/**
+ * Q&A 목록 조회 Repository
+ */
+async function selectQnaList(
+ tx: any,
+ options: {
+ where?: any;
+ orderBy?: any[];
+ offset: number;
+ limit: number;
+ }
+) {
+ const { where, orderBy = [desc(qnaView.createdAt)], offset, limit } = options;
+
+ return await tx
+ .select()
+ .from(qnaView)
+ .where(where)
+ .orderBy(...orderBy)
+ .offset(offset)
+ .limit(limit);
+}
+
+/**
+ * Q&A 총 개수 조회 Repository
+ */
+async function countQnaList(tx: any, where?: any) {
+ const [result] = await tx
+ .select({ count: count() })
+ .from(qnaView)
+ .where(where);
+ return result.count;
+}
+
+/**
+ * Q&A 상세 조회 Repository
+ */
+async function selectQnaDetail(tx: any, id: number) {
+ const [question] = await tx
+ .select()
+ .from(qnaView)
+ .where(eq(qnaView.id, id));
+
+ return question || null;
+}
+
+/**
+ * 답변 목록 조회 Repository
+ */
+async function selectAnswersByQnaId(tx: any, qnaId: number) {
+ return await tx
+ .select()
+ .from(qnaAnswerView)
+ .where(eq(qnaAnswerView.qnaId, qnaId))
+ .orderBy(desc(qnaAnswerView.createdAt));
+}
+
+/**
+ * 댓글 목록 조회 Repository (계층형)
+ */
+async function selectCommentsByAnswerId(tx: any, answerId: number) {
+ return await tx
+ .select()
+ .from(qnaCommentView)
+ .where(eq(qnaCommentView.answerId, answerId))
+ .orderBy(asc(qnaCommentView.createdAt)); // 댓글은 오래된 순으로
+}
+
+/**
+ * 내가 답변한 질문 ID 목록 조회
+ */
+async function getMyAnsweredQnaIds(tx: any, userId: number, qnaIds: number[]) {
+ if (qnaIds.length === 0) return [];
+
+ const results = await tx
+ .select({ qnaId: qnaAnswer.qnaId })
+ .from(qnaAnswer)
+ .where(
+ and(
+ eq(qnaAnswer.author, userId),
+ inArray(qnaAnswer.qnaId, qnaIds),
+ eq(qnaAnswer.isDeleted, false)
+ )
+ );
+
+ return results.map(r => r.qnaId);
+}
+
+/* ================================================================
+ 1) Q&A 목록 조회 (고급 필터링/정렬 지원)
+================================================================ */
+
+export async function getQnaList(input: GetQnaSchema) {
+ return unstable_cache(
+ async () => {
+ try {
+ const offset = (input.page - 1) * input.perPage;
+ const advancedTable = input.flags.includes("advancedTable") || true;
+
+ // 현재 사용자 ID (내 답변 여부 확인용)
+ const currentUserId = await getCurrentUserId();
+
+ // 고급 필터링 WHERE 절 구성
+ const advancedWhere = advancedTable
+ ? filterColumns({
+ table: qnaView,
+ filters: input.filters,
+ joinOperator: input.joinOperator,
+ })
+ : undefined;
+
+ // 전역 검색 WHERE 절
+ let globalWhere;
+ if (input.search) {
+ const searchPattern = `%${input.search}%`;
+ globalWhere = or(
+ ilike(qnaView.title, searchPattern),
+ ilike(qnaView.content, searchPattern),
+ ilike(qnaView.authorName, searchPattern),
+ ilike(qnaView.companyName, searchPattern)
+ );
+ }
+
+ // Q&A 특화 필터링
+ const qnaSpecificWhere = and(
+ // 도메인 필터
+ input.authorDomain.length > 0
+ ? inArray(qnaView.authorDomain, input.authorDomain)
+ : undefined,
+
+ // 벤더 타입 필터
+ input.vendorType.length > 0
+ ? inArray(qnaView.vendorType, input.vendorType)
+ : undefined,
+
+ // 답변 유무 필터 (뷰에서 이미 계산된 값 사용)
+ input.hasAnswers === "answered"
+ ? eq(qnaView.hasAnswers, true)
+ : input.hasAnswers === "unanswered"
+ ? eq(qnaView.hasAnswers, false)
+ : undefined,
+
+ // 내 질문만 보기
+ input.myQuestions === "true" && currentUserId
+ ? eq(qnaView.author, currentUserId)
+ : undefined,
+
+ // 날짜 범위 필터
+ );
+
+ // 최종 WHERE 절 결합
+ const finalWhere = and(
+ advancedWhere,
+ globalWhere,
+ qnaSpecificWhere
+ );
+
+ // 정렬 설정
+ const orderBy = input.sort.length > 0
+ ? input.sort.map((item) =>
+ item.desc
+ ? desc(qnaView[item.id as keyof typeof qnaView])
+ : asc(qnaView[item.id as keyof typeof qnaView])
+ )
+ : [desc(qnaView.lastActivityAt), desc(qnaView.createdAt)]; // 최근 활동순으로 기본 정렬
+
+ // 트랜잭션 내에서 데이터 조회
+ const { data, total } = await db.transaction(async (tx) => {
+ const data = await selectQnaList(tx, {
+ where: finalWhere,
+ orderBy,
+ offset,
+ limit: input.perPage,
+ });
+
+ const total = await countQnaList(tx, finalWhere);
+
+ // 내가 답변한 질문들 표시 (로그인한 경우만)
+ let dataWithMyAnswers = data;
+ if (currentUserId && data.length > 0) {
+ const qnaIds = data.map(q => q.id);
+ const myAnsweredIds = await getMyAnsweredQnaIds(tx, currentUserId, qnaIds);
+
+ dataWithMyAnswers = data.map(q => ({
+ ...q,
+ hasMyAnswer: myAnsweredIds.includes(q.id),
+ isMyQuestion: currentUserId === q.author,
+ }));
+ } else {
+ dataWithMyAnswers = data.map(q => ({
+ ...q,
+ hasMyAnswer: false,
+ isMyQuestion: false,
+ }));
+ }
+
+ return { data: dataWithMyAnswers, total };
+ });
+
+ const pageCount = Math.ceil(total / input.perPage);
+
+ return {
+ data,
+ pageCount,
+ total,
+ // 메타 정보 추가
+ meta: {
+ currentPage: input.page,
+ perPage: input.perPage,
+ hasNextPage: input.page < pageCount,
+ hasPrevPage: input.page > 1,
+ }
+ };
+ } catch (err) {
+ console.error("Q&A 목록 조회 실패:", err);
+ return {
+ data: [],
+ pageCount: 0,
+ total: 0,
+ meta: {
+ currentPage: 1,
+ perPage: input.perPage,
+ hasNextPage: false,
+ hasPrevPage: false,
+ }
+ };
+ }
+ },
+ [JSON.stringify(input)], // 캐싱 키
+ {
+ revalidate: 1800, // 30분 캐싱
+ tags: ["qna", "qna-list"],
+ }
+ )();
+}
+
+/* ================================================================
+ 2) Q&A 상세 조회
+================================================================ */
+
+export async function getQnaById(id: number) {
+ return unstable_cache(
+ async () => {
+ try {
+ const currentUserId = await getCurrentUserId();
+
+ return await db.transaction(async (tx) => {
+ // 질문 정보 조회 (뷰 사용으로 단순화)
+ const question = await selectQnaDetail(tx, id);
+ if (!question) return null;
+
+ // 답변 목록 조회 (뷰 사용으로 단순화)
+ const answers = await selectAnswersByQnaId(tx, id);
+
+ // 각 답변의 댓글들 조회 및 계층 구조 생성
+ const answersWithComments = await Promise.all(
+ answers.map(async (answer) => {
+ const comments = await selectCommentsByAnswerId(tx, answer.id);
+
+ // 댓글 계층 구조 생성 (뷰에서 이미 계층 정보 제공)
+ const commentMap = new Map();
+ const rootComments: any[] = [];
+
+ // 먼저 모든 댓글을 Map에 저장
+ comments.forEach(comment => {
+ commentMap.set(comment.id, {
+ ...comment,
+ children: [],
+ isMyComment: currentUserId === comment.author,
+ });
+ });
+
+ // 계층 구조 생성
+ comments.forEach(comment => {
+ const commentWithChildren = commentMap.get(comment.id);
+ if (comment.parentCommentId) {
+ const parent = commentMap.get(comment.parentCommentId);
+ if (parent) {
+ parent.children.push(commentWithChildren);
+ }
+ } else {
+ rootComments.push(commentWithChildren);
+ }
+ });
+
+ return {
+ ...answer,
+ comments: rootComments,
+ isMyAnswer: currentUserId === answer.author,
+ };
+ })
+ );
+
+ return {
+ ...question,
+ answers: answersWithComments,
+ isMyQuestion: currentUserId === question.author,
+ // 뷰에서 이미 계산된 통계 정보 활용
+ totalInteractions: question.totalAnswers + question.totalComments,
+ };
+ });
+ } catch (err) {
+ console.error("Q&A 상세 조회 실패:", err);
+ return null;
+ }
+ },
+ [`qna-detail-${id}`],
+ {
+ revalidate: 1800, // 30분 캐싱
+ tags: ["qna", `qna-${id}`],
+ }
+ )();
+}
+
+/* ================================================================
+ 3) Q&A 생성/수정/삭제 (CRUD 액션들)
+================================================================ */
+
+/**
+ * 새로운 Q&A 질문 생성
+ */
+export async function createQna(input: CreateQnaSchema) {
+ try {
+ const userId = await getAuthenticatedUser();
+
+ const [newQna] = await db.insert(qna).values({
+ title: input.title,
+ content: input.content,
+ category: input.category,
+ author: userId,
+ }).returning();
+
+ revalidateTag("qna");
+ revalidateTag("qna-list");
+
+ return {
+ success: true,
+ data: newQna,
+ message: "질문이 성공적으로 등록되었습니다."
+ };
+ } catch (err) {
+ console.error("질문 생성 실패:", err);
+ return {
+ success: false,
+ error: err instanceof Error ? err.message : "질문 등록에 실패했습니다.",
+ data: null
+ };
+ }
+}
+
+/**
+ * Q&A 질문 수정
+ */
+export async function updateQna(id: number, input: UpdateQnaSchema) {
+ unstable_noStore();
+ try {
+ const userId = await getAuthenticatedUser();
+
+ // 권한 확인
+ const [existing] = await db
+ .select({
+ author: qna.author,
+ title: qna.title
+ })
+ .from(qna)
+ .where(eq(qna.id, id));
+
+ if (!existing) {
+ return { success: false, error: "질문을 찾을 수 없습니다." };
+ }
+
+ if (existing.author !== userId) {
+ return { success: false, error: "수정 권한이 없습니다." };
+ }
+
+ const [updated] = await db
+ .update(qna)
+ .set({
+ ...input,
+ updatedAt: new Date()
+ })
+ .where(eq(qna.id, id))
+ .returning();
+
+ revalidateTag("qna");
+ revalidateTag("qna-list");
+ revalidateTag(`qna-${id}`);
+
+ return {
+ success: true,
+ data: updated,
+ message: "질문이 성공적으로 수정되었습니다."
+ };
+ } catch (err) {
+ console.error("질문 수정 실패:", err);
+ return {
+ success: false,
+ error: err instanceof Error ? err.message : "질문 수정에 실패했습니다."
+ };
+ }
+}
+
+/**
+ * Q&A 질문 삭제 (소프트 삭제)
+ */
+export async function deleteQna(id: number) {
+ unstable_noStore();
+ try {
+ const userId = await getAuthenticatedUser();
+
+ // 권한 확인
+ const [existing] = await db
+ .select({
+ author: qna.author,
+ title: qna.title
+ })
+ .from(qna)
+ .where(eq(qna.id, id));
+
+ if (!existing) {
+ return { success: false, error: "질문을 찾을 수 없습니다." };
+ }
+
+ if (existing.author !== userId) {
+ return { success: false, error: "삭제 권한이 없습니다." };
+ }
+
+ await db
+ .update(qna)
+ .set({
+ isDeleted: true,
+ deletedAt: new Date()
+ })
+ .where(eq(qna.id, id));
+
+ revalidateTag("qna");
+ revalidateTag("qna-list");
+ revalidateTag(`qna-${id}`);
+
+ return {
+ success: true,
+ message: "질문이 성공적으로 삭제되었습니다."
+ };
+ } catch (err) {
+ console.error("질문 삭제 실패:", err);
+ return {
+ success: false,
+ error: err instanceof Error ? err.message : "질문 삭제에 실패했습니다."
+ };
+ }
+}
+
+/* ================================================================
+ 4) 답변 관련 CRUD 액션들
+================================================================ */
+
+/**
+ * 답변 생성
+ */
+export async function createAnswer(input: CreateAnswerSchema) {
+ unstable_noStore();
+ try {
+ const userId = await getAuthenticatedUser();
+
+ // 질문 존재 여부 확인
+ const [questionExists] = await db
+ .select({ id: qna.id })
+ .from(qna)
+ .where(and(
+ eq(qna.id, input.qnaId),
+ eq(qna.isDeleted, false)
+ ));
+
+ if (!questionExists) {
+ return { success: false, error: "존재하지 않는 질문입니다." };
+ }
+
+ const [newAnswer] = await db.insert(qnaAnswer).values({
+ qnaId: input.qnaId,
+ content: input.content,
+ author: userId,
+ }).returning();
+
+ revalidateTag("qna");
+ revalidateTag("qna-list");
+ revalidateTag(`qna-${input.qnaId}`);
+
+ return {
+ success: true,
+ data: newAnswer,
+ message: "답변이 성공적으로 등록되었습니다."
+ };
+ } catch (error) {
+ console.error("답변 생성 실패:", error);
+ return {
+ success: false,
+ error: error instanceof Error ? error.message : "답변 등록에 실패했습니다."
+ };
+ }
+}
+
+/**
+ * 답변 수정
+ */
+export async function updateAnswer(id: number, input: UpdateAnswerSchema) {
+ unstable_noStore();
+ try {
+ const userId = await getAuthenticatedUser();
+
+ // 권한 확인
+ const [existing] = await db
+ .select({
+ author: qnaAnswer.author,
+ qnaId: qnaAnswer.qnaId
+ })
+ .from(qnaAnswer)
+ .where(eq(qnaAnswer.id, id));
+
+ if (!existing) {
+ return { success: false, error: "답변을 찾을 수 없습니다." };
+ }
+
+ if (existing.author !== userId) {
+ return { success: false, error: "수정 권한이 없습니다." };
+ }
+
+ const [updated] = await db
+ .update(qnaAnswer)
+ .set({
+ content: input.content,
+ updatedAt: new Date()
+ })
+ .where(eq(qnaAnswer.id, id))
+ .returning();
+
+ revalidateTag("qna");
+ revalidateTag("qna-list");
+ revalidateTag(`qna-${existing.qnaId}`);
+
+ return {
+ success: true,
+ data: updated,
+ message: "답변이 성공적으로 수정되었습니다."
+ };
+ } catch (err) {
+ console.error("답변 수정 실패:", err);
+ return {
+ success: false,
+ error: err instanceof Error ? err.message : "답변 수정에 실패했습니다."
+ };
+ }
+}
+
+/**
+ * 답변 삭제
+ */
+export async function deleteAnswer(id: number) {
+ unstable_noStore();
+ try {
+ const userId = await getAuthenticatedUser();
+
+ // 권한 확인
+ const [existing] = await db
+ .select({
+ author: qnaAnswer.author,
+ qnaId: qnaAnswer.qnaId
+ })
+ .from(qnaAnswer)
+ .where(eq(qnaAnswer.id, id));
+
+ if (!existing) {
+ return { success: false, error: "답변을 찾을 수 없습니다." };
+ }
+
+ if (existing.author !== userId) {
+ return { success: false, error: "삭제 권한이 없습니다." };
+ }
+
+ // 하드 삭제 (답변은 CASCADE로 댓글도 함께 삭제)
+ await db.delete(qnaAnswer).where(eq(qnaAnswer.id, id));
+
+ revalidateTag("qna");
+ revalidateTag("qna-list");
+ revalidateTag(`qna-${existing.qnaId}`);
+
+ return {
+ success: true,
+ message: "답변이 성공적으로 삭제되었습니다."
+ };
+ } catch (err) {
+ console.error("답변 삭제 실패:", err);
+ return {
+ success: false,
+ error: err instanceof Error ? err.message : "답변 삭제에 실패했습니다."
+ };
+ }
+}
+
+/* ================================================================
+ 5) 댓글 관련 CRUD 액션들
+================================================================ */
+
+/**
+ * 댓글 생성
+ */
+export async function createComment(input: CreateCommentSchema) {
+ unstable_noStore();
+ try {
+ const userId = await getAuthenticatedUser();
+
+ // 답변 존재 여부 확인
+ const [answerExists] = await db
+ .select({
+ id: qnaAnswer.id,
+ qnaId: qnaAnswer.qnaId
+ })
+ .from(qnaAnswer)
+ .where(and(
+ eq(qnaAnswer.id, input.answerId),
+ eq(qnaAnswer.isDeleted, false)
+ ));
+
+ if (!answerExists) {
+ return { success: false, error: "존재하지 않는 답변입니다." };
+ }
+
+ // 부모 댓글 존재 여부 확인 (대댓글인 경우)
+ if (input.parentCommentId) {
+ const [parentExists] = await db
+ .select({ id: qnaComments.id })
+ .from(qnaComments)
+ .where(and(
+ eq(qnaComments.id, input.parentCommentId),
+ eq(qnaComments.answerId, input.answerId),
+ eq(qnaComments.isDeleted, false)
+ ));
+
+ if (!parentExists) {
+ return { success: false, error: "존재하지 않는 부모 댓글입니다." };
+ }
+ }
+
+ const [newComment] = await db.insert(qnaComments).values({
+ ...input,
+ author: userId,
+ }).returning();
+
+ revalidateTag("qna");
+ revalidateTag("qna-list");
+ revalidateTag(`qna-${answerExists.qnaId}`);
+
+ return {
+ success: true,
+ data: newComment,
+ message: "댓글이 성공적으로 등록되었습니다."
+ };
+ } catch (error) {
+ console.error("댓글 작성 실패:", error);
+ return {
+ success: false,
+ error: error instanceof Error ? error.message : "댓글 등록에 실패했습니다."
+ };
+ }
+}
+
+/**
+ * 댓글 수정
+ */
+export async function updateComment(id: number, input: UpdateCommentSchema) {
+ unstable_noStore();
+ try {
+ const userId = await getAuthenticatedUser();
+
+ // 권한 확인 및 질문 ID 가져오기
+ const [existing] = await db
+ .select({
+ author: qnaComments.author,
+ answerId: qnaComments.answerId,
+ qnaId: qnaAnswer.qnaId
+ })
+ .from(qnaComments)
+ .leftJoin(qnaAnswer, eq(qnaComments.answerId, qnaAnswer.id))
+ .where(eq(qnaComments.id, id));
+
+ if (!existing) {
+ return { success: false, error: "댓글을 찾을 수 없습니다." };
+ }
+
+ if (existing.author !== userId) {
+ return { success: false, error: "수정 권한이 없습니다." };
+ }
+
+ const [updated] = await db
+ .update(qnaComments)
+ .set({
+ content: input.content,
+ updatedAt: new Date()
+ })
+ .where(eq(qnaComments.id, id))
+ .returning();
+
+ revalidateTag("qna");
+ revalidateTag("qna-list");
+ revalidateTag(`qna-${existing.qnaId}`);
+
+ return {
+ success: true,
+ data: updated,
+ message: "댓글이 성공적으로 수정되었습니다."
+ };
+ } catch (error) {
+ console.error("댓글 수정 실패:", error);
+ return {
+ success: false,
+ error: error instanceof Error ? error.message : "댓글 수정에 실패했습니다."
+ };
+ }
+}
+
+/**
+ * 댓글 삭제
+ */
+export async function deleteComment(id: number) {
+ unstable_noStore();
+ try {
+ const userId = await getAuthenticatedUser();
+
+ // 권한 확인 및 질문 ID 가져오기
+ const [existing] = await db
+ .select({
+ author: qnaComments.author,
+ answerId: qnaComments.answerId,
+ qnaId: qnaAnswer.qnaId
+ })
+ .from(qnaComments)
+ .leftJoin(qnaAnswer, eq(qnaComments.answerId, qnaAnswer.id))
+ .where(eq(qnaComments.id, id));
+
+ if (!existing) {
+ return { success: false, error: "댓글을 찾을 수 없습니다." };
+ }
+
+ if (existing.author !== userId) {
+ return { success: false, error: "삭제 권한이 없습니다." };
+ }
+
+ // 하드 삭제 (대댓글도 CASCADE로 함께 삭제)
+ await db.delete(qnaComments).where(eq(qnaComments.id, id));
+
+ revalidateTag("qna");
+ revalidateTag("qna-list");
+ revalidateTag(`qna-${existing.qnaId}`);
+
+ return {
+ success: true,
+ message: "댓글이 성공적으로 삭제되었습니다."
+ };
+ } catch (error) {
+ console.error("댓글 삭제 실패:", error);
+ return {
+ success: false,
+ error: error instanceof Error ? error.message : "댓글 삭제에 실패했습니다."
+ };
+ }
+}
+
+/* ================================================================
+ 6) 답변별 댓글 목록 조회
+================================================================ */
+
+export async function getCommentsByAnswerId(answerId: number) {
+ return unstable_cache(
+ async () => {
+ try {
+ const currentUserId = await getCurrentUserId();
+
+ return await db.transaction(async (tx) => {
+ const comments = await selectCommentsByAnswerId(tx, answerId);
+
+ // 댓글 계층 구조 생성
+ const commentMap = new Map();
+ const rootComments: any[] = [];
+
+ // 모든 댓글을 Map에 저장
+ comments.forEach(comment => {
+ commentMap.set(comment.id, {
+ ...comment,
+ children: [],
+ isMyComment: currentUserId === comment.author,
+ });
+ });
+
+ // 계층 구조 생성
+ comments.forEach(comment => {
+ const commentWithChildren = commentMap.get(comment.id);
+ if (comment.parentCommentId) {
+ const parent = commentMap.get(comment.parentCommentId);
+ if (parent) {
+ parent.children.push(commentWithChildren);
+ }
+ } else {
+ rootComments.push(commentWithChildren);
+ }
+ });
+
+ return rootComments;
+ });
+ } catch (error) {
+ console.error("댓글 조회 실패:", error);
+ return [];
+ }
+ },
+ [`comments-${answerId}`],
+ {
+ revalidate: 1800, // 30분 캐싱
+ tags: ["qna", `comments-${answerId}`],
+ }
+ )();
+}
+
+/* ================================================================
+ 7) 통계 및 메타 정보 조회
+================================================================ */
+
+/**
+ * Q&A 대시보드 통계 조회
+ */
+export async function getQnaStats() {
+ return unstable_cache(
+ async () => {
+ try {
+ // 뷰를 사용하여 간단하게 통계 조회
+ const [stats] = await db
+ .select({
+ totalQuestions: count(),
+ answeredQuestions: sql<number>`COUNT(CASE WHEN ${qnaView.hasAnswers} = true THEN 1 END)`,
+ unansweredQuestions: sql<number>`COUNT(CASE WHEN ${qnaView.hasAnswers} = false THEN 1 END)`,
+ popularQuestions: sql<number>`COUNT(CASE WHEN ${qnaView.isPopular} = true THEN 1 END)`,
+ totalAnswers: sql<number>`SUM(${qnaView.totalAnswers})`,
+ totalComments: sql<number>`SUM(${qnaView.totalComments})`,
+ })
+ .from(qnaView);
+
+ // 최근 활동 통계
+ const [recentStats] = await db
+ .select({
+ questionsThisWeek: sql<number>`COUNT(CASE WHEN ${qnaView.createdAt} >= NOW() - INTERVAL '7 days' THEN 1 END)`,
+ questionsThisMonth: sql<number>`COUNT(CASE WHEN ${qnaView.createdAt} >= NOW() - INTERVAL '30 days' THEN 1 END)`,
+ activeQuestionsThisWeek: sql<number>`COUNT(CASE WHEN ${qnaView.lastActivityAt} >= NOW() - INTERVAL '7 days' THEN 1 END)`,
+ })
+ .from(qnaView);
+
+ return {
+ ...stats,
+ ...recentStats,
+ // 추가 계산 통계
+ answerRate: stats.totalQuestions > 0
+ ? Math.round((stats.answeredQuestions / stats.totalQuestions) * 100)
+ : 0,
+ avgAnswersPerQuestion: stats.totalQuestions > 0
+ ? Math.round((stats.totalAnswers || 0) / stats.totalQuestions * 100) / 100
+ : 0,
+ };
+ } catch (err) {
+ console.error("Q&A 통계 조회 실패:", err);
+ return {
+ totalQuestions: 0,
+ answeredQuestions: 0,
+ unansweredQuestions: 0,
+ popularQuestions: 0,
+ totalAnswers: 0,
+ totalComments: 0,
+ questionsThisWeek: 0,
+ questionsThisMonth: 0,
+ activeQuestionsThisWeek: 0,
+ answerRate: 0,
+ avgAnswersPerQuestion: 0,
+ };
+ }
+ },
+ ["qna-stats"],
+ {
+ revalidate: 3600, // 1시간 캐싱
+ tags: ["qna", "qna-stats"],
+ }
+ )();
+}
+
+/**
+ * 사용자별 Q&A 활동 통계
+ */
+export async function getMyQnaActivity() {
+ try {
+ const userId = await getAuthenticatedUser();
+
+ return unstable_cache(
+ async () => {
+ const [myStats] = await db
+ .select({
+ myQuestions: count(),
+ myAnsweredQuestions: sql<number>`COUNT(CASE WHEN ${qnaView.hasAnswers} = true THEN 1 END)`,
+ })
+ .from(qnaView)
+ .where(eq(qnaView.author, userId));
+
+ const [myAnswers] = await db
+ .select({
+ totalAnswers: count(),
+ })
+ .from(qnaAnswerView)
+ .where(eq(qnaAnswerView.author, userId));
+
+ const [myComments] = await db
+ .select({
+ totalComments: count(),
+ })
+ .from(qnaCommentView)
+ .where(eq(qnaCommentView.author, userId));
+
+ return {
+ ...myStats,
+ ...myAnswers,
+ ...myComments,
+ };
+ },
+ [`my-qna-activity-${userId}`],
+ {
+ revalidate: 1800, // 30분 캐싱
+ tags: ["qna", `user-activity-${userId}`],
+ }
+ )();
+ } catch (err) {
+ return {
+ myQuestions: 0,
+ myAnsweredQuestions: 0,
+ totalAnswers: 0,
+ totalComments: 0,
+ };
+ }
+} \ No newline at end of file
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-07 07:42:35 +0000