(대표님) 20250620 작업사항

5 files changed, 607 insertions, 2 deletions

diff --git a/db/schema/evaluationCriteria.ts b/db/schema/evaluationCriteria.ts
new file mode 100644
index 00000000..23a987cf
--- /dev/null
+++ b/db/schema/evaluationCriteria.ts
@@ -0,0 +1,137 @@
+/* IMPORT */

+import {

+  decimal,

+  integer,

+  pgTable,

+  pgView,

+  serial,

+  text,

+  timestamp,

+  varchar,

+} from 'drizzle-orm/pg-core';

+import { eq, relations } from 'drizzle-orm';

+

+// ----------------------------------------------------------------------------------------------------

+

+/* CONSTANTS */

+const REG_EVAL_CRITERIA_CATEGORY = [

+  { label: 'CS', value: 'customer-service' },

+  { label: '관리자', value: 'administrator' },

+  { label: '구매', value: 'procurement' },

+  { label: '설계', value: 'design' },

+  { label: '조달', value: 'sourcing' },

+  { label: '품질', value: 'quality' },

+];

+const REG_EVAL_CRITERIA_ITEM = [

+  { label: '가점항목', value: 'customer-service' },

+  { label: '납기', value: 'delivery' },

+  { label: '경영현황', value: 'management-status' },

+  { label: '감점항목', value: 'penalty-item' },

+  { label: '구매', value: 'procurement' },

+  { label: '품질', value: 'quality' },

+];

+const REG_EVAL_CRITERIA_CATEGORY2 = [

+  { label: '공정', value: 'processScore' },

+  { label: '가격', value: 'priceScore' },

+  { label: '납기', value: 'deliveryScore' },

+  { label: '자율평가', value: 'selfEvaluationScore' },

+];

+

+const REG_EVAL_CRITERIA_CATEGORY_ENUM = REG_EVAL_CRITERIA_CATEGORY.map(c => c.value) as [string, ...string[]];

+const REG_EVAL_CRITERIA_CATEGORY_ENUM2 = REG_EVAL_CRITERIA_CATEGORY2.map(c => c.value) as [string, ...string[]];

+const REG_EVAL_CRITERIA_ITEM_ENUM = REG_EVAL_CRITERIA_ITEM.map(c => c.value) as [string, ...string[]];

+

+// ----------------------------------------------------------------------------------------------------

+

+/* TABLE SCHEMATA */

+const regEvalCriteria = pgTable('reg_eval_criteria', {

+  id: serial('id').primaryKey(),

+  category: varchar('category', { enum: REG_EVAL_CRITERIA_CATEGORY_ENUM, length: 32 }).default('quality').notNull(),

+  category2: varchar('category', { enum: REG_EVAL_CRITERIA_CATEGORY_ENUM2, length: 32 }).default('processScore').notNull(),

+  item: varchar('item', { enum: REG_EVAL_CRITERIA_ITEM_ENUM, length: 32 }).default('quality').notNull(),

+  classification: varchar('classification', { length: 255 }).notNull(),

+  range: varchar('range', { length: 255 }),

+  remarks: text('remarks'),

+  createdAt: timestamp('created_at').defaultNow().notNull(),

+  updatedAt: timestamp('updated_at').defaultNow().notNull(),

+});

+const regEvalCriteriaDetails = pgTable('reg_eval_criteria_details', {

+  id: serial('id').primaryKey(),

+  criteriaId: integer('criteria_id')

+    .notNull()

+    .references(() => regEvalCriteria.id, { onDelete: 'cascade' }),

+  detail: text('detail').notNull(),

+  orderIndex: integer('order_index').default(0).notNull(),

+  scoreEquipShip: decimal('score_equip_ship', { precision: 5, scale: 2 }),

+  scoreEquipMarine: decimal('score_equip_marine', { precision: 5, scale: 2 }),

+  scoreBulkShip: decimal('score_bulk_ship', { precision: 5, scale: 2 }),

+  scoreBulkMarine: decimal('score_bulk_marine', { precision: 5, scale: 2 }),

+  createdAt: timestamp('created_at').defaultNow().notNull(),

+  updatedAt: timestamp('updated_at').defaultNow().notNull(),

+});

