summaryrefslogtreecommitdiff
path: root/db/schema
diff options
context:
space:
mode:
Diffstat (limited to 'db/schema')
-rw-r--r--db/schema/index.ts1
-rw-r--r--db/schema/procurementRFQ.ts788
-rw-r--r--db/schema/users.ts2
-rw-r--r--db/schema/vendors.ts2
4 files changed, 793 insertions, 0 deletions
diff --git a/db/schema/index.ts b/db/schema/index.ts
index 189519e8..537cc16c 100644
--- a/db/schema/index.ts
+++ b/db/schema/index.ts
@@ -12,3 +12,4 @@ export * from './vendors';
export * from './tasks';
export * from './logs';
export * from './basicContractDocumnet';
+export * from './procurementRFQ';
diff --git a/db/schema/procurementRFQ.ts b/db/schema/procurementRFQ.ts
new file mode 100644
index 00000000..eb83afa8
--- /dev/null
+++ b/db/schema/procurementRFQ.ts
@@ -0,0 +1,788 @@
+import { foreignKey, pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, alias, check } from "drizzle-orm/pg-core";
+import { eq, sql, and, or, relations } from "drizzle-orm";
+import { projects } from "./projects";
+import { users } from "./users";
+import { items } from "./items";
+import { vendors } from "./vendors";
+
+export const procurementRfqs = pgTable(
+ "procurement_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" }),
+
+ series: varchar("series", { length: 50 }),
+
+ itemId: integer("item_id")
+ .notNull()
+ .references(() => items.id, { onDelete: "cascade" }),
+
+ dueDate: date("due_date", { mode: "date" })
+ .$type<Date>()
+ .notNull(),
+
+ rfqSendDate: date("rfq_send_date", { mode: "date" })
+ .$type<Date | null>(), // notNull() 제약조건 제거, null 허용
+
+ status: varchar("status", { length: 30 })
+ .$type<"RFQ Created" | "RFQ Vendor Assignned" | "RFQ Sent" | "Quotation Analysis" | "PO Transfer" | "PO Create">()
+ .default("RFQ Created")
+ .notNull(),
+
+ rfqSealedYn: boolean("rfq_sealed_yn").default(false),
+ picCode: varchar("pic_code", { length: 50 }),
+
+ remark: text("remark"),
+ // 생성자
+
+ sentBy: integer("sent_by")
+ .references(() => users.id, { onDelete: "set null" }),
+
+
+ createdBy: integer("created_by")
+ .notNull()
+ .references(() => users.id, { onDelete: "set null" }),
+
+ updatedBy: integer("updated_by")
+ .notNull()
+ .references(() => users.id, { onDelete: "set null" }),
+
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ updatedAt: timestamp("updated_at").defaultNow().notNull(),
+ },
+
+);
+
+export const paymentTerms = pgTable("payment_terms", {
+ code: varchar("code", { length: 50 }).primaryKey(),
+ description: varchar("description", { length: 255 }).notNull(),
+ // days: integer("days").notNull(),
+ isActive: boolean("is_active").default(true).notNull(),
+ createdBy: integer("created_by")
+ .references(() => users.id, { onDelete: "set null" }),
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+});
+
+// 인코텀즈 테이블
+export const incoterms = pgTable("incoterms", {
+ code: varchar("code", { length: 20 }).primaryKey(),
+ description: varchar("description", { length: 255 }).notNull(),
+ isActive: boolean("is_active").default(true).notNull(),
+ createdBy: integer("created_by")
+ .references(() => users.id, { onDelete: "set null" }),
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+});
+
+export const procurementRfqDetails = pgTable(
+ "procurement_rfq_details",
+ {
+ id: serial("id").primaryKey(),
+ procurementRfqsId: integer("procurement_rfqs_id")
+ .references(() => procurementRfqs.id, { onDelete: "set null" }),
+ vendorsId: integer("vendors_id")
+ .references(() => vendors.id, { onDelete: "set null" }),
+ currency: varchar("currency", { length: 10 }).default("USD"),
+
+ // 정규화된 paymentTerms 참조
+ paymentTermsCode: varchar("payment_terms_code", { length: 50 })
+ .references(() => paymentTerms.code, { onDelete: "set null" }),
+ // paymentTerms 필드는 제거 (코드로 조회)
+
+ // 정규화된 incoterms 참조
+ incotermsCode: varchar("incoterms_code", { length: 20 })
+ .references(() => incoterms.code, { onDelete: "set null" }),
+ incotermsDetail: varchar("incoterms_detail", { length: 255 }),
+
+ deliveryDate: date("delivery_date", { mode: "date" })
+ .$type<Date>()
+ .notNull(),
+ taxCode: varchar("tax_code", { length: 255 }).default("VV"),
+ placeOfShipping: varchar("place_of_shipping", { length: 255 }),
+ placeOfDestination: varchar("place_of_destination", { length: 255 }),
+ remark: text("remark"),
+ cancelReason: text("cancel_reason"),
+ updatedBy: integer("updated_by")
+ .notNull()
+ .references(() => users.id, { onDelete: "set null" }),
+ updatedAt: timestamp("updated_at").defaultNow().notNull(),
+ materialPriceRelatedYn: boolean("material_price_related_yn").default(false),
+ }
+);
+
+export const prItems = pgTable(
+ "pr_items",
+ {
+ id: serial("id").primaryKey(),
+ procurementRfqsId: integer("procurement_rfqs_id")
+ .references(() => procurementRfqs.id, { onDelete: "set null" }),
+
+ rfqItem: varchar("rfq_item", { length: 50 }), // 단위
+ prItem: varchar("pr_item", { length: 50 }), // 단위
+ prNo: varchar("pr_no", { length: 50 }), // 단위
+
+ itemId: integer("item_id")
+ .references(() => items.id, { onDelete: "set null" }),
+
+ materialCode: varchar("material_code", { length: 255 }),
+ materialCategory: varchar("material_category", { length: 255 }),
+ acc: varchar("acc", { length: 255 }),
+ materialDescription: varchar("material_description", { length: 255 }),
+ size: varchar("size", { length: 255 }),
+ deliveryDate: date("delivery_date", { mode: "date" })
+ .$type<Date>(),
+ quantity: numeric("quantity", { precision: 12, scale: 2 })
+ .$type<number>()
+ .default(1),
+ uom: varchar("uom", { length: 50 }), // 단위
+ grossWeight: numeric("gross_weight", { precision: 12, scale: 2 })
+ .$type<number>()
+ .default(1),
+ gwUom: varchar("gw_uom", { length: 50 }), // 단위
+
+ specNo: varchar("spec_no", { length: 255 }),
+ specUrl: varchar("spec_url", { length: 255 }),
+ trackingNo: varchar("tracking_no", { length: 255 }),
+
+ majorYn: boolean("major_yn").default(false),
+
+ projectDef: varchar("project_def", { length: 255 }),
+ projectSc: varchar("project_sc", { length: 255 }),
+ projectKl: varchar("project_kl", { length: 255 }),
+ projectLc: varchar("project_lc", { length: 255 }),
+ projectDl: varchar("project_dl", { length: 255 }),
+
+ remark: text("remark"),
+
+ },
+);
+
+
+//view
+export const procurementRfqsView = pgView("procurement_rfqs_view").as((qb) => {
+ const createdByUser = alias(users, "created_by_user");
+ const updatedByUser = alias(users, "updated_by_user");
+ const sentByUser = alias(users, "sent_by_user");
+
+ return qb
+ .select({
+ // Basic RFQ identification
+ id: sql<number>`${procurementRfqs.id}`.as("id"),
+ rfqCode: sql<string>`${procurementRfqs.rfqCode}`.as("rfq_code"),
+ series: sql<string | null>`${procurementRfqs.series}`.as("series"),
+ rfqSealedYn: sql<string | null>`${procurementRfqs.rfqSealedYn}`.as("rfq_sealed_yn"),
+
+ // Project information
+ projectCode: sql<string | null>`${projects.code}`.as("project_code"),
+ projectName: sql<string | null>`${projects.name}`.as("project_name"),
+
+ // Item information
+ itemCode: sql<string | null>`${items.itemCode}`.as("item_code"),
+ itemName: sql<string | null>`${items.itemName}`.as("item_name"),
+
+ // Status and dates
+ status: sql<string>`${procurementRfqs.status}`.as("status"),
+ picCode: sql<string | null>`${procurementRfqs.picCode}`.as("pic_code"),
+ rfqSendDate: sql<Date | null>`${procurementRfqs.rfqSendDate}`.as("rfq_send_date"),
+ dueDate: sql<Date | null>`${procurementRfqs.dueDate}`.as("due_date"),
+
+ // 가장 빠른 견적서 제출 날짜 추가
+ earliestQuotationSubmittedAt: sql<Date | null>`(
+ SELECT MIN(submitted_at)
+ FROM procurement_vendor_quotations
+ WHERE rfq_id = ${procurementRfqs.id}
+ AND submitted_at IS NOT NULL
+ )`.as("earliest_quotation_submitted_at"),
+
+ // Audit information
+ createdByUserName: sql<string | null>`${createdByUser.name}`.as("created_by_user_name"),
+ sentByUserName: sql<string | null>`${sentByUser.name}`.as("sent_by_user_name"),
+ updatedAt: sql<Date>`${procurementRfqs.updatedAt}`.as("updated_at"),
+ updatedByUserName: sql<string | null>`${updatedByUser.name}`.as("updated_by_user_name"),
+ remark: sql<string | null>`${procurementRfqs.remark}`.as("remark"),
+
+ // Related item information
+ majorItemMaterialCode: sql<string | null>`(
+ SELECT material_code
+ FROM pr_items
+ WHERE procurement_rfqs_id = ${procurementRfqs.id}
+ AND major_yn = true
+ LIMIT 1
+ )`.as("major_item_material_code"),
+
+ poNo: sql<string | null>`(
+ SELECT pr_no
+ FROM pr_items
+ WHERE procurement_rfqs_id = ${procurementRfqs.id}
+ AND major_yn = true
+ LIMIT 1
+ )`.as("po_no"),
+
+ prItemsCount: sql<number>`(
+ SELECT COUNT(*)
+ FROM pr_items
+ WHERE procurement_rfqs_id = ${procurementRfqs.id}
+ )`.as("pr_items_count")
+ })
+ .from(procurementRfqs)
+ .leftJoin(projects, eq(procurementRfqs.projectId, projects.id))
+ .leftJoin(items, eq(procurementRfqs.itemId, items.id))
+ .leftJoin(createdByUser, eq(procurementRfqs.createdBy, createdByUser.id))
+ .leftJoin(updatedByUser, eq(procurementRfqs.updatedBy, updatedByUser.id))
+ .leftJoin(sentByUser, eq(procurementRfqs.sentBy, sentByUser.id));
+});
+
+// 수정된 pr_items_view
+export const prItemsView = pgView("pr_items_view").as((qb) => {
+ return qb
+ .select({
+ id: prItems.id,
+ procurementRfqsId: prItems.procurementRfqsId,
+ rfqItem: prItems.rfqItem,
+ prItem: prItems.prItem,
+ prNo: prItems.prNo,
+ itemId: prItems.itemId,
+ materialCode: prItems.materialCode,
+ materialCategory: prItems.materialCategory,
+ acc: prItems.acc,
+ materialDescription: prItems.materialDescription,
+ size: prItems.size,
+ deliveryDate: prItems.deliveryDate,
+ quantity: prItems.quantity,
+ uom: prItems.uom,
+ grossWeight: prItems.grossWeight, // 필드명 수정
+ gwUom: prItems.gwUom,
+ specNo: prItems.specNo,
+ specUrl: prItems.specUrl,
+ trackingNo: prItems.trackingNo,
+ majorYn: prItems.majorYn,
+ projectDef: prItems.projectDef,
+ projectSc: prItems.projectSc,
+ projectKl: prItems.projectKl,
+ projectLc: prItems.projectLc,
+ projectDl: prItems.projectDl,
+ remark: prItems.remark,
+ rfqCode: procurementRfqs.rfqCode,
+ itemCode: items.itemCode,
+ itemName: items.itemName
+ })
+ .from(prItems)
+ .leftJoin(procurementRfqs, eq(prItems.procurementRfqsId, procurementRfqs.id))
+ .leftJoin(items, eq(prItems.itemId, items.id));
+});
+
+export const procurementRfqDetailsView = pgView("procurement_rfq_details_view").as((qb) => {
+ // 기존 별칭 정의 유지
+ const rfqDetailsTable = alias(procurementRfqDetails, "rfq_details");
+ const rfqsTable = alias(procurementRfqs, "rfqs");
+ const projectsTable = alias(projects, "projects");
+ const itemsTable = alias(items, "items");
+ const vendorsTable = alias(vendors, "vendors");
+ const paymentTermsTable = alias(paymentTerms, "payment_terms");
+ const incotermsTable = alias(incoterms, "incoterms");
+ const updatedByUser = alias(users, "updated_by_user");
+
+ return qb
+ .select({
+ // procurementRfqDetails 필드
+ detailId: sql<number>`${rfqDetailsTable.id}`.as("detail_id"),
+ rfqId: sql<number>`${rfqsTable.id}`.as("rfq_id"),
+ rfqCode: sql<string>`${rfqsTable.rfqCode}`.as("rfq_code"),
+
+ // 프로젝트 관련 필드
+ projectCode: sql<string | null>`${projectsTable.code}`.as("project_code"),
+ projectName: sql<string | null>`${projectsTable.name}`.as("project_name"),
+
+ // 아이템 관련 필드
+ itemCode: sql<string | null>`${itemsTable.itemCode}`.as("item_code"),
+ itemName: sql<string | null>`${itemsTable.itemName}`.as("item_name"),
+
+ // 벤더 관련 필드
+ vendorName: sql<string | null>`${vendorsTable.vendorName}`.as("vendor_name"),
+ vendorCode: sql<string | null>`${vendorsTable.vendorCode}`.as("vendor_code"),
+ vendorId: sql<string | null>`${vendorsTable.id}`.as("vendor_id"),
+ vendorCountry: sql<string | null>`${vendorsTable.country}`.as("vendor_country"),
+
+ // RFQ 상세 정보 필드
+ currency: sql<string | null>`${rfqDetailsTable.currency}`.as("currency"),
+ paymentTermsCode: sql<string | null>`${paymentTermsTable.code}`.as("payment_terms_code"),
+ paymentTermsDescription: sql<string | null>`${paymentTermsTable.description}`.as("payment_terms_description"),
+ incotermsCode: sql<string | null>`${incotermsTable.code}`.as("incoterms_code"),
+ incotermsDescription: sql<string | null>`${incotermsTable.description}`.as("incoterms_description"),
+ incotermsDetail: sql<string | null>`${rfqDetailsTable.incotermsDetail}`.as("incoterms_detail"),
+ deliveryDate: sql<Date | null>`${rfqDetailsTable.deliveryDate}`.as("delivery_date"),
+ taxCode: sql<string | null>`${rfqDetailsTable.taxCode}`.as("tax_code"),
+ placeOfShipping: sql<string | null>`${rfqDetailsTable.placeOfShipping}`.as("place_of_shipping"),
+ placeOfDestination: sql<string | null>`${rfqDetailsTable.placeOfDestination}`.as("place_of_destination"),
+ materialPriceRelatedYn: sql<boolean | null>`${rfqDetailsTable.materialPriceRelatedYn}`.as("material_price_related_yn"),
+
+ // 업데이트 관련 필드
+ updatedByUserName: sql<string | null>`${updatedByUser.name}`.as("updated_by_user_name"),
+ updatedAt: sql<Date | null>`${rfqDetailsTable.updatedAt}`.as("updated_at"),
+
+ // 추가적으로 pr_items 개수도 카운트하는 필드 추가
+ prItemsCount: sql<number>`(
+ SELECT COUNT(*)
+ FROM pr_items
+ WHERE procurement_rfqs_id = ${rfqsTable.id}
+ )`.as("pr_items_count"),
+
+ // 메이저 아이템 개수도 카운트
+ majorItemsCount: sql<number>`(
+ SELECT COUNT(*)
+ FROM pr_items
+ WHERE procurement_rfqs_id = ${rfqsTable.id}
+ AND major_yn = true
+ )`.as("major_items_count"),
+
+ // 새로운 필드 추가: 코멘트 수 카운트
+ commentCount: sql<number>`(
+ SELECT COUNT(*)
+ FROM procurement_rfq_comments
+ WHERE rfq_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId}
+ )`.as("comment_count"),
+
+ // 새로운 필드 추가: 최근 코멘트 날짜
+ lastCommentDate: sql<Date | null>`(
+ SELECT created_at
+ FROM procurement_rfq_comments
+ WHERE rfq_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId}
+ ORDER BY created_at DESC LIMIT 1
+ )`.as("last_comment_date"),
+
+ // 새로운 필드 추가: 벤더가 마지막으로 코멘트한 날짜
+ lastVendorCommentDate: sql<Date | null>`(
+ SELECT created_at
+ FROM procurement_rfq_comments
+ WHERE rfq_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId} AND is_vendor_comment = true
+ ORDER BY created_at DESC LIMIT 1
+ )`.as("last_vendor_comment_date"),
+
+ // 새로운 필드 추가: 첨부파일 수 카운트
+ attachmentCount: sql<number>`(
+ SELECT COUNT(*)
+ FROM procurement_rfq_attachments
+ WHERE rfq_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId}
+ )`.as("attachment_count"),
+
+ // 새로운 필드 추가: 견적서 제출 여부
+ hasQuotation: sql<boolean>`(
+ SELECT COUNT(*) > 0
+ FROM procurement_vendor_quotations
+ WHERE rfq_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId}
+ )`.as("has_quotation"),
+
+ // 새로운 필드 추가: 견적서 상태
+ quotationStatus: sql<string | null>`(
+ SELECT status
+ FROM procurement_vendor_quotations
+ WHERE rfq_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId}
+ ORDER BY created_at DESC LIMIT 1
+ )`.as("quotation_status"),
+
+ // 새로운 필드 추가: 견적서 총액
+ quotationTotalPrice: sql<number | null>`(
+ SELECT total_price
+ FROM procurement_vendor_quotations
+ WHERE rfq_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId}
+ ORDER BY created_at DESC LIMIT 1
+ )`.as("quotation_total_price"),
+
+ // 최신 견적서 버전
+ quotationVersion: sql<number | null>`(
+ SELECT quotation_version
+ FROM procurement_vendor_quotations
+ WHERE rfq_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId}
+ ORDER BY quotation_version DESC LIMIT 1
+ )`.as("quotation_version"),
+
+ // 총 견적서 버전 수
+ quotationVersionCount: sql<number>`(
+ SELECT COUNT(DISTINCT quotation_version)
+ FROM procurement_vendor_quotations
+ WHERE rfq_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId}
+ )`.as("quotation_version_count"),
+
+ // 마지막 견적서 생성 날짜
+ lastQuotationDate: sql<Date | null>`(
+ SELECT created_at
+ FROM procurement_vendor_quotations
+ WHERE rfq_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId}
+ ORDER BY quotation_version DESC LIMIT 1
+ )`.as("last_quotation_date"),
+ })
+ .from(rfqDetailsTable)
+ // 기존 조인 유지
+ .leftJoin(rfqsTable, eq(rfqDetailsTable.procurementRfqsId, rfqsTable.id))
+ .leftJoin(projectsTable, eq(rfqsTable.projectId, projectsTable.id))
+ .leftJoin(itemsTable, eq(rfqsTable.itemId, itemsTable.id))
+ .leftJoin(vendorsTable, eq(rfqDetailsTable.vendorsId, vendorsTable.id))
+ .leftJoin(paymentTermsTable, eq(rfqDetailsTable.paymentTermsCode, paymentTermsTable.code))
+ .leftJoin(incotermsTable, eq(rfqDetailsTable.incotermsCode, incotermsTable.code))
+ .leftJoin(updatedByUser, eq(rfqDetailsTable.updatedBy, updatedByUser.id));
+});
+
+export const procurementAttachments = pgTable(
+ "procurement_attachments",
+ {
+ id: serial("id").primaryKey(),
+ attachmentType: varchar("attachment_type", { length: 50 }).notNull(), // 'RFQ_COMMON', 'VENDOR_SPECIFIC'
+ procurementRfqsId: integer("procurement_rfqs_id")
+ .references(() => procurementRfqs.id, { onDelete: "cascade" }),
+ procurementRfqDetailsId: integer("procurement_rfq_details_id")
+ .references(() => procurementRfqDetails.id, { onDelete: "cascade" }),
+ fileName: varchar("file_name", { length: 255 }).notNull(),
+ originalFileName: varchar("original_file_name", { length: 255 }).notNull(),
+ filePath: varchar("file_path", { length: 512 }).notNull(),
+ fileSize: integer("file_size"),
+ fileType: varchar("file_type", { length: 100 }),
+ description: varchar("description", { length: 500 }),
+ createdBy: integer("created_by")
+ .references(() => users.id, { onDelete: "set null" })
+ .notNull(),
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ },
+ (table) => ({
+ attachmentTypeCheck: check(
+ "attachment_type_check",
+ sql`${table.procurementRfqsId} IS NOT NULL OR ${table.procurementRfqDetailsId} IS NOT NULL`
+ )
+ })
+);
+
+export type ProcurementRfqsView = typeof procurementRfqsView.$inferSelect;
+export type PrItemsView = typeof prItemsView.$inferSelect;
+export type ProcurementRfqDetailsView = typeof procurementRfqDetailsView.$inferSelect;
+
+
+//vendor response
+export const procurementVendorQuotations = pgTable(
+ "procurement_vendor_quotations",
+ {
+ id: serial("id").primaryKey(),
+ rfqId: integer("rfq_id")
+ .notNull()
+ .references(() => procurementRfqs.id, { onDelete: "cascade" }),
+ vendorId: integer("vendor_id")
+ .notNull()
+ .references(() => vendors.id, { onDelete: "set null" }),
+
+ // 견적 요약 정보
+ quotationCode: varchar("quotation_code", { length: 50 }),
+ quotationVersion: integer("quotation_version").default(1),
+ totalItemsCount: integer("total_items_count").default(0),
+ subTotal: numeric("sub_total").default("0"),
+ taxTotal: numeric("tax_total").default("0"),
+ discountTotal: numeric("discount_total").default("0"),
+ totalPrice: numeric("total_price").default("0"),
+ currency: varchar("currency", { length: 10 }).default("USD"),
+
+ // 견적 유효성 및 배송 정보
+ validUntil: date("valid_until", { mode: "date" }).$type<Date>(),
+ estimatedDeliveryDate: date("estimated_delivery_date", { mode: "date" }).$type<Date>(),
+
+ // 지불 조건 등 상세 정보
+ paymentTermsCode: varchar("payment_terms_code", { length: 50 })
+ .references(() => paymentTerms.code, { onDelete: "set null" }),
+ incotermsCode: varchar("incoterms_code", { length: 20 })
+ .references(() => incoterms.code, { onDelete: "set null" }),
+ incotermsDetail: varchar("incoterms_detail", { length: 255 }),
+
+ // 상태 관리
+ status: varchar("status", { length: 30 })
+ .$type<"Draft" | "Submitted" | "Revised" | "Rejected" | "Accepted">()
+ .default("Draft")
+ .notNull(),
+
+ // 기타 정보
+ remark: text("remark"),
+ rejectionReason: text("rejection_reason"),
+ submittedAt: timestamp("submitted_at"),
+ acceptedAt: timestamp("accepted_at"),
+
+ // 감사 필드
+ createdBy: integer("created_by"),
+ updatedBy: integer("updated_by"),
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ updatedAt: timestamp("updated_at").defaultNow().notNull(),
+ }
+);
+
+export const procurementRfqComments = pgTable(
+ "procurement_rfq_comments",
+ {
+ id: serial("id").primaryKey(),
+ rfqId: integer("rfq_id")
+ .notNull()
+ .references(() => procurementRfqs.id, { onDelete: "cascade" }),
+ vendorId: integer("vendor_id")
+ .references(() => vendors.id, { onDelete: "set null" }),
+ userId: integer("user_id")
+ .references(() => users.id, { onDelete: "set null" }),
+ content: text("content").notNull(),
+ isVendorComment: boolean("is_vendor_comment").default(false),
+ isRead: boolean("is_read").default(false), // 읽음 상태 추가
+ parentCommentId: integer("parent_comment_id"),
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ updatedAt: timestamp("updated_at").defaultNow().notNull(),
+ },
+ // 자기참조 FK 정의
+ (table) => {
+ return {
+ parentFk: foreignKey({
+ columns: [table.parentCommentId],
+ foreignColumns: [table.id],
+ }).onDelete("set null"),
+ };
+ }
+);
+
+export const procurementRfqAttachments = pgTable(
+ "procurement_rfq_attachments",
+ {
+ id: serial("id").primaryKey(),
+ rfqId: integer("rfq_id")
+ .notNull()
+ .references(() => procurementRfqs.id, { onDelete: "cascade" }),
+ commentId: integer("comment_id")
+ .references(() => procurementRfqComments.id, { onDelete: "cascade" }),
+ quotationId: integer("quotation_id")
+ .references(() => procurementVendorQuotations.id, { onDelete: "cascade" }),
+ fileName: varchar("file_name", { length: 255 }).notNull(),
+ fileSize: integer("file_size").notNull(),
+ fileType: varchar("file_type", { length: 100 }),
+ filePath: varchar("file_path", { length: 500 }).notNull(),
+ isVendorUpload: boolean("is_vendor_upload").default(false),
+ uploadedBy: integer("uploaded_by")
+ .references(() => users.id, { onDelete: "set null" }),
+ vendorId: integer("vendor_id")
+ .references(() => vendors.id, { onDelete: "set null" }),
+ uploadedAt: timestamp("uploaded_at").defaultNow().notNull(),
+ }
+);
+
+export const procurementQuotationItems = pgTable(
+ "procurement_quotation_items",
+ {
+ id: serial("id").primaryKey(),
+ quotationId: integer("quotation_id")
+ .notNull()
+ .references(() => procurementVendorQuotations.id, { onDelete: "cascade" }),
+
+ // PR 아이템과의 연결 추가
+ prItemId: integer("pr_item_id")
+ .notNull()
+ .references(() => prItems.id, { onDelete: "cascade" }),
+
+ // 원본 PR 아이템 정보 참조 (읽기 전용)
+ materialCode: varchar("material_code", { length: 50 }),
+ materialDescription: varchar("material_description", { length: 255 }),
+ quantity: numeric("quantity").notNull(),
+ uom: varchar("uom", { length: 20 }),
+
+ // 벤더가 입력하는 정보
+ unitPrice: numeric("unit_price").notNull(),
+ totalPrice: numeric("total_price").notNull(),
+ currency: varchar("currency", { length: 10 }).default("USD"),
+ vendorMaterialCode: varchar("vendor_material_code", { length: 50 }),
+ vendorMaterialDescription: varchar("vendor_material_description", { length: 255 }),
+
+ // 배송 관련 정보
+ deliveryDate: date("delivery_date", { mode: "date" }).$type<Date>(),
+ leadTimeInDays: integer("lead_time_in_days"),
+
+ // 세금 및 기타 정보
+ taxRate: numeric("tax_rate"),
+ taxAmount: numeric("tax_amount"),
+ discountRate: numeric("discount_rate"),
+ discountAmount: numeric("discount_amount"),
+
+ // 기타 정보
+ remark: text("remark"),
+ isAlternative: boolean("is_alternative").default(false),
+ isRecommended: boolean("is_recommended").default(false),
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ updatedAt: timestamp("updated_at").defaultNow().notNull(),
+ }
+);
+
+
+// procurementRfqComments 테이블의 관계 정의 - 타입 명시
+export const procurementRfqCommentsRelations = relations(
+ procurementRfqComments,
+ ({ one, many }) => ({
+ user: one(users, {
+ fields: [procurementRfqComments.userId],
+ references: [users.id],
+ }),
+ vendor: one(vendors, {
+ fields: [procurementRfqComments.vendorId],
+ references: [vendors.id],
+ }),
+ rfq: one(procurementRfqs, {
+ fields: [procurementRfqComments.rfqId],
+ references: [procurementRfqs.id],
+ }),
+ parentComment: one(procurementRfqComments, {
+ fields: [procurementRfqComments.parentCommentId],
+ references: [procurementRfqComments.id],
+ relationName: "commentHierarchy",
+ }),
+ childComments: many(procurementRfqComments, { relationName: "commentHierarchy" }),
+ attachments: many(procurementRfqAttachments, { relationName: "commentAttachments" }),
+ })
+);
+
+export const procurementRfqAttachmentsRelations = relations(
+ procurementRfqAttachments,
+ ({ one }) => ({
+ comment: one(procurementRfqComments, {
+ fields: [procurementRfqAttachments.commentId],
+ references: [procurementRfqComments.id],
+ relationName: "commentAttachments",
+ }),
+ rfq: one(procurementRfqs, {
+ fields: [procurementRfqAttachments.rfqId],
+ references: [procurementRfqs.id],
+ }),
+ uploader: one(users, {
+ fields: [procurementRfqAttachments.uploadedBy],
+ references: [users.id],
+ }),
+ vendor: one(vendors, {
+ fields: [procurementRfqAttachments.vendorId],
+ references: [vendors.id],
+ }),
+ })
+);
+
+
+export const procurementRfqsRelations = relations(
+ procurementRfqs,
+ ({ one, many }) => ({
+ project: one(projects, {
+ fields: [procurementRfqs.projectId],
+ references: [projects.id],
+ }),
+ item: one(items, {
+ fields: [procurementRfqs.itemId],
+ references: [items.id],
+ }),
+ createdByUser: one(users, {
+ fields: [procurementRfqs.createdBy],
+ references: [users.id],
+ relationName: "rfqCreator",
+ }),
+ updatedByUser: one(users, {
+ fields: [procurementRfqs.updatedBy],
+ references: [users.id],
+ relationName: "rfqUpdater",
+ }),
+ rfqDetails: many(procurementRfqDetails),
+ prItems: many(prItems, { relationName: "rfqPrItems" }),
+ })
+);
+
+export const prItemsRelations = relations(
+ prItems,
+ ({ one }) => ({
+ rfq: one(procurementRfqs, {
+ fields: [prItems.procurementRfqsId],
+ references: [procurementRfqs.id],
+ relationName: "rfqPrItems"
+ }),
+ })
+);
+
+// procurementRfqDetails 테이블의 관계 정의
+export const procurementRfqDetailsRelations = relations(
+ procurementRfqDetails,
+ ({ one }) => ({
+ rfq: one(procurementRfqs, {
+ fields: [procurementRfqDetails.procurementRfqsId],
+ references: [procurementRfqs.id],
+ }),
+ vendor: one(vendors, {
+ fields: [procurementRfqDetails.vendorsId],
+ references: [vendors.id],
+ }),
+ paymentTerms: one(paymentTerms, {
+ fields: [procurementRfqDetails.paymentTermsCode],
+ references: [paymentTerms.code],
+ }),
+ incoterms: one(incoterms, {
+ fields: [procurementRfqDetails.incotermsCode],
+ references: [incoterms.code],
+ }),
+ updatedByUser: one(users, {
+ fields: [procurementRfqDetails.updatedBy],
+ references: [users.id],
+ }),
+ })
+);
+
+export const vendorsRelations = relations(
+ vendors,
+ ({ many }) => ({
+ users: many(users, {
+ relationName: "vendorUsers"
+ }),
+ })
+);
+
+export const usersRelations = relations(
+ users,
+ ({ one }) => ({
+ vendor: one(vendors, {
+ fields: [users.companyId],
+ references: [vendors.id],
+ relationName: "vendorUsers"
+ }),
+ })
+);
+
+export const procurementVendorQuotationsRelations = relations(
+ procurementVendorQuotations,
+ ({ one, many }) => ({
+ rfq: one(procurementRfqs, {
+ fields: [procurementVendorQuotations.rfqId],
+ references: [procurementRfqs.id],
+ }),
+ vendor: one(vendors, {
+ fields: [procurementVendorQuotations.vendorId],
+ references: [vendors.id],
+ }),
+ // Add this missing relation
+ items: many(procurementQuotationItems),
+ // These are optional but good to have
+ paymentTerms: one(paymentTerms, {
+ fields: [procurementVendorQuotations.paymentTermsCode],
+ references: [paymentTerms.code],
+ }),
+ incoterms: one(incoterms, {
+ fields: [procurementVendorQuotations.incotermsCode],
+ references: [incoterms.code],
+ }),
+ })
+);
+
+export const procurementQuotationItemsRelations = relations(
+ procurementQuotationItems,
+ ({ one }) => ({
+ quotation: one(procurementVendorQuotations, {
+ fields: [procurementQuotationItems.quotationId],
+ references: [procurementVendorQuotations.id],
+ }),
+ prItem: one(prItems, {
+ fields: [procurementQuotationItems.prItemId],
+ references: [prItems.id],
+ }),
+ })
+);
+
+export type ProcurementVendorQuotations = typeof procurementVendorQuotations.$inferSelect;
diff --git a/db/schema/users.ts b/db/schema/users.ts
index 843ee2f3..8346c24b 100644
--- a/db/schema/users.ts
+++ b/db/schema/users.ts
@@ -16,6 +16,8 @@ export const users = pgTable("users", {
.defaultNow()
.notNull(),
imageUrl: varchar("image_url", { length: 1024 }),
+ language: varchar("language", { length: 10 }).default("en"), // 언어 필드 추가 (기본값: 영어)
+
});
diff --git a/db/schema/vendors.ts b/db/schema/vendors.ts
index f56a1288..182b7037 100644
--- a/db/schema/vendors.ts
+++ b/db/schema/vendors.ts
@@ -59,6 +59,8 @@ export const vendors = pgTable("vendors", {
creditRating: varchar("credit_rating", { length: 50 }),
cashFlowRating: varchar("cash_flow_rating", { length: 50 }),
+ businessSize: varchar("business_size", { length: 255 }),
+
createdAt: timestamp("created_at").defaultNow().notNull(),
updatedAt: timestamp("updated_at").defaultNow().notNull(),
});
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-07 16:06:59 +0000