(대표님/최겸) 20250702 변경사항 업데이트

3 files changed, 603 insertions, 1 deletions

diff --git a/db/schema/bRfq.ts b/db/schema/bRfq.ts
index 3cd1e98a..18929b77 100644
--- a/db/schema/bRfq.ts
+++ b/db/schema/bRfq.ts
@@ -217,6 +217,8 @@ export const bRfqAttachmentRevisions = pgTable(
             .references(() => users.id, { onDelete: "set null" })
             .notNull(),
         createdAt: timestamp("created_at").defaultNow().notNull(),
+
+        
     },
     (t) => ({
         // 첨부파일당 하나의 최신 리비전만 허용
@@ -320,6 +322,14 @@ export const vendorAttachmentResponses = pgTable(
 
         createdAt: timestamp("created_at").defaultNow().notNull(),
         updatedAt: timestamp("updated_at").defaultNow().notNull(),
+
+        createdBy: integer("created_by")
+        .references(() => users.id, { onDelete: "set null" })
+      ,
+
+        updatedBy: integer("updated_by")
+        .references(() => users.id, { onDelete: "set null" })
+        ,
     }, (t) => ({
         // attachmentId + vendorId + rfqType 유니크
         vendorResponseIdx: uniqueIndex('vendor_response_idx').on(
diff --git a/db/schema/evaluationCriteria.ts b/db/schema/evaluationCriteria.ts
index 232c4b69..f87b2219 100644
--- a/db/schema/evaluationCriteria.ts
+++ b/db/schema/evaluationCriteria.ts
@@ -10,6 +10,7 @@ import {
   varchar

 } from 'drizzle-orm/pg-core';

 import { eq, relations, sql } from 'drizzle-orm';

+import { users } from './users';

 

 // ----------------------------------------------------------------------------------------------------

 

@@ -54,6 +55,14 @@ const regEvalCriteria = pgTable('reg_eval_criteria', {
   remarks: text('remarks'),

   createdAt: timestamp('created_at').defaultNow().notNull(),

   updatedAt: timestamp('updated_at').defaultNow().notNull(),

+  createdBy: integer("created_by")

+    .notNull()

+    .references(() => users.id, { onDelete: "set null" }),

+

+  updatedBy: integer("updated_by")

+    .notNull()

+    .references(() => users.id, { onDelete: "set null" }),

+

 });

 const regEvalCriteriaDetails = pgTable('reg_eval_criteria_details', {

   id: serial('id').primaryKey(),

@@ -102,7 +111,7 @@ const regEvalCriteriaView = pgView('reg_eval_criteria_view').as((qb) =>
 const regEvalCriteriaRelations = relations(regEvalCriteria, ({ many }) => ({

   details: many(regEvalCriteriaDetails),

 }));

-const regEvalCriteriaDetailsRelations = relations(regEvalCriteriaDetails, ({one}) => ({

+const regEvalCriteriaDetailsRelations = relations(regEvalCriteriaDetails, ({ one }) => ({

   criteria: one(regEvalCriteria, {

     fields: [regEvalCriteriaDetails.criteriaId],

     references: [regEvalCriteria.id],

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