(대표님) rfq, 계약, 서명 등

3 files changed, 318 insertions, 10 deletions

diff --git a/db/schema/basicContractDocumnet.ts b/db/schema/basicContractDocumnet.ts
index 7fc975a0..0f335e90 100644
--- a/db/schema/basicContractDocumnet.ts
+++ b/db/schema/basicContractDocumnet.ts
@@ -244,4 +244,20 @@ export const basicContractTemplateStatsView = pgView('basic_contract_template_st
     );
 });
 
-export type BasicContractTemplateStatsView = typeof basicContractTemplateStatsView.$inferSelect;
\ No newline at end of file
+export type BasicContractTemplateStatsView = typeof basicContractTemplateStatsView.$inferSelect;
+
+
+export const buyerSignatures = pgTable('buyer_signatures', {
+  id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
+  name: varchar('name', { length: 255 }).notNull().default('삼성중공업'),
+  imageUrl: text('image_url').notNull(),
+  dataUrl: text('data_url'), // Base64 데이터
+  mimeType: varchar('mime_type', { length: 100 }),
+  fileSize: integer('file_size'),
+  isActive: boolean('is_active').default(true),
+  createdAt: timestamp('created_at').defaultNow().notNull(),
+  updatedAt: timestamp('updated_at').defaultNow().notNull(),
+});
+
+export type BuyerSignature = typeof buyerSignatures.$inferSelect;
+export type NewBuyerSignature = typeof buyerSignatures.$inferInsert;
\ No newline at end of file
diff --git a/db/schema/items.ts b/db/schema/items.ts
index e50ff491..d7640049 100644
--- a/db/schema/items.ts
+++ b/db/schema/items.ts
@@ -4,12 +4,12 @@ import { MATERIAL_GROUP_MASTER } from "./MDG/mdg"
 // 자재 아이템 정보 테이블 (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 }).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 - 아이템명 (자재 클래스명)
-  description: text("description"), // CLAS_DTL - 아이템 상세 설명 (클래스 내역)
+  ProjectNo: varchar("project_no", { length: 100 }).notNull(), 
+  itemCode: varchar("item_code", { length: 100 }).notNull(),
+  itemName: varchar("item_name", { length: 255 }).notNull(), 
+  packageCode: varchar("package_code", { length: 255 }).notNull(),
+  smCode: varchar("sm_code", { length: 255 }), 
+  description: text("description"), 
   parentItemCode: varchar("parent_item_code", { length: 18 }), // PRNT_CLAS_CD - 부모 아이템 코드 (부모 클래스 코드)
   itemLevel: integer("item_level"), // CLAS_LVL - 아이템 레벨 (클래스 레벨)
   deleteFlag: varchar("delete_flag", { length: 1 }), // DEL_ORDR - 삭제 지시자 (Y/N)
diff --git a/db/schema/rfqLast.ts b/db/schema/rfqLast.ts
index f643a2fa..b58341c5 100644
--- a/db/schema/rfqLast.ts
+++ b/db/schema/rfqLast.ts
@@ -1,4 +1,4 @@
-import { decimal, json,index, pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, alias, check, uniqueIndex, unique } from "drizzle-orm/pg-core";
+import {bigint, jsonb, decimal, json,index, pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, alias, check, uniqueIndex, unique } from "drizzle-orm/pg-core";
 import { eq, sql, relations } from "drizzle-orm";
 import { projects } from "./projects";
 import { users } from "./users";
@@ -159,7 +159,7 @@ export const rfqLastDetails = pgTable(
         
         // 계약 전환 정보 (선택사항)
         contractStatus: varchar("contract_status", { length: 30 })
-            .$type<"대기" | "진행중" | "완료" | "취소" | null>(),
+            .$type<"대기" | "진행중" | "완료" | "취소" |"입찰진행중"|"일반계약 진행중"| null>(),
         contractCreatedAt: timestamp("contract_created_at", { withTimezone: true })
             .$type<Date | null>(),
         contractNo: varchar("contract_no", { length: 50 }),
@@ -926,4 +926,296 @@ export const vendorSelections = pgTable(
         approvalStatusIdx: index("vendor_selections_approval_status_idx").on(table.approvalStatus),
       };
     }
