(대표님) 작업사항 - rfqLast, tbeLast, pdfTron, userAuth

1 files changed, 118 insertions, 93 deletions

diff --git a/db/schema/rfqLastTBE.ts b/db/schema/rfqLastTBE.ts
index ba7e30b5..1efb43bb 100644
--- a/db/schema/rfqLastTBE.ts
+++ b/db/schema/rfqLastTBE.ts
@@ -40,7 +40,7 @@ export const rfqLastTbeSessions = pgTable(
 
         // 평가 결과 (단순화)
         evaluationResult: varchar("evaluation_result", { length: 30 })
-            .$type<"pass" | "conditional_pass" | "non_pass" | null>(),
+            .$type<"Acceptable" | "Acceptable with Comment" | "Not Acceptable" | null>(),
 
         // 조건부 승인 시 조건
         conditionalRequirements: text("conditional_requirements"),
@@ -123,7 +123,7 @@ export const rfqLastTbeDocumentReviews = pgTable(
 
         // 벤더 문서인 경우
         vendorAttachmentId: integer("vendor_attachment_id")
-            .references(() => rfqLastVendorAttachments.id, { onDelete: "cascade" }),
+            .references(() => rfqLastTbeVendorDocuments.id, { onDelete: "cascade" }),
 
         // 검토 정보
         documentType: varchar("document_type", { length: 50 }),
@@ -169,6 +169,7 @@ export const rfqLastTbeDocumentReviews = pgTable(
 // ==========================================
 // 3. PDFTron 코멘트 관리
 // ==========================================
+// 수정된 스키마 (버전 관리 제거)
 export const rfqLastTbePdftronComments = pgTable(
     "rfq_last_tbe_pdftron_comments",
     {
@@ -177,65 +178,80 @@ export const rfqLastTbePdftronComments = pgTable(
             .notNull()
             .references(() => rfqLastTbeDocumentReviews.id, { onDelete: "cascade" }),
 
-        // PDFTron 관련 정보
+        // PDFTron 문서 식별자
         pdftronDocumentId: varchar("pdftron_document_id", { length: 255 }).notNull(),
-        pdftronAnnotationId: varchar("pdftron_annotation_id", { length: 255 }).notNull(),
-        annotationType: varchar("annotation_type", { length: 50 }), // highlight, note, drawing, etc.
 
-        // 위치 정보
-        pageNumber: integer("page_number"),
-        xPosition: numeric("x_position", { precision: 10, scale: 4 }).$type<number>(),
-        yPosition: numeric("y_position", { precision: 10, scale: 4 }).$type<number>(),
-        coordinates: jsonb("coordinates"), // 복잡한 도형의 경우
-
-        // 코멘트 내용
-        commentText: text("comment_text"),
-        commentCategory: varchar("comment_category", { length: 50 })
-            .$type<"technical" | "commercial" | "quality" | "compliance" | "general">(),
-
-        severity: varchar("severity", { length: 20 })
-            .$type<"minor" | "major" | "critical">()
-            .default("minor"),
-
-        // 상태 관리
-        status: varchar("status", { length: 30 })
-            .$type<"open" | "resolved" | "rejected" | "deferred">()
-            .default("open"),
-
-        // 해결 정보
-        resolvedBy: integer("resolved_by")
-            .references(() => users.id, { onDelete: "set null" }),
-        resolvedAt: timestamp("resolved_at", { withTimezone: true }).$type<Date | null>(),
-        resolutionNote: text("resolution_note"),
-
-        // 답변 스레드
-        replies: jsonb("replies").$type<{
-            userId: number;
-            userName: string;
-            message: string;
-            createdAt: string;
-        }[]>(),
+        // XFDF XML 전체 저장 (모든 annotation 포함)
+        xfdfString: text("xfdf_string").notNull(),
+
+        // 파싱된 annotation 데이터 (검색/필터링용)
+        annotationData: jsonb("annotation_data").$type<{
+            annotations: {
+                id: string;
+                type: string;
+                page: number;
+                author: string;
+                subject: string;
+                contents?: string;
+                color?: string;
+                opacity?: number;
+                createdDate: string;
+                modifiedDate?: string;
+                customData?: {
+                    category?: "technical" | "commercial" | "quality" | "compliance" | "general";
+                    severity?: "minor" | "major" | "critical";
+                    status?: "open" | "resolved" | "rejected" | "deferred";
+                    createdBy?: number;
+                    createdByType?: "buyer" | "vendor";
+                    resolvedBy?: number;
+                    resolvedAt?: string;
+                    resolutionNote?: string;
+                };
+                replies?: {
+                    author: string;
+                    contents: string;
+                    createdDate: string;
+                }[];
+                coords?: number[]; // 좌표 데이터
+            }[];
+        }>(),
+
+        // 요약 정보 (빠른 조회용)
+        commentSummary: jsonb("comment_summary").$type<{
+            total: number;
+            open: number;
+            resolved: number;
+            rejected: number;
+            deferred: number;
+            byCategory: Record<string, number>;
+            bySeverity: Record<string, number>;
+            byAuthor: Record<string, number>;
+        }>(),
 
         // 작성자 정보
         createdBy: integer("created_by")
             .notNull()
-            .references(() => users.id, { onDelete: "set null" }),
+            .references(() => users.id, { onDelete: "restrict" }),
         createdByType: varchar("created_by_type", { length: 20 })
             .$type<"buyer" | "vendor">()
             .notNull(),
 
+        // 마지막 수정자
+        lastModifiedBy: integer("last_modified_by")
+            .references(() => users.id, { onDelete: "set null" }),
+
         createdAt: timestamp("created_at").defaultNow().notNull(),
         updatedAt: timestamp("updated_at").defaultNow().notNull(),
     },
     (table) => ({
         documentReviewIdx: index("idx_pdftron_doc_review").on(table.documentReviewId),
-        statusIdx: index("idx_pdftron_status").on(table.status),
-        // PDFTron ID들에 대한 유니크 제약
-        uniquePdftronAnnotation: uniqueIndex("unique_pdftron_annotation")
-            .on(table.pdftronDocumentId, table.pdftronAnnotationId),
-    })
-);
+        documentIdIdx: index("idx_pdftron_doc_id").on(table.pdftronDocumentId),
 
+        // documentReviewId와 pdftronDocumentId 조합 유니크
+        uniqueDocument: uniqueIndex("unique_document")
+            .on(table.documentReviewId, table.pdftronDocumentId),
+    })
+)
 // ==========================================
 // 4. TBE 새로운 벤더 첨부파일 (TBE 중 추가 제출)
 // ==========================================
@@ -478,6 +494,9 @@ export const tbeLastView = pgView("tbe_last_view").as((qb) => {
             series: sql<string | null>`${rfqsLast.series}`.as("series"),
             rfqStatus: sql<string>`${rfqsLast.status}`.as("rfq_status"),
             rfqDueDate: sql<Date | null>`${rfqsLast.dueDate}`.as("rfq_due_date"),
+            picName: sql<string>`${rfqsLast.picName}`.as("pic_name"),
+            EngPicName: sql<string>`${rfqsLast.EngPicName}`.as("eng_pic_name"),
+
 
             // 패키지 정보
             packageNo: sql<string | null>`${rfqsLast.packageNo}`.as("package_no"),
@@ -591,68 +610,74 @@ export const tbeLastView = pgView("tbe_last_view").as((qb) => {
 // TBE 문서 상세 뷰 (구매자 + 벤더 문서 통합)
 // ==========================================
 export const tbeDocumentsView = pgView("tbe_documents_view").as((qb) => {
-    const ba = alias(rfqLastAttachments, "ba");
-    const baRev = alias(rfqLastAttachmentRevisions, "ba_rev");
+    const dr = alias(rfqLastTbeDocumentReviews, "dr")
+    const ba = alias(rfqLastAttachments, "ba")
+    const baRev = alias(rfqLastAttachmentRevisions, "ba_rev")
+    const vd = alias(rfqLastTbeVendorDocuments, "vd")
+
     return qb
         .select({
-            // 문서 검토 ID
-            documentReviewId: sql<number | null>`dr.id`.as("document_review_id"),
-            tbeSessionId: sql<number>`COALESCE(dr.tbe_session_id, vd.tbe_session_id)`.as("tbe_session_id"),
-
-            // 문서 구분
-            documentSource: sql<string>`
-        CASE 
-          WHEN dr.id IS NOT NULL THEN dr.document_source
-          WHEN vd.id IS NOT NULL THEN 'vendor'
-          ELSE NULL
-        END
-      `.as("document_source"),
-
-            // 문서 정보
-            documentId: sql<number>`COALESCE(dr.buyer_attachment_id, vd.id)`.as("document_id"),
-            documentType: sql<string | null>`COALESCE(dr.document_type, vd.document_type)`.as("document_type"),
-            documentName: sql<string>`COALESCE(dr.document_name, vd.file_name)`.as("document_name"),
+            // 기본키/세션
+            documentReviewId: sql<number>`dr.id`.as("document_review_id"),
+            tbeSessionId: sql<number>`dr.tbe_session_id`.as("tbe_session_id"),
+
+            // 소스
+            documentSource: sql<"buyer" | "vendor">`dr.document_source`.as("document_source"),
+
+            // 문서 식별자: buyer면 buyerAttachmentId, vendor면 vendorAttachmentId
+            documentId: sql<number | null>`
+          CASE 
+            WHEN dr.document_source = 'buyer' THEN dr.buyer_attachment_id
+            WHEN dr.document_source = 'vendor' THEN dr.vendor_attachment_id
+            ELSE NULL
+          END
+        `.as("document_id"),
+
+            // 표시 정보
+            documentType: sql<string | null>`dr.document_type`.as("document_type"),
+            documentName: sql<string | null>`dr.document_name`.as("document_name"),
+
+            // 파일 메타: buyer면 ba_rev.*, vendor면 vd.*
             originalFileName: sql<string | null>`COALESCE(ba_rev.original_file_name, vd.original_file_name)`.as("original_file_name"),
-            filePath: sql<string | null>`COALESCE(ba_rev.file_path, vd.file_path)`.as("file_path"),
-            fileSize: sql<number | null>`COALESCE(ba_rev.file_size, vd.file_size)`.as("file_size"),
-            fileType: sql<string | null>`COALESCE(ba_rev.file_type, vd.file_type)`.as("file_type"),
+            filePath: sql<string | null>`COALESCE(ba_rev.file_path,         vd.file_path)`.as("file_path"),
+            fileSize: sql<number | null>`COALESCE(ba_rev.file_size,         vd.file_size)`.as("file_size"),
+            fileType: sql<string | null>`COALESCE(ba_rev.file_type,         vd.file_type)`.as("file_type"),
 
-            // 검토 상태
-            reviewStatus: sql<string>`COALESCE(dr.review_status, vd.review_status, '미검토')`.as("review_status"),
+            // 리뷰 상태/정보 (dr 기준)
+            reviewStatus: sql<string>`dr.review_status`.as("review_status"),
             technicalCompliance: sql<boolean | null>`dr.technical_compliance`.as("technical_compliance"),
             qualityAcceptable: sql<boolean | null>`dr.quality_acceptable`.as("quality_acceptable"),
             requiresRevision: sql<boolean>`COALESCE(dr.requires_revision, false)`.as("requires_revision"),
 
-            // PDFTron 관련
+            // PDFTron
             hasPdftronComments: sql<boolean>`COALESCE(dr.has_pdftron_comments, false)`.as("has_pdftron_comments"),
             pdftronDocumentId: sql<string | null>`dr.pdftron_document_id`.as("pdftron_document_id"),
             pdftronAnnotationCount: sql<number>`COALESCE(dr.pdftron_annotation_count, 0)`.as("pdftron_annotation_count"),
 
-            // 검토 정보
-            reviewedBy: sql<number | null>`COALESCE(dr.reviewed_by, vd.reviewed_by)`.as("reviewed_by"),
-            reviewedAt: sql<Date | null>`COALESCE(dr.reviewed_at, vd.reviewed_at)`.as("reviewed_at"),
-            reviewComments: sql<string | null>`COALESCE(dr.review_comments, vd.review_comments)`.as("review_comments"),
-
-            // 제출 정보 (벤더 문서인 경우)
-            submittedBy: sql<number | null>`vd.submitted_by`.as("submitted_by"),
-            submittedAt: sql<Date | null>`vd.submitted_at`.as("submitted_at"),
-
-            // 타임스탬프
-            createdAt: sql<Date>`COALESCE(dr.created_at, vd.submitted_at)`.as("created_at"),
-            updatedAt: sql<Date>`COALESCE(dr.updated_at, vd.submitted_at)`.as("updated_at"),
+            // 검토자/타임스탬프
+            reviewedBy: sql<number | null>`dr.reviewed_by`.as("reviewed_by"),
+            reviewedAt: sql<Date | null>`dr.reviewed_at`.as("reviewed_at"),
+            reviewComments: sql<string | null>`dr.review_comments`.as("review_comments"),
+
+            // 제출 정보(벤더 문서일 때 vd의 제출 정보 노출, 아니면 null)
+            submittedBy: sql<number | null>`
+          CASE WHEN dr.document_source = 'vendor' THEN vd.submitted_by ELSE NULL END
+        `.as("submitted_by"),
+            submittedAt: sql<Date | null>`
+          CASE WHEN dr.document_source = 'vendor' THEN vd.submitted_at ELSE NULL END
+        `.as("submitted_at"),
+
+            // 생성/업데이트 시각: 리뷰기준
+            createdAt: sql<Date>`dr.created_at`.as("created_at"),
+            updatedAt: sql<Date>`dr.updated_at`.as("updated_at"),
         })
-        .from(
-            sql`(
-        SELECT * FROM rfq_last_tbe_document_reviews
-      ) dr
-      FULL OUTER JOIN (
-        SELECT * FROM rfq_last_tbe_vendor_documents
-      ) vd ON false
-    `
-        )
+        .from(dr)
+        // buyer: 리뷰가 가리키는 첨부 개정에 조인
         .leftJoin(ba, sql`dr.buyer_attachment_id = ${ba.id}`)
-        .leftJoin(baRev, sql`dr.buyer_attachment_revision_id = ${baRev.id}`);
-});
+        .leftJoin(baRev, sql`dr.buyer_attachment_revision_id = ${baRev.id}`)
+        // vendor: 리뷰가 가리키는 벤더 문서에 조인
+        .leftJoin(vd, sql`dr.vendor_attachment_id = ${vd.id}`)
+})
 
 // Type exports
 export type TbeLastView = typeof tbeLastView.$inferSelect;