summaryrefslogtreecommitdiff
path: root/db/schema
diff options
context:
space:
mode:
Diffstat (limited to 'db/schema')
-rw-r--r--db/schema/evaluation.ts1
-rw-r--r--db/schema/evaluationTarget.ts550
-rw-r--r--db/schema/history.ts113
-rw-r--r--db/schema/index.ts1
-rw-r--r--db/schema/vendorDocu.ts2
-rw-r--r--db/schema/vendors.ts2
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", {
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-07 13:39:46 +0000