(대표님) 변경사항 20250707 10시 43분 - unstaged 변경사항 추가

1 files changed, 497 insertions, 53 deletions

diff --git a/db/schema/evaluationTarget.ts b/db/schema/evaluationTarget.ts
index 66f3dc04..54f2ba16 100644
--- a/db/schema/evaluationTarget.ts
+++ b/db/schema/evaluationTarget.ts
@@ -3,7 +3,7 @@ import { eq , sql, relations} from "drizzle-orm";
 import { vendors } from "./vendors";
 import { users } from "./users";
 import { contracts } from "./contract";
-import { regEvalCriteriaDetails } from "./evaluationCriteria";
+import { regEvalCriteria, regEvalCriteriaDetails } from "./evaluationCriteria";
 
 // 평가 대상 메인 테이블
 export const evaluationTargets = pgTable("evaluation_targets", {
@@ -416,19 +416,7 @@ export const periodicEvaluations = pgTable("periodic_evaluations", {
     enum: ["S", "A", "B", "C", "D"] 
   }),
   
-  // 평가항목별 점수
-  processScore: decimal("process_score", { precision: 5, scale: 2 }).default("0"), // 공정
-  priceScore: decimal("price_score", { precision: 5, scale: 2 }).default("0"), // 가격
-  deliveryScore: decimal("delivery_score", { precision: 5, scale: 2 }).default("0"), // 납기
-  selfEvaluationScore: decimal("self_evaluation_score", { precision: 5, scale: 2 }).default("0"), // 자율평가
-  
-  // 합계 점수
-  totalScore: decimal("total_score", { precision: 5, scale: 2 }).default("0"),
-  
-  // 가점/감점
-  participationBonus: decimal("participation_bonus", { precision: 5, scale: 2 }).default("0"), // 참여도 가점
-  qualityDeduction: decimal("quality_deduction", { precision: 5, scale: 2 }).default("0"), // 품질 감점
-  
+ 
   // 평가 상태
   status: varchar("status", {
     length: 30,
@@ -465,27 +453,11 @@ export const reviewerEvaluations = pgTable("reviewer_evaluations", {
   evaluationTargetReviewerId: integer("evaluation_target_reviewer_id")
     .references(() => evaluationTargetReviewers.id, { onDelete: "cascade" })
     .notNull(),
-  
-  // 평가항목별 점수 (카테고리별 합산 점수)
-  processScore: decimal("process_score", { precision: 5, scale: 2 }),
-  priceScore: decimal("price_score", { precision: 5, scale: 2 }),
-  deliveryScore: decimal("delivery_score", { precision: 5, scale: 2 }),
-  selfEvaluationScore: decimal("self_evaluation_score", { precision: 5, scale: 2 }),
-  
-  // 가점/감점
-  participationBonus: decimal("participation_bonus", { precision: 5, scale: 2 }).default("0"),
-  qualityDeduction: decimal("quality_deduction", { precision: 5, scale: 2 }).default("0"),
-  
-  // 리뷰어 총점
-  totalScore: decimal("total_score", { precision: 5, scale: 2 }),
-  grade: varchar("grade", { 
-    length: 5,
-    enum: ["S", "A", "B", "C", "D"] 
-  }),
-  
+    
   // 평가 완료 여부
   isCompleted: boolean("is_completed").default(false),
   completedAt: timestamp("completed_at"),
+  submittedAt: timestamp("submitted_at"),
   
   // 리뷰어 의견
   reviewerComment: text("reviewer_comment"),
@@ -510,10 +482,10 @@ export const reviewerEvaluationDetails = pgTable("reviewer_evaluation_details",
   // 평가 기준 참조
   regEvalCriteriaDetailsId: integer("reg_eval_criteria_details_id")
     .references(() => regEvalCriteriaDetails.id)
-    .notNull(),
+    ,
   
   // 리뷰어가 매긴 점수
-  score: decimal("score", { precision: 5, scale: 2 }).notNull(),
+  score: decimal("score", { precision: 5, scale: 2 }),
   
   // 세부 의견
   comment: text("comment"),
@@ -584,11 +556,11 @@ export const reviewerEvaluationDetailsRelations = relations(reviewerEvaluationDe
 type PeriodicEvaluation = typeof periodicEvaluations.$inferSelect;
 type NewPeriodicEvaluation = typeof periodicEvaluations.$inferInsert;
 
-type ReviewerEvaluation = typeof reviewerEvaluations.$inferSelect;
+export type ReviewerEvaluation = typeof reviewerEvaluations.$inferSelect;
 type NewReviewerEvaluation = typeof reviewerEvaluations.$inferInsert;
 
-type ReviewerEvaluationDetail = typeof reviewerEvaluationDetails.$inferSelect;
-type NewReviewerEvaluationDetail = typeof reviewerEvaluationDetails.$inferInsert;
+export type ReviewerEvaluationDetail = typeof reviewerEvaluationDetails.$inferSelect;
+export type NewReviewerEvaluationDetail = typeof reviewerEvaluationDetails.$inferInsert;
 
 // 관계 포함 타입들
 type PeriodicEvaluationWithRelations = PeriodicEvaluation & {
@@ -634,26 +606,212 @@ export const periodicEvaluationsView = pgView('periodic_evaluations_view').as((q
       submissionDate: periodicEvaluations.submissionDate,
       submissionDeadline: periodicEvaluations.submissionDeadline,
       
-      // 평가 점수 (최종 확정)
+      // 평가 점수 (최종 확정 - 이건 수동으로 설정하는 값이므로 유지)
       finalScore: periodicEvaluations.finalScore,
       finalGrade: periodicEvaluations.finalGrade,
       
-      // 평가 점수 
-      evaluationScore: periodicEvaluations.evaluationScore,
-      evaluationGrade: periodicEvaluations.evaluationGrade,
+      // ═══════════════════════════════════════════════════════════════
+      // 동적으로 계산되는 평가 점수들
+      // ═══════════════════════════════════════════════════════════════
+      
+      // 공정 점수 (processScore)
+      processScore: sql<number>`(
+        SELECT COALESCE(AVG(CAST(red.score AS DECIMAL(5,2))), 0)
+        FROM ${reviewerEvaluationDetails} red
+        JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id
+        JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id
+        JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id
+        WHERE re.periodic_evaluation_id = ${periodicEvaluations.id}
+        AND rec.category2 = 'processScore'
+        AND re.is_completed = true
+      )`.as('process_score'),
+      
+      // 가격 점수 (priceScore)
+      priceScore: sql<number>`(
+        SELECT COALESCE(AVG(CAST(red.score AS DECIMAL(5,2))), 0)
+        FROM ${reviewerEvaluationDetails} red
+        JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id
+        JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id
+        JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id
+        WHERE re.periodic_evaluation_id = ${periodicEvaluations.id}
+        AND rec.category2 = 'priceScore'
+        AND re.is_completed = true
+      )`.as('price_score'),
+      
+      // 납기 점수 (deliveryScore)
+      deliveryScore: sql<number>`(
+        SELECT COALESCE(AVG(CAST(red.score AS DECIMAL(5,2))), 0)
+        FROM ${reviewerEvaluationDetails} red
+        JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id
+        JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id
+        JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id
+        WHERE re.periodic_evaluation_id = ${periodicEvaluations.id}
+        AND rec.category2 = 'deliveryScore'
+        AND re.is_completed = true
+      )`.as('delivery_score'),
+      
+      // 자율평가 점수 (selfEvaluationScore)
+      selfEvaluationScore: sql<number>`(
+        SELECT COALESCE(AVG(CAST(red.score AS DECIMAL(5,2))), 0)
+        FROM ${reviewerEvaluationDetails} red
+        JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id
+        JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id
+        JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id
+        WHERE re.periodic_evaluation_id = ${periodicEvaluations.id}
+        AND rec.category2 = 'selfEvaluationScore'
+        AND re.is_completed = true
+      )`.as('self_evaluation_score'),
+      
+      // 참여도 가점 (bonus)
+      participationBonus: sql<number>`(
+        SELECT COALESCE(AVG(CAST(red.score AS DECIMAL(5,2))), 0)
+        FROM ${reviewerEvaluationDetails} red
+        JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id
+        JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id
+        JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id
+        WHERE re.periodic_evaluation_id = ${periodicEvaluations.id}
+        AND rec.category2 = 'bonus'
+        AND re.is_completed = true
+      )`.as('participation_bonus'),
+      
+      // 품질 감점 (penalty)
+      qualityDeduction: sql<number>`(
+        SELECT COALESCE(AVG(CAST(red.score AS DECIMAL(5,2))), 0)
+        FROM ${reviewerEvaluationDetails} red
+        JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id
+        JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id
+        JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id
+        WHERE re.periodic_evaluation_id = ${periodicEvaluations.id}
+        AND rec.category2 = 'penalty'
+        AND re.is_completed = true
+      )`.as('quality_deduction'),
       
-      // 평가항목별 점수
-      processScore: periodicEvaluations.processScore,
-      priceScore: periodicEvaluations.priceScore,
-      deliveryScore: periodicEvaluations.deliveryScore,
-      selfEvaluationScore: periodicEvaluations.selfEvaluationScore,
+      // 합계 점수 (4개 기본 점수의 합)
+      totalScore: sql<number>`(
+        SELECT COALESCE(
+          (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'processScore' AND re.is_completed = true), 0
+          ) +
+          COALESCE(
+            (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'priceScore' AND re.is_completed = true), 0
+          ) +
+          COALESCE(
+            (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'deliveryScore' AND re.is_completed = true), 0
+          ) +
+          COALESCE(
+            (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'selfEvaluationScore' AND re.is_completed = true), 0
+          )
+      )`.as('total_score'),
       
-      // 합계 점수
-      totalScore: periodicEvaluations.totalScore,
+      // 최종 평가 점수 (합계 + 가점 - 감점)
+      evaluationScore: sql<number>`(
+        SELECT (
+          COALESCE(
+            (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'processScore' AND re.is_completed = true), 0
+          ) +
+          COALESCE(
+            (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'priceScore' AND re.is_completed = true), 0
+          ) +
+          COALESCE(
+            (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'deliveryScore' AND re.is_completed = true), 0
+          ) +
+          COALESCE(
+            (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'selfEvaluationScore' AND re.is_completed = true), 0
+          ) +
+          COALESCE(
+            (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'bonus' AND re.is_completed = true), 0
+          ) -
+          COALESCE(
+            (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'penalty' AND re.is_completed = true), 0
+          )
+        )
+      )`.as('evaluation_score'),
       
-      // 가점/감점
-      participationBonus: periodicEvaluations.participationBonus,
-      qualityDeduction: periodicEvaluations.qualityDeduction,
+      // 평가 등급 (evaluationScore 기반으로 계산)
+      evaluationGrade: sql<string>`(
+        CASE 
+          WHEN (
+            COALESCE(
+              (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'processScore' AND re.is_completed = true), 0
+            ) +
+            COALESCE(
+              (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'priceScore' AND re.is_completed = true), 0
+            ) +
+            COALESCE(
+              (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'deliveryScore' AND re.is_completed = true), 0
+            ) +
+            COALESCE(
+              (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'selfEvaluationScore' AND re.is_completed = true), 0
+            ) +
+            COALESCE(
+              (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'bonus' AND re.is_completed = true), 0
+            ) -
+            COALESCE(
+              (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'penalty' AND re.is_completed = true), 0
+            )
+          ) >= 90 THEN 'S'
+          WHEN  (
+            COALESCE(
+              (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'processScore' AND re.is_completed = true), 0
+            ) +
+            COALESCE(
+              (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'priceScore' AND re.is_completed = true), 0
+            ) +
+            COALESCE(
+              (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'deliveryScore' AND re.is_completed = true), 0
+            ) +
+            COALESCE(
+              (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'selfEvaluationScore' AND re.is_completed = true), 0
+            ) +
+            COALESCE(
+              (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'bonus' AND re.is_completed = true), 0
+            ) -
+            COALESCE(
+              (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'penalty' AND re.is_completed = true), 0
+            )
+          ) >= 80 THEN 'A'
+          WHEN  (
+            COALESCE(
+              (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'processScore' AND re.is_completed = true), 0
+            ) +
+            COALESCE(
+              (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'priceScore' AND re.is_completed = true), 0
+            ) +
+            COALESCE(
+              (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'deliveryScore' AND re.is_completed = true), 0
+            ) +
+            COALESCE(
+              (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'selfEvaluationScore' AND re.is_completed = true), 0
+            ) +
+            COALESCE(
+              (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'bonus' AND re.is_completed = true), 0
+            ) -
+            COALESCE(
+              (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'penalty' AND re.is_completed = true), 0
+            )
+          ) >= 70 THEN 'B'
+          WHEN  (
+            COALESCE(
+              (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'processScore' AND re.is_completed = true), 0
+            ) +
+            COALESCE(
+              (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'priceScore' AND re.is_completed = true), 0
+            ) +
+            COALESCE(
+              (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'deliveryScore' AND re.is_completed = true), 0
+            ) +
+            COALESCE(
+              (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'selfEvaluationScore' AND re.is_completed = true), 0
+            ) +
+            COALESCE(
+              (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'bonus' AND re.is_completed = true), 0
+            ) -
+            COALESCE(
+              (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM ${reviewerEvaluationDetails} red JOIN ${regEvalCriteriaDetails} recd ON red.reg_eval_criteria_details_id = recd.id JOIN ${regEvalCriteria} rec ON recd.criteria_id = rec.id JOIN ${reviewerEvaluations} re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = ${periodicEvaluations.id} AND rec.category2 = 'penalty' AND re.is_completed = true), 0
+            )
+          ) >= 60 THEN 'C'
+          ELSE 'D'
+        END
+      )`.as('evaluation_grade'),
       
       // 평가 상태
       status: periodicEvaluations.status,
@@ -673,11 +831,110 @@ export const periodicEvaluationsView = pgView('periodic_evaluations_view').as((q
       // ═══════════════════════════════════════════════════════════════
       // 평가 대상 추가 정보 (evaluationTargets 조인)
       // ═══════════════════════════════════════════════════════════════
-      evaluationTargetStatus: evaluationTargets.status,
       evaluationTargetAdminComment: evaluationTargets.adminComment,
       evaluationTargetConsolidatedComment: evaluationTargets.consolidatedComment,
       evaluationTargetConsensusStatus: evaluationTargets.consensusStatus,
       evaluationTargetConfirmedAt: evaluationTargets.confirmedAt,
+
+
+      // ═══════════════════════════════════════════════════════════════
+      // 부서별 리뷰어 상태 (서브쿼리로 계산)
+      // ═══════════════════════════════════════════════════════════════
+      
+      // 발주 평가 담당 상태
+      orderEvalStatus: 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 = 'ORDER_EVAL'
+        LIMIT 1
+      )`.as('order_eval_status'),
+      
+      // 조달 평가 담당 상태
+      procurementEvalStatus: 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 = 'PROCUREMENT_EVAL'
+        LIMIT 1
+      )`.as('procurement_eval_status'),
+      
+      // 품질 평가 담당 상태
+      qualityEvalStatus: 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 = 'QUALITY_EVAL'
+        LIMIT 1
+      )`.as('quality_eval_status'),
+      
+      // 설계 평가 담당 상태
+      designEvalStatus: 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 = 'DESIGN_EVAL'
+        LIMIT 1
+      )`.as('design_eval_status'),
+      
+      // CS 평가 담당 상태
+      csEvalStatus: 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 = 'CS_EVAL'
+        LIMIT 1
+      )`.as('cs_eval_status'),
       
       // ═══════════════════════════════════════════════════════════════
       // 리뷰어 통계 (서브쿼리로 계산)
@@ -714,9 +971,196 @@ export const periodicEvaluationsView = pgView('periodic_evaluations_view').as((q
     .orderBy(periodicEvaluations.createdAt)
 );
 
-
 // ================================================================
 // TYPES
 // ================================================================
 
 export type PeriodicEvaluationView = typeof periodicEvaluationsView.$inferSelect;
+
+// reviewerEvaluations를 기준으로 하는 종합 뷰 테이블
+export const reviewerEvaluationsView = pgView('reviewer_evaluations_view').as((qb) =>
+  qb
+    .select({
+      // ═══════════════════════════════════════════════════════════════
+      // 리뷰어 평가 기본 정보 (reviewerEvaluations)
+      // ═══════════════════════════════════════════════════════════════
+      reviewerEvaluationId: reviewerEvaluations.id,
+      periodicEvaluationId: reviewerEvaluations.periodicEvaluationId,
+      evaluationTargetReviewerId: reviewerEvaluations.evaluationTargetReviewerId,
+      
+         // 평가 완료 여부
+      isCompleted: reviewerEvaluations.isCompleted,
+      completedAt: reviewerEvaluations.completedAt,
+      
+      // 리뷰어 의견
+      reviewerComment: reviewerEvaluations.reviewerComment,
+      
+      // 생성/수정일
+      reviewerEvaluationCreatedAt: reviewerEvaluations.createdAt,
+      reviewerEvaluationUpdatedAt: reviewerEvaluations.updatedAt,
+      
+      // ═══════════════════════════════════════════════════════════════
+      // 정기평가 정보 (periodicEvaluations)
+      // ═══════════════════════════════════════════════════════════════
+      evaluationPeriod: periodicEvaluations.evaluationPeriod,
+
+      submittedAt:reviewerEvaluations.submittedAt,
+      
+      // 업체 제출 관련
+      documentsSubmitted: periodicEvaluations.documentsSubmitted,
+      submissionDate: periodicEvaluations.submissionDate,
+      submissionDeadline: periodicEvaluations.submissionDeadline,
+      
+      // 정기평가 최종 점수
+      periodicFinalScore: periodicEvaluations.finalScore,
+      periodicFinalGrade: periodicEvaluations.finalGrade,
+      periodicEvaluationScore: periodicEvaluations.evaluationScore,
+      periodicEvaluationGrade: periodicEvaluations.evaluationGrade,
+      
+      // 정기평가 상태
+      periodicStatus: periodicEvaluations.status,
+      reviewCompletedAt: periodicEvaluations.reviewCompletedAt,
+      finalizedAt: periodicEvaluations.finalizedAt,
+      finalizedBy: periodicEvaluations.finalizedBy,
+      
+      // 정기평가 비고
+      evaluationNote: periodicEvaluations.evaluationNote,
+      
+      // ═══════════════════════════════════════════════════════════════
+      // 평가 대상 기본 정보 (evaluationTargets)
+      // ═══════════════════════════════════════════════════════════════
+      evaluationYear: evaluationTargets.evaluationYear,
+      division: evaluationTargets.division,
+      
+      // 벤더 정보
+      vendorId: evaluationTargets.vendorId,
+      vendorCode: evaluationTargets.vendorCode,
+      vendorName: evaluationTargets.vendorName,
+      
+      // 분류 정보
+      domesticForeign: evaluationTargets.domesticForeign,
+      materialType: evaluationTargets.materialType,
+      // 평가 대상 의견
+      adminComment: evaluationTargets.adminComment,
+      consolidatedComment: evaluationTargets.consolidatedComment,
+      
+      // 평가 대상 확정 정보
+      evaluationTargetConfirmedAt: evaluationTargets.confirmedAt,
+      evaluationTargetConfirmedBy: evaluationTargets.confirmedBy,
+      
+      // LD 클레임 정보
+      ldClaimCount: evaluationTargets.ldClaimCount,
+      ldClaimAmount: evaluationTargets.ldClaimAmount,
+      ldClaimCurrency: evaluationTargets.ldClaimCurrency,
+      
+      // ═══════════════════════════════════════════════════════════════
+      // 리뷰어 정보 (evaluationTargetReviewers + users)
+      // ═══════════════════════════════════════════════════════════════
+      departmentCode: evaluationTargetReviewers.departmentCode,
+      departmentNameFrom: evaluationTargetReviewers.departmentNameFrom,
+      
+      // 리뷰어 담당자 정보
+      reviewerUserId: evaluationTargetReviewers.reviewerUserId,
+      reviewerName: sql<string | null>`reviewer_user.name`.as('reviewer_name'),
+      reviewerEmail: sql<string | null>`reviewer_user.email`.as('reviewer_email'),
+      
+      // 리뷰어 지정 정보
+      assignedAt: evaluationTargetReviewers.assignedAt,
+      assignedBy: evaluationTargetReviewers.assignedBy,
+      assignedByUserName: sql<string | null>`assigned_by_user.name`.as('assigned_by_user_name'),
+      
+      // ═══════════════════════════════════════════════════════════════
+      // 최종 확정자 정보
+      // ═══════════════════════════════════════════════════════════════
+      finalizedByUserName: sql<string | null>`finalized_by_user.name`.as('finalized_by_user_name'),
+      finalizedByUserEmail: sql<string | null>`finalized_by_user.email`.as('finalized_by_user_email'),
+      
+      // ═══════════════════════════════════════════════════════════════
+      // 추가 계산 필드
+      // ═══════════════════════════════════════════════════════════════
+      
+      // 평가 진행률 (개인별)
+      evaluationProgress: sql<string>`
+        CASE 
+          WHEN ${reviewerEvaluations.isCompleted} = true THEN 'COMPLETED'
+          ELSE 'NOT_STARTED'
+        END
+      `.as('evaluation_progress'),
+      
+
+    })
+    .from(reviewerEvaluations)
+    
+    // 정기평가 정보 조인
+    .leftJoin(
+      periodicEvaluations, 
+      eq(reviewerEvaluations.periodicEvaluationId, periodicEvaluations.id)
+    )
+    
+    // 평가 대상 정보 조인
+    .leftJoin(
+      evaluationTargets,
+      eq(periodicEvaluations.evaluationTargetId, evaluationTargets.id)
+    )
+    
+    // 리뷰어 지정 정보 조인
+    .leftJoin(
+      evaluationTargetReviewers,
+      eq(reviewerEvaluations.evaluationTargetReviewerId, evaluationTargetReviewers.id)
+    )
+    
+    // 리뷰어 사용자 정보 조인
+    .leftJoin(
+      sql`users reviewer_user`,
+      sql`${evaluationTargetReviewers.reviewerUserId} = reviewer_user.id`
+    )
+    
+    // 지정자 사용자 정보 조인
+    .leftJoin(
+      sql`users assigned_by_user`,
+      sql`${evaluationTargetReviewers.assignedBy} = assigned_by_user.id`
+    )
+    
+    // 최종 확정자 사용자 정보 조인
+    .leftJoin(
+      sql`users finalized_by_user`,
+      sql`${periodicEvaluations.finalizedBy} = finalized_by_user.id`
+    )
+    
+    // 정렬: 최신순, 미완료 우선
+    .orderBy(
+      sql`${reviewerEvaluations.isCompleted} ASC`, // false(미완료)가 먼저
+      sql`${reviewerEvaluations.updatedAt} DESC`
+    )
+);
+
+// ================================================================
+// TYPES
+// ================================================================
+
+export type ReviewerEvaluationView = typeof reviewerEvaluationsView.$inferSelect;
+
+// 리뷰어 평가 뷰 관련 추가 타입들
+export interface ReviewerEvaluationSummary {
+  reviewerId: number;
+  reviewerName: string;
+  departmentCode: string;
+  totalEvaluations: number;
+  completedEvaluations: number;
+  pendingEvaluations: number;
+  overdueEvaluations: number;
+  averageScore: number | null;
+  averageGrade: string | null;
+}
+
+export interface DepartmentEvaluationStatus {
+  departmentCode: string;
+  departmentName: string;
+  totalReviewers: number;
+  completedReviewers: number;
+  completionRate: number;
+  averageScore: number | null;
+}
+
+export type EvaluationProgress = 'NOT_STARTED' | 'IN_PROGRESS' | 'COMPLETED';
+export type PeriodicEvaluationStatus = 'PENDING_SUBMISSION' | 'SUBMITTED' | 'IN_REVIEW' | 'REVIEW_COMPLETED' | 'FINALIZED';