diff options
Diffstat (limited to 'db/schema')
| -rw-r--r-- | db/schema/contract.ts | 49 | ||||
| -rw-r--r-- | db/schema/rfq.ts | 13 | ||||
| -rw-r--r-- | db/schema/vendorData.ts | 21 |
3 files changed, 71 insertions, 12 deletions
diff --git a/db/schema/contract.ts b/db/schema/contract.ts index 3c29f0d0..10721b4d 100644 --- a/db/schema/contract.ts +++ b/db/schema/contract.ts @@ -135,31 +135,31 @@ export const contractEnvelopes = pgTable("contract_envelopes", { // 하나의 Envelope에 여러 서명자(사인 요청 대상)가 있을 수 있음 export const contractSigners = pgTable("contract_signers", { id: integer("id").primaryKey().generatedAlwaysAsIdentity(), - + // Envelope와 1:N 관계 envelopeId: integer("envelope_id") .notNull() .references(() => contractEnvelopes.id, { onDelete: "cascade" }), - + // Reference to vendor_contacts table (optional - if signer is from vendor contacts) vendorContactId: integer("vendor_contact_id") .references(() => vendorContacts.id), - + // Is this signer from the requester (company) side or vendor side - signerType: varchar("signer_type", { + signerType: varchar("signer_type", { length: 20, enum: ["REQUESTER", "VENDOR"] }).notNull().default("VENDOR"), - + // 서명자 정보 (manual entry or populated from vendor contact) signerEmail: varchar("signer_email", { length: 255 }).notNull(), signerName: varchar("signer_name", { length: 100 }).notNull(), signerPosition: varchar("signer_position", { length: 100 }), - + // 서명자별 상태 (sent, delivered, signed, declined, etc.) signerStatus: varchar("signer_status", { length: 50 }).default("PENDING"), signedAt: timestamp("signed_at"), - + // 생성/수정 시각 createdAt: timestamp("created_at").defaultNow().notNull(), updatedAt: timestamp("updated_at").defaultNow().notNull(), @@ -210,13 +210,46 @@ export const contractsDetailView = pgView("contracts_detail_view").as((qb) => { // Timestamps createdAt: contracts.createdAt, updatedAt: contracts.updatedAt, - + // Electronic signature status - ADDED .as('has_signature') here hasSignature: sql<boolean>`EXISTS ( SELECT 1 FROM ${contractEnvelopes} WHERE ${contractEnvelopes.contractId} = ${contracts.id} )`.as('has_signature'), + // --- 전자서명 이력 (Envelope) + 서명자(Signer)를 JSON 으로 중첩한 배열 --- + envelopes: sql<string>`COALESCE(( + SELECT json_agg( + json_build_object( + 'id', ce.id, + 'envelopeId', ce.envelope_id, + 'documentId', ce.document_id, + 'envelopeStatus', ce.envelope_status, + 'fileName', ce.file_name, + 'filePath', ce.file_path, + 'createdAt', ce.created_at, + 'updatedAt', ce.updated_at, + 'signers', ( + SELECT json_agg( + json_build_object( + 'id', cs.id, + 'vendorContactId', cs.vendor_contact_id, + 'signerType', cs.signer_type, + 'signerEmail', cs.signer_email, + 'signerName', cs.signer_name, + 'signerPosition', cs.signer_position, + 'signerStatus', cs.signer_status, + 'signedAt', cs.signed_at + ) + ) + FROM ${contractSigners} AS cs + WHERE cs.envelope_id = ce.id + ) + ) + ) + FROM ${contractEnvelopes} AS ce + WHERE ce.contract_id = ${contracts.id} + ), '[]')`.as("envelopes") }) .from(contracts) .leftJoin(projects, eq(contracts.projectId, projects.id)) diff --git a/db/schema/rfq.ts b/db/schema/rfq.ts index fe5581ce..98c4245c 100644 --- a/db/schema/rfq.ts +++ b/db/schema/rfq.ts @@ -81,6 +81,9 @@ export const rfqAttachments = pgTable("rfq_attachments", { evaluationId: integer("evaluation_id") .references(() => rfqEvaluations.id) .$type<number | null>(), + cbeId: integer("cbe_id") + .references(() => cbeEvaluations.id) + .$type<number | null>(), createdAt: timestamp("created_at").defaultNow().notNull(), commentId: integer("comment_id") .references(() => rfqComments.id) @@ -92,11 +95,15 @@ export const rfqComments = pgTable("rfq_comments", { rfqId: integer("rfq_id").references(() => rfqs.id), vendorId: integer("vendor_id").references(() => vendors.id), commentText: text("comment_text").notNull(), - commentedBy: integer("commented_by").notNull(), + commentedBy: integer("commented_by") + .references(() => users.id) // users 테이블의 id 필드 참조 추가 + .notNull(), // 아래처럼, 평가(TBE/CBE)에 속한 코멘트인지 여부를 구분할 필드 - evaluationId: integer("evaluation_id").references(() => rfqEvaluations.id) + evaluationId: integer("evaluation_id") + .references(() => rfqEvaluations.id) .$type<number | null>(), - cbeId: integer("evaluation_id").references(() => cbeEvaluations.id) + cbeId: integer("cbe_id") + .references(() => cbeEvaluations.id) .$type<number | null>(), createdAt: timestamp("created_at").defaultNow().notNull(), updatedAt: timestamp("updated_at").defaultNow().notNull(), diff --git a/db/schema/vendorData.ts b/db/schema/vendorData.ts index 048e5ef8..2739e8eb 100644 --- a/db/schema/vendorData.ts +++ b/db/schema/vendorData.ts @@ -238,11 +238,19 @@ import { createdAt: timestamp("created_at", { withTimezone: true }), updatedAt: timestamp("updated_at", { withTimezone: true }), +<<<<<<< HEAD }); export type ViewTagSubfields = typeof viewTagSubfields.$inferSelect; export const vendorDataReportTemps = pgTable("vendor_data_report_temps", { +======= +}) + +export type ViewTagSubfields = typeof viewTagSubfields.$inferSelect + +export const vendorDataReportTemps = pgTable("vendor_data_report_temps", { +>>>>>>> dev id: serial("id").primaryKey(), contractItemId: integer("contract_item_id") .notNull() @@ -253,6 +261,7 @@ import { fileName: varchar("file_name", { length: 255 }).notNull(), filePath: varchar("file_path", { length: 1024 }).notNull(), createdAt: timestamp("created_at", { withTimezone: true }) +<<<<<<< HEAD .defaultNow() .notNull(), updatedAt: timestamp("updated_at", { withTimezone: true }) @@ -261,4 +270,14 @@ import { }); export type VendorDataReportTemps = typeof vendorDataReportTemps.$inferSelect; -
\ No newline at end of file + +======= + .defaultNow() + .notNull(), + updatedAt: timestamp("updated_at", { withTimezone: true }) + .defaultNow() + .notNull(), + }); + + export type VendorDataReportTemps = typeof vendorDataReportTemps.$inferSelect; +>>>>>>> dev |