From e6e98a1bed7a23d148ab97b3a7414ade4b1d236b Mon Sep 17 00:00:00 2001
From: dujinkim <dujin.kim@dtsolution.co.kr>
Date: Thu, 27 Mar 2025 04:30:31 +0000
Subject: 20240327 작업사항
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

---
 db/schema/contract.ts | 49 +++++++++++++++++++++++++++++++++++++++++--------
 1 file changed, 41 insertions(+), 8 deletions(-)

(limited to 'db/schema/contract.ts')

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))
-- 
cgit v1.2.3