+

+// ----------------------------------------------------------------------------------------------------

+

+/* VIEWS */

+const regEvalCriteriaView = pgView('reg_eval_criteria_view').as((qb) =>

+  qb

+    .select({

+      id: regEvalCriteria.id,

+      category: regEvalCriteria.category,

+      item: regEvalCriteria.item,

+      classification: regEvalCriteria.classification,

+      range: regEvalCriteria.range,

+      detailId: regEvalCriteriaDetails.id,

+      detail: regEvalCriteriaDetails.detail,

+      orderIndex: regEvalCriteriaDetails.orderIndex,

+      scoreEquipShip: regEvalCriteriaDetails.scoreEquipShip,

+      scoreEquipMarine: regEvalCriteriaDetails.scoreEquipMarine,

+      scoreBulkShip: regEvalCriteriaDetails.scoreBulkShip,

+      scoreBulkMarine: regEvalCriteriaDetails.scoreBulkMarine,

+    })

+    .from(regEvalCriteria)

+    .leftJoin(regEvalCriteriaDetails, eq(regEvalCriteria.id, regEvalCriteriaDetails.criteriaId))

+    .orderBy(regEvalCriteria.id, regEvalCriteriaDetails.orderIndex)

+);

+

+// ----------------------------------------------------------------------------------------------------

+

+/* RELATIONS */

+const regEvalCriteriaRelations = relations(regEvalCriteria, ({ many }) => ({

+  details: many(regEvalCriteriaDetails),

+}));

+const regEvalCriteriaDetailsRelations = relations(regEvalCriteriaDetails, ({one}) => ({

+  criteria: one(regEvalCriteria, {

+    fields: [regEvalCriteriaDetails.criteriaId],

+    references: [regEvalCriteria.id],

+  }),

+}));

+

+// ----------------------------------------------------------------------------------------------------

+

+/* TYPES */

+type RegEvalCriteria = typeof regEvalCriteria.$inferSelect;

+type NewRegEvalCriteria = typeof regEvalCriteria.$inferInsert;

+type RegEvalCriteriaDetails = typeof regEvalCriteriaDetails.$inferSelect;

+type NewRegEvalCriteriaDetails = typeof regEvalCriteriaDetails.$inferInsert;

+type RegEvalCriteriaWithDetails = RegEvalCriteria & { criteriaDetails: RegEvalCriteriaDetails[] };

+type RegEvalCriteriaView = typeof regEvalCriteriaView.$inferSelect;

+

+// ----------------------------------------------------------------------------------------------------

+

+/* Export */

+export {

+  REG_EVAL_CRITERIA_CATEGORY,

+  REG_EVAL_CRITERIA_ITEM,

+  regEvalCriteria,

+  regEvalCriteriaDetails,

+  regEvalCriteriaDetailsRelations,

+  regEvalCriteriaRelations,

+  regEvalCriteriaView,

+  type NewRegEvalCriteria,

+  type NewRegEvalCriteriaDetails,

+  type RegEvalCriteriaView,

+  type RegEvalCriteriaWithDetails,

+  type RegEvalCriteria,

+  type RegEvalCriteriaDetails,

+};
\ No newline at end of file
diff --git a/db/schema/evaluationTarget.ts b/db/schema/evaluationTarget.ts
index 4eff1c19..915641c8 100644
--- a/db/schema/evaluationTarget.ts
+++ b/db/schema/evaluationTarget.ts
@@ -3,6 +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";
 
 // 평가 대상 메인 테이블
 export const evaluationTargets = pgTable("evaluation_targets", {
@@ -178,7 +179,7 @@ export const orderRecordsRelations = relations(contracts, ({ one }) => ({
   }),
 }));
 
