initial commit

1 files changed, 227 insertions, 0 deletions

diff --git a/db/schema/contract.ts b/db/schema/contract.ts
new file mode 100644
index 00000000..3c29f0d0
--- /dev/null
+++ b/db/schema/contract.ts
@@ -0,0 +1,227 @@
+import {
+    pgTable,
+    text,
+    varchar,
+    timestamp,
+    integer,
+    numeric,
+    date,
+    boolean,
+    unique,
+    jsonb,
+    uniqueIndex, pgView
+} from "drizzle-orm/pg-core"
+import { projects } from "./projects"
+import { vendorContacts, vendors } from "./vendors"
+import { eq, sql } from "drizzle-orm";
+
+// ============ contracts (계약/PO 정보) ============
+export const contracts = pgTable("contracts", {
+    // 주 키
+    id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
+
+    // 프로젝트와 벤더 참조
+    projectId: integer("project_id")
+        .notNull()
+        .references(() => projects.id, { onDelete: "cascade" }),
+
+    vendorId: integer("vendor_id")
+        .notNull()
+        .references(() => vendors.id, { onDelete: "cascade" }),
+
+    // 계약/PO 번호(유니크)
+    contractNo: varchar("contract_no", { length: 100 }).notNull().unique(),
+    contractName: varchar("contract_name", { length: 255 }).notNull(),
+
+    // 계약/PO 상태나 기간
+    status: varchar("status", { length: 50 }).notNull().default("ACTIVE"),
+    startDate: date("start_date"),      // 발주일(혹은 유효 시작일)
+    endDate: date("end_date"),          // 계약 종료일/유효 기간 등
+
+    // --- PO에 자주 쓰이는 필드 추가 ---
+    paymentTerms: text("payment_terms"),        // 결제 조건(예: 30일 후 현금, 선금/잔금 등)
+    deliveryTerms: text("delivery_terms"),      // 납품 조건(Incoterms 등)
+    deliveryDate: date("delivery_date"),        // 납품 기한(납기 예정일)
+    deliveryLocation: varchar("delivery_location", { length: 255 }), // 납품 장소
+
+    // 가격/금액 관련
+    currency: varchar("currency", { length: 10 }).default("KRW"),  // 통화 (KRW, USD 등)
+    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 }),  // (합계) - (할인) + (세금) + (배송비)
+
+    // 부분 납품/부분 결제 가능 여부
+    partialShippingAllowed: boolean("partial_shipping_allowed").default(false),
+    partialPaymentAllowed: boolean("partial_payment_allowed").default(false),
+
+    // 추가 메모/비고
+    remarks: text("remarks"),
+
+    // 버전 관리 (PO 재발행 등)
+    version: integer("version").default(1),
+
+    // 생성/수정 시각
+    createdAt: timestamp("created_at").defaultNow().notNull(),
+    updatedAt: timestamp("updated_at").defaultNow().notNull(),
+})
+
+// 타입 추론
+export type Contract = typeof contracts.$inferSelect
+
+// ============ contract_items (1:N 관계) ============
+// 한 계약(contracts.id)에 여러 아이템을 연결
+export const contractItems = pgTable("contract_items", {
+    id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
+
+    contractId: integer("contract_id")
+        .notNull()
+        .references(() => contracts.id, { onDelete: "cascade" }),
+
+    itemId: integer("item_id")
+        .notNull()
+    // .references(() => items.id, { onDelete: "cascade" })
+    ,
+
+    // --- 품목(아이템) 단위 정보 ---
+    description: text("description"), // 품목 설명 (스펙, 모델명 등)
+    quantity: integer("quantity").notNull().default(1),
+    unitPrice: numeric("unit_price", { precision: 10, scale: 2 }),
+
+    taxRate: numeric("tax_rate", { precision: 5, scale: 2 }),   // % (예: 10.00)
+    taxAmount: numeric("tax_amount", { precision: 10, scale: 2 }), // 계산된 세금
+    totalLineAmount: numeric("total_line_amount", { precision: 12, scale: 2 }), // (수량×단가±할인+세금) 등
+
+    // 비고
+    remark: text("remark"),
+
+    // 생성/수정 시각
+    createdAt: timestamp("created_at").defaultNow().notNull(),
+    updatedAt: timestamp("updated_at").defaultNow().notNull(),
+}, (t) => [
+    // 같은 계약 + 같은 itemId에 대한 중복을 막음
+    unique().on(t.contractId, t.itemId),
+    uniqueIndex("contract_items_contract_item_idx").on(t.contractId, t.itemId),
+])
+
+export type ContractItem = typeof contractItems.$inferSelect
+
+// ============ DocuSign 연동용 (전자서명 이력) ============
+
+// Envelope(전자서명 요청) 테이블
+export const contractEnvelopes = pgTable("contract_envelopes", {
+    id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
+
+    // 연결된 계약
+    contractId: integer("contract_id")
+        .notNull()
+        .references(() => contracts.id, { onDelete: "cascade" }),
+
+    // DocuSign에서 발급되는 Envelope/Document 식별자
+    envelopeId: varchar("envelope_id", { length: 200 }).notNull(),
+    documentId: varchar("document_id", { length: 200 }),
+
+    // Envelope 전체 상태 (예: sent, completed, voided ...)
+    envelopeStatus: varchar("envelope_status", { length: 50 }),
+
+    fileName: varchar("file_name", { length: 255 }).notNull(),
+    filePath: varchar("file_path", { length: 1024 }).notNull(),
+    // 생성/수정 시각
+    createdAt: timestamp("created_at").defaultNow().notNull(),
+    updatedAt: timestamp("updated_at").defaultNow().notNull(),
+});
+
+// 하나의 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", { 
+        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(),
+});
+
+
+
+export const contractsDetailView = pgView("contracts_detail_view").as((qb) => {
+    return qb
+        .select({
+            // Contract primary information
+            id: contracts.id,
+            contractNo: contracts.contractNo,
+            contractName: contracts.contractName,
+            status: contracts.status,
+            startDate: contracts.startDate,
+            endDate: contracts.endDate,
+
+            // Project information
+            projectId: contracts.projectId,
+            projectCode: projects.code,
+            projectName: projects.name,
+
+            // Vendor information
+            vendorId: contracts.vendorId,
+            vendorName: vendors.vendorName,
+
+            // Payment and delivery details
+            paymentTerms: contracts.paymentTerms,
+            deliveryTerms: contracts.deliveryTerms,
+            deliveryDate: contracts.deliveryDate,
+            deliveryLocation: contracts.deliveryLocation,
+
+            // Financial information
+            currency: contracts.currency,
+            totalAmount: contracts.totalAmount,
+            discount: contracts.discount,
+            tax: contracts.tax,
+            shippingFee: contracts.shippingFee,
+            netTotal: contracts.netTotal,
+
+            // Additional settings
+            partialShippingAllowed: contracts.partialShippingAllowed,
+            partialPaymentAllowed: contracts.partialPaymentAllowed,
+            remarks: contracts.remarks,
+            version: contracts.version,
+
+            // 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'),
+        })
+        .from(contracts)
+        .leftJoin(projects, eq(contracts.projectId, projects.id))
+        .leftJoin(vendors, eq(contracts.vendorId, vendors.id))
+});
+
+// Type inference for the view
+export type ContractDetail = typeof contractsDetailView.$inferSelect;
\ No newline at end of file