initial commit

1 files changed, 736 insertions, 0 deletions

diff --git a/db/schema/rfq.ts b/db/schema/rfq.ts
new file mode 100644
index 00000000..fe5581ce
--- /dev/null
+++ b/db/schema/rfq.ts
@@ -0,0 +1,736 @@
+import { pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, uniqueIndex, foreignKey } from "drizzle-orm/pg-core";
+import { vendors } from "./vendors";
+import { users } from "./users";
+import { items } from "./items";
+import { eq, sql, and } from "drizzle-orm";
+import { projects } from "./projects";
+
+export const rfqs = pgTable(
+    "rfqs",
+    {
+        id: serial("id").primaryKey(),
+
+        // RFQ 고유 코드
+        rfqCode: varchar("rfq_code", { length: 50 }).unique(), // ex) "RFQ-2025-001"
+
+        // 프로젝트 참조
+        projectId: integer("project_id")
+            .references(() => projects.id, { onDelete: "set null" }),
+
+        description: varchar("description", { length: 255 }),
+
+        dueDate: date("due_date", { mode: "date" })
+            .$type<Date>()
+            .notNull(),
+
+        status: varchar("status", { length: 30 })
+            .$type<"DRAFT" | "PUBLISHED" | "EVALUATION" | "AWARDED">()
+            .default("DRAFT")
+            .notNull(),
+
+        // Budgetary / Purchase 여부
+        rfqType: varchar("rfq_type", { length: 30 }).default("PURCHASE"),
+
+        // 자기 자신 테이블 FK(부모 RFQ)
+        parentRfqId: integer("parent_rfq_id"),
+
+        // 생성자
+        createdBy: integer("created_by")
+            .notNull()
+            .references(() => users.id, { onDelete: "set null" }),
+
+        createdAt: timestamp("created_at").defaultNow().notNull(),
+        updatedAt: timestamp("updated_at").defaultNow().notNull(),
+    },
+    // <==== 여기서 자기참조 FK를 정의 (수정됨)
+    (table) => {
+        return {
+            // parentRfqId -> rfqs.id
+            parentFk: foreignKey({
+                columns: [table.parentRfqId],
+                foreignColumns: [table.id],
+            }).onDelete("set null"), // 메서드 체이닝 방식으로 변경
+        };
+    }
+);
+
+export const rfqItems = pgTable("rfq_items", {
+    id: serial("id").primaryKey(),
+    rfqId: integer("rfq_id")
+        .notNull()
+        .references(() => rfqs.id, { onDelete: "cascade" }),
+    itemCode: varchar("item_code", { length: 100 })
+        .notNull()
+        .references(() => items.itemCode, { onDelete: "cascade" }),
+    description: text("description"),
+    quantity: numeric("quantity", { precision: 12, scale: 2 })
+        .$type<number>()
+        .default(1),
+    uom: varchar("uom", { length: 50 }), // 단위
+    createdAt: timestamp("created_at").defaultNow().notNull(),
+    updatedAt: timestamp("updated_at").defaultNow().notNull(),
+});
+
+
+export const rfqAttachments = pgTable("rfq_attachments", {
+    id: serial("id").primaryKey(),
+    rfqId: integer("rfq_id").references(() => rfqs.id),
+    vendorId: integer("vendor_id").references(() => vendors.id),
+    fileName: varchar("file_name", { length: 255 }).notNull(),
+    filePath: varchar("file_path", { length: 1024 }).notNull(),
+    evaluationId: integer("evaluation_id")
+        .references(() => rfqEvaluations.id)
+        .$type<number | null>(),
+    createdAt: timestamp("created_at").defaultNow().notNull(),
+    commentId: integer("comment_id")
+        .references(() => rfqComments.id)
+        .$type<number | null>(),
+});
+
+export const rfqComments = pgTable("rfq_comments", {
+    id: serial("id").primaryKey(),
+    rfqId: integer("rfq_id").references(() => rfqs.id),
+    vendorId: integer("vendor_id").references(() => vendors.id),
+    commentText: text("comment_text").notNull(),
+    commentedBy: integer("commented_by").notNull(),
+    // 아래처럼, 평가(TBE/CBE)에 속한 코멘트인지 여부를 구분할 필드
+    evaluationId: integer("evaluation_id").references(() => rfqEvaluations.id)
+        .$type<number | null>(),
+    cbeId: integer("evaluation_id").references(() => cbeEvaluations.id)
+        .$type<number | null>(),
+    createdAt: timestamp("created_at").defaultNow().notNull(),
+    updatedAt: timestamp("updated_at").defaultNow().notNull(),
+});
+
+export const rfqEvaluations = pgTable("rfq_evaluations", {
+    id: serial("id").primaryKey(),
+    rfqId: integer("rfq_id")
+        .notNull()
+        .references(() => rfqs.id),
+    vendorId: integer("vendor_id")
+        .notNull()
+        .references(() => vendors.id),
+    evalType: varchar("eval_type", { length: 30 })
+        .$type<"TBE" | "CBE">(),
+    // TBE or CBE (기술 / 상업)
+    result: varchar("result", { length: 255 }), // 예: "PASS", "FAIL", "ACCEPTABLE" 등
+    notes: text("notes"),
+    createdAt: timestamp("created_at").defaultNow().notNull(),
+    updatedAt: timestamp("updated_at").defaultNow().notNull(),
+});
+
+
+// General vendor response to RFQ invitation
+export const vendorResponses = pgTable(
+    "vendor_responses",
+    {
+        id: serial("id").primaryKey(),
+
+        rfqId: integer("rfq_id")
+            .notNull()
+            .references(() => rfqs.id, { onDelete: "cascade" }),
+
+        vendorId: integer("vendor_id")
+            .notNull()
+            .references(() => vendors.id, { onDelete: "cascade" }),
+
+        // Vendor's overall response status
+        responseStatus: varchar("response_status", { length: 30 })
+            .$type<"INVITED" | "ACCEPTED" | "DECLINED" | "REVIEWING" | "RESPONDED">()
+            .default("REVIEWING")
+            .notNull(),
+
+        // General notes from vendor
+        notes: text("notes"),
+
+        // Metadata
+        respondedBy: varchar("responded_by", { length: 255 }),  // Could be vendor contact name
+        respondedAt: timestamp("responded_at").defaultNow().notNull(),
+        updatedAt: timestamp("updated_at").defaultNow().notNull(),
+    },
+    (table) => ({
+        // Each vendor can only have one response per RFQ
+        uniqueConstraint: uniqueIndex("vendor_response_unique").on(
+            table.rfqId,
+            table.vendorId
+        ),
+    })
+);
+
+// Technical response details from vendor
+export const vendorTechnicalResponses = pgTable(
+    "vendor_technical_responses",
+    {
+        id: serial("id").primaryKey(),
+
+        // Link to main response
+        responseId: integer("response_id")
+            .notNull()
+            .references(() => vendorResponses.id, { onDelete: "cascade" }),
+
+        // 간소화된 기술 응답 필드
+        // 기술 내용은 주로 첨부 파일로 받으므로 최소한의 필드만 유지
+        summary: text("summary"),  // 간단한 기술 응답 요약
+        notes: text("notes"),      // 추가 노트나 코멘트
+
+        // Metadata
+        createdAt: timestamp("created_at").defaultNow().notNull(),
+        updatedAt: timestamp("updated_at").defaultNow().notNull(),
+    }
+);
+
+// Commercial response details from vendor
+export const vendorCommercialResponses = pgTable(
+    "vendor_commercial_responses",
+    {
+        id: serial("id").primaryKey(),
+
+        // Link to main response
+        responseId: integer("response_id")
+            .notNull()
+            .references(() => vendorResponses.id, { onDelete: "cascade" }),
+
+        // Commercial response fields
+        totalPrice: numeric("total_price", { precision: 18, scale: 2 }).$type<number>(),
+        currency: varchar("currency", { length: 10 }).default("USD"),
+
+        // Commercial terms
+        paymentTerms: varchar("payment_terms", { length: 255 }),
+        incoterms: varchar("incoterms", { length: 50 }),
+        deliveryPeriod: varchar("delivery_period", { length: 100 }),
+        warrantyPeriod: varchar("warranty_period", { length: 100 }),
+
+        // Validity of offer
+        validityPeriod: varchar("validity_period", { length: 100 }),
+
+        // Price breakdown or other commercial notes
+        priceBreakdown: text("price_breakdown"),
+        commercialNotes: text("commercial_notes"),
+
+        // Metadata
+        createdAt: timestamp("created_at").defaultNow().notNull(),
+        updatedAt: timestamp("updated_at").defaultNow().notNull(),
+    }
+);
+
+
+// Attachment table for vendor responses
+export const vendorResponseAttachments = pgTable(
+    "vendor_response_attachments",
+    {
+        id: serial("id").primaryKey(),
+
+        // Can be linked to any type of response
+        responseId: integer("response_id")
+            .references(() => vendorResponses.id, { onDelete: "cascade" }),
+        technicalResponseId: integer("technical_response_id")
+            .references(() => vendorTechnicalResponses.id, { onDelete: "cascade" }),
+        commercialResponseId: integer("commercial_response_id")
+            .references(() => vendorCommercialResponses.id, { onDelete: "cascade" }),
+
+        // File details
+        fileName: varchar("file_name", { length: 255 }).notNull(),
+        filePath: varchar("file_path", { length: 1024 }).notNull(),
+        fileType: varchar("file_type", { length: 50 }),
+
+        // 첨부 파일 분류
+        attachmentType: varchar("attachment_type", { length: 50 })
+            .$type<"TECHNICAL_SPEC" | "QUALITY_CERT" | "COMPLIANCE_DOC" | "COMMERCIAL" | "OTHER">(),
+        description: varchar("description", { length: 255 }),
+
+        // Metadata
+        uploadedAt: timestamp("uploaded_at").defaultNow().notNull(),
+        uploadedBy: varchar("uploaded_by", { length: 255 }),
+    }
+);
+
+
+export const cbeEvaluations = pgTable("cbe_evaluations", {
+    id: serial("id").primaryKey(),
+
+    // 어떤 RFQ에 대한 CBE인지
+    rfqId: integer("rfq_id")
+        .notNull()
+        .references(() => rfqs.id, { onDelete: "cascade" }),
+
+    // 어떤 벤더의 상업평가(CBE)인지
+    vendorId: integer("vendor_id")
+        .notNull()
+        .references(() => vendors.id, { onDelete: "cascade" }),
+
+    // 평가자(작성자) 정보가 필요하다면 (예: 상업팀 담당자)
+    evaluatedBy: integer("evaluated_by")
+        .references(() => users.id, { onDelete: "set null" }),
+
+    // 평가 일시
+    evaluatedAt: timestamp("evaluated_at").defaultNow().notNull(),
+
+    // 상업평가 결과(예: ACCEPT, REJECT, PENDING 등)
+    result: varchar("result", { length: 50 }),
+
+    // 총 금액(견적 기준일 수도 있고, 평가 과정에서 조정이 있을 수도 있음)
+    totalCost: numeric("total_cost", { precision: 18, scale: 2 }).$type<number>(),
+
+    // 통화
+    currency: varchar("currency", { length: 10 }).default("USD"),
+
+    // 결제 조건, 인도 조건, 기타 상업 조건
+    paymentTerms: varchar("payment_terms", { length: 255 }),
+    incoterms: varchar("incoterms", { length: 50 }),
+    deliverySchedule: text("delivery_schedule"), // 정해진 형식이 있다면 varchar, 아니면 text
+
+    // 기타 비고
+    notes: text("notes"),
+
+    // 기본 관리 필드
+    createdAt: timestamp("created_at").defaultNow().notNull(),
+    updatedAt: timestamp("updated_at").defaultNow().notNull(),
+});
+
+
+
+export const cbeView = pgView("cbe_view").as((qb) => {
+    return qb
+        .select({
+            cbeId: sql<number>`${cbeEvaluations.id}`.as("cbe_id"),
+            rfqId: sql<number>`${cbeEvaluations.rfqId}`.as("rfq_id"),
+            vendorId: sql<number>`${cbeEvaluations.vendorId}`.as("vendor_id"),
+            totalCost: sql<number | null>`${cbeEvaluations.totalCost}`.as("total_cost"),
+            currency: sql<string | null>`${cbeEvaluations.currency}`.as("currency"),
+            paymentTerms: sql<string | null>`${cbeEvaluations.paymentTerms}`.as("payment_terms"),
+            incoterms: sql<string | null>`${cbeEvaluations.incoterms}`.as("incoterms"),
+            result: sql<string | null>`${cbeEvaluations.result}`.as("result"),
+            notes: sql<string | null>`${cbeEvaluations.notes}`.as("notes"),
+            evaluatedBy: sql<number | null>`${cbeEvaluations.evaluatedBy}`.as("evaluated_by"),
+            evaluatedAt: sql<Date>`${cbeEvaluations.evaluatedAt}`.as("evaluated_at"),
+
+            rfqCode: sql<string>`${rfqs.rfqCode}`.as("rfq_code"),
+            rfqDescription: sql<string | null>`${rfqs.description}`.as("rfq_description"),
+            vendorName: sql<string>`${vendors.vendorName}`.as("vendor_name"),
+            vendorCode: sql<string | null>`${vendors.vendorCode}`.as("vendor_code"),
+
+            projectId: sql<number | null>`${projects.id}`.as("project_id"),
+            projectCode: sql<string | null>`${projects.code}`.as("project_code"),
+            projectName: sql<string | null>`${projects.name}`.as("project_name"),
+
+            evaluatorName: sql<string | null>`${users.name}`.as("evaluator_name"),
+            evaluatorEmail: sql<string | null>`${users.email}`.as("evaluator_email"),
+
+        })
+        .from(cbeEvaluations)
+        .innerJoin(rfqs, eq(cbeEvaluations.rfqId, rfqs.id))
+        .innerJoin(vendors, eq(cbeEvaluations.vendorId, vendors.id))
+        .leftJoin(projects, eq(rfqs.projectId, projects.id))
+        .leftJoin(users, eq(cbeEvaluations.evaluatedBy, users.id));
+});
+
+
+//view
+
+export const rfqsView = pgView("rfqs_view").as((qb) => {
+    return qb
+        .select({
+            // rfqs 주요 필드
+            id: sql<number>`${rfqs.id}`.as("rfq_id"),
+            status: sql<string>`${rfqs.status}`.as("status"),
+            createdAt: sql<Date>`${rfqs.createdAt}`.as("created_at"),
+            updatedAt: sql<Date>`${rfqs.updatedAt}`.as("updated_at"),
+            createdBy: sql<number | null>`${rfqs.createdBy}`.as("created_by"),
+            rfqType: sql<string | null>`${rfqs.rfqType}`.as("rfq_type"),
+
+            // rfqs 추가 필드
+            rfqCode: sql<string>`${rfqs.rfqCode}`.as("rfq_code"),
+            description: sql<string | null>`${rfqs.description}`.as("description"),
+            dueDate: sql<Date | null>`${rfqs.dueDate}`.as("due_date"),
+            parentRfqId: sql<number | null>`${rfqs.parentRfqId}`.as("parent_rfq_id"),
+
+            // 프로젝트 테이블 필드
+            projectId: sql<number | null>`${projects.id}`.as("project_id"),
+            projectCode: sql<string | null>`${projects.code}`.as("project_code"),
+            projectName: sql<string | null>`${projects.name}`.as("project_name"),
+
+            // users 테이블 관련 필드
+            userEmail: sql<string | null>`${users.email}`.as("user_email"),
+            userName: sql<string | null>`${users.name}`.as("user_name"),
+
+            // rfq_items 및 rfq_attachments 카운트
+            itemCount: sql<number>`(
+          SELECT COUNT(*) 
+          FROM "rfq_items" 
+          WHERE "rfq_items"."rfq_id" = ${rfqs.id}
+        )`.as("item_count"),
+
+            attachmentCount: sql<number>`(
+          SELECT COUNT(*) 
+          FROM "rfq_attachments" 
+          WHERE "rfq_attachments"."rfq_id" = ${rfqs.id}
+        )`.as("attachment_count"),
+
+
+        })
+        .from(rfqs)
+        .leftJoin(projects, eq(rfqs.projectId, projects.id))
+        .leftJoin(users, eq(rfqs.createdBy, users.id))
+})
+
+// vendorRfqView 업데이트 벤더 기준
+export const vendorRfqView = pgView("vendor_rfq_view").as((qb) => {
+    return qb
+        .select({
+            // vendors 테이블
+            vendorId: sql<number>`${vendors.id}`.as("vendor_id"),
+            vendorName: sql<string>`${vendors.vendorName}`.as("vendor_name"),
+            vendorCode: sql<string>`${vendors.vendorCode}`.as("vendor_code"),
+            address: sql<string | null>`${vendors.address}`.as("address"),
+            country: sql<string | null>`${vendors.country}`.as("country"),
+            email: sql<string | null>`${vendors.email}`.as("email"),
+            website: sql<string | null>`${vendors.website}`.as("website"),
+            vendorStatus: sql<string>`${vendors.status}`.as("vendor_status"),
+
+            // rfqVendors 테이블
+            rfqId: sql<number | null>`${vendorResponses.rfqId}`.as("rfq_id"),
+            rfqVendorStatus: sql<string | null>`${vendorResponses.responseStatus}`.as("rfq_vendor_status"),
+            rfqVendorUpdated: sql<Date | null>`${vendorResponses.updatedAt}`.as("rfq_vendor_updated"),
+
+            // rfqs 테이블
+            rfqCode: sql<string | null>`${rfqs.rfqCode}`.as("rfq_code"),
+            description: sql<string | null>`${rfqs.description}`.as("description"),
+            dueDate: sql<Date | null>`${rfqs.dueDate}`.as("due_date"),
+
+            // 프로젝트 테이블 (업데이트됨)
+            projectId: sql<number | null>`${projects.id}`.as("project_id"),
+            projectCode: sql<string | null>`${projects.code}`.as("project_code"),
+            projectName: sql<string | null>`${projects.name}`.as("project_name"),
+        })
+        .from(vendors)
+        .leftJoin(vendorResponses, eq(vendorResponses.vendorId, vendors.id))
+        .leftJoin(rfqs, eq(vendorResponses.rfqId, rfqs.id))
+        .leftJoin(projects, eq(rfqs.projectId, projects.id)) // 프로젝트 테이블 조인 추가
+})
+
+
+
+// vendorTbeView 업데이트
+export const vendorTbeView = pgView("vendor_tbe_view").as((qb) => {
+    return qb
+        .select({
+            // vendors 기본정보
+            vendorId: sql<number>`${vendors.id}`.as("vendor_id"),
+            vendorName: sql<string>`${vendors.vendorName}`.as("vendor_name"),
+            vendorCode: sql<string | null>`${vendors.vendorCode}`.as("vendor_code"),
+            address: sql<string | null>`${vendors.address}`.as("address"),
+            country: sql<string | null>`${vendors.country}`.as("country"),
+            email: sql<string | null>`${vendors.email}`.as("email"),
+            website: sql<string | null>`${vendors.website}`.as("website"),
+            vendorStatus: sql<string>`${vendors.status}`.as("vendor_status"),
+
+            // rfq_vendors
+            vendorResponseId: sql<number | null>`${vendorResponses.id}`.as("vendor_response_id"),
+            rfqId: sql<number | null>`${vendorResponses.rfqId}`.as("rfq_id"),
+            rfqVendorStatus: sql<string | null>`${vendorResponses.responseStatus}`.as("rfq_vendor_status"),
+            rfqVendorUpdated: sql<Date | null>`${vendorResponses.updatedAt}`.as("rfq_vendor_updated"),
+
+            // rfqs
+            rfqCode: sql<string | null>`${rfqs.rfqCode}`.as("rfq_code"),
+            rfqType: sql<string | null>`${rfqs.rfqType}`.as("rfq_type"),
+            description: sql<string | null>`${rfqs.description}`.as("description"),
+            dueDate: sql<Date | null>`${rfqs.dueDate}`.as("due_date"),
+
+            // 프로젝트 테이블 필드 (업데이트됨)
+            projectId: sql<number | null>`${projects.id}`.as("project_id"),
+            projectCode: sql<string | null>`${projects.code}`.as("project_code"),
+            projectName: sql<string | null>`${projects.name}`.as("project_name"),
+
+            // rfq_evaluations (TBE 전용)
+            tbeId: sql<number | null>`${rfqEvaluations.id}`.as("tbe_id"),
+            tbeResult: sql<string | null>`${rfqEvaluations.result}`.as("tbe_result"),
+            tbeNote: sql<string | null>`${rfqEvaluations.notes}`.as("tbe_note"),
+            tbeUpdated: sql<Date | null>`${rfqEvaluations.updatedAt}`.as("tbe_updated"),
+        })
+        .from(vendors)
+        .leftJoin(
+            vendorResponses,
+            eq(vendorResponses.vendorId, vendors.id)
+        )
+        // 1) rfqVendors ↔ rfqs
+        .leftJoin(
+            rfqs,
+            eq(vendorResponses.rfqId, rfqs.id)
+        )
+        // 2) 프로젝트 테이블 조인 추가
+        .leftJoin(
+            projects,
+            eq(rfqs.projectId, projects.id)
+        )
+        // 3) rfqEvaluations
+        .leftJoin(
+            rfqEvaluations,
+            and(
+                eq(rfqEvaluations.vendorId, vendors.id),
+                eq(rfqEvaluations.evalType, "TBE"),
+                eq(rfqEvaluations.rfqId, vendorResponses.rfqId)
+            )
+        )
+})
+
+export const vendorCbeView = pgView("vendor_cbe_view").as((qb) => {
+    return qb
+      .select({
+        // [1] Vendor 기본정보
+        vendorId: sql<number>`${vendors.id}`.as("vendor_id"),
+        vendorName: sql<string>`${vendors.vendorName}`.as("vendor_name"),
+        vendorCode: sql<string | null>`${vendors.vendorCode}`.as("vendor_code"),
+        address: sql<string | null>`${vendors.address}`.as("address"),
+        country: sql<string | null>`${vendors.country}`.as("country"),
+        email: sql<string | null>`${vendors.email}`.as("email"),
+        website: sql<string | null>`${vendors.website}`.as("website"),
+        vendorStatus: sql<string>`${vendors.status}`.as("vendor_status"),
+  
+        // [2] rfq_vendors (vendorResponses)
+        vendorResponseId: sql<number | null>`${vendorResponses.id}`.as("vendor_response_id"),
+        rfqId: sql<number | null>`${vendorResponses.rfqId}`.as("rfq_id"),
+        rfqVendorStatus: sql<string | null>`${vendorResponses.responseStatus}`.as("rfq_vendor_status"),
+        rfqVendorUpdated: sql<Date | null>`${vendorResponses.updatedAt}`.as("rfq_vendor_updated"),
+  
+        // [3] rfqs
+        rfqCode: sql<string | null>`${rfqs.rfqCode}`.as("rfq_code"),
+        rfqType: sql<string | null>`${rfqs.rfqType}`.as("rfq_type"),
+        description: sql<string | null>`${rfqs.description}`.as("description"),
+        dueDate: sql<Date | null>`${rfqs.dueDate}`.as("due_date"),
+  
+        // [4] 프로젝트 정보
+        projectId: sql<number | null>`${projects.id}`.as("project_id"),
+        projectCode: sql<string | null>`${projects.code}`.as("project_code"),
+        projectName: sql<string | null>`${projects.name}`.as("project_name"),
+  
+        // [5] CBE 평가(cbeEvaluations)
+        cbeId: sql<number | null>`${cbeEvaluations.id}`.as("cbe_id"),
+        cbeResult: sql<string | null>`${cbeEvaluations.result}`.as("cbe_result"),
+        cbeNote: sql<string | null>`${cbeEvaluations.notes}`.as("cbe_note"),
+        cbeUpdated: sql<Date | null>`${cbeEvaluations.updatedAt}`.as("cbe_updated"),
+  
+        // 상업평가용 추가 필드들
+        totalCost: sql<number | null>`${cbeEvaluations.totalCost}`.as("total_cost"),
+        currency: sql<string | null>`${cbeEvaluations.currency}`.as("currency"),
+        paymentTerms: sql<string | null>`${cbeEvaluations.paymentTerms}`.as("payment_terms"),
+        incoterms: sql<string | null>`${cbeEvaluations.incoterms}`.as("incoterms"),
+        deliverySchedule: sql<string | null>`${cbeEvaluations.deliverySchedule}`.as("delivery_schedule"),
+      })
+      .from(vendors)
+      .leftJoin(
+        vendorResponses,
+        eq(vendorResponses.vendorId, vendors.id)
+      )
+      .leftJoin(
+        rfqs,
+        eq(vendorResponses.rfqId, rfqs.id)
+      )
+      .leftJoin(
+        projects,
+        eq(rfqs.projectId, projects.id)
+      )
+      .leftJoin(
+        cbeEvaluations,
+        and(
+          eq(cbeEvaluations.vendorId, vendors.id),
+          eq(cbeEvaluations.rfqId, vendorResponses.rfqId)
+        )
+      );
+  });
+
+// Types for TypeScript
+export type VendorResponse = typeof vendorResponses.$inferSelect;
+export type VendorTechnicalResponse = typeof vendorTechnicalResponses.$inferSelect;
+export type VendorCommercialResponse = typeof vendorCommercialResponses.$inferSelect;
+export type VendorResponseAttachment = typeof vendorResponseAttachments.$inferSelect;
+
+
+// View to combine vendor response data with projects
+export const vendorResponsesView = pgView("vendor_responses_view").as((qb) => {
+    return qb
+        .select({
+            // Response identification
+            responseId: sql<number>`${vendorResponses.id}`.as("response_id"),
+            rfqId: sql<number>`${vendorResponses.rfqId}`.as("rfq_id"),
+            vendorId: sql<number>`${vendorResponses.vendorId}`.as("vendor_id"),
+
+            // RFQ information
+            rfqCode: sql<string>`${rfqs.rfqCode}`.as("rfq_code"),
+            rfqDescription: sql<string | null>`${rfqs.description}`.as("rfq_description"),
+            rfqDueDate: sql<Date | null>`${rfqs.dueDate}`.as("rfq_due_date"),
+            rfqStatus: sql<string>`${rfqs.status}`.as("rfq_status"),
+            rfqType: sql<string | null>`${rfqs.rfqType}`.as("rfq_type"),
+            rfqCreatedAt: sql<Date>`${rfqs.createdAt}`.as("rfq_created_at"),
+            rfqUpdatedAt: sql<Date>`${rfqs.updatedAt}`.as("rfq_updated_at"),
+            rfqCreatedBy: sql<number | null>`${rfqs.createdBy}`.as("rfq_created_by"),
+
+            // Project information
+            projectId: sql<number | null>`${projects.id}`.as("project_id"),
+            projectCode: sql<string | null>`${projects.code}`.as("project_code"),
+            projectName: sql<string | null>`${projects.name}`.as("project_name"),
+
+            // Vendor information
+            vendorName: sql<string>`${vendors.vendorName}`.as("vendor_name"),
+            vendorCode: sql<string | null>`${vendors.vendorCode}`.as("vendor_code"),
+
+            // Response status
+            responseStatus: sql<"INVITED" | "ACCEPTED" | "DECLINED" | "REVIEWING" | "RESPONDED">`${vendorResponses.responseStatus}`
+            .as("response_status"),
+            respondedAt: sql<Date>`${vendorResponses.respondedAt}`.as("responded_at"),
+
+            // Technical response indicators
+            hasTechnicalResponse: sql<boolean>`CASE WHEN ${vendorTechnicalResponses.id} IS NOT NULL THEN TRUE ELSE FALSE END`.as("has_technical_response"),
+            technicalResponseId: sql<number | null>`${vendorTechnicalResponses.id}`.as("technical_response_id"),
+
+            // Commercial response indicators
+            hasCommercialResponse: sql<boolean>`CASE WHEN ${vendorCommercialResponses.id} IS NOT NULL THEN TRUE ELSE FALSE END`.as("has_commercial_response"),
+            commercialResponseId: sql<number | null>`${vendorCommercialResponses.id}`.as("commercial_response_id"),
+            totalPrice: sql<number | null>`${vendorCommercialResponses.totalPrice}`.as("total_price"),
+            currency: sql<string | null>`${vendorCommercialResponses.currency}`.as("currency"),
+
+            // Evaluation links
+            tbeId: sql<number | null>`${rfqEvaluations.id}`.as("tbe_id"),
+            tbeResult: sql<string | null>`${rfqEvaluations.result}`.as("tbe_result"),
+            cbeId: sql<number | null>`${cbeEvaluations.id}`.as("cbe_id"),
+            cbeResult: sql<string | null>`${cbeEvaluations.result}`.as("cbe_result"),
+
+            // Attachments count
+            attachmentCount: sql<number>`(
+          SELECT COUNT(*) 
+          FROM "vendor_response_attachments" 
+          WHERE "vendor_response_attachments"."response_id" = ${vendorResponses.id}
+        )`.as("attachment_count"),
+        })
+        .from(vendorResponses)
+        .innerJoin(rfqs, eq(vendorResponses.rfqId, rfqs.id))
+        .innerJoin(vendors, eq(vendorResponses.vendorId, vendors.id))
+        .leftJoin(projects, eq(rfqs.projectId, projects.id)) // Added project join
+        .leftJoin(
+            vendorTechnicalResponses,
+            eq(vendorTechnicalResponses.responseId, vendorResponses.id)
+        )
+        .leftJoin(
+            vendorCommercialResponses,
+            eq(vendorCommercialResponses.responseId, vendorResponses.id)
+        )
+        .leftJoin(
+            rfqEvaluations,
+            and(
+                eq(rfqEvaluations.rfqId, vendorResponses.rfqId),
+                eq(rfqEvaluations.vendorId, vendorResponses.vendorId),
+                eq(rfqEvaluations.evalType, "TBE")
+            )
+        )
+        .leftJoin(
+            cbeEvaluations,
+            and(
+                eq(cbeEvaluations.rfqId, vendorResponses.rfqId),
+                eq(cbeEvaluations.vendorId, vendorResponses.vendorId)
+            )
+        );
+});
+
+
+export type VendorResponsesView = typeof vendorResponsesView.$inferSelect
+
+
+export type Rfq = typeof rfqs.$inferSelect
+export type RfqItem = typeof rfqItems.$inferSelect
+export type RfqAttach = typeof rfqAttachments.$inferSelect
+export type RfqComment = typeof rfqComments.$inferSelect
+export type RfqEvaluation = typeof rfqEvaluations.$inferSelect
+
+export type VendorRfqViewBase = typeof vendorRfqView.$inferSelect
+
+// 2) 우리가 코드에서 필요한 속성까지 합친 확장 타입
+export interface VendorRfqViewWithComments extends VendorRfqViewBase {
+    comments?: {
+        id: number
+        commentText: string
+        vendorId?: number
+        evaluationId?: number
+        createdAt: Date
+        commentedBy?: number
+    }[]
+    // 필요하다면 quote, attachments 등 추가 필드도...
+}
+
+export type VendorTbeView = typeof vendorTbeView.$inferSelect
+export type VendorCbeView = typeof vendorCbeView.$inferSelect
+
+export interface RfqWithItemCount {
+    // 기본 RFQ 식별자
+    rfqId: number;
+    // id: number;  // rfqId와 동일 (편의상 중복)
+
+    // RFQ 기본 정보
+    rfqCode: string;
+    description: string | null;
+    status: string;  // 필요시 "DRAFT" | "PUBLISHED" | "EVALUATION" | "AWARDED" 로 제한 가능
+
+    // 날짜 정보
+    dueDate: Date | null;
+    createdAt: Date;
+    updatedAt: Date;
+
+    // 프로젝트 관련 정보
+    projectCode: string | null;
+    projectName: string | null;
+
+    // 생성자 정보
+    createdBy: number | null;
+    createdByEmail: string | null;
+
+    // 사용자 정보
+    userId?: number | null;
+    userEmail?: string | null;
+    userName?: string | null;
+
+    // 집계 정보 (서브쿼리로 계산됨)
+    itemCount: number;
+    attachCount: number;
+
+    // 추가 필드 (필요시 데이터 변환 함수에서 채울 수 있음)
+    rfqType?: string | null;
+    projectId?: number | null;
+    parentRfqId?: number | null;
+
+    // 다른 필요한 필드들
+    vendorId?: number | null;
+    rfqVendorId?: number | null;
+    rfqVendorStatus?: string | null;
+    rfqVendorUpdated?: Date | null;
+}
+
+export interface RfqWithItems extends Rfq {
+    lines: RfqItem[];
+}
+
+export interface RfqWithAll extends VendorResponsesView {
+
+    // 아래처럼 배열/객체로
+    items: Array<{
+        id: number
+        itemCode: string
+        itemName: string
+        quantity?: number
+        uom?: string
+        description?: string | null
+    }>
+    attachments: Array<{
+        id: number
+        fileName: string
+        filePath: string
+        vendorId?: number | null
+        evaluationId?: number | null
+    }>
+    comments: Array<{
+        id: number
+        commentText: string
+        vendorId?: number | null
+        evaluationId?: number | null
+        createdAt: Date
+    }>
+
+}
+export type RfqsView = typeof rfqsView.$inferSelect