(대표님, 최겸) 계약, 업로드 관련, 메뉴처리, 입찰, 프리쿼트, rfqLast관련, tbeLast관련

1 files changed, 107 insertions, 2 deletions

diff --git a/db/schema/rfqLast.ts b/db/schema/rfqLast.ts
index 766aeb6b..f643a2fa 100644
--- a/db/schema/rfqLast.ts
+++ b/db/schema/rfqLast.ts
@@ -1,4 +1,4 @@
-import { index, pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, alias, check, uniqueIndex } from "drizzle-orm/pg-core";
+import { decimal, json,index, pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, alias, check, uniqueIndex, unique } from "drizzle-orm/pg-core";
 import { eq, sql, relations } from "drizzle-orm";
 import { projects } from "./projects";
 import { users } from "./users";
@@ -131,6 +131,40 @@ export const rfqLastDetails = pgTable(
         placeOfDestination: varchar("place_of_destination", { length: 255 }),
         remark: text("remark"),
         cancelReason: text("cancel_reason"),
+        
+        // ===== 업체 선정 관련 컬럼 추가 =====
+        // 선정 정보
+        isSelected: boolean("is_selected").default(false),
+        selectionDate: timestamp("selection_date", { withTimezone: true })
+            .$type<Date | null>(),
+        selectionReason: text("selection_reason"),
+        selectedBy: integer("selected_by")
+            .references(() => users.id, { onDelete: "set null" }),
+        
+        // 가격 및 평가 정보
+        totalAmount: decimal("total_amount", { precision: 15, scale: 2 }),
+        priceRank: integer("price_rank"),
+        technicalScore: decimal("technical_score", { precision: 5, scale: 2 }), // 기술평가 점수 (선택)
+        commercialScore: decimal("commercial_score", { precision: 5, scale: 2 }), // 상업평가 점수 (선택)
+        totalScore: decimal("total_score", { precision: 5, scale: 2 }), // 종합점수 (선택)
+        
+        // 선정 승인 프로세스 (선택사항)
+        selectionApprovalStatus: varchar("selection_approval_status", { length: 30 })
+            .$type<"대기" | "승인" | "반려" | null>(),
+        selectionApprovedBy: integer("selection_approved_by")
+            .references(() => users.id, { onDelete: "set null" }),
+        selectionApprovedAt: timestamp("selection_approved_at", { withTimezone: true })
+            .$type<Date | null>(),
+        selectionApprovalComment: text("selection_approval_comment"),
+        
+        // 계약 전환 정보 (선택사항)
+        contractStatus: varchar("contract_status", { length: 30 })
+            .$type<"대기" | "진행중" | "완료" | "취소" | null>(),
+        contractCreatedAt: timestamp("contract_created_at", { withTimezone: true })
+            .$type<Date | null>(),
+        contractNo: varchar("contract_no", { length: 50 }),
+        
+        // ===== 기존 컬럼들 =====
         updatedBy: integer("updated_by")
             .notNull()
             .references(() => users.id, { onDelete: "set null" }),
@@ -153,7 +187,7 @@ export const rfqLastDetails = pgTable(
         firstYn: boolean("first_yn").default(false),
         
         firstDescription: text("first_description"),
-        sparepartDescription: text("sparepart_escription"),
+        sparepartDescription: text("sparepart_description"), // 오타 수정
         
         sendVersion: integer("send_version").default(0),
         isLatest: boolean("is_latest").notNull().default(true),
@@ -165,6 +199,11 @@ export const rfqLastDetails = pgTable(
         lastEmailSentAt: timestamp("last_email_sent_at"),
         emailStatus: varchar("email_status", { length: 30 })
             .$type<"pending" | "sent" | "failed" | "bounced" | null>(),
+            
+        // ===== 추가 메타데이터 =====
+        createdAt: timestamp("created_at").defaultNow().notNull(), // 생성일 추가
+        createdBy: integer("created_by")
+            .references(() => users.id, { onDelete: "set null" }),
     },
     (table) => {
         return {
@@ -174,10 +213,18 @@ export const rfqLastDetails = pgTable(
                 .on(table.rfqsLastId, table.vendorsId)
                 .where(sql`${table.isLatest} = true`),
             
+            // 선정된 업체는 RFQ당 하나만 (partial unique index)
+            uniqueSelectedVendor: uniqueIndex("unique_selected_vendor")
+                .on(table.rfqsLastId)
+                .where(sql`${table.isSelected} = true AND ${table.isLatest} = true`),
+            
             // 성능을 위한 추가 인덱스들
             rfqIdIndex: index("idx_rfqs_last_id").on(table.rfqsLastId),
             vendorIdIndex: index("idx_vendors_id").on(table.vendorsId),
             isLatestIndex: index("idx_is_latest").on(table.isLatest),
+            isSelectedIndex: index("idx_is_selected").on(table.isSelected),
+            priceRankIndex: index("idx_price_rank").on(table.priceRank),
+            selectionDateIndex: index("idx_selection_date").on(table.selectionDate),
         };
     }
 );
@@ -822,3 +869,61 @@ export const rfqDetailsLastRelations = relations(
     })
 );
 
+
+
+export const vendorSelections = pgTable(
+    "vendor_selections",
+    {
+      id: serial("id").primaryKey(),
+      
+      // RFQ 정보
+      rfqId: integer("rfq_id")
+        .notNull()
+        .references(() => rfqsLast.id, { onDelete: "cascade" }),
+      rfqCode: varchar("rfq_code", { length: 50 }),
+      
+      // 선정된 업체 정보
+      vendorId: integer("vendor_id")
+        .notNull()
+        .references(() => vendors.id, { onDelete: "restrict" }),
+      vendorName: varchar("vendor_name", { length: 255 }).notNull(),
+      vendorCode: varchar("vendor_code", { length: 50 }).notNull(),
+      
+      // 선정 금액 정보
+      selectedAmount: decimal("selected_amount", { precision: 15, scale: 2 }).notNull(),
+      currency: varchar("currency", { length: 10 }).notNull(),
+      
+      // 선정 사유 및 평가
+      selectionReason: text("selection_reason").notNull(),
+      priceRank: integer("price_rank"),
+      hasConditionDifferences: boolean("has_condition_differences").default(false),
+      criticalDifferences: json("critical_differences").$type<string[]>(),
+      
+      // 선정 승인 정보 (옵션)
+      approvalStatus: varchar("approval_status", { length: 30 })
+        .$type<"대기" | "승인" | "반려">()
+        .default("대기"),
+      approvedBy: integer("approved_by")
+        .references(() => users.id, { onDelete: "set null" }),
+      approvedAt: timestamp("approved_at", { withTimezone: true }),
+      approvalComment: text("approval_comment"),
+      
+      // 메타 정보
+      selectedBy: integer("selected_by")
+        .references(() => users.id, { onDelete: "set null" }),
+      selectedAt: timestamp("selected_at", { withTimezone: true }).notNull(),
+      
+      createdAt: timestamp("created_at").defaultNow().notNull(),
+      updatedAt: timestamp("updated_at").defaultNow().notNull(),
+    },
+    (table) => {
+      return {
+        // RFQ당 하나의 선정만 가능
+        uniqueRfqSelection: unique().on(table.rfqId),
+        // 인덱스
+        rfqIdIdx: index("vendor_selections_rfq_id_idx").on(table.rfqId),
+        vendorIdIdx: index("vendor_selections_vendor_id_idx").on(table.vendorId),
+        approvalStatusIdx: index("vendor_selections_approval_status_idx").on(table.approvalStatus),
+      };
+    }
+  );
\ No newline at end of file