~20250428 작업사항

1 files changed, 186 insertions, 67 deletions

diff --git a/db/schema/rfq.ts b/db/schema/rfq.ts
index 98c4245c..4e35dd10 100644
--- a/db/schema/rfq.ts
+++ b/db/schema/rfq.ts
@@ -3,7 +3,7 @@ import { vendors } from "./vendors";
 import { users } from "./users";
 import { items } from "./items";
 import { eq, sql, and } from "drizzle-orm";
-import { projects } from "./projects";
+import { biddingProjects, projects } from "./projects";
 
 export const rfqs = pgTable(
     "rfqs",
@@ -17,6 +17,9 @@ export const rfqs = pgTable(
         projectId: integer("project_id")
             .references(() => projects.id, { onDelete: "set null" }),
 
+        bidProjectId: integer("bid_project_id")
+            .references(() => biddingProjects.id, { onDelete: "set null" }),
+
         description: varchar("description", { length: 255 }),
 
         dueDate: date("due_date", { mode: "date" })
@@ -71,7 +74,6 @@ export const rfqItems = pgTable("rfq_items", {
     updatedAt: timestamp("updated_at").defaultNow().notNull(),
 });
 
-
 export const rfqAttachments = pgTable("rfq_attachments", {
     id: serial("id").primaryKey(),
     rfqId: integer("rfq_id").references(() => rfqs.id),
@@ -103,7 +105,7 @@ export const rfqComments = pgTable("rfq_comments", {
         .references(() => rfqEvaluations.id)
         .$type<number | null>(),
     cbeId: integer("cbe_id")
-        .references(() => cbeEvaluations.id)
+        .references(() => vendorResponses.id)
         .$type<number | null>(),
     createdAt: timestamp("created_at").defaultNow().notNull(),
     updatedAt: timestamp("updated_at").defaultNow().notNull(),
@@ -175,6 +177,12 @@ export const vendorTechnicalResponses = pgTable(
             .notNull()
             .references(() => vendorResponses.id, { onDelete: "cascade" }),
 
+        responseStatus: varchar("response_status", { length: 30 })
+            .$type<"PENDING" | "IN_PROGRESS" | "SUBMITTED" | "REJECTED" | "ACCEPTED">()
+            .default("PENDING")
+            .notNull(),
+
+
         // 간소화된 기술 응답 필드
         // 기술 내용은 주로 첨부 파일로 받으므로 최소한의 필드만 유지
         summary: text("summary"),  // 간단한 기술 응답 요약
@@ -197,6 +205,11 @@ export const vendorCommercialResponses = pgTable(
             .notNull()
             .references(() => vendorResponses.id, { onDelete: "cascade" }),
 
+        responseStatus: varchar("response_status", { length: 30 })
+            .$type<"PENDING" | "IN_PROGRESS" | "SUBMITTED" | "REJECTED" | "ACCEPTED">()
+            .default("PENDING")
+            .notNull(),
+
         // Commercial response fields
         totalPrice: numeric("total_price", { precision: 18, scale: 2 }).$type<number>(),
         currency: varchar("currency", { length: 10 }).default("USD"),
@@ -260,7 +273,7 @@ export const cbeEvaluations = pgTable("cbe_evaluations", {
         .notNull()
         .references(() => rfqs.id, { onDelete: "cascade" }),
 
-    // 어떤 벤더의 상업평가(CBE)인지
+    // 어떤 협력업체의 상업평가(CBE)인지
     vendorId: integer("vendor_id")
         .notNull()
         .references(() => vendors.id, { onDelete: "cascade" }),
@@ -380,7 +393,7 @@ export const rfqsView = pgView("rfqs_view").as((qb) => {
         .leftJoin(users, eq(rfqs.createdBy, users.id))
 })
 
-// vendorRfqView 업데이트 벤더 기준
+// vendorRfqView 업데이트 협력업체 기준
 export const vendorRfqView = pgView("vendor_rfq_view").as((qb) => {
     return qb
         .select({
@@ -437,9 +450,17 @@ export const vendorTbeView = pgView("vendor_tbe_view").as((qb) => {
             rfqVendorStatus: sql<string | null>`${vendorResponses.responseStatus}`.as("rfq_vendor_status"),
             rfqVendorUpdated: sql<Date | null>`${vendorResponses.updatedAt}`.as("rfq_vendor_updated"),
 
+             // TBE 응답 상태 추가
+             technicalResponseId: sql<number | null>`${vendorTechnicalResponses.id}`.as("technical_response_id"),
+             technicalResponseStatus: sql<string | null>`${vendorTechnicalResponses.responseStatus}`.as("technical_response_status"),
+             technicalSummary: sql<string | null>`${vendorTechnicalResponses.summary}`.as("technical_summary"),
+             technicalNotes: sql<string | null>`${vendorTechnicalResponses.notes}`.as("technical_notes"),
+             technicalUpdated: sql<Date | null>`${vendorTechnicalResponses.updatedAt}`.as("technical_updated"),
+
             // rfqs
             rfqCode: sql<string | null>`${rfqs.rfqCode}`.as("rfq_code"),
             rfqType: sql<string | null>`${rfqs.rfqType}`.as("rfq_type"),
+            rfqStatus: sql<string | null>`${rfqs.status}`.as("rfq_status"),
             description: sql<string | null>`${rfqs.description}`.as("description"),
             dueDate: sql<Date | null>`${rfqs.dueDate}`.as("due_date"),
 
@@ -468,6 +489,11 @@ export const vendorTbeView = pgView("vendor_tbe_view").as((qb) => {
         .leftJoin(
             projects,
             eq(rfqs.projectId, projects.id)
+            
+        )
+        .leftJoin(
+            vendorTechnicalResponses,
+            eq(vendorTechnicalResponses.responseId, vendorResponses.id)
         )
         // 3) rfqEvaluations
         .leftJoin(
@@ -482,68 +508,68 @@ export const vendorTbeView = pgView("vendor_tbe_view").as((qb) => {
 
 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)
+        .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;
@@ -582,7 +608,7 @@ export const vendorResponsesView = pgView("vendor_responses_view").as((qb) => {
 
             // Response status
             responseStatus: sql<"INVITED" | "ACCEPTED" | "DECLINED" | "REVIEWING" | "RESPONDED">`${vendorResponses.responseStatus}`
-            .as("response_status"),
+                .as("response_status"),
             respondedAt: sql<Date>`${vendorResponses.respondedAt}`.as("responded_at"),
 
             // Technical response indicators
@@ -713,6 +739,10 @@ export interface RfqWithItems extends Rfq {
     lines: RfqItem[];
 }
 
+export interface RfqViewWithItems extends RfqsView {
+    lines: RfqItem[];
+}
+
 export interface RfqWithAll extends VendorResponsesView {
 
     // 아래처럼 배열/객체로
@@ -741,3 +771,92 @@ export interface RfqWithAll extends VendorResponsesView {
 
 }
 export type RfqsView = typeof rfqsView.$inferSelect
+
+
+export const vendorResponseCBEView = pgView("vendor_response_cbe_view").as((qb) => {
+    return qb
+        .select({
+            // 기본 응답 식별 정보
+            responseId: sql<number>`${vendorResponses.id}`.as("response_id"),
+            rfqId: sql<number>`${vendorResponses.rfqId}`.as("rfq_id"),
+            vendorId: sql<number>`${vendorResponses.vendorId}`.as("vendor_id"),
+
+            // vendorResponses 상세 정보
+            responseStatus: sql<string>`${vendorResponses.responseStatus}`.as("response_status"),
+            notes: sql<string | null>`${vendorResponses.notes}`.as("response_notes"),
+            respondedBy: sql<string | null>`${vendorResponses.respondedBy}`.as("responded_by"),
+            respondedAt: sql<Date>`${vendorResponses.respondedAt}`.as("responded_at"),
+            responseUpdatedAt: sql<Date>`${vendorResponses.updatedAt}`.as("response_updated_at"),
+
+            // RFQ 정보
+            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"),
+
+            // 협력업체 정보
+            vendorName: sql<string>`${vendors.vendorName}`.as("vendor_name"),
+            vendorCode: sql<string | null>`${vendors.vendorCode}`.as("vendor_code"),
+            vendorStatus: sql<string | null>`${vendors.status}`.as("vendor_status"),
+
+            // 프로젝트 정보
+            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"),
+
+            // 상업 응답 상세 정보
+            commercialResponseId: sql<number | null>`${vendorCommercialResponses.id}`.as("commercial_response_id"),
+            commercialResponseStatus: sql<string | null>`${vendorCommercialResponses.responseStatus}`.as("commercial_response_status"),
+            totalPrice: sql<number | null>`${vendorCommercialResponses.totalPrice}`.as("total_price"),
+            currency: sql<string | null>`${vendorCommercialResponses.currency}`.as("currency"),
+            paymentTerms: sql<string | null>`${vendorCommercialResponses.paymentTerms}`.as("payment_terms"),
+            incoterms: sql<string | null>`${vendorCommercialResponses.incoterms}`.as("incoterms"),
+            deliveryPeriod: sql<string | null>`${vendorCommercialResponses.deliveryPeriod}`.as("delivery_period"),
+            warrantyPeriod: sql<string | null>`${vendorCommercialResponses.warrantyPeriod}`.as("warranty_period"),
+            validityPeriod: sql<string | null>`${vendorCommercialResponses.validityPeriod}`.as("validity_period"),
+            priceBreakdown: sql<string | null>`${vendorCommercialResponses.priceBreakdown}`.as("price_breakdown"),
+            commercialNotes: sql<string | null>`${vendorCommercialResponses.commercialNotes}`.as("commercial_notes"),
+            commercialCreatedAt: sql<Date | null>`${vendorCommercialResponses.createdAt}`.as("commercial_created_at"),
+            commercialUpdatedAt: sql<Date | null>`${vendorCommercialResponses.updatedAt}`.as("commercial_updated_at"),
+
+            // 첨부파일 개수 및 상세 정보
+            attachmentCount: sql<number>`(
+                SELECT COUNT(*) 
+                FROM "vendor_response_attachments" 
+                WHERE "vendor_response_attachments"."response_id" = ${vendorResponses.id}
+            )`.as("attachment_count"),
+
+            commercialAttachmentCount: sql<number>`(
+                SELECT COUNT(*) 
+                FROM "vendor_response_attachments" 
+                WHERE "vendor_response_attachments"."commercial_response_id" = ${vendorCommercialResponses.id}
+            )`.as("commercial_attachment_count"),
+
+            // 첨부파일 유형별 개수 (선택적)
+            technicalAttachmentCount: sql<number>`(
+                SELECT COUNT(*) 
+                FROM "vendor_response_attachments" 
+                WHERE "vendor_response_attachments"."response_id" = ${vendorResponses.id}
+                AND "vendor_response_attachments"."attachment_type" = 'TECHNICAL_SPEC'
+            )`.as("technical_attachment_count"),
+
+            // 최신 첨부파일 정보 (선택적)
+            latestAttachmentDate: sql<Date | null>`(
+                SELECT MAX("uploaded_at") 
+                FROM "vendor_response_attachments" 
+                WHERE "vendor_response_attachments"."response_id" = ${vendorResponses.id}
+            )`.as("latest_attachment_date"),
+        })
+        .from(vendorResponses)
+        .innerJoin(rfqs, eq(vendorResponses.rfqId, rfqs.id))
+        .innerJoin(vendors, eq(vendorResponses.vendorId, vendors.id))
+        .leftJoin(projects, eq(rfqs.projectId, projects.id))
+        .leftJoin(
+            vendorCommercialResponses,
+            eq(vendorCommercialResponses.responseId, vendorResponses.id)
+        );
+});
+
+// TypeScript 타입 정의
+export type VendorResponseCBEView = typeof vendorResponseCBEView.$inferSelect;
\ No newline at end of file