-// 평가 담당 부서 코드 상수 (조직 API와 매핑)
+// 평가 담당 부서 코드 상수 
 export const EVALUATION_DEPARTMENT_CODES = {
   ORDER_EVAL: "ORDER_EVAL",           // 발주 평가 담당
   PROCUREMENT_EVAL: "PROCUREMENT_EVAL", // 조달 평가 담당
@@ -192,7 +193,7 @@ export type EvaluationDepartmentCode = keyof typeof EVALUATION_DEPARTMENT_CODES;
   // ============= TypeScript 타입 정의 =============
   
   export type EvaluationTargetStatus = "PENDING" | "CONFIRMED" | "EXCLUDED";
-  export type Division = "OCEAN" | "SHIPYARD";
+  export type Division = "PLANT" | "SHIP";
   export type MaterialType = "EQUIPMENT" | "BULK" | "EQUIPMENT_BULK";
   export type DomesticForeign = "DOMESTIC" | "FOREIGN";
   
@@ -383,3 +384,339 @@ export const evaluationTargetsWithDepartments = pgView("evaluation_targets_with_
 // 타입 정의
 export type EvaluationTargetWithDepartments = typeof evaluationTargetsWithDepartments.$inferSelect;
 
+
+
+export const periodicEvaluations = pgTable("periodic_evaluations", {
+  id: serial("id").primaryKey(),
+  
+  // 평가 대상 참조
+  evaluationTargetId: integer("evaluation_target_id")
+    .references(() => evaluationTargets.id, { onDelete: "cascade" })
+    .notNull(),
+  
+  // 평가 기본 정보 
+  evaluationPeriod: varchar("evaluation_period", { length: 20 }).notNull(), // "상반기", "하반기", "연간" 등
+  
+  // 업체 제출 관련
+  documentsSubmitted: boolean("documents_submitted").default(false),
+  submissionDate: timestamp("submission_date"),
+  submissionDeadline: timestamp("submission_deadline"),
+  
+  // 평가 점수 (최종 확정)
+  finalScore: decimal("final_score", { precision: 5, scale: 2 }),
+  finalGrade: varchar("final_grade", { 
+    length: 5,
+    enum: ["S", "A", "B", "C", "D"] 
+  }),
+  
+  // 평가 점수 (평가자 평균)
+  evaluationScore: decimal("evaluation_score", { precision: 5, scale: 2 }),
+  evaluationGrade: varchar("evaluation_grade", { 
+    length: 5,
+    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,
+    enum: ["PENDING_SUBMISSION", "SUBMITTED", "IN_REVIEW", "REVIEW_COMPLETED", "FINALIZED"]
+  }).notNull().default("PENDING_SUBMISSION"),
+  
+  // 평가 완료 정보
+  reviewCompletedAt: timestamp("review_completed_at"),
+  finalizedAt: timestamp("finalized_at"),
+  finalizedBy: integer("finalized_by").references(() => users.id),
+  
+  // 비고
+  evaluationNote: text("evaluation_note"),
+  
+  createdAt: timestamp("created_at").defaultNow().notNull(),
+  updatedAt: timestamp("updated_at").defaultNow().notNull(),
+}, (table) => ({
+  // 같은 평가대상에 같은 기간에는 하나의 평가만
+  uniqueEvaluationTarget: unique("unique_evaluation_target")
+    .on(table.evaluationTargetId, table.evaluationPeriod),
+}));
+
+
+// 2. 리뷰어별 개별 평가 테이블
+export const reviewerEvaluations = pgTable("reviewer_evaluations", {
+  id: serial("id").primaryKey(),
+  
+  // 정기평가 참조
+  periodicEvaluationId: integer("periodic_evaluation_id")
+    .references(() => periodicEvaluations.id, { onDelete: "cascade" })
+    .notNull(),
+  
+  // 리뷰어 정보 (evaluationTargetReviewers 참조)
+  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"),
+  
+  // 리뷰어 의견
+  reviewerComment: text("reviewer_comment"),
+  
+  createdAt: timestamp("created_at").defaultNow().notNull(),
+  updatedAt: timestamp("updated_at").defaultNow().notNull(),
+}, (table) => ({
+  // 같은 평가에 같은 리뷰어는 하나의 평가만
+  uniqueReviewerEvaluation: unique("unique_reviewer_evaluation")
+    .on(table.periodicEvaluationId, table.evaluationTargetReviewerId),
+}));
+
+// 2-1. 리뷰어별 세부 평가 점수 테이블 (평가표의 각 항목별 점수)
+export const reviewerEvaluationDetails = pgTable("reviewer_evaluation_details", {
+  id: serial("id").primaryKey(),
+  
+  // 리뷰어 평가 참조
+  reviewerEvaluationId: integer("reviewer_evaluation_id")
+    .references(() => reviewerEvaluations.id, { onDelete: "cascade" })
+    .notNull(),
+  
+  // 평가 기준 참조
+  regEvalCriteriaDetailsId: integer("reg_eval_criteria_details_id")
+    .references(() => regEvalCriteriaDetails.id)
+    .notNull(),
+  
+  // 리뷰어가 매긴 점수
+  score: decimal("score", { precision: 5, scale: 2 }).notNull(),
+  
+  // 세부 의견
+  comment: text("comment"),
+  
+  createdAt: timestamp("created_at").defaultNow().notNull(),
+  updatedAt: timestamp("updated_at").defaultNow().notNull(),
+}, (table) => ({
+  // 같은 리뷰어 평가에서 같은 평가 기준은 하나의 점수만
+  uniqueReviewerCriteria: unique("unique_reviewer_criteria")
+    .on(table.reviewerEvaluationId, table.regEvalCriteriaDetailsId),
+}));
+
+// 1. periodicEvaluations relations
+export const periodicEvaluationsRelations = relations(periodicEvaluations, ({ one, many }) => ({
+  // 평가 대상
+  evaluationTarget: one(evaluationTargets, {
+    fields: [periodicEvaluations.evaluationTargetId],
+    references: [evaluationTargets.id],
+  }),
+  
+  // 최종 확정자
+  finalizedByUser: one(users, {
+    fields: [periodicEvaluations.finalizedBy],
+    references: [users.id],
+  }),
+  
+  // 리뷰어별 평가들
+  reviewerEvaluations: many(reviewerEvaluations),
+  
+}));
+
+// 2. reviewerEvaluations relations
+export const reviewerEvaluationsRelations = relations(reviewerEvaluations, ({ one, many }) => ({
+  // 정기평가
+  periodicEvaluation: one(periodicEvaluations, {
+    fields: [reviewerEvaluations.periodicEvaluationId],
+    references: [periodicEvaluations.id],
+  }),
+  
+  // 평가 대상 리뷰어
+  evaluationTargetReviewer: one(evaluationTargetReviewers, {
+    fields: [reviewerEvaluations.evaluationTargetReviewerId],
+    references: [evaluationTargetReviewers.id],
+  }),
+  
+  // 세부 평가 점수들
+  evaluationDetails: many(reviewerEvaluationDetails),
+}));
+
+// 3. reviewerEvaluationDetails relations
+export const reviewerEvaluationDetailsRelations = relations(reviewerEvaluationDetails, ({ one }) => ({
+  // 리뷰어 평가
+  reviewerEvaluation: one(reviewerEvaluations, {
+    fields: [reviewerEvaluationDetails.reviewerEvaluationId],
+    references: [reviewerEvaluations.id],
+  }),
+  
+  // 평가 기준 세부사항
+  regEvalCriteriaDetail: one(regEvalCriteriaDetails, {
+    fields: [reviewerEvaluationDetails.regEvalCriteriaDetailsId],
+    references: [regEvalCriteriaDetails.id],
+  }),
+}));
+
+// ----------------------------------------------------------------------------------------------------
+
+/* TYPES */
+type PeriodicEvaluation = typeof periodicEvaluations.$inferSelect;
+type NewPeriodicEvaluation = typeof periodicEvaluations.$inferInsert;
+
+type ReviewerEvaluation = typeof reviewerEvaluations.$inferSelect;
+type NewReviewerEvaluation = typeof reviewerEvaluations.$inferInsert;
+
+type ReviewerEvaluationDetail = typeof reviewerEvaluationDetails.$inferSelect;
+type NewReviewerEvaluationDetail = typeof reviewerEvaluationDetails.$inferInsert;
+
+// 관계 포함 타입들
+type PeriodicEvaluationWithRelations = PeriodicEvaluation & {
+  evaluationTarget?: typeof evaluationTargets.$inferSelect;
+  finalizedByUser?: typeof users.$inferSelect;
+  reviewerEvaluations?: ReviewerEvaluationWithRelations[];
+};
+
+type ReviewerEvaluationWithRelations = ReviewerEvaluation & {
+  periodicEvaluation?: PeriodicEvaluation;
+  evaluationTargetReviewer?: typeof evaluationTargetReviewers.$inferSelect;
+  evaluationDetails?: ReviewerEvaluationDetailWithRelations[];
+};
+
+type ReviewerEvaluationDetailWithRelations = ReviewerEvaluationDetail & {
+  reviewerEvaluation?: ReviewerEvaluation;
+  regEvalCriteriaDetail?: typeof regEvalCriteriaDetails.$inferSelect;
+};
+
+export const periodicEvaluationsView = pgView('periodic_evaluations_view').as((qb) =>
+  qb
+    .select({
+      // ═══════════════════════════════════════════════════════════════
+      // 정기평가 기본 정보 (평가 대상 핵심 정보 포함)
+      // ═══════════════════════════════════════════════════════════════
+      id: periodicEvaluations.id,
+      evaluationTargetId: periodicEvaluations.evaluationTargetId,
+      
+      // 평가 대상 핵심 정보 (조인으로 가져와서 기본 정보로 포함)
+      evaluationYear: evaluationTargets.evaluationYear,
+      division: evaluationTargets.division,
+      vendorId: evaluationTargets.vendorId,
+      vendorCode: evaluationTargets.vendorCode,
+      vendorName: evaluationTargets.vendorName,
+      domesticForeign: evaluationTargets.domesticForeign,
+      materialType: evaluationTargets.materialType,
+      
+      // 평가 기간
+      evaluationPeriod: periodicEvaluations.evaluationPeriod,
+      
+      // 업체 제출 관련
+      documentsSubmitted: periodicEvaluations.documentsSubmitted,
+      submissionDate: periodicEvaluations.submissionDate,
+      submissionDeadline: periodicEvaluations.submissionDeadline,
+      
+      // 평가 점수 (최종 확정)
+      finalScore: periodicEvaluations.finalScore,
+      finalGrade: periodicEvaluations.finalGrade,
+      
+      // 평가 점수 (평가자 평균)
+      evaluationScore: periodicEvaluations.evaluationScore,
+      evaluationGrade: periodicEvaluations.evaluationGrade,
+      
+      // 평가항목별 점수
+      processScore: periodicEvaluations.processScore,
+      priceScore: periodicEvaluations.priceScore,
+      deliveryScore: periodicEvaluations.deliveryScore,
+      selfEvaluationScore: periodicEvaluations.selfEvaluationScore,
+      
+      // 합계 점수
+      totalScore: periodicEvaluations.totalScore,
+      
+      // 가점/감점
+      participationBonus: periodicEvaluations.participationBonus,
+      qualityDeduction: periodicEvaluations.qualityDeduction,
+      
+      // 평가 상태
+      status: periodicEvaluations.status,
+      
+      // 평가 완료 정보
+      reviewCompletedAt: periodicEvaluations.reviewCompletedAt,
+      finalizedAt: periodicEvaluations.finalizedAt,
+      finalizedBy: periodicEvaluations.finalizedBy,
+      
+      // 비고
+      evaluationNote: periodicEvaluations.evaluationNote,
+      
+      // 생성/수정일
+      createdAt: periodicEvaluations.createdAt,
+      updatedAt: periodicEvaluations.updatedAt,
+      
+      // ═══════════════════════════════════════════════════════════════
+      // 평가 대상 추가 정보 (evaluationTargets 조인)
+      // ═══════════════════════════════════════════════════════════════
+      evaluationTargetStatus: evaluationTargets.status,
+      evaluationTargetAdminComment: evaluationTargets.adminComment,
+      evaluationTargetConsolidatedComment: evaluationTargets.consolidatedComment,
+      evaluationTargetConsensusStatus: evaluationTargets.consensusStatus,
+      evaluationTargetConfirmedAt: evaluationTargets.confirmedAt,
+      
+      // ═══════════════════════════════════════════════════════════════
+      // 리뷰어 통계 (서브쿼리로 계산)
+      // ═══════════════════════════════════════════════════════════════
+      totalReviewers: sql<number>`(
+        SELECT COUNT(*)::int
+        FROM ${reviewerEvaluations} re
+        WHERE re.periodic_evaluation_id = ${periodicEvaluations.id}
+      )`.as('total_reviewers'),
+      
+      completedReviewers: sql<number>`(
+        SELECT COUNT(*)::int
+        FROM ${reviewerEvaluations} re
+        WHERE re.periodic_evaluation_id = ${periodicEvaluations.id}
+        AND re.is_completed = true
+      )`.as('completed_reviewers'),
+      
+      pendingReviewers: sql<number>`(
+        SELECT COUNT(*)::int
+        FROM ${reviewerEvaluations} re
+        WHERE re.periodic_evaluation_id = ${periodicEvaluations.id}
+        AND re.is_completed = false
+      )`.as('pending_reviewers'),
+      
+      // ═══════════════════════════════════════════════════════════════
+      // 최종 확정자 정보
+      // ═══════════════════════════════════════════════════════════════
+      finalizedByUserName: users.name,
+      finalizedByUserEmail: users.email,
+    })
+    .from(periodicEvaluations)
+    .leftJoin(evaluationTargets, eq(periodicEvaluations.evaluationTargetId, evaluationTargets.id))
+    .leftJoin(users, eq(periodicEvaluations.finalizedBy, users.id))
+    .orderBy(periodicEvaluations.createdAt)
+);
+
+
+// ================================================================
+// TYPES
+// ================================================================
+
+export type PeriodicEvaluationView = typeof periodicEvaluationsView.$inferSelect;
diff --git a/db/schema/index.ts b/db/schema/index.ts
index fc59692d..480207f9 100644
--- a/db/schema/index.ts
+++ b/db/schema/index.ts
@@ -20,6 +20,7 @@ export * from './bRfq';
 export * from './techVendors';
 export * from './evaluation';
 export * from './evaluationTarget';
+export * from './projectGtc';
 
 // MDG SOAP 수신용
 // export * from './MDG/modelMaster'
diff --git a/db/schema/projectGtc.ts b/db/schema/projectGtc.ts
new file mode 100644
index 00000000..220e42df
--- /dev/null
+++ b/db/schema/projectGtc.ts
@@ -0,0 +1,45 @@
+import { pgTable, pgView, timestamp, integer, varchar, serial } from 'drizzle-orm/pg-core';
+import { projects } from './projects';
+import { eq, sql } from "drizzle-orm";
+
+export const projectGtcFiles = pgTable('project_gtc_files', {
+  id: serial("id").primaryKey(),
+  projectId: integer('project_id').references(() => projects.id).notNull(),
+  fileName: varchar("file_name", { length: 255 }).notNull(),
+  filePath: varchar("file_path", { length: 1024 }).notNull(),
+  originalFileName: varchar("original_file_name", { length: 255 }).notNull(),
+  fileSize: integer('file_size'),
+  mimeType: varchar("mime_type", { length: 100 }),
+  createdAt: timestamp('created_at').defaultNow().notNull(),
+  updatedAt: timestamp('updated_at').defaultNow().notNull(),
+});
+
+// Project GTC 뷰 (프로젝트 정보와 파일 정보를 조인)
+export const projectGtcView = pgView('project_gtc_view').as((qb) => {
+  return qb
+    .select({
+      // 프로젝트 정보
+      id: sql<number>`${projects.id}`.as('id'),
+      code: sql<string>`${projects.code}`.as('code'),
+      name: sql<string>`${projects.name}`.as('name'),
+      type: sql<string>`${projects.type}`.as('type'),
+      projectCreatedAt: sql<Date>`${projects.createdAt}`.as('project_created_at'),
+      projectUpdatedAt: sql<Date>`${projects.updatedAt}`.as('project_updated_at'),
+      
+      // GTC 파일 정보
+      gtcFileId: sql<number | null>`${projectGtcFiles.id}`.as('gtc_file_id'),
+      fileName: sql<string | null>`${projectGtcFiles.fileName}`.as('fileName'),
+      filePath: sql<string | null>`${projectGtcFiles.filePath}`.as('filePath'),
+      originalFileName: sql<string | null>`${projectGtcFiles.originalFileName}`.as('originalFileName'),
+      fileSize: sql<number | null>`${projectGtcFiles.fileSize}`.as('fileSize'),
+      mimeType: sql<string | null>`${projectGtcFiles.mimeType}`.as('mimeType'),
+      gtcCreatedAt: sql<Date | null>`${projectGtcFiles.createdAt}`.as('gtcCreatedAt'),
+      gtcUpdatedAt: sql<Date | null>`${projectGtcFiles.updatedAt}`.as('gtcUpdatedAt'),
+    })
+    .from(projects)
+    .leftJoin(projectGtcFiles, eq(projects.id, projectGtcFiles.projectId));
+});
+
+// 타입 정의
+export type ProjectGtcFile = typeof projectGtcFiles.$inferSelect;
+export type ProjectGtcView = typeof projectGtcView.$inferSelect; 
\ No newline at end of file
diff --git a/db/schema/techSales.ts b/db/schema/techSales.ts
index 334bf6bb..744d22cc 100644
--- a/db/schema/techSales.ts
+++ b/db/schema/techSales.ts
@@ -34,6 +34,8 @@ import {
   integer,
   numeric,
   date,
+  json,
+  index,
 } from "drizzle-orm/pg-core";
 import { relations } from "drizzle-orm";
 import { biddingProjects } from "./projects";
@@ -60,6 +62,7 @@ export const TECH_SALES_QUOTATION_STATUSES = {
   SUBMITTED: "Submitted", 
   REVISED: "Revised",
   ACCEPTED: "Accepted",
+  REJECTED: "Rejected",
 } as const;
 
 export type TechSalesQuotationStatus = typeof TECH_SALES_QUOTATION_STATUSES[keyof typeof TECH_SALES_QUOTATION_STATUSES];
@@ -90,6 +93,12 @@ export const TECH_SALES_QUOTATION_STATUS_CONFIG = {
     description: "승인된 견적서",
     color: "text-green-600",
   },
+  [TECH_SALES_QUOTATION_STATUSES.REJECTED]: { 
+    label: "거절됨", 
+    variant: "destructive" as const,
+    description: "거절된 견적서",
+    color: "text-red-600",
+  },
 } as const;
 
 // ===== 스키마 정의 =====
@@ -241,6 +250,37 @@ export const techSalesVendorQuotations = pgTable(
   }
 );
 
