(대표님) 기본계약 및 정기평가 작업사항, OCR 변경사항

2 files changed, 64 insertions, 16 deletions

diff --git a/db/schema/basicContractDocumnet.ts b/db/schema/basicContractDocumnet.ts
index ce22fdc9..18dba460 100644
--- a/db/schema/basicContractDocumnet.ts
+++ b/db/schema/basicContractDocumnet.ts
@@ -1,20 +1,41 @@
-import { pgTable, pgView, text, timestamp, integer, varchar } from 'drizzle-orm/pg-core';
+import { pgTable, pgView, text, timestamp, integer, varchar, boolean } from 'drizzle-orm/pg-core';
 import { vendors } from './vendors';
 import { eq, sql } from "drizzle-orm";
 import { users } from './users';
 
 export const basicContractTemplates = pgTable('basic_contract_templates', {
   id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
+  templateCode: varchar("template_code", { length: 50 }).notNull().unique(), // 템플릿 코드
   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(),
+  validityPeriod: integer('validity_period'), // 계약 유효기간(개월)
+  
+  // 법무검토 관련
+  legalReviewRequired: boolean('legal_review_required').notNull().default(false), // 법무검토 필요 여부
+  
+  // 적용 범위 (각 사업부별)
+  shipBuildingApplicable: boolean('shipbuilding_applicable').notNull().default(false), // 조선해양
+  windApplicable: boolean('wind_applicable').notNull().default(false), // 풍력
+  pcApplicable: boolean('pc_applicable').notNull().default(false), // PC
+  nbApplicable: boolean('nb_applicable').notNull().default(false), // NB
+  rcApplicable: boolean('rc_applicable').notNull().default(false), // RC
+  gyApplicable: boolean('gy_applicable').notNull().default(false), // GY
+  sysApplicable: boolean('sys_applicable').notNull().default(false), // S&Sys
+  infraApplicable: boolean('infra_applicable').notNull().default(false), // Infra
+  
+  // 감사 정보
   createdAt: timestamp('created_at').defaultNow(),
+  createdBy: integer('created_by').references(() => users.id), // 생성자
   updatedAt: timestamp('updated_at').defaultNow(),
-  validityPeriod: integer('validity_period'), // 계약 유효기간(개월) - 새로 추가할 필드
-
+  updatedBy: integer('updated_by').references(() => users.id), // 수정자
+  disposedAt: timestamp('disposed_at'), // 폐기일자
+  restoredAt: timestamp('restored_at'), // 복구일자
 });
 
