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

6 files changed, 616 insertions, 53 deletions

diff --git a/db/schema/evaluation.ts b/db/schema/evaluation.ts
index fd27ec9b..00b222a7 100644
--- a/db/schema/evaluation.ts
+++ b/db/schema/evaluation.ts
@@ -150,6 +150,7 @@ export const evaluationSubmissions = pgTable('evaluation_submissions', {
   .references(() => periodicEvaluations.id, { onDelete: 'cascade' })
   .notNull(),
   companyId: integer('company_id').references(() => vendors.id, { onDelete: 'cascade' }).notNull(),
+  vendorId: integer('vendor_id').references(() => vendors.id, { onDelete: 'cascade' }).notNull(),
   evaluationYear: integer('evaluation_year').notNull(),
   evaluationRound: varchar('evaluation_round', { length: 50 }),
   submissionStatus: varchar('submission_status', { length: 50 }).default('draft').notNull(),
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';
diff --git a/db/schema/history.ts b/db/schema/history.ts
new file mode 100644
index 00000000..13b00196
--- /dev/null
+++ b/db/schema/history.ts
@@ -0,0 +1,113 @@
+import {
+    pgTable,
+    uuid,
+    varchar,
+    timestamp,
+    text,
+    boolean,
+    integer,
+    inet
+} from 'drizzle-orm/pg-core';
+import { relations } from 'drizzle-orm';
+import { users } from './users';
+
+// 로그인 세션 테이블 (로그인/로그아웃 이력)
+export const loginSessions = pgTable('login_sessions', {
+    id: uuid('id').primaryKey().defaultRandom(),
+    userId: integer('user_id').references(() => users.id, { onDelete: 'cascade' }).notNull(),
+    loginAt: timestamp('login_at').defaultNow().notNull(),
+    logoutAt: timestamp('logout_at'), // 로그아웃 시간 (nullable)
+    ipAddress: inet('ip_address').notNull(),
+    userAgent: text('user_agent'),
+    sessionToken: varchar('session_token', { length: 255 }).unique(), // NextAuth JWT token ID
+    nextAuthSessionId: varchar('nextauth_session_id', { length: 255 }).unique(), // NextAuth 세션 ID
+    authMethod: varchar('auth_method', { length: 50 }).notNull(), // 'otp', 'email', 'sgips', 'saml'
+    isActive: boolean('is_active').default(true).notNull(),
+    lastActivityAt: timestamp('last_activity_at').defaultNow().notNull(), // 마지막 활동 시간
+    sessionExpiredAt: timestamp('session_expired_at'), // 세션 만료 시간
+    createdAt: timestamp('created_at').defaultNow().notNull(),
+    updatedAt: timestamp('updated_at').defaultNow().notNull(),
+});
+
+// 페이지 방문 이력 테이블 (라우트별 접속 이력)
+export const pageVisits = pgTable('page_visits', {
+    id: uuid('id').primaryKey().defaultRandom(),
+    userId: integer('user_id').references(() => users.id, { onDelete: 'cascade' }), // nullable (비로그인 사용자 추적 가능)
+    sessionId: uuid('session_id').references(() => loginSessions.id, { onDelete: 'set null' }), // nullable
+    route: varchar('route', { length: 500 }).notNull(), // 방문한 라우트
+    pageTitle: varchar('page_title', { length: 200 }), // 페이지 제목
+    referrer: text('referrer'), // 이전 페이지 URL
+    ipAddress: inet('ip_address').notNull(),
+    userAgent: text('user_agent'),
+    visitedAt: timestamp('visited_at').defaultNow().notNull(),
+    duration: integer('duration'), // 페이지 체류 시간 (초 단위, nullable)
+    // 추가 메타데이터
+    queryParams: text('query_params'), // URL 쿼리 파라미터
+    deviceType: varchar('device_type', { length: 50 }), // mobile, desktop, tablet
+    browserName: varchar('browser_name', { length: 50 }),
+    osName: varchar('os_name', { length: 50 }),
+});
+
+// 일별 접속 통계 테이블 (선택사항 - 성능 최적화용)
+export const dailyAccessStats = pgTable('daily_access_stats', {
+    id: uuid('id').primaryKey().defaultRandom(),
+    date: timestamp('date').notNull(),
+    totalVisits: integer('total_visits').default(0).notNull(),
+    uniqueUsers: integer('unique_users').default(0).notNull(),
+    totalSessions: integer('total_sessions').default(0).notNull(),
+    avgSessionDuration: integer('avg_session_duration'), // 평균 세션 지속 시간 (초)
+    createdAt: timestamp('created_at').defaultNow().notNull(),
+});
+
+// Relations 정의
+export const usersRelationsLogin = relations(users, ({ many }) => ({
+    loginSessions: many(loginSessions),
+    pageVisits: many(pageVisits),
+}));
+
+export const loginSessionsRelations = relations(loginSessions, ({ one, many }) => ({
+    user: one(users, {
+        fields: [loginSessions.userId],
+        references: [users.id],
+    }),
+    pageVisits: many(pageVisits),
+}));
+
+export const pageVisitsRelations = relations(pageVisits, ({ one }) => ({
+    user: one(users, {
+        fields: [pageVisits.userId],
+        references: [users.id],
+    }),
+    session: one(loginSessions, {
+        fields: [pageVisits.sessionId],
+        references: [loginSessions.id],
+    }),
+}));
+
+
+// NextAuth 연동을 위한 추가 필드
+export const tempAuthSessions = pgTable('temp_auth_sessions', {
+    id: uuid('id').primaryKey().defaultRandom(),
+    tempAuthKey: varchar('temp_auth_key', { length: 255 }).unique().notNull(),
+    userId: integer('user_id').references(() => users.id, { onDelete: 'cascade' }).notNull(),
+    email: varchar('email', { length: 255 }).notNull(),
+    authMethod: varchar('auth_method', { length: 50 }).notNull(), // 'otp', 'email', 'sgips', 'saml'
+    expiresAt: timestamp('expires_at').notNull(),
+    isUsed: boolean('is_used').default(false).notNull(),
+    createdAt: timestamp('created_at').defaultNow().notNull(),
+});
+
+
+
+export type TempAuthSession = typeof tempAuthSessions.$inferSelect;
+export type NewTempAuthSession = typeof tempAuthSessions.$inferInsert;
+
+
+export type LoginSession = typeof loginSessions.$inferSelect;
+export type NewLoginSession = typeof loginSessions.$inferInsert;
+
+export type PageVisit = typeof pageVisits.$inferSelect;
+export type NewPageVisit = typeof pageVisits.$inferInsert;
+
+export type DailyAccessStats = typeof dailyAccessStats.$inferSelect;
+export type NewDailyAccessStats = typeof dailyAccessStats.$inferInsert;
\ No newline at end of file
diff --git a/db/schema/index.ts b/db/schema/index.ts
index 99750e26..387bba8c 100644
--- a/db/schema/index.ts
+++ b/db/schema/index.ts
@@ -26,6 +26,7 @@ export * from './menu';
 export * from './information';
 export * from './qna';
 export * from './notice';
+export * from './history';
 
 // MDG SOAP 수신용
 export * from './MDG/mdg'
diff --git a/db/schema/vendorDocu.ts b/db/schema/vendorDocu.ts
index 1953d5ac..80eb5c5d 100644
--- a/db/schema/vendorDocu.ts
+++ b/db/schema/vendorDocu.ts
@@ -226,6 +226,7 @@ export const enhancedDocumentsView = pgView("enhanced_documents_view", {
   projectCode: varchar("project_code", { length: 50 }),
   vendorName: varchar("vendor_name", { length: 255 }),
   vendorCode: varchar("vendor_code", { length: 50 }),
+  vendorId: varchar("vendor_id", { length: 50 }),
 
       // ✅ B4 전용 필드들 (drawingKind가 B4일 때만 사용)
       cGbn: varchar("c_gbn", { length: 50 }), // CGbn
@@ -482,6 +483,7 @@ export const enhancedDocumentsView = pgView("enhanced_documents_view", {
     p.code as project_code,
     v.vendor_name as vendor_name,
     v.vendor_code as vendor_code,
+    c.vendor_id as vendor_id,
     
     -- 현재 스테이지 정보
     csi.current_stage_id,
diff --git a/db/schema/vendors.ts b/db/schema/vendors.ts
index 0a694a40..78ecf83c 100644
--- a/db/schema/vendors.ts
+++ b/db/schema/vendors.ts
@@ -51,6 +51,7 @@ export const vendors = pgTable("vendors", {
   representativeBirth: varchar("representative_birth", { length: 20 }),
   representativeEmail: varchar("representative_email", { length: 255 }),
   representativePhone: varchar("representative_phone", { length: 50 }),
+  representativeWorkExpirence: boolean("representative_work_expirence").default(false),
   corporateRegistrationNumber: varchar("corporate_registration_number", {
     length: 100,
   }),
@@ -151,6 +152,7 @@ export const vendorMaterialsView = pgView("vendor_materials_view").as((qb) => {
 export const vendorAttachments = pgTable("vendor_attachments", {
   id: serial("id").primaryKey(),
   vendorId: integer("vendor_id").references(() => vendors.id),
+  fileType: varchar("file_type", { length: 255 }),
   fileName: varchar("file_name", { length: 255 }).notNull(),
   filePath: varchar("file_path", { length: 1024 }).notNull(),
   attachmentType: varchar("attachment_type", {