5 files changed, 200 insertions, 106 deletions
diff --git a/db/schema/basicContractDocumnet.ts b/db/schema/basicContractDocumnet.ts
index 33530ca9..25069942 100644
--- a/db/schema/basicContractDocumnet.ts
+++ b/db/schema/basicContractDocumnet.ts
@@ -9,8 +9,8 @@ export const basicContractTemplates = pgTable('basic_contract_templates', {
   templateName: text('template_name').notNull(),
   revision: integer('revision').notNull().default(1), // 리비전
   status: text('status').notNull().default('ACTIVE'), // ACTIVE, DISPOSED
-  fileName: varchar("file_name", { length: 255 }).notNull(),
-  filePath: varchar("file_path", { length: 1024 }).notNull(),
+  fileName: varchar("file_name", { length: 255 }),
+  filePath: varchar("file_path", { length: 1024 }),
   validityPeriod: integer('validity_period'), // 계약 유효기간(개월)
   
   // 법무검토 관련
diff --git a/db/schema/evaluationTarget.ts b/db/schema/evaluationTarget.ts
index c8011e96..f55cacc4 100644
--- a/db/schema/evaluationTarget.ts
+++ b/db/schema/evaluationTarget.ts
@@ -124,7 +124,7 @@ export const evaluationTargetReviews = pgTable("evaluation_target_reviews", {
 }, (table) => ({
   // 같은 평가대상에 같은 담당자는 한 번만 검토
   uniqueTargetReviewer: unique("unique_target_reviewer")
-    .on(table.evaluationTargetId, table.reviewerUserId),
+    .on(table.evaluationTargetId, table.reviewerUserId, table.departmentCode),
 }));
 
 
@@ -321,7 +321,9 @@ export const evaluationTargetsWithDepartments = pgView("evaluation_targets_with_
     )
     .leftJoin(
       sql`evaluation_target_reviews order_review`,
-      sql`${evaluationTargets.id} = order_review.evaluation_target_id AND order_review.reviewer_user_id = order_reviewer.id`
+      sql`${evaluationTargets.id} = order_review.evaluation_target_id 
+           AND order_review.reviewer_user_id = order_reviewer.id 
+           AND order_review.department_code = 'ORDER_EVAL'`
     )
     
     // 조달 평가 담당자 JOIN
@@ -335,7 +337,9 @@ export const evaluationTargetsWithDepartments = pgView("evaluation_targets_with_
     )
     .leftJoin(
       sql`evaluation_target_reviews procurement_review`,
-      sql`${evaluationTargets.id} = procurement_review.evaluation_target_id AND procurement_review.reviewer_user_id = procurement_reviewer.id`
+      sql`${evaluationTargets.id} = procurement_review.evaluation_target_id 
+           AND procurement_review.reviewer_user_id = procurement_reviewer.id 
+           AND procurement_review.department_code = 'PROCUREMENT_EVAL'`
     )
     
     // 품질 평가 담당자 JOIN
@@ -349,7 +353,9 @@ export const evaluationTargetsWithDepartments = pgView("evaluation_targets_with_
     )
     .leftJoin(
       sql`evaluation_target_reviews quality_review`,
-      sql`${evaluationTargets.id} = quality_review.evaluation_target_id AND quality_review.reviewer_user_id = quality_reviewer.id`
+      sql`${evaluationTargets.id} = quality_review.evaluation_target_id 
+           AND quality_review.reviewer_user_id = quality_reviewer.id 
+           AND quality_review.department_code = 'QUALITY_EVAL'`
     )
     
     // 설계 평가 담당자 JOIN
@@ -363,7 +369,9 @@ export const evaluationTargetsWithDepartments = pgView("evaluation_targets_with_
     )
     .leftJoin(
       sql`evaluation_target_reviews design_review`,
-      sql`${evaluationTargets.id} = design_review.evaluation_target_id AND design_review.reviewer_user_id = design_reviewer.id`
+      sql`${evaluationTargets.id} = design_review.evaluation_target_id 
+           AND design_review.reviewer_user_id = design_reviewer.id 
+           AND design_review.department_code = 'DESIGN_EVAL'`
     )
     
     // CS 평가 담당자 JOIN
@@ -377,7 +385,9 @@ export const evaluationTargetsWithDepartments = pgView("evaluation_targets_with_
     )
     .leftJoin(
       sql`evaluation_target_reviews cs_review`,
-      sql`${evaluationTargets.id} = cs_review.evaluation_target_id AND cs_review.reviewer_user_id = cs_reviewer.id`
+      sql`${evaluationTargets.id} = cs_review.evaluation_target_id 
+           AND cs_review.reviewer_user_id = cs_reviewer.id 
+           AND cs_review.department_code = 'CS_EVAL'`
     );
 });
 
