diff options
Diffstat (limited to 'db/schema/rfqLast.ts')
| -rw-r--r-- | db/schema/rfqLast.ts | 109 |
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 |