(대표님) 정기평가 피드백 반영, 설계 피드백 반영, (최겸) 기술영업 피드백 반영

3 files changed, 431 insertions, 1 deletions

diff --git a/db/schema/evaluationTarget.ts b/db/schema/evaluationTarget.ts
index 343efc4e..c8011e96 100644
--- a/db/schema/evaluationTarget.ts
+++ b/db/schema/evaluationTarget.ts
@@ -844,6 +844,25 @@ export const periodicEvaluationsView = pgView('periodic_evaluations_view').as((q
         LIMIT 1
       )`.as('cs_eval_status'),
       
+            // admin 평가 담당 상태
+            adminEvalStatus: sql<string>`(
+              SELECT CASE
+                WHEN re.id IS NULL THEN 'NOT_ASSIGNED'
+                WHEN re.is_completed = true THEN 'COMPLETED'
+                WHEN (
+                  SELECT COALESCE(SUM(CAST(red.score AS DECIMAL(5,2))), 0)
+                  FROM ${reviewerEvaluationDetails} red
+                  WHERE red.reviewer_evaluation_id = re.id
+                ) > 0 THEN 'IN_PROGRESS'
+                ELSE 'NOT_STARTED'
+              END
+              FROM ${reviewerEvaluations} re
+              JOIN ${evaluationTargetReviewers} etr ON re.evaluation_target_reviewer_id = etr.id
+              WHERE re.periodic_evaluation_id = ${periodicEvaluations.id}
+              AND etr.department_code = 'admin'
+              LIMIT 1
+            )`.as('admin_eval_status'),
+
       // ═══════════════════════════════════════════════════════════════
       // 리뷰어 통계 (서브쿼리로 계산)
       // ═══════════════════════════════════════════════════════════════
@@ -1072,3 +1091,303 @@ export interface DepartmentEvaluationStatus {
 
 export type EvaluationProgress = 'NOT_STARTED' | 'IN_PROGRESS' | 'COMPLETED';
 export type PeriodicEvaluationStatus = 'PENDING_SUBMISSION' | 'SUBMITTED' | 'IN_REVIEW' | 'REVIEW_COMPLETED' | 'FINALIZED';
+
+
+/**
+ * 배열에서 첫 번째 값을 뽑아내는 헬퍼. 정렬 기준을 주면 해당 기준으로 정렬 후 첫 번째 값을 가져온다.
+ */
+const first = <T>(expr: SQL<T>, order?: SQL) =>
+  order
+    ? sql<T>`(ARRAY_AGG(${expr} ORDER BY ${order} NULLS LAST))[1]`
+    : sql<T>`(ARRAY_AGG(${expr}))[1]`;
+
+/**
+ * BOOL_AND / BOOL_OR 사용이 애매한 경우를 대비한 캐스팅 헬퍼 (선택적으로 사용)
+ *  ex) boolMax(expr) → (MAX((expr)::int))::bool
+ */
+const boolMax = (expr: SQL<boolean>) => sql<boolean>`(MAX((${expr})::int))::bool`;
+
+export const periodicEvaluationsAggregatedView = pgView(
+  "periodic_evaluations_aggregated_view"
+).as((qb) =>
+  qb
+    .select({
+      // ═══════════════════════════════════════════════════════════════
+      // 집계된 식별자 및 기본 정보
+      // ═══════════════════════════════════════════════════════════════
+      id: sql<string>`CONCAT(${periodicEvaluationsView.evaluationYear}, '_', ${periodicEvaluationsView.vendorId})`.as(
+        "id"
+      ),
+
+      // 그룹핑 기준 필드들
+      evaluationYear: periodicEvaluationsView.evaluationYear,
+      vendorId: periodicEvaluationsView.vendorId,
+      vendorCode: periodicEvaluationsView.vendorCode,
+      vendorName: periodicEvaluationsView.vendorName,
+      domesticForeign: periodicEvaluationsView.domesticForeign,
+      materialType: periodicEvaluationsView.materialType,
+
+      // ═══════════════════════════════════════════════════════════════
+      // 평가 점수들의 평균 계산
+      // ═══════════════════════════════════════════════════════════════
+      processScore: sql<number>`ROUND(AVG(NULLIF(${periodicEvaluationsView.processScore}, 0)), 1)`.as(
+        "process_score"
+      ),
+      priceScore: sql<number>`ROUND(AVG(NULLIF(${periodicEvaluationsView.priceScore}, 0)), 1)`.as(
+        "price_score"
+      ),
+      deliveryScore: sql<number>`ROUND(AVG(NULLIF(${periodicEvaluationsView.deliveryScore}, 0)), 1)`.as(
+        "delivery_score"
+      ),
+      selfEvaluationScore: sql<number>`ROUND(AVG(NULLIF(${periodicEvaluationsView.selfEvaluationScore}, 0)), 1)`.as(
+        "self_evaluation_score"
+      ),
+      participationBonus: sql<number>`ROUND(AVG(NULLIF(${periodicEvaluationsView.participationBonus}, 0)), 1)`.as(
+        "participation_bonus"
+      ),
+      qualityDeduction: sql<number>`ROUND(AVG(NULLIF(${periodicEvaluationsView.qualityDeduction}, 0)), 1)`.as(
+        "quality_deduction"
+      ),
+
+      // 최종 점수의 평균
+      finalScore: sql<number>`ROUND(AVG(NULLIF(${periodicEvaluationsView.finalScore}, 0)), 1)`.as(
+        "final_score"
+      ),
+
+      // ═══════════════════════════════════════════════════════════════
+      // 평가 등급 (평균 점수를 기반으로 재계산)
+      // ═══════════════════════════════════════════════════════════════
+      evaluationGrade: sql<string>`
+        CASE 
+          WHEN (
+            AVG(NULLIF(${periodicEvaluationsView.processScore}, 0)) + 
+            AVG(NULLIF(${periodicEvaluationsView.priceScore}, 0)) + 
+            AVG(NULLIF(${periodicEvaluationsView.deliveryScore}, 0)) + 
+            AVG(NULLIF(${periodicEvaluationsView.selfEvaluationScore}, 0)) +
+            AVG(NULLIF(${periodicEvaluationsView.participationBonus}, 0)) - 
+            AVG(NULLIF(${periodicEvaluationsView.qualityDeduction}, 0))
+          ) >= 90 THEN 'S'
+          WHEN (
+            AVG(NULLIF(${periodicEvaluationsView.processScore}, 0)) + 
+            AVG(NULLIF(${periodicEvaluationsView.priceScore}, 0)) + 
+            AVG(NULLIF(${periodicEvaluationsView.deliveryScore}, 0)) + 
+            AVG(NULLIF(${periodicEvaluationsView.selfEvaluationScore}, 0)) +
+            AVG(NULLIF(${periodicEvaluationsView.participationBonus}, 0)) - 
+            AVG(NULLIF(${periodicEvaluationsView.qualityDeduction}, 0))
+          ) >= 80 THEN 'A'
+          WHEN (
+            AVG(NULLIF(${periodicEvaluationsView.processScore}, 0)) + 
+            AVG(NULLIF(${periodicEvaluationsView.priceScore}, 0)) + 
+            AVG(NULLIF(${periodicEvaluationsView.deliveryScore}, 0)) + 
+            AVG(NULLIF(${periodicEvaluationsView.selfEvaluationScore}, 0)) +
+            AVG(NULLIF(${periodicEvaluationsView.participationBonus}, 0)) - 
+            AVG(NULLIF(${periodicEvaluationsView.qualityDeduction}, 0))
+          ) >= 70 THEN 'B'
+          WHEN (
+            AVG(NULLIF(${periodicEvaluationsView.processScore}, 0)) + 
+            AVG(NULLIF(${periodicEvaluationsView.priceScore}, 0)) + 
+            AVG(NULLIF(${periodicEvaluationsView.deliveryScore}, 0)) + 
+            AVG(NULLIF(${periodicEvaluationsView.selfEvaluationScore}, 0)) +
+            AVG(NULLIF(${periodicEvaluationsView.participationBonus}, 0)) - 
+            AVG(NULLIF(${periodicEvaluationsView.qualityDeduction}, 0))
+          ) >= 60 THEN 'C'
+          ELSE 'D'
+        END
+      `.as("evaluation_grade"),
+
+      finalGrade: sql<string>`
+        CASE 
+          WHEN AVG(NULLIF(${periodicEvaluationsView.finalScore}, 0)) >= 90 THEN 'S'
+          WHEN AVG(NULLIF(${periodicEvaluationsView.finalScore}, 0)) >= 80 THEN 'A'
+          WHEN AVG(NULLIF(${periodicEvaluationsView.finalScore}, 0)) >= 70 THEN 'B'
+          WHEN AVG(NULLIF(${periodicEvaluationsView.finalScore}, 0)) >= 60 THEN 'C'
+          ELSE 'D'
+        END
+      `.as("final_grade"),
+
+      // ═══════════════════════════════════════════════════════════════
+      // 상태 정보 (우선순위 기반으로 결정)
+      // ═══════════════════════════════════════════════════════════════
+      status: sql<string>`
+        CASE 
+          WHEN COUNT(CASE WHEN ${periodicEvaluationsView.status} = 'FINALIZED' THEN 1 END) = COUNT(*) THEN 'FINALIZED'
+          WHEN COUNT(CASE WHEN ${periodicEvaluationsView.status} IN ('REVIEW_COMPLETED', 'FINALIZED') THEN 1 END) = COUNT(*) THEN 'REVIEW_COMPLETED'
+          WHEN COUNT(CASE WHEN ${periodicEvaluationsView.status} IN ('IN_REVIEW', 'REVIEW_COMPLETED', 'FINALIZED') THEN 1 END) > 0 THEN 'IN_REVIEW'
+          WHEN COUNT(CASE WHEN ${periodicEvaluationsView.status} IN ('SUBMITTED', 'IN_REVIEW', 'REVIEW_COMPLETED', 'FINALIZED') THEN 1 END) > 0 THEN 'SUBMITTED'
+          ELSE 'PENDING_SUBMISSION'
+        END
+      `.as("status"),
+
+      // ═══════════════════════════════════════════════════════════════
+      // 부서별 평가 상태 (최고 진행 상태로 설정)
+      // ═══════════════════════════════════════════════════════════════
+      orderEvalStatus: sql<string>`
+        CASE 
+          WHEN COUNT(CASE WHEN ${periodicEvaluationsView.orderEvalStatus} = 'COMPLETED' THEN 1 END) > 0 THEN 'COMPLETED'
+          WHEN COUNT(CASE WHEN ${periodicEvaluationsView.orderEvalStatus} = 'IN_PROGRESS' THEN 1 END) > 0 THEN 'IN_PROGRESS'
+          WHEN COUNT(CASE WHEN ${periodicEvaluationsView.orderEvalStatus} = 'NOT_STARTED' THEN 1 END) > 0 THEN 'NOT_STARTED'
+          ELSE 'NOT_ASSIGNED'
+        END
+      `.as("order_eval_status"),
+
+      procurementEvalStatus: sql<string>`
+        CASE 
+          WHEN COUNT(CASE WHEN ${periodicEvaluationsView.procurementEvalStatus} = 'COMPLETED' THEN 1 END) > 0 THEN 'COMPLETED'
+          WHEN COUNT(CASE WHEN ${periodicEvaluationsView.procurementEvalStatus} = 'IN_PROGRESS' THEN 1 END) > 0 THEN 'IN_PROGRESS'
+          WHEN COUNT(CASE WHEN ${periodicEvaluationsView.procurementEvalStatus} = 'NOT_STARTED' THEN 1 END) > 0 THEN 'NOT_STARTED'
+          ELSE 'NOT_ASSIGNED'
+        END
+      `.as("procurement_eval_status"),
+
+      qualityEvalStatus: sql<string>`
+        CASE 
+          WHEN COUNT(CASE WHEN ${periodicEvaluationsView.qualityEvalStatus} = 'COMPLETED' THEN 1 END) > 0 THEN 'COMPLETED'
+          WHEN COUNT(CASE WHEN ${periodicEvaluationsView.qualityEvalStatus} = 'IN_PROGRESS' THEN 1 END) > 0 THEN 'IN_PROGRESS'
+          WHEN COUNT(CASE WHEN ${periodicEvaluationsView.qualityEvalStatus} = 'NOT_STARTED' THEN 1 END) > 0 THEN 'NOT_STARTED'
+          ELSE 'NOT_ASSIGNED'
+        END
+      `.as("quality_eval_status"),
+
+      designEvalStatus: sql<string>`
+        CASE 
+          WHEN COUNT(CASE WHEN ${periodicEvaluationsView.designEvalStatus} = 'COMPLETED' THEN 1 END) > 0 THEN 'COMPLETED'
+          WHEN COUNT(CASE WHEN ${periodicEvaluationsView.designEvalStatus} = 'IN_PROGRESS' THEN 1 END) > 0 THEN 'IN_PROGRESS'
+          WHEN COUNT(CASE WHEN ${periodicEvaluationsView.designEvalStatus} = 'NOT_STARTED' THEN 1 END) > 0 THEN 'NOT_STARTED'
+          ELSE 'NOT_ASSIGNED'
+        END
+      `.as("design_eval_status"),
+
+      csEvalStatus: sql<string>`
+        CASE 
+          WHEN COUNT(CASE WHEN ${periodicEvaluationsView.csEvalStatus} = 'COMPLETED' THEN 1 END) > 0 THEN 'COMPLETED'
+          WHEN COUNT(CASE WHEN ${periodicEvaluationsView.csEvalStatus} = 'IN_PROGRESS' THEN 1 END) > 0 THEN 'IN_PROGRESS'
+          WHEN COUNT(CASE WHEN ${periodicEvaluationsView.csEvalStatus} = 'NOT_STARTED' THEN 1 END) > 0 THEN 'NOT_STARTED'
+          ELSE 'NOT_ASSIGNED'
+        END
+      `.as("cs_eval_status"),
+
+      adminEvalStatus: sql<string>`
+        CASE 
+          WHEN COUNT(CASE WHEN ${periodicEvaluationsView.adminEvalStatus} = 'COMPLETED' THEN 1 END) > 0 THEN 'COMPLETED'
+          WHEN COUNT(CASE WHEN ${periodicEvaluationsView.adminEvalStatus} = 'IN_PROGRESS' THEN 1 END) > 0 THEN 'IN_PROGRESS'
+          WHEN COUNT(CASE WHEN ${periodicEvaluationsView.adminEvalStatus} = 'NOT_STARTED' THEN 1 END) > 0 THEN 'NOT_STARTED'
+          ELSE 'NOT_ASSIGNED'
+        END
+      `.as("admin_eval_status"),
+
+      // ═══════════════════════════════════════════════════════════════
+      // 문서 제출 및 날짜 정보
+      // ═══════════════════════════════════════════════════════════════
+      documentsSubmitted: sql<boolean>`
+        BOOL_AND(${periodicEvaluationsView.documentsSubmitted})
+      `.as("documents_submitted"),
+
+      submissionDate: sql<Date>`MAX(${periodicEvaluationsView.submissionDate})`.as(
+        "submission_date"
+      ),
+      submissionDeadline: sql<Date>`MAX(${periodicEvaluationsView.submissionDeadline})`.as(
+        "submission_deadline"
+      ),
+      reviewCompletedAt: sql<Date>`MAX(${periodicEvaluationsView.reviewCompletedAt})`.as(
+        "review_completed_at"
+      ),
+      finalizedAt: sql<Date>`MAX(${periodicEvaluationsView.finalizedAt})`.as(
+        "finalized_at"
+      ),
+
+      // ═══════════════════════════════════════════════════════════════
+      // Division 정보 및 집계 메타데이터
+      // ═══════════════════════════════════════════════════════════════
+      division: sql<string>`
+        CASE 
+          WHEN COUNT(DISTINCT ${periodicEvaluationsView.division}) > 1 THEN 'BOTH'
+          ELSE MAX(${periodicEvaluationsView.division})
+        END
+      `.as("division"),
+
+      evaluationCount: sql<number>`COUNT(*)::int`.as("evaluation_count"),
+      divisions: sql<string>`STRING_AGG(DISTINCT ${periodicEvaluationsView.division}, ',')`.as(
+        "divisions"
+      ),
+
+      // ═══════════════════════════════════════════════════════════════
+      // 리뷰어 통계 (합계)
+      // ═══════════════════════════════════════════════════════════════
+      totalReviewers: sql<number>`SUM(${periodicEvaluationsView.totalReviewers})::int`.as(
+        "total_reviewers"
+      ),
+      completedReviewers: sql<number>`SUM(${periodicEvaluationsView.completedReviewers})::int`.as(
+        "completed_reviewers"
+      ),
+      pendingReviewers: sql<number>`SUM(${periodicEvaluationsView.pendingReviewers})::int`.as(
+        "pending_reviewers"
+      ),
+
+      // ═══════════════════════════════════════════════════════════════
+      // 기타 정보
+      // ═══════════════════════════════════════════════════════════════
+      evaluationPeriod: sql<string>`MAX(${periodicEvaluationsView.evaluationPeriod})`.as(
+        "evaluation_period"
+      ),
+      evaluationNote: sql<string>`STRING_AGG(${periodicEvaluationsView.evaluationNote}, ' | ')`.as(
+        "evaluation_note"
+      ),
+
+      // 최종 확정자 정보 (가장 최근 확정자)
+      finalizedBy: first<string>(
+        sql`${periodicEvaluationsView.finalizedBy}`,
+        sql`${periodicEvaluationsView.finalizedAt} DESC`
+      ).as("finalized_by"),
+      finalizedByUserName: first<string>(
+        sql`${periodicEvaluationsView.finalizedByUserName}`,
+        sql`${periodicEvaluationsView.finalizedAt} DESC`
+      ).as("finalized_by_user_name"),
+      finalizedByUserEmail: first<string>(
+        sql`${periodicEvaluationsView.finalizedByUserEmail}`,
+        sql`${periodicEvaluationsView.finalizedAt} DESC`
+      ).as("finalized_by_user_email"),
+
+      // 생성/수정 일시
+      createdAt: sql<Date>`MIN(${periodicEvaluationsView.createdAt})`.as(
+        "created_at"
+      ),
+      updatedAt: sql<Date>`MAX(${periodicEvaluationsView.updatedAt})`.as(
+        "updated_at"
+      ),
+
+      // 추가 평가 대상 정보들 (첫 번째 레코드 사용)
+      evaluationTargetId: first<string>(
+        sql`${periodicEvaluationsView.evaluationTargetId}`
+      ).as("evaluation_target_id"),
+
+      evaluationTargetAdminComment: sql<string>`
+        STRING_AGG(DISTINCT ${periodicEvaluationsView.evaluationTargetAdminComment}, ' | ')
+      `.as("evaluation_target_admin_comment"),
+
+      evaluationTargetConsolidatedComment: sql<string>`
+        STRING_AGG(DISTINCT ${periodicEvaluationsView.evaluationTargetConsolidatedComment}, ' | ')
+      `.as("evaluation_target_consolidated_comment"),
+
+      // Boolean / Enum 필드라면 first()로 첫 번째 값만 가져오도록 변경
+      evaluationTargetConsensusStatus: first<string>(
+        sql`${periodicEvaluationsView.evaluationTargetConsensusStatus}`,
+        sql`${periodicEvaluationsView.updatedAt} DESC`
+      ).as("evaluation_target_consensus_status"),
+
+      evaluationTargetConfirmedAt: sql<Date>`
+        MAX(${periodicEvaluationsView.evaluationTargetConfirmedAt})
+      `.as("evaluation_target_confirmed_at"),
+    })
+    .from(periodicEvaluationsView)
+    .groupBy(
+      periodicEvaluationsView.evaluationYear,
+      periodicEvaluationsView.vendorId,
+      periodicEvaluationsView.vendorCode,
+      periodicEvaluationsView.vendorName,
+      periodicEvaluationsView.domesticForeign,
+      periodicEvaluationsView.materialType
+    )
+);
+
+// 타입 정의
+export type PeriodicEvaluationAggregatedView = typeof periodicEvaluationsAggregatedView.$inferSelect;
\ No newline at end of file
diff --git a/db/schema/gtc.ts b/db/schema/gtc.ts
new file mode 100644
index 00000000..a4052d61
--- /dev/null
+++ b/db/schema/gtc.ts
@@ -0,0 +1,110 @@
+import { 
+    pgTable, 
+    serial, 
+    varchar, 
+    text, 
+    integer, 
+    timestamp, 
+    pgEnum,
+    boolean,
+    index,
+    uniqueIndex
+  } from "drizzle-orm/pg-core"
+  import { relations } from "drizzle-orm"
+import { projects } from "./projects"
+import { users } from "./users"
+  
+  // GTC 구분 enum
+  export const gtcTypeEnum = pgEnum("gtc_type", ["standard", "project"])
+  
+  // GTC 문서 테이블
+  export const gtcDocuments = pgTable("gtc_documents", {
+    id: serial("id").primaryKey(),
+    
+    // 구분 (표준/프로젝트)
+    type: gtcTypeEnum("type").notNull(),
+    
+    // 프로젝트 참조 (프로젝트 타입인 경우만)
+    projectId: integer("project_id").references(() => projects.id, { 
+      onDelete: "cascade" 
+    }),
+    
+    // 리비전 번호
+    revision: integer("revision").notNull().default(0),
+    
+    // 파일 정보
+    fileName: varchar("file_name", { length: 255 }),
+    filePath: varchar("file_path", { length: 500 }),
+    fileSize: integer("file_size"), // bytes
+    
+    // 최초 등록 정보
+    createdAt: timestamp("created_at", { withTimezone: true })
+      .defaultNow()
+      .notNull(),
+    createdById: integer("created_by_id")
+      .references(() => users.id, { onDelete: "set null" })
+      .notNull(),
+    
+    // 최종 수정 정보  
+    updatedAt: timestamp("updated_at", { withTimezone: true })
+      .defaultNow()
+      .notNull(),
+    updatedById: integer("updated_by_id")
+      .references(() => users.id, { onDelete: "set null" }),
+    
+    // 편집 사유
+    editReason: text("edit_reason"),
+    
+    // 활성 상태
+    isActive: boolean("is_active").default(true).notNull(),
+    
+  }, (table) => {
+    return {
+      // 프로젝트별 리비전 유니크 (표준의 경우 projectId가 null)
+      projectRevisionIdx: uniqueIndex("gtc_project_revision_idx")
+        .on(table.projectId, table.revision, table.type),
+      
+      // 조회 성능을 위한 인덱스들
+      typeIdx: index("gtc_type_idx").on(table.type),
+      projectIdx: index("gtc_project_idx").on(table.projectId),
+      createdAtIdx: index("gtc_created_at_idx").on(table.createdAt),
+      updatedAtIdx: index("gtc_updated_at_idx").on(table.updatedAt),
+    }
+  })
+  
+  // 관계 정의 (필요한 경우)
+  export const gtcDocumentsRelations = relations(gtcDocuments, ({ one }) => ({
+    project: one(projects, {
+      fields: [gtcDocuments.projectId],
+      references: [projects.id],
+    }),
+    createdBy: one(users, {
+      fields: [gtcDocuments.createdById],
+      references: [users.id],
+    }),
+    updatedBy: one(users, {
+      fields: [gtcDocuments.updatedById],
+      references: [users.id],
+    }),
+  }))
+  
+  // 타입 정의
+  export type GtcDocument = typeof gtcDocuments.$inferSelect
+  export type NewGtcDocument = typeof gtcDocuments.$inferInsert
+  
+  // 조인된 결과를 위한 타입
+  export type GtcDocumentWithRelations = GtcDocument & {
+    project?: {
+      id: number
+      code: string
+      name: string
+    }
+    createdBy?: {
+      id: number
+      name: string
+    }
+    updatedBy?: {
+      id: number
+      name: string
+    }
+  }
\ No newline at end of file
diff --git a/db/schema/vendorData.ts b/db/schema/vendorData.ts
index 5e1e65cf..a512a8d7 100644
--- a/db/schema/vendorData.ts
+++ b/db/schema/vendorData.ts
@@ -248,7 +248,8 @@ export const tagTypeClassFormMappings = pgTable("tag_type_class_form_mappings",
             table.projectId,
             table.tagTypeLabel,
             table.classLabel,
-            table.formCode
+            table.formCode,
+            table.remark
         )
     };
 });