diff options
Diffstat (limited to 'db/schema/vendorDocu.ts')
| -rw-r--r-- | db/schema/vendorDocu.ts | 290 |
1 files changed, 290 insertions, 0 deletions
diff --git a/db/schema/vendorDocu.ts b/db/schema/vendorDocu.ts index 1fda2285..58c0ad29 100644 --- a/db/schema/vendorDocu.ts +++ b/db/schema/vendorDocu.ts @@ -21,6 +21,10 @@ export const documents = pgTable( // .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 }), @@ -810,6 +814,7 @@ export const simplifiedDocumentsView = pgView("simplified_documents_view", { 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 }), @@ -1054,6 +1059,7 @@ export const simplifiedDocumentsView = pgView("simplified_documents_view", { SELECT d.id as document_id, d.project_id, + d.vendor_id, d.doc_number, d.drawing_kind, d.drawing_move_gbn, @@ -1323,3 +1329,287 @@ export const documentStagesOnlyView = pgView("document_stages_only_view", { // 타입 추출 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(), + // 메타 정보 + 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), + } + } +) + +// 🎯 간소화된 스테이지 전용 뷰 +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 }), + + // 현재 스테이지 정보 (가장 우선순위가 높은 미완료 스테이지) + 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<Array<{ + id: number; + stageName: string; + stageStatus: string; + stageOrder: number; + planDate: string | null; + actualDate: string | null; + assigneeName: string | null; + priority: string; + description: string | null; + notes: string | null; + reminderDays: number | null; + }>>(), + + // 메타 정보 + 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, + + -- 현재 스테이지 정보 + 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 + -- 간소화된 JOIN (vendors는 vendor_id로 직접 조인) + LEFT JOIN projects p ON sd.project_id = p.id + LEFT JOIN vendors v ON sd.vendor_id = v.id + + -- 스테이지 관련 정보 JOIN + 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 |