diff options
Diffstat (limited to 'db/schema/qna.ts')
| -rw-r--r-- | db/schema/qna.ts | 583 |
1 files changed, 583 insertions, 0 deletions
diff --git a/db/schema/qna.ts b/db/schema/qna.ts new file mode 100644 index 00000000..1efe25ee --- /dev/null +++ b/db/schema/qna.ts @@ -0,0 +1,583 @@ +import { + pgTable, + varchar, + text, + timestamp, + integer, + boolean,pgEnum, + index, serial, pgView +} from "drizzle-orm/pg-core"; +import { relations, sql, eq } from "drizzle-orm"; +import { users } from "./users"; +import { vendors } from "./vendors"; +import { techVendors } from "./techVendors"; + + +export const qnaCategoryEnum = pgEnum('qna_category', [ + 'engineering', // 설계 + 'procurement', // 구매 + 'technical_sales' // 기술영업 +]); + + +export const QNA_CATEGORY_LABELS =[ + { label: '설계', value: 'engineering' }, + { label: '구매', value: 'procurement' }, + { label: '기술영업', value: 'technical_sales' }, + ]; + + +export const qna = pgTable( + "qna", + { + id: serial("id").primaryKey(), + + title: varchar("title", { length: 255 }).notNull(), + content: text("content").notNull(), + category: qnaCategoryEnum("category").notNull(), + + /* 작성자 – 원본 유저 삭제 시 질문도 같이 제거(CASCADE) */ + author: integer("author") + .references(() => users.id, { + onDelete: "cascade", + }) + .notNull(), + + createdAt: timestamp("created_at").defaultNow().notNull(), + updatedAt: timestamp("updated_at").defaultNow().notNull(), + + /* 소프트 삭제 */ + isDeleted: boolean("is_deleted").default(false).notNull(), + deletedAt: timestamp("deleted_at"), + }, + (t) => ({ + idxAuthor: index("idx_qna_author").on(t.author), + }) +); + +export const qnaAnswer = pgTable( + "qna_answer", + { + id: serial("id").primaryKey(), + + /* FK → 질문 (수정: varchar → integer) */ + qnaId: integer("qna_id") + .references(() => qna.id, { + onDelete: "cascade", + }) + .notNull(), + + content: text("content").notNull(), + + /* 작성자 – 삭제 시 답변도 같이 제거 */ + author: integer("author") + .references(() => users.id, { + onDelete: "cascade", + }) + .notNull(), + + createdAt: timestamp("created_at").defaultNow().notNull(), + updatedAt: timestamp("updated_at").defaultNow().notNull(), + + /* 소프트 삭제 */ + isDeleted: boolean("is_deleted").default(false).notNull(), + deletedAt: timestamp("deleted_at"), + }, + (t) => ({ + idxQna: index("idx_answer_qna").on(t.qnaId), + idxAuthor: index("idx_answer_author").on(t.author), + }) +); + +export const qnaComments = pgTable( + "qna_comments", + { + id: serial("id").primaryKey(), + + content: text("content").notNull(), + + /* 작성자 */ + author: integer("author") + .references(() => users.id, { + onDelete: "cascade", + }) + .notNull(), + + /* FK → 답변 (수정: varchar → integer) */ + answerId: integer("answer_id") + .references(() => qnaAnswer.id, { + onDelete: "cascade", + }) + .notNull(), + + /* 대댓글용 self-join (nullable) - 순환참조 해결을 위해 reference 제거 */ + parentCommentId: integer("parent_comment_id"), + + createdAt: timestamp("created_at").defaultNow().notNull(), + updatedAt: timestamp("updated_at").defaultNow().notNull(), + + /* 소프트 삭제 */ + isDeleted: boolean("is_deleted").default(false).notNull(), + deletedAt: timestamp("deleted_at"), + }, + (t) => ({ + idxAnswer: index("idx_comment_answer").on(t.answerId), + idxParent: index("idx_comment_parent").on(t.parentCommentId), + }) +); + + +export const qnaRelations = relations(qna, ({ many }) => ({ + answers: many(qnaAnswer), +})); + +export const qnaAnswerRelations = relations(qnaAnswer, ({ one, many }) => ({ + question: one(qna, { + fields: [qnaAnswer.qnaId], + references: [qna.id], + }), + comments: many(qnaComments), +})); + +export const qnaCommentRelations = relations(qnaComments, ({ one, many }) => ({ + answer: one(qnaAnswer, { + fields: [qnaComments.answerId], + references: [qnaAnswer.id], + }), + // self-reference relations - 순환참조 해결을 위해 별도 처리 + parent: one(qnaComments, { + fields: [qnaComments.parentCommentId], + references: [qnaComments.id], + relationName: "parentComment" + }), + children: many(qnaComments, { + relationName: "parentComment" + }), +})); + + + +export type Qna = typeof qna.$inferSelect; +export type NewQna = typeof qna.$inferInsert; + +export type QnaAnswer = typeof qnaAnswer.$inferSelect; +export type NewQnaAnswer = typeof qnaAnswer.$inferInsert; + +export type QnaComment = typeof qnaComments.$inferSelect; +export type NewQnaComment = typeof qnaComments.$inferInsert; + + +export const qnaView = pgView('qna_view').as((qb) => + qb + .select({ + // ═══════════════════════════════════════════════════════════════ + // Q&A 기본 정보 + // ═══════════════════════════════════════════════════════════════ + id: qna.id, + title: qna.title, + content: qna.content, + author: qna.author, + category: qna.category, + + // 생성/수정/삭제 정보 + createdAt: qna.createdAt, + updatedAt: qna.updatedAt, + isDeleted: qna.isDeleted, + deletedAt: qna.deletedAt, + + // ═══════════════════════════════════════════════════════════════ + // 작성자 정보 (users 조인) - 모든 컬럼에 author 접두사 + // ═══════════════════════════════════════════════════════════════ + authorName: users.name, + authorEmail: users.email, + authorDomain: users.domain, + authorPhone: users.phone, + authorImageUrl: users.imageUrl, + authorLanguage: users.language, + authorIsActive: users.isActive, + authorLastLoginAt: users.lastLoginAt, + + // ═══════════════════════════════════════════════════════════════ + // 회사 정보 (vendors/techVendors 조인) - 명시적 별칭 사용 + // ═══════════════════════════════════════════════════════════════ + // 일반 벤더 정보 + vendorName: sql<string>`${vendors.vendorName}`.as('vendor_name'), + vendorCode: sql<string>`${vendors.vendorCode}`.as('vendor_code'), + vendorStatus: sql<string>`${vendors.status}`.as('vendor_status'), + vendorCountry: sql<string>`${vendors.country}`.as('vendor_country'), + vendorBusinessSize: sql<string>`${vendors.businessSize}`.as('vendor_business_size'), + + // 기술 벤더 정보 + techVendorName: sql<string>`${techVendors.vendorName}`.as('tech_vendor_name'), + techVendorCode: sql<string>`${techVendors.vendorCode}`.as('tech_vendor_code'), + techVendorStatus: sql<string>`${techVendors.status}`.as('tech_vendor_status'), + techVendorCountry: sql<string>`${techVendors.country}`.as('tech_vendor_country'), + techVendorType: sql<string>`${techVendors.techVendorType}`.as('tech_vendor_type'), + + // 통합 회사 정보 (COALESCE 사용) + companyName: sql<string>`COALESCE(${vendors.vendorName}, ${techVendors.vendorName})`.as('company_name'), + companyCode: sql<string>`COALESCE(${vendors.vendorCode}, ${techVendors.vendorCode})`.as('company_code'), + companyCountry: sql<string>`COALESCE(${vendors.country}, ${techVendors.country})`.as('company_country'), + vendorType: sql<string>` + CASE + WHEN ${vendors.vendorName} IS NOT NULL THEN 'vendor' + WHEN ${techVendors.vendorName} IS NOT NULL THEN 'techVendor' + ELSE NULL + END + `.as('vendor_type'), + + // ═══════════════════════════════════════════════════════════════ + // 답변 관련 통계 (서브쿼리로 계산) + // ═══════════════════════════════════════════════════════════════ + totalAnswers: sql<number>`( + SELECT COUNT(*)::int + FROM ${qnaAnswer} qa + WHERE qa.qna_id = ${qna.id} + AND qa.is_deleted = false + )`.as('total_answers'), + + answerCount: sql<number>`( + SELECT COUNT(*)::int + FROM ${qnaAnswer} qa + WHERE qa.qna_id = ${qna.id} + AND qa.is_deleted = false + )`.as('answer_count'), + + lastAnsweredAt: sql<Date>`( + SELECT MAX(qa.created_at) + FROM ${qnaAnswer} qa + WHERE qa.qna_id = ${qna.id} + AND qa.is_deleted = false + )`.as('last_answered_at'), + + firstAnsweredAt: sql<Date>`( + SELECT MIN(qa.created_at) + FROM ${qnaAnswer} qa + WHERE qa.qna_id = ${qna.id} + AND qa.is_deleted = false + )`.as('first_answered_at'), + + // ═══════════════════════════════════════════════════════════════ + // 댓글 관련 통계 (전체 댓글 수) + // ═══════════════════════════════════════════════════════════════ + totalComments: sql<number>`( + SELECT COUNT(*)::int + FROM ${qnaComments} qc + INNER JOIN ${qnaAnswer} qa ON qc.answer_id = qa.id + WHERE qa.qna_id = ${qna.id} + AND qc.is_deleted = false + AND qa.is_deleted = false + )`.as('total_comments'), + + // ═══════════════════════════════════════════════════════════════ + // 최근 활동 정보 + // ═══════════════════════════════════════════════════════════════ + lastActivityAt: sql<Date>`( + SELECT GREATEST( + ${qna.updatedAt}, + COALESCE(( + SELECT MAX(qa.updated_at) + FROM ${qnaAnswer} qa + WHERE qa.qna_id = ${qna.id} + AND qa.is_deleted = false + ), ${qna.updatedAt}), + COALESCE(( + SELECT MAX(qc.updated_at) + FROM ${qnaComments} qc + INNER JOIN ${qnaAnswer} qa ON qc.answer_id = qa.id + WHERE qa.qna_id = ${qna.id} + AND qc.is_deleted = false + AND qa.is_deleted = false + ), ${qna.updatedAt}) + ) + )`.as('last_activity_at'), + + // ═══════════════════════════════════════════════════════════════ + // 질문 상태 분류 + // ═══════════════════════════════════════════════════════════════ + hasAnswers: sql<boolean>`( + SELECT COUNT(*) > 0 + FROM ${qnaAnswer} qa + WHERE qa.qna_id = ${qna.id} + AND qa.is_deleted = false + )`.as('has_answers'), + + isAnswered: sql<boolean>`( + SELECT COUNT(*) > 0 + FROM ${qnaAnswer} qa + WHERE qa.qna_id = ${qna.id} + AND qa.is_deleted = false + )`.as('is_answered'), + + isPopular: sql<boolean>`( + (SELECT COUNT(*) FROM ${qnaAnswer} qa WHERE qa.qna_id = ${qna.id} AND qa.is_deleted = false) >= 3 + OR + (SELECT COUNT(*) FROM ${qnaComments} qc + INNER JOIN ${qnaAnswer} qa ON qc.answer_id = qa.id + WHERE qa.qna_id = ${qna.id} AND qc.is_deleted = false AND qa.is_deleted = false) >= 5 + )`.as('is_popular'), + }) + .from(qna) + .leftJoin(users, eq(qna.author, users.id)) + .leftJoin(vendors, eq(users.companyId, vendors.id)) + .leftJoin(techVendors, eq(users.techCompanyId, techVendors.id)) + .where(eq(qna.isDeleted, false)) + .orderBy(qna.createdAt) + ); + +/* ================================================================ + Q&A 답변 뷰 (답변 목록용) - 컬럼명 충돌 해결 +================================================================ */ +export const qnaAnswerView = pgView('qna_answer_view').as((qb) => + qb + .select({ + // ═══════════════════════════════════════════════════════════════ + // 답변 기본 정보 + // ═══════════════════════════════════════════════════════════════ + id: qnaAnswer.id, + qnaId: qnaAnswer.qnaId, + content: qnaAnswer.content, + author: qnaAnswer.author, + + // 생성/수정/삭제 정보 - 명시적 별칭 + createdAt: sql<Date>`${qnaAnswer.createdAt}`.as('created_at'), + updatedAt: sql<Date>`${qnaAnswer.updatedAt}`.as('updated_at'), + isDeleted: sql<boolean>`${qnaAnswer.isDeleted}`.as('is_deleted'), + deletedAt: sql<Date>`${qnaAnswer.deletedAt}`.as('deleted_at'), + + // ═══════════════════════════════════════════════════════════════ + // 질문 기본 정보 (조인으로 가져와서 컨텍스트 제공) - question 접두사 + // ═══════════════════════════════════════════════════════════════ + questionTitle: sql<string>`${qna.title}`.as('question_title'), + questionCategory: sql<string>`${qna.category}`.as('question_category'), + questionAuthor: sql<number>`${qna.author}`.as('question_author'), + questionCreatedAt: sql<Date>`${qna.createdAt}`.as('question_created_at'), + + // ═══════════════════════════════════════════════════════════════ + // 답변 작성자 정보 (users 조인) - author 접두사 + // ═══════════════════════════════════════════════════════════════ + authorName: sql<string>`${users.name}`.as('author_name'), + authorEmail: sql<string>`${users.email}`.as('author_email'), + authorDomain: sql<string>`${users.domain}`.as('author_domain'), + authorPhone: sql<string>`${users.phone}`.as('author_phone'), + authorImageUrl: sql<string>`${users.imageUrl}`.as('author_image_url'), + authorLanguage: sql<string>`${users.language}`.as('author_language'), + + // ═══════════════════════════════════════════════════════════════ + // 회사 정보 (vendors/techVendors 조인) - 명시적 별칭 + // ═══════════════════════════════════════════════════════════════ + vendorName: sql<string>`${vendors.vendorName}`.as('vendor_name'), + vendorCode: sql<string>`${vendors.vendorCode}`.as('vendor_code'), + techVendorName: sql<string>`${techVendors.vendorName}`.as('tech_vendor_name'), + techVendorCode: sql<string>`${techVendors.vendorCode}`.as('tech_vendor_code'), + + // 통합 회사 정보 + companyName: sql<string>`COALESCE(${vendors.vendorName}, ${techVendors.vendorName})`.as('company_name'), + companyCode: sql<string>`COALESCE(${vendors.vendorCode}, ${techVendors.vendorCode})`.as('company_code'), + vendorType: sql<string>` + CASE + WHEN ${vendors.vendorName} IS NOT NULL THEN 'vendor' + WHEN ${techVendors.vendorName} IS NOT NULL THEN 'techVendor' + ELSE NULL + END + `.as('vendor_type'), + + // ═══════════════════════════════════════════════════════════════ + // 댓글 관련 통계 (서브쿼리로 계산) + // ═══════════════════════════════════════════════════════════════ + totalComments: sql<number>`( + SELECT COUNT(*)::int + FROM ${qnaComments} qc + WHERE qc.answer_id = ${qnaAnswer.id} + AND qc.is_deleted = false + )`.as('total_comments'), + + commentCount: sql<number>`( + SELECT COUNT(*)::int + FROM ${qnaComments} qc + WHERE qc.answer_id = ${qnaAnswer.id} + AND qc.is_deleted = false + )`.as('comment_count'), + + parentCommentsCount: sql<number>`( + SELECT COUNT(*)::int + FROM ${qnaComments} qc + WHERE qc.answer_id = ${qnaAnswer.id} + AND qc.parent_comment_id IS NULL + AND qc.is_deleted = false + )`.as('parent_comments_count'), + + childCommentsCount: sql<number>`( + SELECT COUNT(*)::int + FROM ${qnaComments} qc + WHERE qc.answer_id = ${qnaAnswer.id} + AND qc.parent_comment_id IS NOT NULL + AND qc.is_deleted = false + )`.as('child_comments_count'), + + lastCommentedAt: sql<Date>`( + SELECT MAX(qc.created_at) + FROM ${qnaComments} qc + WHERE qc.answer_id = ${qnaAnswer.id} + AND qc.is_deleted = false + )`.as('last_commented_at'), + + // ═══════════════════════════════════════════════════════════════ + // 답변 순서 정보 + // ═══════════════════════════════════════════════════════════════ + answerOrder: sql<number>`( + SELECT ROW_NUMBER() OVER ( + PARTITION BY qa2.qna_id + ORDER BY qa2.created_at ASC + ) + FROM ${qnaAnswer} qa2 + WHERE qa2.id = ${qnaAnswer.id} + AND qa2.is_deleted = false + )`.as('answer_order'), + + isFirstAnswer: sql<boolean>`( + ${qnaAnswer.id} = ( + SELECT qa2.id + FROM ${qnaAnswer} qa2 + WHERE qa2.qna_id = ${qnaAnswer.qnaId} + AND qa2.is_deleted = false + ORDER BY qa2.created_at ASC + LIMIT 1 + ) + )`.as('is_first_answer'), + + isLatestAnswer: sql<boolean>`( + ${qnaAnswer.id} = ( + SELECT qa2.id + FROM ${qnaAnswer} qa2 + WHERE qa2.qna_id = ${qnaAnswer.qnaId} + AND qa2.is_deleted = false + ORDER BY qa2.created_at DESC + LIMIT 1 + ) + )`.as('is_latest_answer'), + }) + .from(qnaAnswer) + .leftJoin(qna, eq(qnaAnswer.qnaId, qna.id)) + .leftJoin(users, eq(qnaAnswer.author, users.id)) + .leftJoin(vendors, eq(users.companyId, vendors.id)) + .leftJoin(techVendors, eq(users.techCompanyId, techVendors.id)) + .where(eq(qnaAnswer.isDeleted, false)) + .orderBy(qnaAnswer.createdAt) +); + +/* ================================================================ + Q&A 댓글 뷰 (댓글 목록용) - 컬럼명 충돌 해결 +================================================================ */ +export const qnaCommentView = pgView('qna_comment_view').as((qb) => + qb + .select({ + // ═══════════════════════════════════════════════════════════════ + // 댓글 기본 정보 + // ═══════════════════════════════════════════════════════════════ + id: qnaComments.id, + content: qnaComments.content, + author: qnaComments.author, + answerId: qnaComments.answerId, + parentCommentId: qnaComments.parentCommentId, + + // 생성/수정/삭제 정보 - 명시적 별칭 + createdAt: sql<Date>`${qnaComments.createdAt}`.as('created_at'), + updatedAt: sql<Date>`${qnaComments.updatedAt}`.as('updated_at'), + isDeleted: sql<boolean>`${qnaComments.isDeleted}`.as('is_deleted'), + deletedAt: sql<Date>`${qnaComments.deletedAt}`.as('deleted_at'), + + // ═══════════════════════════════════════════════════════════════ + // 답변 기본 정보 (조인으로 가져와서 컨텍스트 제공) - answer 접두사 + // ═══════════════════════════════════════════════════════════════ + answerContent: sql<string>`${qnaAnswer.content}`.as('answer_content'), + answerAuthor: sql<number>`${qnaAnswer.author}`.as('answer_author'), + answerCreatedAt: sql<Date>`${qnaAnswer.createdAt}`.as('answer_created_at'), + qnaId: sql<number>`${qnaAnswer.qnaId}`.as('qna_id'), + + // ═══════════════════════════════════════════════════════════════ + // 질문 기본 정보 (답변을 통한 조인) - question 접두사 + // ═══════════════════════════════════════════════════════════════ + questionTitle: sql<string>`${qna.title}`.as('question_title'), + questionCategory: sql<string>`${qna.category}`.as('question_category'), + questionAuthor: sql<number>`${qna.author}`.as('question_author'), + + // ═══════════════════════════════════════════════════════════════ + // 댓글 작성자 정보 (users 조인) - author 접두사 + // ═══════════════════════════════════════════════════════════════ + authorName: sql<string>`${users.name}`.as('author_name'), + authorEmail: sql<string>`${users.email}`.as('author_email'), + authorDomain: sql<string>`${users.domain}`.as('author_domain'), + authorImageUrl: sql<string>`${users.imageUrl}`.as('author_image_url'), + + // ═══════════════════════════════════════════════════════════════ + // 회사 정보 (vendors/techVendors 조인) - 명시적 별칭 + // ═══════════════════════════════════════════════════════════════ + vendorName: sql<string>`${vendors.vendorName}`.as('vendor_name'), + techVendorName: sql<string>`${techVendors.vendorName}`.as('tech_vendor_name'), + companyName: sql<string>`COALESCE(${vendors.vendorName}, ${techVendors.vendorName})`.as('company_name'), + vendorType: sql<string>` + CASE + WHEN ${vendors.vendorName} IS NOT NULL THEN 'vendor' + WHEN ${techVendors.vendorName} IS NOT NULL THEN 'techVendor' + ELSE NULL + END + `.as('vendor_type'), + + // ═══════════════════════════════════════════════════════════════ + // 댓글 계층 구조 정보 + // ═══════════════════════════════════════════════════════════════ + isParentComment: sql<boolean>`${qnaComments.parentCommentId} IS NULL`.as('is_parent_comment'), + isChildComment: sql<boolean>`${qnaComments.parentCommentId} IS NOT NULL`.as('is_child_comment'), + + // 자식 댓글 수 (대댓글 수) + childCommentsCount: sql<number>`( + SELECT COUNT(*)::int + FROM ${qnaComments} qc2 + WHERE qc2.parent_comment_id = ${qnaComments.id} + AND qc2.is_deleted = false + )`.as('child_comments_count'), + + hasChildComments: sql<boolean>`( + SELECT COUNT(*) > 0 + FROM ${qnaComments} qc2 + WHERE qc2.parent_comment_id = ${qnaComments.id} + AND qc2.is_deleted = false + )`.as('has_child_comments'), + + // 댓글 깊이 (0: 부모, 1: 자식) + commentDepth: sql<number>` + CASE + WHEN ${qnaComments.parentCommentId} IS NULL THEN 0 + ELSE 1 + END + `.as('comment_depth'), + + // ═══════════════════════════════════════════════════════════════ + // 댓글 순서 정보 + // ═══════════════════════════════════════════════════════════════ + commentOrder: sql<number>`( + SELECT ROW_NUMBER() OVER ( + PARTITION BY qc2.answer_id, qc2.parent_comment_id + ORDER BY qc2.created_at ASC + ) + FROM ${qnaComments} qc2 + WHERE qc2.id = ${qnaComments.id} + AND qc2.is_deleted = false + )`.as('comment_order'), + }) + .from(qnaComments) + .leftJoin(qnaAnswer, eq(qnaComments.answerId, qnaAnswer.id)) + .leftJoin(qna, eq(qnaAnswer.qnaId, qna.id)) + .leftJoin(users, eq(qnaComments.author, users.id)) + .leftJoin(vendors, eq(users.companyId, vendors.id)) + .leftJoin(techVendors, eq(users.techCompanyId, techVendors.id)) + .where(eq(qnaComments.isDeleted, false)) + .orderBy(qnaComments.createdAt) +); + /* ================================================================ + 타입 추론 + ================================================================ */ + export type QnaViewSelect = typeof qnaView.$inferSelect; + export type QnaAnswerViewSelect = typeof qnaAnswerView.$inferSelect; + export type QnaCommentViewSelect = typeof qnaCommentView.$inferSelect;
\ No newline at end of file |