(대표님) 입찰, 법무검토, EDP 변경사항 대응, dolce 개선, form-data 개선, 정규업체 등록관리 추가

(최겸) pq 미사용 컴포넌트 및 페이지 제거, 파일 라우트에 pq 적용

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