// enhanced-schema.ts import { pgTable, integer, varchar, timestamp, date, pgView, uniqueIndex, jsonb, boolean, serial, index, text } 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 }), contractId: integer("contract_id") .notNull() .references(() => contracts.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.status ), uniqueContractVendorDoc: uniqueIndex("unique_contract_vendor_doc").on( table.contractId, table.vendorDocNumber ).where(sql`${table.vendorDocNumber} IS NOT NULL`), // ✅ 외부 시스템 문서 ID 유니크 인덱스 uniqueExternalDoc: uniqueIndex("unique_external_doc").on( table.contractId, table.externalDocumentId, 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 }), // 상대 시스템에서 생성한 ID }, (table) => { return { uniqueStageRevisionUsage: uniqueIndex("unique_stage_revision_usage").on( table.issueStageId, table.revision, table.usage, 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(), // ✅ 프로젝트 및 벤더 정보 추가 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, -- ✅ 프로젝트 및 벤더 정보 추가 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(), contractId: integer("contract_id") .notNull() .references(() => contracts.id, { onDelete: "cascade" }), 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.contractId, table.targetSystem ), } } ) // 변경 로그 테이블 (모든 변경사항 추적) export const changeLogs = pgTable( "change_logs", { id: serial("id").primaryKey(), contractId: integer("contract_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 { contractSyncedIdx: index("idx_change_logs_contract_synced").on( table.contractId, 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(), contractId: integer("contract_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 { contractSystemIdx: index("idx_sync_batches_contract_system").on( table.contractId, 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", { contractId: integer("contract_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.contract_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.contract_id = sc.contract_id AND (cl.target_systems IS NULL OR cl.target_systems @> to_jsonb(sc.target_system)) GROUP BY cl.contract_id, sc.target_system ) SELECT cs.contract_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.contract_id = cs.contract_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(), 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 }), // 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: 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 -- 리비전별 첨부파일 집계 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, '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, '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 ), -- 첫 번째 스테이지 정보 (drawingKind에 따라 다른 조건) first_stage_info AS ( SELECT DISTINCT ON (ist.document_id) 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 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%') ORDER BY ist.document_id, ist.stage_order ASC ), -- 두 번째 스테이지 정보 (drawingKind에 따라 다른 조건) second_stage_info AS ( SELECT DISTINCT ON (ist.document_id) 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 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%') ORDER BY ist.document_id, ist.stage_order ASC ), -- 첨부파일 수 집계 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, -- B4 전용 필드들 d.c_gbn, d.d_gbn, d.degree_gbn, d.dept_gbn, d.s_gbn, d.j_gbn, -- 프로젝트 및 벤더 정보 p.code as project_code, v.vendor_name as vendor_name, v.vendor_code as vendor_code, -- 첫 번째 스테이지 정보 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 -- contracts, projects, vendors 테이블 JOIN LEFT JOIN contracts c ON d.contract_id = c.id INNER JOIN projects p ON c.project_id = p.id AND p.type = 'ship' LEFT JOIN vendors v ON c.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