(대표님) 20250619 1844 KST 작업사항

1 files changed, 385 insertions, 0 deletions

diff --git a/db/schema/evaluationTarget.ts b/db/schema/evaluationTarget.ts
new file mode 100644
index 00000000..4eff1c19
--- /dev/null
+++ b/db/schema/evaluationTarget.ts
@@ -0,0 +1,385 @@
+import { pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, uniqueIndex, unique, decimal } from "drizzle-orm/pg-core";
+import { eq , sql, relations} from "drizzle-orm";
+import { vendors } from "./vendors";
+import { users } from "./users";
+import { contracts } from "./contract";
+
+// 평가 대상 메인 테이블
+export const evaluationTargets = pgTable("evaluation_targets", {
+  id: serial("id").primaryKey(),
+  
+  // 평가년도 (예: 2024)
+  evaluationYear: integer("evaluation_year").notNull(),
+  
+  // 구분 (해양/조선)
+  division: varchar("division", { 
+    length: 20,
+    enum: ["SHIP", "PLANT"] 
+  }).notNull(),
+  
+  // 벤더 정보
+  vendorId: integer("vendor_id").references(() => vendors.id).notNull(),
+  vendorCode: varchar("vendor_code", { length: 100 }).notNull(),
+  vendorName: varchar("vendor_name", { length: 255 }).notNull(),
+  
+  // 내외자 구분
+  domesticForeign: varchar("domestic_foreign", { 
+    length: 20,
+    enum: ["DOMESTIC", "FOREIGN"] 
+  }).notNull(),
+  
+  // 자재구분
+  materialType: varchar("material_type", { 
+    length: 30,
+    enum: ["EQUIPMENT", "BULK", "EQUIPMENT_BULK"] 
+  }).notNull(),
+  
+  // 상태
+  status: varchar("status", { 
+    length: 30,
+    enum: ["PENDING", "CONFIRMED", "EXCLUDED"] 
+  }).notNull().default("PENDING"),
+  
+  // 관리자 의견
+  adminComment: text("admin_comment"),
+  adminUserId: integer("admin_user_id").references(() => users.id),
+  
+  // 종합 담당자 의견 (각 담당자들의 의견을 모아서 표시)
+  consolidatedComment: text("consolidated_comment"),
+  
+  // 의견 일치 여부 (null: 아직 검토중, true: 일치, false: 불일치)
+  consensusStatus: boolean("consensus_status"),
+  
+  // 최종 확정일
+  confirmedAt: timestamp("confirmed_at"),
+  confirmedBy: integer("confirmed_by").references(() => users.id),
+
+  ldClaimCount: integer("ld_claim_count").default(0),
+  ldClaimAmount: decimal("ld_claim_amount", { precision: 15, scale: 2 }).default("0"),
+  ldClaimCurrency: varchar("ld_claim_currency", { 
+    length: 10,
+    enum: ["KRW", "USD", "EUR", "JPY"]
+  }).default("KRW"),
+  
+  
+  createdAt: timestamp("created_at").defaultNow().notNull(),
+  updatedAt: timestamp("updated_at").defaultNow().notNull(),
+});
+
+// 평가 대상별 담당자 지정 테이블
+export const evaluationTargetReviewers = pgTable("evaluation_target_reviewers", {
+  id: serial("id").primaryKey(),
+  
+  evaluationTargetId: integer("evaluation_target_id")
+    .references(() => evaluationTargets.id, { onDelete: "cascade" })
+    .notNull(),
+  
+
+  departmentCode: varchar("department_code", { length: 50 }).notNull(),
+  departmentNameFrom: varchar("department_name_from", { length: 50 }),
+  
+  reviewerUserId: integer("reviewer_user_id")
+    .references(() => users.id)
+    .notNull(),
+  
+  // 지정일
+  assignedAt: timestamp("assigned_at").defaultNow().notNull(),
+  assignedBy: integer("assigned_by").references(() => users.id).notNull(),
+  
+  createdAt: timestamp("created_at").defaultNow().notNull(),
+  updatedAt: timestamp("updated_at").defaultNow().notNull(),
+}, (table) => ({
+  // 같은 평가대상에 같은 부서는 한 명의 담당자만
+  uniqueTargetDepartment: unique("unique_target_department")
+    .on(table.evaluationTargetId, table.departmentCode),
+}));
+
+// 각 담당자의 평가 대상 검토 결과 테이블
+export const evaluationTargetReviews = pgTable("evaluation_target_reviews", {
+  id: serial("id").primaryKey(),
+  
+  evaluationTargetId: integer("evaluation_target_id")
+    .references(() => evaluationTargets.id, { onDelete: "cascade" })
+    .notNull(),
+  
+  reviewerUserId: integer("reviewer_user_id")
+    .references(() => users.id)
+    .notNull(),
+  
+  // 부서 코드 (API에서 부서 정보 조회용)
+  departmentCode: varchar("department_code", { length: 50 }).notNull(),
+  
+  // 평가 대상 동의 여부
+  isApproved: boolean("is_approved"),
+  
+  // 검토 의견
+  reviewComment: text("review_comment"),
+  
+  // 검토일
+  reviewedAt: timestamp("reviewed_at"),
+  
+  createdAt: timestamp("created_at").defaultNow().notNull(),
+  updatedAt: timestamp("updated_at").defaultNow().notNull(),
+}, (table) => ({
+  // 같은 평가대상에 같은 담당자는 한 번만 검토
+  uniqueTargetReviewer: unique("unique_target_reviewer")
+    .on(table.evaluationTargetId, table.reviewerUserId),
+}));
+
+
+// 관계 정의
+export const evaluationTargetsRelations = relations(evaluationTargets, ({ one, many }) => ({
+  vendor: one(vendors, {
+    fields: [evaluationTargets.vendorId],
+    references: [vendors.id],
+  }),
+  adminUser: one(users, {
+    fields: [evaluationTargets.adminUserId],
+    references: [users.id],
+  }),
+  confirmedByUser: one(users, {
+    fields: [evaluationTargets.confirmedBy],
+    references: [users.id],
+  }),
+  reviewers: many(evaluationTargetReviewers),
+  reviews: many(evaluationTargetReviews),
+}));
+
+export const evaluationTargetReviewersRelations = relations(evaluationTargetReviewers, ({ one }) => ({
+  evaluationTarget: one(evaluationTargets, {
+    fields: [evaluationTargetReviewers.evaluationTargetId],
+    references: [evaluationTargets.id],
+  }),
+  reviewer: one(users, {
+    fields: [evaluationTargetReviewers.reviewerUserId],
+    references: [users.id],
+  }),
+  assignedByUser: one(users, {
+    fields: [evaluationTargetReviewers.assignedBy],
+    references: [users.id],
+  }),
+}));
+
+export const evaluationTargetReviewsRelations = relations(evaluationTargetReviews, ({ one }) => ({
+  evaluationTarget: one(evaluationTargets, {
+    fields: [evaluationTargetReviews.evaluationTargetId],
+    references: [evaluationTargets.id],
+  }),
+  reviewer: one(users, {
+    fields: [evaluationTargetReviews.reviewerUserId],
+    references: [users.id],
+  }),
+}));
+
+export const orderRecordsRelations = relations(contracts, ({ one }) => ({
+  vendor: one(vendors, {
+    fields: [contracts.vendorId],
+    references: [vendors.id],
+  }),
+}));
+
+// 평가 담당 부서 코드 상수 (조직 API와 매핑)
+export const EVALUATION_DEPARTMENT_CODES = {
+  ORDER_EVAL: "ORDER_EVAL",           // 발주 평가 담당
+  PROCUREMENT_EVAL: "PROCUREMENT_EVAL", // 조달 평가 담당
+  QUALITY_EVAL: "QUALITY_EVAL",       // 품질 평가 담당
+  DESIGN_EVAL: "DESIGN_EVAL",         // 설계 평가 담당
+  CS_EVAL: "CS_EVAL",                 // CS 평가 담당
+} as const;
+
+export type EvaluationDepartmentCode = keyof typeof EVALUATION_DEPARTMENT_CODES;
+
+  // ============= TypeScript 타입 정의 =============
+  
+  export type EvaluationTargetStatus = "PENDING" | "CONFIRMED" | "EXCLUDED";
+  export type Division = "OCEAN" | "SHIPYARD";
+  export type MaterialType = "EQUIPMENT" | "BULK" | "EQUIPMENT_BULK";
+  export type DomesticForeign = "DOMESTIC" | "FOREIGN";
+  
+  export interface EvaluationTargetCreate {
+    evaluationYear: number;
+    division: Division;
+    vendorId: number;
+    vendorCode: string;
+    vendorName: string;
+    domesticForeign: DomesticForeign;
+    materialType: MaterialType;
+    adminComment?: string;
+    managerComment?: string;
+  }
+  
+  export interface ReviewerAssignment {
+    departmentId: number;
+    reviewerUserId: number;
+  }
+  
+  export interface EvaluationReview {
+    evaluationTargetId: number;
+    reviewerUserId: number;
+    departmentId: number;
+    isApproved: boolean;
+    reviewComment?: string;
+  }
+  
+  export interface EvaluationTargetWithReviews {
+    id: number;
+    evaluationYear: number;
+    division: Division;
+    vendorCode: string;
+    vendorName: string;
+    domesticForeign: DomesticForeign;
+    materialType: MaterialType;
+    status: EvaluationTargetStatus;
+    consensusStatus: boolean | null;
+    adminComment?: string;
+    managerComment?: string;
+    reviews: {
+      departmentName: string;
+      reviewerName: string;
+      isApproved: boolean | null;
+      reviewComment?: string;
+      reviewedAt?: Date;
+    }[];
+  }
+
+
+  
+
+export type EvaluationTargets = typeof evaluationTargets.$inferSelect;
+export type NewEvaluationTargets= typeof evaluationTargets.$inferInsert;
+
+
+export const evaluationTargetsWithDepartments = pgView("evaluation_targets_with_departments").as((qb) => {
+  return qb
+    .select({
+      // 기본 정보
+      id: evaluationTargets.id,
+      evaluationYear: evaluationTargets.evaluationYear,
+      division: evaluationTargets.division,
+      vendorCode: evaluationTargets.vendorCode,
+      vendorName: evaluationTargets.vendorName,
+      domesticForeign: evaluationTargets.domesticForeign,
+      materialType: evaluationTargets.materialType,
+      status: evaluationTargets.status,
+      consensusStatus: evaluationTargets.consensusStatus,
+      adminComment: evaluationTargets.adminComment,
+      consolidatedComment: evaluationTargets.consolidatedComment,
+      confirmedAt: evaluationTargets.confirmedAt,
+      confirmedBy: evaluationTargets.confirmedBy,
+      ldClaimCount: evaluationTargets.ldClaimCount,
+      ldClaimAmount: evaluationTargets.ldClaimAmount,
+      ldClaimCurrency: evaluationTargets.ldClaimCurrency,
+      createdAt: evaluationTargets.createdAt,
+      updatedAt: evaluationTargets.updatedAt,
+      
+      // 발주 평가 담당 (ORDER_EVAL)
+      orderReviewerName: sql<string | null>`order_reviewer.name`.as('order_reviewer_name'),
+      orderReviewerEmail: sql<string | null>`order_reviewer.email`.as('order_reviewer_email'),
+      orderDepartmentName: sql<string | null>`order_etr.department_name_from`.as('order_department_name'),
+      orderIsApproved: sql<boolean | null>`order_review.is_approved`.as('order_is_approved'),
+      orderReviewedAt: sql<Date | null>`order_review.reviewed_at`.as('order_reviewed_at'),
+      
+      // 조달 평가 담당 (PROCUREMENT_EVAL)
+      procurementReviewerName: sql<string | null>`procurement_reviewer.name`.as('procurement_reviewer_name'),
+      procurementReviewerEmail: sql<string | null>`procurement_reviewer.email`.as('procurement_reviewer_email'),
+      procurementDepartmentName: sql<string | null>`procurement_etr.department_name_from`.as('procurement_department_name'),
+      procurementIsApproved: sql<boolean | null>`procurement_review.is_approved`.as('procurement_is_approved'),
+      procurementReviewedAt: sql<Date | null>`procurement_review.reviewed_at`.as('procurement_reviewed_at'),
+      
+      // 품질 평가 담당 (QUALITY_EVAL)
+      qualityReviewerName: sql<string | null>`quality_reviewer.name`.as('quality_reviewer_name'),
+      qualityReviewerEmail: sql<string | null>`quality_reviewer.email`.as('quality_reviewer_email'),
+      qualityDepartmentName: sql<string | null>`quality_etr.department_name_from`.as('quality_department_name'),
+      qualityIsApproved: sql<boolean | null>`quality_review.is_approved`.as('quality_is_approved'),
+      qualityReviewedAt: sql<Date | null>`quality_review.reviewed_at`.as('quality_reviewed_at'),
+      
+      // 설계 평가 담당 (DESIGN_EVAL)
+      designReviewerName: sql<string | null>`design_reviewer.name`.as('design_reviewer_name'),
+      designReviewerEmail: sql<string | null>`design_reviewer.email`.as('design_reviewer_email'),
+      designDepartmentName: sql<string | null>`design_etr.department_name_from`.as('design_department_name'),
+      designIsApproved: sql<boolean | null>`design_review.is_approved`.as('design_is_approved'),
+      designReviewedAt: sql<Date | null>`design_review.reviewed_at`.as('design_reviewed_at'),
+      
+      // CS 평가 담당 (CS_EVAL)
+      csReviewerName: sql<string | null>`cs_reviewer.name`.as('cs_reviewer_name'),
+      csReviewerEmail: sql<string | null>`cs_reviewer.email`.as('cs_reviewer_email'),
+      csDepartmentName: sql<string | null>`cs_etr.department_name_from`.as('cs_department_name'),
+      csIsApproved: sql<boolean | null>`cs_review.is_approved`.as('cs_is_approved'),
+      csReviewedAt: sql<Date | null>`cs_review.reviewed_at`.as('cs_reviewed_at'),
+    })
+    .from(evaluationTargets)
+    
+    // 발주 평가 담당자 JOIN
+    .leftJoin(
+      sql`evaluation_target_reviewers order_etr`,
+      sql`${evaluationTargets.id} = order_etr.evaluation_target_id AND order_etr.department_code = 'ORDER_EVAL'`
+    )
+    .leftJoin(
+      sql`users order_reviewer`, 
+      sql`order_etr.reviewer_user_id = order_reviewer.id`
+    )
+    .leftJoin(
+      sql`evaluation_target_reviews order_review`,
+      sql`${evaluationTargets.id} = order_review.evaluation_target_id AND order_review.reviewer_user_id = order_reviewer.id`
+    )
+    
+    // 조달 평가 담당자 JOIN
+    .leftJoin(
+      sql`evaluation_target_reviewers procurement_etr`,
+      sql`${evaluationTargets.id} = procurement_etr.evaluation_target_id AND procurement_etr.department_code = 'PROCUREMENT_EVAL'`
+    )
+    .leftJoin(
+      sql`users procurement_reviewer`, 
+      sql`procurement_etr.reviewer_user_id = procurement_reviewer.id`
+    )
+    .leftJoin(
+      sql`evaluation_target_reviews procurement_review`,
+      sql`${evaluationTargets.id} = procurement_review.evaluation_target_id AND procurement_review.reviewer_user_id = procurement_reviewer.id`
+    )
+    
+    // 품질 평가 담당자 JOIN
+    .leftJoin(
+      sql`evaluation_target_reviewers quality_etr`,
+      sql`${evaluationTargets.id} = quality_etr.evaluation_target_id AND quality_etr.department_code = 'QUALITY_EVAL'`
+    )
+    .leftJoin(
+      sql`users quality_reviewer`, 
+      sql`quality_etr.reviewer_user_id = quality_reviewer.id`
+    )
+    .leftJoin(
+      sql`evaluation_target_reviews quality_review`,
+      sql`${evaluationTargets.id} = quality_review.evaluation_target_id AND quality_review.reviewer_user_id = quality_reviewer.id`
+    )
+    
+    // 설계 평가 담당자 JOIN
+    .leftJoin(
+      sql`evaluation_target_reviewers design_etr`,
+      sql`${evaluationTargets.id} = design_etr.evaluation_target_id AND design_etr.department_code = 'DESIGN_EVAL'`
+    )
+    .leftJoin(
+      sql`users design_reviewer`, 
+      sql`design_etr.reviewer_user_id = design_reviewer.id`
+    )
+    .leftJoin(
+      sql`evaluation_target_reviews design_review`,
+      sql`${evaluationTargets.id} = design_review.evaluation_target_id AND design_review.reviewer_user_id = design_reviewer.id`
+    )
+    
+    // CS 평가 담당자 JOIN
+    .leftJoin(
+      sql`evaluation_target_reviewers cs_etr`,
+      sql`${evaluationTargets.id} = cs_etr.evaluation_target_id AND cs_etr.department_code = 'CS_EVAL'`
+    )
+    .leftJoin(
+      sql`users cs_reviewer`, 
+      sql`cs_etr.reviewer_user_id = cs_reviewer.id`
+    )
+    .leftJoin(
+      sql`evaluation_target_reviews cs_review`,
+      sql`${evaluationTargets.id} = cs_review.evaluation_target_id AND cs_review.reviewer_user_id = cs_reviewer.id`
+    );
+});
+
+// 타입 정의
+export type EvaluationTargetWithDepartments = typeof evaluationTargetsWithDepartments.$inferSelect;
+