-  );
\ No newline at end of file
+  );
+
+
+
+  export type PurchaseRequestStatus = 
+  | "작성중"        // 임시저장 상태
+  | "요청완료"      // 설계 엔지니어가 최종 제출
+  | "검토중"        // 구매팀 검토 중
+  | "승인"          // 승인되어 RFQ 생성 가능
+  | "반려"          // 반려됨
+  | "RFQ생성완료";  // rfqsLast로 이관 완료
+
+export const purchaseRequests = pgTable(
+  "purchase_requests",
+  {
+    id: serial("id").primaryKey(),
+    
+    // 요청 번호 (PR-2025-00001 형식)
+    requestCode: varchar("request_code", { length: 50 }).unique().notNull(),
+    
+    // 프로젝트 정보
+    projectId: integer("project_id")
+      .references(() => projects.id, { onDelete: "set null" }),
+    projectCode: varchar("project_code", { length: 100 }),
+    projectName: varchar("project_name", { length: 255 }),
+    
+    // ITB 관련 필드
+    projectCompany: varchar("project_company", { length: 255 }),
+    projectSite: varchar("project_site", { length: 255 }),
+    classNo: varchar("class_no", { length: 50 }),
+    
+    // 패키지 정보
+    packageNo: varchar("package_no", { length: 50 }),
+    packageName: varchar("package_name", { length: 255 }),
+    
+    // 자재 정보
+    majorItemMaterialCategory: varchar("major_item_material_category", { length: 100 }),
+    majorItemMaterialDescription: varchar("major_item_material_description", { length: 255 }),
+    smCode: varchar("sm_code", { length: 255 }),
+    
+    // 요청 내용
+    requestTitle: varchar("request_title", { length: 255 }).notNull(),
+    requestDescription: text("request_description"),
+    estimatedBudget: varchar("estimated_budget", { length: 100 }),
+    requestedDeliveryDate: timestamp("requested_delivery_date", { withTimezone: true }),
+    
+    // 아이템 목록 (JSON 배열)
+    items: jsonb("items").$type<{
+      id: string;
+      itemCode: string;
+      itemName: string;
+      specification: string;
+      quantity: number;
+      unit: string;
+      estimatedUnitPrice?: number;
+      remarks?: string;
+    }[]>().default([]),
+    
+    // 상태 관리
+    status: varchar("status", { length: 30 })
+      .$type<PurchaseRequestStatus>()
+      .default("작성중")
+      .notNull(),
+    
+    // 반려 사유
+    rejectReason: text("reject_reason"),
+    
+    // 요청 확정 정보
+    confirmedAt: timestamp("confirmed_at", { withTimezone: true }),
+    confirmedBy: integer("confirmed_by")
+      .references(() => users.id, { onDelete: "set null" }),
+    
+    // RFQ 생성 정보
+    rfqId: integer("rfq_id"),
+    rfqCode: varchar("rfq_code", { length: 50 }),
+    rfqCreatedAt: timestamp("rfq_created_at", { withTimezone: true }),
+    
+    // 담당자 정보
+    engPicId: integer("eng_pic_id")
+      .references(() => users.id, { onDelete: "set null" }),
+    engPicName: varchar("eng_pic_name", { length: 50 }),
+    
+    // 구매 담당자
+    purchasePicId: integer("purchase_pic_id")
+      .references(() => users.id, { onDelete: "set null" }),
+    purchasePicName: varchar("purchase_pic_name", { length: 50 }),
+    
+    // 메타 정보
+    createdBy: integer("created_by")
+      .notNull()
+      .references(() => users.id, { onDelete: "set null" }),
+    updatedBy: integer("updated_by")
+      .notNull()
+      .references(() => users.id, { onDelete: "set null" }),
+    createdAt: timestamp("created_at").defaultNow().notNull(),
+    updatedAt: timestamp("updated_at").defaultNow().notNull(),
+  }
+);
+
+// 첨부파일 테이블
+export const purchaseRequestAttachments = pgTable(
+  "purchase_request_attachments",
+  {
+    id: serial("id").primaryKey(),
+    requestId: integer("request_id")
+      .notNull()
+      .references(() => purchaseRequests.id, { onDelete: "cascade" }),
+    
+    // 파일 정보
+    fileName: varchar("file_name", { length: 255 }).notNull(),
+    originalFileName: varchar("original_file_name", { length: 255 }).notNull(),
+    filePath: varchar("file_path", { length: 512 }).notNull(),
+    fileSize: integer("file_size"),
+    fileType: varchar("file_type", { length: 100 }),
+    
+    // 첨부파일 분류
+    category: varchar("category", { length: 50 }).notNull().default("설계문서"),
+    description: varchar("description", { length: 500 }),
+    
+    // 메타 정보
+    createdBy: integer("created_by")
+      .references(() => users.id, { onDelete: "set null" })
+      .notNull(),
+    createdAt: timestamp("created_at").defaultNow().notNull(),
+  },
+  (table) => ({
+    requestFileIdx: uniqueIndex("request_file_idx").on(table.requestId, table.fileName),
+  })
+);
+
+
+
+export const purchaseRequestsView = pgView("purchase_requests_view", {
+    // 기본 정보
+    id: serial("id"),
+    requestCode: varchar("request_code", { length: 50 }),
+    requestTitle: varchar("request_title", { length: 255 }),
+    requestDescription: text("request_description"),
+    
+    // 프로젝트 정보
+    projectId: integer("project_id"),
+    projectCode: varchar("project_code", { length: 100 }),
+    projectName: varchar("project_name", { length: 255 }),
+    projectCompany: varchar("project_company", { length: 255 }),
+    projectSite: varchar("project_site", { length: 255 }),
+    classNo: varchar("class_no", { length: 50 }),
+    
+    // 패키지 정보
+    packageNo: varchar("package_no", { length: 50 }),
+    packageName: varchar("package_name", { length: 255 }),
+    
+    // 자재 정보
+    majorItemMaterialCategory: varchar("major_item_material_category", { length: 100 }),
+    majorItemMaterialDescription: varchar("major_item_material_description", { length: 255 }),
+    smCode: varchar("sm_code", { length: 255 }),
+    
+    // 예산 및 납기
+    estimatedBudget: varchar("estimated_budget", { length: 100 }),
+    requestedDeliveryDate: timestamp("requested_delivery_date", { withTimezone: true }),
+    
+    // 아이템 정보
+    items: jsonb("items"),
+    itemCount: integer("item_count"),
+    totalQuantity: numeric("total_quantity"),
+    totalEstimatedAmount: numeric("total_estimated_amount"),
+    
+    // 상태 정보
+    status: varchar("status", { length: 30 }),
+    rejectReason: text("reject_reason"),
+    
+    // 확정 정보
+    confirmedAt: timestamp("confirmed_at", { withTimezone: true }),
+    confirmedBy: integer("confirmed_by"),
+    confirmedByName: varchar("confirmed_by_name", { length: 100 }),
+    
+    // RFQ 정보
+    rfqId: integer("rfq_id"),
+    rfqCode: varchar("rfq_code", { length: 50 }),
+    rfqCreatedAt: timestamp("rfq_created_at", { withTimezone: true }),
+    
+    // 담당자 정보
+    engPicId: integer("eng_pic_id"),
+    engPicName: varchar("eng_pic_name", { length: 50 }),
+    engPicEmail: varchar("eng_pic_email", { length: 100 }),
+    
+    purchasePicId: integer("purchase_pic_id"),
+    purchasePicName: varchar("purchase_pic_name", { length: 50 }),
+    purchasePicEmail: varchar("purchase_pic_email", { length: 100 }),
+    
+    // 첨부파일 정보
+    attachmentCount: bigint("attachment_count", { mode: "number" }),
+    
+    // 생성/수정 정보
+    createdBy: integer("created_by"),
+    createdByName: varchar("created_by_name", { length: 100 }),
+    createdByEmail: varchar("created_by_email", { length: 255 }),
+    updatedBy: integer("updated_by"),
+    updatedByName: varchar("updated_by_name", { length: 100 }),
+    updatedByEmail: varchar("updated_by_email", { length: 255 }),
+    createdAt: timestamp("created_at"),
+    updatedAt: timestamp("updated_at"),
+  }).as(sql`
+    SELECT 
+      pr.id,
+      pr.request_code,
+      pr.request_title,
+      pr.request_description,
+      
+      -- 프로젝트 정보
+      pr.project_id,
+      pr.project_code,
+      pr.project_name,
+      pr.project_company,
+      pr.project_site,
+      pr.class_no,
+      
+      -- 패키지 정보
+      pr.package_no,
+      pr.package_name,
+      
+      -- 자재 정보
+      pr.major_item_material_category,
+      pr.major_item_material_description,
+      pr.sm_code,
+      
+      -- 예산 및 납기
+      pr.estimated_budget,
+      pr.requested_delivery_date,
+      
+      -- 아이템 정보
+      pr.items,
+      jsonb_array_length(pr.items) as item_count,
+      (
+        SELECT SUM((item->>'quantity')::numeric)
+        FROM jsonb_array_elements(pr.items) as item
+      ) as total_quantity,
+      (
+        SELECT SUM(
+          (item->>'quantity')::numeric * 
+          COALESCE((item->>'estimatedUnitPrice')::numeric, 0)
+        )
+        FROM jsonb_array_elements(pr.items) as item
+      ) as total_estimated_amount,
+      
+      -- 상태 정보
+      pr.status,
+      pr.reject_reason,
+      
+      -- 확정 정보
+      pr.confirmed_at,
+      pr.confirmed_by,
+      cb.name as confirmed_by_name,
+      
+      -- RFQ 정보
+      pr.rfq_id,
+      pr.rfq_code,
+      pr.rfq_created_at,
+      
+      -- 담당자 정보
+      pr.eng_pic_id,
+      pr.eng_pic_name,
+      ep.email as eng_pic_email,
+      
+      pr.purchase_pic_id,
+      pr.purchase_pic_name,
+      pp.email as purchase_pic_email,
+      
+      -- 첨부파일 수
+      (
+        SELECT COUNT(*)
+        FROM purchase_request_attachments pra
+        WHERE pra.request_id = pr.id
+      ) as attachment_count,
+      
+      -- 생성/수정 정보
+      pr.created_by,
+      cr.name as created_by_name,
+      cr.email as created_by_email,
+      pr.updated_by,
+      up.name as updated_by_name,
+      up.email as updated_by_email,
+      pr.created_at,
+      pr.updated_at
+      
+    FROM purchase_requests pr
+    LEFT JOIN users cb ON cb.id = pr.confirmed_by
+    LEFT JOIN users ep ON ep.id = pr.eng_pic_id
+    LEFT JOIN users pp ON pp.id = pr.purchase_pic_id
+    LEFT JOIN users cr ON cr.id = pr.created_by
+    LEFT JOIN users up ON up.id = pr.updated_by
+  `);
+  
+  export type PurchaseRequestView = typeof purchaseRequestsView.$inferSelect;
\ No newline at end of file