diff options
Diffstat (limited to 'db/schema/vendorDocu.ts')
| -rw-r--r-- | db/schema/vendorDocu.ts | 609 |
1 files changed, 500 insertions, 109 deletions
diff --git a/db/schema/vendorDocu.ts b/db/schema/vendorDocu.ts index 8eb9cbc6..ad49f031 100644 --- a/db/schema/vendorDocu.ts +++ b/db/schema/vendorDocu.ts @@ -1,43 +1,31 @@ -import { pgTable, integer, varchar, timestamp, date, pgView, uniqueIndex, jsonb } from "drizzle-orm/pg-core" +// 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(), - - // documentType: varchar("document_type", { length: 50 }).notNull(), - pic: varchar("pic", { length: 50 }), - - // 어느 계약(Contract) 소속인지 contractId: integer("contract_id") .notNull() .references(() => contracts.id, { onDelete: "cascade" }), - - // 예: 문서 번호(유니크 설정 가능) docNumber: varchar("doc_number", { length: 100 }).notNull(), - // 예: 문서(도서) 제목 title: varchar("title", { length: 255 }).notNull(), - - // 추가 예시: 발행일, 상태 등 status: varchar("status", { length: 50 }) .notNull() .default("ACTIVE"), issuedDate: date("issued_date"), - - // 생성/수정 시각 createdAt: timestamp("created_at").defaultNow().notNull(), updatedAt: timestamp("updated_at").defaultNow().notNull(), }, (table) => { return { - // contractId + docNumber + status 복합 유니크 uniqueContractDocStatus: uniqueIndex("unique_contract_doc_status").on( table.contractId, table.docNumber, @@ -47,60 +35,86 @@ export const documents = pgTable( } ) +// 확장된 issueStages 테이블 export const issueStages = pgTable( "issue_stages", { - // 주 키 id: integer("id").primaryKey().generatedAlwaysAsIdentity(), - - // 어느 문서 소속인지 documentId: integer("document_id") .notNull() .references(() => documents.id, { onDelete: "cascade" }), - - // 스테이지명 (예: "Issued for Review", "AFC" 등) 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 { - // document_id + stage_name 조합을 유니크 인덱스로 지정 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"), - // 선택적: 업로더 ID 또는 이름 uploaderId: integer("uploader_id"), uploaderName: varchar("uploader_name", { length: 100 }), - // 선택적: 추가 메타데이터 - comment: varchar("comment", { length: 500 }), - status: varchar("status", { length: 50 }), + + // 확장된 상태 관리 + revisionStatus: varchar("revision_status", { length: 50 }) + .notNull() + .default("SUBMITTED"), // SUBMITTED, UNDER_REVIEW, APPROVED, REJECTED, SUPERSEDED + + // 상세 날짜 추적 + submittedDate: date("submitted_date"), + 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 }), + + comment: varchar("comment", { length: 500 }), createdAt: timestamp("created_at").defaultNow().notNull(), updatedAt: timestamp("updated_at").defaultNow().notNull(), }, (table) => { return { - // "issue_stage_id + revision" 조합을 유니크로 묶음 (유지) uniqueStageRev: uniqueIndex("unique_stage_rev").on( table.issueStageId, table.revision @@ -108,6 +122,8 @@ export const revisions = pgTable( } } ) + +// 기존 documentAttachments (변경 없음) export const documentAttachments = pgTable( "document_attachments", { @@ -124,29 +140,308 @@ export const documentAttachments = pgTable( } ) + +export const enhancedDocumentsView = pgView("enhanced_documents_view", { + // 기본 문서 정보 + documentId: integer("document_id").notNull(), + docNumber: varchar("doc_number", { length: 100 }).notNull(), + 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(), + + // 현재 스테이지 정보 (가장 우선순위가 높은 미완료 스테이지) + 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<Array<{ + id: number; + stageName: string; + stageStatus: string; + stageOrder: number; + planDate: string | null; + actualDate: string | null; + assigneeName: string | null; + priority: string; + revisions: Array<{ + id: number; + issueStageId: number; + revision: string; + uploaderType: string; + uploaderId: number | null; + uploaderName: string | null; + comment: string | null; + revisionStatus: string; + submittedDate: string | null; + approvedDate: string | null; + reviewStartDate: string | null; + rejectedDate: string | null; + reviewerId: number | null; + reviewerName: string | null; + reviewComments: string | null; + createdAt: Date; + updatedAt: Date; + attachments: Array<{ + id: number; + revisionId: number; + fileName: string; + filePath: string; + fileSize: number | null; + fileType: string | null; + createdAt: Date; + updatedAt: Date; + }>; + }>; + }>>(), + + // 메타 정보 + 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, + 'revisionStatus', r.revision_status, + 'submittedDate', r.submitted_date, + '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.title, + d.pic, + d.status, + d.issued_date, + d.contract_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 + 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", { - // Match the columns in your SELECT statement 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"), // possibly can be null + 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` @@ -157,61 +452,19 @@ export const vendorDocumentsView = pgView("vendor_documents_view", { 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 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 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, + (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 @@ -219,7 +472,6 @@ export const vendorDocumentsView = pgView("vendor_documents_view", { 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(), @@ -228,11 +480,7 @@ export const documentStagesView = pgView("document_stages_view", { issuedDate: date("issued_date"), contractId: integer("contract_id").notNull(), stageCount: integer("stage_count").notNull(), - - // 문자열 배열을 받을 것이므로 jsonb + $type<string[]>() - // 스테이지가 없으면 null이 올 수도 있다면 string[] | null stageList: jsonb("stage_list").$type<string[] | null>(), - createdAt: timestamp("created_at", { withTimezone: true }).notNull(), updatedAt: timestamp("updated_at", { withTimezone: true }).notNull(), }).as(sql` @@ -243,26 +491,169 @@ export const documentStagesView = pgView("document_stages_view", { d.status, d.issued_date, d.contract_id, - - ( - SELECT COUNT(*) - FROM issue_stages - WHERE document_id = d.id - ) AS stage_count, - + (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 - ), + (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<Record<string, any>>(), + oldValues: jsonb("old_values").$type<Record<string, any>>(), + newValues: jsonb("new_values").$type<Record<string, any>>(), + 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<string[]>().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<number[]>().notNull(), // 포함된 change_log ID들 + successCount: integer("success_count").default(0), + failureCount: integer("failure_count").default(0), + syncMetadata: jsonb("sync_metadata").$type<Record<string, any>>(), // 추가 메타데이터 + 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 |