diff options
Diffstat (limited to 'db/schema/rfqLast.ts')
| -rw-r--r-- | db/schema/rfqLast.ts | 83 |
1 files changed, 57 insertions, 26 deletions
diff --git a/db/schema/rfqLast.ts b/db/schema/rfqLast.ts index 56cc0c35..615e57f4 100644 --- a/db/schema/rfqLast.ts +++ b/db/schema/rfqLast.ts @@ -1,4 +1,4 @@ -import { pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, alias, check, uniqueIndex } from "drizzle-orm/pg-core"; +import { index, pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, alias, check, uniqueIndex } from "drizzle-orm/pg-core"; import { eq, sql, relations } from "drizzle-orm"; import { projects } from "./projects"; import { users } from "./users"; @@ -40,12 +40,10 @@ export const rfqsLast = pgTable( // 아래 컬럼은 대표 자재코드, 대표 자재명으로 사용 itemCode: varchar("item_code", { length: 100 }), itemName: varchar("item_name", { length: 255 }), - - dueDate: date("due_date", { mode: "date" }) - .$type<Date>(), // 인터페이스한 값은 dueDate가 없으므로 notNull 제약조건 제거 - - rfqSendDate: date("rfq_send_date", { mode: "date" }) - .$type<Date | null>(), // notNull() 제약조건 제거, null 허용 (ECC에서 수신 후 보내지 않은 RFQ) + dueDate: timestamp("due_date", { withTimezone: true }) + .$type<Date | null>(), + rfqSendDate: timestamp("rfq_send_date", { withTimezone: true }) + .$type<Date | null>(), status: varchar("status", { length: 30 }) .$type<RfqStatus>() @@ -113,23 +111,21 @@ export const rfqLastDetails = pgTable( 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>(), - + + deliveryDate: timestamp("delivery_date", { withTimezone: true }) + .$type<Date | null>(), + contractDuration: varchar("contract_duration", { length: 255 }), - - + taxCode: varchar("tax_code", { length: 255 }), placeOfShipping: varchar("place_of_shipping", { length: 255 }), placeOfDestination: varchar("place_of_destination", { length: 255 }), @@ -139,25 +135,50 @@ export const rfqLastDetails = pgTable( .notNull() .references(() => users.id, { onDelete: "set null" }), updatedAt: timestamp("updated_at").defaultNow().notNull(), - + shortList: boolean('short_list').notNull().default(false), returnYn: boolean('return_yn').notNull().default(false), returnedAt: timestamp("returned_at").defaultNow(), - - - prjectGtcYn: boolean('prject_gtc_yn').notNull().default(false), + + projectGtcYn: boolean('project_gtc_yn').notNull().default(false), generalGtcYn: boolean('general_gtc_yn').notNull().default(false), ndaYn: boolean('nda_yn').notNull().default(false), agreementYn: boolean('agreement_yn').notNull().default(false), - + gtcType: varchar("gtc_type", { length: 30 }) + .$type<"general" | "project" | "none" | null>() + .default("none"), + materialPriceRelatedYn: boolean("material_price_related_yn").default(false), sparepartYn: boolean("sparepart_yn").default(false), firstYn: boolean("first_yn").default(false), - + firstDescription: text("first_description"), sparepartDescription: text("sparepart_escription"), - + sendVersion: integer("send_version").default(0), + isLatest: boolean("is_latest").notNull().default(true), + + // 이메일 발송 추적 필드 + emailSentAt: timestamp("email_sent_at"), + emailSentTo: text("email_sent_to"), + emailResentCount: integer("email_resent_count").default(0), + lastEmailSentAt: timestamp("last_email_sent_at"), + emailStatus: varchar("email_status", { length: 30 }) + .$type<"pending" | "sent" | "failed" | "bounced" | null>(), + }, + (table) => { + return { + // 복합 유니크 인덱스: rfqsLastId + vendorsId + isLatest(true) + // partial unique index를 사용하여 isLatest=true인 경우에만 유니크 제약 + uniqueLatestVendor: uniqueIndex("unique_latest_vendor") + .on(table.rfqsLastId, table.vendorsId) + .where(sql`${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), + }; } ); @@ -177,7 +198,7 @@ export const rfqPrItems = pgTable( materialCode: varchar("material_code", { length: 255 }), materialCategory: varchar("material_category", { length: 255 }),//자재그룹코드 - + acc: varchar("acc", { length: 255 }), materialDescription: varchar("material_description", { length: 255 }), @@ -330,7 +351,7 @@ export const rfqsLastView = pgView("rfqs_last_view").as((qb) => { LIMIT 1 )`.as("major_item_materialD_description"), - + majorItemMaterialCategory: sql<string | null>`( SELECT material_category FROM rfq_pr_items @@ -433,10 +454,11 @@ export const rfqLastDetailsView = pgView("rfq_last_details_view").as((qb) => { returnedAt: sql<Date | null>`${rfqDetailsTable.returnedAt}`.as("returned_at"), // GTC/NDA/Agreement 필드 - prjectGtcYn: sql<boolean>`${rfqDetailsTable.prjectGtcYn}`.as("prject_gtc_yn"), + projectGtcYn: sql<boolean>`${rfqDetailsTable.projectGtcYn}`.as("project_gtc_yn"), generalGtcYn: sql<boolean>`${rfqDetailsTable.generalGtcYn}`.as("general_gtc_yn"), ndaYn: sql<boolean>`${rfqDetailsTable.ndaYn}`.as("nda_yn"), agreementYn: sql<boolean>`${rfqDetailsTable.agreementYn}`.as("agreement_yn"), + gtcType: sql<string | null>`${rfqDetailsTable.gtcType}`.as("gtc_type"), // 추가 YN 필드들 materialPriceRelatedYn: sql<boolean | null>`${rfqDetailsTable.materialPriceRelatedYn}`.as("material_price_related_yn"), @@ -449,6 +471,15 @@ export const rfqLastDetailsView = pgView("rfq_last_details_view").as((qb) => { firstDescription: sql<string | null>`${rfqDetailsTable.firstDescription}`.as("first_description"), sparepartDescription: sql<string | null>`${rfqDetailsTable.sparepartDescription}`.as("sparepart_description"), + isLatest: sql<boolean>`${rfqDetailsTable.isLatest}`.as("is_latest"), + + sendVersion: sql<string | null>`${rfqDetailsTable.sendVersion}`.as("send_version"), + emailSentAt: sql<string | null>`${rfqDetailsTable.emailSentAt}`.as("email_sent_at"), + emailSentTo: sql<string | null>`${rfqDetailsTable.emailSentTo}`.as("email_sent_to"), + emailResentCount: sql<number | null>`${rfqDetailsTable.emailResentCount}`.as("email_resent_count"), + lastEmailSentAt: sql<string | null>`${rfqDetailsTable.lastEmailSentAt}`.as("last_email_sent_at"), + emailStatus: sql<string | null>`${rfqDetailsTable.emailStatus}`.as("email_status"), + // 업데이트 관련 필드 |