+// 기술영업 벤더 견적서 revision 히스토리 테이블 (이전 버전 스냅샷 저장)
+export const techSalesVendorQuotationRevisions = pgTable(
+  "tech_sales_vendor_quotation_revisions",
+  {
+    id: serial("id").primaryKey(),
+    quotationId: integer("quotation_id")
+      .notNull()
+      .references(() => techSalesVendorQuotations.id, { onDelete: "cascade" }),
+    
+    // 버전 정보
+    version: integer("version").notNull(),
+    
+    // 이전 데이터 JSON 스냅샷
+    snapshot: json("snapshot").notNull(),
+    
+    // 변경 사유
+    changeReason: text("change_reason"),
+    revisionNote: text("revision_note"),
+    
+    // 변경자 정보
+    revisedBy: integer("revised_by"),
+    revisedAt: timestamp("revised_at").defaultNow().notNull(),
+  },
+  (table) => ({
+    quotationVersionIdx: index("tech_sales_quotation_revisions_quotation_version_idx").on(
+      table.quotationId,
+      table.version
+    ),
+  })
+);
+
 export const techSalesRfqComments = pgTable(
   "tech_sales_rfq_comments",
   {
@@ -299,6 +339,28 @@ export const techSalesRfqCommentAttachments = pgTable("tech_sales_rfq_comment_at
   uploadedAt: timestamp("uploaded_at").defaultNow().notNull(),
 });
 
+// 기술영업 벤더 견적서 첨부파일 테이블
+export const techSalesVendorQuotationAttachments = pgTable("tech_sales_vendor_quotation_attachments", {
+  id: serial("id").primaryKey(),
+  quotationId: integer("quotation_id")
+    .notNull()
+    .references(() => techSalesVendorQuotations.id, { onDelete: "cascade" }),
+  fileName: varchar("file_name", { length: 255 }).notNull(),
+  originalFileName: varchar("original_file_name", { length: 255 }).notNull(),
+  fileSize: integer("file_size").notNull(),
+  fileType: varchar("file_type", { length: 100 }),
+  filePath: varchar("file_path", { length: 500 }).notNull(),
+  description: text("description"), // 파일 설명
+  uploadedBy: integer("uploaded_by").references(() => users.id, {
+    onDelete: "set null",
+  }),
+  vendorId: integer("vendor_id").references(() => techVendors.id, {
+    onDelete: "set null",
+  }),
+  isVendorUpload: boolean("is_vendor_upload").default(true), // 벤더가 업로드한 파일인지
+  createdAt: timestamp("created_at").defaultNow().notNull(),
+  updatedAt: timestamp("updated_at").defaultNow().notNull(),
+});
 
 // 타입 정의
 export type TechSalesVendorQuotations =
@@ -390,6 +452,7 @@ export const techSalesVendorQuotationsRelations = relations(techSalesVendorQuota
   
   // 첨부파일 관계
   attachments: many(techSalesRfqCommentAttachments),
+  quotationAttachments: many(techSalesVendorQuotationAttachments),
 }));
 
 export const techSalesAttachmentsRelations = relations(techSalesAttachments, ({ one }) => ({
@@ -474,4 +537,26 @@ export const techSalesRfqCommentAttachmentsRelations = relations(techSalesRfqCom
     fields: [techSalesRfqCommentAttachments.vendorId],
     references: [techVendors.id],
   }),
+}));
+
+// 기술영업 벤더 견적서 첨부파일 relations
+export const techSalesVendorQuotationAttachmentsRelations = relations(techSalesVendorQuotationAttachments, ({ one }) => ({
+  // 견적서 관계
+  quotation: one(techSalesVendorQuotations, {
+    fields: [techSalesVendorQuotationAttachments.quotationId],
+    references: [techSalesVendorQuotations.id],
+  }),
+  
+  // 업로드한 사용자 관계
+  uploadedByUser: one(users, {
+    fields: [techSalesVendorQuotationAttachments.uploadedBy],
+    references: [users.id],
+    relationName: "techSalesQuotationAttachmentUploadedBy",
+  }),
+  
+  // 벤더 관계
+  vendor: one(techVendors, {
+    fields: [techSalesVendorQuotationAttachments.vendorId],
+    references: [techVendors.id],
+  }),
 }));
\ No newline at end of file