+
 export const basicContract = pgTable('basic_contract', {
   id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
   templateId: integer('template_id').references(() => basicContractTemplates.id),
@@ -42,25 +63,49 @@ export const basicContractView = pgView('basic_contract_view').as((qb) => {
       status: sql<string>`${basicContract.status}`.as('basic_contract_status'),
       createdAt: sql<Date>`${basicContract.createdAt}`.as('created_at'),
       updatedAt: sql<Date>`${basicContract.updatedAt}`.as('updated_at'),
-      completedAt: sql<Date>`${basicContract.updatedAt}`.as('completed_at'),
+      completedAt: sql<Date | null>`${basicContract.completedAt}`.as('completed_at'),
       
       // 벤더 정보
       vendorCode: sql<string | null>`${vendors.vendorCode}`.as('vendor_code'),
       vendorEmail: sql<string | null>`${vendors.email}`.as('vendor_email'),
       vendorName: sql<string | null>`${vendors.vendorName}`.as('vendor_name'),
       
-      // 사용자 정보
-      userName: sql<string | null>`${users.name}`.as('user_name'),
+      // 요청자 정보
+      requestedByName: sql<string | null>`${users.name}`.as('requested_by_name'),
       
-      // 템플릿 정보
+      // 템플릿 정보 (확장된 필드들)
+      templateCode: sql<string | null>`${basicContractTemplates.templateCode}`.as('template_code'),
       templateName: sql<string | null>`${basicContractTemplates.templateName}`.as('template_name'),
-      validityPeriod: sql<number | null>`${basicContractTemplates.validityPeriod}`.as('validityPeriod'),
-
-      filePath: sql<string | null>`${basicContractTemplates.filePath}`.as('file_path'),
-      fileName: sql<string | null>`${basicContractTemplates.fileName}`.as('file_name'),
-
+      templateRevision: sql<number | null>`${basicContractTemplates.revision}`.as('template_revision'),
+      templateStatus: sql<string | null>`${basicContractTemplates.status}`.as('template_status'),
+      validityPeriod: sql<number | null>`${basicContractTemplates.validityPeriod}`.as('validity_period'),
+      legalReviewRequired: sql<boolean | null>`${basicContractTemplates.legalReviewRequired}`.as('legal_review_required'),
+      
+      // 적용 범위 정보
+      shipBuildingApplicable: sql<boolean | null>`${basicContractTemplates.shipBuildingApplicable}`.as('shipbuilding_applicable'),
+      windApplicable: sql<boolean | null>`${basicContractTemplates.windApplicable}`.as('wind_applicable'),
+      pcApplicable: sql<boolean | null>`${basicContractTemplates.pcApplicable}`.as('pc_applicable'),
+      nbApplicable: sql<boolean | null>`${basicContractTemplates.nbApplicable}`.as('nb_applicable'),
+      rcApplicable: sql<boolean | null>`${basicContractTemplates.rcApplicable}`.as('rc_applicable'),
+      gyApplicable: sql<boolean | null>`${basicContractTemplates.gyApplicable}`.as('gy_applicable'),
+      sysApplicable: sql<boolean | null>`${basicContractTemplates.sysApplicable}`.as('sys_applicable'),
+      infraApplicable: sql<boolean | null>`${basicContractTemplates.infraApplicable}`.as('infra_applicable'),
+      
+      // 템플릿 파일 정보
+      templateFilePath: sql<string | null>`${basicContractTemplates.filePath}`.as('template_file_path'),
+      templateFileName: sql<string | null>`${basicContractTemplates.fileName}`.as('template_file_name'),
+      
+      // 서명된 계약서 파일 정보
       signedFilePath: sql<string | null>`${basicContract.filePath}`.as('signed_file_path'),
-
+      signedFileName: sql<string | null>`${basicContract.fileName}`.as('signed_file_name'),
+      
+      // 템플릿 감사 정보
+      templateCreatedAt: sql<Date | null>`${basicContractTemplates.createdAt}`.as('template_created_at'),
+      templateCreatedBy: sql<number | null>`${basicContractTemplates.createdBy}`.as('template_created_by'),
+      templateUpdatedAt: sql<Date | null>`${basicContractTemplates.updatedAt}`.as('template_updated_at'),
+      templateUpdatedBy: sql<number | null>`${basicContractTemplates.updatedBy}`.as('template_updated_by'),
+      templateDisposedAt: sql<Date | null>`${basicContractTemplates.disposedAt}`.as('template_disposed_at'),
+      templateRestoredAt: sql<Date | null>`${basicContractTemplates.restoredAt}`.as('template_restored_at'),
     })
     .from(basicContract)
     .leftJoin(vendors, eq(basicContract.vendorId, vendors.id))
diff --git a/db/schema/items.ts b/db/schema/items.ts
index d57fa6b4..102f897f 100644
--- a/db/schema/items.ts
+++ b/db/schema/items.ts
@@ -1,10 +1,10 @@
-import { pgTable, varchar, text, timestamp ,serial, integer} from "drizzle-orm/pg-core"
+import { pgTable, varchar, text, timestamp ,serial, integer, unique} from "drizzle-orm/pg-core"
 
 // 자재 아이템 정보 테이블 (items) - 기존 CMCTB_MAT_CLAS 테이블 매핑 (SOAP 연결 시 컬럼이 추가/삭제될 수 있음)
 export const items = pgTable("items", {
   id: serial("id").primaryKey(), // 고유 식별자 (신규 추가)
   ProjectNo: varchar("project_no", { length: 100 }).notNull(), // CLAS_CD - 아이템 코드 (자재 클래스 코드)
-  itemCode: varchar("item_code", { length: 100 }).unique(), // CLAS_CD - 아이템 코드 (자재 클래스 코드)
+  itemCode: varchar("item_code", { length: 100 }).notNull(), // CLAS_CD - 아이템 코드 (자재 클래스 코드) - unique 제거
   itemName: varchar("item_name", { length: 255 }).notNull(), // CLAS_NM - 아이템명 (자재 클래스명)
   packageCode: varchar("package_code", { length: 255 }).notNull(), // CLAS_NM - 아이템명 (자재 클래스명)
   smCode: varchar("sm_code", { length: 255 }), // CLAS_NM - 아이템명 (자재 클래스명)
@@ -19,7 +19,10 @@ export const items = pgTable("items", {
   baseUnitOfMeasure: varchar("base_unit_of_measure", { length: 3 }), // BSE_UOM - 기준 단위 (Base UOM)
   createdAt: timestamp("created_at").defaultNow().notNull(), // 생성일시 (신규 추가)
   updatedAt: timestamp("updated_at").defaultNow().notNull(), // 수정일시 (신규 추가)
-});
+}, (table) => ({
+  // ProjectNo와 itemCode의 복합 unique constraint
+  projectItemUnique: unique("project_item_unique").on(table.ProjectNo, table.itemCode),
+}));
 
 export const materials = pgTable("materials", {
   id: serial("id").primaryKey(), // 고유 식별자 (신규 추가)