// enhanced-schema.ts import { pgTable, integer, varchar, timestamp, date, pgView, uniqueIndex, jsonb, boolean, serial,json, index, text, bigint } from "drizzle-orm/pg-core" import { eq, sql } from "drizzle-orm"; import { projects } from "./projects"; import { vendors } from "./vendors"; import { contracts } from "./contract"; // 기존 documents 테이블 (변경 없음) export const documents = pgTable( "documents", { id: integer("id").primaryKey().generatedAlwaysAsIdentity(), pic: varchar("pic", { length: 50 }), projectId: integer("project_id") // .notNull() .references(() => projects.id, { onDelete: "cascade" }), contractId: integer("contract_id") // .notNull() .references(() => contracts.id, { onDelete: "cascade" }), vendorId: integer("vendor_id") // .notNull() .references(() => vendors.id, { onDelete: "cascade" }), // 기본 문서 정보 docNumber: varchar("doc_number", { length: 100 }).notNull(), vendorDocNumber: varchar("vendor_doc_number", { length: 100 }), title: varchar("title", { length: 255 }).notNull(), status: varchar("status", { length: 50 }) .notNull() .default("ACTIVE"), issuedDate: date("issued_date"), // ✅ DOLCE 연동을 위한 새로운 필드들 drawingKind: varchar("drawing_kind", { length: 10 }), // B3, B4, B5 drawingMoveGbn: varchar("drawing_move_gbn", { length: 50 }), // 도면입수, 도면제출, GTT Deliverable, SHI Input Information discipline: varchar("discipline", { length: 10 }), // DE, ME, etc. // ✅ 외부 시스템 연동 정보 externalDocumentId: varchar("external_document_id", { length: 100 }), // DOLCE 시스템의 문서 ID externalSystemType: varchar("external_system_type", { length: 20 }), // DOLCE, SWP externalSyncedAt: timestamp("external_synced_at"), // 마지막 동기화 시간 // ✅ B4 전용 필드들 (drawingKind가 B4일 때만 사용) cGbn: varchar("c_gbn", { length: 50 }), // CGbn dGbn: varchar("d_gbn", { length: 50 }), // DGbn degreeGbn: varchar("degree_gbn", { length: 50 }), // DegreeGbn deptGbn: varchar("dept_gbn", { length: 50 }), // DeptGbn jGbn: varchar("j_gbn", { length: 50 }), // JGbn sGbn: varchar("s_gbn", { length: 50 }), // SGbn // ✅ DOLCE 응답의 추가 정보들 shiDrawingNo: varchar("shi_drawing_no", { length: 100 }), // SHI 도면 번호 manager: varchar("manager", { length: 100 }), // 담당자 managerENM: varchar("manager_enm", { length: 100 }), // 담당자 영문명 managerNo: varchar("manager_no", { length: 50 }), // 담당자 번호 registerGroup: integer("register_group"), // 등록 그룹 registerGroupId: integer("register_group_id"), // 등록 그룹 ID // ✅ 생성자 정보 (DOLCE에서 가져온 정보) createUserNo: varchar("create_user_no", { length: 50 }), // 생성자 번호 createUserId: varchar("create_user_id", { length: 100 }), // 생성자 ID (한글명) createUserENM: varchar("create_user_enm", { length: 100 }), // 생성자 영문명 createdAt: timestamp("created_at").defaultNow().notNull(), updatedAt: timestamp("updated_at").defaultNow().notNull(), }, (table) => { return { uniqueContractDocStatus: uniqueIndex("unique_contract_doc_status").on( table.contractId, table.docNumber, table.discipline, table.status ), uniqueContractVendorDoc: uniqueIndex("unique_contract_vendor_doc").on( table.contractId, table.discipline, table.vendorDocNumber ).where(sql`${table.vendorDocNumber} IS NOT NULL`), // ✅ 외부 시스템 문서 ID 유니크 인덱스 uniqueExternalDoc: uniqueIndex("unique_external_doc").on( table.contractId, table.externalDocumentId, table.discipline, table.externalSystemType ).where(sql`${table.externalDocumentId} IS NOT NULL`), uniqueProjectDocStatus: uniqueIndex("unique_project_doc_status").on( table.projectId, table.docNumber, table.discipline, table.status ), // uniqueProjectVendorDoc: uniqueIndex("unique_project_vendor_doc").on( // table.projectId, // table.vendorDocNumber // ).where(sql`${table.vendorDocNumber} IS NOT NULL`), // ✅ 외부 시스템 문서 ID 유니크 인덱스 uniqueExternalDocProject: uniqueIndex("unique_external_doc_project").on( table.projectId, table.externalDocumentId, table.discipline, table.externalSystemType ).where(sql`${table.externalDocumentId} IS NOT NULL`), // ✅ drawingKind 인덱스 (자주 검색될 것 같음) drawingKindIndex: index("drawing_kind_idx").on(table.drawingKind), } } ) // 확장된 issueStages 테이블 export const issueStages = pgTable( "issue_stages", { id: integer("id").primaryKey().generatedAlwaysAsIdentity(), documentId: integer("document_id") .notNull() .references(() => documents.id, { onDelete: "cascade" }), stageName: varchar("stage_name", { length: 100 }).notNull(), // 기존 필드 planDate: date("plan_date"), actualDate: date("actual_date"), // 새로운 스케줄 관리 필드들 stageStatus: varchar("stage_status", { length: 50 }) .notNull() .default("PLANNED"), // PLANNED, IN_PROGRESS, SUBMITTED, APPROVED, REJECTED, COMPLETED stageOrder: integer("stage_order").default(0), priority: varchar("priority", { length: 20 }).default("MEDIUM"), // HIGH, MEDIUM, LOW // 담당자 정보 assigneeId: integer("assignee_id"), assigneeName: varchar("assignee_name", { length: 100 }), // 알림 및 추가 정보 reminderDays: integer("reminder_days").default(3), description: varchar("description", { length: 500 }), notes: varchar("notes", { length: 1000 }), createdAt: timestamp("created_at").defaultNow().notNull(), updatedAt: timestamp("updated_at").defaultNow().notNull(), }, (table) => { return { uniqueDocumentStage: uniqueIndex("unique_document_stage").on( table.documentId, table.stageName ), documentStageOrder: uniqueIndex("document_stage_order").on( table.documentId, table.stageOrder ), } } ); // 확장된 revisions 테이블 export const revisions = pgTable( "revisions", { id: integer("id").primaryKey().generatedAlwaysAsIdentity(), issueStageId: integer("issue_stage_id").notNull(), revision: varchar("revision", { length: 50 }).notNull(), uploaderType: varchar("uploader_type", { length: 20 }).notNull().default("vendor"), uploaderId: integer("uploader_id"), uploaderName: varchar("uploader_name", { length: 100 }), usage: varchar("usage", { length: 100 }), usageType: varchar("usage_type", { length: 255 }), // 확장된 상태 관리 revisionStatus: varchar("revision_status", { length: 50 }) .notNull() .default("SUBMITTED"), // SUBMITTED, UNDER_REVIEW, APPROVED, REJECTED, SUPERSEDED // 상세 날짜 추적 submittedDate: date("submitted_date"), uploadedAt: date("uploaded_at"), reviewStartDate: date("review_start_date"), approvedDate: date("approved_date"), rejectedDate: date("rejected_date"), // 검토자 정보 reviewerId: integer("reviewer_id"), reviewerName: varchar("reviewer_name", { length: 100 }), reviewComments: varchar("review_comments", { length: 1000 }), externalUploadId: varchar("external_upload_id", { length: 255 }), comment: varchar("comment", { length: 500 }), createdAt: timestamp("created_at").defaultNow().notNull(), updatedAt: timestamp("updated_at").defaultNow().notNull(), registerId: varchar("register_id", { length: 50 }), serialNo: varchar("serial_no", { length: 50 }), registerSerialNoMax: varchar("register_serial_no_max", { length: 50 }), }, (table) => { return { uniqueStageRevisionUsage: uniqueIndex("unique_stage_revision_usage").on( table.issueStageId, table.revision, table.usage, table.serialNo, sql`COALESCE(${table.usageType}, '')` ), } } ) // 기존 documentAttachments (변경 없음) export const documentAttachments = pgTable( "document_attachments", { id: integer("id").primaryKey().generatedAlwaysAsIdentity(), revisionId: integer("revision_id") .notNull() .references(() => revisions.id, { onDelete: "cascade" }), fileName: varchar("file_name", { length: 255 }).notNull(), filePath: varchar("file_path", { length: 1024 }).notNull(), fileType: varchar("file_type", { length: 50 }), fileSize: integer("file_size"), // DOLCE 연동 관련 필드 추가 uploadId: varchar("upload_id", { length: 36 }), // UUID 형태 fileId: varchar("file_id", { length: 36 }), // UUID 형태 uploadedBy: varchar("uploaded_by", { length: 255 }), // userId 저장 dolceFilePath: varchar("dolce_file_path", { length: 1024 }), // DOLCE에서 반환하는 파일 경로 uploadedAt: timestamp("uploaded_at"), // DOLCE 업로드 성공 시간 createdAt: timestamp("created_at").defaultNow().notNull(), updatedAt: timestamp("updated_at").defaultNow().notNull(), } ) export const enhancedDocumentsView = pgView("enhanced_documents_view", { // 기본 문서 정보 documentId: integer("document_id").notNull(), docNumber: varchar("doc_number", { length: 100 }).notNull(), drawingKind: varchar("drawing_kind", { length: 50 }), vendorDocNumber: varchar("vendor_doc_number", { length: 100 }), // ✅ 벤더 문서 번호 추가 title: varchar("title", { length: 255 }).notNull(), pic: varchar("pic", { length: 50 }), status: varchar("status", { length: 50 }).notNull(), issuedDate: date("issued_date"), contractId: integer("contract_id").notNull(), // ✅ 프로젝트 및 벤더 정보 추가 projectId: integer("project_id"), projectCode: varchar("project_code", { length: 50 }), vendorName: varchar("vendor_name", { length: 255 }), vendorCode: varchar("vendor_code", { length: 50 }), vendorId: varchar("vendor_id", { length: 50 }), // ✅ B4 전용 필드들 (drawingKind가 B4일 때만 사용) cGbn: varchar("c_gbn", { length: 50 }), // CGbn dGbn: varchar("d_gbn", { length: 50 }), // DGbn degreeGbn: varchar("degree_gbn", { length: 50 }), // DegreeGbn deptGbn: varchar("dept_gbn", { length: 50 }), // DeptGbn jGbn: varchar("j_gbn", { length: 50 }), // JGbn sGbn: varchar("s_gbn", { length: 50 }), // SGbn // 현재 스테이지 정보 (가장 우선순위가 높은 미완료 스테이지) currentStageId: integer("current_stage_id"), currentStageName: varchar("current_stage_name", { length: 100 }), currentStageStatus: varchar("current_stage_status", { length: 50 }), currentStageOrder: integer("current_stage_order"), currentStagePlanDate: date("current_stage_plan_date"), currentStageActualDate: date("current_stage_actual_date"), currentStageAssigneeName: varchar("current_stage_assignee_name", { length: 100 }), currentStagePriority: varchar("current_stage_priority", { length: 20 }), // 계산 필드 daysUntilDue: integer("days_until_due"), isOverdue: boolean("is_overdue"), daysDifference: integer("days_difference"), // 전체 진행률 totalStages: integer("total_stages"), completedStages: integer("completed_stages"), progressPercentage: integer("progress_percentage"), // 최신 리비전 정보 latestRevisionId: integer("latest_revision_id"), latestRevision: varchar("latest_revision", { length: 50 }), latestRevisionStatus: varchar("latest_revision_status", { length: 50 }), latestRevisionUploaderName: varchar("latest_revision_uploader_name", { length: 100 }), latestSubmittedDate: date("latest_submitted_date"), // 전체 스테이지 목록 (리비전 및 첨부파일 포함) allStages: jsonb("all_stages").$type; }>; }>>(), // 메타 정보 attachmentCount: integer("attachment_count"), createdAt: timestamp("created_at", { withTimezone: true }).notNull(), updatedAt: timestamp("updated_at", { withTimezone: true }).notNull(), }).as(sql` WITH document_stats AS ( SELECT d.id as document_id, COUNT(ist.id) as total_stages, COUNT(CASE WHEN ist.stage_status IN ('COMPLETED', 'APPROVED') THEN 1 END) as completed_stages, CASE WHEN COUNT(ist.id) > 0 THEN ROUND((COUNT(CASE WHEN ist.stage_status IN ('COMPLETED', 'APPROVED') THEN 1 END) * 100.0) / COUNT(ist.id)) ELSE 0 END as progress_percentage FROM documents d LEFT JOIN issue_stages ist ON d.id = ist.document_id GROUP BY d.id ), current_stage_info AS ( SELECT DISTINCT ON (document_id) document_id, id as current_stage_id, stage_name as current_stage_name, stage_status as current_stage_status, stage_order as current_stage_order, plan_date as current_stage_plan_date, actual_date as current_stage_actual_date, assignee_name as current_stage_assignee_name, priority as current_stage_priority, CASE WHEN actual_date IS NULL AND plan_date IS NOT NULL THEN plan_date - CURRENT_DATE ELSE NULL END as days_until_due, CASE WHEN actual_date IS NULL AND plan_date < CURRENT_DATE THEN true WHEN actual_date IS NOT NULL AND actual_date > plan_date THEN true ELSE false END as is_overdue, CASE WHEN actual_date IS NOT NULL AND plan_date IS NOT NULL THEN actual_date - plan_date ELSE NULL END as days_difference FROM issue_stages WHERE stage_status NOT IN ('COMPLETED', 'APPROVED') ORDER BY document_id, stage_order ASC, priority DESC ), latest_revision_info AS ( SELECT DISTINCT ON (ist.document_id) ist.document_id, r.id as latest_revision_id, r.revision as latest_revision, r.revision_status as latest_revision_status, r.uploader_name as latest_revision_uploader_name, r.submitted_date as latest_submitted_date FROM revisions r JOIN issue_stages ist ON r.issue_stage_id = ist.id ORDER BY ist.document_id, r.created_at DESC ), -- 리비전별 첨부파일 집계 revision_attachments AS ( SELECT r.id as revision_id, COALESCE( json_agg( json_build_object( 'id', da.id, 'revisionId', da.revision_id, 'fileName', da.file_name, 'filePath', da.file_path, 'fileSize', da.file_size, 'fileType', da.file_type, 'createdAt', da.created_at, 'updatedAt', da.updated_at ) ORDER BY da.created_at ) FILTER (WHERE da.id IS NOT NULL), '[]'::json ) as attachments FROM revisions r LEFT JOIN document_attachments da ON r.id = da.revision_id GROUP BY r.id ), -- 스테이지별 리비전 집계 (첨부파일 포함) stage_revisions AS ( SELECT ist.id as stage_id, COALESCE( json_agg( json_build_object( 'id', r.id, 'issueStageId', r.issue_stage_id, 'revision', r.revision, 'uploaderType', r.uploader_type, 'uploaderId', r.uploader_id, 'uploaderName', r.uploader_name, 'comment', r.comment, 'usage', r.usage, 'revisionStatus', r.revision_status, 'submittedDate', r.submitted_date, 'uploadedAt', r.uploaded_at, 'approvedDate', r.approved_date, 'reviewStartDate', r.review_start_date, 'rejectedDate', r.rejected_date, 'reviewerId', r.reviewer_id, 'reviewerName', r.reviewer_name, 'reviewComments', r.review_comments, 'createdAt', r.created_at, 'updatedAt', r.updated_at, 'attachments', ra.attachments ) ORDER BY r.created_at ) FILTER (WHERE r.id IS NOT NULL), '[]'::json ) as revisions FROM issue_stages ist LEFT JOIN revisions r ON ist.id = r.issue_stage_id LEFT JOIN revision_attachments ra ON r.id = ra.revision_id GROUP BY ist.id ), -- 문서별 스테이지 집계 (리비전 포함) stage_aggregation AS ( SELECT ist.document_id, json_agg( json_build_object( 'id', ist.id, 'stageName', ist.stage_name, 'stageStatus', ist.stage_status, 'stageOrder', ist.stage_order, 'planDate', ist.plan_date, 'actualDate', ist.actual_date, 'assigneeName', ist.assignee_name, 'priority', ist.priority, 'revisions', sr.revisions ) ORDER BY ist.stage_order ) as all_stages FROM issue_stages ist LEFT JOIN stage_revisions sr ON ist.id = sr.stage_id GROUP BY ist.document_id ), attachment_counts AS ( SELECT ist.document_id, COUNT(da.id) as attachment_count FROM issue_stages ist LEFT JOIN revisions r ON ist.id = r.issue_stage_id LEFT JOIN document_attachments da ON r.id = da.revision_id GROUP BY ist.document_id ) SELECT d.id as document_id, d.doc_number, d.drawing_kind, d.vendor_doc_number, -- ✅ 벤더 문서 번호 추가 d.title, d.pic, d.status, d.issued_date, d.contract_id, d.c_gbn, d.d_gbn, d.degree_gbn, d.dept_gbn, d.s_gbn, d.j_gbn, -- ✅ 프로젝트 및 벤더 정보 추가 c.project_id as project_id, p.code as project_code, v.vendor_name as vendor_name, v.vendor_code as vendor_code, c.vendor_id as vendor_id, -- 현재 스테이지 정보 csi.current_stage_id, csi.current_stage_name, csi.current_stage_status, csi.current_stage_order, csi.current_stage_plan_date, csi.current_stage_actual_date, csi.current_stage_assignee_name, csi.current_stage_priority, -- 계산 필드 csi.days_until_due, csi.is_overdue, csi.days_difference, -- 진행률 정보 ds.total_stages, ds.completed_stages, ds.progress_percentage, -- 최신 리비전 정보 lri.latest_revision_id, lri.latest_revision, lri.latest_revision_status, lri.latest_revision_uploader_name, lri.latest_submitted_date, -- 전체 스테이지 (리비전 및 첨부파일 포함) COALESCE(sa.all_stages, '[]'::json) as all_stages, -- 기타 COALESCE(ac.attachment_count, 0) as attachment_count, d.created_at, d.updated_at FROM documents d -- ✅ contracts, projects, vendors 테이블 JOIN 추가 LEFT JOIN contracts c ON d.contract_id = c.id LEFT JOIN projects p ON c.project_id = p.id LEFT JOIN vendors v ON c.vendor_id = v.id LEFT JOIN document_stats ds ON d.id = ds.document_id LEFT JOIN current_stage_info csi ON d.id = csi.document_id LEFT JOIN latest_revision_info lri ON d.id = lri.document_id LEFT JOIN stage_aggregation sa ON d.id = sa.document_id LEFT JOIN attachment_counts ac ON d.id = ac.document_id ORDER BY d.created_at DESC `); // 기존 뷰들도 유지 (호환성을 위해) export const vendorDocumentsView = pgView("vendor_documents_view", { id: integer("id").notNull(), docNumber: varchar("doc_number", { length: 100 }).notNull(), title: varchar("title", { length: 255 }).notNull(), pic: varchar("pic", { length: 255 }).notNull(), status: varchar("status", { length: 50 }).notNull(), issuedDate: date("issued_date"), contractId: integer("contract_id").notNull(), latestStageId: integer("latest_stage_id"), latestStageName: varchar("latest_stage_name", { length: 100 }), latestStagePlanDate: date("latest_stage_plan_date"), latestStageActualDate: date("latest_stage_actual_date"), latestRevisionId: integer("latest_revision_id"), latestRevision: varchar("latest_revision", { length: 50 }), latestRevisionUploaderType: varchar("latest_revision_uploader_type", { length: 20 }), latestRevisionUploaderName: varchar("latest_revision_uploader_name", { length: 100 }), attachmentCount: integer("attachment_count"), createdAt: timestamp("created_at", { withTimezone: true }).notNull(), updatedAt: timestamp("updated_at", { withTimezone: true }).notNull(), }).as(sql` SELECT d.id, d.doc_number, d.title, d.pic, d.status, d.issued_date, d.contract_id, (SELECT id FROM issue_stages WHERE document_id = d.id ORDER BY created_at DESC LIMIT 1) AS latest_stage_id, (SELECT stage_name FROM issue_stages WHERE document_id = d.id ORDER BY created_at DESC LIMIT 1) AS latest_stage_name, (SELECT plan_date FROM issue_stages WHERE document_id = d.id ORDER BY created_at DESC LIMIT 1) AS latest_stage_plan_date, (SELECT actual_date FROM issue_stages WHERE document_id = d.id ORDER BY created_at DESC LIMIT 1) AS latest_stage_actual_date, (SELECT r.id FROM revisions r JOIN issue_stages i ON r.issue_stage_id = i.id WHERE i.document_id = d.id ORDER BY r.created_at DESC LIMIT 1) AS latest_revision_id, (SELECT r.revision FROM revisions r JOIN issue_stages i ON r.issue_stage_id = i.id WHERE i.document_id = d.id ORDER BY r.created_at DESC LIMIT 1) AS latest_revision, (SELECT r.uploader_type FROM revisions r JOIN issue_stages i ON r.issue_stage_id = i.id WHERE i.document_id = d.id ORDER BY r.created_at DESC LIMIT 1) AS latest_revision_uploader_type, (SELECT r.uploader_name FROM revisions r JOIN issue_stages i ON r.issue_stage_id = i.id WHERE i.document_id = d.id ORDER BY r.created_at DESC LIMIT 1) AS latest_revision_uploader_name, (SELECT COUNT(*) FROM document_attachments a JOIN revisions r ON a.revision_id = r.id JOIN issue_stages i ON r.issue_stage_id = i.id WHERE i.document_id = d.id) AS attachment_count, d.created_at, d.updated_at FROM documents d JOIN contracts c ON d.contract_id = c.id `); export const documentStagesView = pgView("document_stages_view", { documentId: integer("document_id").notNull(), docNumber: varchar("doc_number", { length: 100 }).notNull(), title: varchar("title", { length: 255 }).notNull(), status: varchar("status", { length: 50 }).notNull(), issuedDate: date("issued_date"), contractId: integer("contract_id").notNull(), stageCount: integer("stage_count").notNull(), stageList: jsonb("stage_list").$type(), createdAt: timestamp("created_at", { withTimezone: true }).notNull(), updatedAt: timestamp("updated_at", { withTimezone: true }).notNull(), }).as(sql` SELECT d.id AS document_id, d.doc_number, d.title, d.status, d.issued_date, d.contract_id, (SELECT COUNT(*) FROM issue_stages WHERE document_id = d.id) AS stage_count, COALESCE( (SELECT json_agg(i.stage_name) FROM issue_stages i WHERE i.document_id = d.id), '[]' ) AS stage_list, d.created_at, d.updated_at FROM documents d `); // 타입 추출 export type VendorDocumentsView = typeof vendorDocumentsView.$inferSelect export type DocumentStagesView = typeof documentStagesView.$inferSelect export type EnhancedDocumentsView = typeof enhancedDocumentsView.$inferSelect // 동기화 설정 테이블 export const syncConfigs = pgTable( "sync_configs", { id: serial("id").primaryKey(), vendorId: integer("vendor_id").notNull(), targetSystem: varchar("target_system", { length: 50 }).notNull(), // 'SHI', 'SAP' 등 syncEnabled: boolean("sync_enabled").default(true), syncIntervalMinutes: integer("sync_interval_minutes").default(30), // 30분마다 lastSuccessfulSync: timestamp("last_successful_sync"), lastSyncAttempt: timestamp("last_sync_attempt"), endpointUrl: text("endpoint_url").notNull(), authToken: text("auth_token"), apiVersion: varchar("api_version", { length: 20 }).default("v1"), maxBatchSize: integer("max_batch_size").default(100), retryMaxAttempts: integer("retry_max_attempts").default(3), createdAt: timestamp("created_at").defaultNow().notNull(), updatedAt: timestamp("updated_at").defaultNow().notNull(), }, (table) => { return { contractSystemIdx: index("idx_sync_configs_contract_system").on( table.vendorId, table.targetSystem ), } } ) // 변경 로그 테이블 (모든 변경사항 추적) export const changeLogs = pgTable( "change_logs", { id: serial("id").primaryKey(), vendorId: integer("vendor_id").notNull(), entityType: varchar("entity_type", { length: 50 }).notNull(), // 'document', 'revision', 'attachment' entityId: integer("entity_id").notNull(), action: varchar("action", { length: 20 }).notNull(), // 'CREATE', 'UPDATE', 'DELETE' changedFields: jsonb("changed_fields").$type>(), oldValues: jsonb("old_values").$type>(), newValues: jsonb("new_values").$type>(), userId: integer("user_id"), userName: varchar("user_name", { length: 255 }), createdAt: timestamp("created_at").defaultNow().notNull(), isSynced: boolean("is_synced").default(false), syncAttempts: integer("sync_attempts").default(0), lastSyncError: text("last_sync_error"), syncedAt: timestamp("synced_at"), targetSystems: jsonb("target_systems").$type().default(sql`'[]'::jsonb`), // 동기화할 시스템 목록 }, (table) => { return { vendorSyncedIdx: index("idx_change_logs_vendor_synced").on( table.vendorId, table.isSynced ), createdAtIdx: index("idx_change_logs_created_at").on(table.createdAt), entityIdx: index("idx_change_logs_entity").on(table.entityType, table.entityId), syncAttemptsIdx: index("idx_change_logs_sync_attempts").on(table.syncAttempts), } } ) // 동기화 배치 테이블 (배치 단위로 동기화 관리) export const syncBatches = pgTable( "sync_batches", { id: serial("id").primaryKey(), vendorId: integer("vendor_id").notNull(), targetSystem: varchar("target_system", { length: 50 }).notNull(), batchSize: integer("batch_size").notNull(), status: varchar("status", { length: 20 }).notNull().default("PENDING"), // 'PENDING', 'PROCESSING', 'SUCCESS', 'FAILED', 'PARTIAL' startedAt: timestamp("started_at"), completedAt: timestamp("completed_at"), errorMessage: text("error_message"), retryCount: integer("retry_count").default(0), changeLogIds: jsonb("change_log_ids").$type().notNull(), // 포함된 change_log ID들 successCount: integer("success_count").default(0), failureCount: integer("failure_count").default(0), syncMetadata: jsonb("sync_metadata").$type>(), // 추가 메타데이터 createdAt: timestamp("created_at").defaultNow().notNull(), updatedAt: timestamp("updated_at").defaultNow().notNull(), }, (table) => { return { projectSystemIdx: index("idx_sync_batches_project_system").on( table.vendorId, table.targetSystem ), statusIdx: index("idx_sync_batches_status").on(table.status), createdAtIdx: index("idx_sync_batches_created_at").on(table.createdAt), } } ) // 동기화 상태 추적을 위한 뷰 export const syncStatusView = pgView("sync_status_view", { vendorId: integer("vendor_id").notNull(), targetSystem: varchar("target_system", { length: 50 }).notNull(), totalChanges: integer("total_changes").notNull(), pendingChanges: integer("pending_changes").notNull(), syncedChanges: integer("synced_changes").notNull(), failedChanges: integer("failed_changes").notNull(), lastSyncAt: timestamp("last_sync_at"), nextSyncAt: timestamp("next_sync_at"), syncEnabled: boolean("sync_enabled"), }).as(sql` WITH change_stats AS ( SELECT cl.vendor_id, sc.target_system, COUNT(*) as total_changes, COUNT(CASE WHEN cl.is_synced = false AND cl.sync_attempts < sc.retry_max_attempts THEN 1 END) as pending_changes, COUNT(CASE WHEN cl.is_synced = true THEN 1 END) as synced_changes, COUNT(CASE WHEN cl.sync_attempts >= sc.retry_max_attempts AND cl.is_synced = false THEN 1 END) as failed_changes, MAX(cl.synced_at) as last_sync_at FROM change_logs cl CROSS JOIN sync_configs sc WHERE cl.vendor_id = sc.vendor_id AND (cl.target_systems IS NULL OR cl.target_systems @> to_jsonb(ARRAY[sc.target_system])) GROUP BY cl.vendor_id, sc.target_system ) SELECT cs.vendor_id, cs.target_system, COALESCE(cs.total_changes, 0) as total_changes, COALESCE(cs.pending_changes, 0) as pending_changes, COALESCE(cs.synced_changes, 0) as synced_changes, COALESCE(cs.failed_changes, 0) as failed_changes, cs.last_sync_at, CASE WHEN sc.sync_enabled = true AND sc.last_successful_sync IS NOT NULL THEN sc.last_successful_sync + (sc.sync_interval_minutes || ' minutes')::interval ELSE NULL END as next_sync_at, sc.sync_enabled FROM sync_configs sc LEFT JOIN change_stats cs ON sc.vendor_id = cs.vendor_id AND sc.target_system = cs.target_system `) // 타입 추출 export type SyncConfig = typeof syncConfigs.$inferSelect export type SyncConfigInsert = typeof syncConfigs.$inferInsert export type ChangeLog = typeof changeLogs.$inferSelect export type ChangeLogInsert = typeof changeLogs.$inferInsert export type SyncBatch = typeof syncBatches.$inferSelect export type SyncBatchInsert = typeof syncBatches.$inferInsert export type SyncStatusView = typeof syncStatusView.$inferSelect export const simplifiedDocumentsView = pgView("simplified_documents_view", { // 기본 문서 정보 documentId: integer("document_id").notNull(), projectId: integer("project_id"), docNumber: varchar("doc_number", { length: 100 }).notNull(), drawingKind: varchar("drawing_kind", { length: 10 }), drawingMoveGbn: varchar("drawing_move_gbn", { length: 50 }), discipline: varchar("discipline", { length: 10 }), vendorDocNumber: varchar("vendor_doc_number", { length: 100 }), title: varchar("title", { length: 255 }).notNull(), pic: varchar("pic", { length: 50 }), status: varchar("status", { length: 50 }).notNull(), issuedDate: date("issued_date"), contractId: integer("contract_id"), vendorId: integer("vendor_id"), // 외부 시스템 연동 정보 externalDocumentId: varchar("external_document_id", { length: 100 }), externalSystemType: varchar("external_system_type", { length: 20 }), externalSyncedAt: timestamp("external_synced_at"), // DOLCE 응답의 추가 정보들 shiDrawingNo: varchar("shi_drawing_no", { length: 100 }), manager: varchar("manager", { length: 100 }), managerENM: varchar("manager_enm", { length: 100 }), managerNo: varchar("manager_no", { length: 50 }), registerGroup: integer("register_group"), registerGroupId: integer("register_group_id"), // 생성자 정보 createUserNo: varchar("create_user_no", { length: 50 }), createUserId: varchar("create_user_id", { length: 100 }), createUserENM: varchar("create_user_enm", { length: 100 }), // 프로젝트 및 벤더 정보 projectCode: varchar("project_code", { length: 50 }), vendorName: varchar("vendor_name", { length: 255 }), vendorCode: varchar("vendor_code", { length: 50 }), // B4 전용 필드들 cGbn: varchar("c_gbn", { length: 50 }), dGbn: varchar("d_gbn", { length: 50 }), degreeGbn: varchar("degree_gbn", { length: 50 }), deptGbn: varchar("dept_gbn", { length: 50 }), jGbn: varchar("j_gbn", { length: 50 }), sGbn: varchar("s_gbn", { length: 50 }), // 첫 번째 스테이지 날짜 정보 (drawingKind에 따라 다름) firstStageId: integer("first_stage_id"), firstStageName: varchar("first_stage_name", { length: 100 }), firstStagePlanDate: date("first_stage_plan_date"), firstStageActualDate: date("first_stage_actual_date"), // 두 번째 스테이지 날짜 정보 (drawingKind에 따라 다름) secondStageId: integer("second_stage_id"), secondStageName: varchar("second_stage_name", { length: 100 }), secondStagePlanDate: date("second_stage_plan_date"), secondStageActualDate: date("second_stage_actual_date"), // 전체 스테이지 목록 allStages: json("all_stages").$type; }>; }>>(), // 메타 정보 attachmentCount: integer("attachment_count"), createdAt: timestamp("created_at", { withTimezone: true }).notNull(), updatedAt: timestamp("updated_at", { withTimezone: true }).notNull(), }).as(sql` WITH -- 리비전별 첨부파일 집계 revision_attachments AS ( SELECT r.id as revision_id, COALESCE( json_agg( json_build_object( 'id', da.id, 'revisionId', da.revision_id, 'fileName', da.file_name, 'filePath', da.file_path, 'fileSize', da.file_size, 'fileType', da.file_type, 'dolceFilePath', da.dolce_file_path, 'createdAt', da.created_at, 'updatedAt', da.updated_at ) ORDER BY da.created_at ) FILTER (WHERE da.id IS NOT NULL), '[]'::json ) as attachments FROM revisions r LEFT JOIN document_attachments da ON r.id = da.revision_id GROUP BY r.id ), -- 스테이지별 리비전 집계 (첨부파일 포함) stage_revisions AS ( SELECT ist.id as stage_id, COALESCE( json_agg( json_build_object( 'id', r.id, 'issueStageId', r.issue_stage_id, 'revision', r.revision, 'uploaderType', r.uploader_type, 'uploaderId', r.uploader_id, 'uploaderName', r.uploader_name, 'comment', r.comment, 'usage', r.usage, 'usageType', r.usage_type, 'revisionStatus', r.revision_status, 'submittedDate', r.submitted_date, 'uploadedAt', r.uploaded_at, 'approvedDate', r.approved_date, 'reviewStartDate', r.review_start_date, 'rejectedDate', r.rejected_date, 'reviewerId', r.reviewer_id, 'reviewerName', r.reviewer_name, 'serialNo', r.serial_no, 'reviewComments', r.review_comments, 'createdAt', r.created_at, 'updatedAt', r.updated_at, 'attachments', COALESCE(ra.attachments, '[]'::json) ) ORDER BY r.created_at ) FILTER (WHERE r.id IS NOT NULL), '[]'::json ) as revisions FROM issue_stages ist LEFT JOIN revisions r ON ist.id = r.issue_stage_id LEFT JOIN revision_attachments ra ON r.id = ra.revision_id GROUP BY ist.id ), -- 문서별 스테이지 집계 (리비전 포함) stage_aggregation AS ( SELECT ist.document_id, json_agg( json_build_object( 'id', ist.id, 'stageName', ist.stage_name, 'stageStatus', ist.stage_status, 'stageOrder', ist.stage_order, 'planDate', ist.plan_date, 'actualDate', ist.actual_date, 'assigneeName', ist.assignee_name, 'priority', ist.priority, 'revisions', COALESCE(sr.revisions, '[]'::json) ) ORDER BY ist.stage_order ) as all_stages FROM issue_stages ist LEFT JOIN stage_revisions sr ON ist.id = sr.stage_id GROUP BY ist.document_id ), -- 첫 번째 스테이지 정보 (drawingKind에 따라 다른 조건) first_stage_info AS ( SELECT document_id, first_stage_id, first_stage_name, first_stage_plan_date, first_stage_actual_date FROM ( SELECT ist.document_id, ist.id as first_stage_id, ist.stage_name as first_stage_name, ist.plan_date as first_stage_plan_date, ist.actual_date as first_stage_actual_date, ROW_NUMBER() OVER (PARTITION BY ist.document_id ORDER BY ist.stage_order ASC) as rn FROM issue_stages ist JOIN documents d ON ist.document_id = d.id WHERE (d.drawing_kind = 'B4' AND LOWER(ist.stage_name) LIKE '%pre%') OR (d.drawing_kind = 'B3' AND LOWER(ist.stage_name) LIKE '%approval%') OR (d.drawing_kind = 'B5' AND LOWER(ist.stage_name) LIKE '%first%') ) ranked WHERE rn = 1 ), -- 두 번째 스테이지 정보 (drawingKind에 따라 다른 조건) second_stage_info AS ( SELECT document_id, second_stage_id, second_stage_name, second_stage_plan_date, second_stage_actual_date FROM ( SELECT ist.document_id, ist.id as second_stage_id, ist.stage_name as second_stage_name, ist.plan_date as second_stage_plan_date, ist.actual_date as second_stage_actual_date, ROW_NUMBER() OVER (PARTITION BY ist.document_id ORDER BY ist.stage_order ASC) as rn FROM issue_stages ist JOIN documents d ON ist.document_id = d.id WHERE (d.drawing_kind = 'B4' AND LOWER(ist.stage_name) LIKE '%work%') OR (d.drawing_kind = 'B3' AND LOWER(ist.stage_name) LIKE '%work%') OR (d.drawing_kind = 'B5' AND LOWER(ist.stage_name) LIKE '%second%') ) ranked WHERE rn = 1 ), -- 첨부파일 수 집계 attachment_counts AS ( SELECT ist.document_id, COUNT(da.id) as attachment_count FROM issue_stages ist LEFT JOIN revisions r ON ist.id = r.issue_stage_id LEFT JOIN document_attachments da ON r.id = da.revision_id GROUP BY ist.document_id ) SELECT d.id as document_id, d.project_id, d.vendor_id, d.doc_number, d.drawing_kind, d.drawing_move_gbn, d.discipline, d.vendor_doc_number, d.title, d.pic, d.status, d.issued_date, d.contract_id, -- 외부 시스템 연동 정보 d.external_document_id, d.external_system_type, d.external_synced_at, -- DOLCE 응답의 추가 정보들 d.shi_drawing_no, d.manager, d.manager_enm, d.manager_no, d.register_group, d.register_group_id, -- 생성자 정보 d.create_user_no, d.create_user_id, d.create_user_enm, -- 프로젝트 및 벤더 정보 p.code as project_code, v.vendor_name, v.vendor_code, -- B4 전용 필드들 d.c_gbn, d.d_gbn, d.degree_gbn, d.dept_gbn, d.s_gbn, d.j_gbn, -- 첫 번째 스테이지 정보 fsi.first_stage_id, fsi.first_stage_name, fsi.first_stage_plan_date, fsi.first_stage_actual_date, -- 두 번째 스테이지 정보 ssi.second_stage_id, ssi.second_stage_name, ssi.second_stage_plan_date, ssi.second_stage_actual_date, -- 전체 스테이지 (리비전 및 첨부파일 포함) COALESCE(sa.all_stages, '[]'::json) as all_stages, -- 기타 COALESCE(ac.attachment_count, 0) as attachment_count, d.created_at, d.updated_at FROM documents d -- projects, vendors 테이블 JOIN (projectId가 이제 documents에 직접 있음) LEFT JOIN projects p ON d.project_id = p.id AND p.type = 'ship' LEFT JOIN contracts c ON d.contract_id = c.id LEFT JOIN vendors v ON d.vendor_id = v.id -- 스테이지 정보 JOIN LEFT JOIN first_stage_info fsi ON d.id = fsi.document_id LEFT JOIN second_stage_info ssi ON d.id = ssi.document_id LEFT JOIN stage_aggregation sa ON d.id = sa.document_id LEFT JOIN attachment_counts ac ON d.id = ac.document_id ORDER BY d.created_at DESC `); // 타입 추출 export type SimplifiedDocumentsView = typeof simplifiedDocumentsView.$inferSelect export const documentStagesOnlyView = pgView("document_stages_only_view", { // 기본 문서 정보 documentId: integer("document_id").notNull(), docNumber: varchar("doc_number", { length: 100 }).notNull(), drawingKind: varchar("drawing_kind", { length: 50 }), vendorDocNumber: varchar("vendor_doc_number", { length: 100 }), title: varchar("title", { length: 255 }).notNull(), pic: varchar("pic", { length: 50 }), status: varchar("status", { length: 50 }).notNull(), issuedDate: date("issued_date"), contractId: integer("contract_id").notNull(), // 프로젝트 및 벤더 정보 projectCode: varchar("project_code", { length: 50 }), vendorName: varchar("vendor_name", { length: 255 }), vendorCode: varchar("vendor_code", { length: 50 }), vendorId: varchar("vendor_id", { length: 50 }), // 현재 스테이지 정보 (가장 우선순위가 높은 미완료 스테이지) currentStageId: integer("current_stage_id"), currentStageName: varchar("current_stage_name", { length: 100 }), currentStageStatus: varchar("current_stage_status", { length: 50 }), currentStageOrder: integer("current_stage_order"), currentStagePlanDate: date("current_stage_plan_date"), currentStageActualDate: date("current_stage_actual_date"), currentStageAssigneeName: varchar("current_stage_assignee_name", { length: 100 }), currentStagePriority: varchar("current_stage_priority", { length: 20 }), // 계산 필드 daysUntilDue: integer("days_until_due"), isOverdue: boolean("is_overdue"), daysDifference: integer("days_difference"), // 전체 진행률 totalStages: integer("total_stages"), completedStages: integer("completed_stages"), progressPercentage: integer("progress_percentage"), // 전체 스테이지 목록 (리비전 및 첨부파일 제외) allStages: jsonb("all_stages").$type>(), // 메타 정보 createdAt: timestamp("created_at", { withTimezone: true }).notNull(), updatedAt: timestamp("updated_at", { withTimezone: true }).notNull(), }).as(sql` WITH document_stats AS ( SELECT d.id as document_id, COUNT(ist.id) as total_stages, COUNT(CASE WHEN ist.stage_status IN ('COMPLETED', 'APPROVED') THEN 1 END) as completed_stages, CASE WHEN COUNT(ist.id) > 0 THEN ROUND((COUNT(CASE WHEN ist.stage_status IN ('COMPLETED', 'APPROVED') THEN 1 END) * 100.0) / COUNT(ist.id)) ELSE 0 END as progress_percentage FROM documents d LEFT JOIN issue_stages ist ON d.id = ist.document_id GROUP BY d.id ), current_stage_info AS ( SELECT DISTINCT ON (document_id) document_id, id as current_stage_id, stage_name as current_stage_name, stage_status as current_stage_status, stage_order as current_stage_order, plan_date as current_stage_plan_date, actual_date as current_stage_actual_date, assignee_name as current_stage_assignee_name, priority as current_stage_priority, CASE WHEN actual_date IS NULL AND plan_date IS NOT NULL THEN plan_date - CURRENT_DATE ELSE NULL END as days_until_due, CASE WHEN actual_date IS NULL AND plan_date < CURRENT_DATE THEN true WHEN actual_date IS NOT NULL AND actual_date > plan_date THEN true ELSE false END as is_overdue, CASE WHEN actual_date IS NOT NULL AND plan_date IS NOT NULL THEN actual_date - plan_date ELSE NULL END as days_difference FROM issue_stages WHERE stage_status NOT IN ('COMPLETED', 'APPROVED') ORDER BY document_id, stage_order ASC, priority DESC ), -- 문서별 스테이지 집계 (리비전 제외) stage_aggregation AS ( SELECT ist.document_id, json_agg( json_build_object( 'id', ist.id, 'stageName', ist.stage_name, 'stageStatus', ist.stage_status, 'stageOrder', ist.stage_order, 'planDate', ist.plan_date, 'actualDate', ist.actual_date, 'assigneeName', ist.assignee_name, 'priority', ist.priority, 'description', ist.description, 'notes', ist.notes, 'reminderDays', ist.reminder_days ) ORDER BY ist.stage_order ) as all_stages FROM issue_stages ist GROUP BY ist.document_id ) SELECT d.id as document_id, d.doc_number, d.drawing_kind, d.vendor_doc_number, d.title, d.pic, d.status, d.issued_date, d.contract_id, -- 프로젝트 및 벤더 정보 p.code as project_code, v.vendor_name as vendor_name, v.vendor_code as vendor_code, c.vendor_id as vendor_id, -- 현재 스테이지 정보 csi.current_stage_id, csi.current_stage_name, csi.current_stage_status, csi.current_stage_order, csi.current_stage_plan_date, csi.current_stage_actual_date, csi.current_stage_assignee_name, csi.current_stage_priority, -- 계산 필드 csi.days_until_due, csi.is_overdue, csi.days_difference, -- 진행률 정보 ds.total_stages, ds.completed_stages, ds.progress_percentage, -- 전체 스테이지 (리비전 제외) COALESCE(sa.all_stages, '[]'::json) as all_stages, -- 메타 정보 d.created_at, d.updated_at FROM documents d -- 프로젝트 및 벤더 정보 JOIN LEFT JOIN contracts c ON d.contract_id = c.id LEFT JOIN projects p ON c.project_id = p.id LEFT JOIN vendors v ON c.vendor_id = v.id -- 스테이지 관련 정보 JOIN LEFT JOIN document_stats ds ON d.id = ds.document_id LEFT JOIN current_stage_info csi ON d.id = csi.document_id LEFT JOIN stage_aggregation sa ON d.id = sa.document_id ORDER BY d.created_at DESC `); // 타입 추출 export type DocumentStagesOnlyView = typeof documentStagesOnlyView.$inferSelect export const stageDocuments = pgTable( "stage_documents", { id: integer("id").primaryKey().generatedAlwaysAsIdentity(), // 필수 문서 정보 docNumber: varchar("doc_number", { length: 100 }).notNull(), title: varchar("title", { length: 255 }).notNull(), status: varchar("status", { length: 50 }).notNull().default("ACTIVE"), // 선택적 문서 정보 vendorDocNumber: varchar("vendor_doc_number", { length: 100 }), issuedDate: date("issued_date"), // 직접 참조로 JOIN 최소화 projectId: integer("project_id") .notNull() .references(() => projects.id, { onDelete: "cascade" }), vendorId: integer("vendor_id").notNull(), // vendors 테이블의 vendor_id 직접 저장 contractId: integer("contract_id").notNull(), // 구매자 시스템 동기화 필드 추가 buyerSystemStatus: varchar("buyer_system_status", { length: 50 }), // 승인(DC), 검토중, 반려 등 buyerSystemComment: text("buyer_system_comment"), lastSyncedAt: timestamp("last_synced_at", { withTimezone: true }), syncStatus: varchar("sync_status", { length: 20 }).default("pending"), // pending, synced, error, syncing syncError: text("sync_error"), // 실시간 동기화를 위한 버전 관리 syncVersion: integer("sync_version").default(0), // 변경 추적용 lastModifiedBy: varchar("last_modified_by", { length: 100 }), // EVCP or BUYER_SYSTEM // 메타 정보 createdAt: timestamp("created_at").defaultNow().notNull(), updatedAt: timestamp("updated_at").defaultNow().notNull(), }, (table) => { return { // 프로젝트 내에서 문서 번호 유니크 uniqueProjectDoc: uniqueIndex("unique_project_doc").on( table.projectId, table.docNumber, table.status ), // 벤더 문서 번호 유니크 (있는 경우) uniqueProjectVendorDoc: uniqueIndex("unique_project_vendor_doc").on( table.projectId, table.vendorDocNumber ).where(sql`${table.vendorDocNumber} IS NOT NULL`), // 검색용 인덱스 vendorIdIndex: index("stage_doc_vendor_id_idx").on(table.vendorId), statusIndex: index("stage_doc_status_idx").on(table.status), // 동기화 관련 인덱스 추가 syncStatusIndex: index("stage_doc_sync_status_idx").on(table.syncStatus), buyerStatusIndex: index("stage_doc_buyer_status_idx").on(table.buyerSystemStatus), } } ) // 🎯 간소화된 스테이지 전용 뷰 // db/schema/stage-documents.ts export const stageDocumentsView = pgView("stage_documents_view", { // 기본 문서 정보 documentId: integer("document_id").notNull(), docNumber: varchar("doc_number", { length: 100 }).notNull(), vendorDocNumber: varchar("vendor_doc_number", { length: 100 }), title: varchar("title", { length: 255 }).notNull(), status: varchar("status", { length: 50 }).notNull(), issuedDate: date("issued_date"), // 프로젝트 및 벤더 정보 projectId: integer("project_id").notNull(), contractId: integer("contract_id").notNull(), projectCode: varchar("project_code", { length: 50 }), vendorId: integer("vendor_id").notNull(), vendorName: varchar("vendor_name", { length: 255 }), vendorCode: varchar("vendor_code", { length: 50 }), // 동기화 상태 필드 추가 buyerSystemStatus: varchar("buyer_system_status", { length: 50 }), buyerSystemComment: text("buyer_system_comment"), lastSyncedAt: timestamp("last_synced_at", { withTimezone: true }), syncStatus: varchar("sync_status", { length: 20 }), syncError: text("sync_error"), syncVersion: integer("sync_version"), lastModifiedBy: varchar("last_modified_by", { length: 100 }), // 현재 스테이지 정보 currentStageId: integer("current_stage_id"), currentStageName: varchar("current_stage_name", { length: 100 }), currentStageStatus: varchar("current_stage_status", { length: 50 }), currentStageOrder: integer("current_stage_order"), currentStagePlanDate: date("current_stage_plan_date"), currentStageActualDate: date("current_stage_actual_date"), currentStageAssigneeName: varchar("current_stage_assignee_name", { length: 100 }), currentStagePriority: varchar("current_stage_priority", { length: 20 }), // 계산 필드 daysUntilDue: integer("days_until_due"), isOverdue: boolean("is_overdue"), daysDifference: integer("days_difference"), // 전체 진행률 totalStages: integer("total_stages"), completedStages: integer("completed_stages"), progressPercentage: integer("progress_percentage"), // 전체 스테이지 목록 allStages: jsonb("all_stages").$type>(), // 메타 정보 createdAt: timestamp("created_at", { withTimezone: true }).notNull(), updatedAt: timestamp("updated_at", { withTimezone: true }).notNull(), }).as(sql` WITH document_stats AS ( SELECT sd.id as document_id, COUNT(ist.id) as total_stages, COUNT(CASE WHEN ist.stage_status IN ('COMPLETED', 'APPROVED') THEN 1 END) as completed_stages, CASE WHEN COUNT(ist.id) > 0 THEN ROUND((COUNT(CASE WHEN ist.stage_status IN ('COMPLETED', 'APPROVED') THEN 1 END) * 100.0) / COUNT(ist.id)) ELSE 0 END as progress_percentage FROM stage_documents sd LEFT JOIN stage_issue_stages ist ON sd.id = ist.document_id GROUP BY sd.id ), current_stage_info AS ( SELECT DISTINCT ON (document_id) document_id, id as current_stage_id, stage_name as current_stage_name, stage_status as current_stage_status, stage_order as current_stage_order, plan_date as current_stage_plan_date, actual_date as current_stage_actual_date, assignee_name as current_stage_assignee_name, priority as current_stage_priority, CASE WHEN actual_date IS NULL AND plan_date IS NOT NULL THEN plan_date - CURRENT_DATE ELSE NULL END as days_until_due, CASE WHEN actual_date IS NULL AND plan_date < CURRENT_DATE THEN true WHEN actual_date IS NOT NULL AND actual_date > plan_date THEN true ELSE false END as is_overdue, CASE WHEN actual_date IS NOT NULL AND plan_date IS NOT NULL THEN actual_date - plan_date ELSE NULL END as days_difference FROM stage_issue_stages WHERE stage_status NOT IN ('COMPLETED', 'APPROVED') ORDER BY document_id, stage_order ASC, priority DESC ), stage_aggregation AS ( SELECT ist.document_id, json_agg( json_build_object( 'id', ist.id, 'stageName', ist.stage_name, 'stageStatus', ist.stage_status, 'stageOrder', ist.stage_order, 'planDate', ist.plan_date, 'actualDate', ist.actual_date, 'assigneeName', ist.assignee_name, 'priority', ist.priority, 'description', ist.description, 'notes', ist.notes, 'reminderDays', ist.reminder_days ) ORDER BY ist.stage_order ) as all_stages FROM stage_issue_stages ist GROUP BY ist.document_id ) SELECT sd.id as document_id, sd.doc_number, sd.vendor_doc_number, sd.title, sd.status, sd.issued_date, -- 프로젝트 및 벤더 정보 sd.project_id, sd.contract_id, p.code as project_code, sd.vendor_id, v.vendor_name, v.vendor_code, -- 동기화 상태 필드 추가 sd.buyer_system_status, sd.buyer_system_comment, sd.last_synced_at, sd.sync_status, sd.sync_error, sd.sync_version, sd.last_modified_by, -- 현재 스테이지 정보 csi.current_stage_id, csi.current_stage_name, csi.current_stage_status, csi.current_stage_order, csi.current_stage_plan_date, csi.current_stage_actual_date, csi.current_stage_assignee_name, csi.current_stage_priority, -- 계산 필드 csi.days_until_due, csi.is_overdue, csi.days_difference, -- 진행률 정보 ds.total_stages, ds.completed_stages, ds.progress_percentage, -- 전체 스테이지 COALESCE(sa.all_stages, '[]'::json) as all_stages, -- 메타 정보 sd.created_at, sd.updated_at FROM stage_documents sd LEFT JOIN projects p ON sd.project_id = p.id LEFT JOIN vendors v ON sd.vendor_id = v.id LEFT JOIN document_stats ds ON sd.id = ds.document_id LEFT JOIN current_stage_info csi ON sd.id = csi.document_id LEFT JOIN stage_aggregation sa ON sd.id = sa.document_id ORDER BY sd.created_at DESC `); // 🎯 issue_stages 테이블도 stage_documents를 참조하도록 수정 export const stageIssueStages = pgTable( "stage_issue_stages", // 또는 기존 issue_stages 테이블을 수정 { id: integer("id").primaryKey().generatedAlwaysAsIdentity(), documentId: integer("document_id") .notNull() .references(() => stageDocuments.id, { onDelete: "cascade" }), stageName: varchar("stage_name", { length: 100 }).notNull(), // 기존 필드들 planDate: date("plan_date"), actualDate: date("actual_date"), // 스케줄 관리 필드들 stageStatus: varchar("stage_status", { length: 50 }) .notNull() .default("PLANNED"), // PLANNED, IN_PROGRESS, SUBMITTED, APPROVED, REJECTED, COMPLETED stageOrder: integer("stage_order").default(0), priority: varchar("priority", { length: 20 }).default("MEDIUM"), // HIGH, MEDIUM, LOW // 담당자 정보 assigneeId: integer("assignee_id"), assigneeName: varchar("assignee_name", { length: 100 }), // 알림 및 추가 정보 reminderDays: integer("reminder_days").default(3), description: varchar("description", { length: 500 }), notes: varchar("notes", { length: 1000 }), createdAt: timestamp("created_at").defaultNow().notNull(), updatedAt: timestamp("updated_at").defaultNow().notNull(), }, (table) => { return { uniqueDocumentStage: uniqueIndex("unique_stage_document_stage").on( table.documentId, table.stageName ), documentStageOrder: uniqueIndex("stage_document_stage_order").on( table.documentId, table.stageOrder ), } } ); // 타입 추출 export type StageDocument = typeof stageDocuments.$inferSelect export type StageDocumentInsert = typeof stageDocuments.$inferInsert export type StageDocumentsView = typeof stageDocumentsView.$inferSelect export type StageIssueStage = typeof stageIssueStages.$inferSelect export type StageIssueStageInsert = typeof stageIssueStages.$inferInsert // 📄 스테이지 제출 테이블 - 구매자 시스템 동기화 필드 추가 export const stageSubmissions = pgTable( "stage_submissions", { id: integer("id").primaryKey().generatedAlwaysAsIdentity(), // 참조 관계 stageId: integer("stage_id") .notNull() .references(() => stageIssueStages.id, { onDelete: "cascade" }), documentId: integer("document_id") .notNull() .references(() => stageDocuments.id, { onDelete: "cascade" }), // 리비전 관리 revisionNumber: integer("revision_number").notNull().default(1), revisionCode: varchar("revision_code", { length: 50 }).notNull(), // 새 필드: "Rev0", "A", "1" 등 revisionType: varchar("revision_type", { length: 50 }) .default("INITIAL"), // INITIAL, RESUBMISSION, CORRECTION // 제출 정보 submissionStatus: varchar("submission_status", { length: 50 }) .notNull() .default("DRAFT"), // DRAFT, SUBMITTED, UNDER_REVIEW, APPROVED, REJECTED, WITHDRAWN // 제출자 정보 submittedBy: varchar("submitted_by", { length: 100 }).notNull(), submittedByEmail: varchar("submitted_by_email", { length: 255 }), submittedAt: timestamp("submitted_at", { withTimezone: true }).notNull().defaultNow(), // 검토자 정보 reviewedBy: varchar("reviewed_by", { length: 100 }), reviewedByEmail: varchar("reviewed_by_email", { length: 255 }), reviewedAt: timestamp("reviewed_at", { withTimezone: true }), // 제출 내용 submissionTitle: varchar("submission_title", { length: 500 }), submissionDescription: text("submission_description"), submissionNotes: text("submission_notes"), // 반려/승인 정보 reviewStatus: varchar("review_status", { length: 50 }), // PENDING, APPROVED, REJECTED, CONDITIONAL reviewComments: text("review_comments"), rejectionReason: text("rejection_reason"), // 벤더 정보 (denormalized for quick access) vendorId: integer("vendor_id").notNull(), vendorCode: varchar("vendor_code", { length: 50 }), // 메타데이터 totalFiles: integer("total_files").default(0), totalFileSize: bigint("total_file_size", { mode: "number" }).default(0), // bytes // ✨ 구매자 시스템 동기화 정보 (제출 레벨) buyerSystemStatus: varchar("buyer_system_status", { length: 50 }), // PENDING, SYNCING, SYNCED, FAILED, PARTIAL buyerSystemComment: text("buyer_system_comment"), buyerSystemSubmissionId: varchar("buyer_system_submission_id", { length: 255 }), // 구매자 시스템의 제출 ID lastSyncedAt: timestamp("last_synced_at", { withTimezone: true }), syncStatus: varchar("sync_status", { length: 20 }).default("pending"), // pending, syncing, synced, failed, partial syncError: text("sync_error"), syncVersion: integer("sync_version").default(0), lastModifiedBy: varchar("last_modified_by", { length: 100 }), // EVCP, BUYER_SYSTEM syncRetryCount: integer("sync_retry_count").default(0), nextRetryAt: timestamp("next_retry_at", { withTimezone: true }), // 동기화 통계 totalFilesToSync: integer("total_files_to_sync").default(0), syncedFilesCount: integer("synced_files_count").default(0), failedFilesCount: integer("failed_files_count").default(0), // 타임스탬프 createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(), updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(), }, (table) => { return { // 한 스테이지에서 리비전 번호는 유니크 uniqueStageRevision: uniqueIndex("unique_stage_revision").on( table.stageId, table.revisionNumber ), // 검색용 인덱스 documentIdIndex: index("submission_document_id_idx").on(table.documentId), vendorIdIndex: index("submission_vendor_id_idx").on(table.vendorId), statusIndex: index("submission_status_idx").on(table.submissionStatus), submittedAtIndex: index("submission_submitted_at_idx").on(table.submittedAt), // ✨ 동기화 관련 인덱스 syncStatusIndex: index("submission_sync_status_idx").on(table.syncStatus), buyerSystemStatusIndex: index("submission_buyer_status_idx").on(table.buyerSystemStatus), nextRetryIndex: index("submission_next_retry_idx").on(table.nextRetryAt), } } ); // 📎 제출 첨부파일 테이블 - 구매자 시스템 동기화 필드 추가 export const stageSubmissionAttachments = pgTable( "stage_submission_attachments", { id: integer("id").primaryKey().generatedAlwaysAsIdentity(), // 참조 관계 submissionId: integer("submission_id") .notNull() .references(() => stageSubmissions.id, { onDelete: "cascade" }), // 파일 정보 fileName: varchar("file_name", { length: 255 }).notNull(), originalFileName: varchar("original_file_name", { length: 255 }).notNull(), fileType: varchar("file_type", { length: 100 }), // application/pdf, image/jpeg, etc. fileExtension: varchar("file_extension", { length: 20 }), fileSize: bigint("file_size", { mode: "number" }).notNull(), // bytes // 저장 정보 storageType: varchar("storage_type", { length: 50 }) .default("S3"), // S3, LOCAL, AZURE, GCS storagePath: varchar("storage_path", { length: 1000 }).notNull(), storageUrl: varchar("storage_url", { length: 2000 }), bucketName: varchar("bucket_name", { length: 255 }), // 파일 메타데이터 mimeType: varchar("mime_type", { length: 100 }), checksum: varchar("checksum", { length: 255 }), // MD5 or SHA256 // 문서 분류 (옵션) documentType: varchar("document_type", { length: 100 }), // DRAWING, SPEC, REPORT, CERTIFICATE, etc. documentCategory: varchar("document_category", { length: 100 }), // 버전 관리 fileVersion: varchar("file_version", { length: 50 }), isLatest: boolean("is_latest").default(true), // 업로드 정보 uploadedBy: varchar("uploaded_by", { length: 100 }).notNull(), uploadedAt: timestamp("uploaded_at", { withTimezone: true }).defaultNow().notNull(), // 상태 status: varchar("status", { length: 50 }) .default("ACTIVE"), // ACTIVE, DELETED, ARCHIVED // ✨ 구매자 시스템 동기화 정보 (파일 레벨) buyerSystemFileId: varchar("buyer_system_file_id", { length: 255 }), // 구매자 시스템의 파일 ID buyerSystemStatus: varchar("buyer_system_status", { length: 50 }), // PENDING, UPLOADING, UPLOADED, FAILED buyerSystemComment: text("buyer_system_comment"), buyerSystemUrl: varchar("buyer_system_url", { length: 2000 }), // 구매자 시스템에서의 파일 URL // 동기화 상태 syncStatus: varchar("sync_status", { length: 20 }).default("pending"), // pending, syncing, synced, failed syncStartedAt: timestamp("sync_started_at", { withTimezone: true }), syncCompletedAt: timestamp("sync_completed_at", { withTimezone: true }), syncError: text("sync_error"), syncRetryCount: integer("sync_retry_count").default(0), lastSyncAttempt: timestamp("last_sync_attempt", { withTimezone: true }), // 전송 정보 transferMethod: varchar("transfer_method", { length: 50 }), // API, FTP, SFTP, EMAIL transferredBytes: bigint("transferred_bytes", { mode: "number" }), // 실제 전송된 바이트 transferProgress: integer("transfer_progress"), // 0-100 퍼센트 // 동기화 메타데이터 syncVersion: integer("sync_version").default(0), lastModifiedBy: varchar("last_modified_by", { length: 100 }), // EVCP, BUYER_SYSTEM // 보안/접근 제어 isPublic: boolean("is_public").default(false), expiresAt: timestamp("expires_at", { withTimezone: true }), // 추가 메타데이터 metadata: jsonb("metadata").$type<{ pageCount?: number; dimensions?: { width: number; height: number }; duration?: number; // for videos buyerSystemMetadata?: any; // 구매자 시스템에서 받은 추가 정보 [key: string]: any; }>(), // 타임스탬프 createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(), updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(), }, (table) => { return { // 검색용 인덱스 submissionIdIndex: index("attachment_submission_id_idx").on(table.submissionId), fileTypeIndex: index("attachment_file_type_idx").on(table.fileType), statusIndex: index("attachment_status_idx").on(table.status), uploadedAtIndex: index("attachment_uploaded_at_idx").on(table.uploadedAt), // ✨ 동기화 관련 인덱스 syncStatusIndex: index("attachment_sync_status_idx").on(table.syncStatus), buyerSystemFileIdIndex: index("attachment_buyer_file_id_idx").on(table.buyerSystemFileId), buyerSystemStatusIndex: index("attachment_buyer_status_idx").on(table.buyerSystemStatus), } } ); // 🔍 제출 현황 뷰 수정 - 동기화 상태 포함 export const stageSubmissionView = pgView("stage_submission_view", { // 문서 정보 documentId: integer("document_id").notNull(), docNumber: varchar("doc_number", { length: 100 }).notNull(), vendorDocNumber: varchar("vendor_doc_number", { length: 100 }), documentTitle: varchar("document_title", { length: 255 }).notNull(), // 프로젝트/벤더 정보 projectId: integer("project_id").notNull(), projectCode: varchar("project_code", { length: 50 }), vendorId: integer("vendor_id").notNull(), vendorName: varchar("vendor_name", { length: 255 }), vendorCode: varchar("vendor_code", { length: 50 }), // 스테이지 정보 stageId: integer("stage_id"), stageName: varchar("stage_name", { length: 100 }), stageOrder: integer("stage_order"), stageStatus: varchar("stage_status", { length: 50 }), stagePlanDate: date("stage_plan_date"), // 최신 제출 정보 latestSubmissionId: integer("latest_submission_id"), latestRevisionNumber: integer("latest_revision_number"), latestRevisionCode: varchar("latest_revision_code", { length: 50 }), // ⭐ 추가 latestSubmissionStatus: varchar("latest_submission_status", { length: 50 }), latestSubmittedAt: timestamp("latest_submitted_at", { withTimezone: true }), latestSubmittedBy: varchar("latest_submitted_by", { length: 100 }), latestReviewStatus: varchar("latest_review_status", { length: 50 }), // ✨ 동기화 상태 latestSyncStatus: varchar("latest_sync_status", { length: 20 }), latestBuyerSystemStatus: varchar("latest_buyer_system_status", { length: 50 }), lastSyncedAt: timestamp("last_synced_at", { withTimezone: true }), totalFilesToSync: integer("total_files_to_sync"), syncedFilesCount: integer("synced_files_count"), syncProgress: integer("sync_progress"), // 0-100 퍼센트 // 제출 통계 totalSubmissions: integer("total_submissions"), approvedSubmissions: integer("approved_submissions"), rejectedSubmissions: integer("rejected_submissions"), pendingSubmissions: integer("pending_submissions"), // 파일 정보 totalFiles: integer("total_files"), totalFileSize: bigint("total_file_size", { mode: "number" }), // 제출 필요 여부 requiresSubmission: boolean("requires_submission"), requiresSync: boolean("requires_sync"), isOverdue: boolean("is_overdue"), daysUntilDue: integer("days_until_due"), // 제출 이력 (revisionCode 추가) submissionHistory: jsonb("submission_history").$type>(), }).as(sql` WITH eligible_documents AS ( -- buyerSystemStatus가 '승인(DC)'인 문서만 선택 SELECT sd.id as document_id, sd.doc_number, sd.vendor_doc_number, sd.title as document_title, sd.project_id, sd.vendor_id, p.code as project_code, v.vendor_name, v.vendor_code FROM stage_documents sd LEFT JOIN projects p ON sd.project_id = p.id LEFT JOIN vendors v ON sd.vendor_id = v.id WHERE sd.buyer_system_status = '승인(DC)' AND sd.status = 'ACTIVE' ), submission_stats AS ( SELECT ss.stage_id, COUNT(*) as total_submissions, COUNT(CASE WHEN ss.review_status = 'APPROVED' THEN 1 END) as approved_submissions, COUNT(CASE WHEN ss.review_status = 'REJECTED' THEN 1 END) as rejected_submissions, COUNT(CASE WHEN ss.review_status = 'PENDING' OR ss.review_status IS NULL THEN 1 END) as pending_submissions, SUM(ss.total_files) as total_files, SUM(ss.total_file_size) as total_file_size, MAX(ss.revision_number) as latest_revision_number FROM stage_submissions ss GROUP BY ss.stage_id ), latest_submission AS ( SELECT DISTINCT ON (stage_id) stage_id, id as submission_id, revision_number, revision_code, -- ⭐ 추가 submission_status, submitted_at, submitted_by, review_status, sync_status, buyer_system_status, last_synced_at, total_files_to_sync, synced_files_count, CASE WHEN total_files_to_sync > 0 THEN ROUND((synced_files_count * 100.0) / total_files_to_sync) ELSE 0 END as sync_progress FROM stage_submissions ORDER BY stage_id, revision_number DESC ), submission_history_agg AS ( SELECT ss.stage_id, json_agg( json_build_object( 'submissionId', ss.id, 'revisionNumber', ss.revision_number, 'revisionCode', ss.revision_code, -- ⭐ 추가 'status', ss.submission_status, 'submittedAt', ss.submitted_at, 'submittedBy', ss.submitted_by, 'reviewStatus', ss.review_status, 'syncStatus', ss.sync_status, 'fileCount', ss.total_files ) ORDER BY ss.revision_number DESC ) as submission_history FROM stage_submissions ss GROUP BY ss.stage_id ) SELECT ed.document_id, ed.doc_number, ed.vendor_doc_number, ed.document_title, ed.project_id, ed.project_code, ed.vendor_id, ed.vendor_name, ed.vendor_code, ist.id as stage_id, ist.stage_name, ist.stage_order, ist.stage_status, ist.plan_date as stage_plan_date, ls.submission_id as latest_submission_id, ls.revision_number as latest_revision_number, ls.revision_code as latest_revision_code, -- ⭐ 추가 ls.submission_status as latest_submission_status, ls.submitted_at as latest_submitted_at, ls.submitted_by as latest_submitted_by, ls.review_status as latest_review_status, -- 동기화 상태 ls.sync_status as latest_sync_status, ls.buyer_system_status as latest_buyer_system_status, ls.last_synced_at, ls.total_files_to_sync, ls.synced_files_count, ls.sync_progress, COALESCE(ss.total_submissions, 0) as total_submissions, COALESCE(ss.approved_submissions, 0) as approved_submissions, COALESCE(ss.rejected_submissions, 0) as rejected_submissions, COALESCE(ss.pending_submissions, 0) as pending_submissions, COALESCE(ss.total_files, 0) as total_files, COALESCE(ss.total_file_size, 0) as total_file_size, -- 제출이 필요한지 판단 CASE WHEN ist.stage_status IN ('PLANNED', 'IN_PROGRESS', 'REJECTED') AND (ls.review_status IS NULL OR ls.review_status != 'APPROVED') THEN true ELSE false END as requires_submission, -- 동기화가 필요한지 판단 CASE WHEN ls.sync_status IN ('pending', 'failed') OR ls.synced_files_count < ls.total_files_to_sync THEN true ELSE false END as requires_sync, -- 기한 관련 CASE WHEN ist.plan_date < CURRENT_DATE AND ist.stage_status NOT IN ('COMPLETED', 'APPROVED') THEN true ELSE false END as is_overdue, CASE WHEN ist.plan_date IS NOT NULL THEN ist.plan_date - CURRENT_DATE ELSE NULL END as days_until_due, COALESCE(sha.submission_history, '[]'::json) as submission_history FROM eligible_documents ed INNER JOIN stage_issue_stages ist ON ed.document_id = ist.document_id LEFT JOIN submission_stats ss ON ist.id = ss.stage_id LEFT JOIN latest_submission ls ON ist.id = ls.stage_id LEFT JOIN submission_history_agg sha ON ist.id = sha.stage_id ORDER BY ed.document_id, ist.stage_order `); export type StageSubmissionView = typeof stageSubmissionView.$inferSelect