(김준회) SWP 문서 업로드 (Submisssion) 초기 개발건

1 files changed, 522 insertions, 0 deletions

diff --git a/lib/swp/sync-service.ts b/lib/swp/sync-service.ts
new file mode 100644
index 00000000..0a801bd8
--- /dev/null
+++ b/lib/swp/sync-service.ts
@@ -0,0 +1,522 @@
+"use server";
+
+import db from "@/db/db";
+import { eq, and, sql } from "drizzle-orm";
+import {
+  swpDocuments,
+  swpDocumentRevisions,
+  swpDocumentFiles,
+  type SwpDocumentInsert,
+  type SwpDocumentRevisionInsert,
+  type SwpDocumentFileInsert,
+  swpSchema,
+} from "@/db/schema/SWP/swp-documents";
+
+// ============================================================================
+// API 응답 타입 정의
+// ============================================================================
+
+export interface SwpDocumentApiResponse {
+  DOC_NO: string;
+  DOC_TITLE: string;
+  DOC_GB: string;
+  DOC_TYPE: string;
+  OWN_DOC_NO: string;
+  SHI_DOC_NO: string;
+  PROJ_NO: string;
+  PROJ_NM: string;
+  PKG_NO: string;
+  MAT_CD: string;
+  MAT_NM: string;
+  DISPLN: string;
+  CTGRY: string;
+  VNDR_CD: string;
+  CPY_CD: string;
+  CPY_NM: string;
+  PIC_NM: string;
+  PIC_DEPTCD: string;
+  PIC_DEPTNM: string;
+  LTST_REV_NO: string;
+  LTST_REV_SEQ: string;
+  LTST_ACTV_STAT: string;
+  STAGE: string;
+  SKL_CD: string;
+  MOD_TYPE: string;
+  ACT_TYPE_NM: string;
+  USE_YN: string;
+  CRTER: string;
+  CRTE_DTM: string;
+  CHGR: string;
+  CHG_DTM: string;
+  REV_DTM: string | null;
+}
+
+export interface SwpFileApiResponse {
+  OWN_DOC_NO: string;
+  REV_NO: string;
+  STAGE: string;
+  FILE_NM: string;
+  FILE_SEQ: string;
+  FILE_SZ: string;
+  FLD_PATH: string;
+  ACTV_NO: string | null;
+  ACTV_SEQ: string;
+  BOX_SEQ: string;
+  OFDC_NO: string;
+  PROJ_NO: string;
+  PKG_NO: string;
+  VNDR_CD: string;
+  CPY_CD: string;
+  STAT: string;
+  STAT_NM: string;
+  IDX: string;
+  CRTER: string;
+  CRTE_DTM: string;
+  CHGR: string;
+  CHG_DTM: string;
+}
+
+// ============================================================================
+// 동기화 결과 타입
+// ============================================================================
+
+export interface SyncResult {
+  success: boolean;
+  projectNo: string;
+  stats: {
+    documents: {
+      total: number;
+      inserted: number;
+      updated: number;
+    };
+    revisions: {
+      total: number;
+      inserted: number;
+      updated: number;
+    };
+    files: {
+      total: number;
+      inserted: number;
+      updated: number;
+    };
+  };
+  errors: string[];
+  duration: number;
+}
+
+// ============================================================================
+// 동기화 메인 함수
+// ============================================================================
+
+export async function syncSwpProject(
+  projectNo: string,
+  documents: SwpDocumentApiResponse[],
+  files: SwpFileApiResponse[]
+): Promise<SyncResult> {
+  const startTime = Date.now();
+  const errors: string[] = [];
+  const stats = {
+    documents: { total: 0, inserted: 0, updated: 0 },
+    revisions: { total: 0, inserted: 0, updated: 0 },
+    files: { total: 0, inserted: 0, updated: 0 },
+  };
+
+  try {
+    // 트랜잭션으로 일괄 처리
+    await db.transaction(async (tx) => {
+      // 1. 문서 동기화
+      console.log(`[SYNC] 문서 동기화 시작: ${documents.length}개`);
+      for (const doc of documents) {
+        try {
+          const result = await upsertDocument(tx, doc);
+          stats.documents.total++;
+          if (result.inserted) stats.documents.inserted++;
+          if (result.updated) stats.documents.updated++;
+        } catch (error) {
+          errors.push(
+            `문서 ${doc.DOC_NO} 동기화 실패: ${error instanceof Error ? error.message : String(error)}`
+          );
+        }
+      }
+
+      // 2. 리비전별로 파일 그룹핑
+      const revisionMap = new Map<string, SwpFileApiResponse[]>();
+      for (const file of files) {
+        const key = `${file.OWN_DOC_NO}|${file.REV_NO}`;
+        if (!revisionMap.has(key)) {
+          revisionMap.set(key, []);
+        }
+        revisionMap.get(key)!.push(file);
+      }
+
+      // 3. 리비전 및 파일 동기화
+      console.log(`[SYNC] 리비전 동기화 시작: ${revisionMap.size}개`);
+      for (const [key, revFiles] of revisionMap) {
+        const [docNo, revNo] = key.split("|");
+        const firstFile = revFiles[0];
+
+        try {
+          // 리비전 생성/업데이트
+          const revisionResult = await upsertRevision(tx, docNo, firstFile);
+          stats.revisions.total++;
+          if (revisionResult.inserted) stats.revisions.inserted++;
+          if (revisionResult.updated) stats.revisions.updated++;
+
+          const revisionId = revisionResult.id;
+
+          // 파일들 생성/업데이트
+          for (const file of revFiles) {
+            try {
+              const fileResult = await upsertFile(tx, revisionId, docNo, file);
+              stats.files.total++;
+              if (fileResult.inserted) stats.files.inserted++;
+              if (fileResult.updated) stats.files.updated++;
+            } catch (error) {
+              errors.push(
+                `파일 ${file.FILE_NM} 동기화 실패: ${error instanceof Error ? error.message : String(error)}`
+              );
+            }
+          }
+        } catch (error) {
+          errors.push(
+            `리비전 ${docNo}-${revNo} 동기화 실패: ${error instanceof Error ? error.message : String(error)}`
+          );
+        }
+      }
+
+      console.log(
+        `[SYNC] 동기화 완료: 문서 ${stats.documents.total}, 리비전 ${stats.revisions.total}, 파일 ${stats.files.total}`
+      );
+    });
+
+    return {
+      success: errors.length === 0,
+      projectNo,
+      stats,
+      errors,
+      duration: Date.now() - startTime,
+    };
+  } catch (error) {
+    return {
+      success: false,
+      projectNo,
+      stats,
+      errors: [
+        ...errors,
+        `트랜잭션 실패: ${error instanceof Error ? error.message : String(error)}`,
+      ],
+      duration: Date.now() - startTime,
+    };
+  }
+}
+
+// ============================================================================
+// Upsert 헬퍼 함수들
+// ============================================================================
+
+async function upsertDocument(
+  tx: any,
+  doc: SwpDocumentApiResponse
+): Promise<{ id: string; inserted: boolean; updated: boolean }> {
+  const data: SwpDocumentInsert = {
+    DOC_NO: doc.DOC_NO,
+    DOC_TITLE: doc.DOC_TITLE,
+    DOC_GB: doc.DOC_GB || null,
+    DOC_TYPE: doc.DOC_TYPE || null,
+    OWN_DOC_NO: doc.OWN_DOC_NO,
+    SHI_DOC_NO: doc.SHI_DOC_NO,
+    PROJ_NO: doc.PROJ_NO,
+    PROJ_NM: doc.PROJ_NM,
+    PKG_NO: doc.PKG_NO || null,
+    MAT_CD: doc.MAT_CD || null,
+    MAT_NM: doc.MAT_NM || null,
+    DISPLN: doc.DISPLN || null,
+    CTGRY: doc.CTGRY || null,
+    VNDR_CD: doc.VNDR_CD || null,
+    CPY_CD: doc.CPY_CD,
+    CPY_NM: doc.CPY_NM,
+    PIC_NM: doc.PIC_NM,
+    PIC_DEPTCD: doc.PIC_DEPTCD || null,
+    PIC_DEPTNM: doc.PIC_DEPTNM,
+    LTST_REV_NO: doc.LTST_REV_NO || null,
+    LTST_REV_SEQ: doc.LTST_REV_SEQ || null,
+    LTST_ACTV_STAT: doc.LTST_ACTV_STAT || null,
+    STAGE: doc.STAGE || null,
+    SKL_CD: doc.SKL_CD,
+    MOD_TYPE: doc.MOD_TYPE || null,
+    ACT_TYPE_NM: doc.ACT_TYPE_NM || null,
+    USE_YN: doc.USE_YN || null,
+    CRTER: doc.CRTER,
+    CRTE_DTM: doc.CRTE_DTM,
+    CHGR: doc.CHGR,
+    CHG_DTM: doc.CHG_DTM,
+    REV_DTM: doc.REV_DTM || null,
+    sync_status: "synced",
+    last_synced_at: new Date(),
+    updated_at: new Date(),
+  };
+
+  // 기존 문서 확인
+  const existing = await tx
+    .select()
+    .from(swpDocuments)
+    .where(eq(swpDocuments.DOC_NO, doc.DOC_NO))
+    .limit(1);
+
+  if (existing.length > 0) {
+    // 업데이트
+    await tx
+      .update(swpDocuments)
+      .set(data)
+      .where(eq(swpDocuments.DOC_NO, doc.DOC_NO));
+    return { id: doc.DOC_NO, inserted: false, updated: true };
+  } else {
+    // 삽입
+    await tx.insert(swpDocuments).values(data);
+    return { id: doc.DOC_NO, inserted: true, updated: false };
+  }
+}
+
+async function upsertRevision(
+  tx: any,
+  docNo: string,
+  file: SwpFileApiResponse
+): Promise<{ id: number; inserted: boolean; updated: boolean }> {
+  const data: Omit<SwpDocumentRevisionInsert, "id"> = {
+    DOC_NO: docNo,
+    REV_NO: file.REV_NO,
+    STAGE: file.STAGE,
+    ACTV_NO: file.ACTV_NO || null,
+    ACTV_SEQ: file.ACTV_SEQ,
+    BOX_SEQ: file.BOX_SEQ,
+    OFDC_NO: file.OFDC_NO,
+    PROJ_NO: file.PROJ_NO,
+    PKG_NO: file.PKG_NO || null,
+    VNDR_CD: file.VNDR_CD || null,
+    CPY_CD: file.CPY_CD,
+    sync_status: "synced",
+    last_synced_at: new Date(),
+    updated_at: new Date(),
+  };
+
+  // 기존 리비전 확인
+  const existing = await tx
+    .select()
+    .from(swpDocumentRevisions)
+    .where(
+      and(
+        eq(swpDocumentRevisions.DOC_NO, docNo),
+        eq(swpDocumentRevisions.REV_NO, file.REV_NO)
+      )
+    )
+    .limit(1);
+
+  if (existing.length > 0) {
+    // 업데이트
+    await tx
+      .update(swpDocumentRevisions)
+      .set(data)
+      .where(eq(swpDocumentRevisions.id, existing[0].id));
+    return { id: existing[0].id, inserted: false, updated: true };
+  } else {
+    // 삽입
+    const result = await tx
+      .insert(swpDocumentRevisions)
+      .values(data)
+      .returning({ id: swpDocumentRevisions.id });
+    return { id: result[0].id, inserted: true, updated: false };
+  }
+}
+
+async function upsertFile(
+  tx: any,
+  revisionId: number,
+  docNo: string,
+  file: SwpFileApiResponse
+): Promise<{ id: number; inserted: boolean; updated: boolean }> {
+  const data: Omit<SwpDocumentFileInsert, "id"> = {
+    revision_id: revisionId,
+    DOC_NO: docNo,
+    FILE_NM: file.FILE_NM,
+    FILE_SEQ: file.FILE_SEQ,
+    FILE_SZ: file.FILE_SZ,
+    FLD_PATH: file.FLD_PATH,
+    STAT: file.STAT,
+    STAT_NM: file.STAT_NM,
+    IDX: file.IDX,
+    ACTV_NO: file.ACTV_NO || null,
+    CRTER: file.CRTER,
+    CRTE_DTM: file.CRTE_DTM,
+    CHGR: file.CHGR,
+    CHG_DTM: file.CHG_DTM,
+    sync_status: "synced",
+    last_synced_at: new Date(),
+    updated_at: new Date(),
+  };
+
+  // 기존 파일 확인 (revision + fileSeq로 unique)
+  const existing = await tx
+    .select()
+    .from(swpDocumentFiles)
+    .where(
+      and(
+        eq(swpDocumentFiles.revision_id, revisionId),
+        eq(swpDocumentFiles.FILE_SEQ, file.FILE_SEQ)
+      )
+    )
+    .limit(1);
+
+  if (existing.length > 0) {
+    // 업데이트
+    await tx
+      .update(swpDocumentFiles)
+      .set(data)
+      .where(eq(swpDocumentFiles.id, existing[0].id));
+    return { id: existing[0].id, inserted: false, updated: true };
+  } else {
+    // 삽입
+    const result = await tx
+      .insert(swpDocumentFiles)
+      .values(data)
+      .returning({ id: swpDocumentFiles.id });
+    return { id: result[0].id, inserted: true, updated: false };
+  }
+}
+
+// ============================================================================
+// 조회 헬퍼 함수들
+// ============================================================================
+
+/**
+ * 프로젝트의 문서 계층 구조 조회 (복잡한 JSON 집계는 SQL 직접 실행)
+ */
+export async function getProjectDocumentsHierarchy(projectNo: string) {
+  return db.execute(sql`
+    SELECT 
+      d."DOC_NO",
+      d."DOC_TITLE",
+      d."PROJ_NO",
+      d."PROJ_NM",
+      d."PKG_NO",
+      d."VNDR_CD",
+      d."CPY_NM",
+      d."MAT_NM",
+      d."LTST_REV_NO",
+      d."LTST_ACTV_STAT",
+      d.sync_status,
+      d.last_synced_at,
+      
+      COALESCE(
+        json_agg(
+          json_build_object(
+            'id', r.id,
+            'revNo', r."REV_NO",
+            'stage', r."STAGE",
+            'actvNo', r."ACTV_NO",
+            'ofdcNo', r."OFDC_NO",
+            'syncStatus', r.sync_status,
+            'fileCount', (
+              SELECT COUNT(*)::int
+              FROM swp.swp_document_files f2
+              WHERE f2.revision_id = r.id
+            ),
+            'files', (
+              SELECT COALESCE(json_agg(
+                json_build_object(
+                  'id', f.id,
+                  'fileNm', f."FILE_NM",
+                  'fileSeq', f."FILE_SEQ",
+                  'fileSz', f."FILE_SZ",
+                  'fldPath', f."FLD_PATH",
+                  'stat', f."STAT",
+                  'statNm', f."STAT_NM",
+                  'syncStatus', f.sync_status,
+                  'createdAt', f.created_at
+                )
+                ORDER BY f."FILE_SEQ"
+              ), '[]'::json)
+              FROM swp.swp_document_files f
+              WHERE f.revision_id = r.id
+            )
+          )
+          ORDER BY r."REV_NO" DESC
+        ) FILTER (WHERE r.id IS NOT NULL),
+        '[]'::json
+      ) as revisions,
+      
+      COUNT(DISTINCT r.id)::int as revision_count,
+      COUNT(f.id)::int as total_file_count
+      
+    FROM swp.swp_documents d
+    LEFT JOIN swp.swp_document_revisions r ON d."DOC_NO" = r."DOC_NO"
+    LEFT JOIN swp.swp_document_files f ON r.id = f.revision_id
+    WHERE d."PROJ_NO" = ${projectNo}
+    GROUP BY 
+      d."DOC_NO",
+      d."DOC_TITLE",
+      d."PROJ_NO",
+      d."PROJ_NM",
+      d."PKG_NO",
+      d."VNDR_CD",
+      d."CPY_NM",
+      d."MAT_NM",
+      d."LTST_REV_NO",
+      d."LTST_ACTV_STAT",
+      d.sync_status,
+      d.last_synced_at
+    ORDER BY d."DOC_NO"
+  `);
+}
+
+/**
+ * 특정 문서의 모든 리비전 조회
+ */
+export async function getDocumentRevisions(docNo: string) {
+  return db
+    .select()
+    .from(swpDocumentRevisions)
+    .where(eq(swpDocumentRevisions.DOC_NO, docNo))
+    .orderBy(sql`${swpDocumentRevisions.REV_NO} DESC`);
+}
+
+/**
+ * 특정 리비전의 모든 파일 조회
+ */
+export async function getRevisionFiles(revisionId: number) {
+  return db
+    .select()
+    .from(swpDocumentFiles)
+    .where(eq(swpDocumentFiles.revision_id, revisionId))
+    .orderBy(swpDocumentFiles.FILE_SEQ);
+}
+
+/**
+ * 프로젝트 동기화 상태 조회
+ */
+export async function getProjectSyncStatus(projectNo: string) {
+  return db.execute(sql`
+    SELECT 
+      d."PROJ_NO",
+      d."PROJ_NM",
+      
+      COUNT(DISTINCT d."DOC_NO")::int as total_documents,
+      COUNT(DISTINCT r.id)::int as total_revisions,
+      COUNT(f.id)::int as total_files,
+      
+      COUNT(DISTINCT d."DOC_NO") FILTER (WHERE d.sync_status = 'synced')::int as docs_synced,
+      COUNT(DISTINCT d."DOC_NO") FILTER (WHERE d.sync_status = 'pending')::int as docs_pending,
+      COUNT(DISTINCT d."DOC_NO") FILTER (WHERE d.sync_status = 'error')::int as docs_error,
+      
+      COUNT(DISTINCT r.id) FILTER (WHERE r.sync_status = 'synced')::int as revs_synced,
+      COUNT(f.id) FILTER (WHERE f.sync_status = 'synced')::int as files_synced,
+      
+      MAX(d.last_synced_at) as last_sync_time
+      
+    FROM swp.swp_documents d
+    LEFT JOIN swp.swp_document_revisions r ON d."DOC_NO" = r."DOC_NO"
+    LEFT JOIN swp.swp_document_files f ON r.id = f.revision_id
+    WHERE d."PROJ_NO" = ${projectNo}
+    GROUP BY d."PROJ_NO", d."PROJ_NM"
+  `);
+}
+