diff --git a/db/schema/gtc.ts b/db/schema/gtc.ts
index a4052d61..281b6af9 100644
--- a/db/schema/gtc.ts
+++ b/db/schema/gtc.ts
@@ -8,9 +8,9 @@ import {
     pgEnum,
     boolean,
     index,
-    uniqueIndex
+    uniqueIndex,  pgView  
   } from "drizzle-orm/pg-core"
-  import { relations } from "drizzle-orm"
+  import { relations ,sql, eq} from "drizzle-orm"
 import { projects } from "./projects"
 import { users } from "./users"
   
@@ -107,4 +107,172 @@ import { users } from "./users"
       id: number
       name: string
     }
-  }
-\ No newline at end of file
+  }
+
+
+  export const gtcDocumentsView = pgView('gtc_documents_view').as((qb) =>
+    qb
+      .select({
+        // GTC 문서 기본 필드
+        id: gtcDocuments.id,
+        type: gtcDocuments.type,
+        projectId: gtcDocuments.projectId,
+        revision: gtcDocuments.revision,
+        fileName: gtcDocuments.fileName,
+        filePath: gtcDocuments.filePath,
+        fileSize: gtcDocuments.fileSize,
+        createdAt: gtcDocuments.createdAt,
+        createdById: gtcDocuments.createdById,
+        updatedAt: gtcDocuments.updatedAt,
+        updatedById: gtcDocuments.updatedById,
+        editReason: gtcDocuments.editReason,
+        isActive: gtcDocuments.isActive,
+        
+        // 프로젝트 정보
+        projectCode: projects.code,
+        projectName: projects.name,
+        
+        // 생성자 정보  
+        createdByName: sql<string>`created_by_user.name`.as('created_by_name'),
+        createdByEmail: sql<string>`created_by_user.email`.as('created_by_email'),
+        
+        // 수정자 정보
+        updatedByName: sql<string>`updated_by_user.name`.as('updated_by_name'),
+        updatedByEmail: sql<string>`updated_by_user.email`.as('updated_by_email'),
+        
+        // 집계 필드들
+        // 같은 타입/프로젝트의 총 문서 수
+        totalDocumentsInGroup: sql<number>`
+          (
+            SELECT count(*)
+            FROM gtc_documents gd2
+            WHERE gd2.type = ${gtcDocuments.type}
+            AND gd2.is_active = true
+            AND (
+              (${gtcDocuments.type} = 'project' AND gd2.project_id = ${gtcDocuments.projectId}) OR
+              (${gtcDocuments.type} = 'standard' AND gd2.project_id IS NULL)
+            )
+          )
+        `.as('total_documents_in_group'),
+        
+        // 최신 리비전 번호
+        latestRevision: sql<number>`
+          (
+            SELECT max(revision)
+            FROM gtc_documents gd3
+            WHERE gd3.type = ${gtcDocuments.type}
+            AND gd3.is_active = true
+            AND (
+              (${gtcDocuments.type} = 'project' AND gd3.project_id = ${gtcDocuments.projectId}) OR
+              (${gtcDocuments.type} = 'standard' AND gd3.project_id IS NULL)
+            )
+          )
+        `.as('latest_revision'),
+        
+        // 현재 문서가 최신인지 여부
+        isLatestRevision: sql<boolean>`
+          ${gtcDocuments.revision} = (
+            SELECT max(revision)
+            FROM gtc_documents gd4
+            WHERE gd4.type = ${gtcDocuments.type}
+            AND gd4.is_active = true
+            AND (
+              (${gtcDocuments.type} = 'project' AND gd4.project_id = ${gtcDocuments.projectId}) OR
+              (${gtcDocuments.type} = 'standard' AND gd4.project_id IS NULL)
+            )
+          )
+        `.as('is_latest_revision'),
+        
+        // 이전 리비전 ID (있다면)
+        previousRevisionId: sql<number>`
+          (
+            SELECT id
+            FROM gtc_documents gd5
+            WHERE gd5.type = ${gtcDocuments.type}
+            AND gd5.is_active = true
+            AND gd5.revision < ${gtcDocuments.revision}
+            AND (
+              (${gtcDocuments.type} = 'project' AND gd5.project_id = ${gtcDocuments.projectId}) OR
+              (${gtcDocuments.type} = 'standard' AND gd5.project_id IS NULL)
+            )
+            ORDER BY gd5.revision DESC
+            LIMIT 1
+          )
+        `.as('previous_revision_id'),
+        
+        // 다음 리비전 ID (있다면)
+        nextRevisionId: sql<number>`
+          (
+            SELECT id
+            FROM gtc_documents gd6
+            WHERE gd6.type = ${gtcDocuments.type}
+            AND gd6.is_active = true
+            AND gd6.revision > ${gtcDocuments.revision}
+            AND (
+              (${gtcDocuments.type} = 'project' AND gd6.project_id = ${gtcDocuments.projectId}) OR
+              (${gtcDocuments.type} = 'standard' AND gd6.project_id IS NULL)
+            )
+            ORDER BY gd6.revision ASC
+            LIMIT 1
+          )
+        `.as('next_revision_id'),
+        
+        // 파일 크기 (사람이 읽기 쉬운 형태)
+        fileSizeFormatted: sql<string>`
+          CASE 
+            WHEN ${gtcDocuments.fileSize} IS NULL THEN NULL
+            WHEN ${gtcDocuments.fileSize} < 1024 THEN ${gtcDocuments.fileSize} || ' B'
+            WHEN ${gtcDocuments.fileSize} < 1024 * 1024 THEN round(${gtcDocuments.fileSize} / 1024.0, 1) || ' KB'
+            WHEN ${gtcDocuments.fileSize} < 1024 * 1024 * 1024 THEN round(${gtcDocuments.fileSize} / (1024.0 * 1024), 1) || ' MB'
+            ELSE round(${gtcDocuments.fileSize} / (1024.0 * 1024 * 1024), 1) || ' GB'
+          END
+        `.as('file_size_formatted'),
+        
+        // 프로젝트가 있는 경우, 해당 프로젝트의 총 GTC 문서 수
+        projectTotalDocuments: sql<number>`
+          CASE 
+            WHEN ${gtcDocuments.projectId} IS NOT NULL THEN (
+              SELECT count(*)
+              FROM gtc_documents gd7
+              WHERE gd7.project_id = ${gtcDocuments.projectId}
+              AND gd7.is_active = true
+            )
+            ELSE NULL
+          END
+        `.as('project_total_documents'),
+        
+        // 리비전 히스토리 (배열로 반환)
+        revisionHistory: sql<number[]>`
+          (
+            SELECT array_agg(revision ORDER BY revision)
+            FROM gtc_documents gd8
+            WHERE gd8.type = ${gtcDocuments.type}
+            AND gd8.is_active = true
+            AND (
+              (${gtcDocuments.type} = 'project' AND gd8.project_id = ${gtcDocuments.projectId}) OR
+              (${gtcDocuments.type} = 'standard' AND gd8.project_id IS NULL)
+            )
+          )
+        `.as('revision_history'),
+        
+        // 수정 이력이 있는지 여부
+        hasEditHistory: sql<boolean>`
+          ${gtcDocuments.createdById} != ${gtcDocuments.updatedById} OR 
+          ${gtcDocuments.createdAt} != ${gtcDocuments.updatedAt}
+        `.as('has_edit_history'),
+      })
+      .from(gtcDocuments)
+      .leftJoin(projects, sql`${gtcDocuments.projectId} = ${projects.id}`)
+      .leftJoin(
+        sql`users created_by_user`, 
+        sql`${gtcDocuments.createdById} = created_by_user.id`
+      )
+      .leftJoin(
+        sql`users updated_by_user`, 
+        sql`${gtcDocuments.updatedById} = updated_by_user.id`
+      )
+  );
+
+  export type GtcDocumentView = typeof gtcDocumentsView.$inferSelect;
+
+  
+\ No newline at end of file
diff --git a/db/schema/index.ts b/db/schema/index.ts
index 19470fca..5c6aad41 100644
--- a/db/schema/index.ts
+++ b/db/schema/index.ts
@@ -29,6 +29,7 @@ export * from './notice';
 export * from './history';
 export * from './notification';
 export * from './templates';
