스키마 조정

1 files changed, 100 insertions, 1 deletions

diff --git a/db/schema/contract.ts b/db/schema/contract.ts
index cc1f8c57..1d628442 100644
--- a/db/schema/contract.ts
+++ b/db/schema/contract.ts
@@ -331,4 +331,103 @@ export const contractsDetailView = pgView("contracts_detail_view").as((qb) => {
   export type ContractDetailParsed = Omit<ContractDetail, "envelopes" | "items"> & {
     envelopes: Envelope[]
     items: ContractItemParsed[]
-  }
\ No newline at end of file
+  }
+
+  // ============ poa (Purchase Order Amendment) ============
+export const poa = pgTable("poa", {
+  // 주 키
+  id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
+
+  // Form code는 원본과 동일하게 유지
+  contractNo: varchar("contract_no", { length: 100 }).notNull(),
+
+  // 원본 PO 참조
+  originalContractNo: varchar("original_contract_no", { length: 100 })
+      .notNull()
+      .references(() => contracts.contractNo, { onDelete: "cascade" }),
+
+  // 원본 계약 정보
+  projectId: integer("project_id")
+      .notNull()
+      .references(() => projects.id, { onDelete: "cascade" }),
+  vendorId: integer("vendor_id")
+      .notNull()
+      .references(() => vendors.id, { onDelete: "cascade" }),
+  originalContractName: varchar("original_contract_name", { length: 255 }).notNull(),
+  originalStatus: varchar("original_status", { length: 50 }).notNull(),
+
+  // 변경된 납품 조건
+  deliveryTerms: text("delivery_terms"),      // 변경된 납품 조건
+  deliveryDate: date("delivery_date"),        // 변경된 납품 기한
+  deliveryLocation: varchar("delivery_location", { length: 255 }), // 변경된 납품 장소
+
+  // 변경된 가격/금액 관련
+  currency: varchar("currency", { length: 10 }),  // 변경된 통화
+  totalAmount: numeric("total_amount", { precision: 12, scale: 2 }), // 변경된 총 금액
+  discount: numeric("discount", { precision: 12, scale: 2 }), // 변경된 할인
+  tax: numeric("tax", { precision: 12, scale: 2 }),          // 변경된 세금
+  shippingFee: numeric("shipping_fee", { precision: 12, scale: 2 }), // 변경된 배송비
+  netTotal: numeric("net_total", { precision: 12, scale: 2 }),  // 변경된 순 총액
+
+  // 변경 사유
+  changeReason: text("change_reason"),
+
+  // 승인 상태
+  approvalStatus: varchar("approval_status", { length: 50 }).default("PENDING"),
+
+  // 생성/수정 시각
+  createdAt: timestamp("created_at").defaultNow().notNull(),
+  updatedAt: timestamp("updated_at").defaultNow().notNull(),
+})
+
+// 타입 추론
+export type POA = typeof poa.$inferSelect
+
+// ============ poa_detail_view ============
+export const poaDetailView = pgView("poa_detail_view").as((qb) => {
+  return qb
+      .select({
+          // POA primary information
+          id: poa.id,
+          contractNo: poa.contractNo,
+          projectId: contracts.projectId,
+          vendorId: contracts.vendorId,
+          changeReason: poa.changeReason,
+          approvalStatus: poa.approvalStatus,
+
+          // Original PO information
+          originalContractName: sql<string>`${contracts.contractName}`.as('original_contract_name'),
+          originalStatus: sql<string>`${contracts.status}`.as('original_status'),
+          originalStartDate: sql<Date>`${contracts.startDate}`.as('original_start_date'),
+          originalEndDate: sql<Date>`${contracts.endDate}`.as('original_end_date'),
+
+          // Changed delivery details
+          deliveryTerms: poa.deliveryTerms,
+          deliveryDate: poa.deliveryDate,
+          deliveryLocation: poa.deliveryLocation,
+
+          // Changed financial information
+          currency: poa.currency,
+          totalAmount: poa.totalAmount,
+          discount: poa.discount,
+          tax: poa.tax,
+          shippingFee: poa.shippingFee,
+          netTotal: poa.netTotal,
+
+          // Timestamps
+          createdAt: poa.createdAt,
+          updatedAt: poa.updatedAt,
+
+          // Electronic signature status
+          hasSignature: sql<boolean>`EXISTS (
+              SELECT 1 
+              FROM ${contractEnvelopes} 
+              WHERE ${contractEnvelopes.contractId} = ${poa.id}
+          )`.as('has_signature'),
+      })
+      .from(poa)
+      .leftJoin(contracts, eq(poa.contractNo, contracts.contractNo))
+});
+
+// Type inference for the view
+export type POADetail = typeof poaDetailView.$inferSelect;
\ No newline at end of file