+export * from './gtc';
 
 // 부서별 도메인 할당 관리
 export * from './departmentDomainAssignments';
diff --git a/db/schema/techVendors.ts b/db/schema/techVendors.ts
index e34d3e60..582ff724 100644
--- a/db/schema/techVendors.ts
+++ b/db/schema/techVendors.ts
@@ -1,6 +1,8 @@
 import { pgTable, serial, varchar, text, timestamp, integer, boolean, pgView } from "drizzle-orm/pg-core";
-import { sql, eq } from "drizzle-orm";
-import { items } from "./items";
+import { sql } from "drizzle-orm";
+import { itemShipbuilding } from "./items";
+import { itemOffshoreTop } from "./items";
+import { itemOffshoreHull } from "./items";
 
 // 벤더 타입 enum 정의
 export const VENDOR_TYPES = ["조선", "해양TOP", "해양HULL"] as const;
@@ -53,6 +55,7 @@ export const techVendorContacts = pgTable("tech_vendor_contacts", {
   vendorId: integer("vendor_id").notNull().references(() => techVendors.id),
   contactName: varchar("contact_name", { length: 255 }).notNull(),
   contactPosition: varchar("contact_position", { length: 100 }),
+  contactTitle: varchar("contact_title", { length: 100 }),
   contactEmail: varchar("contact_email", { length: 255 }).notNull(),
   contactPhone: varchar("contact_phone", { length: 50 }),
   contactCountry: varchar("contact_country", { length: 100 }),
@@ -65,16 +68,9 @@ export const techVendorPossibleItems = pgTable("tech_vendor_possible_items", {
   
   // 벤더 정보
   vendorId: integer("vendor_id").notNull().references(() => techVendors.id),
-  vendorCode: varchar("vendor_code", { length: 100 }), // 검색/필터용 중복 저장
-  vendorEmail: varchar("vendor_email", { length: 255 }), // 검색/필터용 중복 저장
-  
-  // 아이템 정보 (itemCode도 nullable로 변경 - 해양 HULL 등에서 없을 수 있음)
-  itemCode: varchar("item_code", { length: 100 }).notNull(),
-  workType: varchar("work_type", { length: 100 }), // 공종 (nullable)
-  shipTypes: varchar("ship_types", { length: 255 }), // 선종 (nullable)
-  itemList: varchar("item_list", { length: 500 }), // 아이템 리스트 (nullable)
-  subItemList: varchar("sub_item_list", { length: 500 }), // 서브 아이템 리스트 (nullable)
-  
+  shipbuildingItemId: integer("shipbuilding_item_id").references(() => itemShipbuilding.id),
+  offshoreTopItemId: integer("offshore_top_item_id").references(() => itemOffshoreTop.id),
+  offshoreHullItemId: integer("offshore_hull_item_id").references(() => itemOffshoreHull.id),
   createdAt: timestamp("created_at").defaultNow().notNull(),
   updatedAt: timestamp("updated_at").defaultNow().notNull(),
 });
@@ -90,19 +86,6 @@ export const techVendorAttachments = pgTable("tech_vendor_attachments", {
   createdAt: timestamp("created_at").defaultNow().notNull(),
   updatedAt: timestamp("updated_at").defaultNow().notNull(),
 });
-// 뷰: 벤더 + 아이템 정보 조인
-export const techVendorItemsView = pgView("tech_vendor_items_view").as((qb) => {
-  return qb
-    .select({
-      vendorItemId: techVendorPossibleItems.id,
-      vendorId: techVendorPossibleItems.vendorId,
-      itemCode: items.itemCode,
-      createdAt: techVendorPossibleItems.createdAt,
-      updatedAt: techVendorPossibleItems.updatedAt,
-    })
-    .from(techVendorPossibleItems)
-    .leftJoin(items, eq(techVendorPossibleItems.itemCode, items.itemCode));
-});
 
 // 벤더 상세 정보 뷰 (연락처 정보 포함)
 export const techVendorDetailView = pgView("tech_vendor_detail_view").as((qb) => {
@@ -140,6 +123,7 @@ export const techVendorDetailView = pgView("tech_vendor_detail_view").as((qb) =>
               'id', c.id,
               'contactName', c.contact_name,
               'contactPosition', c.contact_position,
+              'contactTitle', c.contact_title,
               'contactEmail', c.contact_email,
               'contactPhone', c.contact_phone,
               'isPrimary', c.is_primary
@@ -147,7 +131,7 @@ export const techVendorDetailView = pgView("tech_vendor_detail_view").as((qb) =>
           ),
           '[]'::json
         )
-        FROM vendor_contacts c
+        FROM tech_vendor_contacts c
         WHERE c.vendor_id = tech_vendors.id)
       `.as("contacts"),
       // 첨부파일 정보 (수정된 버전)
@@ -181,21 +165,6 @@ export const techVendorDetailView = pgView("tech_vendor_detail_view").as((qb) =>
         WHERE c.vendor_id = tech_vendors.id)
       `.as("contact_count"),
       
-      // 가능 아이템 목록 JSON
-      possibleItems: sql<string>`
-        (SELECT COALESCE(
-          json_agg(
-            json_build_object(
-              'itemCode', i.item_code
-            )
-          ),
-          '[]'::json
-        )
-        FROM tech_vendor_possible_items i
-        LEFT JOIN items it ON i.item_code = it.item_code
-        WHERE i.vendor_id = tech_vendors.id)
-      `.as("possible_items"),
-      
       // 아이템 수
       itemCount: sql<number>`
         (SELECT COUNT(*) 
@@ -207,65 +176,11 @@ export const techVendorDetailView = pgView("tech_vendor_detail_view").as((qb) =>
 });
 
 
-export const techVendorCandidates = pgTable("tech_vendor_candidates", {
-  id: serial("id").primaryKey(),
-  companyName: varchar("company_name", { length: 255 }).notNull(),
-  contactEmail: varchar("contact_email", { length: 255 }),
-  contactPhone: varchar("contact_phone", { length: 50 }),
-  taxId: varchar("tax_id", { length: 100 }),
-  country: varchar("country", { length: 100 }),
-  status: varchar("status", {
-    length: 30,
-    enum: [
-      "COLLECTED",  // 단순 데이터 수집 완료
-      "INVITED",    // 초청 메일 발송
-      "DISCARDED",  // 불필요, 검토 후 배제됨
-    ],
-  })
-    .notNull()
-    .default("COLLECTED"),
-  remark: text("remark"),
-  vendorId: integer("vendor_id")
-    .references(() => techVendors.id, { onDelete: "cascade" }),
-  createdAt: timestamp("created_at").defaultNow().notNull(),
-  updatedAt: timestamp("updated_at").defaultNow().notNull(),
-});
-export const techVendorCandidatesWithVendorInfo = pgView("tech_vendor_candidates_with_vendor_info").as((qb) => {
-  return qb
-    .select({
-      // ----------------------------------------
-      // 1) techVendorCandidates 기본 필드
-      id: techVendorCandidates.id,
-      companyName: techVendorCandidates.companyName,
-      contactEmail: techVendorCandidates.contactEmail,
-      contactPhone: techVendorCandidates.contactPhone,
-      taxId: techVendorCandidates.taxId,
-      country: techVendorCandidates.country,
-      status: techVendorCandidates.status,
-      remark: techVendorCandidates.remark,
-      createdAt: techVendorCandidates.createdAt,
-      updatedAt: techVendorCandidates.updatedAt,
-
-      // ----------------------------------------
-      // 2) techVendors 조인해서 가져올 필드
-      vendorName: techVendors.vendorName,
-      vendorCode: techVendors.vendorCode,
-      vendorCreatedAt: sql<Date>`${techVendors.createdAt}`.as("vendor_created_at"),
-
-    })
-    .from(techVendorCandidates)
-    .leftJoin(techVendors, eq(techVendorCandidates.vendorId, techVendors.id));
-});
-
-export type TechVendorCandidatesWithVendorInfo = typeof techVendorCandidatesWithVendorInfo.$inferSelect;
-
 export type TechVendor = typeof techVendors.$inferSelect
 export type TechVendorContact = typeof techVendorContacts.$inferSelect
 export type TechVendorItem = typeof techVendorPossibleItems.$inferSelect
 export type TechVendorAttach = typeof techVendorAttachments.$inferSelect
-export type TechVendorItemsView = typeof techVendorItemsView.$inferSelect
 export type TechVendorDetailView = typeof techVendorDetailView.$inferSelect
-export type TechVendorCandidate = typeof techVendorCandidates.$inferSelect
 export type TechVendorWithAttachments = TechVendor & {
   hasAttachments?: boolean;
   attachmentsList?: TechVendorAttach[];